CODEWITHSUNDEEP
sqlsql-servergroup-byweek-number
daniel aagentah
daniel aagentah

Reputation: 1702

Group by week number including empty weeks and sum other column

I am trying to select from a table in SQL Server, so that it groups my FinalDate column into week numbers and sums the ThisApp column for that week in the next cell. I looked online and I cannot seem to find what I am after.

I was wondering if this was something I can do in T-SQL?

These are currently the rows in my table:

    FinalDate   ThisApp
    ------------------------
    15/04/2016  20459.92
    29/05/2016  7521.89
    30/05/2016  5963.61
    31/05/2016  3293.72
    03/06/2016  27413.20
    04/06/2016  8392.16
    05/06/2016  7789.46
    05/06/2016  11414.73
    10/06/2016  48893.46
    11/06/2016  14685.47
    11/06/2016  7030.03

I would want to replace the FinalDate column with week numbers and sum the This App for each week number.

Also::

I would need it to display continuous week numbers, so I wouldn't want it to skip any weeks, so for example:

    FinalDate   ThisApp
    ------------------------
    01/01/2016  10.00 -- (Would be week 1)
    02/01/2016  10.00 -- (Would be week 1)
    15/01/2016  10.00 -- (Would be week 3)

Would display like:

    FinalDate   ThisApp
    ------------------------
     1          20.00
     2          0.00 --This would show as 0.00 because there was no week 2.
     3          10.00

I understand this a very specific request that's why I was wondering If I could do it in SQL.

Upvotes: 1

Views: 2601

Answers (3)

Stan Shaw
Stan Shaw

Reputation: 3034

HoneyBadger's answer is good, but it will group week 1 values from every year together (so Jan 1st, 1975 will be grouped with Jan 1st, 2016). To avoid that, you will need to add the year to the grouping.

Also, as he pointed out, you will need a separate table if you wish NULL weeks to be returned, as well. However, you should make a calendar table (Google this - they're simple and widely used) and you should NOT just make a table with weeks 1-52 in it.

Lastly, to ensure that you don't get records with 0 returned for all Year/Weeks that precede or succeed your data, you will want to add start and end date parameters. You can either accept them or set them to the first and last FinalDate in Your_Table.

Here is what you should use:

DECLARE @StartDate date = (SELECT MIN(FinalDate) FROM Your_Table),
        @EndDate date = (SELECT MAX(FinalDate) FROM Your_Table)

SELECT   YEAR(C.BaseDate) AS [Year], 
         DATEPART(WEEK, C.BaseDate) AS [Week],
         ISNULL(SUM(YT.ThisApp), 0) AS [This App Total]
FROM     Calendar C
LEFT OUTER JOIN Your_Table YT
    ON C.BaseDate = CAST(YT.FinalDate AS DATE)
WHERE C.BaseDate BETWEEN @StartDate AND @EndDate
GROUP BY YEAR(C.BaseDate), 
         DATEPART(WEEK, C.BaseDate)

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15150

SELECT   DATEPART(WEEK, FinalDate) FinalDate
,        SUM(ThisApp) ThisApp
FROM     Your_Table
GROUP BY DATEPART(WEEK, FinalDate)

In order to get 0 for weeks not existent in the dataset you have to create a table with weeknumbers (1-52) and right join to it. In that case you'd get something like:

SELECT        wk.Number
,             ISNULL(SUM(ThisApp), 0)
FROM          Your_Table T
RIGHT JOIN    WeekNumbers wk
           ON wk.Number = DATEPART(WEEK, T.FinalDate)
GROUP BY      wk.Number

Upvotes: 1

sapi
sapi

Reputation: 244

 DECLARE @tmp table(dat datetime,
                    val float)

 insert into @tmp 
 values  ('15/04/2016' , 20459.92),
         ('29/05/2016',  7521.89),
         ('30/05/2016',  5963.61),
         ('31/05/2016',  3293.72),
         ('03/06/2016',  27413.20),
         ('04/06/2016',  8392.16),
         ('05/06/2016',  7789.46),
         ('05/06/2016',  11414.73),
         ('10/06/2016',  48893.46),
         ('11/06/2016',  14685.47),
         ('11/06/2016',  7030.03)

 SELECT  Weeknumb,
         ISNULL(sumvals,0) as weekval
 FROM 
  (SELECT DATEPART(ISOWK,DATEADD(wk,t2.number,'2016')) as Weeknumb
   FROM master..spt_values t2
   WHERE t2.type = 'P'
   AND t2.number <= 255
   AND YEAR(DATEADD(wk,t2.number,'2016'))=2016)allWeeks
   LEFT JOIN
  (SELECT  sum(val) as  sumvals,
           datepart(ISOWK,dat) as  weeks
   FROM @tmp
   GROUP BY datepart(ISOWK,dat) ) actualData
   ON weeks = Weeknumb
  ORDER BY Weeknumb asc

there you go. All weeks of 2016 with your values summed

Upvotes: 1

Related Questions