February 25, 2016 at 8:39 am
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'
February 25, 2016 at 8:53 am
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 25, 2016 at 9:03 am
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
February 25, 2016 at 9:06 am
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/
February 25, 2016 at 9:09 am
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();
February 26, 2016 at 1:54 am
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.
February 26, 2016 at 2:47 am
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.test tt
JOIN curr_per cp 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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 26, 2016 at 3:03 am
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.
February 26, 2016 at 3:07 am
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.
February 26, 2016 at 4:51 am
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 26, 2016 at 6:22 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply