Help with query - 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)

Help with query

Author

Topic

squarefish
Starting Member

28 Posts

Posted - 2009-03-31 : 03:44:00

I have the following query which joins two tables and outputs some grouped data. The problem is it is looking for records from the docsales table with matching month and year, and if it doesn't find any it does not return anything, so on the first day of the month there are no records displayed.

I would like it to return 0's, or NULLs for the second table fields but return the branchname/branchID anyway.

Is this possible?

Here is a sample of the output

BNP Peterborough Peugeot 1 50 BNP 3 2009 3
BNH Peterborough Honda 1 49 BNH 3 2009 5
LCS Leicester Vauxhall 1 47 LCS 3 2009 2
BNV Peterborough Vauxhall 1 45 BNV 3 2009 2
CFO Cambridge Ford 1 45 CFO 3 2009 4

SELECT TOP (100) PERCENT dbo.docBranchData.branchID, dbo.docBranchData.branchName, dbo.docBranchData.branchSales,
SUM(dbo.docSales.salesNewRetailO) AS total, dbo.docSales.salesBranchID, MONTH(dbo.docSales.salesTimestamp) AS month,
YEAR(dbo.docSales.salesTimestamp) AS year, dbo.docBranchData.branchRegion
FROM dbo.docSales INNER JOIN
dbo.docBranchData ON dbo.docSales.salesBranchID = dbo.docBranchData.branchID
GROUP BY dbo.docBranchData.branchID, dbo.docBranchData.branchName, dbo.docBranchData.branchSales, dbo.docSales.salesBranchID,
MONTH(dbo.docSales.salesTimestamp), YEAR(dbo.docSales.salesTimestamp), dbo.docBranchData.branchRegion
HAVING (dbo.docBranchData.branchSales = 1) AND (MONTH(dbo.docSales.salesTimestamp) = '3') AND (YEAR(dbo.docSales.salesTimestamp) = '2009')
ORDER BY total DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 03:50:36

[code]SELECT TOP (100) PERCENT
bd.branchID,
bd.branchName,
bd.branchSales,
SUM(COALESCE(s.salesNewRetailO, 0)) AS total,
s.salesBranchID,
MONTH(s.salesTimestamp) AS month,
YEAR(s.salesTimestamp) AS year,
bd.branchRegion
FROM dbo.docBranchData AS db
LEFT JOIN dbo.docSales AS s ON s.salesBranchID = bd.branchID
AND s.salesTimestamp >= '20090301'
AND s.salesTimestamp < '20090401'
WHERE bd.branchSales = 1
GROUP BY bd.branchID,
bd.branchName,
bd.branchSales,
s.salesBranchID,
MONTH(s.salesTimestamp),
YEAR(s.salesTimestamp),
bd.branchRegion
ORDER BY SUM(COALESCE(s.salesNewRetailO, 0)) DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

squarefish
Starting Member

28 Posts

Posted - 2009-03-31 : 08:19:18

Once again Peso you have saved the day!!

Thank You!

Richard

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources