CODEWITHSUNDEEP
sqlsql-serverreporting
mttp1990
mttp1990

Reputation: 114

SQL query, needing assistance with desired output

Please bear with me as i'm just starting to dive into SQL reports which is a huge jump from just dumping data.

Here is a brief descriptionof the table schema. Table Name: CallLog Fields:callid, tracker, recvddate, callsource

DB snap

I've had success using this simple grouping query

Select
    right('00' + rtrim(Datepart(ww, recvddate)),2) week,
    Tracker,
    count(callsource) as [Email Tickets]
from
    calllog

where datepart(year,recvddate) >= '2017' and callsource = 'email'
group by right('00' + rtrim(Datepart(ww, recvddate)),2),Tracker
order by 2,1

Which gives me:

I'm attempting to generate a report wherein I count the the amount of records of each callsource type as their own column in the resulting query.

This is what i've tried: 

Select
    right('00' + rtrim(Datepart(ww, c1.recvddate)),2) week,
    c1.Tracker,
    count(c1.callsource) as [Email Tickets],
    c2.[Queue Tickets]
from
    calllog c1
inner join
(
    Select
        right('00' + rtrim(Datepart(ww, recvddate)),2) week,
        Tracker,
        count(callsource) as [Queue Tickets]
    from
        calllog
    where datepart(year,recvddate) >= '2017' and callsource = 'phone'
    group by right('00' + rtrim(Datepart(ww, recvddate)),2),Tracker
) c2 
on c1.callid = c2.callid

where datepart(year,recvddate) >= '2017' and callsource = 'email'
group by right('00' + rtrim(Datepart(ww, recvddate)),2),Tracker
order by 2,1

But i receive the below error. I've tried changing how i reference the columns but nothing i've tried works.

enter image description here

Any advice is appreciated.

UPDATE:

Withthe help of @Simon I was able to come up with this query which is what i was looking for. 

SELECT      A.[week] as [Week],
            A.tracker as Tracker,
            B.Tickets as [Queue Tickets],
            C.Tickets as [Email Tickets],
            A.Tickets as [Total Tickets]
FROM        (SELECT RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2) [week],
                    Tracker,
                    COUNT(callid) as Tickets
             FROM   calllog
             WHERE DATEPART(year,recvddate) >= '2017' and tracker = 'djtaylor'
             GROUP BY RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2), Tracker) as A

INNER JOIN  (SELECT RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2) [week],
                    Tracker,
                    COUNT(callid) as Tickets
             FROM   calllog
             WHERE DATEPART(year,recvddate) >= '2017' and callsource = 'queue'
             GROUP BY RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2), Tracker) as B ON A.[week] = B.[week]
                                                                                    AND A.Tracker = B.Tracker
INNER JOIN  (SELECT RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2) [week],
                    Tracker,
                    COUNT(callid) as Tickets
             FROM   calllog
             WHERE DATEPART(year,recvddate) >= '2017' and callsource = 'email'
             GROUP BY RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2), Tracker) as C ON A.[week] = C.[week]
                                                                                    AND A.Tracker = C.Tracker
ORDER BY 2,1

Upvotes: 0

Views: 54

Answers (1)

Simon
Simon

Reputation: 784

I think this is what you want:

Select
    right('00' + rtrim(Datepart(ww, recvddate)),2) [week],
    Tracker,
    callsource,
    count(callid) as [Email Tickets]
from
    calllog

where datepart(year,recvddate) >= '2017' and callsource = 'email'
group by right('00' + rtrim(Datepart(ww, recvddate)),2), Tracker, callsource
order by [week], Tracker

OK. I think this is what you want:

SELECT      A.[week] as [Week],
            A.tracker as Tracker,
            A.callsource,
            A.Tickets as CallSourceTickets,
            B.Tickets CallCenterTickets
FROM        (SELECT RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2) [week],
                    Tracker,
                    callsource,
                    COUNT(callid) as Tickets
             FROM   calllog
             WHERE DATEPART(year,recvddate) >= '2017'
             GROUP BY RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2), Tracker, callsource) as A
INNER JOIN  (SELECT RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2) [week],
                    Tracker,
                    COUNT(callid) as Tickets
             FROM   calllog
             WHERE DATEPART(year,recvddate) >= '2017'
             GROUP BY RIGHT('00' + RTRIM(DATEPART(ww, recvddate)),2), Tracker) as B ON A.[week] = B.[week]
                                                                                    AND A.Tracker B.Tracker
ORDER BY [Week], Tracker

Upvotes: 1

Related Questions