Solved

# Building summary table by day of week

Posted on 2012-09-04
415 Views
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
0
Question by:j_heck

LVL 25

Expert Comment

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
0

LVL 68

Accepted Solution

INSERT INTO dbo.DailySales
( StoreId, [DayofWeek], YearNumber, DaySales, DayCount )
SELECT StoreId
, DATEPART(dw,TransDate)
, DatePart(yyyy,TransDate)
, SUM(SoldPrice)
, 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)
0

Author Closing Comment

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?
0

LVL 68

Expert Comment

>> 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)
0

## Featured Post

### Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…