August 3, 2006 at 9:42 am
Hi All:
I have a rather tricky SQL to write. Depending on the value of an INNER JOIN, I need to SUM the data in one value or another one. I tried this to no avail. Thanks for any help or suggestions.
Bill
SELECT A.Incident_Id AS Incident#,
CAST(SUM(B.Billing_Time) AS decimal(8,2)) AS Incident_Bill,
CAST(SUM(B.Absorbed_Time) AS decimal(8,2)) AS Incident_Abs,
CAST(SUM(B.Actual_Time) AS decimal(8,2)) AS Incident_Actual,
CAST(SUM(E.Billing_time) AS decimal(8,2)) AS Solmon_Bill,
CAST(SUM(D.Billing_Time) AS decimal(8,2)) As Task_Bill,
CAST(SUM(D.Absorbed_Time) AS decimal(8,2)) As Task_Abs
FROM Incident as A
INNER JOIN Inc_HistoryB ON A.Incident_Id = B.Incident_Id WHERE B.Event_Type_ID <> 1810
INNER JOIN Task C ON A.Incident_Id = C.Entity_ID
INNER JOIN Task_History D ON C.Task_ID = D.Task_ID
INNER JOIN Inc_HistoryE ON A.Incident_Id = E.Incident_Id WHERE E.Event_Type_ID = 1810
GROUP BY A.Incident_Id
ORDER by A.Incident_Id
August 3, 2006 at 9:56 am
Use CASE ... WHEN for conditionals
For example, if you only want to include B.Billing_Time in the SUM() if B.Event_Type_ID <> 1810, then use:
SUM(
CASE B.Event_Type_ID
WHEN 1810 THEN 0 -- zero if equal to 1810
ELSE B.Billing_Time -- Otherwise the billing time
END
)
August 3, 2006 at 9:56 am
Try joining Inc_History just once without the condition, and use a CASE statement to separate the two types of values to sum:
To get the Incident_Bill time:
CASE B.Event_Type_ID WHEN 1810 THEN 0 ELSE B.Billing_Time END
To get the Solomon_Bill time:
CASE B.Event_Type_ID WHEN 1810 THEN B.Billing_Time ELSE 0 END
----
SELECT
A.Incident_Id AS Incident, CONVERT(decimal (8,2),SUM(CASE B.Event_Type_ID WHEN 1810 THEN 0 ELSE B.Billing_Time END)) AS Incident_Bill, CONVERT(decimal (8,2),SUM(B.Absorbed_Time)) AS Incident_Abs, CONVERT(decimal (8,2),SUM(B.Actual_Time)) AS Incident_Actual, CONVERT(decimal (8,2),SUM(CASE B.Event_Type_ID WHEN 1810 THEN B.Billing_Time ELSE 0 END)) AS Solmon_Bill, CONVERT(decimal (8,2),SUM(D.Billing_Time)) As Task_Bill, CONVERT(decimal (8,2),SUM(D.Absorbed_Time)) As Task_Abs FROM Incident as A INNER JOIN Inc_History B ON A.Incident_Id = B.Incident_Id INNER JOIN Task C ON A.Incident_Id = C.Entity_ID INNER JOIN Task_History D ON C.Task_ID = D.Task_ID GROUP BY A.Incident_Id ORDER BY A.Incident_Id
-Eddie
Eddie Wuerch
MCM: SQL
August 3, 2006 at 9:58 am
You could restructure more like this:
SELECT A.Incident_Id AS Incident#,
CAST(SUM(B.Billing_Time) AS decimal(8,2)) AS Incident_Bill,
CAST(SUM(B.Absorbed_Time) AS decimal(8,2)) AS Incident_Abs,
CAST(SUM(B.Actual_Time) AS decimal(8,2)) AS Incident_Actual,
CAST(SUM(E.Billing_time) AS decimal(8,2)) AS Solmon_Bill,
CAST(SUM(D.Billing_Time) AS decimal(8,2)) As Task_Bill,
CAST(SUM(D.Absorbed_Time) AS decimal(8,2)) As Task_Abs
FROM Incident as A
INNER JOIN ( select billing_time, absorbed_time, actual_time, incident_id
from Inc_History
where B.Event_Type_ID 1810
) B
ON A.Incident_Id = B.Incident_Id
INNER JOIN Task C
ON A.Incident_Id = C.Entity_ID
INNER JOIN Task_History D
ON C.Task_ID = D.Task_ID
INNER JOIN Inc_History E
ON A.Incident_Id = E.Incident_Id
WHERE E.Event_Type_ID = 1810
GROUP BY A.Incident_Id
ORDER by A.Incident_Id
August 3, 2006 at 10:59 am
Thank you all for your help and assistance. It worked out GREAT ;-))
Bill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply