GROUP BY Week

Transact-SQL
1

Hi ALL,
I have been working on a query but I am not getting the expected results.

Select * from(Arg,
B_Label,
C_Label,
D_Label,
E_Label,
F_Label AS Q1_F_Label,
Date from [dbo].[vw_F2_2] WHERE B_Label in ('XYZ','IJK') and Date >'2015-12-1' ) Q1

JOIN

(SELECT F_Label AS Q2_F_Label, AVG(Arg) AS Mean, STDEV(Arg) AS Std_Dev
FROM [dbo].[vw_F2_2] where B_Label in ('XYZ','IJK') and Date >'2015-12-1' GROUP BY F_Label) Q2

ON Q1.Q1_F_Label = Q2.Q2_F_Label .

For example I have 100 C_Labels

I am getting the AVG(Arg) and STDEV(Arg) for all the 100 C_Labels.

I want to get the AVG (Arg) and STDEV(Arg) for each week , like below

                      Week 1    Mean   
                   
                       Week 2   Mean   

                      Week 3   Mean

but with my query I am getting the average and stdev for all three weeks combined.

Please Help!!
Thank You

2

Looking at your subquery that calculates the mean and stdev, it looks like you need to group by week as well (unless F_Label is indicative of the week.

So your subquery should be something like this:

SELECT
    F_Label AS Q2_F_Label ,
    DATEPART(week, Date) [Week] ,
    AVG(Arg) AS Mean ,
    STDEV(Arg) AS Std_Dev
FROM
    [dbo].[vw_F2_2]
WHERE
    B_Label IN ( 'XYZ', 'IJK' )
    AND Date > '2015-12-1'
GROUP BY
    F_Label ,
    DATEPART(week, Date)

However, even that may not give you exactly what you are looking for because the data returned by the DATEPART function is dependent on DATEFIRST setting See here. There is also the ISO_WEEK that you could use, but it depends on what your definition of a week is.

3

Hi James,
Thanks for the reply.
Since I am using Date > 2015-12-1
So for this date there are 7 weeks until today Nov 29th, Dec6th, dec13th, dec 20th, dec 27th, Jan 3, Jan 10. I want to get the mean for each of these weeks.

Thank You