Fill NULL Value From Previous Non-null Row with Running Total


Here is the original question:
I have included my solution:

--Sample Tables with data
if object_id('MyTempSales') is not null drop table MyTempSales
if object_id('MyTempInvoice') is not null drop table MyTempInvoice
CREATE TABLE MyTempSales(
	[OcrName] [nvarchar](30) NULL,
	[FirmName] [nvarchar](30) NULL,
	[ItmsGrpNam] [nvarchar](20) NOT NULL,
	[DocDate] [datetime] NULL,
	[LineTotal] [numeric](19, 6) NULL,
)

CREATE TABLE MyTempInvoice(
	[OcrName] [nvarchar](30) NULL,
	[FirmName] [nvarchar](30) NULL,
	[ItmsGrpNam] [nvarchar](20) NOT NULL,
	[DocDate] [datetime] NULL,
	[LineTotal] [numeric](19, 6) NULL,
)

insert into MyTempSales values ('123','Dell','Items','2007-04-01 00:00:00.000',6000.000000)
insert into MyTempSales values ('123','Dell','Items','2007-04-02 00:00:00.000',3750.000000)
insert into MyTempSales values ('123','Dell','Items','2007-04-03 00:00:00.000',500.000000)
insert into MyTempSales values ('123','Dell','Items','2007-04-10 00:00:00.000',500.000000)
insert into MyTempSales values ('123','Dell','Items','2007-04-12 00:00:00.000',3750.000000)
insert into MyTempSales values ('123','Dell','Items','2007-04-17 00:00:00.000',1700.000000)
insert into MyTempSales values ('123','Dell','Items','2007-04-29 00:00:00.000',200.000000)
insert into MyTempSales values ('123','Dell','Items','2007-04-30 00:00:00.000',5000.000000)

insert into MyTempSales values ('123','Dell','PCs','2007-04-05 00:00:00.000',300.000000)
insert into MyTempSales values ('123','Dell','PCs','2007-04-08 00:00:00.000',400.000000)
insert into MyTempSales values ('123','Dell','PCs','2007-04-19 00:00:00.000',600.000000)
insert into MyTempSales values ('123','Dell','PCs','2007-04-21 00:00:00.000',700.000000)
insert into MyTempSales values ('123','Dell','PCs','2007-04-29 00:00:00.000',3000.000000)

insert into MyTempSales values ('Division','Dell','Items','2007-04-07 00:00:00.000',6000.000000)
insert into MyTempSales values ('Division','Dell','Items','2007-04-18 00:00:00.000',1050.000000)
insert into MyTempSales values ('Division','Dell','Items','2007-04-26 00:00:00.000',9000.000000)




insert into MyTempInvoice values ('123','Dell','Items','2007-04-15 00:00:00.000',5000.000000)
insert into MyTempInvoice values ('123','Dell','Items','2007-04-16 00:00:00.000',3000.000000)
insert into MyTempInvoice values ('123','Dell','Items','2007-04-17 00:00:00.000',250.500000)
insert into MyTempInvoice values ('123','Dell','Items','2007-04-29 00:00:00.000',600.000000)
insert into MyTempInvoice values ('123','Dell','Items','2007-04-30 00:00:00.000',3000.000000)


insert into MyTempInvoice values ('123','Dell','PCs','2007-04-14 00:00:00.000',2000.000000)
insert into MyTempInvoice values ('123','Dell','PCs','2007-04-18 00:00:00.000',1000.000000)
insert into MyTempInvoice values ('123','Dell','PCs','2007-04-23 00:00:00.000',5000.000000)
insert into MyTempInvoice values ('123','Dell','PCs','2007-04-24 00:00:00.000',600.000000)
insert into MyTempInvoice values ('123','Dell','PCs','2007-04-26 00:00:00.000',2500.000000)

insert into MyTempInvoice values ('Division','Dell','Items','2007-04-17 00:00:00.000',6000.000000)
insert into MyTempInvoice values ('Division','Dell','Items','2007-04-25 00:00:00.000',1050.000000)
insert into MyTempInvoice values ('Division','Dell','Items','2007-04-28 00:00:00.000',9000.000000)

CREATE PROCEDURE usp_Sample_Backlog 
@FromDate Datetime, @ToDate Datetime
AS
BEGIN

-- YOUR LOGIC

;with myCalendar as
(SELECT @FromDate as dt
union all
SELECT dateadd(day,1,dt) from myCalendar WHERE dt<@ToDate)
,mycte as (
SELECT  c.OcrName, s.FirmName, c.ItmsGrpNam, c.dt, s.DocDate, ISNULL(s.LineTotal,0) as LineTotal1, ISNULL(i.LineTotal,0)  as LineTotal2 ,  row_number()over( order by  c.OcrName, c.ItmsGrpNam , c.dt ) rn
from (SELECT distinct OcrName, ItmsGrpNam, dt FROM MyTempSales, myCalendar) c Left join MyTempSales s On c.dt=s.[DocDate] AND c.OcrName=s.OcrName ANd c.ItmsGrpNam=s.ItmsGrpNam
 Left join MyTempInvoice i On c.dt=i.[DocDate] AND c.OcrName=i.OcrName ANd c.ItmsGrpNam=i.ItmsGrpNam)

 ,mycte1 as
 (SELECT OcrName, d.FirmName, ItmsGrpNam,dt,LineTotal1,LineTotal2 FROM mycte m
OUTER APPLY (SELECT TOP 1 FirmName FROM mycte
WHERE rn<= m.rn AND FirmName IS NOT NULL
ORDER BY rn desc) d
)
 

 SELECT  t1.OcrName, t1.FirmName,  t1.ItmsGrpNam, t1.dt as DocDate
 , SUM(t2.LineTotal1) as Sales_Amt, SUM(t2.LineTotal2) as Invoice_Amt, (SUM(t2.LineTotal1)-SUM(t2.LineTotal2))  as B_Amount
 FROM mycte1 t1 
 inner join mycte1 t2 on t1.dt>=t2.dt AND t1.OcrName=t2.OcrName ANd t1.ItmsGrpNam=t2.ItmsGrpNam 
 Group by   t1.OcrName, t1.FirmName, t1.ItmsGrpNam, t1.dt, t1.LineTotal1,  t1.LineTotal2
 Order by   t1.OcrName, t1.ItmsGrpNam, t1.dt
 

End
--Test
exec usp_Sample_Backlog '2007-04-01','2007-04-30'

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s