CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Sherwin
Sherwin

Reputation: 377

How to show total weekly count in one table

I'm trying to achieve a report that will show all daily count as well weekly count in the same table. I've tried different techniques that I know but it seems that I wasn't able to get what I want.

I'm trying to show a similar table below.

+-----------+-----+-------+--------+--+--+--+
|  August   |     | Count |        |  |  |  |
+-----------+-----+-------+--------+--+--+--+
| 8/1/2013  | Thu | 1,967 |        |  |  |  |
| 8/2/2013  | Fri | 1,871 |        |  |  |  |
| 8/3/2013  | Sat | 1,950 |        |  |  |  |
| 8/4/2013  | Sun | 2,013 | 7801   |  |  |  |
| 8/5/2013  | Mon | 2,039 |        |  |  |  |
| 8/6/2013  | Tue | 1,871 |        |  |  |  |
| 8/7/2013  | Wed | 1,611 |        |  |  |  |
| 8/8/2013  | Thu | 1,680 |        |  |  |  |
| 8/9/2013  | Fri | 1,687 |        |  |  |  |
| 8/10/2013 | Sat | 1,649 |        |  |  |  |
| 8/11/2013 | Sun | 1,561 | 12,098 |  |  |  |
+-----------+-----+-------+--------+--+--+--+

Please let me if there's an existing code or technique that I could to achieve something like this. Thanks.

Sherwin

Upvotes: 0

Views: 257

Answers (4)

Phill C
Phill C

Reputation: 606

Try something like this but make sure to check which WEEKDAY is Sunday on your server since this can be modified. 

select T1.August, T1.[Count], 
case DATEPART(WEEKDAY, O.Order_Date)
    WHEN 1 THEN (SELECT CONVERT(varchar(10), SUM(T2.[Count]) FROM TableName T2 WHERE T2.August BETWEEN DATEADD(d,-7,T1.August) and T1.August))
    ELSE ''
end as Weekly_Count
FROM TablleName T1
ORDER BY T.August

Upvotes: 2

JsonStatham
JsonStatham

Reputation: 10364

Would something along these lines work, the syntax may not be 100%

select
[Date],
DOW,
[Count],
Case When DOW = 'Mon' then 1 else 2 end as Partition_DOW,
SUM([Count]) OVER (PARTITION BY (Case When DOW = 'Mon' then 1 else 0 end) ORDER BY [Date]) AS 'Monthly_Total'
from My_table
where Month([Date]) = Month(Date()) AND Year([Date]) = Year(Date())

Upvotes: 0

Dalen
Dalen

Reputation: 9006

If you don't mind having those subtotal on a new row instead of on a new column, GROUP BY WITH ROLLUP could be the solution for you:

SET LANGUAGE GERMAN is used for setting monday as first day of the week and allowing us to sum up until sunday

SET LANGUAGE GERMAN;

WITH first AS
(
  SELECT
    date,
    day,
    DATEPART(dw, date) AS dayweek,
    DATEPART(wk, date) AS week,
    count
  FROM example
)

SELECT
  CASE WHEN (GROUPING(dayweek) = 1) THEN 'TOT' ELSE CAST(MAX(date) AS VARCHAR(20)) END AS date,
  CASE WHEN (GROUPING(dayweek) = 1) THEN 'TOT' ELSE MAX(day) END AS day,
  SUM(count) AS count
FROM first
GROUP BY week,dayweek WITH ROLLUP

see the complete example on sqlfiddle

Upvotes: 2

Tarp
Tarp

Reputation: 1

If you use stored procedures, then make a temp table and loop it through with a cursor, and make the sums. You could also do something like this:

SELECT CreatedDate, Amount, CASE WHEN DATENAME(dw , CreatedDate) = 'Sunday' THEN (SELECT SUM(Amount) FROM AmountTable at2 WHERE CreatedDate <= at1.CreatedDate AND CreatedDate > DATEADD(Day, -7, at1.CreatedDate)) ELSE 0 END AS 'WeekTotal'
from AmountTable at1

Upvotes: 0

Related Questions