dastaub
asked on
SQL 2005 Group By Query
The below SQL 2005 Group by query works. The additional solution I am looking for is when a Null appears for the ProName. I would like a solution that places the word: "unknown" when the group by results in a Null for ProName? Currently it lists the ProNames and sometimes a Null when the provider is missing from the tables.
SELECT zReports.PhysResp, sum(1) as Volume,
Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle as ProName
FROM Orders
INNER JOIN zReports ON Orders.VisitID = zReports.VisitID
left JOIN Provider ON Provider.ProID = zReports.PhysResp
WHERE
(zReports.TimeIn2 >= '200701010000' AND zReports.TimeIn2 <= '201108302359' and zReports.FacNo = 1 and Orders.OrderType = 'RAD')
Group by ZReports.PhysResp,Provider .ProLName, Provider.ProFName, Provider.Protitle
Order by Provider.ProLName, Provider.ProFName, Provider.Protitle
SELECT zReports.PhysResp, sum(1) as Volume,
Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle as ProName
FROM Orders
INNER JOIN zReports ON Orders.VisitID = zReports.VisitID
left JOIN Provider ON Provider.ProID = zReports.PhysResp
WHERE
(zReports.TimeIn2 >= '200701010000' AND zReports.TimeIn2 <= '201108302359' and zReports.FacNo = 1 and Orders.OrderType = 'RAD')
Group by ZReports.PhysResp,Provider
Order by Provider.ProLName, Provider.ProFName, Provider.Protitle
It looks like you just need to wrap ProName expression with COALESCE():
COALESCE(Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle, 'Unknown') as ProName
You may also want to account for NULL in any one of the columns also.
COALESCE(Provider.ProLName
You may also want to account for NULL in any one of the columns also.
COALESCE(Provider.ProLName + COALESCE(' ' + Provider.ProFName, '') + COALESCE(', ' + Provider.Protitle, ''), 'Unknown') as ProName
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
might need to switch Provider.ProID = zReports.PhysResp around.
SELECT zReports.PhysResp,
sum(1) as Volume,
CASE WHEN ISNULL(Provider.ProID, '') = '' THEN 'unknown'
ELSE Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle
END as ProName
FROM
Orders INNER JOIN
zReports ON Orders.VisitID = zReports.VisitID left JOIN
Provider ON zReports.PhysResp = Provider.ProID
WHERE
(zReports.TimeIn2 >= '200701010000'
AND zReports.TimeIn2 <= '201108302359'
and zReports.FacNo = 1
and Orders.OrderType = 'RAD')
Group by
ZReports.PhysResp,
Provider.ProLName,
Provider.ProFName,
Provider.Protitle
Order by
Provider.ProLName,
Provider.ProFName,
Provider.Protitle
Try this
SELECT zReports.PhysResp, sum(1) as Volume,
isnull(Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle, 'Unknown') as ProName
FROM Orders
INNER JOIN zReports ON Orders.VisitID = zReports.VisitID
left JOIN Provider ON Provider.ProID = zReports.PhysResp
WHERE
(zReports.TimeIn2 >= '200701010000' AND zReports.TimeIn2 <= '201108302359' and zReports.FacNo = 1 and Orders.OrderType = 'RAD')
Group by ZReports.PhysResp,Provider.ProLName, Provider.ProFName, Provider.Protitle
Order by Provider.ProLName, Provider.ProFName, Provider.Protitle
ASKER
all answer were thoughtful. Below is solution:
SELECT zReports.PhysResp, sum(1) as Volume,
IsNull(Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle, 'Unknown') as ProName
FROM Orders
INNER JOIN zReports ON Orders.VisitID = zReports.VisitID
left JOIN Provider ON Provider.ProID = zReports.PhysResp
WHERE
(zReports.TimeIn2 >= '200701010000' AND zReports.TimeIn2 <= '201108302359' and zReports.FacNo = 1 and Orders.OrderType = 'RAD')
Group by ZReports.PhysResp,Provider .ProLName, Provider.ProFName, Provider.Protitle
Order by Provider.ProLName, Provider.ProFName, Provider.Protitle
SELECT zReports.PhysResp, sum(1) as Volume,
IsNull(Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle, 'Unknown') as ProName
FROM Orders
INNER JOIN zReports ON Orders.VisitID = zReports.VisitID
left JOIN Provider ON Provider.ProID = zReports.PhysResp
WHERE
(zReports.TimeIn2 >= '200701010000' AND zReports.TimeIn2 <= '201108302359' and zReports.FacNo = 1 and Orders.OrderType = 'RAD')
Group by ZReports.PhysResp,Provider
Order by Provider.ProLName, Provider.ProFName, Provider.Protitle
FROM titles
GROUP BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END
GO