weekly calculation per month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

weekly calculation per month

Author

Topic

thuya
Starting Member

7 Posts

Posted - 2009-07-01 : 23:13:07

i want to know weekly calc per month.

eg. for jan month

1st Week-------------how many day
2nd Week-------------how many day
3rd Week-------------how many day
4th Week-------------how many day

pls help me!
urgent

thanks

thuya

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 23:18:59

what is your definition of week in a month ?

For example the Month of January 2009, 1st week is from which date to which date ?

KH
[spoiler]Time is always against us[/spoiler]

thuya
Starting Member

7 Posts

Posted - 2009-07-01 : 23:52:08

yeah!
i just want to COUNT for (How many Day for (January , 1st week), for (2nd week) for (3rd week) for(4th week)

thanks for your reply
regards
thuya

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 23:56:07

You have not answer my question at all.

You have to gives us your definition of WEEK of a MONTH.

Your week starts from Sat, Sun, Mon, Tue ? ? ? How many days is it for Week 1 January 2009 ?

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 01:01:44

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-07-02 : 01:14:45

Hi Try this too


drop table #Calendar

CREATE TABLE #Calendar(Calendar_Date datetime, Year_Num smallint,
Day_Name varchar(15),Month_Num smallint,Week_Num smallint)

DECLARE @dtDate datetime
SET @dtDate = '1/1/2009'
WHILE (@dtDate <= '12/31/2009')
BEGIN
INSERT INTO #Calendar
(Calendar_Date,Year_Num,Day_Name,Month_Num,Week_Num )

SELECT @dtdate,
Year(@dtDate), 
datename(dw,@dtdate),datepart(month,@dtdate),
datepart(wk,@dtdate)
SET @dtDate = DATEADD(dd,1,@dtDate)
END

SELECT * FROM #Calendar
order by 1

thuya
Starting Member

7 Posts

Posted - 2009-07-02 : 01:16:56

hi
Mr.Tan

i mean eg. my database table is Temptable
Filed----Date column with Datetime DataType
record------01/01/2008 (mm/dd/yyyy)
01/02/2008
01/03/2008
01/04/2008
01/05/2008
01/06/2008
01/07/2008
01/08/2008
01/09/2008 etc......
how can i know starts from Sat, Sun, Mon, Tue ? ? ?
i just want to count Day for (first week,second week,third week,fourth week) crietia by month by year

thanks
regards
thuya

thuya

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 02:00:58

quote:
Originally posted by thuya

hi
Mr.Tan

i mean eg. my database table is Temptable
Filed----Date column with Datetime DataType
record------01/01/2008 (mm/dd/yyyy)
01/02/2008
01/03/2008
01/04/2008
01/05/2008
01/06/2008
01/07/2008
01/08/2008
01/09/2008 etc......
how can i know starts from Sat, Sun, Mon, Tue ? ? ?
i just want to count Day for (first week,second week,third week,fourth week) crietia by month by year

thanks
regards
thuya

thuya

then can you tell me by your definition, for the month of January 2009, how many days for each week ?
week 1 : ?
week 2 : 7
week 3 : 7
week 4 : 7
week 5 : ?

It will varies if you definition for a week starts from Sunday or Monday or any other day.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 03:27:02

if your are using ISO WEEK, ie. Week starting from Monday


select	ISO_WEEK_NO, count(*)
from	F_TABLE_DATE('2009-01-01', '2009-01-31')
GROUP BY ISO_WEEK_NO

KH
[spoiler]Time is always against us[/spoiler]

thuya
Starting Member

7 Posts

Posted - 2009-07-02 : 04:11:21

i will try it!

thanks
regards

thuya

thuya
Starting Member

7 Posts

Posted - 2009-07-13 : 02:53:26

i have one table x_logfile table.

i just want to calculate for weekly from x_logfile with Date Field

but! i dont know Date Field value!

so! weekly calculation is depend on Date Field Vaule

Date Field value is contains 06/13/2008 , 06/22/2008.

i just want to count! like that ........

06/13/2008 plus 7 days is one weeks.
so! start Date is 06/13/2008 ........End Date is 06/20/2008 ! it is one weeks!
start Date is 06/22/2008 ...........End Date is 06/29/2008 ! it is second weeks!
so! only 2 weeks for jun.

pls help me
thanks
regards

thuya

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-13 : 03:01:05

quote:
i just want to calculate for weekly from x_logfile with Date Field

What do you want to calculate ? Or you want to find the end date is it ?


select [date_field], end_date = dateadd(day, 7, [date_field])
from   x_logfile

If this is not what you want, can you post your table structure (only the related field), some sample data from that table and the required result.

KH
[spoiler]Time is always against us[/spoiler]

thuya
Starting Member

7 Posts

Posted - 2009-07-13 : 04:01:11

quote:
Originally posted by khtan

quote:
i just want to calculate for weekly from x_logfile with Date Field

What do you want to calculate ? Or you want to find the end date is it ?

select [date_field], end_date = dateadd(day, 7, [date_field])
from x_logfile

If this is not what you want, can you post your table structure (only the related field), some sample data from that table and the required result.

KH
[spoiler]Time is always against us[/spoiler]

i just want to page hits count! for weekly
so! first week 15 hits count
second week 20 hits count

how to write sql query for conditions statement for firsweek , secondweek,

thuya

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-13 : 04:35:55

can you post your table structure (only the related field), some sample data from that table and the required result.

KH
[spoiler]Time is always against us[/spoiler]

thuya
Starting Member

7 Posts

Posted - 2009-07-13 : 05:14:20

quote:
Originally posted by khtan

can you post your table structure (only the related field), some sample data from that table and the required result.

KH
[spoiler]Time is always against us[/spoiler]

my table structure

Date URIStem
06/13/2008 /sitename/folder/......jpg
06/14/2008 /sitename/folder/......html
06/22/2008 /sitename/folder/......html

so! 06/13/2008 to 06/20/2008 is first week -----hits count 2
06/22/2008 to 06/29/2008 is second week ---hits count 1

thuya

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-13 : 06:21:07

[code]
declare @sample table
(
[date] datetime,
[URIStem] varchar(30)
)

insert into @sample
select '06/13/2008', '/sitename/folder/......jpg' union all
select '06/14/2008', '/sitename/folder/......html' union all
select '06/22/2008', '/sitename/folder/......html'

declare @week table
(
start_date datetime
)

insert into @week
select '06/13/2008' union all
select '06/22/2008'

select start_date = w.start_date,
end_Date = dateadd(day, 7, w.start_date),
count(*)
from @sample s
inner join @week w on s.[date] >= w.start_date
and s.[date] <= dateadd(day, 7, w.start_date)
group by w.start_date

/*
start_date end_Date
------------------------------------------------------ ------------------------------------------------------ -----------
2008-06-13 00:00:00.000 2008-06-20 00:00:00.000 2
2008-06-22 00:00:00.000 2008-06-29 00:00:00.000 1

(2 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources