Help with Row Number

Question

Post reply

Help with Row Number

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
February 25, 2016 at 8:39 am

#316262

Hello,
The following query pulls back a number of dates.
select rcp.CalendarPeriodId
,rc.CalendarId
,rcp.CalendarYearId
,rcp.PeriodNumber
,rcp.PeriodStartDate,rcp.PeriodEndDate
,CASE WHEN GETDATE() BETWEEN rcp.PeriodStartDate AND rcp.PeriodEndDate THEN 1 ELSE 0 END AS 'CurrentPeriod'
from RentCalendarPeriod rcp
LEFT JOIN RentCalendarYear rcy ON rcy.CalenderYearId = rcp.CalendarYearId
LEFT JOIN RentCalendar rc ON rc.CalendarId = rcy.CalendarId
The CalenderID could be 1 - Weekly or 2 - Monthly. That table is the Rent Calendar.
Attached to each RentCalendar is a Year (The RentCalendarYear table) So each Calendar will have multiple CalendarYearIds
Attached to the RentCalenderYear, is the RentCalendarPeriod.
For the Weekly Calendar in Year 2015 as an example there will be 52 periods
For the Monthly Calendar in Year 2015 as an example there will be 12 periods
Each Period Number corresponds to 1 being the first period of a Calendar Year.
What I am trying to work out is that for any Calendar IDs I have (using the code above as a basis) I want to flag what periods are currently the "current" period, but then not only return the current period but the 11 periods prior to this, so in effect everytime you ran the statement it would pull back the last 12 periods of each Calendar you have.
Not sure if Row Number is the best thing to use and I can find the current period by the following -
,CASE WHEN GETDATE() BETWEEN rcp.PeriodStartDate AND rcp.PeriodEndDate THEN 1 ELSE 0 END AS 'CurrentPeriod'

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23000 More actions · Answer 1

Can you post some sample data please?

It will make it much easier to understand what you want and to come up with a solution.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

To create the table -

/****** Object: Table [dbo].[test] Script Date: 25/02/2016 16:01:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[test](

[CalendarPeriodId] [int] NOT NULL,

[CalendarId] [int] NULL,

[CalendarYearId] [int] NOT NULL,

[PeriodNumber] [int] NOT NULL,

[PeriodStartDate] [datetime] NOT NULL,

[PeriodEndDate] [datetime] NOT NULL,

[CurrentPeriod] [int] NULL

) ON [PRIMARY]

GO

I'll post the dates in a moment

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

TSQL Tryer (2/25/2016)
To create the table -
/****** Object: Table [dbo].[test] Script Date: 25/02/2016 16:01:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[CalendarPeriodId] [int] NOT NULL,
[CalendarId] [int] NULL,
[CalendarYearId] [int] NOT NULL,
[PeriodNumber] [int] NOT NULL,
[PeriodStartDate] [datetime] NOT NULL,
[PeriodEndDate] [datetime] NOT NULL,
[CurrentPeriod] [int] NOT NULL
) ON [PRIMARY]
GO
I'll post the dates in a moment

What does this table have to do with your original question? This looks like the table you might use to hold the results of the query you need help with???

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Maybe something like this?

select rcp.CalendarPeriodId

,rc.CalendarId

,rcp.CalendarYearId

,rcp.PeriodNumber

,rcp.PeriodStartDate,rcp.PeriodEndDate

,CASE WHEN GETDATE() BETWEEN rcp.PeriodStartDate AND rcp.PeriodEndDate THEN 1 ELSE 0 END AS 'CurrentPeriod'

from RentCalendarPeriod rcp

LEFT JOIN RentCalendarYear rcy ON rcy.CalenderYearId = rcp.CalendarYearId

LEFT JOIN RentCalendar rc ON rc.CalendarId = rcy.CalendarId

WHERE rcp.PeriodEndDate >= DATEADD( yy, -1, GETDATE())

AND rcp.PeriodStartDate <= GETDATE();

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 5

Sorry for the delay.

To make it easier I have placed my results in one table -

The table -

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Test](

[CalendarPeriodId] [int] NOT NULL,

[CalendarId] [int] NULL,

[CalendarYearId] [int] NOT NULL,

[PeriodNumber] [int] NOT NULL,

[PeriodStartDate] [datetime] NOT NULL,

[PeriodEndDate] [datetime] NOT NULL,

[CurrentPeriod] [int] NOT NULL

) ON [PRIMARY]

GO

To Insert the data I'm looking at -

INSERT INTO Test(CalendarPeriodId,CalendarId,CalendarYearId,PeriodNumber,PeriodStartDate,PeriodEndDate,CurrentPeriod)

SELECT '1','1','1','1','Apr 6 2015 12:00AM','Apr 12 2015 12:00AM','0' UNION ALL

SELECT '2','1','1','2','Apr 13 2015 12:00AM','Apr 19 2015 12:00AM','0' UNION ALL

SELECT '3','1','1','3','Apr 20 2015 12:00AM','Apr 26 2015 12:00AM','0' UNION ALL

SELECT '4','1','1','4','Apr 27 2015 12:00AM','May 3 2015 12:00AM','0' UNION ALL

SELECT '5','1','1','5','May 4 2015 12:00AM','May 10 2015 12:00AM','0' UNION ALL

SELECT '6','1','1','6','May 11 2015 12:00AM','May 17 2015 12:00AM','0' UNION ALL

SELECT '7','1','1','7','May 18 2015 12:00AM','May 24 2015 12:00AM','0' UNION ALL

SELECT '8','1','1','8','May 25 2015 12:00AM','May 31 2015 12:00AM','0' UNION ALL

SELECT '9','1','1','9','Jun 1 2015 12:00AM','Jun 7 2015 12:00AM','0' UNION ALL

SELECT '10','1','1','10','Jun 8 2015 12:00AM','Jun 14 2015 12:00AM','0' UNION ALL

SELECT '11','1','1','11','Jun 15 2015 12:00AM','Jun 21 2015 12:00AM','0' UNION ALL

SELECT '12','1','1','12','Jun 22 2015 12:00AM','Jun 28 2015 12:00AM','0' UNION ALL

SELECT '13','1','1','13','Jun 29 2015 12:00AM','Jul 5 2015 12:00AM','0' UNION ALL

SELECT '14','1','1','14','Jul 6 2015 12:00AM','Jul 12 2015 12:00AM','0' UNION ALL

SELECT '15','1','1','15','Jul 13 2015 12:00AM','Jul 19 2015 12:00AM','0' UNION ALL

SELECT '16','1','1','16','Jul 20 2015 12:00AM','Jul 26 2015 12:00AM','0' UNION ALL

SELECT '17','1','1','17','Jul 27 2015 12:00AM','Aug 2 2015 12:00AM','0' UNION ALL

SELECT '18','1','1','18','Aug 3 2015 12:00AM','Aug 9 2015 12:00AM','0' UNION ALL

SELECT '19','1','1','19','Aug 10 2015 12:00AM','Aug 16 2015 12:00AM','0' UNION ALL

SELECT '20','1','1','20','Aug 17 2015 12:00AM','Aug 23 2015 12:00AM','0' UNION ALL

SELECT '21','1','1','21','Aug 24 2015 12:00AM','Aug 30 2015 12:00AM','0' UNION ALL

SELECT '22','1','1','22','Aug 31 2015 12:00AM','Sep 6 2015 12:00AM','0' UNION ALL

SELECT '23','1','1','23','Sep 7 2015 12:00AM','Sep 13 2015 12:00AM','0' UNION ALL

SELECT '24','1','1','24','Sep 14 2015 12:00AM','Sep 20 2015 12:00AM','0' UNION ALL

SELECT '25','1','1','25','Sep 21 2015 12:00AM','Sep 27 2015 12:00AM','0' UNION ALL

SELECT '26','1','1','26','Sep 28 2015 12:00AM','Oct 4 2015 12:00AM','0' UNION ALL

SELECT '27','1','1','27','Oct 5 2015 12:00AM','Oct 11 2015 12:00AM','0' UNION ALL

SELECT '28','1','1','28','Oct 12 2015 12:00AM','Oct 18 2015 12:00AM','0' UNION ALL

SELECT '29','1','1','29','Oct 19 2015 12:00AM','Oct 25 2015 12:00AM','0' UNION ALL

SELECT '30','1','1','30','Oct 26 2015 12:00AM','Nov 1 2015 12:00AM','0' UNION ALL

SELECT '31','1','1','31','Nov 2 2015 12:00AM','Nov 8 2015 12:00AM','0' UNION ALL

SELECT '32','1','1','32','Nov 9 2015 12:00AM','Nov 15 2015 12:00AM','0' UNION ALL

SELECT '33','1','1','33','Nov 16 2015 12:00AM','Nov 22 2015 12:00AM','0' UNION ALL

SELECT '34','1','1','34','Nov 23 2015 12:00AM','Nov 29 2015 12:00AM','0' UNION ALL

SELECT '35','1','1','35','Nov 30 2015 12:00AM','Dec 6 2015 12:00AM','0' UNION ALL

SELECT '36','1','1','36','Dec 7 2015 12:00AM','Dec 13 2015 12:00AM','0' UNION ALL

SELECT '37','1','1','37','Dec 14 2015 12:00AM','Dec 20 2015 12:00AM','0' UNION ALL

SELECT '38','1','1','38','Dec 21 2015 12:00AM','Dec 27 2015 12:00AM','0' UNION ALL

SELECT '39','1','1','39','Dec 28 2015 12:00AM','Jan 3 2016 12:00AM','0' UNION ALL

SELECT '40','1','1','40','Jan 4 2016 12:00AM','Jan 10 2016 12:00AM','0' UNION ALL

SELECT '41','1','1','41','Jan 11 2016 12:00AM','Jan 17 2016 12:00AM','0' UNION ALL

SELECT '42','1','1','42','Jan 18 2016 12:00AM','Jan 24 2016 12:00AM','0' UNION ALL

SELECT '43','1','1','43','Jan 25 2016 12:00AM','Jan 31 2016 12:00AM','0' UNION ALL

SELECT '44','1','1','44','Feb 1 2016 12:00AM','Feb 7 2016 12:00AM','0' UNION ALL

SELECT '45','1','1','45','Feb 8 2016 12:00AM','Feb 14 2016 12:00AM','0' UNION ALL

SELECT '46','1','1','46','Feb 15 2016 12:00AM','Feb 21 2016 12:00AM','0' UNION ALL

SELECT '47','1','1','47','Feb 22 2016 12:00AM','Feb 28 2016 12:00AM','1' UNION ALL

SELECT '48','1','1','48','Feb 29 2016 12:00AM','Mar 6 2016 12:00AM','0' UNION ALL

SELECT '49','1','1','49','Mar 7 2016 12:00AM','Mar 13 2016 12:00AM','0' UNION ALL

SELECT '50','1','1','50','Mar 14 2016 12:00AM','Mar 20 2016 12:00AM','0' UNION ALL

SELECT '51','1','1','51','Mar 21 2016 12:00AM','Mar 27 2016 12:00AM','0' UNION ALL

SELECT '52','1','1','52','Mar 28 2016 12:00AM','Apr 3 2016 12:00AM','0' UNION ALL

SELECT '53','2','2','1','Apr 2 2015 12:00AM','May 1 2015 12:00AM','0' UNION ALL

SELECT '54','2','2','2','May 2 2015 12:00AM','Jun 1 2015 12:00AM','0' UNION ALL

SELECT '55','2','2','3','Jun 2 2015 12:00AM','Jul 1 2015 12:00AM','0' UNION ALL

SELECT '56','2','2','4','Jul 2 2015 12:00AM','Aug 1 2015 12:00AM','0' UNION ALL

SELECT '57','2','2','5','Aug 2 2015 12:00AM','Sep 1 2015 12:00AM','0' UNION ALL

SELECT '58','2','2','6','Sep 2 2015 12:00AM','Oct 1 2015 12:00AM','0' UNION ALL

SELECT '59','2','2','7','Oct 2 2015 12:00AM','Nov 1 2015 12:00AM','0' UNION ALL

SELECT '60','2','2','8','Nov 2 2015 12:00AM','Dec 1 2015 12:00AM','0' UNION ALL

SELECT '61','2','2','9','Dec 2 2015 12:00AM','Jan 1 2016 12:00AM','0' UNION ALL

SELECT '62','2','2','10','Jan 2 2016 12:00AM','Feb 1 2016 12:00AM','0' UNION ALL

SELECT '63','2','2','11','Feb 2 2016 12:00AM','Mar 1 2016 12:00AM','1' UNION ALL

SELECT '64','2','2','12','Mar 2 2016 12:00AM','Apr 1 2016 12:00AM','0'

The original code I placed will show you that the Column "Current Period" is just marking which ever period is most current by looking at the system date.

That is the first part done.

Now that I know the Current Period for any Calendar I have. What I need to do is pull back the 11 previous periods of each Calendar and the Current Period - so all in all there would be 12 results for each Calendar.

So for CalendarID 1, the Current Period is 47. I need to also in my query pull back 46, 45, 44, 43, 42, 41, 40, 39, 38, 37, and 36. If they can also be ordered in that way too with the Current Period being 1 and in this case period 36 being 12.

Neil Burton SSC-Insane Points: 23000 More actions · Answer 6

SELECT TOP 12 ---Create a list of numbers from 0 - 12

N = IDENTITY(INT ,0,1)

INTO #tally

FROM

master.dbo.syscolumns sc1,

master.dbo.syscolumns sc2;

WITH curr_per AS --Find all the current period indicators

(

SELECT

periodnumber

,CalendarPeriodId

,CalendarYearId

FROM

test where CurrentPeriod = 1

)

SELECT

tt.*

FROM

dbo.testtt

JOIN curr_percp ON cp.CalendarYearId = tt.CalendarYearId

CROSS APPLY

(

SELECT

N

FROM #Tally

)t

WHERE

tt.PeriodNumber = (cp.PeriodNumber - t.N) -- Subtract each number in the table from the current period

ORDER BY

tt.PeriodNumber DESC

DROP TABLE

#tally

Does this do the trick? This article[/url] explains about tally tables in more depth than I ever could.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 7

That's spot on - it works!!

Just need to work out the logic of it now. But thank you that seems to have done the Trick.

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 8

The only thing I'm thinking is what if the Current Period is 1 but the year is say 2017 (next financial year....it would need to pull back the previous 11 periods from the previous year.

Neil Burton SSC-Insane Points: 23000 More actions · Answer 9

TSQL Tryer (2/26/2016)
The only thing I'm thinking is what if the Current Period is 1 but the year is say 2017 (next financial year....it would need to pull back the previous 11 periods from the previous year.

I'm not completely with you here. Do you mean if the Current Period is in the first twelve weeks of the year? Period number 11 for CalendarYearID 2 looks like this I think.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 10

BWFC (2/26/2016)

TSQL Tryer (2/26/2016)
The only thing I'm thinking is what if the Current Period is 1 but the year is say 2017 (next financial year....it would need to pull back the previous 11 periods from the previous year.

I'll provide the data to make myself more clear.

Thank you