CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Ae Ae
Ae Ae

Reputation: 11

Finding Overlapping Between StartDate and EndDate to see if at the same day there are more than 3 occurences

So I have a table in SQL SERVER 2008 that has a list of itmes. A Person can only order max of 4 items per day. So i want to know in any start date or end date is there a time where they have more than 4 items for that particular day.

Here is an example of my table:

OrderNo  Item     quantity   StartDate   EndDate
112      xbox        2       2012-12-05  2012-12-10
123      tv          1       2012-12-06  2012-12-07
125      computer    4       2012-12-10  2012-12-11
165      game        1       2012-12-06  2012-12-10
186      toy         2       2012-12-02  2012-12-04

so from this table we can see that they had more than 4 items per day...now I need to know how many items and what days did they have more than 4 items.

Basically I want to check the overlapping dates from when the items were out and when it was returned to see if there were more than 4 items out at the same time on a particular date.

I have no clue how to approach this. I have looked at numerous overlapping dates and ranges in SQL.

Upvotes: 1

Views: 402

Answers (2)

Dale M
Dale M

Reputation: 2473

You need a calendar table. Ideally this is a permenant table set up in your master database all properly indexed but you can create it on the fly like:

WITH Calendar
AS
(
    SELECT MIN(StartDate) AS Today
          ,MAX(EndDate) AS LastDay
    FROM table
    UNION ALL
    SELECT DATEADD(day,1,Today)
          ,LastDay
    FROM Calendar
    WHERE Today<LastDay
)

Note: you have a normal maximum recursion of 100 so the most you can get is 100 days with this unless you add OPTION (MAXRECURSION n) where n is an int less than 32768.

You now have a table you can join with your original table that covers all the relevant dates, like so

SELECT Today
      ,SUM(Quantity) AS ItemCount
FROM Calendar c
     INNER JOIN 
     Table t ON c.Today BETWEEN t.StartDate AND t.EndDate
GROUP BY Today
HAVING SUM(Quantity)>4

See this SQL Fiddle

This SQL fiddle gives the solution with a "permenant" calendar table.

Upvotes: 1

Nico
Nico

Reputation: 1197

This one uses running totals to summarize items ordered per date. To check against any date ( not given in sample data ), simply use this result and do some joins. For more information about sequential numbers / date take a look at islands and gaps

insert into "timestamps"
select
  *
from
(
select "OrderNo", "ts", "quantity", case when "timestamp" = 'StartDate' then 1 else -1 end as "factor", 0 as "rolSum" from
    ( select "OrderNo", "StartDate", "EndDate", "quantity" from "data" ) as d
    unpivot ( "ts" for "timestamp" in ( "StartDate", "EndDate" )) as pvt
) as data
order by "ts"

declare @rolSum int = 0
update "timestamps" set @rolSum = "rolSum" = @rolSum + "quantity" * "factor" from "timestamps"

select 
    "OrderNo"
  , "ts"
  , "rolSum"
from
  "timestamps"

See SQL-Fiddle-Demo ( including table creation and your demo data ).

Upvotes: 0

Related Questions