January 9, 2014 at 3:11 pm
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?
January 9, 2014 at 3:26 pm
Could you provide DDL, sample data and expected results in the form of insert statements? You can read on how to do it in the article linked in my signature.
January 9, 2014 at 3:34 pm
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
January 9, 2014 at 3:54 pm
Thanks a lot.
I shall try that.
January 11, 2014 at 8:41 am
Guess I should be a little more clear.
I am looking to count the # of requests for the current month and last 3 months(Static).
But don't know how COALesce will be useful to join the count part...
The output should look like:
Configuration Items CountRequestsJan CountRequestOld
Intel 20 10
AMD 3 5
With CTECurrent
As
(
SELECT
P.Configuration_Item,
P.TotalDays,
COUNT(P.Requests) AS CountRequestsJan
FROM Products P
WHERE
(P.Configuration_Item Like '%Intel%' OR Configuration_Item Like '%AMD%’)
And
P.TotalDays between '2014-01-01 00:00:00.0000000' and GETDATE()
Group By P.Configuration_Item,
P.TotalDays
)
, CTEPast
As
(
SELECT
P.Configuration_Item,
P.TotalDays,
COUNT(Tickets) AS CountRequestOld
FROM Products P
WHERE
(P.Configuration_Item Like '%Intel%' OR P.Configuration_Item Like '%AMD%’)
And
P.TotalDays between '2014-01-01 00:00:00.0000000' and '2013-10-01 00:00:00.0000000’
Group By P.Configuration_Item,
P.TotalDays
)
***** Left or Right Join messes up the count.
January 12, 2014 at 6:47 pm
Sharon,
I think you've been around long enough to know that you get better help by posting DDL and consumable sample data. But I'm feeling generous on this Monday morning so I'll set it up for you.
DECLARE @Current_Month DATETIME = '2014-01-14';
WITH SampleData (Configuration_Item, Request_Date) AS
(
SELECT 'INTEL', CAST('2013-06-05' AS DATETIME)
UNION ALL SELECT 'INTEL', '2013-10-01'
UNION ALL SELECT 'INTEL', '2013-10-12'
UNION ALL SELECT 'INTEL', '2013-11-05'
UNION ALL SELECT 'INTEL', '2013-12-04'
UNION ALL SELECT 'INTEL', '2013-12-11'
UNION ALL SELECT 'INTEL', '2014-01-05'
UNION ALL SELECT 'AMD', '2013-10-01'
UNION ALL SELECT 'AMD', '2013-10-12'
UNION ALL SELECT 'AMD', '2013-11-05'
UNION ALL SELECT 'AMD', '2013-12-04'
UNION ALL SELECT 'AMD', '2013-12-05'
UNION ALL SELECT 'AMD', '2013-12-15'
UNION ALL SELECT 'AMD', '2013-12-18'
UNION ALL SELECT 'AMD', '2014-01-05'
),
ConvertDaystoMonths AS
(
SELECT *
FROM SampleData a
CROSS APPLY
(
SELECT rd=DATEADD(month, DATEDIFF(month, 0, Request_Date), 0)
,cd=DATEADD(month, DATEDIFF(month, 0, @Current_Month), 0)
) b
)
SELECT Configuration_Item
,CurrentRequests=(
SELECT COUNT(*)
FROM ConvertDaystoMonths b
WHERE a.Configuration_Item = b.Configuration_Item AND
rd = cd
)
,PriorRequests=(
SELECT COUNT(*)
FROM ConvertDaystoMonths b
WHERE a.Configuration_Item = b.Configuration_Item AND
rd BETWEEN DATEADD(month, -3, cd) AND DATEADD(month, -1, cd)
)
FROM ConvertDaystoMonths a
GROUP BY Configuration_Item;
The basic idea is to group by your configuration items and perform 2 correlated sub-queries on your needed date ranges. Note how I have converted the request dates to the first of each month.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 13, 2014 at 1:35 pm
Really helpful.
Thank you
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply