Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display data Expand / Collapse
Author
Message
Posted Thursday, January 9, 2014 3:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 215, Visits: 639
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?
Post #1529593
Posted Thursday, January 9, 2014 3:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 3,374, Visits: 7,300
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.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1529598
Posted Thursday, January 9, 2014 3:34 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 1,166, Visits: 1,192
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
Post #1529599
Posted Thursday, January 9, 2014 3:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 215, Visits: 639
Thanks a lot.
I shall try that.
Post #1529602
Posted Saturday, January 11, 2014 8:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 215, Visits: 639
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.
Post #1530038
Posted Sunday, January 12, 2014 6:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:45 PM
Points: 3,617, Visits: 5,237
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1530172
Posted Monday, January 13, 2014 1:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 215, Visits: 639
Really helpful.
Thank you
Post #1530446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse