SQL question

[tonyyeb]

Here is a tricky question...

I have a table full of dates in a SQL database. Here is an example

ID Start Date  End Date
1  01/01/2010 01/01/2010
2  10/03/2010 14/10/2010
3  04/04/2010 04/04/2010
4  09/06/2010 09/06/2010
5  12/11/2010 30/11/2010
6  03/12/2010 03/12/2010
7  04/12/2010 04/12/2010
8  05/12/2010 06/12/2010

What I would like is to be able to say something like:

There are 3 x 1 single days
There are 2 x 4 days
There are 1 x 18 days

So I am grouping the periods. Is there a SQL command/function/query that would do this... taking into account the last three records which following each other chronologically with a sequence of 1 single say, 1 single day, 2 days.

Thanks for ANY help!!

 

[djkav]

You can use the GROUP BY clause usually at the end of the SQL statement where you normally put ORDER BY.

I also think I had an issue using GROUP BY with date fields. I had to convert them to string types. I maybe wrong, its a good few years since I did that. But I deffo remember having a date field problem with on of these clauses.

 

[mark66]

Hi,

I am not too sure of the command or correct syntax as I am away from my work, but is there not a DATEDIFF function? Something like:

SELECT DATEDIFF(DAY, START_DATE, END_DATE), COUNT(*)
FROM FILE_A
GROUP BY DATEDIFF(DAY, START_DATE, END_DATE)

 

[Pho]

The first part is pretty easy..

SELECT
	DATEDIFF(D, [Start Date], [End Date]) AS Days,
	COUNT(id) as Count	
FROM dates
GROUP BY DATEDIFF(D, [Start Date], [End Date])

Works on SQL Server anyway, pretty much as asbove ^.


The second part is more tricky. What SQL system are you using? I might be wrong but it sounds like you might need to iterative over all your results to work it out. Do you absolutely need consecutive days?

 

[tonyyeb]

I'm not sure if i've made this clear enough. Yes I can calculate the difference in the start and end date of each record. The problem comes when you have for example three records which follow each other. Such as:

Start Date  End Date
01/01/2010 01/01/2010
02/01/2010 02/01/2010
03/01/2010 03/01/2010

I would want to count this as 1 x 3 days not 3 x 1 days.

EDIT: Using SQL Server 2005.

 

[Teq]

Hmm, this would be:

SELECT ( end_date - start_date + 1 ) as days, count(*) FROM (
...TABLE...
)
GROUP BY end_date - start_date + 1

in Oracle (as it does not have a datediff function), assuming that is what you were looking for? The guys above seem to have answered your question as you have described it, but I thought I'd give a different angle

 

[tonyyeb]

@Teq

Only part of the problem has been solved.

To fix it I have had to add another field which includes a period ID so that groups of individual days that run sequentially and chronologically, are seen as one period.

@all

Thanks for your efforts. Looks like this one wouldn't be simple to solve using just SQL (and without modifying the table!)

 

[Pho]

You could iterative over each row, using cursors (bad!) or something like this. Here you'll have access to the previous rows data and so can work out if it's sequential or not provided your initial query is ordered by date.

It's definitely possible as this is written for SQL Server, just a little more work.