Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

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
0
j_heck
Asked:
j_heck
  • 2
1 Solution
 
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
 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now