create star and end dates - 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

SQL Server 2000 Forums

Transact-SQL (2000)

create star and end dates

Author

Topic

Ubbe
Starting Member

14 Posts

Posted - 2011-10-20 : 07:22:01

I need assistance please...

I wish the following:
ra gr xno startxdate endxdate
2I 728 1471 2011-10-11 2011-10-14
1I 701 1471 2011-10-15 2011-10-15
2I 728 1471 2011-10-16 2011-10-21

with this dataset
xdate ra gr xno
2011-10-11 2I 728 1471
2011-10-12 2I 728 1471
2011-10-13 2I 728 1471
2011-10-14 2I 728 1471
2011-10-15 1I 701 1471
2011-10-16 2I 728 1471
2011-10-17 2I 728 1471
2011-10-18 2I 728 1471
2011-10-19 2I 728 1471
2011-10-20 2I 728 1471
2011-10-21 2I 728 1471

How do you read row by row, and breaks at the right date.

Best Regards
ub

-oOo-

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 08:01:08

no need of row by row. just use


SELECT ra,gr,xno,MIN(xdate) AS startxdate,MAX(xdate) AS endxdate
FROM table
GROUP BY ra,gr,xno

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

Ubbe
Starting Member

14 Posts

Posted - 2011-10-20 : 10:19:11

Thanks but then the result will be like this:
startxdate endxdate ra gr xno
---------- ---------- ----- ---------- ----
2011-10-15 2011-10-29 1I 701 1471
2011-10-11 2011-10-31 2I 728 1471

what I want is:
ra gr xno startxdate endxdate
2I 728 1471 2011-10-11 2011-10-14
1I 701 1471 2011-10-15 2011-10-15
2I 728 1471 2011-10-16 2011-10-21

to read row by row and break at the date,
Hope you understand, I'm bad at that explanation :)

/ub

-oOo-

Ubbe
Starting Member

14 Posts

Posted - 2011-10-20 : 10:22:19

Sorry!
I think that your proposal will be:

startxdate endxdate ra gr xno
---------- ---------- ----- ---------- ----
2011-10-15 2011-10-15 1I 701 1471
2011-10-11 2011-10-21 2I 728 1471

-oOo-

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-10-20 : 12:08:25

In SQL2005, and above, you can use the ROW_NUMBER() windowed function.

In SQL2000 there is no good way to do this. If you do not have too much data you may be able to get away with:


-- *** Test Data ***
CREATE TABLE #t
(
	xdate datetime NOT NULL
	,ra char(2) NOT NULL
	,gr smallint NOT NULL
	,xno smallint NOT NULL
)
INSERT INTO #t
SELECT '20111011', '2I', 728, 1471
UNION ALL SELECT '20111012', '2I', 728, 1471
UNION ALL SELECT '20111013', '2I', 728, 1471
UNION ALL SELECT '20111014', '2I', 728, 1471
UNION ALL SELECT '20111015', '1I', 701, 1471
UNION ALL SELECT '20111016', '2I', 728, 1471
UNION ALL SELECT '20111017', '2I', 728, 1471
UNION ALL SELECT '20111018', '2I', 728, 1471
UNION ALL SELECT '20111019', '2I', 728, 1471
UNION ALL SELECT '20111020', '2I', 728, 1471
UNION ALL SELECT '20111021', '2I', 728, 1471
-- *** End Test Data ***

SELECT ra, gr, xno, StartDate, EndDate
FROM
(
	SELECT ra, gr, xno, grp
		,MIN(xdate) AS StartDate
		,MAX(xdate) AS EndDate
	FROM
	(
		SELECT ra, gr, xno
			, YEAR(xdate) * 10000 + MONTH(xdate) * 100 + DAY(xdate)
				-
				(
					SELECT COUNT(*)
					FROM #t T2
					WHERE T2.ra = T1.ra
						AND T2.gr = T1.gr
						AND T2.xno = T1.xno
						AND T2.xdate <= T1.xdate
				) AS grp
			,xdate
		FROM #t T1
	) D1
	GROUP BY ra, gr, xno, grp
) D
ORDER BY StartDate

Ubbe
Starting Member

14 Posts

Posted - 2011-10-21 : 02:29:59

I have sql2000 and i have lots of data over 600 000 rows.
And Start and End date must not overlap.
the key: ra, gr, xno, grp.

Are there any more ideas ...

-oOo-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-21 : 02:39:44

did you try the query Ifor posted ?

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

Ubbe
Starting Member

14 Posts

Posted - 2011-10-21 : 03:27:56

Yes, and I am very grateful.
But I try to get the right enddate.
In this proposal, the enddate last day of the month.
ex.
ra gr xno StartDate EndDate
2I 728 1471 2010-09-01 2010-09-30
2I 728 1471 2010-10-01 2010-10-31
2I 728 1471 2010-11-01 2010-11-30
2I 728 1471 2010-12-01 2010-12-23
and so on...

my wish is.
ex.
2I 728 1471 2010-09-01 2011-10-14
1I 701 1471 2011-10-15 2011-10-15
2I 728 1471 2011-10-16 2011-10-21
1I 701 1471 2011-10-22 2011-10-22
and so on...

I am sorry I did not express myself well.

-oOo-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-21 : 03:37:12

can you provide the sample data that Ifor's query does not give you what you want ? Please also post your expected result for the sample data

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

Ubbe
Starting Member

14 Posts

Posted - 2011-10-21 : 08:46:09

OK!
-- *** Test Data ***
CREATE TABLE #t
(
xdate datetime NOT NULL
,ra char(3) NOT NULL
,gr char(3) NOT NULL
,xno char(4) NOT NULL
)
INSERT INTO #t
SELECT '2011-09-01', '2I', '728', '1471'
UNION ALL SELECT '2011-09-02', '2I', '728', '1471'
UNION ALL SELECT '2011-09-03', '2I', '728', '1471'
UNION ALL SELECT '2011-09-04', '2I', '728', '1471'
UNION ALL SELECT '2011-09-05', '2I', '728', '1471'
UNION ALL SELECT '2011-09-06', '2I', '728', '1471'
UNION ALL SELECT '2011-09-07', '2I', '728', '1471'
UNION ALL SELECT '2011-09-08', '2I', '728', '1471'
UNION ALL SELECT '2011-09-09', '2I', '728', '1471'
UNION ALL SELECT '2011-09-10', '2I', '728', '1471'
UNION ALL SELECT '2011-09-11', '2I', '728', '1471'
UNION ALL SELECT '2011-09-12', '2I', '728', '1471'
UNION ALL SELECT '2011-09-13', '2I', '728', '1471'
UNION ALL SELECT '2011-09-14', '2I', '728', '1471'
UNION ALL SELECT '2011-09-15', '2I', '728', '1471'
UNION ALL SELECT '2011-09-16', '2I', '728', '1471'
UNION ALL SELECT '2011-09-17', '2I', '728', '1471'
UNION ALL SELECT '2011-09-18', '2I', '728', '1471'
UNION ALL SELECT '2011-09-19', '2I', '728', '1471'
UNION ALL SELECT '2011-09-20', '2I', '728', '1471'
UNION ALL SELECT '2011-09-21', '2I', '728', '1471'
UNION ALL SELECT '2011-09-22', '2I', '728', '1471'
UNION ALL SELECT '2011-09-23', '2I', '728', '1471'
UNION ALL SELECT '2011-09-24', '2I', '728', '1471'
UNION ALL SELECT '2011-09-25', '2I', '728', '1471'
UNION ALL SELECT '2011-09-26', '2I', '728', '1471'
UNION ALL SELECT '2011-09-27', '2I', '728', '1471'
UNION ALL SELECT '2011-09-28', '2I', '728', '1471'
UNION ALL SELECT '2011-09-29', '2I', '728', '1471'
UNION ALL SELECT '2011-09-30', '2I', '728', '1471'
UNION ALL SELECT '2011-10-01', '2I', '728', '1471'
UNION ALL SELECT '2011-10-02', '2I', '728', '1471'
UNION ALL SELECT '2011-10-03', '2I', '728', '1471'
UNION ALL SELECT '2011-10-04', '2I', '728', '1471'
UNION ALL SELECT '2011-10-05', '2I', '728', '1471'
UNION ALL SELECT '2011-10-06', '2I', '728', '1471'
UNION ALL SELECT '2011-10-07', '2I', '728', '1471'
UNION ALL SELECT '2011-10-08', '2I', '728', '1471'
UNION ALL SELECT '2011-10-09', '2I', '728', '1471'
UNION ALL SELECT '2011-10-10', '2I', '728', '1471'
UNION ALL SELECT '2011-10-11', '2I', '728', '1471'
UNION ALL SELECT '2011-10-12', '2I', '728', '1471'
UNION ALL SELECT '2011-10-13', '2I', '728', '1471'
UNION ALL SELECT '2011-10-14', '2I', '728', '1471'
UNION ALL SELECT '2011-10-15', '1I', '701', '1471'
UNION ALL SELECT '2011-10-16', '2I', '728', '1471'
UNION ALL SELECT '2011-10-17', '2I', '728', '1471'
UNION ALL SELECT '2011-10-18', '2I', '728', '1471'
UNION ALL SELECT '2011-10-19', '2I', '728', '1471'
UNION ALL SELECT '2011-10-20', '2I', '728', '1471'
UNION ALL SELECT '2011-10-21', '2I', '728', '1471'
UNION ALL SELECT '2011-10-22', '1I', '701', '1471'
UNION ALL SELECT '2011-10-23', '2I', '728', '1471'
UNION ALL SELECT '2011-10-24', '2I', '728', '1471'
UNION ALL SELECT '2011-10-25', '2I', '728', '1471'
UNION ALL SELECT '2011-10-26', '2I', '728', '1471'
UNION ALL SELECT '2011-10-27', '2I', '728', '1471'
UNION ALL SELECT '2011-10-28', '2I', '728', '1471'
UNION ALL SELECT '2011-10-29', '1I', '701', '1471'
UNION ALL SELECT '2011-10-30', '2I', '728', '1471'
UNION ALL SELECT '2011-10-31', '2I', '728', '1471'
UNION ALL SELECT '2011-11-01', '2I', '728', '1471'

-- *** End Test Data ***

SELECT ra, gr, xno, StartDate, EndDate
FROM
(
SELECT ra, gr, xno, grp
,MIN(xdate) AS StartDate
,MAX(xdate) AS EndDate
FROM
(
SELECT ra, gr, xno
, YEAR(xdate) * 10000 + MONTH(xdate) * 100 + DAY(xdate)
-
(
SELECT COUNT(*)
FROM #t T2
WHERE T2.ra = T1.ra
AND T2.gr = T1.gr
AND T2.xno = T1.xno
AND T2.xdate <= T1.xdate
) AS grp
,xdate
FROM #t T1
) D1
GROUP BY ra, gr, xno, grp
) D
ORDER BY StartDate

This is the result of the above script
ra gr xno StartDate EndDate
2I 728 1471 2011-09-01 2011-09-30
2I 728 1471 2011-10-01 2011-10-14
1I 701 1471 2011-10-15 2011-10-15
2I 728 1471 2011-10-16 2011-10-21
1I 701 1471 2011-10-22 2011-10-22
2I 728 1471 2011-10-23 2011-10-28
1I 701 1471 2011-10-29 2011-10-29
2I 728 1471 2011-10-30 2011-10-31
2I 728 1471 2011-11-01 2011-11-01

But my this is may wish
ra gr xno StartDate EndDate
2I 728 1471 2011-09-01 2011-10-14
1I 701 1471 2011-10-15 2011-10-15
2I 728 1471 2011-10-16 2011-10-21
1I 701 1471 2011-10-22 2011-10-22
2I 728 1471 2011-10-23 2011-10-28
1I 701 1471 2011-10-29 2011-10-29
2I 728 1471 2011-10-30 2011-11-01

I have to compress it more between start-and enddate,
see the first and last line.

/ub

-oOo-

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-10-21 : 09:03:59

To get over the month boundary problem:

replace

YEAR(xdate) * 10000 + MONTH(xdate) * 100 + DAY(xdate)

with

DATEDIFF(day, 0, xdate)

Ubbe
Starting Member

14 Posts

Posted - 2011-10-21 : 09:30:47

OK, that's right by my example, but if I run on a larger data source.
So will the following results

Line ra gr xno StartDate EndDate
1 2I 728 1471 2010-09-01 2010-12-23
2 2I 728 1471 2010-12-26 2010-12-30
3 2I 728 1471 2011-01-01 2011-03-20
4 2I 728 1471 2011-03-22 2011-04-17
5 2I 728 1471 2011-04-20 2011-04-21
6 2I 728 1471 2011-04-25 2011-04-30
7 2I 728 1471 2011-05-02 2011-05-16
8 2I 728 1471 2011-05-18 2011-07-03
9 2I 728 1471 2011-07-09 2011-07-10
10 2I 728 1471 2011-07-16 2011-07-17
11 2I 728 1471 2011-07-23 2011-07-24
12 2I 728 1471 2011-07-30 2011-07-31
13 2I 728 1471 2011-08-06 2011-08-07
14 2I 728 1471 2011-08-13 2011-10-14
15 1I 701 1471 2011-10-15 2011-10-15
16 2I 728 1471 2011-10-16 2011-10-21
17 1I 701 1471 2011-10-22 2011-10-22
18 2I 728 1471 2011-10-23 2011-10-28
19 1I 701 1471 2011-10-29 2011-10-29

"Line" 1 to 14 can conpress as follows
ra gr xno StartDate EndDate
2I 728 1471 2010-09-01 2011-10-14

-oOo-

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-10-21 : 09:53:28

You have now introduced gaps in your dates which even a basic reading of the query would tell you will not work.

Also, your sample data seems to consist mainly of ra = '2I', gr = '728' and xno = '1471'. If your real data has a similar distribution you may be best looking for a solution based on expections rather than a triangular join. If all else fails, just use a cursor.

Ubbe
Starting Member

14 Posts

Posted - 2011-10-21 : 10:04:12

I think I understand.
Thanks for your help Ifor.

-oOo-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources