Get the sum of the amount entries since each row is a month and the column is a week using sql?

Question

I have 1000 entries in a table TBL_yearlyReportwith two columns:

• Date_Val (e.g. '2016-12-25 23: 48: 40.360')

• Amount (e.g. 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 results of the year, and I want to go through a year in 2012.

The report should contain a row for each month, and the returned columns will be 4, which are weeks1, week2, week3, week4

The column value each week is the sum of the current week.

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 identifiers between the months to use BETWEEN to sum the amount?

How to get the amount on a weekly basis for each month, for example

     week1   |    week2   |  week3   | week4

      jan  
      feb
      march 
      june

etc.

, ,

SELECT SUM(convert(INT, amount)) ah  
FROM TBL_yearlyReport 
WHERE YEAR(Date_Val) = 2016  
  AND id between 1 and 100     

:

SELECT @WEEK1, @WEEK2, @WEEK3, @WEEK4 
FROM TBL_yearlyReport 
WHERE Date_Val BETWEEN @startWEEKDATE AND @EndWEEKDATE

Answer 1

, :

, 11, 4 2,75. 75 , 3- . , . 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];

:

    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