entry's per week - 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

entry's per week

Author

Topic

bluemagic74
Starting Member

15 Posts

Posted - 2012-01-17 : 11:03:14

Create table TABLE1
[USERID] INT NULL
ENTRYTIME_INDAYS INT NULL
GO

SAMPLE DATA

USERID, ENTRYTIME_INDAYS
1111,4
2222,1
3333,33
4444,7
5555,345
....
...

Now I want to caliculate weekly entries per user

I wrote this script

select userID , COUNT (*) as Week1 from TABLE1 where ENTRYTIME_INDAYS in (0,1,2,3,4,5,6) -- 0 to 6 days
group by USERID

IS there any way i can loop the all the weeks in script

ex: USERID, WEEK1,WEEK2,WEEK3,.....

Thank you.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 11:45:33

Seems like you r table and data are incomplete

Do you have a column for week? Year? Or a Datetime column?

I assume you want to SUM(ENTRYTIME_INDAYS) GROUP BY WEEK

????

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

bluemagic74
Starting Member

15 Posts

Posted - 2012-01-17 : 11:57:00

ok. actually there is datetime column.

USERID,ENTRYDATETIME,ENTRYTIME_INDAYS(added difference between registration complete date and ENTRYDATETIME)

USERID,ENTRYDATETIME,ENTRYTIME_INDAYS
1,2007-12-10,3
1,2007-12-12,4
2,2008-12-3,234
3,2008-03-09,1

NOW I NEED FOR WEEK1(0to 6 days) userid 1 count is 3...like that for all users...week1 week2 week3....

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 12:06:03

Start with this


SELECT USERID, YEAR(ENTRYDATETIME), DATEPART(WEEK,ENTRYDATETIME), SUM(ENTRYTIME_INDAYS) AS SUM_ENTRYTIME_INDAYS
FROM TABLE1
GROUP BY USERID, YEAR(ENTRYDATETIME), DATEPART(WEEK,ENTRYDATETIME)

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

bluemagic74
Starting Member

15 Posts

Posted - 2012-01-17 : 12:22:11

thank you.

for this output shows year and count
how can get a particular week count

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 13:16:11

look again

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources