CODEWITHSUNDEEP
sql-serversum
jmd9qs
jmd9qs

Reputation: 169

SQL Server : select the sum of the results of a subquery, ConnectWise Database

I have a query I'm running to find ConnectWise time entries that have been entered by certain teams in the past month. What I'd like to do is modify the below query to include only one result per ticket, but ONLY if the datediff(s, time_start, time_end) result is over 1800. Everything I've tried errors out, so my only MWE is the below query:

SELECT
    service.SR_Service_RecID AS "Ticket #",
    te.Date_Start AS "Time Entry Day",
    m.first_name AS First,
    m.last_name AS Last,
    DATEDIFF(s, te.time_start, te.time_end) AS "Time (s)"
FROM 
    dbo.Time_Entry te
LEFT JOIN  
    dbo.Activity_Class activity ON activity.Activity_Class_RecID = te.Activity_Class_RecID
LEFT JOIN 
    dbo.SR_Service service ON te.SR_Service_RecID = service.SR_Service_RecID
LEFT JOIN 
    dbo.Member m ON m.Member_RecID = te.Member_RecID
LEFT JOIN 
    dbo.SR_Team_Mbr member ON member.Member_RecID = M.Member_RecID
LEFT JOIN 
    dbo.SR_Team T ON T.SR_Team_RecID = member.SR_Team_RecID
WHERE 
    m.member_recID IN (SELECT M.Member_RecID
                       FROM Member M
                       LEFT JOIN dbo.SR_Team_Mbr member ON member.Member_RecID = M.Member_RecID
                       LEFT JOIN dbo.SR_Team T ON T.SR_Team_RecID = member.SR_Team_RecID
                       WHERE T.Description LIKE '%c'
                          OR T.Description LIKE '%d'
                          OR T.Description LIKE '%e')
    AND activity.Description LIKE 'T%'
    AND service.Date_Entered >= DATEADD(MONTH, -1, GETDATE())
GROUP BY 
    service.SR_Service_RecID, te.Time_RecID, te.Date_Start,
    m.first_name, m.last_name,
    DATEDIFF(s, te.time_start, te.time_end)
ORDER BY 
    service.SR_Service_RecID DESC

Which produces:

Ticket #   Time Entry Day            First   Last    Time(s)
756065     2016-09-28 00:00:00.000   Mr.     Smith   1,000
756065     2016-09-28 00:00:00.000   Mr.     Smith   560

I'm looking for:

Ticket #   Time Entry Day            First   Last    Time(s)
756065     2016-09-28 00:00:00.000   Mr.     Smith   1,560

I've tried making this a part of a subquery, I think my issue is I'm having trouble wrapping my head around the logic of what I need to do to get the result I need.

Any assistance is greatly appreciated.

Upvotes: 0

Views: 292

Answers (2)

D.S.
D.S.

Reputation: 1413

The issue is with your joins to the SR_Team and SR_Team_Member tables. A Member can be on multiple teams. It appears you are attempting to check if the Member is on a particular set of Teams.

You mention you want one row per ticket, but a member can have multiple time entries per ticket for the same amount of time. So in some instances you will get multiple rows per ticket.

If you want entires were the SUM of all of the members time on a ticket exceeds 1800 seconds, you will need to SUM the DATEDIFF(), use a GROUP BY, Remove the DATEDIFF() > 1800 from the WHERE clause and add a HAVING clause after your GROUP BY.

This query will get you any individual time entries > 1800 seconds

    SELECT 
    service.SR_Service_RecID AS "Ticket #",
    convert(date,te.Date_Start) AS "Time Entry Day",
    m.first_name AS First,
    m.last_name AS Last,
    DATEDIFF(s, te.time_start, te.time_end) AS "Time (s)"
FROM 
    dbo.Time_Entry te
INNER JOIN dbo.Activity_Class activity ON activity.Activity_Class_RecID = te.Activity_Class_RecID
INNER JOIN dbo.SR_Service service ON te.SR_Service_RecID = service.SR_Service_RecID
INNER JOIN dbo.Member m ON m.Member_RecID = te.Member_RecID
WHERE  datediff(s,te.time_start, te.time_end) > 1800
AND activity.Description like 'T%'
AND service.Date_Entered >= dateadd(month, -1, getdate())
AND EXISTS (select *
            from dbo.SR_Team_Mbr tm
            inner join dbo.SR_Team t on tm.SR_Team_RecID = t.SR_Team_RecID and tm.Member_RecID = m.Member_RecID
            where (t.Description like '%c' or t.Description like '%d' or t.Description like '%e'))
ORDER BY service.SR_Service_RecID desc

This Query will get you Tickets where the SUM of all time entries for a member on a ticket > 1800 seconds

SELECT 
    service.SR_Service_RecID AS "Ticket #",
    convert(date,te.Date_Start) AS "Time Entry Day",
    m.first_name AS First,
    m.last_name AS Last,
    SUM(DATEDIFF(s, te.time_start, te.time_end))AS "Time (s)"
FROM 
    dbo.Time_Entry te
INNER JOIN dbo.Activity_Class activity ON activity.Activity_Class_RecID = te.Activity_Class_RecID
INNER JOIN dbo.SR_Service service ON te.SR_Service_RecID = service.SR_Service_RecID
INNER JOIN dbo.Member m ON m.Member_RecID = te.Member_RecID
WHERE  activity.Description like 'T%'
AND service.Date_Entered >= dateadd(month, -1, getdate())
AND EXISTS (select *
            from dbo.SR_Team_Mbr tm
            inner join dbo.SR_Team t on tm.SR_Team_RecID = t.SR_Team_RecID and tm.Member_RecID = m.Member_RecID
            where (t.Description like '%c' or t.Description like '%d' or t.Description like '%e'))
GROUP BY service.SR_Service_RecID, convert(date,te.Date_Start), m.First_Name, m.Last_Name
HAVING SUM(DATEDIFF(s, te.time_start, te.time_end)) > 1800
ORDER BY service.SR_Service_RecID desc

Upvotes: 0

techspider
techspider

Reputation: 3410

There are two things that are incorrect in your query:

  1. You didn't have an aggregate function in your SELECT statement.
  2. You have Time (s) column in your group by resulting in additional row

So, change your query as below rectifying those two issues:

SELECT
    service.SR_Service_RecID AS "Ticket #",
    te.Date_Start AS "Time Entry Day",
    m.first_name AS First,
    m.last_name AS Last,
    SUM(DATEDIFF(s, te.time_start, te.time_end)) AS "Time (s)"
FROM 
    dbo.Time_Entry te
LEFT JOIN  
    dbo.Activity_Class activity ON activity.Activity_Class_RecID = te.Activity_Class_RecID
LEFT JOIN 
    dbo.SR_Service service ON te.SR_Service_RecID = service.SR_Service_RecID
LEFT JOIN 
    dbo.Member m ON m.Member_RecID = te.Member_RecID
LEFT JOIN 
    dbo.SR_Team_Mbr member ON member.Member_RecID = M.Member_RecID
LEFT JOIN 
    dbo.SR_Team T ON T.SR_Team_RecID = member.SR_Team_RecID
WHERE 
    m.member_recID IN (SELECT M.Member_RecID
                       FROM Member M
                       LEFT JOIN dbo.SR_Team_Mbr member ON member.Member_RecID = M.Member_RecID
                       LEFT JOIN dbo.SR_Team T ON T.SR_Team_RecID = member.SR_Team_RecID
                       WHERE T.Description LIKE '%c'
                          OR T.Description LIKE '%d'
                          OR T.Description LIKE '%e')
    AND activity.Description LIKE 'T%'
    AND service.Date_Entered >= DATEADD(MONTH, -1, GETDATE())
GROUP BY 
    service.SR_Service_RecID, te.Date_Start,
    m.first_name, m.last_name
ORDER BY 
    service.SR_Service_RecID DESC

This MSDN article should give you complete insight into GROUP BY.

Upvotes: 1

Related Questions