Weeks of months - 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

Weeks of months

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-10-24 : 16:29:10

Hi all,
I have the list of dates:


2012-08-16 22:22:32.000
2012-07-11 20:18:03.000
2012-09-12 14:29:35.000
2012-09-17 14:20:50.000
2012-08-16 16:28:23.000
2012-08-16 17:13:48.000
2012-07-18 15:37:36.000
2012-08-16 16:35:41.000
2012-07-12 12:29:13.000
2012-09-26 21:16:29.000
2012-07-11 19:42:34.000
2012-08-16 17:50:52.000
2012-09-27 19:15:12.000
I need to count how many lines fall into each week (1,2,3,4) no matter which month is!!
Week 1	Week 2	Week 3	Week 4	
13	10	10	11	
8	6	8	8

Any hints please!!
Thanks.

--------------------------
Joins are what RDBMS's do for a living

Mike Jackson
Starting Member

37 Posts

Posted - 2012-10-24 : 16:53:20

The easiest way would be to create a calendar table. Add column for week number.

Join the 2 tables then count all by week number.

Mike

Mike Jackson
Starting Member

37 Posts

Posted - 2012-10-24 : 17:03:07

select DATEPART(dw, yourfield), count(DATEPART(dw, yourfield))
from yourtable
group by DATEPART(dw, yourfield)
order by DATEPART(dw, yourfield)

That should do it also... (not tested)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 17:18:00

I see two lines of numerical values. what do second row values represent?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-10-24 : 18:21:51

quote:

Originally posted by xhostx

Hi all,
I have the list of dates:


2012-08-16 22:22:32.000
2012-07-11 20:18:03.000
2012-09-12 14:29:35.000
2012-09-17 14:20:50.000
2012-08-16 16:28:23.000
2012-08-16 17:13:48.000
2012-07-18 15:37:36.000
2012-08-16 16:35:41.000
2012-07-12 12:29:13.000
2012-09-26 21:16:29.000
2012-07-11 19:42:34.000
2012-08-16 17:50:52.000
2012-09-27 19:15:12.000
I need to count how many lines fall into each week (1,2,3,4) no matter which month is!!
Week 1	Week 2	Week 3	Week 4	
13	10	10	11	
8	6	8	8

Any hints please!!
Thanks.

--------------------------
Joins are what RDBMS's do for a living

You need to start by defining exactly what "week of month" means in your application.

There are many possible definitions, and we have no way of knowing what yours is.

CODO ERGO SUM

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-10-24 : 20:56:16

how to get second row in yours pivot?


declare @a table(col1 datetime)insert into @a select
'2012-08-16 22:22:32.000' union all select
'2012-07-11 20:18:03.000' union all select
'2012-09-12 14:29:35.000' union all select
'2012-09-17 14:20:50.000' union all select
'2012-08-16 16:28:23.000' union all select
'2012-08-16 17:13:48.000' union all select
'2012-07-18 15:37:36.000' union all select
'2012-08-16 16:35:41.000' union all select
'2012-07-12 12:29:13.000' union all select
'2012-09-26 21:16:29.000' union all select
'2012-07-11 19:42:34.000' union all select
'2012-08-16 17:50:52.000' union all select
'2012-09-27 19:15:12.000'
SELECT [1], [2], [3], [4], [5]
FROM(
	select cnt = 1, wm = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, col1), 0), col1) +1
	from @a)src
PIVOT(
	SUM(cnt) FOR wm IN ([1], [2], [3], [4], [5])
)pvt

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-10-25 : 08:40:01

[code]select a.Week, COUNT(*)
from (SELECT case when DAY(createDate) <=7 then 'week1'
when DAY(createDate) >=8 and DAY(createDate) <=14 then 'week2'
when DAY(createDate) >=15 and DAY(createDate) <=21 then 'week3'
when DAY(createDate) >=22 then 'week4'
END as "Week",m.CreateDate, u.UserName, m.Email, m.UserId
FROM dbo.aspnet_Membership m
JOIN dbo.aspnet_Users u
ON u.UserId = m.UserId
WHERE u.LoweredUserName NOT IN ('vada','vadamikala','mbiernat','keng','jhalim','bruce','allen')
-- this is to ignore all test accounts (coded in per request)
and m.CreateDate >= '2000-01-01'
and m.CreateDate<= GETDATE()) a
group by a.Week
[/code]

how about this one!

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 10:04:01

quote:

Originally posted by xhostx

select a.Week, COUNT(*)
from (SELECT case when DAY(createDate) <=7 then 'week1'
	                        when DAY(createDate) >=8 and DAY(createDate) <=14 then 'week2'
	                        when DAY(createDate) >=15 and DAY(createDate) <=21 then 'week3'
	                        when DAY(createDate) >=22 then 'week4'
	                        	                        END as "Week",m.CreateDate, u.UserName, m.Email, m.UserId
	FROM dbo.aspnet_Membership m
	JOIN dbo.aspnet_Users u
		ON u.UserId = m.UserId
	WHERE u.LoweredUserName NOT IN ('vada','vadamikala','mbiernat','keng','jhalim','bruce','allen') 
	-- this is to ignore all test accounts (coded in per request)
	and m.CreateDate >= '2000-01-01'
	and m.CreateDate<= GETDATE()) a
	group by a.Week

how about this one!

--------------------------
Joins are what RDBMS's do for a living

this wont give you weeks as new columns
this will return you one row per week

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-10-25 : 11:43:45

Well, you are right!
but the pupose is to create a stored proc that generate these fields and create a pivot table out of it in Crystal report.

What it meant to happen here is separate the calculation from the report and the jus the pivot table' column manipulation at the report. Do you think is this best practice?

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 12:16:46

quote:
Originally posted by xhostx

Well, you are right!
but the pupose is to create a stored proc that generate these fields and create a pivot table out of it in Crystal report.

What it meant to happen here is separate the calculation from the report and the jus the pivot table' column manipulation at the report. Do you think is this best practice?

--------------------------
Joins are what RDBMS's do for a living

yep...if there's way to do it at report end for pivoting based on dynamic values thats nest, otherwise you've to use dynamic sql in backend code which is not recommended approach
we use similar logic at report end for our SSRS reports when needed using matrix containers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-10-25 : 16:54:34

Thank you ALL :)!

--------------------------
Joins are what RDBMS's do for a living

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources