5

I am wanting to pull data for a date range in 2017 then pull the same date range but for 2016. I thought I would get crafty and use DateAdd() feature, but I am missing something here as I get an error of

Msg 241, Level 16, State 1, Line 14
Conversion failed when converting date and/or time from character string.

What I want to do is get a COUNT(saledate) that fall between the time period which will give me the number of sales. This is sample DDL

Declare @startdate varchar(100), @enddate varchar(100), @prevyrstartdate varchar(100), @prevyrenddate varchar(100)

SET @startdate = '01/01/2017'
SET @enddate = '01/31/2017'
SET @prevyrstartdate = CAST(DateAdd(yy, -1, @startdate) As Date)
Set @prevyrenddate = CAST(DateAdd(yy, -1, @enddate) As Date)

Declare @HoldingPattern Table
(
    salesman varchar(100)
    ,saledate date
)

Insert Into @HoldingPattern (salesman, saledate) VALUES
('BOB', '2017-01-23')
,('BOB', '2017-01-30')
,('BOB', '2017-01-24')
,('BOB', '2017-01-30')
,('BOB', '2017-01-30')
,('BOB', '2017-01-30')
,('BOB', '2017-02-30')
,('BOB', '2017-02-27')
,('BOB', '2017-03-31')
,('BOB', '2017-03-27')
,('BOB', '2016-01-23')
,('BOB', '2016-01-30')
,('BOB', '2016-01-24')
,('BOB', '2016-01-30')
,('BOB', '2016-03-30')
,('BOB', '2016-03-30')
,('BOB', '2016-03-30')
,('BOB', '2016-02-27')
,('BOB', '2016-02-31')
,('BOB', '2016-02-27')


Select
salesman
,[2017 Data] = (Select COUNT(CAST(saledate As nvarchar(100))) FROM @HoldingPattern where saledate between @startdate AND @enddate)
,[2016 Data] = (Select COUNT(CAST(saledate As nvarchar(100))) FROM @HoldingPattern where saledate between @prevyrstartdate and @prevyrstartdate)
FROM @HoldingPattern
  • 2
    Why are you using unsafe formats like m/d/yyyy? Why are you using lazy shorthand like yy instead of typing YEAR? Why are you declaring your variables as varcharinstead of, I don't know, date? – Aaron Bertrand Feb 1 '17 at 17:54
  • @AaronBertrand - I have always used mm/dd/yyyy format, old habits die hard since that was the 1st formatting I used. I thought yy was the way to go, even looking at w3schools it makes me think so. Long term result parameters will be passed in from a .NET application and I have always had more success declaring as 'varchar` – Michael Mormon Feb 1 '17 at 17:58
  • More success = ignoring bugs, probably. Anyway your sample data has a real problem: 2017-02-30 is not a date at least on this planet. – Aaron Bertrand Feb 1 '17 at 18:01
  • 2
    @AaronBertrand - Yes, I used Feb and Mar 2017 as sample data. I extracted roughly 10 dates from my actual data, then just changed the month & year to make it work for this example. I did not mean to anger. Will def read articles posted by SqlZim – Michael Mormon Feb 1 '17 at 18:02
9

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:

  • Perhaps I don't fully understand the question; but I see 9 sales for 2016 and 9 for 2017? – Max Vernon Feb 1 '17 at 18:40
  • 1
    @Max not all in the same month - scroll to the right on the original question to see the where clause limits to January only. – Aaron Bertrand Feb 1 '17 at 18:44
1

Unless I misunderstand the requirements, this looks like a simple pivot query to me.

As @AaronBertrand notes, it is far superior to use the correct data type, and I've done so as well.

DECLARE @startdate date
    , @enddate date
    , @prevyrstartdate date
    , @prevyrenddate date;

SET @startdate = '2017-01-01';
SET @enddate = '2017-01-31';
SET @prevyrstartdate = DATEADD(YEAR, -1, @startdate);
SET @prevyrenddate = DATEADD(YEAR, -1, @enddate);

DECLARE @HoldingPattern TABLE
(
    salesman varchar(100) NOT NULL
    , saledate date NOT NULL
);

INSERT INTO @HoldingPattern (salesman, saledate) 
VALUES ('BOB', '2017-01-23')
        , ('BOB', '2017-01-30')
        , ('BOB', '2017-01-24')
        , ('BOB', '2017-01-30')
        , ('BOB', '2017-01-30')
        , ('BOB', '2017-01-30')
        --, ('BOB', '2017-02-30')
        , ('BOB', '2017-02-27')
        , ('BOB', '2017-03-31')
        , ('BOB', '2017-03-27')
        , ('BOB', '2016-01-23')
        , ('BOB', '2016-01-30')
        , ('BOB', '2016-01-24')
        , ('BOB', '2016-01-30')
        , ('BOB', '2016-03-30')
        , ('BOB', '2016-03-30')
        , ('BOB', '2016-03-30')
        , ('BOB', '2016-02-27')
        --, ('BOB', '2016-02-31')
        , ('BOB', '2016-02-27');

Here is the PIVOT. I'm adding a single day to each end-date so I can use a "less than" comparison, which would really only make a difference if saledate had a time component:

SELECT pvt.*
FROM (
    SELECT *
        , SaleYear = DATEPART(YEAR, hp.saledate)
    FROM @HoldingPattern hp
    WHERE (hp.saledate >= @startdate AND hp.saledate <= @enddate)
        OR (hp.saledate >= @prevyrstartdate AND hp.saledate <= @prevyrenddate)
    ) src
PIVOT (
    COUNT(src.saledate)
    FOR src.SaleYear  IN (
        [2016]
        , [2017]
    )
) pvt;

The results:

+----------+------+------+
| salesman | 2016 | 2017 |
+----------+------+------+
| BOB      |    4 |    6 |
+----------+------+------+

By the way, Aaron's methodology is slightly faster; I just wanted to add another way of getting to the same output.

  • 1
    Certainly, and if it were for more than two months or multiple years, I would probably lean toward pivot as well, unless at scale the more verbose approach was faster there too. Logically they should amount to the same thing, but it's always valuable to test when anything changes (data, index, optimizer). – Aaron Bertrand Feb 1 '17 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.