How to get the Every Friday to Thursday data

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

SQL Server 2008 Forums

Transact-SQL (2008)

How to get the Every Friday to Thursday data

Author

Topic

narukkp
Starting Member

5 Posts

Posted - 2012-02-24 : 04:51:30

hi i want the data every Friday to Thursday in any month
For example:
Feb 2012

i want bellow format:

1-2Feb 1week,
3-9Feb 2ndweek
10-16Feb 3rdWeek
17-23Feb 4th Week
24-29Feb 5th week

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-24 : 04:57:20

[code]DECLARE @Year SMALLINT = 2012,
@Month TINYINT = 2

;WITH cteSource(theDate)
AS (
SELECT DATEADD(MONTH, 12 * @Year + @Month - 22801, Number) AS theDate
FROM master.dbo.spt_values
WHERE Type = 'P'
AND Number BETWEEN 0 AND 30
)
SELECT ROW_NUMBER() OVER (ORDER BY MIN(theDate)) AS WeekNum,
MIN(theDate) AS FromDate,
MAX(theDate) AS ToDate
FROM cteSource
WHERE DATEPART(MONTH, theDate) = @Month
GROUP BY DATEDIFF(DAY, '18991229', theDate) / 7 * 7[/code]

N 56°04'39.26"
E 12°55'05.63"

narukkp
Starting Member

5 Posts

Posted - 2012-02-24 : 05:08:00

Thank q for reply , suppose i want this format entire year wise, so how to get the same format for all months at a time?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-24 : 05:19:12

Like this?

DECLARE	@Year SMALLINT = 2012

;WITH cteSource(theDate)
AS (
	SELECT	DATEADD(MONTH, 12 * @Year - 22800, Number) AS theDate
	FROM	master.dbo.spt_values
	WHERE	Type = 'P'
		AND Number BETWEEN 0 AND 365
)
SELECT		DATEPART(MONTH, theDate) AS theMonth,
		MIN(theDate) AS FromDate,
		MAX(theDate) AS ToDate,
		ROW_NUMBER() OVER (PARTITION BY DATEPART(MONTH, theDate) ORDER BY MIN(theDate)) AS WeekNum
FROM		cteSource
WHERE		DATEPART(YEAR, theDate) = @Year
GROUP BY	DATEPART(MONTH, theDate),
		DATEDIFF(DAY, '18991229', theDate) / 7 * 7

N 56°04'39.26"
E 12°55'05.63"

narukkp
Starting Member

5 Posts

Posted - 2012-02-24 : 08:10:15

Hi Once again thankq for your reply, but still i have some problem because if some dates are missing in my table then it is not working properly. For example: In my table JAN Month the data having following way

date value
Jan-2 1
Jan-2 1
Jan-16 1
Jan-23 1
Jan-23 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-24 : 08:21:46

This is the output I get when I use the complete, unedited, code above (@Year = 2012).

