GROUPING DRIVES ME CRAZY!!!! - 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 Programming

GROUPING DRIVES ME CRAZY!!!!

Author

Topic

Makaio780
Starting Member

24 Posts

Posted - 2012-02-15 : 11:48:37

Select count(vars75_hpd_help_desk_1.incident_number)as tktcount,derivedtbl_1.FCR,Month(Reported_date) as mth
from vars75_hpd_help_desk as vars75_hpd_help_desk_1 inner join
vTbl_ServiceDeskAnalystList ON vARS75_HPD_Help_Desk_1.Owner = vTbl_ServiceDeskAnalystList.[Full Name] CROSS JOIN
(SELECT COUNT(Incident_Number) AS FCR,Month(Reported_date) as mth
FROM vARS75_HPD_Help_Desk
WHERE (Assigned_Group = 'HD-Edmonton' OR
Assigned_Group = 'HD-Edmonton-1.5') AND (Reported_Source = 'Phone') AND (Last_Resolved_Date <= DATEADD(ss, 3600, Reported_Date)) AND
(Reported_date > dateadd(mm,datediff(mm,0,getdate())-12,0))
and ( Reported_date < dateadd(month, datediff(month, 0, getdate()), 0))
group by month(reported_date)) AS derivedtbl_1
where reported_Source = 'phone' and (Reported_date > dateadd(mm,datediff(mm,0,getdate())-12,0))
and ( Reported_date < dateadd(month, datediff(month, 0, getdate()), 0))
group by month(reported_date),derivedtbl_1.FCR

tktcountFCR Mth
5653 2041 1
3902 2041 2
4339 2041 3
3938 2041 4
4565 2041 5
4792 2041 6
3920 2041 7
4595 2041 8
5074 2041 9
4752 2041 10
5197 2041 11
4407 2041 12
5653 2234 1
3902 2234 2
4339 2234 3
3938 2234 4
4565 2234 5
4792 2234 6
3920 2234 7
4595 2234 8
5074 2234 9
4752 2234 10
5197 2234 11
4407 2234 12
5653 2328 1
3902 2328 2
4339 2328 3
3938 2328 4
4565 2328 5
4792 2328 6
3920 2328 7
4595 2328 8
5074 2328 9
4752 2328 10
5197 2328 11
4407 2328 12
5653 2392 1
3902 2392 2
4339 2392 3
3938 2392 4
4565 2392 5
4792 2392 6
3920 2392 7
4595 2392 8
5074 2392 9
4752 2392 10
5197 2392 11
4407 2392 12
5653 2680 1
3902 2680 2
4339 2680 3
3938 2680 4
4565 2680 5
4792 2680 6
3920 2680 7
4595 2680 8
5074 2680 9
4752 2680 10
5197 2680 11
4407 2680 12
5653 2685 1
3902 2685 2
4339 2685 3
3938 2685 4
4565 2685 5
4792 2685 6
3920 2685 7
4595 2685 8
5074 2685 9
4752 2685 10
5197 2685 11
4407 2685 12
5653 2706 1
3902 2706 2
4339 2706 3
3938 2706 4
4565 2706 5
4792 2706 6
3920 2706 7
4595 2706 8
5074 2706 9
4752 2706 10
5197 2706 11
4407 2706 12
5653 2772 1
3902 2772 2
4339 2772 3
3938 2772 4
4565 2772 5
4792 2772 6
3920 2772 7
4595 2772 8
5074 2772 9
4752 2772 10
5197 2772 11
4407 2772 12
5653 2913 1
3902 2913 2
4339 2913 3
3938 2913 4
4565 2913 5
4792 2913 6
3920 2913 7
4595 2913 8
5074 2913 9
4752 2913 10
5197 2913 11
4407 2913 12
5653 2974 1
3902 2974 2
4339 2974 3
3938 2974 4
4565 2974 5
4792 2974 6
3920 2974 7
4595 2974 8
5074 2974 9
4752 2974 10
5197 2974 11
4407 2974 12
5653 3025 1
3902 3025 2
4339 3025 3
3938 3025 4
4565 3025 5
4792 3025 6
3920 3025 7
4595 3025 8
5074 3025 9
4752 3025 10
5197 3025 11
4407 3025 12
5653 3520 1
3902 3520 2
4339 3520 3
3938 3520 4
4565 3520 5
4792 3520 6
3920 3520 7
4595 3520 8
5074 3520 9
4752 3520 10
5197 3520 11
4407 3520 12

How would I group the FCR column by month?

Ron Cheung

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 12:03:03

GROUP BY FCR, Month

???

Not sure what'chyour askin

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Makaio780
Starting Member

24 Posts

Posted - 2012-02-15 : 12:38:36

In the end i would like to combine 2 tables i guess grouped by month.

TktCount Mth
5689 1
3931 2
4367 3
3966 4
4628 5
5006 6
3961 7
4645 8
5107 9
4776 10
5214 11
4417 12

FCR mth
3520 1
2041 2
2392 3
2234 4
2685 5
2913 6
2328 7
2706 8
2974 9
2772 10
3025 11
2680 12

below is the queuries.

Select count(incident_number)as tktcount,Month(Reported_date) as mth
from vars75_hpd_help_desk
where reported_Source = 'phone' and (Reported_date > dateadd(mm,datediff(mm,0,getdate())-12,0))
and ( Reported_date < dateadd(month, datediff(month, 0, getdate()), 0))
group by month(reported_date)

SELECT COUNT(Incident_Number) AS FCR,Month(Reported_date) as mth
FROM vARS75_HPD_Help_Desk
WHERE (Assigned_Group = 'HD-Edmonton' OR
Assigned_Group = 'HD-Edmonton-1.5') AND (Reported_Source = 'Phone') AND (Last_Resolved_Date <= DATEADD(ss, 3600, Reported_Date)) AND
(Reported_date > dateadd(mm,datediff(mm,0,getdate())-12,0))
and ( Reported_date < dateadd(month, datediff(month, 0, getdate()), 0))
group by month(reported_date)

Ron Cheung

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 13:03:10

Does this help?



SELECT Source, YEAR(Report_Date) AS YR, MONTH(Report_Date) AS MTH, COUNT(*) AS Row_Counts
  FROM (
		SELECT 'TICKET' AS Source, Reported_date, Incident_Number
		  FROM vars75_hpd_help_desk
		 WHERE reported_Source = 'phone' 
		   AND (Reported_date > dateadd(mm,datediff(mm,0,getdate())-12,0))
		   AND ( Reported_date < dateadd(month, datediff(month, 0, getdate()), 0))
		UNION ALL
		SELECT 'FCR' AS Source, Reported_date, Incident_Number
		  FROM vars75_hpd_help_desk
		 WHERE (	Assigned_Group = 'HD-Edmonton' 
				OR	Assigned_Group = 'HD-Edmonton-1.5') 
		   AND Reported_Source = 'Phone' 
		   AND (Last_Resolved_Date <= DATEADD(ss, 3600, Reported_Date)) 
		   AND Reported_date > dateadd(mm,datediff(mm,0,getdate())-12,0)
		   AND Reported_date < dateadd(month, datediff(month, 0, getdate()), 0)
) AS XXX
GROUP BY Source, YEAR(Report_Date), MONTH(Report_Date)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Makaio780
Starting Member

24 Posts

Posted - 2012-02-15 : 13:23:03

Thanks for the reply... im sure i could use this somehow!!! thanks again!

Ron Cheung

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 14:05:43

Any Luck?

If not, have a tequila

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources