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
j_heckAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
TempDBACommented:
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
 
j_heckAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.