February 26, 2008 at 4:34 pm
select convert(varchar, datein, 101) DateIn, AdID ExternalAdID, count(LeadID) LeadCount,
Case LeadStatusID
when 1 then 'New'
when 2 then 'Rejected'
when 3 then 'Sent'
when 4 then 'Unresolved'
when 5 then 'Partner Rejected'
end as LeadStatus
from LeadsQueue
where datediff(d, DateIn, getdate()) <=35 and Left(adid, 5) = '99999'
group by convert(varchar, datein, 101), AdID, leadstatusid
order by convert(varchar, datein, 101), AdID, leadstatusid
I have this query and the output is like this for each day -
1/27/2008 9999919693 1 CSRRejected
1/27/2008 9999919693 1 Sent
1/27/2008 9999919695 2 CSRRejected
1/27/2008 9999919695 1 Sent
1/27/2008 9999919696 1 CSRRejected
1/27/2008 9999925180 1 CSRRejected
1/27/2008 9999925183 1 CSRRejected
1/27/2008 9999925183 2 Sent
1/27/2008 9999925192 6 CSRRejected
1/27/2008 9999925192 21 Sent
1/27/2008 9999925192 7 Unresolved
I need it to look like this - Raw leads = total count of leads
02/23/2008 AdID Rawleads CSRRejected Sent Unresolved
None 61 0 36 25
19694 5 0 5 0
24063 1 0 1 0
25173 1 1 0 0
Any idea's?
thanks
February 26, 2008 at 5:15 pm
use an "old-style" pivot command:
select dateadd(day,datediff(day,0,datein),0) Datein,
AdID,
sum(Case when leadstatus=1 then 1 else 0 end) as New,
sum(Case when leadstatus=2 then 1 else 0 end) as Rejected,
...
from LeadsQueue
where datediff(d, DateIn, getdate()) <=35 and Left(adid, 5) = '99999'
group by dateadd(day,datediff(day,0,datein),0), AdID
order by dateadd(day,datediff(day,0,datein),0), AdID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 5:22 pm
Thanks... that did it!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply