SQL - Combine another SELECT query with a JOIN

Question

I found this post on stackoverflow, Add a summary row with totals

What I'm trying to accomplish is just that, but with the converted DURATION field I have listed in this WITH statement below. I'm trying to TOTAL up the Durations column for that day(24 hr period). I don't know if it's possible. Let me know. Thank you!

;WITH dupes AS
(
    SELECT 
        CALLER_PHONE, DIALED_PHONE
    FROM 
        dbo.PBXDATA
    GROUP BY 
        CALLER_PHONE, DIALED_PHONE
)
SELECT 
    c.CALL_TIME, c.SALES_REP, c.CALL_TYPE, c.FLAG1, 
    COALESCE(NULLIF(c.FLAG3, 'NULL'),'') AS FLAG3, 
    ISNULL(dupes.CALLER_PHONE, '') + ISNULL(dupes.DIALED_PHONE,'') AS PHONE, 
    CONVERT(VARCHAR(8), c.DURATION, 108) AS DURATION
FROM 
    dupes
JOIN 
    dbo.PBXDATA c ON dupes.CALLER_PHONE = c.CALLER_PHONE 
                  OR dupes.DIALED_PHONE = c.DIALED_PHONE
WHERE 
    (c.SALES_REP LIKE 'Doug%' OR 
     c.SALES_REP LIKE 'Nick%' OR 
     c.SALES_REP LIKE 'Bob%' OR 
     c.SALES_REP LIKE 'Joe%' OR 
     c.SALES_REP LIKE 'John%')
    AND (c.CALL_TIME >= DATEADD(DAY, 0, DATEDIFF(DAY, 0,   CURRENT_TIMESTAMP)))
    AND (c.CALL_TIME <  DATEADD(DAY, 1, DATEDIFF(DAY, 0,  CURRENT_TIMESTAMP)))
    AND DURATION = (SELECT CAST(DATEADD(S, SUM(DATEDIFF(S, '00:00:00', DURATION)), '00:00:00') AS TIME) 
                    FROM dbo.PBXDATA) 
ORDER BY 
    c.CALL_TIME;

Can you give us tables structure ,input and desired out put ? It's hard to understand what you want. — sagi, Sep 30 '15 at 23:33
I apologize for that. Thank you for reminding me. Below is the link. http://www.tvlift.com/beta-test/table.png — user3434068, Oct 01 '15 at 23:09

score 0 · Answer 1 · answered Sep 30 '15 at 23:44

If you just want an overall total for the Duration in your dupes table, you can just sum your Duration there.

;WITH dupes AS
(
SELECT CALLER_PHONE, DIALED_PHONE, convert(varchar(8), SUM(c.DURATION), 108) AS Total_Time 
FROM dbo.PBXDATA
GROUP BY CALLER_PHONE, DIALED_PHONE
)

And add , Total_Time to your SELECT statement.

If you have multiple days in your query, you would need to add the date field in the dupes and add it as part of the JOIN's ON clause.

SQL - Combine another SELECT query with a JOIN

1 Answers1