theMonth	FromDate	ToDate	WeekNum
1	2012-01-01 00:00:00.000	2012-01-05 00:00:00.000	1
1	2012-01-06 00:00:00.000	2012-01-12 00:00:00.000	2
1	2012-01-13 00:00:00.000	2012-01-19 00:00:00.000	3
1	2012-01-20 00:00:00.000	2012-01-26 00:00:00.000	4
1	2012-01-27 00:00:00.000	2012-01-31 00:00:00.000	5
2	2012-02-01 00:00:00.000	2012-02-02 00:00:00.000	1
2	2012-02-03 00:00:00.000	2012-02-09 00:00:00.000	2
2	2012-02-10 00:00:00.000	2012-02-16 00:00:00.000	3
2	2012-02-17 00:00:00.000	2012-02-23 00:00:00.000	4
2	2012-02-24 00:00:00.000	2012-02-28 00:00:00.000	5
3	2012-03-01 00:00:00.000	2012-03-01 00:00:00.000	1
3	2012-03-02 00:00:00.000	2012-03-08 00:00:00.000	2
3	2012-03-09 00:00:00.000	2012-03-15 00:00:00.000	3
3	2012-03-16 00:00:00.000	2012-03-22 00:00:00.000	4
3	2012-03-23 00:00:00.000	2012-03-29 00:00:00.000	5
3	2012-03-30 00:00:00.000	2012-03-31 00:00:00.000	6
4	2012-04-01 00:00:00.000	2012-04-05 00:00:00.000	1
4	2012-04-06 00:00:00.000	2012-04-12 00:00:00.000	2
4	2012-04-13 00:00:00.000	2012-04-19 00:00:00.000	3
4	2012-04-20 00:00:00.000	2012-04-26 00:00:00.000	4
4	2012-04-27 00:00:00.000	2012-04-30 00:00:00.000	5
5	2012-05-01 00:00:00.000	2012-05-03 00:00:00.000	1
5	2012-05-04 00:00:00.000	2012-05-10 00:00:00.000	2
5	2012-05-11 00:00:00.000	2012-05-17 00:00:00.000	3
5	2012-05-18 00:00:00.000	2012-05-24 00:00:00.000	4
5	2012-05-25 00:00:00.000	2012-05-31 00:00:00.000	5
6	2012-06-01 00:00:00.000	2012-06-07 00:00:00.000	1
6	2012-06-08 00:00:00.000	2012-06-14 00:00:00.000	2
6	2012-06-15 00:00:00.000	2012-06-21 00:00:00.000	3
6	2012-06-22 00:00:00.000	2012-06-28 00:00:00.000	4
6	2012-06-29 00:00:00.000	2012-06-30 00:00:00.000	5
7	2012-07-01 00:00:00.000	2012-07-05 00:00:00.000	1
7	2012-07-06 00:00:00.000	2012-07-12 00:00:00.000	2
7	2012-07-13 00:00:00.000	2012-07-19 00:00:00.000	3
7	2012-07-20 00:00:00.000	2012-07-26 00:00:00.000	4
7	2012-07-27 00:00:00.000	2012-07-31 00:00:00.000	5
8	2012-08-01 00:00:00.000	2012-08-02 00:00:00.000	1
8	2012-08-03 00:00:00.000	2012-08-09 00:00:00.000	2
8	2012-08-10 00:00:00.000	2012-08-16 00:00:00.000	3
8	2012-08-17 00:00:00.000	2012-08-23 00:00:00.000	4
8	2012-08-24 00:00:00.000	2012-08-30 00:00:00.000	5
8	2012-08-31 00:00:00.000	2012-08-31 00:00:00.000	6
9	2012-09-01 00:00:00.000	2012-09-06 00:00:00.000	1
9	2012-09-07 00:00:00.000	2012-09-13 00:00:00.000	2
9	2012-09-14 00:00:00.000	2012-09-20 00:00:00.000	3
9	2012-09-21 00:00:00.000	2012-09-27 00:00:00.000	4
9	2012-09-28 00:00:00.000	2012-09-30 00:00:00.000	5
10	2012-10-01 00:00:00.000	2012-10-04 00:00:00.000	1
10	2012-10-05 00:00:00.000	2012-10-11 00:00:00.000	2
10	2012-10-12 00:00:00.000	2012-10-18 00:00:00.000	3
10	2012-10-19 00:00:00.000	2012-10-25 00:00:00.000	4
10	2012-10-26 00:00:00.000	2012-10-31 00:00:00.000	5
11	2012-11-01 00:00:00.000	2012-11-01 00:00:00.000	1
11	2012-11-02 00:00:00.000	2012-11-08 00:00:00.000	2
11	2012-11-09 00:00:00.000	2012-11-15 00:00:00.000	3
11	2012-11-16 00:00:00.000	2012-11-22 00:00:00.000	4
11	2012-11-23 00:00:00.000	2012-11-29 00:00:00.000	5
11	2012-11-30 00:00:00.000	2012-11-30 00:00:00.000	6
12	2012-12-01 00:00:00.000	2012-12-06 00:00:00.000	1
12	2012-12-07 00:00:00.000	2012-12-13 00:00:00.000	2
12	2012-12-14 00:00:00.000	2012-12-20 00:00:00.000	3
12	2012-12-21 00:00:00.000	2012-12-27 00:00:00.000	4
12	2012-12-28 00:00:00.000	2012-12-31 00:00:00.000	5

N 56°04'39.26"
E 12°55'05.63"

narukkp
Starting Member

5 Posts

Posted - 2012-02-29 : 01:26:53

Hi,
Thank q for your help. But my problem is

I have following tables like
1)Table : QMaster & datatype of date column is nvarchar

date cnt Empcode
2/2/2011 1 101
2/2/2011 1 102
2/4/2011 1 103
2/4/2011 1 104
2/6/2011 1 105
2/6/2011 1 106
2/8/2011 1 107
2/9/2011 1 108
2/10/2011 1 109
2/11/2011 1 110
2/11/2011 1 111
2/24/2011 1 112
2/27/2011 1 113
2/27/2011 1 114
3/01/2011 1 115
3/04/2011 1 116
3/09/2011 1 117

2)Table: QEmp
empcode EmpName
101 a
102 b
103 c
104 d
105 e
106 f
107 g
108 h
109 i
110 j
111 k
112 l
113 m
114 n
115 0
116 p
117 q

MY output format is
Week count empcode empname
1-3 feb 1 101 a
1-3 feb 1 102 b
4-10 feb 1 103 c
4-10 feb 1 104 d
4-10 feb 1 105 e
4-10 feb 1 106 f
4-10 feb 1 107 g
4-10 feb 1 108 h
4-10 feb 1 109 i
11-17 feb 1 110 j
11-17 feb 1 111 k
18-24 feb 1 112 l
25-28 feb 1 113 m
25-28 feb 1 114 n
1-3 march 1 115 0
4-10 march 1 116 p
4-10 march 1 117 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources