I am fairly new to SQL server queries and am stuck with a query which works fine in most instances, however, it fails to return an answer if there is no data in one of my tables...
I have a table with sales figures (invoiced orders and open orders) and another with a sales target for the customer. In most instances my query works fine as a customer will have either been invoiced or have an order so I get four columns returned (customer, target, invoiced_orders, open_orders) I do not get an output when they have nothing invoiced and no open orders. How can I get my query to return data showing zero values in the invoiced_orders and open_orders column? In my query I have used both left and right outer joins but both made no difference...the fields I am missing a value for are both aggregates (sum) value fields in my query (if that helps shed any light on why this is happening...)
For the scenario you described you would use one of the OUTER JOINs. Either left outer join,or right outer join. If you tried that and it did not give you the desired results, that is because of incorrect usage. If you post a sample query and sample input data with expected results, that would make it easier for someone to respond.
If you need help in posting, please see this article
SELECT dbo.IREPORTS_MASTERSUMMARY.COUNTRYREGIONID, dbo.SMM_SALES.DEFAULTDIMENSION, dbo.SMM_SALES.VALUE AS TARGET,
SUM(dbo.IREPORTS_MASTERSUMMARY.INVTOTAL) AS INVOICED, SUM(dbo.IREPORTS_MASTERSUMMARY.ORDERTOTAL) AS ORDERS,
dbo.IREPORTS_MASTERSUMMARY.MONTH, dbo.SMM_SALES.ACCOUNTNUM, dbo.HCMPERSONDETAILSCUBE.PERSONNELNUMBER
FROM dbo.SMM_SALES INNER JOIN
dbo.HCMWORKER ON dbo.SMM_SALES.PERSON = dbo.HCMWORKER.PERSON INNER JOIN
dbo.HCMPERSONDETAILSCUBE ON dbo.HCMWORKER.PERSONNELNUMBER = dbo.HCMPERSONDETAILSCUBE.PERSONNELNUMBER INNER JOIN
dbo.IREPORTS_MASTERSUMMARY ON dbo.SMM_SALES.ACCOUNTNUM = dbo.IREPORTS_MASTERSUMMARY.ACCOUNTNUM AND
MONTH(dbo.SMM_SALES.DATE) = dbo.IREPORTS_MASTERSUMMARY.MONTH
WHERE (dbo.IREPORTS_MASTERSUMMARY.MONTH IN (11, 12)) AND (dbo.HCMWORKER.PERSONNELNUMBER = 39)
GROUP BY dbo.SMM_SALES.DEFAULTDIMENSION, dbo.IREPORTS_MASTERSUMMARY.COUNTRYREGIONID, dbo.IREPORTS_MASTERSUMMARY.MONTH,
dbo.SMM_SALES.VALUE, dbo.SMM_SALES.ACCOUNTNUM, dbo.HCMPERSONDETAILSCUBE.PERSONNELNUMBER
the table IREPORTS_MASTERSUMMARY holds data for the all sales either invoices or orders... for customer C0000175 there is no sales orders or invoices for month 12 (December) so it returns nothing but I do have a sales target...so was expecting the line to show the target and zero values against the invoiced and orders columns.
The query you posted has all INNER JOINS, so it is going to return only if all the tables have matching entries. What is the LEFT JOIN that you tried? It should be something like shown below, but it may not be exact since I am unable to test it.
SELECT ms.COUNTRYREGIONID
,s.DEFAULTDIMENSION
,s.VALUE AS TARGET
,SUM(ms.INVTOTAL) AS INVOICED
,SUM(ms.ORDERTOTAL) AS ORDERS
,ms.MONTH
,s.ACCOUNTNUM
,pd.PERSONNELNUMBER
FROM dbo.HCMWORKER AS w
LEFT JOIN dbo.SMM_SALES AS s ON s.PERSON = w.PERSON
LEFT JOIN dbo.HCMPERSONDETAILSCUBE AS pd ON w.PERSONNELNUMBER = pd.PERSONNELNUMBER
LEFT JOIN dbo.IREPORTS_MASTERSUMMARY AS ms ON s.ACCOUNTNUM = ms.ACCOUNTNUM
AND MONTH(s.DATE) = ms.MONTH
AND ( ms.MONTH IN ( 11 ,12 ) )
WHERE (w.PERSONNELNUMBER = 39)
GROUP BY s.DEFAULTDIMENSION
,ms.COUNTRYREGIONID
,ms.MONTH
,s.VALUE
,s.ACCOUNTNUM
,w.PERSONNELNUMBER