Comparing year over year data
Bad data, bad data types, and bad formatting aside, if you're getting a month of data for present and previous year, you don't need to have four variables; one will do. But it should be a date, not a string. I also recommend avoiding BETWEEN
since it only works in an extremely narrow set of circumstances.
DECLARE @startdate date = '20170101';
DECLARE @HoldingPattern TABLE
(
salesman varchar(100),
saledate date
);
-- let's ignore the illogical dates
INSERT @HoldingPattern (salesman, saledate) VALUES
('BOB', '20170123'),('BOB', '20170130'),('BOB', '20170124'),
('BOB', '20170130'),('BOB', '20170130'),('BOB', '20170130'),
/*('BOB', '20170230'),*/('BOB', '20170227'),('BOB', '20170331'),
('BOB', '20170327'),('BOB', '20160123'),('BOB', '20160130'),
('BOB', '20160124'),('BOB', '20160130'),('BOB', '20160330'),
('BOB', '20160330'),('BOB', '20160330'),('BOB', '20160227'),
/*('BOB', '20160231'),*/('BOB', '20160227');
SELECT
Salesman,
[2017 Data] = COUNT(CASE WHEN saledate >= @startdate
AND saledate < DATEADD(MONTH, 1, @startdate) THEN 1 END),
[2016 Data] = COUNT(CASE WHEN saledate >= DATEADD(YEAR, -1, @startdate)
AND saledate < DATEADD(YEAR, -1, DATEADD(MONTH, 1, @startdate)) THEN 1 END)
FROM @HoldingPattern
GROUP BY Salesman;
Result:
Salesman 2017 Data 2016 Data
-------- --------- ---------
BOB 6 4
Further reading:
- #BackToBasics : Dating Responsibly
- Bad habits to kick : mis-handling date / range queries
- Bad Habits to Kick : Using shorthand with date/time operations
- What do BETWEEN and the devil have in common?