locked
Different Column Values Based on different Date Field RRS feed

  • Question

  • Hey guys, so I have a query which I'm working on which I need to show the number of emails sent out, and emails responded to.

    There are two date fields, one for transactiondate(used when email gets sent) and the other assigneddate (used for when email gets a respond).  The problem is that the numbers are off based on which date key I join to, I either get the wrong respond count s if I join to transactiondate, or I get the wrong sent count if I join on assigneddate.  I need to figure out a way to separate the two columns so that each one gets the right numbers.  So the query I have right now is something like this, which brings me back the correct number of respondcount.

    Select sum(sentcount), sum(respondcount)
    from marketing m
    join datetable d on m.assigneddate = d.datekey --To get the sent count, I would have to join on m.transactiondatekey = d.datekey
    where d.yyyymm = (201805)

    I tried doing a derived table and doing a left join but I still wasn't able to get the correct number counts.  Any help would be appreciated.

    Sunday, May 13, 2018 6:36 PM

Answers

  • So in the example query, you want the number of mails sent in May and the number of responses received in mail (regardless of which month the original mail was sent?)

    You need to join to the date table twice. The simplest would be a UNION query:

    SELECT SUM(sentcount) AS sentcount, 0 AS respondcount
    FROM   marketing m
    JOIN   datetable d ON m.transactiondatekey = d.datekey
    WHERE  d.yyyymm = 201805
    UNION ALL
    SELECT 0, SUM(respondcount)
    FROM   marketing m ON m.assigneddate = d.datekey
    WHERE  d.yyyymm = 2018016

    • Proposed as answer by Xi Jin Monday, May 14, 2018 3:05 AM
    • Marked as answer by Diango Tuesday, May 15, 2018 12:28 AM
    Sunday, May 13, 2018 7:16 PM
  • sounds like this to me

    DECLARE @startdate date = '20180101' --Jan 2018
    DECLARE @ENdDate date= '20180501' -- May 2018
    
    --so you would need total emails sent and respond per month from jan - may 2018 
    then you can do like this
    SELECT FORMAT(f.[Date],'MMM yyyy') AS Period,
    COALESCE(Sent,0) AS SentCount,
    COALESCE(Respond,0) AS RespondCount FROM dbo.CalendarTable(@Startdate,@Enddate,0,1,0)f LEFT JOIN ( SELECT DATEADD(mm,DATEDIFF(mm,0,transactiondatekey),0) AS TranDate, SUM(sentcount) AS Sent FROM marketing
    GROUP BY DATEADD(mm,DATEDIFF(mm,0,transactiondatekey),0)
    )s
    ON s.TranDate = f.[Date]
    LEFT JOIN (
    SELECT DATEADD(mm,DATEDIFF(mm,0,assigneddate),0) AS AssignDate,
    SUM(respondcount) AS Respond
    FROM marketing
    GROUP BY DATEADD(mm,DATEDIFF(mm,0,assigneddate),0)
    )s
    ON s.AssignDate = f.[Date]

    CalendarDate UDF can be found here

    https://visakhm.blogspot.com/2010/02/generating-calendar-table.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Xi Jin Monday, May 14, 2018 3:05 AM
    • Marked as answer by Diango Tuesday, May 15, 2018 12:28 AM
    Sunday, May 13, 2018 7:26 PM

All replies

  • So in the example query, you want the number of mails sent in May and the number of responses received in mail (regardless of which month the original mail was sent?)

    You need to join to the date table twice. The simplest would be a UNION query:

    SELECT SUM(sentcount) AS sentcount, 0 AS respondcount
    FROM   marketing m
    JOIN   datetable d ON m.transactiondatekey = d.datekey
    WHERE  d.yyyymm = 201805
    UNION ALL
    SELECT 0, SUM(respondcount)
    FROM   marketing m ON m.assigneddate = d.datekey
    WHERE  d.yyyymm = 2018016

    • Proposed as answer by Xi Jin Monday, May 14, 2018 3:05 AM
    • Marked as answer by Diango Tuesday, May 15, 2018 12:28 AM
    Sunday, May 13, 2018 7:16 PM
  • sounds like this to me

    DECLARE @startdate date = '20180101' --Jan 2018
    DECLARE @ENdDate date= '20180501' -- May 2018
    
    --so you would need total emails sent and respond per month from jan - may 2018 
    then you can do like this
    SELECT FORMAT(f.[Date],'MMM yyyy') AS Period,
    COALESCE(Sent,0) AS SentCount,
    COALESCE(Respond,0) AS RespondCount FROM dbo.CalendarTable(@Startdate,@Enddate,0,1,0)f LEFT JOIN ( SELECT DATEADD(mm,DATEDIFF(mm,0,transactiondatekey),0) AS TranDate, SUM(sentcount) AS Sent FROM marketing
    GROUP BY DATEADD(mm,DATEDIFF(mm,0,transactiondatekey),0)
    )s
    ON s.TranDate = f.[Date]
    LEFT JOIN (
    SELECT DATEADD(mm,DATEDIFF(mm,0,assigneddate),0) AS AssignDate,
    SUM(respondcount) AS Respond
    FROM marketing
    GROUP BY DATEADD(mm,DATEDIFF(mm,0,assigneddate),0)
    )s
    ON s.AssignDate = f.[Date]

    CalendarDate UDF can be found here

    https://visakhm.blogspot.com/2010/02/generating-calendar-table.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Xi Jin Monday, May 14, 2018 3:05 AM
    • Marked as answer by Diango Tuesday, May 15, 2018 12:28 AM
    Sunday, May 13, 2018 7:26 PM
  • Use 2 separate CTEs to get your counts correctly and then use full join between them, e.g.

    ;with cteSent as (select sum(sentCount) as TotalSent from marketing me join datetable d ... where d.yyyymm=201805),

    cteReceived as (select sum(respondCount) as TotalResponses from  marketing me join ...)

    select cteSent.TotalSent, cteReceived.TotalResponses from cteSent, cteReceived --- single row result

    See more details about this problem in

    Aggregates with multiple tables


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi N Sunday, May 13, 2018 7:32 PM
    • Proposed as answer by Xi Jin Monday, May 14, 2018 3:05 AM
    Sunday, May 13, 2018 7:30 PM
  • Thanks guys for your help.  Good solutions!  The only one I was't able to use was the CTE solution because I have a conditional IF and the CTE had trouble being recognize because of it.
    Tuesday, May 15, 2018 12:28 AM