Group several date fields - 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 2000 Forums

Transact-SQL (2000)

Group several date fields

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-29 : 10:17:21

Scott writes "We have a table that has multiple date fields, such as owned_date and resolve_date. I want to be able to group by week and get a total of owned_date that falls in each week and resolve_date that falls in each week. The only way I have been able to get this information so far is to create two separate SELECT statements and using UNION. This gives me the information I need, however I would like to have them both appear in the same returned record instead of two separate records. What I get now looks like this:

Week   Type   Total
1/1    Owned      6
1/1    Closed     4
1/8    Owned      8
1/8    Closed    10

I would like it to look like this:

Week   Owned   Closed
1/1        6        4
1/8        8       10

I've searched your site, Google, and anything else I could without much luck. Any suggestions?

Thanks,
Scott"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 10:21:11

something like this

select {week}, sum(case when type = 'owned' then 1 else 0 end) as owned,
sum(case when type = 'closed' then 1 else 0 end) as closed
from table1
group by {week}
order by 1

Peter Larsson
Helsingborg, Sweden

Scott_sql
Starting Member

1 Post

Posted - 2007-01-29 : 14:27:15

Thank you for the reply, however it looks like your suggestion is meant for the resulting table, not the original data. I am trying to do this in one step. Any other suggestions?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-29 : 14:41:25

Replace the table in Peter's query with a derived table that has the query that produces the first result table.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 15:17:30

Or better, post the DDL and some sample data to illustrate the problem.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 15:21:27

Since there is only one table involved, my query should work.
You may have to change the column names to fit your environment...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources