Select SQL results grouped by weeks

Question

I want to select data from following table group by weeks   Date       Product Name   Sale  ---------- -------------- -----   14-05-11     a             2  14-05-11     b             4   17-05-11     c             3  19-05-11     a             6  24-05-11     a             6  29-05-11     a             6       Let suppose today is 30-05-11  So my result should look like this    Product Name         First Week   Second Week  Third Week  -------------------- ------------ ------------ -------------     a                      12            6           2    b                       0            0           4     c                       0            3           0     Will some body guide me to how to write SQL query to achieve this behavior

User · Answer

Base on  increddibelly answer  I applied to my query as below  I share for whom concerned  My table structure FamilyData Id  nodeTime  totalEnergy  select    sum totalEnergy  as TotalEnergy     DATEPART   week  nodeTime   as weeknr from FamilyData group by DATEPART  week  nodeTime

User · Answer

the provided solutions seem a little complex  this might help   https   msdn microsoft com en-us library ms174420 aspx   select    mystuff     DATEPART   year  MyDateColumn   as yearnr     DATEPART   week  MyDateColumn   as weeknr from mytable group by    etc

User · Answer

I think this should do it    Select  ProductName  WeekNumber  sum sale  from       SELECT      ProductName      DATEDIFF week   2011-05-30   date  AS WeekNumber      sale     FROM table   GROUP BY ProductName  WeekNumber

User · Answer

Declare  DatePeriod datetime Set  DatePeriod    2011-05-30   Select  ProductName          IsNull  1  0  as  Week 1           IsNull  2  0  as  Week 2           IsNull  3  0  as  Week 3           IsNull  4  0  as  Week 4           IsNull  5   0  as  Week 5  From    Select  ProductName          DATEDIFF week  DATEADD MONTH  DATEDIFF MONTH  0   2011-05-30    0    2011-05-30    1 as  Weeks           Sale as  Sale  From dbo WeekReport  -- Only get rows where the date is the same as the DatePeriod -- i e DatePeriod is 30th May 2011 then only the weeks of May will be calculated Where DatePart Month   2011-05-30    DatePart Month   DatePeriod   p  Pivot  Sum Sale  for Weeks in   1   2   3   4   5    as pv   OUTPUT LOOK LIKE THIS  a   0   0   0   0   20 b   0   0   0   0   4 c   0   0   0   0   3

User · Answer

This should do it for you   Declare  DatePeriod datetime  Set  DatePeriod    2011-05-30   Select  ProductName          IsNull  1  0  as  Week 1           IsNull  2  0  as  Week 2           IsNull  3  0  as  Week 3           IsNull  4  0  as  Week 4           IsNull  5   0  as  Week 5   From    Select  ProductName          DATEDIFF week  DATEADD MONTH  DATEDIFF MONTH  0  InputDate   0   InputDate   1 as  Weeks           Sale as  Sale   From dbo YourTable -- Only get rows where the date is the same as the DatePeriod -- i e DatePeriod is 30th May 2011 then only the weeks of May will be calculated Where DatePart Month  InputDate   DatePart Month   DatePeriod   p  Pivot  Sum Sale  for Weeks in   1   2   3   4   5    as pv   It will calculate the week number relative to the month  So instead of week 20 for the year it will be week 2  The  DatePeriod variable is used to fetch only rows relative to the month  in this example only for the month of May   Output using my sample data

[sql-server] Select SQL results grouped by weeks

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005