sharonsql2013 (1/9/2014)
I need to display the following data but I want to for two seperate dates..One for the current month and another for last 3 months.
SELECT
Category ,
Sub_Category,
Incident_ID,
Time_ON
FROM Products
WHERE Count1 < 1
and Status = 'RESOLVED'
AND (TimeON > = dateadd(mm, datediff(mm,0, GETDATE()), 0) AND Time_Opened <= getdate())
Now I want the same data above for the same where clause except that the 3rd line in the where clausewill have a different range of dates..
Time_ON between dateadd(m, -3, dateadd(mm, datediff(mm,0, GETDATE()), 0)) and dateadd(mm, datediff(mm,0, GETDATE()), 0)
How can i show it , Will I need to write CTE's ? How should I join it?
You can probably do this a number of different ways. I'd go with two CTEs. In general something like:
;WITH CTE_CurrentMonth (Category, SubCategory, IncidentId, TimeOn)
AS
(
-- this month query here
),
CTE_PastThreeMonths (Category, SubCategory, IncidentId, TimeOn)
AS
(
-- three months here
)
SELECT COALESCE(cte1.Category, cte2.Category) AS Category,
COALESCE(cte1.SubCategory, cte2.SubCategory) AS SubCategory,
-- put rest of fields here
FROM CTE_CurrentMonth cte1
CROSS APPLY CTE_PastThreeMonths cte2;
You'll have to adapt & modify for your specific tables.
HTH,
Rob