asked on

Building summary table by day of week

I am trying to build a summary table of sales by store by the day of week (1 = Sun, 2 = Mon, 3 = Tue, etc.). Here is the current code I am using

insert into DailySales
      (StoreId, [DayofWeek], YearNumber, DaySales, DayCount)
DiscountDayCount)
select storeid
      , DATEPART(dw,TransDate)
      , DatePart(yyyy,TransDate)
      , SUM(SoldPrice)
      , COUNT(distinct TransDate)
from TLogDtl
where StoreId in (3,27,44)
      and TransDate between '1/1/2012' and '1/31/2012'
Group by StoreId, TransDate

The problem is that it is still grouping on the TransDate rather than the Day of the Week.

The resulting data is:
DailySalesId StoreId      YearNumber DayOfWeek      DaySales      DayCount
2        3      2012      1        2549.85      1
23        3      2012      1        6861.46      1
44        3      2012      1        6704.01      1
65 3      2012      1        6977.76      1
86        3      2012      1        7095.68      1
3        27      2012      1        3089.21      1
24        27      2012      1        5564.66      1
45        27      2012      1        5534.62      1
66        27      2012      1        7094.34      1
87        27      2012      1        6519.67      1
4        44      2012      1        2881.70      1
25        44      2012      1        8484.22      1
46        44      2012      1        8842.29      1
67        44      2012      1        8340.94      1
88        44      2012      1        8445.31      1
5        3      2012      2        7743.89      1
26        3      2012      2        6538.17      1
47        3      2012      2        6914.54      1
68        3      2012      2        5988.90      1
89        3      2012      2        6619.68      1
6        27      2012      2        5004.15      1
27        27      2012      2        6230.06      1
48        27      2012      2        7468.53      1
69        27      2012      2        5377.88      1
90        27      2012      2        5585.89      1
7        44      2012      2        9443.26      1
28        44      2012      2        9102.63      1
49        44      2012      2        9606.29      1
70        44      2012      2        8501.24      1
91        44      2012      2        7736.54      1
8        3      2012      3        7850.88      1
29        3      2012      3        6211.93      1
50        3      2012      3        6111.43      1
71        3      2012      3        5624.02      1
92        3      2012      3        7778.43      1
9       27      2012      3        6705.31      1
30       27      2012      3        6191.53      1
51       27      2012      3        4636.30      1
72       27      2012      3        5602.14      1
93       27      2012      3        6142.86      1
10      44      2012      3        9004.17      1
31      44      2012      3        8866.96      1
52      44      2012      3        7009.99      1
73      44      2012      3        7142.20      1
94      44      2012      3        9864.73      1

The results I am looking for would be like this:
StoreId      Day Of Wk      Sales      Day Count
3      1        30188.76      5 (this is the number of Sundays counted)
3      2        33805.18      5 (this is the number of Mondays counted)
3      3        33576.69      5
27      1        27802.5      5
27      2        29666.51      5
27      3        29278.14      5
44      1        36994.46      5
44      2        44389.96      5
44      3        41888.05      5

Thanks in advance for the help.

John

TempDBA

I have selected year as well as I thought that is the part you are missing.

SELECT
StoreId, YearNumber, DayOfWeek as [Day Of Wk], Sum(DaySales) as Sales, Count(DayCount) as Day Count
FROM
YOURTable
GROUP BY
StoreId, YearNumber, DayOfWeek

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

j_heck

ASKER

Scott this is exactly what I was looking for. I deon't think I tried doing the two DATEPART commands in the GROUP BY. I was either getting an error or didn't think it could be done like that. But that is right on. Thanks for the help. Now all I have to do is calculate all of the other fields that are involved which will be easier byt they are all done based on a record type of selection. So the TotalSales for example is broken up into Regular Sales, Items on Sale and Items on Deep Discount all in the same row.

Or is there a better way to do this?

Scott Pletcher

>> So the TotalSales for example is broken up into Regular Sales, Items on Sale and Items on Deep Discount all in the same row.

Or is there a better way to do this? <<

Hmm, not sure I have enough info yet; however, in general, you can use regular expressions to combine column amounts and CASE statements to segment data.

For example:

SELECT StoreId
, DATEPART(dw,TransDate)
, DatePart(yyyy,TransDate)
, SUM(SoldPrice) AS TotalSoldPrice
, SUM([Regular Sales] + [Sale Items] + [Deep Discount Items]) AS TotalSales
, SUM(CASE WHEN sale_type = '1' THEN column1 ELSE column2 END) AS TotalSelectiveColumns,
, COUNT(DISTINCT TransDate)
FROM dbo.TLogDtl
WHERE StoreId in (3,27,44)
and TransDate >= '20120101'
and TransDate < '20120201'
GROUP BY StoreId
, DATEPART(dw,TransDate)
, DatePart(yyyy,TransDate)