locked
Traversing in a loop weekly RRS feed

  • Question

  • Hi,

    I have a loop in which I would like to Select query on weekly basis starting from the last date, essentially traversing backwards week-by-week.  For example the table below represents the sample data for the query within loop, without WHERE clause.  What I like to get from the first iteration, say 5 years length backwards from the last date, which is 16/05/2014.  In the second iteration the last date would be 08/05/2014, because that is the next backward week date and again for 5 years period from that date.

    II am not proficient with SQL especially when it comes to dates and I am looking for the neat approach as I have thousands of iterations and performance is the important factor here.

    Thanks

    Friday, June 6, 2014 9:34 AM

Answers

  • If you don't need a massive table (centuries and centuries of dates, just a few years), you could use master..spt_values, but keep in mind the table I used only goes up to 2048, and you'll have creates something with a built in limit that you might reach someday. 

    There's also nothing wrong with using this table to CREATE permanent versions of the tables either (so you use it once, at table creation time, rather than over and over).  You can also join it to itself a time or two and use row_number() for the numbering too.  There are 100 creative ways to achieve the same thing, as you've seen.  As long as they aren't giving wrong sequence numbers, pick whichever method you like best and that meets your needs.  (Most of the many articles you'll find are probably correct also).

    Most to the point though, I suggest making a permanent version of the calendar table (numbers table too), because once a person starts using a Numbers table and a Calendar table, they find lots of other reasons to use them that they hadn't thought of before (or that they were doing a different way), and since you now are in that category, make yours permanent too!

    EDIT: Also, there's a little paradigm shift taking place too: a natural reaction to creating a calendar table or numbers table with way more numbers than you need at the moment, is that it's wasteful.  Ok, don't create one so massive that it needs its own dedicated server (unless you really need it), but remember, as a relational database, handling data in sets is what it does best, so don't worry too much about creating one that seems too big.  The overall benefit to you and your organization (standardization), combined with the most likely outcome (no discernible performance difference, possibly even faster than create plus use on the fly) says just make the permanent table.

    • Edited by johnqflorida Monday, June 9, 2014 11:20 AM Added edit
    • Marked as answer by Kalman Toth Sunday, June 22, 2014 8:53 PM
    Monday, June 9, 2014 11:03 AM

All replies

  • Hi ,

      Can you please give us more details on what you are trying to achieve using these iteration so that we can help you with a better solution ?


    Best Regards Sorna

    Friday, June 6, 2014 11:53 AM
  • Thanks Soma,

    This loop is what I came up with so far and it seems its working:

    WHILE @weekCycleNo <= 2
    BEGIN	
    	SELECT @strikeValue = wk_und1 FROM @tmpWeekTable WHERE wk_rowNo = 1
    	SELECT @strikeDate = wk_date FROM @tmpWeekTable WHERE wk_rowNo = 1
    	
    	SELECT wk_date, wk_rowNo, wk_und1 
    	FROM @tmpWeekTable
    	WHERE wk_date <= @lastDate AND wk_date > DATEADD(yy, -6, @lastDate)
    	
    	SET @lastDate = DATEADD(ww, -1, @lastDate)
    	SET @weekCycleNo = @weekCycleNo + 1
    END

    Friday, June 6, 2014 12:29 PM
  • I've just noticed there are 2 lines that can be ignored, amended code is here:

    WHILE @weekCycleNo <= 2
    BEGIN	
    	SELECT wk_date, wk_rowNo, wk_und1 
    	FROM @tmpWeekTable
    	WHERE wk_date <= @lastDate AND wk_date > DATEADD(yy, -6, @lastDate)
    	
    	SET @lastDate = DATEADD(ww, -1, @lastDate)
    	SET @weekCycleNo = @weekCycleNo + 1
    END

    Friday, June 6, 2014 12:31 PM
  • It sounds like you are describing a running total.

    Please see:

    http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server

    Friday, June 6, 2014 1:39 PM
  • Does this solve your problem?

    declare @forumTable TABLE (date date, value float)
    declare @i int
    set @i = 0
    
    while @i < 2000
    begin
     insert into @forumTable (date,value)
     values (dateadd(day,-@i,getdate()), round(rand()*1000,2))
     set @i = @i + 1
    end
    
    declare @weeks int
    set @weeks = 0
    while @weeks < 5
    begin
     select dateadd(day,-datepart(weekday,date)+1,date) as weekStart, sum(value) as sumValue
      from @forumTable
      where dateadd(day,-datepart(weekday,date)+1,date) between dateadd(year,-5,dateadd(day,-@weeks*7,(select max(date) from @forumTable))) and dateadd(day,-@weeks*7,(select max(date) from @forumTable)) 
     group by dateadd(day,-datepart(weekday,date)+1,date)
     order by dateadd(day,-datepart(weekday,date)+1,date) desc
     set @weeks = @weeks + 1
    end
    
    

    Friday, June 6, 2014 2:17 PM
  • It's very likely that you can do what you want without any loop at all, and handle everything by treating your data as a set.  

    The example below created sample data in the first query via a trick (quick 'n dirty) using table MASTER..SPT_VALUES (a table that happens to exist in all recent versions of SQL), creates a "weekly calendar" table in the second (you'd tweak that for the start/end date ranges you want) also via the MASTER..SPT_VALUES trick, and it's the final query that is serving as an alternative to the loop (I'm partly guessing what you wanted to do, this just does a sum), driven by the weekly values from the weekly calendar table and summing the values in the sample data table.

    Simulate_Sample_Data:
    	Declare  @Sampledata Table(eventdate date, amount money)
    	Insert @SampleData
    		Select DateAdd(Day, -1 * Number, cast(sysdatetime() as date)), 1000 * rand(cast(newid() as varbinary))
    		  from master..spt_values
    		 where type = 'p'
    
    	Select * from @SampleData 
    
    Create_Sample_Weekly_Calendar_Table:
    	Declare @StartDate date = '2014-06-06'
    	Declare @CalendarTable Table(WeekNum int, EachWeekDate date)
    	Insert @CalendarTable
    	  Select number, DateAdd(day, Number * -7, @StartDate)
    	   from master..spt_values
    	   where type = 'p'
    
    	Select * from @CalendarTable
    
    Loopless_Report:
    	 Select EachWeekDate, Sum(Amount)
    	   From @CalendarTable C
    	  Inner join @SampleData S on S.eventDate <= C.EachWeekDate and S.EventDate > DateAdd(day, -7, C.EachWeekDate) 
    	  group by EachWeekDate
    	  order by EachWeekDate Desc
    It's almost always better to avoid loops when you can, specially if it's relatively easy to do.

    Friday, June 6, 2014 9:56 PM
  • 1) the only date format allowed in ANSI Standard SQL is ISO-8601 (yyyy-mm-dd); do not use your local dialect

    2) Google Calendar Tables and  build one keyed on the calendar dates which also includes the ISO week date (yyyyW[05][0-9]-[1-7]) 

     3) your search will become a BETWEEN predicate. SQL has no loops! It is a declarative language. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, June 7, 2014 9:52 PM
  • Thank you everyone for your replies.  John has kindly provided the implementation of Calendar table for weeks.  It looks that this what I need with the week numbers and dates.  Having these 2 columns I can Group by weeks.  

    I have also googled some other solutions and found few, much complicated ones, such as these: Calendar creation.

    But the above linked example provides more than I need I think.

    My question is it safe to use John's example above, although as he says is the (quick 'n dirty) ?  Or should I find another implementation as the above link ?

    Thanks

    Monday, June 9, 2014 8:57 AM
  • I prefer using a number table of my own rather than relying upon spt_values tables

    Here's what I use for CalendarTable

    http://visakhm.blogspot.in/2010/02/generating-calendar-table.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, June 9, 2014 9:02 AM
  • If you don't need a massive table (centuries and centuries of dates, just a few years), you could use master..spt_values, but keep in mind the table I used only goes up to 2048, and you'll have creates something with a built in limit that you might reach someday. 

    There's also nothing wrong with using this table to CREATE permanent versions of the tables either (so you use it once, at table creation time, rather than over and over).  You can also join it to itself a time or two and use row_number() for the numbering too.  There are 100 creative ways to achieve the same thing, as you've seen.  As long as they aren't giving wrong sequence numbers, pick whichever method you like best and that meets your needs.  (Most of the many articles you'll find are probably correct also).

    Most to the point though, I suggest making a permanent version of the calendar table (numbers table too), because once a person starts using a Numbers table and a Calendar table, they find lots of other reasons to use them that they hadn't thought of before (or that they were doing a different way), and since you now are in that category, make yours permanent too!

    EDIT: Also, there's a little paradigm shift taking place too: a natural reaction to creating a calendar table or numbers table with way more numbers than you need at the moment, is that it's wasteful.  Ok, don't create one so massive that it needs its own dedicated server (unless you really need it), but remember, as a relational database, handling data in sets is what it does best, so don't worry too much about creating one that seems too big.  The overall benefit to you and your organization (standardization), combined with the most likely outcome (no discernible performance difference, possibly even faster than create plus use on the fly) says just make the permanent table.

    • Edited by johnqflorida Monday, June 9, 2014 11:20 AM Added edit
    • Marked as answer by Kalman Toth Sunday, June 22, 2014 8:53 PM
    Monday, June 9, 2014 11:03 AM
  • Hi Visakh16, your code generates an error.  Your CTE tries to insert into @Calendar table, but then tries to select from CalendarTable.  I am not sure what it's doing.

    Monday, June 9, 2014 11:05 AM
  • Well, I have a problem using the the calendar table in my example.  I mean replacing While loop with Calendar table.  The query below is used within the While loop and I can't see how to restructure within the Calendar table:

    SELECT MAX(wk_und1) AS und1, YEAR(wk_date) AS wkYear, MONTH(wk_date) AS wkMonth,
    			DENSE_RANK() OVER(ORDER BY YEAR(wk_date)) As YearNo,
    			DENSE_RANK() OVER(ORDER BY MONTH(wk_date)) As MonthNo
    			FROM @tmpWeekTable
    			WHERE wk_date <= @lastDate AND wk_date > DATEADD(yy, -6, @lastDate)
    			GROUP BY YEAR(wk_date), MONTH(wk_date)
    			HAVING MAX(wk_und1) >= @strikeValue
    				AND YEAR(wk_date) > @startKickOutDate
    			ORDER BY YearNo, MonthNo

    There are few things that are selected/aggregated:

    1. For each iteration the query checks if the 'wk_und1' value is greater than the start value.  The start value corresponds to the start of the weekly cycle.

    2. Also, the data I like to output uses DENSE_RANK function to output number of the month, year etc.

    In other words the query is complex and I am not sure how to put that within the Calendar Aggregated join

    Monday, June 9, 2014 2:23 PM
  • I think the only way this can be done without the loop is by using recursive CTE, but that can be even slower to process.  I might be completely wrong but can't see it any other way to have number of aggregations within another/outer aggregation.
    Monday, June 9, 2014 7:43 PM
  • I'm having trouble visualizing the data to start with and the desired output.  If you can mock it up, most ideally via an easy to run Select statement that generates the sample data, we can take a stab at it.  What version of SQL Server?
    Tuesday, June 10, 2014 1:38 AM
  • Thank you John, unfortunately the requirement is changed and so grouping process will be different.  I think I might create a new thread here explaining more clearly.
    Tuesday, June 10, 2014 7:32 PM