[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Group By Week

MikeB

3/24/2007 1:08:00 PM

Hello All, I am new to sql and have a questions. I have the following
table and need to group it by Week. Is there anyway to do this in the SQL
and if so can someone please help me out?

Table 1
id PK int
userid FK int
CategoryID FK to table 2 int
qty int
date smalldatetime

Example data

1 1 1 2 03/23/2007
2 1 2 2 03/24/2007
3 1 1 2 03/28/2007
4 1 2 2 03/28/2007

Table 2
id PK int
categoryid FK int
Item
FatGrams int
Calories int

Example Data
1 1 pop/soda 5 100
2 2 candy 10 200

Now for data, what I need is the total fatgrams and calories grouped by week

SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories), week ?
FROM table1 t1, table2 t2
WHERE t2.categoryid = t1.categoryid
AND t1.userid = 1
GROUP BY week ?
12 Answers

Tom Moreau

3/24/2007 1:26:00 PM

0

Try:

SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories) as cals,
datepart (wk, [date]) as week
FROM
table1 t1
JOIN
table2 t2 ON t2.categoryid = t1.categoryid
WHERE
t1.userid = 1
GROUP BY
datepart (wk, [date])

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"MikeB" <m@nospam.com> wrote in message
news:%233zxyVhbHHA.2448@TK2MSFTNGP02.phx.gbl...
Hello All, I am new to sql and have a questions. I have the following
table and need to group it by Week. Is there anyway to do this in the SQL
and if so can someone please help me out?

Table 1
id PK int
userid FK int
CategoryID FK to table 2 int
qty int
date smalldatetime

Example data

1 1 1 2 03/23/2007
2 1 2 2 03/24/2007
3 1 1 2 03/28/2007
4 1 2 2 03/28/2007

Table 2
id PK int
categoryid FK int
Item
FatGrams int
Calories int

Example Data
1 1 pop/soda 5 100
2 2 candy 10 200

Now for data, what I need is the total fatgrams and calories grouped by week

SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories), week ?
FROM table1 t1, table2 t2
WHERE t2.categoryid = t1.categoryid
AND t1.userid = 1
GROUP BY week ?

MikeB

3/24/2007 1:30:00 PM

0

Great, Thank You!

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:ukGUrfhbHHA.3420@TK2MSFTNGP05.phx.gbl...
> Try:
>
> SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories) as cals,
> datepart (wk, [date]) as week
> FROM
> table1 t1
> JOIN
> table2 t2 ON t2.categoryid = t1.categoryid
> WHERE
> t1.userid = 1
> GROUP BY
> datepart (wk, [date])
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "MikeB" <m@nospam.com> wrote in message
> news:%233zxyVhbHHA.2448@TK2MSFTNGP02.phx.gbl...
> Hello All, I am new to sql and have a questions. I have the following
> table and need to group it by Week. Is there anyway to do this in the SQL
> and if so can someone please help me out?
>
> Table 1
> id PK int
> userid FK int
> CategoryID FK to table 2 int
> qty int
> date smalldatetime
>
> Example data
>
> 1 1 1 2 03/23/2007
> 2 1 2 2 03/24/2007
> 3 1 1 2 03/28/2007
> 4 1 2 2 03/28/2007
>
> Table 2
> id PK int
> categoryid FK int
> Item
> FatGrams int
> Calories int
>
> Example Data
> 1 1 pop/soda 5 100
> 2 2 candy 10 200
>
> Now for data, what I need is the total fatgrams and calories grouped by
> week
>
> SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories), week ?
> FROM table1 t1, table2 t2
> WHERE t2.categoryid = t1.categoryid
> AND t1.userid = 1
> GROUP BY week ?
>
>
>


Tibor Karaszi

3/24/2007 1:55:00 PM

0

.... and don't use datepart to count week numbers if you go by the "international" way of counting
week numbers. SQL Server believes that 1 Jan is always week 1, where many cultures has week 1 as the
first week with 4 days in it. Check out the ISOWeek function (found in Books Online), and also make
sure you have a proper SET DATEFIRST setting.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"MikeB" <m@nospam.com> wrote in message news:ux$2aihbHHA.4808@TK2MSFTNGP04.phx.gbl...
> Great, Thank You!
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:ukGUrfhbHHA.3420@TK2MSFTNGP05.phx.gbl...
>> Try:
>>
>> SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories) as cals,
>> datepart (wk, [date]) as week
>> FROM
>> table1 t1
>> JOIN
>> table2 t2 ON t2.categoryid = t1.categoryid
>> WHERE
>> t1.userid = 1
>> GROUP BY
>> datepart (wk, [date])
>>
>> --
>> Tom
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "MikeB" <m@nospam.com> wrote in message
>> news:%233zxyVhbHHA.2448@TK2MSFTNGP02.phx.gbl...
>> Hello All, I am new to sql and have a questions. I have the following
>> table and need to group it by Week. Is there anyway to do this in the SQL
>> and if so can someone please help me out?
>>
>> Table 1
>> id PK int
>> userid FK int
>> CategoryID FK to table 2 int
>> qty int
>> date smalldatetime
>>
>> Example data
>>
>> 1 1 1 2 03/23/2007
>> 2 1 2 2 03/24/2007
>> 3 1 1 2 03/28/2007
>> 4 1 2 2 03/28/2007
>>
>> Table 2
>> id PK int
>> categoryid FK int
>> Item
>> FatGrams int
>> Calories int
>>
>> Example Data
>> 1 1 pop/soda 5 100
>> 2 2 candy 10 200
>>
>> Now for data, what I need is the total fatgrams and calories grouped by week
>>
>> SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories), week ?
>> FROM table1 t1, table2 t2
>> WHERE t2.categoryid = t1.categoryid
>> AND t1.userid = 1
>> GROUP BY week ?
>>
>>
>>
>
>

MikeB

3/24/2007 4:35:00 PM

0

Actually, after looking at this a little closer, I don't think it will work
for me since this can go over a year so I would cause it to restart. Is
there sometype of datediff funcion where I can pass it the date and todays
date and get the difference in weeks and then group that way?


"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:ukGUrfhbHHA.3420@TK2MSFTNGP05.phx.gbl...
> Try:
>
> SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories) as cals,
> datepart (wk, [date]) as week
> FROM
> table1 t1
> JOIN
> table2 t2 ON t2.categoryid = t1.categoryid
> WHERE
> t1.userid = 1
> GROUP BY
> datepart (wk, [date])
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "MikeB" <m@nospam.com> wrote in message
> news:%233zxyVhbHHA.2448@TK2MSFTNGP02.phx.gbl...
> Hello All, I am new to sql and have a questions. I have the following
> table and need to group it by Week. Is there anyway to do this in the SQL
> and if so can someone please help me out?
>
> Table 1
> id PK int
> userid FK int
> CategoryID FK to table 2 int
> qty int
> date smalldatetime
>
> Example data
>
> 1 1 1 2 03/23/2007
> 2 1 2 2 03/24/2007
> 3 1 1 2 03/28/2007
> 4 1 2 2 03/28/2007
>
> Table 2
> id PK int
> categoryid FK int
> Item
> FatGrams int
> Calories int
>
> Example Data
> 1 1 pop/soda 5 100
> 2 2 candy 10 200
>
> Now for data, what I need is the total fatgrams and calories grouped by
> week
>
> SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories), week ?
> FROM table1 t1, table2 t2
> WHERE t2.categoryid = t1.categoryid
> AND t1.userid = 1
> GROUP BY week ?
>
>
>


--CELKO--

3/24/2007 7:35:00 PM

0

>> Actually, after looking at this a little closer, I don't think it will work for me since this can go over a year so I would cause it to restart. <<

You can build table of date ranges that give you a week number of your
own invention then use a BETWEEN predicate with your data. A century
would only be about 5200 rows.

MikeB

3/24/2007 8:43:00 PM

0

I am not sure I understand you. Can you give me an example?

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1174764877.437341.275410@d57g2000hsg.googlegroups.com...
>>> Actually, after looking at this a little closer, I don't think it will
>>> work for me since this can go over a year so I would cause it to
>>> restart. <<
>
> You can build table of date ranges that give you a week number of your
> own invention then use a BETWEEN predicate with your data. A century
> would only be about 5200 rows.
>


Tom Moreau

3/24/2007 9:05:00 PM

0

Try:

SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories) as cals,
datepart (wk, [date]) as week
FROM
table1 t1
JOIN
table2 t2 ON t2.categoryid = t1.categoryid
WHERE
t1.userid = 1
AND t1.[date] >= '20070101' and t1.[date] < getdate()
GROUP BY
datepart (wk, [date])


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"MikeB" <m@nospam.com> wrote in message
news:uG4laJjbHHA.3408@TK2MSFTNGP03.phx.gbl...
Actually, after looking at this a little closer, I don't think it will work
for me since this can go over a year so I would cause it to restart. Is
there sometype of datediff funcion where I can pass it the date and todays
date and get the difference in weeks and then group that way?


"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:ukGUrfhbHHA.3420@TK2MSFTNGP05.phx.gbl...
> Try:
>
> SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories) as cals,
> datepart (wk, [date]) as week
> FROM
> table1 t1
> JOIN
> table2 t2 ON t2.categoryid = t1.categoryid
> WHERE
> t1.userid = 1
> GROUP BY
> datepart (wk, [date])
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "MikeB" <m@nospam.com> wrote in message
> news:%233zxyVhbHHA.2448@TK2MSFTNGP02.phx.gbl...
> Hello All, I am new to sql and have a questions. I have the following
> table and need to group it by Week. Is there anyway to do this in the SQL
> and if so can someone please help me out?
>
> Table 1
> id PK int
> userid FK int
> CategoryID FK to table 2 int
> qty int
> date smalldatetime
>
> Example data
>
> 1 1 1 2 03/23/2007
> 2 1 2 2 03/24/2007
> 3 1 1 2 03/28/2007
> 4 1 2 2 03/28/2007
>
> Table 2
> id PK int
> categoryid FK int
> Item
> FatGrams int
> Calories int
>
> Example Data
> 1 1 pop/soda 5 100
> 2 2 candy 10 200
>
> Now for data, what I need is the total fatgrams and calories grouped by
> week
>
> SELECT sum(t1qty * t2.fatgrams) as fat, sum(t1.qty * t2.calories), week ?
> FROM table1 t1, table2 t2
> WHERE t2.categoryid = t1.categoryid
> AND t1.userid = 1
> GROUP BY week ?
>
>
>

Hugo Kornelis

3/25/2007 12:52:00 AM

0

On Sat, 24 Mar 2007 12:34:35 -0400, MikeB wrote:

>Actually, after looking at this a little closer, I don't think it will work
>for me since this can go over a year so I would cause it to restart. Is
>there sometype of datediff funcion where I can pass it the date and todays
>date and get the difference in weeks and then group that way?

SELECT SUM(t1.qty * t2.fatgrams) AS fat,
SUM(t1.qty * t2.calories) AS cals,
DATEADD(day,
DATEDIFF(day, '20060102', getdate()) / 7 * 7,
'20060102')
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t2.categoryid = t1.categoryid
WHERE t1.userid = 1
GROUP BY DATEDIFF(day, '20060102', getdate()) / 7;

With the base date above (jan 2, 2006) you'll get weeks starting on
ondays. Change the date to get another first day of the week.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

--CELKO--

3/25/2007 2:18:00 AM

0

>> I am not sure I understand you. Can you give me an example? <<

This is a standard trick; here is the skeleton:

CREATE TABLE ReportPeriods
(period_name VARCHAR (20) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL
CHECK(start_date < end_date),
..);

SELECT R.period_name, SUM(D.stuff)
FROM ReportPeriods AS R,
Data AS D
WHERE D.event_date BETWEEN R.start_date AND end_date
GROUP BY R.period_name;

Instead of proprietary functions, use tables from your reporting
periods. The nice part is that you can have overlapping periods --
"Mom's fruit cake week" can fall on some of the days of Ramadan, etc.

MikeB

3/25/2007 3:10:00 PM

0

Thanks again

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1174789063.955692.166260@e65g2000hsc.googlegroups.com...
>>> I am not sure I understand you. Can you give me an example? <<
>
> This is a standard trick; here is the skeleton:
>
> CREATE TABLE ReportPeriods
> (period_name VARCHAR (20) NOT NULL,
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL
> CHECK(start_date < end_date),
> ..);
>
> SELECT R.period_name, SUM(D.stuff)
> FROM ReportPeriods AS R,
> Data AS D
> WHERE D.event_date BETWEEN R.start_date AND end_date
> GROUP BY R.period_name;
>
> Instead of proprietary functions, use tables from your reporting
> periods. The nice part is that you can have overlapping periods --
> "Mom's fruit cake week" can fall on some of the days of Ramadan, etc.
>