Group by date and and check for continuous dates

Question

I have a set of data like this

date1       Price  Availability  ServiceID
2012-10-01  50     1             5
2012-10-02  60     1             5
2012-10-03  60     1             5
2012-10-04  60     1             5
2012-10-06  60     1             5
2012-10-07  60     0             5
2012-10-08  60     1             5

Now I want to check what is the total price, min availability and serviceid for a particular period

For example

from 2012-10-01 to 2012-10-03 results would be 170, availability 1 and serviceID 5
from 2012-10-06 to 2012-10-08 results would be 180, availability 0! and serviceID 5
from 2012-10-04 to 2012-10-06 results would be 120, availability 1! and serviceID 5 BUT there is a date missing so availability is 0!

I tried something like

select date1, sum(price), min(availability), service from #t
group by date1, price, availability, service
having count(date1) = datediff(day, @startdate, @enddate)

That does not work because if I group by date1, then count in not valid. Dont know how to get sum, min availability and check continuous dates.

EDIT In case I want a results set to contain detailed spec. and filter out results with min(availability) = 0 or noncontinuous date

For example
from 2012-10-01 to 2012-10-03 results would be 170, availability 1 and serviceID 5

date1       Price  Availability  ServiceID
    2012-10-01  50     1             5
    2012-10-02  60     1             5
    2012-10-03  60     1             5

from 2012-10-06 to 2012-10-08 results would be 180, availability 0! and serviceID 5
date1       Price  Availability  ServiceID

from 2012-10-04 to 2012-10-06 results would be 120, availability 1! and serviceID 5 BUT 
date1       Price  Availability  ServiceID

How could result from 2012-10-04 to 2012-10-06 be 180 if there are 2 values with 60 each? Do you need a 'fake' 60 for missing date? — Nenad Zivkovic, Mar 28 '13 at 13:03

George Mastros · Accepted Answer · 2013-03-28T14:31:56.093

Try this:

select  sum(price) As TotalPrice, 
        service,
        Case When Count(*) = DateDiff(Day, @Startdate, @EndDate) + 1 
                Then Min(Availability)
                Else 0 End As Availability
from    #T
Where   Date1 >= @StartDate
        And Date1 <= @endDate
group by service

By filtering the date in a where clause based on dates, and not grouping by the date, this allows you to get a count or rows which you can then match to the date diff. Note that you need to add 1 to the date diff because DateDiff from yesterday to today is only 1 but would represent 2 rows in your source data.

I should also mention that this is not actually checking for continuous dates. It's simply looking for a corresponding number of rows to match the calculated number of days.

Based on your comment and your edit, you may want to try this derived table solution.

Select  T.date1,
        SummaryData.TotalPrice,
        SummaryData.Availability,
        T.Service
From    #T T
        Inner Join (
            select  sum(price) As TotalPrice, 
            service,
                Case When Count(*) = DateDiff(Day, @Startdate, @EndDate) + 1 
                     Then Min(Availability)
                     Else 0 End As Availability
            from    #T
            Where   Date1 >= @StartDate
                    And Date1 <= @endDate
            group by service
            Having Case When Count(*) = DateDiff(Day, @Startdate, @EndDate) + 1 
                        Then Min(Availability)
                        Else 0 End = 1 
            ) As SummaryData
            On T.Service = SummaryData.Service
Where   Date1 >= @StartDate
        And Date1 <= @endDate

basically i would need a cte or a temp table. it cannot be solved in one query? — mko, Mar 28 '13 at 13:28
It's possible to create the same output I show above without using a CTE, but it may not be possible to include the details without using a CTE unless you are on a newer version of SQL Server. What version are you using? — George Mastros, Mar 28 '13 at 14:26

score 0 · Answer 2 · edited May 23 '17 at 10:25

If there wasn't for curveball with missing dates, query would be quite simple:

SELECT SUM(price), MIN(availability) AS Availibility, MIN(serviceID) AS serviceID FROM #t
WHERE date1 BETWEEN @Startdate AND @enddate

Howere, finding missing dates requires one additional CTE, I'll use idea from t-sql get all dates between 2 dates and it can look somehting like this:

;WITH dates AS (
  SELECT @startdate AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
    FROM dates s
   WHERE DATEADD(dd, 1, dt) <= @enddate
)
SELECT SUM(price), 
CASE 
    WHEN EXISTS (SELECT * FROM dates WHERE dt NOT IN (SELECT date1 FROM #t WHERE date1 BETWEEN @Startdate AND @enddate)) THEN 0
    ELSE MIN(availability) 
END AS Availibility, 
MIN(serviceID) AS serviceID FROM #t
WHERE date1 BETWEEN @Startdate AND @enddate

While I was typing G Mastros also posted a solution which I like better then mine :) but decided to post this one as well. Doesn't hurt to look from two sides. — Nenad Zivkovic, Mar 28 '13 at 13:14

Group by date and and check for continuous dates

2 Answers2