populate missing dates and add 1 to column

  • 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 🙂

  • 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/

  • 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?

  • 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

  • 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/

  • 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.

  • 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

  • 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.

  • 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.

  • 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

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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;

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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