Can I use WHILE in a CTE?

  • Hi,

    I would like to have a while loop in a CTE but I wonder if that will work out the way I planned it.

    I am still learning in T-SQL so apologies if this may sound a bit foolish.

    My CTE has nested queries (joining with the previous query) but only the first query (Schedule1) is shown in the query below.

    I would like to have a WHILE loop in Schedule1 or in the SELECT-statement at the end.

    I'll explain at the end.

    A simplified extract of my query:

    DECLARE @startdate DATETIME

    DECLARE @enddate DATETIME

    SET @startdate = '2013-12-31'

    SET @enddate = '2014-08-31'

    ;WITH Schedule1 AS

    (

    SELECTh.res_id,

    'John Doe' AS fullname,

    wsl.DayStart,

    wsl.StartDate,

    wsl.ShiftID,

    t.datum AS DateField,

    (SELECT MAX(WeekDay) FROM WorkSchedules WHERE ShiftID = wsl.ShiftID) AS MaxDaysSchedule

    FROMhumres h

    LEFT OUTER JOIN workschedulelinks wsl ON h.res_id = wsl.Resource AND wsl.EndDate IS NULL

    LEFT OUTER JOIN workschedulegroups wsg ON wsl.GroupCode = wsg.Code AND wsg.ShiftID IS NOT NULL

    CROSS APPLY (SELECT * FROM [dbo].[AllDays] (wsl.StartDate, @enddate)) t

    WHEREh.res_id IN (79, 1131901, 1894)

    GROUP BY

    h.res_id,

    h.fullname,

    wsl.DayStart,

    wsl.StartDate,

    wsl.ShiftID,

    t.datum

    )

    SELECTs1.*

    FROMSchedule1 s1

    This query produces this result set (only first 10 rows):

    res_idfullnameDayStartStartDateShiftIDDateFieldMaxDaysSchedule

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-14 00:00:00.000 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-15 00:00:00.000 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-16 00:00:00.000 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-17 00:00:00.000 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-18 00:00:00.000 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-19 00:00:00.000 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-20 00:00:00.000 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-21 00:00:00.000 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-22 00:00:00.000 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-23 00:00:00.000 14

    The AllDays function generates all separate rows per datefield.

    What I would like to have is the following resultset:

    res_idfullnameDayStartStartDateShiftIDDateFieldMaxDaysSchedule Counter

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-14 00:00:00.000 14 8

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-15 00:00:00.000 14 9

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-16 00:00:00.000 14 10

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-17 00:00:00.000 14 11

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-18 00:00:00.000 14 12

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-19 00:00:00.000 14 13

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-20 00:00:00.000 14 14

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-21 00:00:00.000 14 1

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-22 00:00:00.000 14 2

    79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-23 00:00:00.000 14 3

    For John Doe the "8" in column DayStart is the starting value. It should then add up 1 for every next row until the value of 14 (column "MaxDaysSchedule") has been reached. This value should be shown in a new column "Counter". The next row should then start with 1 until 14 have been reached again. Then again starting with 1 until 14 etc. etc. until the last DateField has been reached.

    The next resource-id (not shown in example) may have a DayStart of i.e. 3 and a MaxDaySchedule of 7. The first row should then show 3 in the counter column; next row 4 in the counter column; next 5; next 6; next 7; next 1; next 2 etc. etc.

    Perhaps all can be solved avoiding a CTE but I would like to keep the CTE if possible because of its speed and readability.

    I hope someone is willing to give my a helping hand.

    Thanks!

  • This could be accomplished without a loop using the modulo operator.

    To get a coded answer, please provide DDL and sample data in a consumable format.

    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
  • I agree with Luis, a while loop is not needed in this query.

    😎

  • I have added CREATE and INSERT queries!

    Could you explain what you had in mind with the MOD function?

    Thx!

  • michielbijnen (8/31/2014)


    I have added CREATE and INSERT queries!

    Could you explain what you had in mind with the MOD function?

    Thx!

    Good job with the DDL and the sample data, will have a look at it tomorrow.

    😎

  • Hi ,Please try below if it's match with your desire result..

    May be it can be done with more simply way.

    I have alterd function alldays to include startdate for join.

    ALTER FUNCTION [dbo].[AllDays] (@startdate DATETIME, @enddate DATETIME)

    RETURNS @alldays TABLE

    (

    startdate DATETIME,

    datum DATETIME

    )

    AS

    BEGIN

    DECLARE @i DATETIME

    SELECT @i = @startdate

    WHILE @i <= @enddate

    BEGIN

    INSERT INTO @alldays

    (

    startdate,

    datum

    )

    VALUES

    (

    @startdate,

    @i

    )

    SELECT @i = @i + 1

    END

    RETURN

    END

    GO

    DECLARE @enddate DATETIME

    SET @enddate = '2014-08-31'

    SELECT H.res_id,

    fullname,

    WSL.DayStart,

    WSL.StartDate,

    WSL.ShiftID,

    T.datum AS DATEFIELD,

    (

    SELECT MAX(WeekDay)

    FROM WorkSchedules

    WHERE ShiftID = WSL.ShiftID

    )

    AS MAXDAYSSCHEDULE,

    CASE

    WHEN ABS(DATEDIFF(DAY, WSL.StartDate, T.datum)) + WSL.DayStart <=

    (

    SELECT MAX(WeekDay)

    FROM WorkSchedules

    WHERE ShiftID = WSL.ShiftID

    )

    THEN ROW_NUMBER() OVER (PARTITION BY (DATEDIFF(DAY, WSL.StartDate, T.datum) + WSL.DayStart - 1) /

    (

    SELECT MAX(WeekDay)

    FROM WorkSchedules

    WHERE ShiftID = WSL.ShiftID

    ), Res_id ORDER BY DATEDIFF(DAY, WSL.StartDate, T.datum) ,Res_id)+wsl.DayStart-1

    ELSE ROW_NUMBER() OVER (PARTITION BY (DATEDIFF(DAY, WSL.StartDate, T.datum) + WSL.DayStart - 1) /

    (

    SELECT MAX(WeekDay)

    FROM WorkSchedules

    WHERE ShiftID = WSL.ShiftID

    ), Res_id ORDER BY DATEDIFF(DAY, WSL.StartDate, T.datum) , Res_id)

    END [Counter]

    FROM humres H

    LEFT JOIN workschedulelinks WSL ON H.res_id = WSL.Resource

    AND WSL.EndDate IS NULL

    CROSS APPLY [dbo].[AllDays](WSL.StartDate, @enddate) T

    WHERE T.startdate = WSL.StartDate

    --and h.res_id IN (79, 233, 321) --= 1131901

    ORDER BY Res_ID,

    DATEFIELD

  • I haven't read all of Megha's code. I stopped at the function because that will be extremely unefficient. Check this alternative that uses an inline table valued function that will run a lot faster. It uses an in-memory tally table (at least the principle) to generate all dates. To learn more about tally tables and how they replace loops, check the following article: http://www.sqlservercentral.com/articles/T-SQL/62867/

    ALTER FUNCTION [dbo].[AllDays]

    (

    @startdate DATETIME,

    @enddate DATETIME

    )

    RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN

    WITH E1(n) AS(

    SELECT n FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n) --10 rows

    ),

    E2(n) AS(

    SELECT a.n FROM E1 a, E1 b --10 x 10 = 100 rows

    ),

    E4(n) AS(

    SELECT a.n FROM E2 a, E2 b --100 x 100 = 10,000 rows (enough for over 27 years)

    )

    SELECT TOP (DATEDIFF( dd, @startdate, @enddate) + 1)

    DATEADD( dd, ROW_NUMBER() OVER( ORDER BY ( SELECT NULL)) - 1, @startdate) datum

    FROM E4;

    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
  • Here's my version of the code. I just added a column to your select. Be sure to understand how does it work and ask any questions that you have.

    The startdate column in the function is not needed at all. It makes no sense to have a constant returned.

    ;WITH Schedule1 AS

    (

    SELECTh.res_id,

    'John Doe' AS fullname,

    wsl.DayStart,

    wsl.StartDate,

    wsl.ShiftID,

    t.datum AS DateField,

    (SELECT MAX(WeekDay) FROM WorkSchedules WHERE ShiftID = wsl.ShiftID) AS MaxDaysSchedule

    FROMhumres h

    LEFT OUTER JOIN workschedulelinks wsl ON h.res_id = wsl.Resource AND wsl.EndDate IS NULL

    LEFT OUTER JOIN workschedulegroups wsg ON wsl.GroupCode = wsg.Code AND wsg.ShiftID IS NOT NULL

    CROSS APPLY (SELECT * FROM [dbo].[AllDays] (wsl.StartDate, @enddate)) t

    WHEREh.res_id IN (79, 1131901, 1894)

    GROUP BY

    h.res_id,

    h.fullname,

    wsl.DayStart,

    wsl.StartDate,

    wsl.ShiftID,

    t.datum

    )

    SELECTs1.res_id,

    s1.fullname,

    s1.DayStart,

    s1.StartDate,

    s1.ShiftID,

    s1.DateField,

    s1.MaxDaysSchedule,

    ISNULL( NULLIF((DATEDIFF( DD, s1.StartDate, s1.DateField) + DayStart) % s1.MaxDaysSchedule, 0), s1.MaxDaysSchedule)

    FROMSchedule1 s1

    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
  • .... What can I say? I have read it 10 times now and it sounds like THE MASTER is severely correcting one of his students.

    Thanks for your reply anyhow! I try to learn from it.

  • Thanks a lot Luis!

    I understand your code and it works perfectly!

  • You're welcome.

    I hope that you've learned something from this. The main lesson would be that you don't need to count yourself because SQL Server will do it for you. The main difference between SQL and procedural languages is that it's designed to work on sets and you should be doing set-based programming instead of procedural-programming. It's not easy to change your mind, the first step is to think on what you want to do with a column and not with a row (sounds simple but it might be simple for everyone).

    Some statements from Celko are correct such as promoting set-based code, but some others are just plain BS such as not using variables or cross apply or adding a new column.

    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
  • michielbijnen (9/1/2014)


    .... What can I say? I have read it 10 times now and it sounds like THE MASTER is severely correcting one of his students.

    Thanks for your reply anyhow! I try to learn from it.

    I suggest you pay no attention to Joe C in these forums - he is completely incapable of dealing in a civilised manner with learners, and is so busy telling them they are wrong (by his standards, which are arbitrary and sometimes unreal) that he forgets to tell them anything useful. In direct conversation he is much more sensible, and his books are not bad, but in forums....

    Look instead at what Luis posted, put the code in his two posts together and see if it does what you need. If not, tell us how the result differs from what you want.

    Tom

  • Just to note, you dont really need the GROUP BY clause in this example. The "SELECT MAX(WeekDay)" part is not really an aggregation on the previous columns listed. But it does not affect your results either.

    ----------------------------------------------------

  • CELKO (10/16/2014)


    .. BS such as not using variables or cross apply or adding a new column.

    Avoiding variables is something in any books on functional programming today. Have you ever has a LISP or APL class? Probably not; so look at F# in the Microsoft world. One of their demos is math problem that they write as a procedural program in F#, improve it a bit then write it as a single recursive statement. The final version is several orders of magnitude faster and smaller. Start with FUNCTIONAL PROGRAMMING THROUGH LAMBDA CALCULUS by Greg Michaelson (978-0-486-47883-8) and get a feel for the mindset.

    Remember what a pain it was to learn Structured Programming (SP) in the 1970's? No more GOTO's and labels, no more jumping in and out of loops. There was lots of bitching about this new bulshit. Your first efforts with the new tools of software engineering were clumsy and a mix of SP and the old mindset. SQL programmers are in the same situation today. Local variables are a big part of the old mindset you need to get rid of.

    CROSS APPLY is dialect; you need a damn good reason for heavy dialect in your code. This is a basic principle of Software Engineering.

    If a new column is an attribute that was left out of the data model, then add it, of course. But if it is computed or redundant in some way, then use a virtual column. A modern CPU can do the math faster than it can read a disk.

    Damn it, Joe, why don't you always adopt that as your style in these forums? I'm sure that it would actually get your points over much mre effectively than your usual intemperate diatribes. The advice you've given in the quoted message is excellent, is phrased in a way to encourage people to follow it instead of treating it as an insult and therefor ignoring it, and doesn't include anything that is just a personal point of view. The only thing I might disagree with is the choice of book to recommend. Obviously I couldn't recommend the old Darlington, Henderson, and Turner (eds) book although I liked it 30+ years ago and Know all three editors because it may be too difficult for beginners and anyway it's out of print, but I've heard much good of Neal Ford's "Thinking Functionally" and although I haven't looked at it myself what I hear of it suggests that it might be easier for modern SQL people - who may have learnt awful languages like C++ or VB and have probably learnt all about cursors and control flow and variables in whatever dialect of SQL they have used - than Greg's book.

    Tom

Viewing 14 posts - 1 through 13 (of 13 total)

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