csupport query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

csupport query

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-10-26 : 11:54:28

Every friday we have a meeting and we have a report which shows us the number of incidents created closed open in the last week and the last 30 days. I have most of the query...but some how i missed the closed column and can't back track my self...can someone help. The Query is below!! DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @STLYStartDate datetime
DECLARE @STLYEndDate datetime
SET @StartDate = CONVERT(VARCHAR(30),Getdate(),101)
SET @EndDate = DATEADD(DAY, 0, @StartDate)
SET @StartDate = DATEADD(DAY, - 7, @EndDate)
SET @STLYEndDate = @StartDate
SET @STLYStartDate = DATEADD(DAY,-7,@STLYEndDate)

-- select @StartDate,@EndDate,@STLYEndDate,@STLYStartDate
SELECT GROUPS.GROUP_NAME,
SUM( CASE WHEN INCIDENTS.DT_CREATED BETWEEN @StartDate and @EndDate
THEN 1 ELSE 0 END )AS CREATED,
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL THEN 1 ELSE 0 END) AS [OPEN],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day,-30, @EndDate)
THEN 1 ELSE 0 END) AS [OPEN30],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day,-90, @EndDate)
THEN 1 ELSE 0 END) AS [OPEN90],
/*COUNT(LastINCIDENTS.DT_CREATED)*/
SUM( CASE WHEN INCIDENTS.DT_CREATED BETWEEN @STLYStartDate and @STLYEndDate
THEN 1 ELSE 0 END )AS STLYCREATED,
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL THEN 1 ELSE 0 END) AS [STLYOPEN],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day,-30, @STLYEndDate)
THEN 1 ELSE 0 END) AS [STLYOPEN30],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day, -90,@STLYEndDate)
THEN 1 ELSE 0 END) AS [STLYOPEN90]

FROM GROUPS INNER JOIN
INCIDENTS ON INCIDENTS.ID_GROUP = GROUPS.ID
INNER JOIN
REPS ON INCIDENTS.ID_ASSIGNEE = REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC
RENT%'

GROUP BY GROUP_NAME ORDER BY GROUP_NAME

just having one of those days today that nothing is going right

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-10-26 : 14:31:31

anyhelp please?

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-11-01 : 10:26:47

i really could use some help with this query am really lost

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-01 : 11:19:52

It might help if you give some sample data and expected results

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-11-01 : 11:30:39

thanks so much for responding here is some sample data...so right now it is creating these five groups but i need it to create 3 more groups based on the data

GROUP_NAME CREATED OPEN CLOSED OPEN30 OPEN90 (No column name) STLYOPEN (No column name) (No column name)
MAC Application Support 19 82 82 61 43 19 82 59 37
MAC Help Desk 12 10 10 3 1 14 10 2 1
MAC Network System Support 17 29 29 17 8 23 29 15 8
MAC Rental Season Helpdesk 0 1 1 1 0 0 1 1 0
MAC Rental Season Merchandising Team 0 5 5 5 0 0 5 5 0

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-11-01 : 11:31:32

i think the data didn't get posted right
the groups name column has all the names under it and created open closed etc are what the numbers correspondt to

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-08 : 04:35:54

<<
but some how i missed the closed column and can't back track my self
>>

What did you mean by it?

Madhivanan

Failing to plan is Planning to fail

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-11-08 : 09:04:55

I wish there was a way to attach a report here os it would be easier for me to explain the problem i'm having.
this is the query

DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @LWStartDate datetime
DECLARE @LWEndDate datetime

SET @StartDate = CONVERT(VARCHAR(30),Getdate(),101)
SET @EndDate = DATEADD(DAY, 1, @StartDate)
SET @StartDate = DATEADD(DAY, - 7, @EndDate)
SET @LWEndDate = @StartDate
SET @LWStartDate = DATEADD(DAY,-7,@LWEndDate)

SELECT GROUPS.GROUP_NAME,
SUM( CASE WHEN INCIDENTS.DT_CREATED BETWEEN @StartDate and @EndDate
THEN 1 ELSE 0 END )AS CREATED,
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL THEN 1 ELSE 0 END) AS [OPEN],
SUM(CASE WHEN INCIDENTS.DT_CLOSED BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) AS [CLOSED],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND
DT_CREATED<DATEADD(day,-30, @EndDate)
THEN 1 ELSE 0 END) AS [OPEN30],

/*COUNT(LastINCIDENTS.DT_CREATED)*/
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND
DT_CREATED<DATEADD(day,-90, @EndDate)
THEN 1 ELSE 0 END) AS [OPEN90],

SUM( CASE WHEN INCIDENTS.DT_CREATED BETWEEN @LWStartDate and @LWEndDate
THEN 1 ELSE 0 END )AS LWCREATED,

SUM(CASE WHEN (INCIDENTS.DT_CLOSED IS NULL or INCIDENTS.DT_CLOSED>@LWEndDate) and INCIDENTS.DT_CREATED<@LWEndDate THEN 1 ELSE 0 END) AS [LWOPEN],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND
DT_CREATED<DATEADD(day,-30, @LWEndDate)
THEN 1 ELSE 0 END) AS [LWOPEN30],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND
DT_CREATED<DATEADD(day,-7, @LWEndDate)
THEN 1 ELSE 0 END) AS [LWCLOSED30],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day,
-90,@LWEndDate)
THEN 1 ELSE 0 END) AS [LWOPEN90]

FROM GROUPS INNER JOIN
INCIDENTS ON INCIDENTS.ID_GROUP = GROUPS.ID
INNER JOIN
REPS ON INCIDENTS.ID_ASSIGNEE = REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC Rent%'

GROUP BY GROUP_NAME ORDER BY GROUP_NAME

and these are the results
GROUP_NAME CREATE OPEN CLOSED OPEN30 OPEN90 LWCREATED LWOPEN LWOPEN30 LWCLOSED30 LWOPEN90
MAC Application Support 13 80 16 61 44 19 83 57 67 42
MAC Help Desk 8 8 8 3 0 14 8 2 3 0
MAC Network System Support 25 36 21 18 7 20 32 13 20 7

does this make sense now?

now i know logically what i need this report to be broken down to but don't know where to start

IF {GROUPS.GROUP_NAME} = "MAC Application Support" THEN
IF {INCIDENTS.ID_STATUS} = 2 and {@Project Hours} > 0 and {@Project Hours} < 48 THEN {GROUPS.GROUP_NAME} + " - Rapid Response (< 2 days)"
ELSE IF {INCIDENTS.ID_STATUS} = 2 and ({REPS.LNAME}<>"Group" and {REPS.LNAME}<>"Hicks") THEN {GROUPS.GROUP_NAME} + " - Rapid Response (< 2 days)"
ELSE IF {INCIDENTS.ID_STATUS} = 2 or ({REPS.LNAME}<>"Group" and {REPS.LNAME}<>"Hicks") Then {GROUPS.GROUP_NAME} + " - Developers"
ELSE {GROUPS.GROUP_NAME} + " - New Tickets & Backlog"

ELSE {GROUPS.GROUP_NAME}

Rapid Response
(LNAME=’Hicks’ or (closed-open < 48 hours)) AND CLOSED

Developers
LNAME<>’Group’ and LNAME<>’Hicks’

New and Backlog
Everything else

i hope you can help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources