Combining 2 selects - 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

Combining 2 selects

Author

Topic

Makaio780
Starting Member

24 Posts

Posted - 2012-02-17 : 12:31:30

How would i combine the 2 selects so i get this as the result

TktCnt FCRTkt Mth Yr
100 50 1 2011
150 75 2 2011

SELECT COUNT(Incident_Number)AS TktCnt,MONTH(Reported_Date) AS Mth,YEAR(Reported_Date) AS Yr
FROM vARS75_HPD_Help_Desk
INNER JOIN vTbl_ServiceDeskAnalystList
ON vARS75_HPD_Help_Desk.Owner = vTbl_ServiceDeskAnalystList.[Full Name]
WHERE Reported_Source = 'Phone'
AND Owner_Group = 'HD-Edmonton'
AND (Reported_Date > DATEADD(mm,DATEDIFF(mm,0,GETDATE())-13,0))
AND (Reported_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY MONTH(Reported_Date),YEAR(Reported_Date)

SELECT COUNT(Incident_Number) AS FCRTkt,MONTH(Reported_Date) AS Mth,YEAR(Reported_Date) AS Yr
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())-13,0))
AND (Reported_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY MONTH(Reported_Date),YEAR(Reported_Date)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-17 : 12:45:34

[code]SELECT
a.TktCnt,
b.FCRTkt,
COALESCE(a.Mth,b.Mth) AS Mth,
COALESCE(a.Yr,b.Yr) AS Yr
FROM
(
SELECT COUNT(Incident_Number)AS TktCnt,MONTH(Reported_Date) AS Mth,YEAR(Reported_Date) AS Yr
FROM vARS75_HPD_Help_Desk
INNER JOIN vTbl_ServiceDeskAnalystList
ON vARS75_HPD_Help_Desk.Owner = vTbl_ServiceDeskAnalystList.[Full Name]
WHERE Reported_Source = 'Phone'
AND Owner_Group = 'HD-Edmonton'
AND (Reported_Date > DATEADD(mm,DATEDIFF(mm,0,GETDATE())-13,0))
AND (Reported_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY MONTH(Reported_Date),YEAR(Reported_Date)
) a FULL JOIN
(
SELECT COUNT(Incident_Number) AS FCRTkt,MONTH(Reported_Date) AS Mth,YEAR(Reported_Date) AS Yr
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())-13,0))
AND (Reported_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY MONTH(Reported_Date),YEAR(Reported_Date)
) b ON a.Yr = b.Yr AND a.Mth = b.Mth[/code]

Makaio780
Starting Member

24 Posts

Posted - 2012-02-17 : 12:50:29

Thanks for the quick response!!! this is exactly what i needed!!!! Thanks so much.

quote:

Originally posted by sunitabeck

SELECT
	a.TktCnt,
	b.FCRTkt,
	COALESCE(a.Mth,b.Mth) AS Mth,
	COALESCE(a.Yr,b.Yr) AS Yr
FROM
(
	SELECT COUNT(Incident_Number)AS TktCnt,MONTH(Reported_Date) AS Mth,YEAR(Reported_Date) AS Yr
	FROM vARS75_HPD_Help_Desk
	INNER JOIN vTbl_ServiceDeskAnalystList 
	ON vARS75_HPD_Help_Desk.Owner = vTbl_ServiceDeskAnalystList.[Full Name]
	WHERE Reported_Source = 'Phone' 
	AND Owner_Group = 'HD-Edmonton'
	AND (Reported_Date > DATEADD(mm,DATEDIFF(mm,0,GETDATE())-13,0))
	AND (Reported_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
	GROUP BY MONTH(Reported_Date),YEAR(Reported_Date)
) a FULL JOIN
(
	SELECT COUNT(Incident_Number) AS FCRTkt,MONTH(Reported_Date) AS Mth,YEAR(Reported_Date) AS Yr
	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())-13,0))
	AND (Reported_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
	GROUP BY MONTH(Reported_Date),YEAR(Reported_Date)
) b ON a.Yr = b.Yr AND a.Mth = b.Mth

Ron Cheung

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 13:04:20

other way is using UNION ALL


SELECT SUM(FCRTkt) AS FCRTkt,SUM(TktCnt) AS TktCnt, Mth,Yr
FROM
(
SELECT CAST(0 AS int) AS FCRTkt,COUNT(Incident_Number)AS TktCnt,MONTH(Reported_Date) AS Mth,YEAR(Reported_Date) AS Yr
FROM vARS75_HPD_Help_Desk
INNER JOIN vTbl_ServiceDeskAnalystList 
ON vARS75_HPD_Help_Desk.Owner = vTbl_ServiceDeskAnalystList.[Full Name]
WHERE Reported_Source = 'Phone' 
AND Owner_Group = 'HD-Edmonton'
AND (Reported_Date > DATEADD(mm,DATEDIFF(mm,0,GETDATE())-13,0))
AND (Reported_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY MONTH(Reported_Date),YEAR(Reported_Date)

UNION ALL

SELECT COUNT(Incident_Number) AS FCRTkt,0,MONTH(Reported_Date) AS Mth,YEAR(Reported_Date) AS Yr
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())-13,0))
AND (Reported_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY MONTH(Reported_Date),YEAR(Reported_Date)
)t
GROUP BY Mth,Yr

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources