Specify the date of sticky

Specify the date of sticky

A report requested by the chief financial officer needs to be collected for the last month. The Yifei ERP system has a monthly shift: the beginning of the period + the number of occurrences in the current period. Generally, an enterprise carries over the inventory of the previous month at the beginning of this month, such as the 10th, which means that if the inventory of the last month is 0 before the 10th;
How to solve the inventory at the end of last month? If you can get the knot of a specified date, it will be perfectly solved.
CCWN: Write a ready-made SQL statement and wrap it into a function:

-- ===================================================== ====== -- Author:
  
   
-- Create date: <2015-11-12> -- Description: <specify a date for saving. Thank you: CCWEN> -- ========================================== ========= Create function [dbo]. [GetDateLineQty] (@ Item as varchar (20), @ DateLine as nvarchar (10) returns decimal () as begindeclare @ qty as decimal) select @ qty = (. QTY + B. LC004) FROM (select LA001, LA009, sum (LA011 * LA005) as qty from invla where LA001 = @ Item AND LA004 <= @ DateLine and LA004> = LEFT (@ DateLine, 6) + '01' group by LA001, LA009) a left join (select LC001, LC003, LC004 from invlc where LC002 = LEFT (@ DateLine, 6) B ON. LA001 = B. LC001 and. LA009 = B. LC003 left join invmbon. LA001 = INVMB. MB001return @ qtyend
  

Let's test it:

-- ============================================= -- Purpose: top 20 sales products-Author: Yan Dehui-Date: -- ============================================ ALTER Proc [dbo]. [UP_Top20Product] asbegindeclare @ yymm as nvarchar (6) -- declare @ cyymm as nvarchar (6) of the current year on April 9, January -- declare @ lyymm as nvarchar (6) of the current month) -- last month's declare @ cyymm1 as nvarchar (6) -- last month's declare @ cyymm2 as nvarchar (6) -- declare @ m as int in December February -- Cumulative month set @ yymm = datename (yyyy, getdate () + '01' set @ lyymm = convert (char (6 ), DATEADD (month,-1, getdate (), 112) set @ cyymm = convert (char (6), getdate (), 112) set @ cyymm1 = convert (char (6), DATEADD (month, 1, getdate (), 112) set @ cyymm2 = convert (char (6), DATEADD (month, 2, getdate (), 112); set @ m = DATEPART (MONTH, DATEADD (month,-1, getdate (); declare @ lmd as nvarchar (8) set @ lmd = convert (char (8), dateadd (dd,-day (getdate (), getdate (), 112); with cras (select Item, sum (invoicing in the current month) as invoicing in the current month, sum (invoicing in the next month) as invoicing in the next month, sum (invoicing in the next two months) as invoicing in the next two months from (select Item, case when [Year] + [Month] = @ cyymm then sum (Acount) else 0 end as invoicing in the current Month, case when [Year] + [Month] = @ cyymm1 then sum (Acount) else 0 end as, which will be invoiced on August 1, January, case when [Year] + [Month] = @ cyymm2 then sum (Acount) else 0 end as invoicing in the next two months -- @ cyym2 from UF_SalePlanning where left (BillDate, 6) in (@ cyymm, @ cyymm1, @ cyymm2) group by Item, Year, Month) as Cgroup by C. item), cr1as (select TH004, SUM (TH008) AS Quantities, SUM (TH037) AS Amount from coptg inner join copth on TG001 = TH001 AND TG002 = TH002 where LEFT (TG003, 6) = @ cyymm AND TH020 = 'y' group by TH004) select MB002 as product,. TH004 as product number,. cumulative quantity of Quantities,. cumulative Amount of Amount,. quantities/@ m as average quantity --, isnull (B. LB003, 0) as inventory last month, [dbo]. [GetDateLineQty] (. TH004, @ lmd) as inventory in the previous month, cr1.Quantities as quantity this month, cr1.Amount as amount this month, invoicing in the current month, invoicing in the next month, and invoicing in the next two months from (select top 20 TH004, SUM (TH008) AS Quantities, SUM (TH037) AS Amount from coptg inner join copth on TG001 = TH001 AND TG002 = TH002 where LEFT (TG003, 6)> = @ yymm and LEFT (TG003, 6) <= @ lyymm AND TH020 = 'y' group by TH004order by SUM (TH037) desc) A left join (select LB001, LB003 from INVLB where LB002 = @ cyymm) as B on B. LB001 =. TH004 left join INVMB on. TH004 = MB001 left join cr on. TH004 = cr. item left join cr1 on. TH004 = cr1.TH004order by. amount descend