User Tools

Site Tools


stocktake_details

Stocktake Details

There are a few views but the following script provides details and stats of outstanding stocktakes for the hosted version only:

select h.session_no, h.description as name_of_stocktake, b.branch_name, c1.num_of_lines as no_of_lines, isnull(c2.num_of_Counted_lines,0) as counted_lines, convert(varchar(16),date_started,120) as date_started, convert(varchar(16),h.last_updated_datetime,120) as last_updated_datetime,  isnull(convert(varchar(16),h.complete_by,120) + ' 23:59','No Date Set') as complete_by, isnull(convert(varchar(16),h.date_snapshot,120),'Not taken') as snapshot_taken, x.description as status, CASE when h.auto_id is null THEN 'Manual' ELSE 'Scheduled' END as 'type'
from stocktake_hdr h
inner join (select session_no, count(*) as num_of_lines from stocktake_det group by session_no) c1 on c1.session_no = h.session_no
left outer join (select session_no, count(*) as num_of_Counted_lines from stocktake_det where count_quantity <> 0 group by session_no) c2 on c2.session_no = h.session_no
inner join vw_branchwarehouse b on b.whse_no = h.whse_no
inner join x_stocktake_status x on x.stocktake_status = h.stocktake_status
where h.stocktake_status not in (4,7)
order by h.session_no
  
  
select h.session_no, h.description as name_of_stocktake, b.branch_name, c1.num_of_lines as no_of_lines, isnull(c2.num_of_Counted_lines,0) as counted_lines, convert(varchar(16),date_started,120) as date_started, convert(varchar(16),h.last_updated_datetime,120) as last_updated_datetime,  isnull(convert(varchar(16),h.complete_by,120) + ' 23:59','No Date Set') as complete_by, isnull(convert(varchar(16),h.date_snapshot,120),'Not taken') as snapshot_taken, x.description as status, CASE when adjust_type = 1 THEN 'Counted Only' ELSE 'All Items' END AS 'Adjustment Type', CASE when h.auto_id is null THEN 'Manual' ELSE 'Scheduled' END as 'type'
from stocktake_hdr h
inner join (select session_no, count(*) as num_of_lines from stocktake_det group by session_no) c1 on c1.session_no = h.session_no
left outer join (select session_no, count(*) as num_of_Counted_lines from stocktake_det where count_quantity <> 0 
group by session_no) c2 on c2.session_no = h.session_no
inner join vw_branchwarehouse b on b.whse_no = h.whse_no
inner join x_stocktake_status x on x.stocktake_status = h.stocktake_status
where h.date_started > '2017-09-23' and  h.stocktake_status = 4
order by h.session_no
  
stocktake_details.txt · Last modified: 2020/06/04 06:19 by 212.42.166.188