[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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?

0
earngreen
Asked:
earngreen
1 Solution
 
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
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now