Sales Ranges

I have a query that returns data in ranges.  For Example, my query returns this

227933.05      731      $1 - $999                      West Sales
204972.71      107      $1,000 - $4,999      West Sales
55435.54                      8       $5,000 - $9,999      West Sales
29346.38                      2       $10,000 - $24,999      West Sales

This works fine but there are two more ranges that need to be reported and the finished product needs to look like this. I do have a reference table with all the ranges in it and tried to do an outer join to force all records from this ranges from the table but this did not work.

227933.05      731      $1 - $999                      West Sales
204972.71      107      $1,000 - $4,999      West Sales
55435.54                       8      $5,000 - $9,999      West Sales
29346.38                       2      $10,000 - $24,999      West Sales
0                        0      $25,000 - $49,999      West Sales
0                        0      $50,000 - $99,999      West Sales

Does anyone know how I can accomplish?

earngreenAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Something like this perhaps:
SELECT  IJ.Branch,
        ISNULL(i.ActualSales, 0) ActualSales,
        ISNULL(i.NumOfSales, 0) NumOfSales,
        CR.RangeDescription,
        CR.RangeID
FROM    tblInvoicePaidRange
		LEFT JOIN (
				Select	IJ.Branch,
						C.CLIENTNAME_H1,
						IJ.RangeID,
						SUM(IJ.SalesAmount) ActualSales,
						SUM(IJ.Invoices) NumOfSales
				From	InvoicesAllowed IJ 
						INNER JOIN SUB_CLIENTS SB ON IJ.CompanyID = SB.SOURCECLIENT_ID	
						INNER JOIN CLIENTS AS C ON SB.CLIENT_ID = C.CLIENT_ID
				WHERE	IJ.ReportMonth >= '06/01/2009' AND IJ.ReportMonth < '06/30/2009'
						AND C.CLIENTNAME_H1 = 'West Sales'
				GROUP BY
						IJ.Branch,
						C.CLIENTNAME_H1,
						i.RangeID) i ON CR.RangeID = i.RangeID

Open in new window

0
 
Patrick MatthewsCommented:
Hello earngreen,

Please post the schema for the tables involved, and the SQL you have thus far.

Regards,

Patrick
0
 
earngreenAuthor Commented:
The query is below. The invoice paid range table has

RangeID,
RangeDescription
MinRange
MaxRange

SELECT     IJ.Branch, SUM(IJ.SalesAmount) AS ActualSales, SUM(IJ.Invoices) AS NumOfSales, CR.RangeDescription, C.CLIENTNAME_H1, 
                      CR.RangeID
FROM         InvoicesAllowed AS IJ INNER JOIN
                      SUB_CLIENTS AS SB ON IJ.CompanyID = SB.SOURCECLIENT_ID INNER JOIN
                      CLIENTS AS C ON SB.CLIENT_ID = C.CLIENT_ID FULL OUTER JOIN
                      tblInvoicePaidRange AS CR ON IJ.RangeID = CR.RangeID
WHERE     (IJ.ReportMonth >= '06/01/2009') AND (IJ.ReportMonth < '06/30/2009') AND (C.CLIENTNAME_H1 = 'West Sales')
GROUP BY IJ.Branch, CR.RangeDescription, C.CLIENTNAME_H1, CR.RangeID

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.