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