2

For the 1st 3 orders, a credit is given, so I am needing a way to flag the 1st 3 dates for a customer. My issue that I am having with my query is it flags the first 3 dates returned regardless of if they were the first 3 dates historically. In my sample data below, lets take custnum 123, the first 3 order dates are

01/04/2013
01/08/2013
01/22/2013

So each of those should be identified as 1st order, 2nd order, 3rd order. No other date for custnum 123 should be flagged as historically the first three orders are the orders above. Below is my sample data, and the query I have (which is inaccurate) that flags the first three dates the query returns NOT the first three dates historically! What should I change in order to flag the first three dates historically?

CREATE TABLE [Calendar] ( [CalendarDate] DATETIME )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '01/01/2013'
SET @EndDate = DATEADD(d, 1095, @StartDate)

WHILE @StartDate <= @EndDate
BEGIN
   INSERT INTO [Calendar] ( CalendarDate )
   SELECT @StartDate

   SET @StartDate = DATEADD(dd, 1, @StartDate)
END 
Create Table #silly
(
  ID int IDENTITY(1,1) PRIMARY KEY,
  custnum int not null,
  cheese varchar(100),
  orderdate date
)

Insert Into #silly Values 
('123', 'swiss', '01/04/2013'), 
('123', 'yum', '01/08/2013'), 
('123', 'aged', '01/22/2013'), 
('123','typenah', '02/01/2013'), 
('123', 'becher', '02/14/2014'),
('11', 'becher', '02/14/2014'),
('11', 'yum', '01/08/2013'), 
('23','typenah', '02/01/2013'),
('23', 'swiss', '01/04/2013')

Declare @date1 date, @date2 date
Set @date1 = '01/31/2013'
Set @date2 = '01/15/2015'

;with datestest as (
Select 
[b].[id]
,[b].[custnum]
,[b].[cheese]
,CAST([cal].[CalendarDate] As Date) As [Date],
b.orderdate
,Row_number() Over(Order by b.orderdate) rn
from [Calendar] cal
RIGHT join #silly b
ON CAST(cal.[CalendarDate] As DATE) = CAST(b.orderdate As Date)
where CAST(cal.[CalendarDate] As DATE) >= @date1 
and  CAST(cal.[CalendarDate] As DATE)  <= @date2
and b.custnum = '123'
)
Select  [id]
,[custnum]
,[cheese]
,[orderdate]
,case when rn=1 then '1st Order' 
when rn=2 then '2nd Order' 
when rn=3 then '3rd Order' 
else '' end As [Date] 
from datestest
  • What is the purpose of the join to the calendar table? – Aaron Bertrand Feb 22 '16 at 22:07
4

First, let's address the way you populate the calendar table - I think you hit a majority of my bad habits/best practices posts:

CREATE TABLE dbo.Calendar 
(
  CalendarDate datetime PRIMARY KEY
); 
  -- always use schema prefix
  -- always terminate statements with ;
  -- why datetime instead of date?
  -- why no key or clustered index? (I added one.)

DECLARE @StartDate datetime, @EndDate datetime;
  -- why not declare together?

SELECT @StartDate = '20130101', 
  -- never use regional, ambiguous formats
  @EndDate = DATEADD(DAY, 1095, @StartDate); 
  -- avoid lazy shorthand like d/dd
  -- also why not just add 3 years instead of manually figuring out # of days?

INSERT dbo.Calendar WITH (TABLOCK) (CalendarDate)
  SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate + 1))
    d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY object_id) - 1, @StartDate)
  FROM sys.all_objects;
  -- doesn't really matter for 1000 rows but generally a set-based query
  -- is going to be much more efficient than any kind of while loop

For more information see this series and tip:

Now, the problem with your query is that your filter only returns two rows from the table, and these are the only two rows that ROW_NUMBER() sees. So, I'm going to assume you want those two rows returned to be labeled "4th order" and "5th order"? Also, I'm not sure exactly what you need the calendar table for, but I'll keep it in for now, because maybe it's there for some reason you haven't revealed in your question.

DECLARE @date1 date = '20130131', @date2 date = '20150115';

;WITH datestest AS
(
  SELECT [b].[id] ,[b].[custnum] ,[b].[cheese],
  CAST([cal].[CalendarDate] AS date) AS [Date],
  b.orderdate, ROW_NUMBER() OVER (ORDER BY b.orderdate) AS rn
  FROM #silly AS b
  LEFT OUTER JOIN dbo.Calendar AS cal 
  -- RIGHT JOINs far less intuitive IMHO
  ON CAST(cal.[CalendarDate] AS date) = CAST(b.orderdate AS date)
  WHERE cal.[CalendarDate] <= @date2 
    -- don't need the cast here
    -- also, leave out the begin date filter here so that ROW_NUMBER() see all history
  AND b.custnum = '123'
)
Select [id] ,[custnum],[cheese],[orderdate],
OrderNumber = CASE 
WHEN rn = 1 THEN '1st Order' 
WHEN rn = 2 THEN '2nd Order' 
WHEN rn = 3 THEN '3rd Order' 
WHEN rn = 4 THEN '4th Order' 
WHEN rn = 5 THEN '5th Order' 
ELSE '' END 
FROM datestest
WHERE [Date] >= @date1;

Remove the WHEN rn = 4 / 5 lines if you really just want '' when it's not one of the first three orders.

And if you want to use this for more than one customer at a time, you can just add a partition clause to the OVER():

DECLARE @date1 date = '20130131', @date2 date = '20150115';

;WITH datestest AS
(
  SELECT [b].[id] ,[b].[custnum] ,[b].[cheese],
  CAST([cal].[CalendarDate] AS date) AS [Date], b.orderdate, 
    ROW_NUMBER() OVER (PARTITION BY b.custnum ORDER BY b.orderdate) AS rn
    -------------------^
  FROM #silly AS b
  LEFT OUTER JOIN dbo.Calendar AS cal 
  ON CAST(cal.[CalendarDate] AS date) = CAST(b.orderdate As date)
  WHERE cal.[CalendarDate] <= @date2 
)
Select [id] ,[custnum],[cheese],[orderdate],
OrderNumber = CASE 
WHEN rn = 1 THEN '1st Order' 
WHEN rn = 2 THEN '2nd Order' 
WHEN rn = 3 THEN '3rd Order' 
ELSE '' END 
FROM datestest
WHERE [Date] >= @date1;

Again, both of these queries can work just fine without the calendar table, so I'm not sure I understand its presence.

Ok, now, some links for reading, in order of appearance above:

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.