how to get # of records per a week in a table

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

how to get # of records per a week in a table

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-20 : 12:40:30

Hello All,

in my Table i have below values (example)

how can i calculate how many number of records in each week (based on insertedDate column)

InsertedDate,AcctType,AcctName,Deposit
1/1/2004,A,Mist ,100
1/2/2004,b,Catch,400
1/3/2004,a,asasdd,100
1/4/2004,c,gfsgd,100
1/5/2004,f,dfggre,100
1/6/2004,e,dfsf,100
1/7/2004,a,sdfsdf,300
1/8/2004,a,sdfsdfs,100
1/9/2004,a,dfgdgh,100
1/10/2004,c,fghgfh,100
.....
........
1/17/2008,c,Last,200

Please Help me to get of it

Best Regards
Dhani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 12:43:56

[code]SELECT DATEPART(yy,InsertedDate) AS Year,DATEPART(wk,InsertedDate) AS WeekNo,
COUNT(*)
FROM Table
GROUP BY DATEPART(yy,InsertedDate),DATEPART(wk,InsertedDate)
[/code]

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-20 : 13:04:55

Hello Visakh,

Thanks for your help,

this is what i am really expecting but here the problem is my InsertedDate column is data type of Varchar2(30) how can i use it
i tried with Convert & Cast functions

Convert ( datetime,insertedate,101)
cast (inserteddate as datetime)

but it says outof range not able to convert

Could you please help me

Best Regards
dhani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 13:11:24

do you have some non date values also stored in it? also will the format of date value stored in table field be consistent?

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-20 : 14:01:46

quote:
Originally posted by visakh16

do you have some non date values also stored in it? also will the format of date value stored in table field be consistent?

Hello Visakh,

do you have some non date values also stored in it?
No, it is always Date
also will the format of date value stored in table field be consistent?
yes, the values are same

Best Regards
Dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-20 : 14:06:58

quote:
Originally posted by visakh16

do you have some non date values also stored in it? also will the format of date value stored in table field be consistent?

Hello Visakh,

the values are consistent
but however values stored as

Completemonthname-DD-YYYY
ex: January-01-2008
March-23-2007
December-31-2006

Best Regards
Dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-20 : 14:54:40

Any Ideas

Please......

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-20 : 16:20:52

Dear All,

now with below workout i am getting Year #, Week #, # of Records,

it is fine, but is there any way to get weekday actual Date (i.e Weak Start Day, week #, # of records)

Please any ideas, please i was stuck here....

SELECT Datepart(yy,CONVERT(datetime, REPLACE(CASE (SUBSTRING(created_date, 1, 3))
WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul'
THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' ELSE '12' END + SUBSTRING(Created_Date,
CHARINDEX('-', Created_Date), LEN(Created_Date)), '-', '/'))) AS YEAR, DATEPART(wk, CONVERT(datetime, REPLACE(CASE (SUBSTRING(created_date, 1,
3))
WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul'
THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' ELSE '12' END + SUBSTRING(Created_Date,
CHARINDEX('-', Created_Date), LEN(Created_Date)), '-', '/'))) AS Week
, COUNT(*) AS Expr1
FROM cc_DIP_temp
GROUP BY datepart(yy, CONVERT(datetime, REPLACE(CASE (SUBSTRING(created_date, 1, 3))
WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul'
THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' ELSE '12' END + SUBSTRING(Created_Date,
CHARINDEX('-', Created_Date), LEN(Created_Date)), '-', '/'))), DATEPART(wk, CONVERT(datetime, REPLACE(CASE (SUBSTRING(created_date, 1, 3))
WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul'
THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' ELSE '12' END + SUBSTRING(Created_Date,
CHARINDEX('-', Created_Date), LEN(Created_Date)), '-', '/')))
order by 1,2

Best Regards
Dhani

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-20 : 16:27:20

I'd help but I can't (dont want to) figure out what you need. Follow the instructions here for presenting your question in a way that will be easy to answer: especially steps 2,3, and 5
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-20 : 17:03:59

Hello BG,

thanks for your advice,

here i am explaing in detail.....

as per present (with previous sql query)
i am getting output as below

Year,Week,Count(*)
2006,3,123
2006,13,3432
2006,29,1234
2006,41,3423
.......
.............
2007,36,2135
2007,48,1123
......
..........
.............
2009,7,198

in the second column, it is week number of the year(first column) , how can i get the week beging date and week end date

so expected output would be

Year,WK_Start_Date,WK-End_Date,WeekNumber,Count(*)
2006,Jan/20/2006,Jan/26/2006,3,123
2006,Apr/2/2006,Apr/8/2006,13,3432
2006,Jul/23/2006,Jul/29/2006,29,1234
2006,Oct/15/2006,Oct/21/2006,41,3423
.......
.............
2009,Feb/15/2006,Feb/21/2006,8,198

what changes can i made to get this result from the above(previous post) sql query

Please Help me

Thanks in advance

Best Regards
Dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-20 : 18:28:48

Hello All,

Can any one please help me
any ideas

Regards
Dhani

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-20 : 18:36:06

dhani,

What I'm looking for is executable t-sql code that you provide that will create a table called [cc_DIP_temp] as well as code that you provide that will populate that table with your sample data. That way all I have to do is write a query, which I can test myself, to make sure the output matches your exected results.

The link I provided was quite clear on this. Sorry, but please have another look at it and try again... and the name is TG not BG

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-20 : 20:57:46

Did you read the link that TG suggested???????????????????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-20 : 20:57:46

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 02:15:51

quote:
Originally posted by dhani

Hello BG,

thanks for your advice,

here i am explaing in detail.....

as per present (with previous sql query)
i am getting output as below

Year,Week,Count(*)
2006,3,123
2006,13,3432
2006,29,1234
2006,41,3423
.......
.............
2007,36,2135
2007,48,1123
......
..........
.............
2009,7,198

in the second column, it is week number of the year(first column) , how can i get the week beging date and week end date

so expected output would be

Year,WK_Start_Date,WK-End_Date,WeekNumber,Count(*)
2006,Jan/20/2006,Jan/26/2006,3,123
2006,Apr/2/2006,Apr/8/2006,13,3432
2006,Jul/23/2006,Jul/29/2006,29,1234
2006,Oct/15/2006,Oct/21/2006,41,3423
.......
.............
2009,Feb/15/2006,Feb/21/2006,8,198

what changes can i made to get this result from the above(previous post) sql query

Please Help me

Thanks in advance

Best Regards
Dhani

assuming year and weekno are integer columns you can try this


SELECT Year,
DATEADD(wk,Week-1,DATEADD(yy,Year-1900,0)) AS WK_Start_Date,
DATEADD(wk,Week,DATEADD(yy,Year-1900,0))-1 AS WK_End_Date,
Week AS WeekNumber,
Count
FROM Table

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-21 : 12:56:25

Hello All,
Sorry for my so far wrong postings,now i got it how to post Q's

Thanks to TG & Brett

Now

1) DDL (unfortunately my createdate column is datatype of varchar not datetime)
create table MyTestTab1 (CreateDate varchar(30),ID int,name varchar(30))

2) DML(i am showing only sample data, but my original table has a million records)

insert into MyTestTab1 Values('January-01-2006',1,'NYC')
insert into MyTestTab1 Values('January-11-2006',2,'NJ')
insert into MyTestTab1 Values('January-06-2006',1,'NYC')
insert into MyTestTab1 Values('June-21-2007',1,'Kolan')
insert into MyTestTab1 Values('January-07-2009',4,'NYC')
insert into MyTestTab1 Values('June-09-2006',1,'Kolan')
insert into MyTestTab1 Values('January-18-2004',1,'NJ')
insert into MyTestTab1 Values('June-28-2006',2,'Kolan')
insert into MyTestTab1 Values('January-29-2007',2,'NYC')
insert into MyTestTab1 Values('January-16-2005',1,'Kolan')
insert into MyTestTab1 Values('March-21-2006',1,'NJ')
insert into MyTestTab1 Values('March-23-2008',1,'NYC')
insert into MyTestTab1 Values('March-01-2006',4,'Kolan')
insert into MyTestTab1 Values('March-17-2008',5,'NYC')
insert into MyTestTab1 Values('March-22-2007',1,'NJ')
insert into MyTestTab1 Values('March-12-2006',7,'NJ')
insert into MyTestTab1 Values('September-01-2004',1,'NYC')
insert into MyTestTab1 Values('September-11-2007',2,'NJ')
insert into MyTestTab1 Values('September-06-2009',1,'NYC')
insert into MyTestTab1 Values('June-21-2008',1,'Kolan')
insert into MyTestTab1 Values('September-17-2007',4,'NYC')
insert into MyTestTab1 Values('September-19-2006',1,'Kolan')
insert into MyTestTab1 Values('January-08-2006',1,'NJ')
insert into MyTestTab1 Values('June-08-2006',2,'Kolan')
insert into MyTestTab1 Values('August-09-2005',2,'NYC')
insert into MyTestTab1 Values('January-19-2006',1,'Kolan')
insert into MyTestTab1 Values('August-11-2008',1,'NJ')
insert into MyTestTab1 Values('November-13-2004',1,'NYC')
insert into MyTestTab1 Values('March-11-2009',4,'Kolan')
insert into MyTestTab1 Values('August-12-2004',5,'NYC')
insert into MyTestTab1 Values('November-22-2009',1,'NJ')
insert into MyTestTab1 Values('November-12-2008',7,'NJ')

3) with below Query i am getting results as
YEAR,Week,Expr1
2004 4 1
2004 33 1
2004 36 1
2004 46 1
2006 23 2
many more...................
..................................

SELECT

Datepart(yy,CONVERT(datetime, REPLACE(CASE (SUBSTRING(createdate, 1, 3))
WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul'
THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' ELSE '12' END + SUBSTRING(createdate,
CHARINDEX('-', createdate), LEN(createdate)), '-', '/'))) AS YEAR

,DATEPART(wk, CONVERT(datetime, REPLACE(CASE (SUBSTRING(createdate, 1,
3))WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul'
THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' ELSE '12' END + SUBSTRING(createdate,
CHARINDEX('-', createdate), LEN(createdate)), '-', '/'))) AS Week

,COUNT(*) AS Expr1

FROM mytesttab1

GROUP BY datepart(yy, CONVERT(datetime, REPLACE(CASE (SUBSTRING(createdate, 1, 3))
WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul'
THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' ELSE '12' END + SUBSTRING(createdate,
CHARINDEX('-', createdate), LEN(createdate)), '-', '/'))),

DATEPART(wk, CONVERT(datetime, REPLACE(CASE (SUBSTRING(createdate, 1, 3))
WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul'
THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' ELSE '12' END + SUBSTRING(createdate,
CHARINDEX('-', createdate), LEN(createdate)), '-', '/')))

order by 1,2

4) Required Output
YEAR,Week,Expr1,weekStartDate,WeekEndDate
2004,4,1,01/18/2004,01/24/2004
2004,33,1,08/15/2004,08/21/2004
2004,36,1,09/07/2004,09/13/2004
2004,46,2,11/18/2004,11/24/2004
2006,23,2,05/11/2006,05/17/2006
many more...................

------> explanation

take example
YEAR,Week,Expr1,weekStartDate,WeekEndDate
2004,4,1,01/18/2004,01/24/2004

here 2004 is the year, 4 is week number having 1 row, week # 4 start date is 01/18/2004 and week # 4 end date is 01/24/2004

Please Help me

Best Regadrs
dhani.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-22 : 00:58:21

did you try my suggestion?

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-22 : 02:03:16

Hello Visakh,

quote:
SELECT Year,
DATEADD(wk,Week-1,DATEADD(yy,Year-1900,0)) AS WK_Start_Date,
DATEADD(wk,Week,DATEADD(yy,Year-1900,0))-1 AS WK_End_Date,
Week AS WeekNumber,
Count
FROM Table

i tried it, but it has some problem with DateAdd function,
also in my query Year is not a column in my table.

also one more doubt here in your query, if i use this query i have to write group by clause (since we are using count function) and also need to mention week start date, week end date in group by clause then the output will be different

Best Regards
dhani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-22 : 02:11:38

whats the problem with dateadd?
if you want to use week start and end in select list then you need to add them to group by

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-22 : 02:19:21

quote:
Originally posted by visakh16

whats the problem with dateadd?
if you want to use week start and end in select list then you need to add them to group by

1) Problem with Date add it is saying invalid column name Year, week

2) then if we add startdate, end date then the result will be group by each date right? we need it to be week wise

here is explanation

we are having many rows in a table
it needs to look how many rows were added (by one date column) each week

upto here it is good we are getting result as

Year, Week#, NoOFRecords
------------------------
2008,22,3980
2009,03,4345

so in the above query we want to see what is actual date (start, end) for week # 22, like wise for week # 03 (seecond row) ,,....
for all other rows

so here if we add week startdate, week end date then it will group by each individual date right?

please give me a hand,

Best Regards
dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-22 : 07:43:56

Hello All,

Thanks to all, whom ever tried to help me, thank you very much.

Mr.Talent gave me this help,

it might be useful to some one else

SELECT
Datepart(yy, a.date) AS Year
,DATEPART(wk, a.date) AS Week
,COUNT(*) AS Cnt
,DATEADD(d, -1*(DATEPART(dw, a.date)-1), a.date) as StartOfWeek
,DATEADD(d, 7-datepart(dw, a.date), a.date) as EndOfWeek
FROM (
SELECT
CONVERT(datetime,
REPLACE(CASE (SUBSTRING(createdate, 1, 3))
WHEN 'Jan' THEN '01'
WHEN 'Feb' THEN '02'
WHEN 'Mar' THEN '03'
WHEN 'Apr' THEN '04'
WHEN 'May' THEN '05'
WHEN 'Jun' THEN '06'
WHEN 'Jul' THEN '07'
WHEN 'Aug' THEN '08'
WHEN 'Sep' THEN '09'
WHEN 'Oct' THEN '10'
WHEN 'Nov' THEN '11'
ELSE '12' END +
SUBSTRING(createdate, CHARINDEX('-', createdate), LEN(createdate)), '-', '/'))
as date FROM mytesttab1) as a
GROUP BY
DATEPART(yy, a.date)
,DATEPART(wk, a.date)
,DATEADD(d, -1*(DATEPART(dw, a.date)-1), a.date)
,DATEADD(d, 7-datepart(dw, a.date), a.date)
order by 1, 2

Best Regards
Dhani

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources