Grouping Rollup Sum not Working Correctly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping Rollup Sum not Working Correctly

Author

Topic

bmannion3
Starting Member

3 Posts

Posted - 2013-09-03 : 13:15:37

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: [url]http://sqlfiddle.com/#!3/1cf793/1[/url]

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-03 : 13:29:17

What do you want for output?

bmannion3
Starting Member

3 Posts

Posted - 2013-09-03 : 16:11:52

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-03 : 16:51:28

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.

bmannion3
Starting Member

3 Posts

Posted - 2013-09-04 : 08:41:38

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources