September 1, 2014 at 9:44 am
Hi,
I would like to populate missing rows in a date sequence (per palstkid group) for a table and increment another column (daysinstock) for each missing date based on the minimum date for each palstkid
e.g. palstkid 101 needs to have a row for missing dates 2014-05-05 to 2014-05-09, incrementing daysinstock by 1 for each day (resulting in a daysinstock value for 2014-05-09 of 6)
Here is code to create sample table and data:
F OBJECT_ID('TempDB..#palletstockhistory','U') IS NOT NULL
DROP TABLE #palletstockhistory
CREATE TABLE [dbo].[#palletstockhistory](
[pId] [int] IDENTITY(1,1) NOT NULL,
[extractdate] [date] NULL,
[palstkid] [int] NULL,
[uomrecqty] [int] NULL,
[qty] [int] NULL,
[quantityRunningTotal] [int] NULL,
[daysinstock] [int] NULL,
CONSTRAINT [PK_palletstockhistory_pId] PRIMARY KEY NONCLUSTERED
(
[pId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT #palletstockhistory ON
INSERT INTO #palletstockhistory
(pId, extractdate, palstkid, uomrecqty, qty, quantityRunningTotal, daysinstock)
SELECT '5853','2014-05-04','101','76','0','0','1' UNION ALL
SELECT '16','2014-05-10','101','76','8','8','7' UNION ALL
SELECT '24','2014-05-11','101','76','9','17','8' UNION ALL
SELECT '26','2014-05-12','101','76','10','27','9' UNION ALL
SELECT '33','2014-05-13','101','76','6','33','10' UNION ALL
SELECT '42','2014-05-14','101','76','6','39','11' UNION ALL
SELECT '53','2014-05-15','101','76','5','44','12' UNION ALL
SELECT '65','2014-05-16','101','76','16','60','13' UNION ALL
SELECT '91','2014-05-19','101','76','5','65','16' UNION ALL
SELECT '92','2014-05-19','101','76','11','76','16' UNION ALL
SELECT '5854','2014-05-04','102','60','0','0','1' UNION ALL
SELECT '43','2014-05-14','102','60','49','49','11' UNION ALL
SELECT '71','2014-05-17','102','60','8','57','14' UNION ALL
SELECT '78','2014-05-18','102','60','3','60','15' UNION ALL
SELECT '5855','2014-05-04','103','60','0','0','1' UNION ALL
SELECT '20','2014-05-10','103','60','60','60','7' UNION ALL
SELECT '5856','2014-05-04','104','60','0','0','1' UNION ALL
SELECT '19','2014-05-10','104','60','60','60','7' UNION ALL
SELECT '5857','2014-05-04','105','60','0','0','1' UNION ALL
SELECT '18','2014-05-10','105','60','60','60','7' UNION ALL
SELECT '5858','2014-05-04','106','60','0','0','1' UNION ALL
SELECT '21','2014-05-11','106','60','60','60','8' UNION ALL
SELECT '5859','2014-05-04','107','60','0','0','1' UNION ALL
SELECT '17','2014-05-10','107','60','60','60','7' UNION ALL
SELECT '5860','2014-05-04','108','60','0','0','1' UNION ALL
SELECT '22','2014-05-11','108','60','60','60','8' UNION ALL
SELECT '5861','2014-05-04','109','36','0','0','1' UNION ALL
SELECT '46','2014-05-14','109','36','36','36','11' UNION ALL
SELECT '5862','2014-05-04','110','60','0','0','1' UNION ALL
SELECT '45','2014-05-14','110','60','59','59','11' UNION ALL
SELECT '50','2014-05-14','110','60','1','60','11' UNION ALL
SELECT '5863','2014-05-04','114','24','0','0','1' UNION ALL
SELECT '58','2014-05-15','114','24','24','24','12' UNION ALL
SELECT '5864','2014-05-04','115','55','0','0','1' UNION ALL
SELECT '57','2014-05-15','115','55','54','54','12' UNION ALL
SELECT '64','2014-05-15','115','55','1','55','12' UNION ALL
SELECT '5865','2014-05-04','124','50','0','0','1' UNION ALL
SELECT '79','2014-05-18','124','50','12','12','15' UNION ALL
SELECT '88','2014-05-18','124','50','35','47','15' UNION ALL
SELECT '89','2014-05-18','124','50','3','50','15' UNION ALL
SELECT '5866','2014-05-04','126','20','0','0','1' UNION ALL
SELECT '77','2014-05-18','126','20','13','13','15' UNION ALL
SELECT '90','2014-05-19','126','20','7','20','16' UNION ALL
SELECT '5867','2014-05-04','127','55','0','0','1' UNION ALL
SELECT '31','2014-05-12','127','55','54','54','9' UNION ALL
SELECT '38','2014-05-13','127','55','1','55','10' UNION ALL
SELECT '5868','2014-05-04','128','55','0','0','1' UNION ALL
SELECT '32','2014-05-12','128','55','54','54','9' UNION ALL
SELECT '39','2014-05-13','128','55','1','55','10' UNION ALL
SELECT '5869','2014-05-04','129','55','0','0','1' UNION ALL
SELECT '37','2014-05-13','129','55','55','55','10' UNION ALL
SELECT '5882','2014-05-11','168','55','0','0','1' UNION ALL
SELECT '84','2014-05-18','168','55','54','54','8' UNION ALL
SELECT '85','2014-05-18','168','55','1','55','8' UNION ALL
SELECT '5883','2014-05-11','169','55','0','0','1' UNION ALL
SELECT '83','2014-05-18','169','55','54','54','8' UNION ALL
SELECT '86','2014-05-18','169','55','1','55','8' UNION ALL
SELECT '5884','2014-05-11','170','55','0','0','1' UNION ALL
SELECT '81','2014-05-18','170','55','54','54','8' UNION ALL
SELECT '87','2014-05-18','170','55','1','55','8' UNION ALL
SELECT '5885','2014-05-11','171','55','0','0','1' UNION ALL
SELECT '82','2014-05-18','171','55','55','55','8' UNION ALL
SELECT '5886','2014-05-12','172','24','0','0','1' UNION ALL
SELECT '59','2014-05-15','172','24','24','24','4' UNION ALL
SELECT '5887','2014-05-12','173','60','0','0','1' UNION ALL
SELECT '60','2014-05-15','173','60','17','17','4' UNION ALL
SELECT '72','2014-05-17','173','60','43','60','6' UNION ALL
SELECT '5888','2014-05-12','174','60','0','0','1' UNION ALL
SELECT '68','2014-05-16','174','60','16','16','5' UNION ALL
SELECT '76','2014-05-17','174','60','44','60','6' UNION ALL
SELECT '5889','2014-05-12','175','60','0','0','1' UNION ALL
SELECT '73','2014-05-17','175','60','60','60','6' UNION ALL
SELECT '5890','2014-05-12','176','60','0','0','1' UNION ALL
SELECT '74','2014-05-17','176','60','60','60','6' UNION ALL
SELECT '5891','2014-05-12','177','60','0','0','1' UNION ALL
SELECT '75','2014-05-17','177','60','60','60','6' UNION ALL
SELECT '5892','2014-05-12','178','36','0','0','1' UNION ALL
SELECT '80','2014-05-18','178','36','36','36','7'
SET IDENTITY_INSERT #palletstockhistory OFF
Any help greatly appreciated 🙂
September 2, 2014 at 2:23 am
There is a very good chance that there is a better way of doing it, but here is one way:
declare @MinDate datetime
declare @MaxDate datetime
--Getting the first and last dates in the table
select @MinDate = min(extractdate), @MaxDate = max(extractdate) from #palletstockhistory;
--CTE that will generate all dates that should be in the table
with DatesList as (
select @MinDate as d
union all
select dateadd(day,1,d)
from DatesList
where d < @MaxDate),
--CTE that will let me know the relevent dates for each palstkid
DatesPerPalstkid as (
select palstkid, 1 as dayinstock, min(extractdate) as MinDate, max(extractdate) as MaxDate
from #palletstockhistory
group by palstkid),
--Getting all the data including the one that exists in the table
AllData as (
select DPP.palstkid, d, row_number() over (partition by DPP.palstkid order by d) + 1 as DaysInStock
from DatesList DL inner join DatesPerPalstkid DPP on DL.d>DPP.MinDate and DL.d < DPP.MaxDate)
--Insert only relevent data
insert into #palletstockhistory(palstkid, extractdate, daysinstock)
select AD.palstkid, AD.d, AD.DaysInStock
from AllData AD left join #palletstockhistory p on p.palstkid = AD.palstkid and p.extractdate = AD.d
where p.extractdate is null
--See what we got
select * from #palletstockhistory order by palstkid, extractdate
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2014 at 2:52 am
Thanks, that works great on my sample data, but when I try it against the real table with more rows it returns the following error:
The maximum recursion 100 has been exhausted before statement completion.
Any ideas how to overcome this?
September 2, 2014 at 3:19 am
phingers (9/2/2014)
Thanks, that works great on my sample data, but when I try it against the real table with more rows it returns the following error:The maximum recursion 100 has been exhausted before statement completion.
Any ideas how to overcome this?
Here is a solution that uses Tally table instead of recursion
😎
USE tempdb;
GO
IF OBJECT_ID('TempDB..#palletstockhistory','U') IS NOT NULL
DROP TABLE #palletstockhistory
CREATE TABLE [dbo].[#palletstockhistory](
[pId] [int] IDENTITY(1,1) NOT NULL,
[extractdate] [date] NULL,
[palstkid] [int] NULL,
[uomrecqty] [int] NULL,
[qty] [int] NULL,
[quantityRunningTotal] [int] NULL,
[daysinstock] [int] NULL,
CONSTRAINT [PK_palletstockhistory_pId] PRIMARY KEY NONCLUSTERED
(
[pId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT #palletstockhistory ON
INSERT INTO #palletstockhistory
(pId, extractdate, palstkid, uomrecqty, qty, quantityRunningTotal, daysinstock)
SELECT '5853','2014-05-04','101','76','0','0','1' UNION ALL
SELECT '16','2014-05-10','101','76','8','8','7' UNION ALL
SELECT '24','2014-05-11','101','76','9','17','8' UNION ALL
SELECT '26','2014-05-12','101','76','10','27','9' UNION ALL
SELECT '33','2014-05-13','101','76','6','33','10' UNION ALL
SELECT '42','2014-05-14','101','76','6','39','11' UNION ALL
SELECT '53','2014-05-15','101','76','5','44','12' UNION ALL
SELECT '65','2014-05-16','101','76','16','60','13' UNION ALL
SELECT '91','2014-05-19','101','76','5','65','16' UNION ALL
SELECT '92','2014-05-19','101','76','11','76','16' UNION ALL
SELECT '5854','2014-05-04','102','60','0','0','1' UNION ALL
SELECT '43','2014-05-14','102','60','49','49','11' UNION ALL
SELECT '71','2014-05-17','102','60','8','57','14' UNION ALL
SELECT '78','2014-05-18','102','60','3','60','15' UNION ALL
SELECT '5855','2014-05-04','103','60','0','0','1' UNION ALL
SELECT '20','2014-05-10','103','60','60','60','7' UNION ALL
SELECT '5856','2014-05-04','104','60','0','0','1' UNION ALL
SELECT '19','2014-05-10','104','60','60','60','7' UNION ALL
SELECT '5857','2014-05-04','105','60','0','0','1' UNION ALL
SELECT '18','2014-05-10','105','60','60','60','7' UNION ALL
SELECT '5858','2014-05-04','106','60','0','0','1' UNION ALL
SELECT '21','2014-05-11','106','60','60','60','8' UNION ALL
SELECT '5859','2014-05-04','107','60','0','0','1' UNION ALL
SELECT '17','2014-05-10','107','60','60','60','7' UNION ALL
SELECT '5860','2014-05-04','108','60','0','0','1' UNION ALL
SELECT '22','2014-05-11','108','60','60','60','8' UNION ALL
SELECT '5861','2014-05-04','109','36','0','0','1' UNION ALL
SELECT '46','2014-05-14','109','36','36','36','11' UNION ALL
SELECT '5862','2014-05-04','110','60','0','0','1' UNION ALL
SELECT '45','2014-05-14','110','60','59','59','11' UNION ALL
SELECT '50','2014-05-14','110','60','1','60','11' UNION ALL
SELECT '5863','2014-05-04','114','24','0','0','1' UNION ALL
SELECT '58','2014-05-15','114','24','24','24','12' UNION ALL
SELECT '5864','2014-05-04','115','55','0','0','1' UNION ALL
SELECT '57','2014-05-15','115','55','54','54','12' UNION ALL
SELECT '64','2014-05-15','115','55','1','55','12' UNION ALL
SELECT '5865','2014-05-04','124','50','0','0','1' UNION ALL
SELECT '79','2014-05-18','124','50','12','12','15' UNION ALL
SELECT '88','2014-05-18','124','50','35','47','15' UNION ALL
SELECT '89','2014-05-18','124','50','3','50','15' UNION ALL
SELECT '5866','2014-05-04','126','20','0','0','1' UNION ALL
SELECT '77','2014-05-18','126','20','13','13','15' UNION ALL
SELECT '90','2014-05-19','126','20','7','20','16' UNION ALL
SELECT '5867','2014-05-04','127','55','0','0','1' UNION ALL
SELECT '31','2014-05-12','127','55','54','54','9' UNION ALL
SELECT '38','2014-05-13','127','55','1','55','10' UNION ALL
SELECT '5868','2014-05-04','128','55','0','0','1' UNION ALL
SELECT '32','2014-05-12','128','55','54','54','9' UNION ALL
SELECT '39','2014-05-13','128','55','1','55','10' UNION ALL
SELECT '5869','2014-05-04','129','55','0','0','1' UNION ALL
SELECT '37','2014-05-13','129','55','55','55','10' UNION ALL
SELECT '5882','2014-05-11','168','55','0','0','1' UNION ALL
SELECT '84','2014-05-18','168','55','54','54','8' UNION ALL
SELECT '85','2014-05-18','168','55','1','55','8' UNION ALL
SELECT '5883','2014-05-11','169','55','0','0','1' UNION ALL
SELECT '83','2014-05-18','169','55','54','54','8' UNION ALL
SELECT '86','2014-05-18','169','55','1','55','8' UNION ALL
SELECT '5884','2014-05-11','170','55','0','0','1' UNION ALL
SELECT '81','2014-05-18','170','55','54','54','8' UNION ALL
SELECT '87','2014-05-18','170','55','1','55','8' UNION ALL
SELECT '5885','2014-05-11','171','55','0','0','1' UNION ALL
SELECT '82','2014-05-18','171','55','55','55','8' UNION ALL
SELECT '5886','2014-05-12','172','24','0','0','1' UNION ALL
SELECT '59','2014-05-15','172','24','24','24','4' UNION ALL
SELECT '5887','2014-05-12','173','60','0','0','1' UNION ALL
SELECT '60','2014-05-15','173','60','17','17','4' UNION ALL
SELECT '72','2014-05-17','173','60','43','60','6' UNION ALL
SELECT '5888','2014-05-12','174','60','0','0','1' UNION ALL
SELECT '68','2014-05-16','174','60','16','16','5' UNION ALL
SELECT '76','2014-05-17','174','60','44','60','6' UNION ALL
SELECT '5889','2014-05-12','175','60','0','0','1' UNION ALL
SELECT '73','2014-05-17','175','60','60','60','6' UNION ALL
SELECT '5890','2014-05-12','176','60','0','0','1' UNION ALL
SELECT '74','2014-05-17','176','60','60','60','6' UNION ALL
SELECT '5891','2014-05-12','177','60','0','0','1' UNION ALL
SELECT '75','2014-05-17','177','60','60','60','6' UNION ALL
SELECT '5892','2014-05-12','178','36','0','0','1' UNION ALL
SELECT '80','2014-05-18','178','36','36','36','7'
SET IDENTITY_INSERT #palletstockhistory OFF
;WITH PH_DATERANCE AS
(
SELECT
PH.palstkid
,MIN(PH.extractdate) AS DATE_FROM
,DATEDIFF(DAY,MIN(PH.extractdate),MAX(PH.extractdate)) AS NUM_DAYS
FROM #palletstockhistory PH
GROUP BY PH.palstkid
)
,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,PH_DATE_LIST AS
(
SELECT
PD.palstkid
,(NM.N + 1) AS P_DAYS_IN_STOCK
,DATEADD(DAY,NM.N,PD.DATE_FROM) AS PH_DATE
FROM PH_DATERANCE PD
CROSS APPLY
(SELECT TOP(NUM_DAYS + 1) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM(N)
)
SELECT
PDL.palstkid
,PDL.PH_DATE
,PH.pId
,PH.uomrecqty
,PH.qty
,PH.quantityRunningTotal
,ISNULL(PH.daysinstock,PDL.P_DAYS_IN_STOCK) AS daysinstock
FROM PH_DATE_LIST PDL
LEFT OUTER JOIN #palletstockhistory PH
ON PDL.palstkid = PH.palstkid
AND PDL.PH_DATE = PH.extractdate;
Top few Results
palstkid PH_DATE pId uomrecqty qty quantityRunningTotal daysinstock
----------- ---------- ----------- ----------- ----------- -------------------- -----------
101 2014-05-04 5853 76 0 0 1
101 2014-05-05 NULL NULL NULL NULL 2
101 2014-05-06 NULL NULL NULL NULL 3
101 2014-05-07 NULL NULL NULL NULL 4
101 2014-05-08 NULL NULL NULL NULL 5
101 2014-05-09 NULL NULL NULL NULL 6
101 2014-05-10 16 76 8 8 7
101 2014-05-11 24 76 9 17 8
101 2014-05-12 26 76 10 27 9
101 2014-05-13 33 76 6 33 10
101 2014-05-14 42 76 6 39 11
101 2014-05-15 53 76 5 44 12
101 2014-05-16 65 76 16 60 13
101 2014-05-17 NULL NULL NULL NULL 14
101 2014-05-18 NULL NULL NULL NULL 15
101 2014-05-19 91 76 5 65 16
101 2014-05-19 92 76 11 76 16
102 2014-05-04 5854 60 0 0 1
102 2014-05-05 NULL NULL NULL NULL 2
102 2014-05-06 NULL NULL NULL NULL 3
102 2014-05-07 NULL NULL NULL NULL 4
102 2014-05-08 NULL NULL NULL NULL 5
102 2014-05-09 NULL NULL NULL NULL 6
102 2014-05-10 NULL NULL NULL NULL 7
102 2014-05-11 NULL NULL NULL NULL 8
102 2014-05-12 NULL NULL NULL NULL 9
102 2014-05-13 NULL NULL NULL NULL 10
102 2014-05-14 43 60 49 49 11
102 2014-05-15 NULL NULL NULL NULL 12
102 2014-05-16 NULL NULL NULL NULL 13
102 2014-05-17 71 60 8 57 14
102 2014-05-18 78 60 3 60 15
September 2, 2014 at 4:44 am
phingers (9/2/2014)
Thanks, that works great on my sample data, but when I try it against the real table with more rows it returns the following error:The maximum recursion 100 has been exhausted before statement completion.
Any ideas how to overcome this?
You can modify the allowed number of iterations by adding the line option OPTION (MAXRECURSION XXX) as the last line for the insert statement. Instead of XXX you should put the number of iterations that you think will be sufficient. If you'll write 0, then there will be no limit to the number of iterations.
Off curse you can use Eirikur Eiriksson's way. I have to admit that I don't understand it yet, and I'll have to take a closer look at it, but it might be more efficient then my way.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2014 at 8:53 am
Thanks for all your help guys, I marked Adi Cohn's as the solution because that's what I used in the end, but the other solution does also achieve the goal.
September 2, 2014 at 8:59 am
phingers (9/2/2014)
Thanks for all your help guys, I marked Adi Cohn's as the solution because that's what I used in the end, but the other solution does also achieve the goal.
out of interest...now that you have a solution, what do you intend to do with the resultset?
is this for some type of report?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 2, 2014 at 10:17 am
Yes, the data will be used to calculate storage charges for pallets based on the number of days the pallets have been in stock. I have a stored procedure to extract the stock information daily and write to the table and I also got the historical data but needed to fill in the 'gaps' so that if the report is run for any date range the number of days the pallet has been in stock is correct, regardless of whether it has been just received, partially sold or fully sold.
September 2, 2014 at 10:29 am
Quick thought, if the workset is large, I would recommend the Tally table version, see the IO statistics below
😎
Tally table
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#palletstockhistory'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Recursive
Table '#palletstockhistory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#palletstockhistory'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 30, logical reads 186, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#palletstockhistory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
September 2, 2014 at 2:47 pm
phingers (9/2/2014)
Yes, the data will be used to calculate storage charges for pallets based on the number of days the pallets have been in stock. I have a stored procedure to extract the stock information daily and write to the table and I also got the historical data but needed to fill in the 'gaps' so that if the report is run for any date range the number of days the pallet has been in stock is correct, regardless of whether it has been just received, partially sold or fully sold.
just a thought.....may be useful?
DECLARE @date_start datetime;
DECLARE @date_end datetime;
SET @date_start = '20140510';
SET @date_end = '20140517';
SELECT
palstkid
, MIN(extractdate) minday
, MAX(extractdate) maxday
, DATEDIFF(day , CASE
WHEN MIN(extractdate) < @date_start THEN @date_start
ELSE MIN(extractdate)
END ,
CASE
WHEN MAX(extractdate) > @date_end THEN @date_end
ELSE MAX(extractdate)
END) AS daystobill
FROM #palletstockhistory
GROUP BY
palstkid
HAVING (MIN(extractdate) <= @date_end)
AND (MAX(extractdate) >= @date_start)
AND (DATEDIFF(day , CASE
WHEN MIN(extractdate) < @date_start THEN @date_start
ELSE MIN(extractdate)
END , CASE
WHEN MAX(extractdate) > @date_end THEN @date_end
ELSE MAX(extractdate)
END) > 0);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 2, 2014 at 7:20 pm
I would be curious how this solution stacks up performance-wise to the others, although I don't have time at the moment to check.
WITH FancyWayToFindGaps AS
(
SELECT palstkid, extractdate=DATEADD(day, -1, MIN(extractdate))
,[days]=1+DATEDIFF(day, MIN(extractdate), MAX(extractdate))
FROM
(
SELECT palstkid, extractdate
,grp=ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)/2
FROM
(
SELECT palstkid
,gapstart=DATEADD(day, 1, MAX(extractdate))
,gapend=DATEADD(day, -1, MIN(extractdate))
FROM
(
SELECT palstkid, extractdate, grp, rn
FROM
(
SELECT palstkid, extractdate
,grp=CAST(extractdate AS DATETIME)-ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)
,rn=ROW_NUMBER() OVER (PARTITION BY palstkid, extractdate ORDER BY extractdate)
FROM #palletstockhistory
) a
WHERE rn=1
) a
GROUP BY palstkid, grp
) a
CROSS APPLY
(
VALUES (gapstart),(gapend)
) b (extractdate)
) a
GROUP BY palstkid, grp
HAVING COUNT(*) = 2
)
SELECT b.pid, extractdate=DATEADD(day, n, a.extractdate), a.palstkid, b.uomrecqty, b.qty
,b.quantityRunningTotal, daysinstock=daysinstock + n
FROM FancyWayToFindGaps a
JOIN #palletstockhistory b ON a.palstkid = b.palstkid AND a.extractdate = b.extractdate
CROSS APPLY
(
SELECT TOP (a.[days]) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
) Tally (n)
UNION ALL
SELECT *
FROM #palletstockhistory;
IO counts:
Table '#palletstockhistory. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
This uses a rather obscure technique to find islands in the dates, convert those to gaps and then use a Tally table to expand that set out to the rows required to fill in the gaps. Despite its relative obscurity, it does pretty well performance-wise in finding gaps.
The SQL of Gaps and Islands in Sequences [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 2, 2014 at 7:41 pm
There is also this variant that, by using a LEFT JOIN instead of the UNION ALL, reduces the scan count to 2.
WITH FancyWayToFindGaps AS
(
SELECT palstkid, extractdate=DATEADD(day, -1, MIN(extractdate))
,[days]=1+DATEDIFF(day, MIN(extractdate), MAX(extractdate))
FROM
(
SELECT palstkid, extractdate
,grp=ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)/2
FROM
(
SELECT palstkid
,gapstart=DATEADD(day, 1, MAX(extractdate))
,gapend=DATEADD(day, -1, MIN(extractdate))
FROM
(
SELECT palstkid, extractdate, grp, rn
FROM
(
SELECT palstkid, extractdate
,grp=CAST(extractdate AS DATETIME)-ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)
,rn=ROW_NUMBER() OVER (PARTITION BY palstkid, extractdate ORDER BY extractdate)
FROM #palletstockhistory
) a
WHERE rn=1
) a
GROUP BY palstkid, grp
) a
CROSS APPLY
(
VALUES (gapstart),(gapend)
) b (extractdate)
) a
GROUP BY palstkid, grp
HAVING COUNT(*) = 2
)
SELECT a.pid, extractdate=ISNULL(b.extractdate1, a.extractdate), a.palstkid, a.uomrecqty, a.qty
,a.quantityRunningTotal, daysinstock=a.daysinstock + ISNULL(n, 0)
FROM #palletstockhistory a
LEFT JOIN
(
SELECT a.extractdate, extractdate1=DATEADD(day, n, a.extractdate), palstkid, n
FROM FancyWayToFindGaps a
CROSS APPLY
(
SELECT 0 UNION ALL
SELECT TOP (a.[days]) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
) Tally (n)
) b ON a.palstkid = b.palstkid AND a.extractdate = b.extractdate
ORDER BY a.palstkid, a.extractdate;
IO Count:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#palletstockhistory. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 3, 2014 at 3:39 am
Did a quick little testing, see the results, DDL, data generator and test code below. Code also attached.
Feel free to play around and improve 😉
😎
-----------------------------------------------------------------------------------
Dense set: 1000000 (10000 customers x 100 days)
Density: 2
Rows: 504173
TT_NAME TIME_START TIME_END TOTAL_TIME
-----------------------------------------------------------------------------------
Outer Apply Tally 10:01:46.5205486 10:01:47.4105995 890051
Cross Apply Tally 10:01:47.4105995 10:01:48.3016505 891051
Left Outer Tally 10:01:45.3464815 10:01:46.5205486 1174067
FancyWayToFindGaps UNION ALL 10:01:48.3016505 10:01:50.6607854 2359135
FancyWayToFindGaps LEFT JOIN 10:01:50.6607854 10:01:53.1569282 2496143
Recursion 10:01:53.1569282 10:02:29.5680108 36411082
-----------------------------------------------------------------------------------
Dense set: 1000000 (10000 customers x 100 days)
Density: 8
Rows: 808306
TT_NAME TIME_START TIME_END TOTAL_TIME
-----------------------------------------------------------------------------------
Cross Apply Tally 10:10:48.7555627 10:10:49.9906333 1235071
Outer Apply Tally 10:10:47.5034911 10:10:48.7555627 1252071
Left Outer Tally 10:10:45.5843813 10:10:47.5024910 1918110
FancyWayToFindGaps UNION ALL 10:10:49.9906333 10:10:51.9757469 1985113
FancyWayToFindGaps LEFT JOIN 10:10:51.9757469 10:10:54.0988683 2123122
Recursion 10:10:54.0988683 10:11:26.5477243 32448856
-----------------------------------------------------------------------------------
Dense set: 1000000 (10000 customers x 100 days)
Density: 10
Rows: 909185
TT_NAME TIME_START TIME_END TOTAL_TIME
-----------------------------------------------------------------------------------
Outer Apply Tally 10:18:26.8257628 10:18:28.1798402 1354078
Cross Apply Tally 10:18:28.1798402 10:18:29.5619193 1382079
FancyWayToFindGaps UNION ALL 10:18:29.5629194 10:18:31.2920182 1729099
FancyWayToFindGaps LEFT JOIN 10:18:31.2920182 10:18:33.1481244 1856106
Left Outer Tally 10:18:24.8756513 10:18:26.8257628 1950111
Recursion 10:18:33.1481244 10:19:05.6419830 32493859
-----------------------------------------------------------------------------------
Sample data and ddl
USE tempdb;
GO
/* Test Set Parameters */
/* number of days */
DECLARE @SET_SIZE INT = 100;
/* number of customers */
DECLARE @KEY_COUNT INT = 10000;
/* initial date value */
DECLARE @BASE_DAY DATETIME = '2008-01-01';
/* density, SIGN(RN mod N)
@SET_SIZE ~ 100%
10 ~ 91%
9 ~ 90%
8 ~ 88%
7 ~ 86%
6 ~ 84%
5 ~ 80%
4 ~ 75%
3 ~ 66%
2 ~ 50%
*/
DECLARE @DENSITY INT = 2;
/* Create the table if needed */
IF OBJECT_ID('dbo.palletstockhistory','U') IS NULL
BEGIN
DECLARE @INDEX_STR NVARCHAR(MAX) = N'CREATE UNIQUE NONCLUSTERED INDEX [NCLUNQ_DBO_PALLETSTOCKHISTORY_PO_NO_C] ON [dbo].[palletstockhistory]
(
[palstkid] ASC,
[extractdate] ASC
)
INCLUDE ( [pId]) WITH
( PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF
, DROP_EXISTING = OFF
, ONLINE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON)';
CREATE TABLE dbo.palletstockhistory(
[pId] [int] IDENTITY(1,1) NOT NULL,
[extractdate] [date] NULL,
[palstkid] [int] NULL,
[uomrecqty] [int] NULL,
[qty] [int] NULL,
[quantityRunningTotal] [int] NULL,
[daysinstock] [int] NULL,
CONSTRAINT [PK_palletstockhistory_pId] PRIMARY KEY NONCLUSTERED
(
[pId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
EXEC (@INDEX_STR);
END
/* Make certain the table is empty */
TRUNCATE TABLE dbo.palletstockhistory;
DECLARE @PALSTK_BASE TABLE
(
START_DAY DATE NOT NULL
,palstkid INT NOT NULL
,uomrecqty INT NOT NULL
);
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,KEYS(N) AS (SELECT TOP(@KEY_COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
INSERT INTO @PALSTK_BASE (START_DAY,palstkid,uomrecqty)
SELECT
DATEADD(DAY,KY.N+(CHECKSUM(NEWID()) % @SET_SIZE),@BASE_DAY) AS START_DAY
,KY.N AS palstkid
,ABS(CHECKSUM(NEWID()) % @SET_SIZE) AS uomrecqty
FROM KEYS KY;
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SET_SIZE + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
INSERT INTO dbo.palletstockhistory
(
extractdate
,palstkid
,uomrecqty
,qty
,quantityRunningTotal
,daysinstock
)
SELECT
X.extractdate
,X.palstkid
,X.uomrecqty
,X.qty
,X.quantityRunningTotal
,X.daysinstock
FROM
(
SELECT
DATEADD(DAY,NM.N,PB.START_DAY) AS extractdate
,PB.palstkid AS palstkid
,PB.uomrecqty AS uomrecqty
,ABS((CHECKSUM(NEWID()) % 37)) AS qty
,(PB.uomrecqty + NM.N) AS quantityRunningTotal
,NM.N + 1 AS daysinstock
,ABS(SIGN((CHECKSUM(NEWID()) % @DENSITY))) AS FILTER_FLAG
FROM @PALSTK_BASE PB
OUTER APPLY NUMS NM
) AS X
WHERE X.FILTER_FLAG = 1;
Test code
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TEST_TIMES TABLE
(
TT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,TT_TIME DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME())
,TT_NAME VARCHAR(50) NOT NULL
);
DECLARE @BUCKET_pId INT ;
DECLARE @BUCKET_extractdate DATETIME ;
DECLARE @BUCKET_palstkid INT ;
DECLARE @BUCKET_uomrecqty INT ;
DECLARE @BUCKET_qty INT ;
DECLARE @BUCKET_quantityRunningTotalINT ;
DECLARE @BUCKET_daysinstock INT ;
--PRINT '
-------------------------------------------------------------
--Left Outer Tally
-------------------------------------------------------------
--';
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('Left Outer Tally');
;WITH PH_DATERANCE AS
(
SELECT
PH.palstkid
,MIN(PH.extractdate) AS DATE_FROM
,DATEDIFF(DAY,MIN(PH.extractdate),MAX(PH.extractdate)) AS NUM_DAYS
FROM dbo.palletstockhistory PH
GROUP BY PH.palstkid
)
,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,PH_DATE_LIST AS
(
SELECT
PD.palstkid
,(NM.N + 1) AS P_DAYS_IN_STOCK
,DATEADD(DAY,NM.N,PD.DATE_FROM) AS PH_DATE
FROM PH_DATERANCE PD
CROSS APPLY
(SELECT TOP(NUM_DAYS + 1) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM(N)
)
SELECT
@BUCKET_pId = PDL.palstkid
,@BUCKET_extractdate = PDL.PH_DATE
,@BUCKET_palstkid = PH.pId
,@BUCKET_uomrecqty = PH.uomrecqty
,@BUCKET_qty = PH.qty
,@BUCKET_quantityRunningTotal = PH.quantityRunningTotal
,@BUCKET_daysinstock = ISNULL(PH.daysinstock,PDL.P_DAYS_IN_STOCK)
FROM PH_DATE_LIST PDL
left outer join dbo.palletstockhistory PH
on PDL.palstkid = PH.palstkid
AND PDL.PH_DATE = PH.extractdate
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('Left Outer Tally');
--PRINT '
-------------------------------------------------------------
--Outer Apply Tally
-------------------------------------------------------------
--';
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('Outer Apply Tally');
;WITH PH_DATERANCE AS
(
SELECT
PH.palstkid
,MIN(PH.extractdate) AS DATE_FROM
,DATEDIFF(DAY,MIN(PH.extractdate),MAX(PH.extractdate)) AS NUM_DAYS
FROM dbo.palletstockhistory PH
GROUP BY PH.palstkid
)
,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,PH_DATE_LIST AS
(
SELECT
PD.palstkid
,(NM.N + 1) AS P_DAYS_IN_STOCK
,DATEADD(DAY,NM.N,PD.DATE_FROM) AS PH_DATE
FROM PH_DATERANCE PD
CROSS APPLY
(SELECT TOP(NUM_DAYS + 1) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM(N)
)
SELECT
@BUCKET_pId = PDL.palstkid
,@BUCKET_extractdate = PDL.PH_DATE
,@BUCKET_palstkid = PH.pId
,@BUCKET_uomrecqty = PH.uomrecqty
,@BUCKET_qty = PH.qty
,@BUCKET_quantityRunningTotal = PH.quantityRunningTotal
,@BUCKET_daysinstock = ISNULL(PH.daysinstock,PDL.P_DAYS_IN_STOCK)
FROM PH_DATE_LIST PDL
OUTER APPLY dbo.palletstockhistory PH
WHERE PDL.palstkid = PH.palstkid
AND PDL.PH_DATE = PH.extractdate
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('Outer Apply Tally');
--PRINT '
-------------------------------------------------------------
--Cross Apply Tally
-------------------------------------------------------------
--';
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('Cross Apply Tally');
;WITH PH_DATERANCE AS
(
SELECT
PH.palstkid
,MIN(PH.extractdate) AS DATE_FROM
,DATEDIFF(DAY,MIN(PH.extractdate),MAX(PH.extractdate)) AS NUM_DAYS
FROM dbo.palletstockhistory PH
GROUP BY PH.palstkid
)
,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,PH_DATE_LIST AS
(
SELECT
PD.palstkid
,(NM.N + 1) AS P_DAYS_IN_STOCK
,DATEADD(DAY,NM.N,PD.DATE_FROM) AS PH_DATE
FROM PH_DATERANCE PD
CROSS APPLY
(SELECT TOP(NUM_DAYS + 1) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM(N)
)
SELECT
@BUCKET_pId = PDL.palstkid
,@BUCKET_extractdate = PDL.PH_DATE
,@BUCKET_palstkid = PH.pId
,@BUCKET_uomrecqty = PH.uomrecqty
,@BUCKET_qty = PH.qty
,@BUCKET_quantityRunningTotal = PH.quantityRunningTotal
,@BUCKET_daysinstock = ISNULL(PH.daysinstock,PDL.P_DAYS_IN_STOCK)
FROM PH_DATE_LIST PDL
CROSS APPLY dbo.palletstockhistory PH
WHERE PDL.palstkid = PH.palstkid
AND PDL.PH_DATE = PH.extractdate
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('Cross Apply Tally');
--PRINT '
-------------------------------------------------------------
--FancyWayToFindGaps
-------------------------------------------------------------
--'
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('FancyWayToFindGaps UNION ALL');
WITH FancyWayToFindGaps AS
(
SELECT palstkid, extractdate=DATEADD(day, -1, MIN(extractdate))
,[days]=1+DATEDIFF(day, MIN(extractdate), MAX(extractdate))
FROM
(
SELECT palstkid, extractdate
,grp=ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)/2
FROM
(
SELECT palstkid
,gapstart=DATEADD(day, 1, MAX(extractdate))
,gapend=DATEADD(day, -1, MIN(extractdate))
FROM
(
SELECT palstkid, extractdate, grp, rn
FROM
(
SELECT palstkid, extractdate
,grp=CAST(extractdate AS DATETIME)-ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)
,rn=ROW_NUMBER() OVER (PARTITION BY palstkid, extractdate ORDER BY extractdate)
FROM dbo.palletstockhistory
) a
WHERE rn=1
) a
GROUP BY palstkid, grp
) a
CROSS APPLY
(
VALUES (gapstart),(gapend)
) b (extractdate)
) a
GROUP BY palstkid, grp
HAVING COUNT(*) = 2
)
SELECT
@BUCKET_pId = X.palstkid
,@BUCKET_extractdate = X.extractdate
,@BUCKET_palstkid = X.pId
,@BUCKET_uomrecqty = X.uomrecqty
,@BUCKET_qty = X.qty
,@BUCKET_quantityRunningTotal = X.quantityRunningTotal
,@BUCKET_daysinstock = X.daysinstock
FROM
(
SELECT b.pid, extractdate=DATEADD(day, n, a.extractdate), a.palstkid, b.uomrecqty, b.qty
,b.quantityRunningTotal, daysinstock=daysinstock + n
FROM FancyWayToFindGaps a
JOIN dbo.palletstockhistory b ON a.palstkid = b.palstkid AND a.extractdate = b.extractdate
CROSS APPLY
(
SELECT TOP (a.[days]) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
) Tally (n)
UNION ALL
SELECT *
FROM dbo.palletstockhistory
) AS X;
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('FancyWayToFindGaps UNION ALL');
--PRINT '
-------------------------------------------------------------
--FancyWayToFindGaps LEFT JOIN
-------------------------------------------------------------
--'
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('FancyWayToFindGaps LEFT JOIN');
WITH FancyWayToFindGaps AS
(
SELECT palstkid, extractdate=DATEADD(day, -1, MIN(extractdate))
,[days]=1+DATEDIFF(day, MIN(extractdate), MAX(extractdate))
FROM
(
SELECT palstkid, extractdate
,grp=ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)/2
FROM
(
SELECT palstkid
,gapstart=DATEADD(day, 1, MAX(extractdate))
,gapend=DATEADD(day, -1, MIN(extractdate))
FROM
(
SELECT palstkid, extractdate, grp, rn
FROM
(
SELECT palstkid, extractdate
,grp=CAST(extractdate AS DATETIME)-ROW_NUMBER() OVER (PARTITION BY palstkid ORDER BY extractdate)
,rn=ROW_NUMBER() OVER (PARTITION BY palstkid, extractdate ORDER BY extractdate)
FROM dbo.palletstockhistory
) a
WHERE rn=1
) a
GROUP BY palstkid, grp
) a
CROSS APPLY
(
VALUES (gapstart),(gapend)
) b (extractdate)
) a
GROUP BY palstkid, grp
HAVING COUNT(*) = 2
)
SELECT
@BUCKET_pId = X.palstkid
,@BUCKET_extractdate = X.extractdate
,@BUCKET_palstkid = X.pId
,@BUCKET_uomrecqty = X.uomrecqty
,@BUCKET_qty = X.qty
,@BUCKET_quantityRunningTotal = X.quantityRunningTotal
,@BUCKET_daysinstock = X.daysinstock
FROM
(
SELECT a.pid, extractdate=ISNULL(b.extractdate1, a.extractdate), a.palstkid, a.uomrecqty, a.qty
,a.quantityRunningTotal, daysinstock=a.daysinstock + ISNULL(n, 0)
FROM dbo.palletstockhistory a
LEFT JOIN
(
SELECT a.extractdate, extractdate1=DATEADD(day, n, a.extractdate), palstkid, n
FROM FancyWayToFindGaps a
CROSS APPLY
(
SELECT 0 UNION ALL
SELECT TOP (a.[days]) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
) Tally (n)
) b ON a.palstkid = b.palstkid AND a.extractdate = b.extractdate
) AS X;
--ORDER BY a.palstkid, a.extractdate;
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('FancyWayToFindGaps LEFT JOIN');
--print '
-------------------------------------------------------------
--Recursion
-------------------------------------------------------------
--'
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('Recursion');
declare @MinDate datetime
declare @MaxDate datetime
--Getting the first and last dates in the table
select @MinDate = min(extractdate), @MaxDate = max(extractdate) from dbo.palletstockhistory;
--CTE that will generate all dates that should be in the table
with DatesList as (
select @MinDate as d
union all
select dateadd(day,1,d)
from DatesList
where d < @MaxDate),
--CTE that will let me know the relevent dates for each palstkid
DatesPerPalstkid as (
select palstkid, 1 as dayinstock, min(extractdate) as MinDate, max(extractdate) as MaxDate
from dbo.palletstockhistory
group by palstkid),
--Getting all the data including the one that exists in the table
AllData as (
select DPP.palstkid, d, row_number() over (partition by DPP.palstkid order by d) + 1 as DaysInStock
from DatesList DL inner join DatesPerPalstkid DPP on DL.d>DPP.MinDate and DL.d < DPP.MaxDate)
--Insert only relevent data
insert into dbo.palletstockhistory(palstkid, extractdate, daysinstock)
select AD.palstkid, AD.d, AD.DaysInStock
from AllData AD left join dbo.palletstockhistory p on p.palstkid = AD.palstkid and p.extractdate = AD.d
where p.extractdate is null
OPTION (MAXRECURSION 0)
--See what we got
select
@BUCKET_pId = X.palstkid
,@BUCKET_extractdate = X.extractdate
,@BUCKET_palstkid = X.pId
,@BUCKET_uomrecqty = X.uomrecqty
,@BUCKET_qty = X.qty
,@BUCKET_quantityRunningTotal = X.quantityRunningTotal
,@BUCKET_daysinstock = X.daysinstock
from dbo.palletstockhistory X
INSERT INTO @TEST_TIMES (TT_NAME) VALUES('Recursion');
SELECT
TT.TT_NAME
,MIN(TT.TT_TIME) AS TIME_START
,MAX(TT.TT_TIME) AS TIME_END
,DATEDIFF(MICROSECOND,MIN(TT.TT_TIME),MAX(TT.TT_TIME)) AS TOTAL_TIME
FROM @TEST_TIMES TT
GROUP BY TT.TT_NAME
ORDER BY 4;
September 3, 2014 at 6:11 pm
Erikur,
Great work with that test harness, although I'd say your statement about "doing a little testing" is a bit of an understatement.
Even though the results didn't come out in my favor, it is good to see them to clear the air. Ran them myself this morning. Mostly I wanted to see if you'd created an INDEX on palstkid and date, which you did.
I got similar results.
Again, excellent work!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply