September 28, 2017 at 11:59 pm
Hi,
I'm trying to summarize some sales figures per customer per Year-Month but can't include the months where customer hasn't bought anything.
I have a calendar table defined and the invoicing data on a separate table. I want to be able to list results like below but I can't include the months that have 0 invoicing.
YearMonth Customer Invoiced
2017-01 A 100
2017-02 A 0
2017-03 A 0
2017-04 A 400
2017-01 B 50
2017-02 B 60
2017-03 B 0
2017-04 B 100
Thanks for the help!
September 29, 2017 at 2:10 am
A Calendar table is the right method, so if it's not working I would guess you're SQL isn't right.
You need to use the dates in your Calendar table as your groupings, and LEFT JOIN onto your data table. An example of how to achieve it (this is untested) would be:SELECT CONVERT(varchar(4),CT.Year) + CT.Monthame AS YearMonth,
YT.Customer,
SUM(InvoiceAmount) AS Invoiced
FROM CalendarTablle CT
LEFT JOIN YourTable YT ON CT.DateColumn = YT.InvoiceDate
GROUP BY CT.MonthName, CT.Year,
YT.Customer;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 29, 2017 at 8:22 am
Hi Thom,
Thanks for your reply. The problem with that one is that I don't get a YearMonth and customer combination with 0 amounts.
Give this a try and you'll see what I mean. For example customer A doesn't have month 2. Customer B doesn't have months 3 and 4.
declare @Calendar table ([Year] int, [Month] int)
insert into @Calendar values (2017, 1)
insert into @Calendar values (2017, 2)
insert into @Calendar values (2017, 3)
insert into @Calendar values (2017, 4)
insert into @Calendar values (2017, 5)
insert into @Calendar values (2017, 6)
insert into @Calendar values (2017, 7)
insert into @Calendar values (2017, 8)
insert into @Calendar values (2017, 9)
--select * from @Calendar
declare @CustomerData table ([Year] int, [Month] int, CustomerCode varchar(10), InvoiceAmt decimal)
insert into @CustomerData values (2017, 1, 'A', 10)
insert into @CustomerData values (2017, 3, 'A', 30)
insert into @CustomerData values (2017, 2, 'B', 20)
insert into @CustomerData values (2017, 5, 'B', 50)
--select * from @CustomerData
SELECT
CAL.[Year],
CAL.[Month],
CUST.CustomerCode,
SUM(CUST.InvoiceAmt) As Invoiced
FROM @Calendar as CAL
LEFT OUTER JOIN @CustomerData as CUST
ON
CAL.[Year] = CUST.[Year] and
CAL.[Month] = CUST.[Month]
GROUP BY
CAL.[Year],
CAL.[Month],
CUST.CustomerCode
September 29, 2017 at 8:30 am
You didn't specify any expected results based on the sample data you provided, so I'm just guessing here, but what happens if you change the outer join to an inner join?
John
September 29, 2017 at 8:31 am
Well what are you expecting for a customer code if there is no data for that month? You can do something like this SUM(ISNULL(CUST.InvoiceAmt, 0)) As Invoiced if you need a value in the amount.
September 29, 2017 at 8:33 am
Try this:
declare @Calendar table ([Year] int, [Month] int)
insert into @Calendar values (2017, 1)
insert into @Calendar values (2017, 2)
insert into @Calendar values (2017, 3)
insert into @Calendar values (2017, 4)
insert into @Calendar values (2017, 5)
insert into @Calendar values (2017, 6)
insert into @Calendar values (2017, 7)
insert into @Calendar values (2017, 8)
insert into @Calendar values (2017, 9)
--select * from @Calendar
declare @CustomerData table ([Year] int, [Month] int, CustomerCode varchar(10), InvoiceAmt decimal)
insert into @CustomerData values (2017, 1, 'A', 10);
insert into @CustomerData values (2017, 3, 'A', 30);
insert into @CustomerData values (2017, 2, 'B', 20);
insert into @CustomerData values (2017, 5, 'B', 50);
--select * from @CustomerData
WITH CUSTOMERS AS (
SELECT DISTINCT CustomerCode
FROM @CustomerData
)
SELECT
CAL.[Year],
CAL.[Month],
C.CustomerCode,
ISNULL(SUM(CUST.InvoiceAmt), 0) AS Invoiced
FROM @Calendar AS CAL
CROSS APPLY CUSTOMERS AS C
LEFT OUTER JOIN @CustomerData AS CUST
ON CAL.[Year] = CUST.[Year]
AND CAL.[Month] = CUST.[Month]
AND C.CustomerCode = CUST.CustomerCode
GROUP BY CAL.[Year],
CAL.[Month],
C.CustomerCode;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 29, 2017 at 8:44 am
Perfect! Thanks a lot for the help. That's exactly what I was after 🙂
Cheers!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply