Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Unanswered: Rolling 4 week sum

    Hi

    I'm trying to work out how I can get a sum of a rolling 4 weeks from data. I have a table with a week end date field and two fields with weekly totals in them.

    For output I'm looking for a week end date and a total for each of the weekly total fields that represents the total of the past 4 weeks.

    Is there a way of doing this?

    Thanks
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I got lost half way through reading; can you illustrate your problem with sample data and desired output?

    It's also good to note your SQL Server version; from my understanding the answer lies within windowed functions, but these aren't available prior to 2005.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Code:
    ALTER FUNCTION dbo.weekend (@we datetime)
    RETURNS datetime
    AS
      BEGIN 
        RETURN DateAdd(dd, (-1 * Coalesce(NullIf(DateDiff(dd, 5, @we) % 7, 0), 7)) + 7, @we)
      END
    here is sum code that take a date value return a sat date then sum by that which will give you the sum of a week them do top 4 by the weekend date that should start you down the right track problem take a couple of querys view to get the right answer
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Thanks! I think I'm getting there.
    Make something idiot proof and someone will make a better idiot...

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are just dealing with weekly data and need a rolling total for four weeks, your most efficient method would be to add a "previous week end date" column and self join four instances of the table together.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    That sounds plausible - could you give me a simple example of how to perform that kind of self join? I could probably add the additional columns quite easily.
    Make something idiot proof and someone will make a better idiot...

  7. #7
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    For clarity, here is a query that does what I want to it do:

    Code:
    SELECT
    		week1 = w.week_end_dt
    	,	DataType = '4week'
    	,	event_name = e.event_type_name
    	,	channel = c.icon_channel_name
    	,	segment = ps.product_segment
    	,	BAC_Volume = (SELECT SUM(CASE WHEN f1.is_lender_loan = 1 THEN
    						CAST(f1.loan_amt AS bigint)
    						ELSE 0
    						END)
    						FROM cmp.fact_icon_detail_volume f1
    						INNER JOIN cmn.dim_week w1
    							ON w1.week_key = f1.event_week_key
    								INNER JOIN cmn.dim_event_type e1 
    									ON e1.event_type_key = f1.event_type_key
    								INNER JOIN cmp.dim_icon_channel c1 
    									ON c1.icon_channel_key = f1.icon_channel_key
    								INNER JOIN cmp.dim_icon_product ip1
    									ON ip1.icon_product_key = f1.icon_product_key
    								INNER JOIN cmn.dim_product_segment ps1
    									ON ps1.product_segment_key = ip1.product_segment_key
    								WHERE ip1.product_segment_key IN (1, 2, 3)
    								AND	e1.event_type_name IN ('Funded', 'Lock')
    								--AND	w1.week_end_dt > '2010-01-01'
    								AND w1.week_end_dt >= DATEADD(day, -21, w.week_end_dt)
    								AND e1.event_type_name = e.event_type_name
    								AND c1.icon_channel_name = c.icon_channel_name
    								AND ps1.product_segment = ps.product_segment)
    	,	ICON_Market_Volume = (SELECT SUM(CAST(f2.loan_amt AS bigint))
    								FROM cmp.fact_icon_detail_volume f2
    								INNER JOIN cmn.dim_week w2
    									ON w2.week_key = f2.event_week_key
    								INNER JOIN cmn.dim_event_type e2 
    									ON e2.event_type_key = f2.event_type_key
    								INNER JOIN cmp.dim_icon_channel c2 
    									ON c2.icon_channel_key = f2.icon_channel_key
    								INNER JOIN cmp.dim_icon_product ip2
    									ON ip2.icon_product_key = f2.icon_product_key
    								INNER JOIN cmn.dim_product_segment ps2
    									ON ps2.product_segment_key = ip2.product_segment_key
    								WHERE ip2.product_segment_key IN (1, 2, 3)
    								AND	e2.event_type_name IN ('Funded', 'Lock')
    								--AND	w2.week_end_dt > '2010-01-01'
    								AND w2.week_end_dt >= DATEADD(day, -21, w.week_end_dt)
    								AND e2.event_type_name = e.event_type_name
    								AND c2.icon_channel_name = c.icon_channel_name
    								AND ps2.product_segment = ps.product_segment)
    
    FROM cmp.fact_icon_detail_volume f
    INNER JOIN cmn.dim_week w
    	ON w.week_key = f.event_week_key
    INNER JOIN cmn.dim_event_type e 
    	ON e.event_type_key = f.event_type_key
    INNER JOIN cmp.dim_icon_channel c 
    	ON c.icon_channel_key = f.icon_channel_key
    INNER JOIN cmp.dim_icon_product ip 
    	ON ip.icon_product_key = f.icon_product_key
    INNER JOIN cmn.dim_product_segment ps
    	ON ps.product_segment_key = ip.product_segment_key
    WHERE ip.product_segment_key IN (1, 2, 3)
    AND	e.event_type_name IN ('Funded', 'Lock')
    AND	w.week_end_dt > 
    	(SELECT DATEADD(day, - (182 + 
    		(DATEDIFF(day, (DATEADD(dd,-(DATEPART(dw, GETDATE()) - 1),GETDATE())), GETDATE()))
    	), GETDATE()))
    GROUP BY w.week_end_dt,	e.event_type_name, c.icon_channel_name,	ps.product_segment
    ORDER BY w.week_end_dt, e.event_type_name, c.icon_channel_name,	ps.product_segment
    but the problem is that it takes forever to run.

    I guess I really need to optimize this, but I'm not sure what I can do that will get better performance.
    Make something idiot proof and someone will make a better idiot...

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by robojam View Post
    That sounds plausible - could you give me a simple example of how to perform that kind of self join? I could probably add the additional columns quite easily.
    The join should be simple.

    select *
    from table as A
    inner join table as B on A.WeekEnd = B.PriorWeekEnd
    inner join table as C on B.WeekEnd = C.PriorWeekEnd
    inner join table as D on C.WeekEnd = D.PriorWeekEnd

    PriorWeekEnd can either be calculated by adjusting WeekEnd by seven days on the fly, or better yet it can be an actual column maintained by a trigger.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    OK, I get it. I'll try that.

    Thanks
    Make something idiot proof and someone will make a better idiot...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •