urgent! split results by two conditionals

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

urgent! split results by two conditionals

Author

Topic

JimXC
Starting Member

1 Post

Posted - 2007-03-12 : 08:21:33

hi
im using sql server 2005

i have a table called download_transactions which contains wallpaper and video downloads. videos have a download_type of 110 and wallpapers 104. i need to pull out a count of these transactions where download_type is 110 and 104.
so one results column is count of 110 downloads
and the other column is count of 104.
thing is, one canned do; ..
where download_type 110 And 104
where download_type in (110, 104)
because this just confuses SQLserver. two conditionals.
how to do this, any ideas.
must i use a cursor to go through results once for 110, then again for 104?

at the moment, i select to pull out the count, and it gives two rows for each download type e.g.
clientID date count
50015 2007-03-06 00:00:00.000 922
50015 2007-03-06 00:00:00.000 1430
50020 2007-03-06 00:00:00.000 486
50020 2007-03-06 00:00:00.000 872

two rows for each clientID, one is count of 110, and one is count of 104.
i just need the 2nd row count to be in a 4th column next, like this:

clientID date 104/count 110/count
50015 2007-03-06 00:00:00.000 922 1430

this is the sql i am using:

declare @start_date datetime
declare @end_date datetime
declare @start_timeshift int
declare @end_timeshift int
declare @client_id int

select @start_timeshift =0,
@end_timeshift =23,
@start_date = (dateadd(hh,@start_timeshift,'2007-03-06')),
@end_date = (dateadd(hh,@end_timeshift,'2007-03-06'))

select c.client_id,
@start_date [date],
count(txn.download_transaction_id) + '' + count(txn.download_transaction_id)

from [main_tbl].dbo.download_transactions txn with ( nolock )

inner join [main_tbl].dbo.billing_partners as b with ( nolock ) on ( ( b.billing_partner_id = txn.billing_partner_id )
and ( b.is_active = 1 ) and ( b.is_development <> 1 ) )
inner join [main_tbl].dbo.suppliers s with ( nolock ) on ( ( s.supplier_id = txn.supplier_id )
and ( s.is_active = 1 ) and ( s.is_development <> 1 ) )
inner join [main_tbl].dbo.clients c with ( nolock ) on ( ( c.client_id = txn.client_id )
and ( c.is_active = 1 ) and ( c.is_development <> 1 ) )
inner join [main_tbl].dbo.downloads d with ( nolock ) on ( ( d.download_id = txn.download_id ) )

inner join [main_tbl].dbo.download_types dt with ( nolock ) on ( ( dt.download_type_id = d.download_type_id ) )

--inner join [LOGGING].dbo.wap_sessions wp with (nolock) on ( ( wp.client_id = txn.client_id ) )

where (txn.client_id in (50025,50015,50030,50020))

and (txn.date_created between @start_date AND @end_date) --between '2007-01-01' And '2007-01-10')
and (txn.billing_successful = 1)
and (txn.PORTAL_CAMPAIGN_ID is null)
--and (dt.download_type_id = '104' + '110') --choose downloadtype
--and (dt.download_type_id = 110) --ch+oose downloadtype
--and (dt.download_type_id = 104) --ch+oose downloadtype

group by
c.client_ID, datepart(day,txn.date_created), dt.download_type_id, dt.download_type_id

order by
c.client_ID, dt.download_type_id

any help will be VERY VERY VERY appreciated.
Thanks
James

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 11:21:48

Here's an example that will help you figure it out for your query,

create table #CountDemo
(
ClientID int,
DownloadType int
)
insert #CountDemo
select 50015, 110 union all
select 50015, 104 union all
select 50020, 110 union all
select 50015, 104 union all
select 50015, 110 union all
select 50020, 110 union all
select 50015, 104 union all
select 50015, 110 union all
select 50020, 104 union all
select 50015, 110 union all
select 50020, 110 union all
select 50015, 104 union all
select 50020, 110 union all
select 50015, 104 union all
select 50015, 110

select ClientID,
	sum(case DownloadType when 104 then 1 else 0 end) as [104/count],
	sum(case DownloadType when 110 then 1 else 0 end) as [110/count]
from #CountDemo
group by ClientID

drop table #CountDemo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources