TSQL QUERY To find satrt n enddate in given dates

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 2005 Forums

Transact-SQL (2005)

TSQL QUERY To find satrt n enddate in given dates

Author

Topic

komal403
Starting Member

7 Posts

Posted - 2010-04-01 : 12:02:37

my table
ActId name startdate
1 x 03/1/2010
1 x 03/20/2010
1 x 03/23/2010
1 x 03/24/2010
1 y 03/25/2010
1 y 03/26/2010
1 x 03/27/2010

my result set should be like this

actid name startdate endadte
1 x 03/1/2010 03/24/2010
1 y 03/25/2010 03/26/2010
1 x 03/27/2010 null

i have tried min n max of dates but not getting...

komal

PackRat
Starting Member

26 Posts

Posted - 2010-04-01 : 12:27:40

need a bit more explanation of how you arrive at the result set, unless there's a typo and that last line in [my table] should read 1 z 3/27/2010


actid name startdate endadte
1 x 03/1/2010 03/24/2010
1 y 03/25/2010 03/26/2010
1 x 03/27/2010 null

Does x have multiple ranges or is it supposed to be 'z'?

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 12:31:52

[code]SELECT ,ActId,name,MIN(startdate) AS startdate,MAX(startdate) AS enddate
FROM Table t
OUTER APPLY (SELECT MIN(startdate) AS dateval
FROM Table
WHERE ActId=t.ActId
AND name <> t.name
AND startdate > t.startdate)t1
GROUP BY ActId,name,t1.dateval
[/code]

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

komal403
Starting Member

7 Posts

Posted - 2010-04-01 : 12:37:31

It should take only latest value of x as end date ..once it appears y it takes start n enddate of y , n then x....

It is x ..not z

komal

PackRat
Starting Member

26 Posts

Posted - 2010-04-01 : 12:39:50

ok I begin to understand; didn't see the pattern at first, sorry.

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 12:47:56

quote:
Originally posted by komal403

It should take only latest value of x as end date ..once it appears y it takes start n enddate of y , n then x....

It is x ..not z

komal

did you try my suggestion?

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

PackRat
Starting Member

26 Posts

Posted - 2010-04-01 : 12:48:56

think he was responding to a post I put up and took down because i'd misinterpreted the question; didn't grok the sequence at first.

nice solution btw, clever use of APPLY

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 12:57:47

quote:
Originally posted by PackRat

think he was responding to a post I put up and took down because i'd misinterpreted the question; didn't grok the sequence at first.

nice solution btw, clever use of APPLY

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

Oh ..Ok..I see

Thanks

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

komal403
Starting Member

7 Posts

Posted - 2010-04-01 : 12:57:58

Hi Vishak...
Thank you...I tried the query you gave me.Its working.

komal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 13:03:53

welcome

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

komal403
Starting Member

7 Posts

Posted - 2010-04-01 : 13:04:30

quote:

Originally posted by visakh16

SELECT ,ActId,name,MIN(startdate) AS startdate,MAX(startdate) AS enddate
FROM Table t
OUTER APPLY (SELECT MIN(startdate) AS dateval
             FROM Table
             WHERE ActId=t.ActId
             AND name <> t.name
             AND startdate > t.startdate)t1
GROUP BY ActId,name,t1.dateval

And.can u explain how outer apply work?
I am new to sql server..If you dont mind...can u just tell me..it would be great help

komal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 13:09:46

well..here you go..
for every column in your table. outer apply fetches the minimum value of date which has a different value of name for same ActID ( so for a grouped occurance of ActId,name it will be always same). then i apply group by over the acdid,name,date value which will help me in separating out your grouped occurances and then apply min() ,max() over dates to get the minimum and maximum value of date within the occurance.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 13:10:25

also see what all you can do with apply operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

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

komal403
Starting Member

7 Posts

Posted - 2010-04-01 : 13:22:39

quote:
Originally posted by visakh16

also see what all you can do with apply operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

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

Hi Vishak...

Thanks a lot

komal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 13:29:45

welcome always

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources