Union ALL and Null Values. - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Union ALL and Null Values.

Author

Topic

Makaio780
Starting Member

24 Posts

Posted - 2012-08-28 : 10:38:43

Hi Guys,

Im trying to do a select that uses a union all to find current months data and previous months data. However the previous month data is not showing the null product_name, and i know there are 29 null pname for previous column.

Results:
Product_Name Current Previous
NULL 27 NULL
SCADA 3 NULL
Microsoft Exchange 2 NULL
Windows7 1 2
Windows Server 1 NULL
Datek/Willowglen Flow Computer 1 NULL
RSA Authentication Manager 1 NULL
Pipelink BPM 1 NULL
Oracle Iexpense 1 NULL
Microsoft Windows 7 Enterprise 1 1

Query:

SELECT Top (10) CategoryList.Product_Name,CurrentMonth.[Current],Previous1.Previous1
FROM (SELECT Product_Name
FROM vARS76_HPD_Help_Desk
GROUP BY Product_Name)AS CategoryList LEFT OUTER JOIN
(SELECT vARS76_HPD_Help_Desk_2.Product_Name, Count(vARS76_HPD_Help_Desk_2.Incident_Number) as Previous1
FROM vARS76_HPD_Help_Desk AS vARS76_HPD_Help_Desk_2 RIGHT OUTER JOIN
vTbl_ServiceDeskAnalystList AS vTbl_ServiceDeskAnalystList_2 ON
vARS76_HPD_Help_Desk_2.Submitter = vTbl_ServiceDeskAnalystList_2.UID
WHERE (vARS76_HPD_Help_Desk_2.Reported_Date > DATEADD(month,- 1, '7/1/2012')) AND (vARS76_HPD_Help_Desk_2.Reported_Date < '7/1/2012') AND
(vARS76_HPD_Help_Desk_2.Incident_Type = 'User Service Request' or vARS76_HPD_Help_Desk_2.Incident_Type = 'User Service Restoration') AND
(vARS76_HPD_Help_Desk_2.Owner_Group = 'HD-Edmonton' OR vARS76_HPD_Help_Desk_2.Owner_Group = 'HD-OM2') AND
(NOT (DATEPART(Hour, vARS76_HPD_Help_Desk_2.Reported_Date) BETWEEN 5 AND 22) AND DATEPART(Weekday, vARS76_HPD_Help_Desk_2.Reported_Date) BETWEEN 2 AND 6 OR
NOT (DATEPART(Hour, vARS76_HPD_Help_Desk_2.Reported_Date) BETWEEN 7 AND 16) AND DATEPART(Weekday, vARS76_HPD_Help_Desk_2.Reported_Date) IN (1, 7))
GROUP BY vARS76_HPD_Help_Desk_2.Product_Name) AS Previous1 ON
CategoryList.Product_Name = Previous1.Product_Name RIGHT OUTER JOIN
(SELECT vARS76_HPD_Help_Desk_1.Product_Name, Count(vARS76_HPD_Help_Desk_1.Incident_Number) as [Current]
FROM vARS76_HPD_Help_Desk AS vARS76_HPD_Help_Desk_1 RIGHT OUTER JOIN
vTbl_ServiceDeskAnalystList AS vTbl_ServiceDeskAnalystList_1 ON
vARS76_HPD_Help_Desk_1.Submitter = vTbl_ServiceDeskAnalystList_1.UID
WHERE (vARS76_HPD_Help_Desk_1.Reported_Date > '7/1/2012') AND (vARS76_HPD_Help_Desk_1.Incident_Type = 'User Service Request' or vARS76_HPD_Help_Desk_1.Incident_Type = 'User Service Restoration') AND
(vARS76_HPD_Help_Desk_1.Reported_Date < '8/1/2012') AND (vARS76_HPD_Help_Desk_1.Owner_Group = 'HD-Edmonton' OR vARS76_HPD_Help_Desk_1.Owner_Group = 'HD-OM2') AND
(NOT (DATEPART(Hour, vARS76_HPD_Help_Desk_1.Reported_Date) BETWEEN 5 AND 22) AND DATEPART(Weekday, vARS76_HPD_Help_Desk_1.Reported_Date) BETWEEN 2 AND 6 OR
NOT (DATEPART(Hour, vARS76_HPD_Help_Desk_1.Reported_Date) BETWEEN 7 AND 16) AND DATEPART(Weekday, vARS76_HPD_Help_Desk_1.Reported_Date) IN (1, 7))
GROUP BY vARS76_HPD_Help_Desk_1.Product_Name) AS CurrentMonth ON
CategoryList.Product_Name = CurrentMonth.Product_Name
Where [Current] <> ''
Order BY [Current] Desc

Thanks in advance.

Makaio

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-28 : 15:26:39

Hard to say without sample data and expected output. What is the query you posted? Did that generate the results you posted?The results you posted seem to show a NULL Product_Name, so I'm not sure what is wrong.

Perhaps a join is eliminating NULL values (CategoryList.Product_Name = CurrentMonth.Product_Name)??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources