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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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