Q: Get sum of amount records as each row is month and column is week using sql? I have 1000 records in table TBL_yearlyReport with 2 columns:


*

*Date_Val  (eg '2016-12-25 23:48:40.360')

*Amount (eg 100, 1000 ,100 etc)
Sample data in TBL_yearlyReport 
Date_Val    | Amount 
---------------------
2016-12-25  | 20000
2016-02-21  |  2000

I want to see a report on the basis of year, and I want to pass in a year as 2012 etc
Report must have a row for each month and columns returned will be 4 that are week1, week2, week3, week4
Each week's column value is the sum of current week amount
Here is what I have done so far:
DECLARE @LoopCounter INT = 1
DECLARE @MONTH INT = 1

BEGIN
     WHILE(@LoopCounter <= 100)
     BEGIN
         SET @LoopCounter  = @LoopCounter  + 1                 

         IF @MONTH = 1 
         BEGIN
             SELECT SUM(convert(INT, amount)) as JAN,  
                    @MONTH AS month  
             FROM TBL_yearlyReport 
             WHERE YEAR(Date_Val) = 2016 AND @MONTH = @MONTH 
         END

         IF @MONTH = 2 
         BEGIN
             SELECT SUM(convert(INT, amount)) as FEB,  
                    @MONTH AS month  
             FROM TBL_yearlyReport 
             WHERE YEAR(Date_Val) = 2016 AND @MONTH = @MONTH 
         END 

                 if @MONTH = 3 
                       begin
          SELECT sum(convert(int ,amount)) as FEB ,  @MONTH as month  FROM TBL_yearlyReport 
                    WHERE  YEAR(Date_Val) = 2016 and  @MONTH = @MONTH 
                end

             /////////so on continue to

                     if @MONTH = 12
                       begin
          SELECT sum(convert(int ,amount)) as FEB ,  @MONTH as month  FROM TBL_yearlyReport 
                    WHERE  YEAR(Date_Val) = 2016 and  @MONTH = @MONTH                     
                end    

                if @MONTH = 12
                begin
                   break
                end  
                 SET @MONTH   = @MONTH  + 1  
                end    END

Now the problem is how to get all ids that are between month so that use BETWEEN to sum amount?
How to get amount sum on weekly basis of each month like
     week1   |    week2   |  week3   | week4

      jan  
      feb
      march 
      june

etc
I want to do that to to get sum of amount on a weekly basis if I have their ids
SELECT SUM(convert(INT, amount)) ah  
FROM TBL_yearlyReport 
WHERE YEAR(Date_Val) = 2016  
  AND id between 1 and 100     

Output should be:
SELECT @WEEK1, @WEEK2, @WEEK3, @WEEK4 
FROM TBL_yearlyReport 
WHERE Date_Val BETWEEN @startWEEKDATE AND @EndWEEKDATE


A: I suppose weeks numeration needed like this:

We can take week number like 11, divide it by 4 and get 2,75. Then we take 75 and see that it equals to 3rd week. In that way we convert all the week nums.
Then you can use cte + pivot:
;WITH cte AS (
SELECT MONTH(Date_Val) as [MonthNum],
       DATENAME(month,Date_Val) as [Month],
       DATEPART(week,Date_Val) as [ActualWeekNum],
       FLOOR((CAST(DATEPART(week,Date_Val) as decimal(5,2))/4- FLOOR(CAST(DATEPART(week,Date_Val) as decimal(5,2))/4))*100) as [Week],
       Amount as [Amount]
FROM  TBL_yearlyReport
WHERE  YEAR(Date_Val) = 2012
)

SELECT [Month],Week1,Week2,Week3,Week4
FROM
(
SELECT [MonthNum],
       [Month],
       CASE WHEN [Week] = 25 THEN 'Week1' 
            WHEN [Week] = 50 THEN 'Week2'
            WHEN [Week] = 75 THEN 'Week3' 
            WHEN [Week] = 0  THEN 'Week4'
            ELSE NULL
            END as [Week],
       Amount 
FROM cte
) d
pivot
(
SUM(Amount) for [WEEK] in (Week1,Week2,Week3,Week4)
) piv
ORDER BY [MonthNum];

Results are:
    Month                          Week1       Week2       Week3       Week4
------------------------------ ----------- ----------- ----------- -----------
January                        1995        4798        5280        4500
February                       6102        4283        4039        4519
March                          7445        2616        5701        5977
April                          4991        5624        4823        4901
May                            3826        5858        1970        3703
June                           3461        3950        4208        1707
July                           2970        2469        5217        2861
August                         2536        2535        4887        2210
September                      2352        2721        4232        3165
October                        2876        3044        4702        7160
November                       3183        3158        4509        5507
December                       3666        3509        3276        3257

