January 30, 2015 at 7:03 am
I don't know what it is but I'm having a mental block yesterday and today. Maybe it is because it was a long week or that I'm busy thinking about Sunday (go Hawks!). If anyone could help me out today, I would appreciate it. Here is the scenario.
CREATE TABLE [dbo].[tblYearMonthTotals](
[YearMonth] [int] NOT NULL,
[CustomerNumber] [char](7) NOT NULL,
[TotalAmount] [numeric](13, 2) NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.tblYearMonthTotals VALUES ('200701','12345',100.00)
INSERT INTO dbo.tblYearMonthTotals VALUES ('200801','12345',100.00)
INSERT INTO dbo.tblYearMonthTotals VALUES ('200901','12345',100.00)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201001','12345',100.00)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201101','12345',100.00)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201201','12345',100.00)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201301','12345',100.00)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201401','12345',100.00)
INSERT INTO dbo.tblYearMonthTotals VALUES ('200701','67890',100.01)
INSERT INTO dbo.tblYearMonthTotals VALUES ('200801','67890',100.01)
INSERT INTO dbo.tblYearMonthTotals VALUES ('200901','67890',100.01)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201001','67890',100.01)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201101','67890',100.01)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201401','67890',100.01)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201101','10000',100.02)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201401','10000',100.02)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201401','20000',100.03)
INSERT INTO dbo.tblYearMonthTotals VALUES ('201001','30000',100.04)
What I need to do is find the customer that had greater than 0.00 in 2014 and no sales in 2012 and 2013, but had sales greater than 0.00 prior to 2012. In the dataset above, I would expect to see CustomerNumbers 10000 and 67890.
Thank you again for any help you can lend!
January 30, 2015 at 7:29 am
Nasty way to store the date. Can you not store the year and the month separately? Or as a proper Datetime set to the first day?
Either would make this easier.
This should work
WITH CleanedUp AS (
SELECT CAST(LEFT(CAST(ymt.YearMonth AS CHAR(6)),4) AS INT) AS Year, * FROM dbo.tblYearMonthTotals AS ymt
)
SELECT [CustomerNumber] FROM CleanedUp WHERE Year = 2014
INTERSECT
SELECT [CustomerNumber] FROM CleanedUp WHERE Year < 2012
EXCEPT
SELECT [CustomerNumber] FROM CleanedUp WHERE Year IN (2012, 2013)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2015 at 7:35 am
Thank you GilaMonster. Yeah, I'm working with an old database and this is how the data was stored. I would certainly have preferred not to store it in this manner. It appears what you provided with get me going in the right direction. I certainly do appreciate it. It is a lot better than the crappy hack I came up with this morning. Have a great weekend!
January 30, 2015 at 9:51 am
SELECT CustomerNumber,
SUM(CASE WHEN [YearMonth] BETWEEN 201401 AND 201412 THEN TotalAmount ELSE 0 END) AS Sales_2014,
SUM(CASE WHEN [YearMonth] BETWEEN 201201 AND 201312 THEN TotalAmount ELSE 0 END) AS Sales_2012_2013,
SUM(CASE WHEN [YearMonth] <= 201112 THEN TotalAmount ELSE 0 END) AS Sales_Prior_To_2012
FROM dbo.tblYearMonthTotals
GROUP BY CustomerNumber
HAVING
SUM(CASE WHEN [YearMonth] BETWEEN 201401 AND 201412 THEN TotalAmount ELSE 0 END) > 0 AND
SUM(CASE WHEN [YearMonth] BETWEEN 201201 AND 201312 THEN TotalAmount ELSE 0 END) = 0 AND
SUM(CASE WHEN [YearMonth] <= 201112 THEN TotalAmount ELSE 0 END) > 0
Edit: Frankly in this kind of situation, I think the YYYYMM actually makes it easier to code than a date/datetime, but maybe that's just me.
Edit2: I also assumed the possibility of returns, for example, +$125.00 in one month, then -$125.00 in the next month, resulting in a net of $0.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply