Group by Query?? - 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

SQL Server Development (2000)

Group by Query??

Author

Topic

erng
Starting Member

2 Posts

Posted - 2003-12-24 : 01:08:28

I have problem about group by query ... I have source data as follow:
Date Gap
01/01/2003 0.1
01/02/2003 0.1
01/03/2003 0.1
01/04/2003 0.2
01/05/2003 0.2
01/06/2003 0.1
01/07/2003 0.1

Result data that I need are as follow:
Start date End date Gap
01/01/2003 01/03/2003 0.1
01/04/2003 01/05/2003 0.2
01/06/2003 01/07/2003 0.1

I try to use group by Gap but it not return as I need ... Please suggest me the correct way that I should do....

Thks , Erng

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-24 : 03:21:06

Does this work for you?

SELECT MIN([Date]) AS StartDate, MAX([Date]) AS EndDate, Gap
FROM GapTable
GROUP BY Gap

Owais

We make a living out of what we get, but we make a life out of what we give.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-12-24 : 03:40:29

Owais, I looked at that answer as well, but you see, Erng wants the "seconds set" of 0.1 to by a different group, hence his 3rd row in required outputs.

Your query would give
01/01/2003 01/07/2003 0.1
01/04/2003 01/05/2003 0.2

To be honest, unless there is more data on how to differentiate between the 1st set of 0.1 and the second set of 0.1 data, I'm not sure how to go forwards. I suspect Page or someone will come forward with a clever way of create another table, allocating an identifying column to the initial data, so that grouping by would work.

If we could get The original data to the following format, for example

Date Gap
01/01/2003 0.1 a
01/02/2003 0.1 a
01/03/2003 0.1 a
01/04/2003 0.2 b
01/05/2003 0.2 b
01/06/2003 0.1 c
01/07/2003 0.1 c

We could use the a,b,c to differentiate when the gaps are the same ...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-12-24 : 04:28:15

I can't see a straightforward set based solution, but something like this would do the job (assuming the source table is called t1)

-- Variables
DECLARE @Source TABLE (rank INT IDENTITY, date DATETIME, gap DECIMAL(4,1))
DECLARE @Results TABLE (startdate DATETIME, enddate DATETIME, gap DECIMAL(4,1))
DECLARE @Rank INT, @Rows INT, @Date DATETIME, @LastDate DATETIME, @Gap DECIMAL(4,1), @CurrentGap DECIMAL(4,1)
SELECT @Rank = 1, @CurrentGap = 0

-- Setup our source in the correct date order
INSERT INTO @Source SELECT date, gap FROM t1 ORDER BY date
SET @Rows = @@ROWCOUNT

-- Loop solution
WHILE @Rank <= @Rows
BEGIN
SELECT @Date = date, @Gap = gap FROM @Source WHERE rank = @Rank
IF @Gap <> @CurrentGap
BEGIN
UPDATE @Results SET enddate = @LastDate WHERE enddate IS NULL
INSERT INTO @Results (startdate, gap) VALUES (@Date, @Gap)
END

SELECT @Rank = @Rank + 1, @CurrentGap = @Gap, @LastDate = @Date
END
UPDATE @Results SET enddate = @LastDate WHERE enddate IS NULL

SELECT * FROM @Results

Raymond

erng
Starting Member

2 Posts

Posted - 2003-12-24 : 05:24:10

Chaos, it unfortunatly that this data is dynamic data and it will change at run time so I can not specific column to make it unique ... but I agree with you that if it has it will help... I will try to work on Raymond solution... and will let you know result.... Thanks you all

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-24 : 07:20:04

doh...i feel soooo dumb! Like Bugs Bunny would have said "What a maroon!!"

ernq, check out Jeff's excellent article on detecting streaks in your data, it should help you get some ideas: http://www.sqlteam.com/item.asp?ItemID=12654

Owais

We make a living out of what we get, but we make a life out of what we give.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources