joins and counts - 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

SQL Server Development (2000)

joins and counts

Author

Topic

hypo
Starting Member

8 Posts

Posted - 2006-03-06 : 15:19:26

hi, im not an sql guru and i hope someone can help ... this is my problem: I need to get the info from the original table to the destination table ... the question is how? (im working with ingres sql server so please only standard sql)

A bit more info: In the original table: Each row contains a problem which is logged on a certain date and has a status. Now i want the dates grouped, and for each status a count on a certain date (see destination table)

original table:
log_date     |  status
14/feb/06	CLOSED
14/feb/06	OPEN
14/feb/06	WAITING
16/feb/06	WAITING
16/feb/06	WAITING
16/feb/06	CLOSED
16/feb/06	WAITING
16/feb/06	WAITING
16/feb/06	UNSOLVED
16/feb/06	UNSOLVED
17/feb/06	CLOSED
19/feb/06	WAITING
19/feb/06	WAITING
19/feb/06	CLOSED
19/feb/06	WAITING
19/feb/06	UNSOLVED
20/feb/06	UNSOLVED
20/feb/06	UNSOLVED
20/feb/06	WAITING
21/feb/06	CLOSED
21/feb/06	OPEN
21/feb/06	OPEN

destination table:
date           open    closed    unsolved    waiting
14/feb/06      1       1         0           1
16/feb/06      0       1         2           4
17/feb/06      0       1         0           0
19/feb/06      0       1         1           3
20/feb/06      0       0         2           1
21/feb/06      2       1         0           0

I realy hope someone can give me a good query to solve this problem ... big thanks in advance!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-06 : 15:30:50

What is the datatype of Log_Date? This code assumes either varchar or the time components are all "00:00:00.000". Post back if that is not the case.


set nocount on

--generate original table
select '14/feb/06' log_date, 'CLOSED' status 
into #origTable union all
select '14/feb/06',	'OPEN' union all
select '14/feb/06',	'WAITING' union all
select '16/feb/06',	'WAITING' union all
select '16/feb/06',	'WAITING' union all
select '16/feb/06',	'CLOSED' union all
select '16/feb/06',	'WAITING' union all
select '16/feb/06',	'WAITING' union all
select '16/feb/06',	'UNSOLVED' union all
select '16/feb/06',	'UNSOLVED' union all
select '17/feb/06',	'CLOSED' union all
select '19/feb/06',	'WAITING' union all
select '19/feb/06',	'WAITING' union all
select '19/feb/06',	'CLOSED' union all
select '19/feb/06',	'WAITING' union all
select '19/feb/06',	'UNSOLVED' union all
select '20/feb/06',	'UNSOLVED' union all
select '20/feb/06',	'UNSOLVED' union all
select '20/feb/06',	'WAITING' union all
select '21/feb/06',	'CLOSED' union all
select '21/feb/06',	'OPEN' union all
select '21/feb/06',	'OPEN'

create table #dest (log_date varchar(10), [open] int, closed int, unsolved int, waiting int)


insert #dest (log_date, [open], closed, unsolved, waiting)
select	[log_date]
	,[open] =	sum(case when status = 'open' then 1 else 0 end)
	,closed =	sum(case when status = 'closed' then 1 else 0 end)
	,unsolved =	sum(case when status = 'unsolved' then 1 else 0 end)
	,waiting =	sum(case when status = 'waiting' then 1 else 0 end)
from	#origTable
group by [log_date]

select * from #dest

drop table #origTable
drop table #dest


output:
log_date  open        closed      unsolved    waiting     
--------- ----------- ----------- ----------- ----------- 
14/feb/06 1           1           0           1
16/feb/06 0           1           2           4
17/feb/06 0           1           0           0
19/feb/06 0           1           1           3
20/feb/06 0           0           2           1
21/feb/06 2           1           0           0

EDIT:
I added some sample data to make sure it worked.

Be One with the Optimizer
TG

hypo
Starting Member

8 Posts

Posted - 2006-03-06 : 15:38:09

thanks already! Ill give it a try tomorrow at work. I'll let you know if it works...
The Date is something special: now it's still a varchar, but it will be transformed to a date type. The original dates also have timestamps but they are stored somewhere else and the date is rounded. Otherwise I don't know how to group by day.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-06 : 15:46:12

for selecting and grouping by "all day":

replace log_date with:

dateadd(day, datediff(day, 0, log_date), 0)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources