SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display data


Display data

Author
Message
sharonsql2013
sharonsql2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 1265
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?
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40275 Visits: 19802
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Rob Taylor
Rob Taylor
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3694 Visits: 1616
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
sharonsql2013
sharonsql2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 1265
Thanks a lot.
I shall try that.
sharonsql2013
sharonsql2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 1265
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.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17007 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
sharonsql2013
sharonsql2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 1265
Really helpful.
Thank you
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search