Counting with DATEDIFF not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting with DATEDIFF not working

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-06 : 15:18:51

I have two tables. One with headers - 28,29,30 or 31 records depending on the month (One record for each day). In this table, we have start_date, end_date columns - both of which stores same value for a record like 01/12/2007, 01/12/2007 (because the records are for a day here). Other table is the details table where we can have 24 records for each day, with the foreign key from the header table.

I am running this query calculating Min, Max, Avg etc. I need to calculate the number of days in the month - so I was using DATEDIFF, which is producing 0 (I checked and its doing the calculation on one record only, so the difference is 0). Here's my query;

select distinct MIN(rdata_value), MAX(rdata_value), AVG(rdata_value), COUNT(RDATA_VALUE), DATEDIFF(day,MIN(rsample_start_date),MAX(rsample_end_date))
from sample_realtime_data, station_realtime_data
where rsample_start_date >= '01-Dec-2007'
and rsample_end_date <= '31-Dec-2007'
and rsample_station = 1049 and rdata_parameter = 'A0123'
group by rsample_start_date, rsample_end_date

Any idea how I can get the no. of days in the month here?
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 15:23:07

Because there is no relation between the two tables.

E 12°55'05.25"
N 56°04'39.16"

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-06 : 15:39:22

Hi Peso,
Thats a problem too. If I put the relationship, I get more than one row in my output, whereas I am looking for a single row as output.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 01:25:34

quote:
Originally posted by sqlbug

Hi Peso,
Thats a problem too. If I put the relationship, I get more than one row in my output, whereas I am looking for a single row as output.
Thanks.

then you need to group on the duplicating fields to return one record per field value. but while doing it you need to decide what value you want for other fields.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 03:44:07

[code]SELECT MIN({t1 | t2}.rdata_value),
MAX({t1 | t2}.rdata_value),
AVG({t1 | t2}.rdata_value),
COUNT({t1 | t2}.RDATA_VALUE),
DATEDIFF(DAY, MIN({t1 | t2}.rsample_start_date), MAX({t1 | t2}.rsample_end_date))
FROM sample_realtime_data AS t1
INNER JOIN station_realtime_data AS t2 ON t2.Col1 = t1.Col1
WHERE {t1 | t2}.rsample_start_date >= '01-Dec-2007'
AND {t1 | t2}.rsample_end_date < '1-Jan-2008'
AND {t1 | t2}.rsample_station = 1049
AND {t1 | t2}.rdata_parameter = 'A0123'[/code]

E 12°55'05.25"
N 56°04'39.16"

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-07 : 12:56:13

Great, Thanks. This is what worked (little modified).
SELECT MIN(t2.rdata_value),
MAX(t2.rdata_value),
AVG(t2.rdata_value),
COUNT(t2.RDATA_VALUE),
DATEDIFF(DAY, MIN(t1.rsample_start_date), MAX(t1.rsample_end_date)) + 1
FROM sample_realtime_data AS t1
INNER JOIN station_realtime_data AS t2 ON t2.rdata_sample_num = t1.rsample_num
WHERE t1.rsample_start_date >= '01-Dec-2007'
AND t1.rsample_end_date < '1-Jan-2008'
AND t1.rsample_station = 1049
AND t2.rdata_parameter = 'A0123'

Now - Say we did not receive some data for few days, in which case the End Date may not be the last day of the month. My other calculations are based on that value. Do you know how I can incorporate SQL in this same statement, so it calculates the no. of days in that month?
Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 12:58:10

what are other calculations you need to perform?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 14:09:49

It's very easy to recalculate a datetime variable to the first day of next month, as shown in the code above.

E 12°55'05.25"
N 56°04'39.16"

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-07 : 14:27:07

Hi Visakh, I need to know the no of days in the month, multiply that by 24 and divide the data count by this no.
SQL Server doesn't have any builtin function that will return the number of days in the month automatically. Thats why I asked. I've seen user-defined functions - but instead I will just do it in my serverside code.
Thanks to Peso and you. You guys are very helpful.
See you later.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 14:32:33

quote:
Originally posted by sqlbug

Hi Visakh, I need to know the no of days in the month, multiply that by 24 and divide the data count by this no.
SQL Server doesn't have any builtin function that will return the number of days in the month automatically. Thats why I asked. I've seen user-defined functions - but instead I will just do it in my serverside code.
Thanks to Peso and you. You guys are very helpful.
See you later.

shouldnt taking DATEDIFF of days between MAX(Date) and MIN(Date) for the month give you count of days for the month?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-07 : 16:27:40

This shows how to get the days in the month for any date.


Select
	Year		= year(a.MyDate),
	Month		= month(a.MyDate),
	DaysInMonth 	= day(dateadd(month,datediff(month,-1,a.MyDate),-1)),
	a.MyDate
from
	(
	--Test Data
	select MyDate = dateadd(month,-12,getdate())	union all
	select MyDate = dateadd(month,-11,getdate())	union all
	select MyDate = dateadd(month,-10,getdate())	union all
	select MyDate = dateadd(month,-9,getdate())	union all
	select MyDate = dateadd(month,-8,getdate())	union all
	select MyDate = dateadd(month,-7,getdate())	union all
	select MyDate = dateadd(month,-6,getdate())	union all
	select MyDate = dateadd(month,-5,getdate())	union all
	select MyDate = dateadd(month,-4,getdate())	union all
	select MyDate = dateadd(month,-3,getdate())	union all
	select MyDate = dateadd(month,-2,getdate())	union all
	select MyDate = dateadd(month,-1,getdate())	union all
	select MyDate = getdate()
	) a
order by
	1,2

Results:
Year        Month       DaysInMonth MyDate
----------- ----------- ----------- ----------------------- 
2007        8           31          2007-08-07 16:26:06.123
2007        9           30          2007-09-07 16:26:06.123
2007        10          31          2007-10-07 16:26:06.123
2007        11          30          2007-11-07 16:26:06.123
2007        12          31          2007-12-07 16:26:06.123
2008        1           31          2008-01-07 16:26:06.123
2008        2           29          2008-02-07 16:26:06.123
2008        3           31          2008-03-07 16:26:06.123
2008        4           30          2008-04-07 16:26:06.123
2008        5           31          2008-05-07 16:26:06.123
2008        6           30          2008-06-07 16:26:06.123
2008        7           31          2008-07-07 16:26:06.123
2008        8           31          2008-08-07 16:26:06.123

(13 row(s) affected)

CODO ERGO SUM

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-07 : 18:33:00

Thanks Michael for the nice statement.

Visakh, no. As I mentioned, if some data are not submitted and that happens on the last day of the month - then End Date would produce 30, instead of 31 - for example. It can even give you 25, if data are not submitted for the last 6 days of the month.
Therefore, we can not base our calculation on that DATEDIFF.

It can easily be done in the serverside code where the calculation will be performed.
Thanks to all of you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources