While Loop Data Issue

Question

While Loop Data Issue

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
July 14, 2022 at 1:52 am

#4063035

How to loop the data based on start and end dates below?
Declare @StartDate Datetime
Declare @EndDate Datetime
Select @StartDate = '20220611'
SET @EndDate = GETDATE()
WHILE @StartDate < = @EndDate
BEGIN
select distinct id from dbo.org where orgdate >= @StartDate and orgdate < @EndDate
SET @StartDate = DATEADD(dd, 7, @StartDate)
END

When i run the above statement, i will get 5 sets of records in each week based on start date. but end date will always take as todays date. I am looking for each week result set. for example start date is June 11,2022 that is saturday . end date should be june 17,2022 that is friday
Final data set should be like below date range sat to friday
June 11 - June 17
June 18 - June 24
June 25 - July 1
July 2 - july 8.

Viewing 5 posts - 1 through 4 (of 4 total)

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

ScottPletcher SSC Guru Points: 101119 More actions · Answer 1

Best practice is to use a tally table in preference to a loop, so I added that to the code. No test data provided so I couldn't test the code.

Declare @EndDate Datetime
Declare @StartDate Datetime

SET @StartDate = '20220611'
SET @EndDate = GETDATE()


;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
select distinct 
    o.id, 
    datename(month, StartDate) + ' ' + CAST(DAY(StartDate) AS varchar(2)) + ' - ' + 
    datename(month, EndDate) + ' ' + CAST(DAY(EndDate) AS varchar(2)) as DateRange
from dbo.org o
inner join cte_tally1000 t on t.number between 0 and 
    ceiling(datediff(day, @StartDate, @EndDate) / 7.0) - 1
cross apply (
    select 
        dateadd(day, t.number * 7, @StartDate) as StartDate, 
        dateadd(day, t.number * 7 + 6, @StartDate) as EndDate
) as calc1
where o.orgdate >= StartDate and o.orgdate < dateadd(day, 1, EndDate)
order by o.id

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

mcfarlandparkway wrote:

How to loop the data based on start and end dates below?
Declare @StartDate Datetime Declare @EndDate Datetime
Select @StartDate = '20220611' SET @EndDate = GETDATE()
WHILE @StartDate < = @EndDate BEGIN select distinct id from dbo.org where orgdate >= @StartDate and orgdate < @EndDate
SET @StartDate = DATEADD(dd, 7, @StartDate) END
When i run the above statement, i will get 5 sets of records in each week based on start date. but end date will always take as todays date. I am looking for each week result set. for example start date is June 11,2022 that is saturday . end date should be june 17,2022 that is friday
Final data set should be like below date range sat to friday
June 11 - June 17
June 18 - June 24
June 25 - July 1
July 2 - july 8.

You should have added that requirement to your previous post. 🙁

https://www.sqlservercentral.com/forums/topic/how-to-get-last-5-weeks-start-and-end-dates

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 3

I'm not sure what you are trying to do but if you install this function: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

Then this code might do the job:

Declare @StartDate Datetime
Declare @EndDate Datetime
Select @StartDate = '20220611'
SET @EndDate = GETDATE()

SELECT value WeekStart, DATEADD(dd, 6, dr.Value) WeekEnd, o.id 
  FROM dbo.DateRange(@StartDate, @EndDate, 'dd', 7) dr
 CROSS APPLY(select distinct o.id 
               from dbo.org o
              where o.orgdate >= dr.Value 
                and o.orgdate < getdate()) o

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Ok... in your last post at https://www.sqlservercentral.com/forums/topic/how-to-get-last-5-weeks-start-and-end-dates you wanted the weeks to be calculated based on whatever GETDATE() returned and you used the example of what you wanted on the "Today date" of '7/13/2022'. I'm going to continue in that vein for this post.

Here's the code using a variable for that specific date just to show you that it works according to your previous spec'd request but with individual weeks per your current request. To be sure, @Today can be any day of the week and it will return the start and end dates of the previous 4 weeks plus the current week, all starting on Saturdays and ending on Fridays. And, follow the instructions in the comments to make it work for GETDATE().

DECLARE @Today DATE = '7/13/2022';--For demo only. Remove when using GETDATE() below

 SELECT  WeekStart = dt.LoSat
        ,WeekEnd   = DATEADD(dd,6,dt.LoSat)
   FROM (VALUES(DATEADD(dd,DATEDIFF(dd,5,@Today)/7*7,5)))s(PrevSat) --Change @Today to GETDATE()
  CROSS APPLY(VALUES(-4),(-3),(-2),(-1),(0))t(WkOffset)
  CROSS APPLY(VALUES(DATEADD(wk,t.WkOffset,s.PrevSat)))dt(LoSat)
  ORDER BY WeekStart
;

And here are the results... just like you listed but also includes the 5th week, which is the current week.

Of course, if you play that against date with time then, as I warned before, you'll miss all but the first instant of the last day of the week because it starts at mid-night. To get the start of the next week so that you can use Closed/Open criteria to include all of the times for the last day of the week (regardless of datatype/precision), change the "6" in the "WeekEnd" formula to a "7" and the column name to suit and Bob's your uncle.

p.s. You've been on this site for more than 8 and a half years now... please learn how to use the code window and please stop thinking in loops. The latter will really help your career. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)