SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouping Rollup Sum not Working Correctly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bmannion3
Starting Member

USA
3 Posts

Posted - 09/03/2013 :  13:15:37  Show Profile  Reply with Quote
I have been trying to alter the following to include Mid-Week totals, however the sum is only producing the total for the Week and not respecting the grouping of the date range.

CREATE TABLE #LISTING (
      Period varchar(255) null,
      Sales numeric(18,0) null,
      Store int null
      )

Insert into #LISTING 
select
    case
        when grouping(d.m)=1 then 'Year ' + cast(max(d.y) as varchar(10))
        when grouping(d.w)=1 then 'Month ' + datename(m, max(Sales_Date)) + ' ' + cast(max(d.y) as varchar(10))
        when grouping(d.mw)=1 then 'Mid-Week ' + datename(m, max(ws)) + ' ' + cast(datepart(d, max(ws)) as varchar(20)) + ' - '
            + datename(m, max(mw)) + ' ' + cast(datepart(d, max(mw)) as varchar(20)) + ' - ' + cast(datepart(yy, max(mw)) as varchar(20))
        when grouping(Sales_Date)=1 then 'Week ' + datename(m, max(ws)) + ' ' + cast(datepart(d, max(ws)) as varchar(20)) + ' - '
            + datename(m, max(we)) + ' ' + cast(datepart(d, max(we)) as varchar(20)) + ' - ' + cast(datepart(yy, max(we)) as varchar(20))
        else cast(cast([Sales_Date] as date) as varchar(255))
    end as Period
    , sum(Amount) as Sales
    , Store_ID as Store
from KF_Store_Sales_Daily
    cross apply (
        select -- aux. expressions for dates
            datepart(yy, [Sales_Date]), -- year
            datepart(m, [Sales_Date]), -- month
            datepart(wk, [Sales_Date]), -- week
            dateadd(d, 4-datepart(w, Sales_Date), Sales_date),  --Mid-Week date
            dateadd(d, 1-datepart(w, Sales_date), Sales_date), -- week start
            dateadd(d, 7-datepart(w, Sales_date), Sales_date) -- week end
    ) d(y, m, w, mw, ws, we)
group by Store_ID, d.y, rollup (d.m, d.w, d.mw, Sales_Date)
order by d.y desc,
    grouping(d.m), d.m,
    grouping(d.w), d.w,
    grouping(d.mw), d.mw,
    grouping(Sales_Date), Sales_Date
    
GO

Select * from #LISTING  Where Store = 1 AND (Period LIKE 'Mid-Week%' OR Period LIKE 'Week%' OR Period LIKE 'Year%')
Group BY Period, Sales, store
Order By store, Period, sales

drop table #LISTING 


I have put up a Fiddle with sample data: http://sqlfiddle.com/#!3/1cf793/1

I do have another requirement to accomplish, however I first need to understand why the sum is not being correctly affected by the grouping.

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/03/2013 :  13:29:17  Show Profile  Reply with Quote
What do you want for output?
Go to Top of Page

bmannion3
Starting Member

USA
3 Posts

Posted - 09/03/2013 :  16:11:52  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

What do you want for output?



PERIOD ********************************** SALES ****STORE
Mid-Week February 1 - February 4 - 2009*********904*********1
Mid-Week February 8 - February 11 - 2009*******855**********1
Week February 1 - February 7 - 2009 ***********2188*********1
Week February 8 - February 14 - 2009 ********* 855**********1
Year 2009********************************3043*********1

Edited by - bmannion3 on 09/03/2013 16:16:04
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/03/2013 :  16:51:28  Show Profile  Reply with Quote
So the value taht appears to be off is: Mid-Week February 1 - February 4 - 2009*********904*********1

It looks like a logic issue. If you run the first part of your query:
SELECT
    Amount AS Sales
    , Store_ID AS Store
	,y, m, w, mw, ws, we
FROM KF_Store_Sales_Daily
    CROSS apply (
        SELECT -- aux. expressions for dates
            datepart(yy, [Sales_Date]), -- year
            datepart(m, [Sales_Date]), -- month
            datepart(wk, [Sales_Date]), -- week
            dateadd(d, 4-datepart(w, Sales_Date), Sales_date),  --Mid-Week date
            dateadd(d, 1-datepart(w, Sales_date), Sales_date), -- week start
            dateadd(d, 7-datepart(w, Sales_date), Sales_date) -- week end
    ) d(y, m, w, mw, ws, we)


You get:

Sales	Store	y	m	w	mw		ws		we
280	1	2009	2	6	2009-02-04	2009-02-01	2009-02-07
243	1	2009	2	6	2009-02-04	2009-02-01	2009-02-07
228	1	2009	2	6	2009-02-04	2009-02-01	2009-02-07
153	1	2009	2	6	2009-02-04	2009-02-01	2009-02-07
237	1	2009	2	6	2009-02-04	2009-02-01	2009-02-07
451	1	2009	2	6	2009-02-04	2009-02-01	2009-02-07
596	1	2009	2	6	2009-02-04	2009-02-01	2009-02-07
232	1	2009	2	7	2009-02-11	2009-02-08	2009-02-14
263	1	2009	2	7	2009-02-11	2009-02-08	2009-02-14
360	1	2009	2	7	2009-02-11	2009-02-08	2009-02-14
So, that looks right to me. I'd guess that you need to change the value of Mid-Week or compare mid-week with the Sales-Date to see if the Sales_Date is greater than mid-week.

Edited by - Lamprey on 09/03/2013 16:51:59
Go to Top of Page

bmannion3
Starting Member

USA
3 Posts

Posted - 09/04/2013 :  08:41:38  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey
I'd guess that you need to change the value of Mid-Week or compare mid-week with the Sales-Date to see if the Sales_Date is greater than mid-week.



So far, correct. Yes the Mid-Week Amount is not using the grouping date. I don't know how to affect the logic compare suggested. This is the core of my question.

Edited by - bmannion3 on 09/04/2013 08:45:27
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000