select rows based on multiple date conditions

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

select rows based on multiple date conditions

Author

Topic

sql_man33
Starting Member

2 Posts

Posted - 2008-06-05 : 16:54:52

Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.

SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.

Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?

Thanks,
Marc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-05 : 17:22:04

select sum(case when datecolumn<=today then 1 else 0 end) as [<=today],sum(case when datecolumn>today then 1 else 0 end) as [>today] from your_table

Madhivanan

Failing to plan is Planning to fail

sql_man33
Starting Member

2 Posts

Posted - 2008-06-07 : 11:30:52

Thanks.
Maybe I should have mentioned I use an Access DB. The driver does not understand this. I'm not sure if this is the correct place to ask but maybe you know anyway.
This is the query
SELECT
Shipyard,
Shipyard_Country,
Main_Vessel_type,
Count(Main_Vessel_type) AS VesselCount,
Due_Or_Delivered
FROM
tblShip
GROUP BY
Due_Or_Delivered,
Main_Vessel_type,
Shipyard_Country,
Shipyard

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources