group data by weeks

I have data entered into a table using a datetime field. How can I group the data one week at a time and show mulitple weeks at a time?Look at the datepart function.

http://msdn2.microsoft.com/en-us/library/ms174420.aspx|||

What do you mean by showing multiple weeks at a time?

Lets assume you have columns Cost as currency and BoughtOn as date

SELECT COUNT(Cost) as Items, SUM(Cost) As TotalPrice, DATEPART(wk, BoughtOn) As Week FROM yourTable GROUP BY DATEPART(wk, BoughtOn)

Is that what you need?

--
SvenC

|||

I'm trying for something like this:

Name

Week End Date

Product1

Product2

Porduct3

Rep1

9/9/2006

1130

331

Rep1

9/16/2006

130

3021

1452

Rep1

9/23/2006

1351

3513

1542

What you have above is very close SvenC.

|||

Can you show your table definition or definitions from which the above result set should come?

--
SvenC

|||

Here is the sql I am using now:

SELECT dbo.sales_rep.sales_rep_first_name + ' ' + dbo.sales_rep.sales_rep_last_name AS Name, dbo.sale.sale_dts, dbo.sale.sale_type,

SUM(dbo.sale.total_pt_of_sale_amt) AS POS, DATEPART(wk, sale_dts) As Week

FROM dbo.sale INNER JOIN

dbo.sales_rep ON dbo.sale.sales_rep_user_id = dbo.sales_rep.sales_rep_user_id

WHERE (dbo.sale.sale_dts BETWEEN @.start AND @.end) and sale_type in ('qqq', 'ttt', 'fff', '11111)

and dbo.sales_rep.sales_rep_user_id in ('id123','id1234','id2151','id5214')

GROUP BY DATEPART(wk, sale_dts), dbo.sale.sale_dts, dbo.sales_rep.sales_rep_last_name, dbo.sales_rep.sales_rep_first_name, dbo.sale.sale_type, dbo.sale.total_pt_of_sale_amt

The sale table is

sale_id int
sales_rep_user_id varchar(7)
sale_dts datetime
sale_type varchar(10)
total_pt_of_sale_amt

|||And what results do you get with that?

Do you need to concatenate the week datepart with the year datepart to get uniqueness for a given week?|||

I get data like this:

Rep Name

Date

Type

POS

Week

|||So you don't get any data?|||

Sorry, didn't know you wanted to see the data:

Rep Name

Date

Type

POS

Week

RepName1

9/5/2006

Product1

1731

36

RepName1

9/5/2006

Product1

216

36

RepName1

9/5/2006

Prodcut2

240

36

RepName1

9/5/2006

Product1

1960

36

RepName1

9/5/2006

Prodcut2

15000

36

RepName2

9/5/2006

Product1

120

36

RepName2

9/5/2006

Prodcut2

600

36

RepName2

9/6/2006

Product1

800

36

RepName2

9/6/2006

Product1

1680

36

RepName2

9/6/2006

Product1

168

36

RepName2

9/6/2006

Product1

348

36

|||That helps.

Now, with your data, where do you want to go? What should the data look like?|||

I'd like to had the date listed as the Satuday of the week. For example: 9/9/2006.

Something like this:

Rep Name	Date	Product1	Product2	Product3
Rep1	9/9/2006	1302	1731	3020
Rep1	10/14/2006	680	311	3151
Rep1	10/21/2006	21001	15000	36
Rep2	10/21/2006	0	3033	11220
Rep3	9/9/2006	1	1002	12125
Rep3	9/16/2006	3320	2111	3601
Rep3	9/23/2006	12102	2210	1101
Rep3	10/14/2006	2151	3264	36

Where there is one record for each rep per week.

|||So you want to pivot your data in a query? That is to say you want dynamic columns? So you'll have as many product columns as the max(type) per sales rep?

Oh boy...|||Pivot the data, yes. However I'm only looking for four (4) sale types and five (5) sales reps.|||Well, try this.

select repname, weekend, sum(Product1Col), sum(Product2Col), sum(Product3Col), sum(Product4Col)
from (
select [repname], 'weekend' = case datepart("dw",[YourDateField])
when 1 then dateadd("dd",6,[YourDateField])
when 2 then dateadd("dd",5,[YourDateField])
when 3 then dateadd("dd",4,[YourDateField])
when 4 then dateadd("dd",3,[YourDateField])
when 5 then dateadd("dd",2,[YourDateField])
when 6 then dateadd("dd",1,[YourDateField])
when 7 then [YourDateField]
end,
'Product1Col' = case [type]
when 'Product1' then [POS]
else 0
end,
'Product2Col' = case [type]
when 'Product2' then [POS]
else 0
end,
'Product3Col' = case [type]
when 'Product3' then [POS]
else 0
end,
'Product4Col' = case [type]
when 'Product4' then [POS]
else 0
end
from table
where [repname] in ('Rep1','Rep2','Rep3','Rep4','Rep5')
) GROUP BY [repname], [weekend]