help with datediff - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

help with datediff

Author

Topic

justjohno
Starting Member

23 Posts

Posted - 2010-03-02 : 18:03:47

I got a table (city_forecast) with 4 columns:

city_id
start_date
end_date
weather

Need to get the difference between the start_date and end_date based on different row values by city_id. Below is what I got so far, when comparing each row, but I think I need to join the rows but don't understand how to get this accomplished.

select city_id, datediff (day, start_date, end_date) as Days
from city_forecast
group by city_id, start_date, end_date

Thanks in advance for your help, I really appreciate the assistance and teaching

John O

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-02 : 18:27:47

[code]
select city_id, sum(datediff (day, start_date, end_date)) as Days
from city_forecast
group by city_id, ~~start_date, end_date~~
[/code]

KH
[spoiler]Time is always against us[/spoiler]

justjohno
Starting Member

23 Posts

Posted - 2010-03-03 : 01:04:21

Thank you so much for the help, but the syntax from the last post gives me the same result I already have. Below is a snippet of my data. What I need to do is get the sum of the difference in days between the earliest start_date from the latest start date group by city_id.

city_id start_date end_date weather
-------------------------------------------------------------------
1 2009-01-03 2009-01-07 Rainy
1 2009-01-19 2009-01-25 Partly Cloudy
1 2009-02-01 2009-02-17 Sunny
2 2009-01-13 2009-01-14 Rainy
2 2009-01-23 2009-01-27 Snow
3 2009-01-13 2009-01-24 Cold
3 2009-01-25 2009-02-27 Snow
3 2009-02-28 2009-03-19 Below Freezing
4 2009-02-28 2009-03-19 Mild
5 2009-01-08 2009-01-29 Cold and Snowy
5 2009-03-01 2009-03-19 Below Freezing
7 2009-01-01 2009-03-31 Sunny with Occasional Shower

Desire result set below
city_id start_date end_date days
--------------------------------------------------------------------
1 2009-03-01 2009-17-02 45

Thanks again

John O

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-03 : 01:12:08

try this

select city_id,startdate,enddate,datediff (day, startdate, enddate) AS days
from (
select city_id, min(start_date) as startdate,max(end_date) as enddate
from city_forecast
group by city_id)s

justjohno
Starting Member

23 Posts

Posted - 2010-03-03 : 01:26:53

bklr...thank you so much for the correct syntax and teaching me. Its exactly what I need.

John O

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-03 : 01:50:34

quote:
Originally posted by justjohno

bklr...thank you so much for the correct syntax and teaching me. Its exactly what I need.

John O

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources