Week no query

Prev: PRB: Single Query to build database and schema.
Next: How to use CDO to send mail with attachment

From: Henry on 12 Feb 2005 03:05

---

Hello

I'm looking for a query which can select and group by week no

I have query for a SQLBase which gives me this but I cannot translate it to
MSSQL?

select Some_Date, (@weekbeg(Some_Date) - @weekbeg(@date(@yearno(Some_Date),
1, 1))) / 7 + 1 as Week_Number
from Some_Table;


Here's an example where I use it.


select weekno = (@WEEKBEG(calltime) -
@WEEKBEG(@DATE(@year(@WEEKBEG(calltime) + 3) +1900, 1, 4))) / 7 + 1 ,
sum_TotalCalls = sum(TotalCalls),
from CDR_tickets
where
(@datetochar(CALLTIME, 'yy/mm/dd') between
'04/12/14' and '04/12/14') and
(@timevalue(CALLTIME) between '07:45' and
'16:00')
group by 1;

can anyboy help me translating this to MSSQL?

regards
Henry

From: Nadim Wakim on 12 Feb 2005 03:34

---

use can use
DATEPART ( datepart , date )

EXAMPLE:

Select DATEPART (dw , t.DateCall), sum(t.Call)

from Calls t

group by DATEPART (dw , t.DateCall)





"Henry" <nospam(a)thanks.com> wrote in message
news:ecbGDnNEFHA.1124(a)TK2MSFTNGP14.phx.gbl...
> Hello
>
> I'm looking for a query which can select and group by week no
>
> I have query for a SQLBase which gives me this but I cannot translate it
> to
> MSSQL?
>
> select Some_Date, (@weekbeg(Some_Date) -
> @weekbeg(@date(@yearno(Some_Date),
> 1, 1))) / 7 + 1 as Week_Number
> from Some_Table;
>
>
> Here's an example where I use it.
>
>
> select weekno = (@WEEKBEG(calltime) -
> @WEEKBEG(@DATE(@year(@WEEKBEG(calltime) + 3) +1900, 1, 4))) / 7 + 1 ,
> sum_TotalCalls = sum(TotalCalls),
> from CDR_tickets
> where
> (@datetochar(CALLTIME, 'yy/mm/dd') between
> '04/12/14' and '04/12/14') and
> (@timevalue(CALLTIME) between '07:45' and
> '16:00')
> group by 1;
>
> can anyboy help me translating this to MSSQL?
>
> regards
> Henry
>
>

From: Simon Shearn on 12 Feb 2005 03:33

---

"Henry" <nospam(a)thanks.com> wrote in message
news:ecbGDnNEFHA.1124(a)TK2MSFTNGP14.phx.gbl...
> Hello
>
> I'm looking for a query which can select and group by week no
>
> I have query for a SQLBase which gives me this but I cannot translate it
to
> MSSQL?
>
> select Some_Date, (@weekbeg(Some_Date) -
@weekbeg(@date(@yearno(Some_Date),
> 1, 1))) / 7 + 1 as Week_Number
> from Some_Table;
>
>
> Here's an example where I use it.
>
>
> select weekno = (@WEEKBEG(calltime) -
> @WEEKBEG(@DATE(@year(@WEEKBEG(calltime) + 3) +1900, 1, 4))) / 7 + 1 ,
> sum_TotalCalls = sum(TotalCalls),
> from CDR_tickets
> where
> (@datetochar(CALLTIME, 'yy/mm/dd') between
> '04/12/14' and '04/12/14') and
> (@timevalue(CALLTIME) between '07:45' and
> '16:00')
> group by 1;
>
> can anyboy help me translating this to MSSQL?
>
> regards
> Henry
>
Hello -

Have you looked at the T-SQL DATEPART() function, eg. DATEPART(wk,
Some_Date). If SQL Server's definition of weeknumber doesn't match yours,
you could write a user-defined function to turn a datetime into a
weeknumber.

Regards,

Simon

From: Henry on 12 Feb 2005 04:29

---

> DATEPART ( datepart , date )
>
> EXAMPLE:
>
> Select DATEPART (dw , t.DateCall), sum(t.Call)
>
> from Calls t
>
> group by DATEPART (dw , t.DateCall)


Thanks Guys, I was already using datepart, for other purposes, I completly
overlooked "week" :(

regards
Henry

From: Tibor Karaszi on 12 Feb 2005 04:45

---

Just make sure DATEPART numbers weeks the way you want it. It doesn't according to how we do it in
Scandinavia or according to the ISO standard... Also, DATEPART is dependent of SET DATEFIRST.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/


"Henry" <nospam(a)thanks.com> wrote in message news:utryJWOEFHA.2804(a)TK2MSFTNGP14.phx.gbl...
>> DATEPART ( datepart , date )
>>
>> EXAMPLE:
>>
>> Select DATEPART (dw , t.DateCall), sum(t.Call)
>>
>> from Calls t
>>
>> group by DATEPART (dw , t.DateCall)
>
>
> Thanks Guys, I was already using datepart, for other purposes, I completly
> overlooked "week" :(
>
> regards
> Henry
>
>

 |  Next  |  Last
Pages: 1 2 3
Prev: PRB: Single Query to build database and schema.
Next: How to use CDO to send mail with attachment