I have a table that host sales totals by item by month I need to know how many months for each item had sales for the Months with Sales output. For example:
What I have when I query the table:
.............| Jan | Feb | Mar | Apr | May |
Item1 | 10 | 7 | 0 | 13 | 0 |
item2 | 0 | 9 | 5 | 0 | 0 |
item3 | 1 | 0 | 0 | 17 | 21 |
What I need:
.............| Jan | Feb | Mar | Apr | May | Months With Sales |
Item1 | 10 | 7 | 0 | 13 | 0 | 3
item2 | 0 | 9 | 5 | 0 | 0 | 2
item3 | 1 | 0 | 0 | 17 | 21 | 3
I could use a CASE but for 12 months there would be quite a lot of potential WHENS.
Any Ideas would be greatly appreciated.
Ken
Okay, this one really works... Maybe my brain is taking the weekend off...
First things first, we need some data (CREATE TABLE and INSERT scripts)
use tempdb;
GO
CREATE TABLE #Sales (
ItemID CHAR(5) NOT NULL,
MonthNo TINYINT,
SalesQty INT
);
GO
INSERT INTO #Sales(ItemID,MonthNo,SalesQty)
VALUES ('Item1',1,10),('Item1',2,7),('Item1',3,0),('Item1',4,13),('Item1',5,0),
('Item2',1,0),('Item2',2,9),('Item2',3,5),('Item2',4,0),('Item2',5,0),
('Item3',1,1),('Item3',2,0),('Item3',3,0),('Item3',4,17),('Item3',5,21);
Then we can write a query against it.
SELECT ItemID
, [1] As Jan
, [2] AS Feb
, [3] As Mar
, [4] AS Apr
, [5] AS May
, [6] AS Jun
, [7] AS Jul
, [8] AS Aug
, [9] AS Sep
, [10] AS Oct
, [11] AS Nov
, [12] AS Dec
, [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12] AS 'Item Total'
, IIF([1]>0,1,0) +
IIF([2]>0,1,0) +
IIF([3]>0,1,0) +
IIF([4]>0,1,0) +
IIF([5]>0,1,0) +
IIF([6]>0,1,0) +
IIF([7]>0,1,0) +
IIF([8]>0,1,0) +
IIF([9]>0,1,0) +
IIF([10]>0,1,0) +
IIF([11]>0,1,0) +
IIF([12]>0,1,0)
AS MonthCountWithSales
FROM
(SELECT ItemID, SalesQty, MonthNo
FROM #Sales) AS SrcTable
PIVOT
(
SUM(SalesQty)
FOR MonthNo IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PvtTable;
Pretty sure this would be easier if you just did it Jeff Moden's way... CrossTabs & Pivots
August 12, 2019 at 10:08 am
A different method that doesn't use PIVOT
;WITH x AS
(
SELECT ItemID,
SUM(IIF(MonthNo=1, SalesQty, 0)) AS Jan,
SUM(IIF(MonthNo=2, SalesQty, 0)) AS Feb,
SUM(IIF(MonthNo=3, SalesQty, 0)) AS Mar,
SUM(IIF(MonthNo=4, SalesQty, 0)) AS Apr,
SUM(IIF(MonthNo=5, SalesQty, 0)) AS May,
SUM(IIF(MonthNo=6, SalesQty, 0)) AS Jun,
SUM(IIF(MonthNo=7, SalesQty, 0)) AS Jul,
SUM(IIF(MonthNo=8, SalesQty, 0)) AS Aug,
SUM(IIF(MonthNo=9, SalesQty, 0)) AS Sep,
SUM(IIF(MonthNo=10, SalesQty, 0)) AS Oct,
SUM(IIF(MonthNo=11, SalesQty, 0)) AS Nov,
SUM(IIF(MonthNo=12, SalesQty, 0)) AS [Dec]
FROM #Sales s
GROUP BY ItemID
)
SELECT *,
x.Jan+x.Feb+x.Mar+x.Apr+x.May+x.Jun+x.Jul+x.Aug+x.Sep+x.Oct+x.Nov+x.Dec AS [Item Total],
IIF(x.Jan>0,1,0)+IIF(x.Feb>0,1,0)+IIF(x.Mar>0,1,0)+IIF(x.Apr>0,1,0)+IIF(x.May>0,1,0)
+IIF(x.Jun>0,1,0)+IIF(x.Jul>0,1,0)+IIF(x.Aug>0,1,0)+IIF(x.Sep>0,1,0)+IIF(x.Oct>0,1,0)
+IIF(x.Nov>0,1,0)+IIF(x.Dec>0,1,0) AS MonthCountWithSales
FROM x
August 19, 2019 at 1:24 pm
Thank you for your help, this works perfectly.
August 19, 2019 at 1:26 pm
Jonathan,
Your example worked well also. Thank you for your help
August 19, 2019 at 5:21 pm
To avoid any IF / CASE logic, I usually use this technique instead:
SIGN(Jan) + SIGN(Feb) + ...
In situations where the numbers could ever be negative, you'd need to include ABS() around the SIGN.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 19, 2019 at 6:41 pm
To avoid any IF / CASE logic, I usually use this technique instead:
SIGN(Jan) + SIGN(Feb) + ...
If situations where the numbers could ever be negative, you'd need to include ABS() around the SIGN.
Nice one Scott! I've never used the SIGN function.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply