finding records and more - 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 2005 Forums

Transact-SQL (2005)

finding records and more

Author

Topic

SQL_Rookie
Starting Member

32 Posts

Posted - 2010-01-12 : 17:34:58

I have table like this
ID num date_beg date_end
1 6757 7303 4/27/2009 12/6/2009
2 8067 7105 12/8/2008 3/1/2009
3 8067 7105 3/16/2009 12/6/2009
4 8067 7107 12/8/2008 3/1/2009
5 8067 7107 3/16/2009 12/6/2009
6 8064 7108 12/8/2008 3/1/2009
7 8064 7108 3/2/2009 12/6/2009

1. I need to find records that have the same id and num but their date may not be in sequence? ie. row 2 and 3, row 4 and 5

2. I need to find records that have the same id and num but their dates are in sequence? ie. row 6 and 7

3. I can I get the dates for one ID and num to be on the same row?
like row 6 would be
8064 7108 12/8/2008 3/1/2009 3/2/2009 12/6/2009

4. Calc the days missing (datediff?) from on date to the other date within the same id and num? Some rows would have any days miss those records would have continues date from one row to the next row with the same id and num that is.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-12 : 18:54:02

what is the final result you would like to see from that data example you gave?


ID num       date_beg  date_end
1  6757 7303 4/27/2009 12/6/2009
2  8067 7105 12/8/2008 3/1/2009
3  8067 7105 3/16/2009 12/6/2009
4  8067 7107 12/8/2008 3/1/2009
5  8067 7107 3/16/2009 12/6/2009
6  8064 7108 12/8/2008 3/1/2009
7  8064 7108 3/2/2009 12/6/2009

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

SQL_Rookie
Starting Member

32 Posts

Posted - 2010-01-12 : 19:06:09

Result set like
ROw ID num date_beg date_end date_beg2 date_end2 status NUmDays
1 6757 7303 4/27/2009 12/6/2009 GOOD 0
2 8067 7105 12/8/2008 3/1/2009 3/16/2009 12/6/2009 bad 15
4 8067 7107 12/8/2008 3/1/2009 3/16/2009 12/6/2009 bad 15
6 8064 7108 12/8/2008 3/1/2009 3/2/2009 12/6/2009 GOOD 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 01:43:58

[code]SELECT t.*,t1.date_beg,t1.date_end,
CASE WHEN DATEDIFF(dd,t.date_end,t1.date_beg) =1 OR t1.date_beg IS NULL THEN 'GOOD' ELSE 'Bad' END AS Status,
CASE WHEN DATEDIFF(dd,t.date_end,t1.date_beg) =1 OR t1.date_beg IS NULL THEN 0 ELSE DATEDIFF(dd,t.date_end,t1.date_beg) END AS NumDays
FROM table t
OUTER APPLY (SELECT TOP 1 date_beg,date_end
FROM table
WHERE ID = t.ID
AND num = t.num
AND date_beg > t.date_end
ORDER BY date_beg) t1
[/code]

SQL_Rookie
Starting Member

32 Posts

Posted - 2010-01-13 : 07:57:03

thanks for the reply..

I think I'm needing a cursor to step thru the table since I don't know how many times the id and num repeat for given days above a I gave examples of 2 rows but I have upto 5 rows with the same id and num.

and when I use the example code above I only get 2 start and 2 end dates on the result set which indicate there was 2 rows from the beginning I need to display upto 5 start and 5 end dates.

also is there a way no show the subsequent rows that have the same id and num cause I would like to display one row with id and num with all the dates on that same row and if there is any break in days (start date must be date following the previous end date) the status would be BAD.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 07:59:13

quote:
Originally posted by SQL_Rookie

thanks for the reply..

I think I'm needing a cursor to step thru the table since I don't know how many times the id and num repeat for given days above a I gave examples of 2 rows but I have upto 5 rows with the same id and num.

and when I use the example code above I only get 2 start and 2 end dates on the result set which indicate there was 2 rows from the beginning I need to display upto 5 start and 5 end dates.

also is there a way no show the subsequent rows that have the same id and num cause I would like to display one row with id and num with all the dates on that same row and if there is any break in days (start date must be date following the previous end date) the status would be BAD.

ok in that case i think you need to go for dynamic crosstab. see below

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

SQL_Rookie
Starting Member

32 Posts

Posted - 2010-01-13 : 08:29:12

well I thought I could use the pivot but I really can't cause I can't really pivot on the dates since it would create a pivot column for each date found in the date_beg and date_end
data looks like
ID num date_beg date_end
1 6757 7303 4/27/2009 12/6/2009
2 8067 7105 12/8/2008 3/1/2009
3 8067 7105 3/16/2009 12/6/2009

but I want it like
1 6757 7303 4/27/2009 12/6/2009
2 8067 7105 12/8/2008 3/1/2009 3/16/2009 12/6/2009

if I pivot on date_beg and date_end my column headings would be the date values and some id and num don't have the same date_beg values...

or I'm I just way off and confused?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 08:33:43

Ok.. Is it ok to show the dates comma separated in a field then?

SQL_Rookie
Starting Member

32 Posts

Posted - 2010-01-13 : 08:54:49

I guess....would I be able to split it out later...
Like if I want to export it out to a file I'm wondering if I can exprt it out to different columns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 08:57:33

quote:
Originally posted by SQL_Rookie

I guess....would I be able to split it out later...
Like if I want to export it out to a file I'm wondering if I can exprt it out to different columns

you can do that using a string parsing udf

SQL_Rookie
Starting Member

32 Posts

Posted - 2010-01-13 : 09:06:26

ok so how do I get them in the same column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 09:17:37

quote:
Originally posted by SQL_Rookie

ok so how do I get them in the same column?

see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources