Combining records having diff ID based on EndDate

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

Transact-SQL (2008)

Combining records having diff ID based on EndDate

Author

Topic

tpratyush
Starting Member

17 Posts

Posted - 2013-10-10 : 08:57:42

Input Table:
ID Start Date End Date Pattern12345-1 4/5/2012 4/4/2013 A1
12345-1 4/5/2012 4/4/2013 NULL
12345-1 4/5/2012 4/4/2013 B1
12345-2 4/5/2012 4/4/2013 A1
12345-2 4/5/2012 4/4/2013 NULL
12345-2 4/5/2012 4/4/2013 B1
12345-3 4/5/2013 2/1/2014 A1
12345-3 4/5/2013 2/1/2014 NULL
12345-3 4/5/2013 2/1/2014 B1

Expected output:
ID Start Date End Date Pattern
12345-1 4/5/2012 4/4/2013 A1
12345-1 4/5/2012 4/4/2013 NULL
12345-1 4/5/2012 4/4/2013 B1
12345-3 4/5/2013 2/1/2014 A1
12345-3 4/5/2013 2/1/2014 NULL
12345-3 4/5/2013 2/1/2014 B1

Can anyone please provide me a query to do this? Do I really need to use a cursor as there are plenty of other fields alongwith these four? Or a simple query can suffice my need?

Thanks and Regards,
Pratyush Tripathy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 09:22:43

Tell us the rules for getting the required output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 09:25:14

may be this?


SELECT MIN(ID) AS ID,[Start Date],[End Date],[Pattern]
FROM Table
GROUP BY [Start Date],[End Date],[Pattern]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tpratyush
Starting Member

17 Posts

Posted - 2013-10-10 : 10:25:28

No. It wont work here. Since I want a generalised query. The query u offered, works fine for this record only. But when I have plenty of cases like these? It wont work. And Group By I think wont work for a larger number of columns(46 columns I have).

Thanks and Regards,
Pratyush Tripathy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 10:31:25

quote:
Originally posted by tpratyush

No. It wont work here. Since I want a generalised query. The query u offered, works fine for this record only. But when I have plenty of cases like these? It wont work. And Group By I think wont work for a larger number of columns(46 columns I have).

Thanks and Regards,
Pratyush Tripathy

then show us what all are your record scenarios? without that we can only provide solution for the info that you provided.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tpratyush
Starting Member

17 Posts

Posted - 2013-10-10 : 11:13:30

ID Emp No. C_StartDate C_EndDate Pattern
12345-1 561597701 4/5/2012 2/1/2013 A1
12345-1 561597701 4/5/2012 2/1/2013 NULL
12345-1 561597701 4/5/2012 2/1/2013 B1
12345-2 561597701 2/2/2013 4/4/2013 A1
12345-2 561597701 2/2/2013 4/4/2013 NULL
12345-2 561597701 2/2/2013 4/4/2013 B1
12345-3 561597701 4/5/2013 2/1/2014 A1
12345-3 561597701 4/5/2013 2/1/2014 NULL
12345-3 561597701 4/5/2013 2/1/2014 B1

Output:
ID C_StartDate C_EndDate Pattern
12345-1 4/5/2012 4/4/2013 A1
12345-1 4/5/2012 4/4/2013 NULL
12345-1 4/5/2012 4/4/2013 B1
12345-3 4/5/2013 2/1/2014 A1
12345-3 4/5/2013 2/1/2014 NULL
12345-3 4/5/2013 2/1/2014 B1

Can u provide me a query to achieve this?

Thanks and Regards,
Pratyush Tripathy

tpratyush
Starting Member

17 Posts

Posted - 2013-10-10 : 11:14:27

In desired output the EmpNo will also be there.. mistakenly didnt print that.

Thanks and Regards,
Pratyush Tripathy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 11:30:34

the date values are different for ID 12345-1 and 12345-2. Then how did you manage to merge them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tpratyush
Starting Member

17 Posts

Posted - 2013-10-10 : 11:33:48

Exactly, I am trying to achieve a COMBINE. Not a MERGE.
If u see the records carefully, you will find that DATEDIFF between -1's EndDate and -2's StartDate = 1 .. So the records are continuous. and based on this DATEDIFF I am trying to Combine the records as well as delete the other record.. In this case, which is -2.

Thanks and Regards,
Pratyush Tripathy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 14:49:41

Then why didnt you merge -3 ids too? it starts from 4/5/2013 which is 1 day after end date of -2 (4/4/2013) right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tpratyush
Starting Member

17 Posts

Posted - 2013-10-11 : 04:57:25

I told you there are some other columns. I need to Combine the first two rows only and they will be having an "approved" status. The thrid one is having a "Pending" status. thats why it wont get combined. For ur convenience, u can add another column as Status and put values as

Approved
Approved
Approved
Approved
Approved
Approved
Pending
Pending
Pending

Respectively.

Thanks and Regards,
Pratyush Tripathy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 08:21:08

quote:
Originally posted by tpratyush

I told you there are some other columns. I need to Combine the first two rows only and they will be having an "approved" status. The thrid one is having a "Pending" status. thats why it wont get combined. For ur convenience, u can add another column as Status and put values as

Approved
Approved
Approved
Approved
Approved
Approved
Pending
Pending
Pending

Respectively.

Thanks and Regards,
Pratyush Tripathy

so how do you think we could guess this rule?
Please try to explain us the full scenario if you need to proper and accurate solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources