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

Posted: December 19, 2012 | Author: | Filed under: SQL Server 2008, Uncategorized | Leave a comment

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'

Create Calendar Table with An Online Date Time Dimension Generator Tool

Posted: December 19, 2012 | Author: | Filed under: Uncategorized | Leave a comment

An auxiliary calendar table is very handy to work with date time related queries. You can use an online tool to configure the table data format you want.
Here is the link to the tool to generate maximum 20 years data in one batch. You can combine two batch files to generate data from year 1990 to 2033.
http://www.regnecentralen.dk/time_dimension_generator.html

Tools From Erland Sommarskog: sp_sqltrace and beta_lockinfo

Posted: December 10, 2012 | Author: | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

You can simply to run this to get a resultset from sp_sqltrace:
EXEC sp_sqltrace ‘EXEC yourSP pVal’

Download link for these Tools from Erland Sommarskog, SQL Server MVP:

”
beta_lockinfo – http://www.sommarskog.se/sqlutil/beta_lockinfo.html
this will give you a snapshot of what is going on right, including current stored procedure and statement. You need to have at least VIEW SERVER STATE to run it.

sp_sqltrace – http://www.sommarskog.se/sqlutil/sqltrace.html
This tool permits you to run a trace filtered for a certain process for a while (default 10 seconds) and then summarises the data. You need the same permissions as to run Profiler, but everything is executed server-side.
“