Static groups for non-existent records

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 2008 Forums

Transact-SQL (2008)

Static groups for non-existent records

Author

Topic

billfalcon
Starting Member

2 Posts

Posted - 2010-11-15 : 17:05:15

I have a query that pulls sales history from an invoice table. Using a nested CASE statement, it dynamically assigns a period to each record, based on its invoice date:

SELECT     invoice_hdr.customer_id AS CustomerID, dbo.p21_view_inv_loc.item_id AS ItemID, invoice_line.customer_part_number AS CustPN, 
                      invoice_line.qty_shipped AS QtyShipped, invoice_line.extended_price AS Sales, CASE WHEN invoice_hdr.invoice_date > DATEADD(mm, - 24, GETDATE()) AND 
                      invoice_hdr.invoice_date <= DATEADD(mm, - 21, GETDATE()) THEN 'P1' ELSE (CASE WHEN invoice_hdr.invoice_date > DATEADD(mm, - 21, GETDATE()) AND 
                      invoice_hdr.invoice_date <= DATEADD(mm, - 18, GETDATE()) THEN 'P2' ELSE (CASE WHEN invoice_hdr.invoice_date > DATEADD(mm, - 18, GETDATE()) AND 
                      invoice_hdr.invoice_date <= DATEADD(mm, - 15, GETDATE()) THEN 'P3' ELSE (CASE WHEN invoice_hdr.invoice_date > DATEADD(mm, - 15, GETDATE()) AND 
                      invoice_hdr.invoice_date <= DATEADD(mm, - 12, GETDATE()) THEN 'P4' ELSE (CASE WHEN invoice_hdr.invoice_date > DATEADD(mm, - 12, GETDATE()) AND 
                      invoice_hdr.invoice_date <= DATEADD(mm, - 9, GETDATE()) THEN 'P5' ELSE (CASE WHEN invoice_hdr.invoice_date > DATEADD(mm, - 9, GETDATE()) AND 
                      invoice_hdr.invoice_date <= DATEADD(mm, - 6, GETDATE()) THEN 'P6' ELSE (CASE WHEN invoice_hdr.invoice_date > DATEADD(mm, - 6, GETDATE()) AND 
                      invoice_hdr.invoice_date <= DATEADD(mm, - 3, GETDATE()) THEN 'P7' ELSE (CASE WHEN invoice_hdr.invoice_date > DATEADD(mm, - 3, GETDATE()) AND 
                      invoice_hdr.invoice_date <= DATEADD(mm, - 0, GETDATE()) THEN 'P8' ELSE 'Px' END) END) END) END) END) END) END) END AS Period
FROM         dbo.invoice_line AS invoice_line INNER JOIN
                      dbo.invoice_hdr AS invoice_hdr ON invoice_line.invoice_no = invoice_hdr.invoice_no INNER JOIN
                      dbo.p21_view_inv_loc ON invoice_line.inv_mast_uid = dbo.p21_view_inv_loc.inv_mast_uid AND 
                      invoice_hdr.sales_location_id = dbo.p21_view_inv_loc.location_id
WHERE     (invoice_hdr.invoice_date > DATEADD(mm, - 24, GETDATE())) AND (invoice_line.company_id = '1') AND (invoice_hdr.sales_location_id IN (100001, 100002, 100005, 
                      100006, 100007, 100008)) AND (invoice_hdr.branch_id = '01') AND (invoice_hdr.invoice_date <= DATEADD(mm, 0, GETDATE()))

This gives me something like this:

101027 OR-225 ABC123 115.000000000 0.0000 P7
101027 OR-225 ABC123 120.000000000 0.0000 P7
100150 OR-225 ABC123 200.000000000 128.0000 P8
101027 OR-225 ABC123 120.000000000 0.0000 P8

In SSRS, I'm aggregating the data to use in a line graph:

SELECT        CustomerID, ItemID, CustPN, SUM(QtyShipped) AS QtyShipped, SUM(Sales) AS Sales, Period
FROM            a_CS_CustomerUsage_Chart
GROUP BY CustomerID, ItemID, CustPN, Period
HAVING        (CustomerID = @CustomerID) AND (ItemID = @ItemID)

Which might produce something like this:
101026 GR-0375 B30667 1500.000000000 795.0000 P7
101026 GR-0375 B30667 3000.000000000 1590.0000 P8

For consistency, I would like all periods to appear (P1-P8), so itermittent/non-existent ($0) sales would be very visible:
101026 GR-0375 B30667 0 0 P1
101026 GR-0375 B30667 0 0 P2
101026 GR-0375 B30667 0 0 P3
101026 GR-0375 B30667 0 0 P4
101026 GR-0375 B30667 0 0 P5
101026 GR-0375 B30667 0 0 P6
101026 GR-0375 B30667 1500 795 P7
101026 GR-0375 B30667 3000 1590 P8

I'm not sure if this is something I can do in T-SQL, or something I need to adjust in SSRS (assuming it can be done at all).

Many thanks in advance.

billfalcon
Starting Member

2 Posts

Posted - 2010-11-16 : 11:00:01

Solved: The SQL is about as far from "good practice" as you can get. However, it works.

I updated my SSRS Dataset query as follows:

SELECT        CASE WHEN tview2.CustomerID IS NULL THEN tview1.CustomerID ELSE tview2.CustomerID END AS CustomerID, CASE WHEN tview2.ItemID IS NULL 
                         THEN tview1.ItemID ELSE tview2.ItemID END AS ItemID, tview2.CustPN, CASE WHEN tview2.Sales IS NULL THEN tview1.sales ELSE tview2.Sales END AS sales, 
                         CASE WHEN tview2.QtyShipped IS NULL THEN tview1.QtyShipped ELSE tview2.QtyShipped END AS QtyShipped, CASE WHEN tview2.Period IS NULL 
                         THEN tview1.Period ELSE tview2.Period END AS Period
FROM            (SELECT        CustomerID, ItemID, CustPN, SUM(QtyShipped) AS QtyShipped, SUM(Sales) AS Sales, Period
                          FROM            a_CS_CustomerUsage_Chart
                          GROUP BY CustomerID, ItemID, CustPN, Period
                          HAVING         (CustomerID = @CustomerID) AND (ItemID = @ItemID)) AS tview2 RIGHT OUTER JOIN
                             (SELECT        @CustomerID AS CustomerID, @ItemID AS ItemID, '0' AS QtyShipped, '0' AS Sales, Period
                               FROM            a_CS_CustomerUsage_Chart AS a_CS_CustomerUsage_Chart_1
                               GROUP BY Period) AS tview1 ON tview2.Period = tview1.Period
ORDER BY Period

Result:
@CustomerID = 101026
@ItemID = GR-0375

101026 GR-0375 NULL 0.0000 0.000000000 P1
101026 GR-0375 NULL 0.0000 0.000000000 P2
101026 GR-0375 NULL 0.0000 0.000000000 P3
101026 GR-0375 NULL 0.0000 0.000000000 P4
101026 GR-0375 NULL 0.0000 0.000000000 P5
101026 GR-0375 NULL 0.0000 0.000000000 P6
101026 GR-0375 B30667 795.000 1500.000000000 P7
101026 GR-0375 B30667 1590.000 3000.000000000 P8

Satisfies my needs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources