Dealing with periods and dates without using cursors

Question

I would like to solve this issue avoiding to use cursors (FETCH).

Here comes the problem...

1st Table/quantity
------------------
periodid periodstart periodend quantity

1        2010/10/01 2010/10/15    5


2st Table/sold items
-----------------------
periodid periodstart periodend solditems

14343    2010/10/05 2010/10/06    2

Now I would like to get the following view or just query result

Table Table/stock
-----------------------
periodstart periodend itemsinstock

2010/10/01 2010/10/04      5

2010/10/05 2010/10/06      3

2010/10/07 2010/10/15      5

It seems impossible to solve this problem without using cursors, or without using single dates instead of periods.

I would appreciate any help.

Thanks

Answer 1

John, what you could do is a WHILE loop. Declare and initialise 2 variables before your loop, one being the start date and the other being end date. Your loop would then look like this:

WHILE(@StartEnd <= @EndDate)
BEGIN
  --processing goes here
  SET @StartEnd = @StartEnd + 1
END

You would need to store your period definitions in another table, so you could retrieve those and output rows when required to a temporary table.

Let me know if you need any more detailed examples, or if I've got the wrong end of the stick!

Answer 2

DECLARE @t1 TABLE (periodid INT,periodstart DATE,periodend DATE,quantity INT)
DECLARE @t2 TABLE (periodid INT,periodstart DATE,periodend DATE,solditems INT)

INSERT INTO @t1 VALUES(1,'2010-10-01T00:00:00.000','2010-10-15T00:00:00.000',5)
INSERT INTO @t2 VALUES(14343,'2010-10-05T00:00:00.000','2010-10-06T00:00:00.000',2)

DECLARE @D1 DATE

SELECT @D1 = MIN(P) FROM (SELECT MIN(periodstart) P FROM @t1
                          UNION ALL
                          SELECT MIN(periodstart) FROM @t2) D

DECLARE @D2 DATE

SELECT @D2 = MAX(P) FROM (SELECT MAX(periodend) P FROM @t1
                          UNION ALL
                          SELECT MAX(periodend) FROM @t2) D

;WITH 
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4),
Dates AS(SELECT DATEADD(DAY,i-1,@D1) AS D FROM Nums where i <= 1+DATEDIFF(DAY,@D1,@D2))  , 
Stock As (
SELECT D ,t1.quantity - ISNULL(t2.solditems,0) AS itemsinstock
FROM Dates
LEFT OUTER JOIN @t1 t1 ON t1.periodend >= D and t1.periodstart <= D
LEFT OUTER JOIN @t2 t2 ON t2.periodend >= D and t2.periodstart <= D ),
NStock As (
select D,itemsinstock, ROW_NUMBER() over (order by D) - ROW_NUMBER() over (partition by itemsinstock order by D) AS G
from Stock)
SELECT MIN(D) AS periodstart, MAX(D) AS periodend, itemsinstock 
FROM NStock
GROUP BY G, itemsinstock
ORDER BY periodstart

Answer 3

Hopefully a little easier to read than Martin's. I used different tables and sample data, hopefully extrapolating the right info:

CREATE TABLE [dbo].[Quantity](
    [PeriodStart] [date] NOT NULL,
    [PeriodEnd] [date] NOT NULL,
    [Quantity] [int] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[SoldItems](
    [PeriodStart] [date] NOT NULL,
    [PeriodEnd] [date] NOT NULL,
    [SoldItems] [int] NOT NULL
) ON [PRIMARY]

INSERT INTO Quantity (PeriodStart,PeriodEnd,Quantity)
SELECT '20100101','20100115',5

INSERT INTO SoldItems (PeriodStart,PeriodEnd,SoldItems)
SELECT '20100105','20100107',2 union all
SELECT '20100106','20100108',1

The actual query is now:

;WITH Dates as (
    select PeriodStart as DateVal from SoldItems union select PeriodEnd from SoldItems union select PeriodStart from Quantity union select PeriodEnd from Quantity
), Periods as (
    select d1.DateVal as StartDate, d2.DateVal as EndDate
    from Dates d1 inner join Dates d2 on d1.DateVal < d2.DateVal left join Dates d3 on d1.DateVal < d3.DateVal and d3.DateVal < d2.DateVal where d3.DateVal is null
), QuantitiesSold as (
    select StartDate,EndDate,COALESCE(SUM(si.SoldItems),0) as Quantity
    from Periods p left join SoldItems si on p.StartDate < si.PeriodEnd and si.PeriodStart < p.EndDate
    group by StartDate,EndDate
)
select StartDate,EndDate,q.Quantity - qs.Quantity
from QuantitiesSold qs inner join Quantity q on qs.StartDate < q.PeriodEnd and q.PeriodStart < qs.EndDate

And the result is:

StartDate   EndDate (No column name)
2010-01-01  2010-01-05  5
2010-01-05  2010-01-06  3
2010-01-06  2010-01-07  2
2010-01-07  2010-01-08  4
2010-01-08  2010-01-15  5

Explanation: I'm using three Common Table Expressions. The first (Dates) is gathering all of the dates that we're talking about, from the two tables involved. The second (Periods) selects consecutive values from the Dates CTE. And the third (QuantitiesSold) then finds items in the SoldItems table that overlap these periods, and adds their totals together. All that remains in the outer select is to subtract these quantities from the total quantity stored in the Quantity Table

Answer 4

Damien,

I am trying to fully understand your solution and test it on a large scale of data, but I receive following errors for your code.

Msg 102, Level 15, State 1, Line 20

Incorrect syntax near 'Dates'.

Msg 102, Level 15, State 1, Line 22

Incorrect syntax near ','.

Msg 102, Level 15, State 1, Line 25

Incorrect syntax near ','.

Answer 5

Damien,

Based on your solution I also wanted to get a neat display for StockItems without overlapping dates. How about this solution?

CREATE TABLE [dbo].[SoldItems](
    [PeriodStart] [datetime] NOT NULL,
    [PeriodEnd] [datetime] NOT NULL,
    [SoldItems] [int] NOT NULL
) ON [PRIMARY]


INSERT INTO SoldItems (PeriodStart,PeriodEnd,SoldItems)
SELECT '20100105','20100106',2 union all
SELECT '20100105','20100108',3 union all
SELECT '20100115','20100116',1 union all
SELECT '20100101','20100120',10


;WITH Dates as (
    select PeriodStart as DateVal from SoldItems
    union 
    select PeriodEnd from SoldItems 
    union
    select PeriodStart from Quantity
    union
    select PeriodEnd from Quantity

), Periods as (
    select d1.DateVal as StartDate, d2.DateVal  as EndDate
    from Dates d1 
    inner join Dates d2 on d1.DateVal < d2.DateVal 
    left join Dates d3 on d1.DateVal < d3.DateVal and 
    d3.DateVal < d2.DateVal where d3.DateVal is null

), QuantitiesSold as (
    select StartDate,EndDate,SUM(si.SoldItems) as Quantity
    from Periods p left join SoldItems si on p.StartDate < si.PeriodEnd and si.PeriodStart < p.EndDate
    group by StartDate,EndDate
)

select StartDate,EndDate, qs.Quantity
from QuantitiesSold qs
where qs.quantity is not null