need help in sql 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

General SQL Server Forums

New to SQL Server Programming

need help in sql query

Author

Topic

bong7890
Starting Member

3 Posts

Posted - 2012-05-20 : 00:15:47

I have a table in Sql2008 like this
----------------------
ID FirstServiceDate LastServicedate
1 1-12-81 1-15-81
1 1-18-81 1-18-81
1 2-9-81 3-1-81
2 5-2-81 5-5-81
2 8-18-81 8-18-81
2 9-9-81 10-21-81
3 3-12-81 4-15-81
3 7-17-81 8-19-81
------------------------
I need to find the minimum service date and maximum service date for each id,under the condition that the gap between each service period is less than 90 days

-------
So the result I am expecting is something like this

-----------
ID Mindate MaxDate
1 1-12-81 3-1-81
2 8-18-81 10-21-81
3 7-17-81 8-19-81

------------

thanks in advance ..

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-20 : 02:17:19

[code]

SELECT ID,MIN(MINDATE) MINDATE,MAX(MAXDATE) MAXDATE
FROM D GROUP BY ID HAVING DATEDIFF(DAY,MIN(MINDATE),MAX(MAXDATE))<90

[/code]

Vijay is here to learn something from you guys.

bong7890
Starting Member

3 Posts

Posted - 2012-05-20 : 09:59:57

Thanks vijay,
It didn't worked got the following result
----------
id mindate maxdate
1 1981-01-12 1981-01-18

-----------

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-20 : 10:41:34

quote:
Originally posted by bong7890

Thanks vijay,
It didn't worked got the following result
----------
id mindate maxdate
1 1981-01-12 1981-01-18

-----------



My query is giving result as per your requirement you want id for which date difference should be less than 90 .For that it is working fine.This query finds Minimum date from mindate column and maximum date from maxdate column ,and check the difference of days for each ID..

Check this out also
select id,MINDATE,MAXDATE,DATEDIFF(DAY,MINDATE,MAXDATE)from 
(SELECT ID,MIN(MINDATE) MINDATE,MAX(MAXDATE) MAXDATE 
 FROM D GROUP BY ID ) Tab

Looking at your required output how do u want to be calculated ,not clear.

Vijay is here to learn something from you guys.

bong7890
Starting Member

3 Posts

Posted - 2012-05-20 : 10:52:31

Thanks vijay..
It was my fault in putting the question. I edited my requirenet . Can you please have a look..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources