Open tickets at begining of the month

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 Programming

Open tickets at begining of the month

Author

Topic

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-25 : 07:59:16

Hi
We have a call logging database , where we log technical queries, these can stay open for long periods say 6 months or can be as short as a couple of weeks , we would like to see the trend to show at the beginning of every month of each year the count of how many tickets are still open

The table has a number of fields but typically looking at OPENDATE, CLOSEDATE, REFERENCE , if a ticket is still open then the default closedate is 1900-01-01 , if it is closed it has a closedate in format 2011-01-02 .




OPENDATE                CLOSEDATE               REFERENCE
2010-12-15 00:00:00.000	2011-02-08 00:00:00.000	GTS11171
2011-02-03 00:00:00.000	1900-01-01 00:00:00.000	GTS11186
2009-09-21 00:00:00.000	1900-01-01 00:00:00.000	GTS11104
2010-11-12 00:00:00.000	1900-01-01 00:00:00.000	GTS11158
2011-01-27 00:00:00.000	2011-02-11 00:00:00.000	GTS11184
2011-02-24 00:00:00.000	1900-01-01 00:00:00.000	GTS11191
2011-02-24 00:00:00.000	1900-01-01 00:00:00.000	GTS11192
2011-02-07 00:00:00.000	1900-01-01 00:00:00.000	GTS11188
2011-02-24 00:00:00.000	1900-01-01 00:00:00.000	GTS11193


Here is my current query 

SELECT 
MONTH(OPENDATE) as month2,
YEAR(OPENDATE) as year2,
COUNT(REFERENCE) As Total
FROM suppdeskdev.F0007_SUPEVENT
WHERE (MONTH (CLOSEDATE) > MONTH (OPENDATE) 
or YEAR(CLOSEDATE) > YEAR(OPENDATE)) or CLOSEDATE='1900-01-01'
GROUP BY YEAR(OPENDATE) , MONTH(OPENDATE)
ORDER BY YEAR(OPENDATE) Asc,MONTH(OPENDATE) Asc

example of current output is , this is the output i want but the totals are not correct .

Month    Year   Total
7	2010	4
8	2010	5
9	2010	1
10	2010	2
11	2010	13
12	2010	5
1	2011	10
2	2011	7

, i know for example that at the 1st Feb 2011 i have around 16 rows that have a closedate of 1900-01-01 , i think i am nearly there but cannot see what i have done wrong any help really appreciated

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-25 : 08:45:23

post the desired output as per your sample table information listed above......

Cheers
MIK

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-25 : 09:22:20

Hi Mik , my desired output is in the same format as above , the only difference is the value for the total columns, i have 186 rows of data , would you want me to show you that data then manually work out the totals per month per year to show what the total column output should be ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 09:27:37

can you based on the sample data that you have posted in your first thread and show us the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-25 : 10:41:06

SELECT
YEAR(OPENDATE) as [year],
MONTH(OPENDATE) as [month],
COUNT(*) As Total
FROM suppdeskdev.F0007_SUPEVENT
WHERE CLOSEDATE > '19000101'
GROUP BY YEAR(OPENDATE), MONTH(OPENDATE)
ORDER BY YEAR(OPENDATE), MONTH(OPENDATE)

N 56°04'39.26"
E 12°55'05.63"

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-25 : 10:49:01

Hi here is the data set and the expected output

The thinking is , go through data to see how many rows have closedate either = 1900-01-01 ( not closed ) or their closedate is greater than that date ( so on the 1st of December how many calls have closedate='1900-01-01' or closedate is > 2010-01-12 .. group all this by 1st of every month hope this makes sense



OpenDate	CloseDate	Reference
24/02/2011	01/01/1900	GTS11191
24/02/2011	01/01/1900	GTS11192
23/02/2011	01/01/1900	GTS11189
23/02/2011	01/01/1900	GTS11190
07/02/2011	01/01/1900	GTS11187
07/02/2011	01/01/1900	GTS11188
03/02/2011	01/01/1900	GTS11186
02/02/2011	21/02/2011	GTS11185
27/01/2011	11/02/2011	GTS11184
25/01/2011	01/01/1900	GTS11182
25/01/2011	01/01/1900	GTS11181
25/01/2011	01/01/1900	GTS11183
21/01/2011	08/02/2011	GTS11180
18/01/2011	01/01/1900	GTS11179
17/01/2011	21/02/2011	GTS11178
14/01/2011	01/01/1900	GTS11175
14/01/2011	19/01/2011	GTS11176
12/01/2011	07/02/2011	GTS11173
12/01/2011	22/02/2011	GTS11174
07/01/2011	14/01/2011	GTS11172
15/12/2010	08/02/2011	GTS11171
10/12/2010	08/02/2011	GTS11170
03/12/2010	01/01/1900	GTS11169
03/12/2010	01/01/1900	GTS11168
02/12/2010	08/02/2011	GTS11167
22/11/2010	08/02/2011	GTS11166
19/11/2010	22/12/2010	GTS11165
19/11/2010	22/02/2011	GTS11164
18/11/2010	01/01/1900	GTS11163
15/11/2010	17/12/2010	GTS11161
15/11/2010	11/02/2011	GTS11162
12/11/2010	01/01/1900	GTS11159
12/11/2010	01/01/1900	GTS11158
12/11/2010	16/12/2010	GTS11160
12/11/2010	17/12/2010	GTS11157


year 	month	            total
2010	Nov	             10
	Dec	             11
2011	Jan	             21
	Feb	             17

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-25 : 10:56:42

[code]DECLARE @Sample TABLE
(
OpenDate DATETIME NOT NULL,
CloseDate DATETIME NOT NULL,
Reference CHAR(8) NOT NULL
)

INSERT @Sample
(
OpenDate,
CloseDate,
Reference
)
SELECT '20101215', '20110208', 'GTS11171' UNION ALL
SELECT '20110203', '19000101', 'GTS11186' UNION ALL
SELECT '20090921', '19000101', 'GTS11104' UNION ALL
SELECT '20101112', '19000101', 'GTS11158' UNION ALL
SELECT '20110127', '20110211', 'GTS11184' UNION ALL
SELECT '20110224', '19000101', 'GTS11191' UNION ALL
SELECT '20110224', '19000101', 'GTS11192' UNION ALL
SELECT '20110207', '19000101', 'GTS11188' UNION ALL
SELECT '20110224', '19000101', 'GTS11193'

-- Solution by Peso
;WITH cteCalendar(FromMonth, ToMonth)
AS (
SELECT MIN(DATEADD(MONTH, DATEDIFF(MONTH, 0, OpenDate), 0)) AS FromMonth,
MAX(DATEADD(MONTH, DATEDIFF(MONTH, 0, CloseDate), 0)) AS ToMonth
FROM @Sample

UNION ALL

SELECT DATEADD(MONTH, 1, FromMonth) AS FromMonth,
ToMonth
FROM cteCalendar
WHERE FromMonth <= ToMonth
)
SELECT c.FromMonth AS StartOfMonth,
f.Items AS OpenCases
FROM cteCalendar AS c
OUTER APPLY (
SELECT COUNT(*) AS Items
FROM @Sample AS s
WHERE s.OpenDate <= c.FromMonth
AND (
s.CloseDate > c.FromMonth
OR s.CloseDate = '19000101'
)
) AS f(Items)
ORDER BY c.FromMonth[/code]

N 56°04'39.26"
E 12°55'05.63"

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-25 : 12:13:49

wow PESO !!! , thanks for a quick reply ... is the code valid for sql2000 as i get some errors
Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'WITH'.
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'OUTER'.
Msg 156, Level 15, State 1, Line 49
Incorrect syntax near the keyword 'AS'.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-25 : 14:35:55

No, that will work on SQL 2005 and onwards....

I know that you your desired output format is same as you mentioned in your original post. However I want you to manually fill in the Correct Count Information as per given sample data. So that we can understand how exactly you want your query should Count and to display the result set accordingly. We don't need All 186 Rows .. just few rows (10) and the desired result for them.

Any how check the below query if this is what you are looking for

select YEAR(OpenDate),datename(MONTH,MONTH(OpenDate)),COUNT(Reference)
from <YourTableName>
Where CloseDate<=OpenDate
group by YEAR(OpenDate),datename(MONTH,MONTH(OpenDate))

This will give you all Tickets which are not yet SOLD, Or still Open

Cheers
MIK

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-03-01 : 04:27:33

Hi Mik, unfortunately your query doesnt give me the correct totals ;

here is my data and the expected results, one of the key points is that the total for the month can contain data from previous months , e.g i f i had a call opened in November , and it did not close until February then it will show in Nov, Dec and Jan figures


OpenDate	CloseDate	Reference
24/02/2011	01/01/1900	GTS11191
24/02/2011	01/01/1900	GTS11192
23/02/2011	01/01/1900	GTS11189
23/02/2011	01/01/1900	GTS11190
07/02/2011	01/01/1900	GTS11187
07/02/2011	01/01/1900	GTS11188
03/02/2011	01/01/1900	GTS11186
02/02/2011	21/02/2011	GTS11185
27/01/2011	11/02/2011	GTS11184
25/01/2011	01/01/1900	GTS11182
25/01/2011	01/01/1900	GTS11181
25/01/2011	01/01/1900	GTS11183
21/01/2011	08/02/2011	GTS11180
18/01/2011	01/01/1900	GTS11179
17/01/2011	21/02/2011	GTS11178
14/01/2011	01/01/1900	GTS11175
14/01/2011	19/01/2011	GTS11176
12/01/2011	07/02/2011	GTS11173
12/01/2011	22/02/2011	GTS11174
07/01/2011	14/01/2011	GTS11172
15/12/2010	08/02/2011	GTS11171
10/12/2010	08/02/2011	GTS11170
03/12/2010	01/01/1900	GTS11169
03/12/2010	01/01/1900	GTS11168
02/12/2010	08/02/2011	GTS11167
22/11/2010	08/02/2011	GTS11166
19/11/2010	22/12/2010	GTS11165
19/11/2010	22/02/2011	GTS11164
18/11/2010	01/01/1900	GTS11163
15/11/2010	17/12/2010	GTS11161
15/11/2010	11/02/2011	GTS11162
12/11/2010	01/01/1900	GTS11159
12/11/2010	01/01/1900	GTS11158
12/11/2010	16/12/2010	GTS11160
12/11/2010	17/12/2010	GTS11157


year 	month	            total
2010	Nov	             10
	Dec	             11
2011	Jan	             21
	Feb	             17

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-01 : 16:48:57

A SQL Server 2000 solution

DECLARE	@Sample TABLE
	(
		OpenDate DATETIME NOT NULL,
		CloseDate DATETIME NOT NULL,
		Reference CHAR(8) NOT NULL
	)

INSERT	@Sample
	(
		OpenDate,
		CloseDate,
		Reference
	)
SELECT	'20101215', '20110208', 'GTS11171' UNION ALL
SELECT	'20110203', '19000101', 'GTS11186' UNION ALL
SELECT	'20090921', '19000101', 'GTS11104' UNION ALL
SELECT	'20101112', '19000101', 'GTS11158' UNION ALL
SELECT	'20110127', '20110211', 'GTS11184' UNION ALL
SELECT	'20110224', '19000101', 'GTS11191' UNION ALL
SELECT	'20110224', '19000101', 'GTS11192' UNION ALL
SELECT	'20110207', '19000101', 'GTS11188' UNION ALL
SELECT	'20110224', '19000101', 'GTS11193'

DECLARE	@Min DATETIME,
	@Max DATETIME

SELECT	@Min = MIN(DATEADD(MONTH, DATEDIFF(MONTH, 0, OpenDate), 0)),
	@Max = MAX(DATEADD(MONTH, DATEDIFF(MONTH, 0, CloseDate), 0))
FROM	@Sample

-- Solution by Peso
SELECT		cal.theMonth AS StartOfMonth,
		COUNT(s.Reference) AS OpenCases
FROM		(
			SELECT	DATEADD(MONTH, Number, @Min) AS theMonth
			FROM	master..spt_values
			WHERE	Type = 'P'
				AND Number <= 1 + DATEDIFF(MONTH, @Min, @Max)
		) AS cal
LEFT JOIN	@Sample AS s ON s.OpenDate <= cal.theMonth
			AND	(
					s.CloseDate > cal.theMonth
					OR s.CloseDate = '19000101'
				)
GROUP BY	cal.theMonth
ORDER BY	cal.theMonth

N 56°04'39.26"
E 12°55'05.63"

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-03-02 : 08:24:55

Hi Peso, many thanks for your reply .. appreciate you taking the time to help me , i substituted the line data for the link to the table, but recieve the following error




Msg 207, Level 16, State 3, Line 24
Invalid column name 'Number'.
Msg 207, Level 16, State 3, Line 24
Invalid column name 'Type'.
Msg 207, Level 16, State 3, Line 24
Invalid column name 'Number'.


DECLARE	@Sample TABLE
	(
		OpenDate DATETIME NOT NULL,
		CloseDate DATETIME NOT NULL,
		Reference CHAR(8) NOT NULL
	)

INSERT	@Sample
	(
		OpenDate,
		CloseDate,
		Reference
	)
SELECT	OPENDATE, CLOSEDATE, REFERENCE from suppdeskdev.F0007_SUPEVENT

DECLARE	@Min DATETIME,
	@Max DATETIME

SELECT	@Min = MIN(DATEADD(MONTH, DATEDIFF(MONTH, 0, OpenDate), 0)),
	@Max = MAX(DATEADD(MONTH, DATEDIFF(MONTH, 0, CloseDate), 0))
FROM	@Sample

-- Solution by Peso
SELECT		cal.theMonth AS StartOfMonth,
		COUNT(s.Reference) AS OpenCases
FROM		(
			SELECT	DATEADD(MONTH, Number, @Min) AS theMonth
			FROM	master..spt_values
			WHERE	Type = 'P'
				AND Number <= 1 + DATEDIFF(MONTH, @Min, @Max)
		) AS cal
LEFT JOIN	@Sample AS s ON s.OpenDate <= cal.theMonth
			AND	(
					s.CloseDate > cal.theMonth
					OR s.CloseDate = '19000101'
				)
GROUP BY	cal.theMonth
ORDER BY	cal.theMonth

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-02 : 17:08:32

Perhaps you are using a case sensitive collation?
Try this

DECLARE	@Min DATETIME,
	@Max DATETIME

SELECT	@Min = DATEADD(MONTH, DATEDIFF(MONTH, 0, MIN(OpenDate)), 0),
	@Max = DATEADD(MONTH, DATEDIFF(MONTH, 0, MAX(CloseDate)), 0)
FROM	SuppDeskDev.F0007_SupEvent

-- Solution by Peso
SELECT		cal.theMonth AS StartOfMonth,
		COUNT(s.OpenDate) AS OpenCases
FROM		(
			SELECT	DATEADD(MONTH, number, @Min) AS theMonth
			FROM	master..spt_values
			WHERE	type = 'P'
				AND number <= 1 + DATEDIFF(MONTH, @Min, @Max)
		) AS cal
LEFT JOIN	SuppDeskDev.F0007_SupEvent AS s ON s.OpenDate <= cal.theMonth
			AND	(
					s.CloseDate > cal.theMonth
					OR s.CloseDate = '19000101'
				)
GROUP BY	cal.theMonth
ORDER BY	cal.theMonth

N 56°04'39.26"
E 12°55'05.63"

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-03-17 : 09:48:33

Peso , many many thanks works very well, thanks to everyone else who added advice much appreciated :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources