July 26, 2021 at 11:19 pm
I am trying to get the last day of the year from a date range that was given as parameters to be able to go through the number of years and in each iteration, determine if the start date and end date change to be able to assign a new date,
I have 2 variables, @startDate and @endDate, these are the general range that my cursor will go through, and I have 2 other variables, which are the parameters that are given to the date range of the query that I use to find data to the table , is it possible to determine the number of years to be able to iterate them and in each turn set the new dates?
startDate = '01-APR-15'
endDate = '30-APR-2021'
//Goal
StartDate |EndDate
01-APR-15 |31-DEC-15
01-JAN-16 |31-DEC-16
01-JAN-17 |31-DEC-17
01-JAN-18 |31-DEC-18
01-JAN-19 |31-DEC-19
01-JAN-20 |31-DEC-20
01-JAN-21 |30-APR-21
//something like that
while(startDate < endDate)
set @newEndDate = '31-DEC-15';
set @newStartDate = '01-JAN-16';
.
.
select * from where DATEFIELD between @newStartDate and @newEndDate
I attach my code, Im a little confused about it
DECLARE @StartDate datetime = '01-JAN-2015',
@endDate datetime = '30-APR-2021', @acumVarchar2 nvarchar(max) = '',
@length2 int, @COUNT2 INT = 0,
@myquery nvarchar(max),
@newStartDate datetime,
@newEndDate datetime,
@DATE22 nvarchar(4);
DECLARE insert_data CURSOR FOR WITH CTE as
(
select datepart(year, @StartDate) as yr
union all
select yr + 1 from CTE where yr < datepart(year, @endDate)
)
select yr from CTE
OPEN insert_data;
;with CTE as
(
select datepart(year, @StartDate) as yr
union all
select yr + 1 from CTE where yr < datepart(year, @endDate)
)
select @length2= COUNT(*) from CTE
FETCH NEXT FROM insert_data INTO @DATE22;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@COUNT2 < @length2-1)
BEGIN
set @acumVarchar2 = @acumVarchar2 + (@DATE22 + ', ')
SET @COUNT2 = @COUNT2 +1
END
ELSE IF(@COUNT2 = @length2-1)
BEGIN
set @acumVarchar2 = @acumVarchar2 + (@DATE22)
set @myquery = @acumVarchar2;
END
print @myquery;
-- here should be the validation of the new date
insert into TABLEWHERESAVEINFO(Key, Value)
select Key, Value
from TABLE1 ii left outer join
(
select * from pedimp
where DATEFIELD between @newStartDate and @newEndDate
) TABLE2 on table1.ID = table2.ID
FETCH NEXT FROM insert_data INTO @DATE22;
END;
CLOSE insert_data;
DEALLOCATE insert_data;
-- here is the print
-- 2015, 2016, 2017, 2018, 2019, 2020, 2021
July 27, 2021 at 11:11 am
Hello dqurve
Was also a bit confused by the cursory code sample... Hopefully, this will help.
Be aware it is often good to have a permanent calendar table available in your database. Such tables greatly simplifies date manipulation. For details on how to set one up see: https://www.sqlservercentral.com/scripts/calendar-table-function
In the sql below, the two CTEs that come first create a very basic calendar table 'on the fly' using recursion, see: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15
The calendar table is then queried using a simple group by statement.
declare @startDate date = '01-Apr-2015';
declare @endDate date = '30-Apr-2021';
with
-- recursive CTE to enumerate day offset numbers between supplied dates
myDayNumbers as
(
select 0 as num
union all
select num + 1 from myDayNumbers
where num < datediff(day,@startDate,@endDate)
),
-- create a mini calendar table
-- by converting the numeric offsets to set of dates
myDays as
(select dateadd(day,num,@startDate) as dt from myDayNumbers
)
-- using calendar table, get required 'goal' date pairs using simple 'group by' query
select min(dt) as StartDate, max(dt) as EndDate
from myDays
group by year(dt)
-- if can be more than 32767 days between start and end dates then set MAXRECURSION to 0
option (MAXRECURSION 32767);
July 27, 2021 at 12:12 pm
"...is it possible to determine the number of years to be able to iterate them and in each turn set the new dates?"
To determine the number of years "to iterate" (not!) you're looking for the number of times the year "boundary" was crossed between the 2 dates: 2015 to 2016 is boundary cross 1, 2016 to 2017 is boundary cross 2, etc.. According to the Docs the SQL function DATEDIFF:
"This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate."
DECLARE
@startDate date='01-JAN-2015',
@endDate date='30-APR-2021';
select datediff(year, @startDate, @endDate) dt_diff;
dt_diff
6
The 'datepart' is year and the year calendar boundary was crossed 6 times between Jan 1 2015 and Apr 30 2021.
How to generate the new rows required? This SSC article contains a walkthrough example and explanation of a really good way. The author of the article provides a very handy table valued function (tvf) called dbo.fnTally which can quickly generate as many rows as necessary. If you're polite the author is even quite responsive to questions.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/ (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;
select @startDate start_dt, fn.n tally_n,
dateadd(year, fn.n, @startDate) start_dt_plus_year
from dbo.fnTally(0, datediff(year, @startDate, @endDate)) fn;
start_dttally_nstart_dt_plus_year
2015-01-0102015-01-01
2015-01-0112016-01-01
2015-01-0122017-01-01
2015-01-0132018-01-01
2015-01-0142019-01-01
2015-01-0152020-01-01
2015-01-0162021-01-01
The 'tally_n' column contains the sequence generated by the tally function and the 'start_dt_plus_year' column contains the start date plus tally_n as year(s). This is nearly complete it just needs CASE logic to decode the calculated dates. The code needs to refer to the calculated years (6) in multiple places so it was extracted into it's own virtual table and aliased as 'yr'.
declare
@startDate date='01-JAN-2015',
@endDate date='30-APR-2021';
select @startDate start_dt, fn.n tally_n,
case when yr.dt_diff=fn.n
then @endDate
else datefromparts(year(@startDate)+fn.n, 12, 31) end answer
from (values (datediff(year, @startDate, @endDate))) yr(dt_diff)
cross apply dbo.fnTally(0, yr.dt_diff) fn
order by start_dt;
start_dttally_nanswer
2015-01-0102015-12-31
2015-01-0112016-12-31
2015-01-0122017-12-31
2015-01-0132018-12-31
2015-01-0142019-12-31
2015-01-0152020-12-31
2015-01-0162021-04-30
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 27, 2021 at 12:46 pm
It's the same idea with a clock as with a calendar
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 27, 2021 at 2:21 pm
Yeah, thanks a lot
July 27, 2021 at 2:22 pm
Alright, thank you for your answer
July 27, 2021 at 7:24 pm
You don't need to use the fnTally function - it can be done inline.
Declare @startDate date = '2015-04-15'
, @endDate date = '2021-04-30';
With t(n)
As (
Select t.n
From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, iTally (YearNumber)
As (
Select Top (datediff(year, @startDate, @endDate) + 1)
year(@startDate) + checksum(row_number() over(Order By @@spid)) - 1
From t t1, t t2 --100 years
)
Select StartDate = iif(it.YearNumber = year(@startDate), @startDate, datefromparts(it.YearNumber, 1, 1))
, EndDate = iif(it.YearNumber = year(@endDate), @endDate, datefromparts(it.YearNumber, 12, 31))
From iTally it;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply