August 19, 2011 at 2:37 pm
I have to get the average for open tickets during each month for the last 12 months.
First I have to find out if the ticket was open during that period.
I run my query with a variable and dump the data in a temp table and then query the result.
Is there a way to run this query in one shot?
Thank you for all the help
---------------------------------------------------------------------------------------
-- Declare the variable to be used.
DECLARE @MyCounter int,
@dtDate DATETIME
-- Initialize the variable.
SET @MyCounter = 0;
-- Test the variable to see if the loop is finished for the last 12 months.
WHILE (@MyCounter < 12)
BEGIN;
-- Initialize date to current end of month
SET @dtDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1-@MyCounter,0))
INSERT INTO TempTable(MMMYY, [YEAR],MONTHNUMBER,[ZONE], [AVG-DAYS],[COUNT],[DESC])
SELECT
CONVERT(varchar(3), @dtDate )+'-'+right(CONVERT(varchar(11),@dtDate ),2) as 'MMMYY',
YEAR (@dtDate) YEAR,
MONTH(@dtDate) 'MONTHNUMBER',
ZONE,
AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)) as 'AVG-DAYS',
COUNT(*) As [Count],
'KPI-XYZ' as [DESC]
FROM CTR_RECORDS
left outer join EVENTS on CTR_EVENT=EVT_CODE
WHERE CTR_SERVICECATEGORY='XYZ'
and (CTR_STATUS in ('CL', 'F', 'O') OR EVT_STATUS in ('C','R', 'Z'))
and ( ----Last Day of Previous Month ----Last Day of Current Month
( EVT_STATUS='C' AND CTR_UPDATED >DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0)) AND CTR_UPDATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
)
OR
( CTR_STATUS= 'CL' AND CTR_UPDATED >DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0)) AND CTR_UPDATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
)
OR
(EVT_STATUS in ('R','Z' ) AND CTR_CREATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
)
OR
( CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
)
)
group by ZONE
set @MyCounter =@MyCounter+1
END
Select * from TempTable
August 19, 2011 at 3:26 pm
Please have a look at the first link in my signature and post ready to use sample data together with your expected result.
For sure, there is a set based solution.
August 23, 2011 at 6:20 am
Thanks, Lutz.
I hope I did it right this time.
---Updatetime can be null then current datetime of the query runtime should be used
--Create the Data Table
CREATE TABLE Incidents (
Incident_ID int identity(1,1),
Zone nvarchar(1),
Status char(1),
CTR_CREATED datetime,
CTR_UPDATED datetime)
----
-- Insert some sample data into our original data table
INSERT INTO Incidents ( Incident_ID, Zone, Status, CTR_CREATED, CTR_UPDATED)
SELECT '43087', '2', 'C', '6/30/2010', '2/17/2011'
UNION ALL SELECT '43700', '2', 'C', '7/15/2010', '1/24/2011'
UNION ALL SELECT '44767', '2', 'C', '8/20/2010', '9/7/2010 '
UNION ALL SELECT '48172', '3', 'C', '12/2/2010', '1/3/2011 '
UNION ALL SELECT '48709', '4', 'O', '12/20/2010', NULL
UNION ALL SELECT '50214', '3', 'O', '1/19/2011', NULL
UNION ALL SELECT '50491', '4', 'O', '1/26/2011', NULL
UNION ALL SELECT '50697', '4', 'C', '2/2/2011', '6/8/2011'
UNION ALL SELECT '50886', '2', 'O', '2/8/2011', NULL
UNION ALL SELECT '52245', '3', 'C', '3/15/2011', '4/6/2011 '
UNION ALL SELECT '52720', '2', 'O', '3/29/2011', NULL
UNION ALL SELECT '54935', '2', 'C', '6/8/2011 ', '8/4/2011'
UNION ALL SELECT '55077', '4', 'C', '6/13/2011', '6/16/2011'
UNION ALL SELECT '56486', '2', 'O', '8/1/2011', '8/1/2011'
UNION ALL SELECT '42172', '2', 'C', '6/4/2010', '8/11/2011'
UNION ALL SELECT '42244', '2', 'C', '6/8/2010', '7/2/2010'
UNION ALL SELECT '42434', '3', 'C', '6/11/2010', '6/24/2010'
UNION ALL SELECT '42903', '2', 'C', '6/28/2009', '11/9/2010'
--CREATE LINKING TABLE
CREATE TABLE EVENTS(
EVT_ID int identity(1,1),
EVT_CODE int,
EVT_STATUS char(1)
---- Insert some sample data into our lINK table
INSERT INTO EVENTS(EVT_ID,EVT_CODE,EVT_STATUS)
SELECT '10025','43700','C'
UNION ALL SELECT '10026','44767','C'
UNION ALL SELECT '10027','48172','C'
UNION ALL SELECT '10028','48709','R'
UNION ALL SELECT '10029','50214','R'
UNION ALL SELECT '10030','50491','R'
UNION ALL SELECT '10031','50697','C'
UNION ALL SELECT '10032','50886','R'
UNION ALL SELECT '10033','52245','C'
UNION ALL SELECT '10034','52720','R'
UNION ALL SELECT '10035','54935','C'
UNION ALL SELECT '10036','55077','C'
UNION ALL SELECT '10037','56486','Z'
UNION ALL SELECT '10038','42172','C'
UNION ALL SELECT '10039','42244','C'
UNION ALL SELECT '10040','42434','C'
UNION ALL SELECT '10041','42903','C'
UNION ALL SELECT '10042','42905','O'
---Results should look like this
----------------------------------------------------------------------------------------------------------
| Year | Month | Zone | Avarage | Count |
----------------------------------------------------------------------------------------------------------
| 2010 | December | 1 | 6.35 | 5 |
----------------------------------------------------------------------------------------------------------
| 2010 | December | 2 | 3.39 | 10 |
----------------------------------------------------------------------------------------------------------
| 2011 | January | 3 | 5.51 | 4 |
----------------------------------------------------------------------------------------------------------
| 2011 | January | 4 | 7.89 | 10 |
----------------------------------------------------------------------------------------------------------
| 2011 | February | 1 | 6.9 | 30 |
----------------------------------------------------------------------------------------------------------
| 2011 | February | 2 | 89.9 | 2 |
----------------------------------------------------------------------------------------------------------
| 2011 | February | 3 | 15.4 | 25 |
----------------------------------------------------------------------------------------------------------
| 2011 | February | 4 | 12.45 | 1 |
----------------------------------------------------------------------------------------------------------
| 2011 | March | 1 | 12.22 | 15 |
----------------------------------------------------------------------------------------------------------
| 2011 | March | 3 | 5.25 | 21 |
----------------------------------------------------------------------------------------------------------
| 2011 | March | 4 | 19.32 | 10 |
----------------------------------------------------------------------------------------------------------
| 2011 | April | 3 | 7.45 | 2 |
----------------------------------------------------------------------------------------------------------
| 2011 | May | 2 | 6.33 | 1 |
----------------------------------------------------------------------------------------------------------
| 2011 | June | 1 | 5.26 | 1 |
----------------------------------------------------------------------------------------------------------
| 2011 | July | 4 | 10.2 | 2 |
----------------------------------------------------------------------------------------------------------
| 2011 | August | 1 | 21.15 | 0 |
----------------------------------------------------------------------------------------------------------
| 2011 | August | 2 | 10.9 | 2
/************************************
To get this result above, my current solution is
to run a query with a variable and dump the data in a temp table and then query the result.
this but I believe there could be a better solution
*/
---------------------------------------------------------------------------------------
-- Declare the variable to be used.
DECLARE @MyCounter int,
@dtDate DATETIME
-- Initialize the variable.
SET @MyCounter = 0;
-- Test the variable to see if the loop is finished for the last 12 months.
WHILE (@MyCounter < 12)
BEGIN;
-- Initialize date to current end of month
SET @dtDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1-@MyCounter,0))
INSERT INTO TempTable(MMMYY, [YEAR],MONTHNUMBER,[ZONE], [AVG-DAYS],[COUNT],[DESC])
SELECT
CONVERT(varchar(3), @dtDate )+'-'+right(CONVERT(varchar(11),@dtDate ),2) as 'MMMYY',
YEAR (@dtDate) YEAR,
MONTH(@dtDate) 'MONTHNUMBER',
ZONE,
AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)) as 'AVG-DAYS',
COUNT(*) As [Count],
'KPI-XYZ' as [DESC]
FROM INCIDENTS left outer join EVENTS on Incident_ID=EVT_CODE
WHERE CTR_SERVICECATEGORY='XYZ'
and (CTR_STATUS in ('CL', 'F', 'O') OR EVT_STATUS in ('C','R', 'Z'))
and ( ----Last Day of Previous Month ----Last Day of Current Month
( EVT_STATUS='C' AND CTR_UPDATED >DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0)) AND CTR_UPDATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
)
OR
( CTR_STATUS= 'CL' AND CTR_UPDATED >DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0)) AND CTR_UPDATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
)
OR
(EVT_STATUS in ('R','Z' ) AND CTR_CREATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
)
OR
( CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
)
)
group by ZONE
set @MyCounter =@MyCounter+1
END
select * from TempTable
August 23, 2011 at 6:35 am
Yes, there almost certainly is a better solution. But you haven't posted any DDL or data for EVENTS, so we can't offer you any tested code. A good place for you to start is to read about Numbers (or Tally) tables. You can use one of those to get the twelve months you need, instead of looping through each month.
John
August 23, 2011 at 7:08 am
Substitute those variables for a 12-row table:
SELECT
dtDate,
[MMMYY] = CONVERT(varchar(3), dtDate )+'-'+right(CONVERT(varchar(11),dtDate ),2),
[YEAR] = YEAR(dtDate),
[MONTHNUMBER] = MONTH(dtDate),
lowerbound = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dtDate),0)),
upperbound = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dtDate)+1,0))
INTO #Dates
FROM (
SELECT dtDate = DATEADD(S,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1-n.n,0)) -- NOW HAS TO BE LESS THAN
FROM (
SELECT 0 AS n UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11) n
) d
--------------------------------------------------------
SELECT
d.[MMMYY],
d.[YEAR],
d.[MONTHNUMBER],
ZONE,
[AVG-DAYS] = AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)),
[Count] = COUNT(*),
[DESC] = 'KPI-XYZ'
FROM INCIDENTS
CROSS JOIN (#Dates) d
left outer join [EVENTS]
on CTR_EVENT = EVT_CODE
WHERE CTR_SERVICECATEGORY='XYZ'
and (CTR_STATUS in ('CL', 'F', 'O') OR EVT_STATUS in ('C','R', 'Z'))
and ( ----Last Day of Previous Month ----Last Day of Current Month
(EVT_STATUS='C' AND CTR_UPDATED > d.lowerbound AND CTR_UPDATED <= d.upperbound)
OR (CTR_STATUS= 'CL' AND CTR_UPDATED > d.lowerbound AND CTR_UPDATED <= d.upperbound)
)
OR (EVT_STATUS in ('R','Z' ) AND CTR_CREATED <= d.upperbound)
OR (CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <= d.upperbound)
)
group by ZONE
Next - get rid of the crazy "subtract a second" date arithmetic.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 23, 2011 at 7:53 am
I just added that.
Thank you.
August 23, 2011 at 8:06 am
I am getting an error.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ')'.
The syntax seems to be correct
August 23, 2011 at 8:17 am
uciltas-924976 (8/23/2011)
I am getting an error.Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ')'.
The syntax seems to be correct
Without seeing the query it's hard to tell.
I think there's a "opinion mismatch" between SQL Server and you regarding a "correct syntax"
August 23, 2011 at 8:21 am
uciltas-924976 (8/23/2011)
I am getting an error.Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ')'.
The syntax seems to be correct
The syntax was definitely incorrect, but it's my mistake, not yours. This corrects it:
-----------------------------------------------------------------
WHERE CTR_SERVICECATEGORY = 'XYZ' AND (CTR_STATUS IN ('CL', 'F', 'O') OR EVT_STATUS IN ('C','R', 'Z'))
and ( ----Last Day of Previous Month ----Last Day of Current Month
(EVT_STATUS='C' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)
OR
(CTR_STATUS= 'CL' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)
OR
(EVT_STATUS in ('R','Z' ) AND CTR_CREATED <= d.upperbound)
OR
(CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <= d.upperbound)
)
GROUP ZONE
-------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 23, 2011 at 8:43 am
It seems like it doesnt like the parenthesis around (#Dates)
August 23, 2011 at 9:22 am
How can I handle AVG_DAYS when is CTR_UPDATED null.
Because if it is nul:
l for the current month we have to use current date time.
previous months data we have to use previous months' upper bound
[AVG-DAYS] = AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)),
August 23, 2011 at 9:31 am
I have one problem here:
[AVG_DAYS] = AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)),
How do we handle [AVG-DAYS] when CTR_UPDATED is null ?
when it is current month data CTR_UPDATED will be getdate()
when previous month data it has to be previous months upper bound
'
'
for May 2011 month data it has to be upper bound of May 2011.
August 23, 2011 at 9:34 am
-same post multiple times; sorry
August 23, 2011 at 9:39 am
SELECT
d.[MMMYY],
d.[YEAR],
d.[MONTHNUMBER],
ZONE,
[AVG-DAYS] = AVG(DATEDIFF(DAY, CTR_CREATED, ISNULL(CTR_UPDATED, CASE WHEN d.dtDate = 0 THEN GETDATE() ELSE d.upperbound END))),
[Count] = COUNT(*),
[DESC] = 'KPI-XYZ'
FROM INCIDENTS
CROSS JOIN #Dates d
LEFT JOIN [EVENTS]
ON CTR_EVENT = EVT_CODE
WHERE CTR_SERVICECATEGORY = 'XYZ' AND (CTR_STATUS IN ('CL', 'F', 'O') OR EVT_STATUS IN ('C','R', 'Z'))
AND ( ----Last Day of Previous Month ----Last Day of Current Month
(EVT_STATUS='C' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)
OR
(CTR_STATUS= 'CL' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)
OR
(EVT_STATUS in ('R','Z' ) AND CTR_CREATED <= d.upperbound)
OR
(CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <= d.upperbound)
)
GROUP BY ZONE
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 23, 2011 at 10:20 am
Thank you very much.
I will have to double check my numbers but this seems to be the solution I was looking for.
If I wanted to create a view for this how can I handle the temp table (#Dates)
Because it will say "the table already exists" if I don't drop it before running the view.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy