T SQL Advance Report Month Range on the 16th - Looking For More Effect. approach
-
Greetings All,
I'm looking for suggestions on how to improve the script below.
Requirements: From the 1st of the month to the 15th, report on the current month plus 4 additional months ( total of five months). From the 16th of the month to the end of the month, advance the month range and report on the current month + 1 plus 4 additional months (total of five months). Example: August 1st - 15th would be August through December. On August 16th we would report on September through January 2020. The query needs to account for crossing the year divide.
I have scripted the following and I'm still testing, but would be interested in know if I can use a more efficient approach with maybe a While Loop or something. I included a screenshot of the data that would be returned. Summary total would happen after this section of script.
DECLARE @MONTHNUM varchar(2);
DECLARE @CurrentYear char(4);
DECLARE @NextYear char(4);
DECLARE @Daynum varchar(2);
SET @MonthNum = DatePart(month,getdate());
SET @CurrentYear = DatePart(Year,getdate());
SET @NextYear = DatePart(Year,getdate()) +1;
SET @Daynum = DatePart(day,getdate())-1;
IF @Daynum <= 15 and @Monthnum between 1 and 7
Select * From #MonthProjectedCounts Where monthnum between currentmonth and currentmonth +4;
else
IF @Daynum > 15 and @Monthnum between 1 and 7
Select * From #MonthProjectedCounts Where monthnum between currentmonth +1 and currentmonth +5;
IF @Daynum <= 15 and @Monthnum = 8
Select * From #MonthProjectedCounts Where Yearnum = 2019 and monthnum between currentmonth and currentmonth +4;
else
IF @Daynum > 15 and @Monthnum = 8
(
Select * From #MonthProjectedCounts Where (Yearnum = 2019 and monthnum between currentmonth +1 and currentmonth +3)
UNION
Select * From #MonthProjectedCounts Where (Yearnum = 2020 and Monthnum = 1)
);
IF @Daynum <= 15 and @Monthnum = 9
(
Select * From #MonthProjectedCounts Where Yearnum = 2019 and monthnum between currentmonth and currentmonth +3
UNION
Select * From #MonthProjectedCounts Where (Yearnum = 2020 and Monthnum = 1)
);
else
IF @Daynum > 15 and @Monthnum = 9
(
Select * From #MonthProjectedCounts Where (Yearnum = 2019 and monthnum between currentmonth +1 and currentmonth +2)
UNION
Select * From #MonthProjectedCounts Where (Yearnum = 2020 and Monthnum in (1,2))
);
...additional script for month 10,11,12
- This topic was modified 5 years, 3 months ago by rdsb_2170.
-
Use something like this to get your start and end dates:
DECLARE @StartDate DATE = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0);
DECLARE @DayNo TINYINT = DAY(GETDATE());
IF @DayNo > 15
SET @StartDate = DATEADD(MONTH, 1, @StartDate);
DECLARE @Enddate DATE = DATEADD(MONTH, 5, @StartDate);From then on, it's simply
SELECT ...
FROM ...
WHERE @StartDate>= DATEFROMPARTS(Yearnum,Monthnum,Daynum) and DATEFROMPARTS(Yearnum,Monthnum,Daynum) < @EndDateOf course, it would be far better if your source data contained a real date column, rather than INTs, because this would
a) Make your query easier to read and maintain, and
b) Open up the possibility of using indexes to make your query run faster.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I used a slightly different approach. First, I totally agree with Phil. It is much, MUCH easier to work with dates. Days and Months are cyclical and it's very hard to work with cyclical data. Dates are linear and are much easier to work with.
I set up a calendar table to indicate that my code would handle the change in month. The final formula can use any date expression where I have used the date field. You'll probably want to use
GETDATE().CREATE TABLE #calendar (dt DATE);
INSERT #calendar (dt)
VALUES
('20190801')
,('20190802')
,('20190803')
,('20190804')
,('20190805')
,('20190806')
,('20190807')
,('20190808')
,('20190809')
,('20190810')
,('20190811')
,('20190812')
,('20190813')
,('20190814')
,('20190815')
,('20190816')
,('20190817')
,('20190818')
,('20190819')
,('20190820')
,('20190821')
,('20190822')
,('20190823')
,('20190824')
,('20190825')
,('20190826')
,('20190827')
,('20190828')
,('20190829')
,('20190830')
,('20190831')
;
SELECT
dt
, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -15, c.dt)) + 1, 0) AS start_dt
, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -15, c.dt)) + 5, 0) AS end_dt
FROM #calendar AS c
;Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply