Help with query

  • 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

  • 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?

  • 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