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

    Unanswered: Looking to add 4 week totals to weekly totals in query

    Hi

    I have a query that works fine getting weekly totals for various GROUP BY fields in a table, but I now need to add the total of the current week and the 3 weeks prior to that. The output of the new query needs to be put in the same Excel spreadsheet as the old query, so I was hoping to use the same number of fields and a UNION.

    Here's the existing query:

    Code:
    SELECT
    		w.week_end_dt
    	,	DataType = '4week'
    	,	e.event_type_name
    	,	c.icon_channel_name
    	,	ps.product_segment
    	,	BAC_Volume = SUM(CASE WHEN f.is_lender_loan = 1 THEN
    			CAST(f.loan_amt AS bigint)
    		ELSE 0
    		END)
    	,	SUM(CAST(f.loan_amt AS bigint)) AS ICON_Market_Volume
    
    FROM table1 f
    INNER JOIN table2 w
    	ON w.week_key = f.event_week_key
    INNER JOIN table3 e 
    	ON e.event_type_key = f.event_type_key
    INNER JOIN table4 c 
    	ON c.icon_channel_key = f.icon_channel_key
    INNER JOIN table5 ip 
    	ON ip.icon_product_key = f.icon_product_key
    INNER JOIN table6 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 > '2010-01-01'
    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
    The output from the existing query is fine - what I'd like to do is to create the same output and UNION it together with the existing data, but the new data should have totals for the past 4 weeks.

    Anyone know how I might do this?

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

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can use COMPUTE...BY to acomplish this.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    COMPUTE BY looks like a good option, but I'm still not sure how I'll get 4 week totals. I only have the week end date for the 4th week of the 4 week range in a field, so I'm still not sure how to pick out the 4 week end dates that I need from just one week end date.
    Make something idiot proof and someone will make a better idiot...

  4. #4
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Actually, I'm not sure that COMPUTE BY helps me in the way that I need.

    I guess what I really need is a way to extract each field from a subquery, but instead of a straight pull of BAC_Volume and ICON_Market_Volume I need the sum of the values for the last 4 weeks.

    Is this something that someone can give me some advice on?

    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
  •