SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Combining records having diff ID based on EndDate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tpratyush
Starting Member

India
17 Posts

Posted - 10/10/2013 :  08:57:42  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/10/2013 :  09:22:43  Show Profile  Reply with Quote
Tell us the rules for getting the required output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2013 :  09:25:14  Show Profile  Reply with Quote
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
Go to Top of Page

tpratyush
Starting Member

India
17 Posts

Posted - 10/10/2013 :  10:25:28  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2013 :  10:31:25  Show Profile  Reply with Quote
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
Go to Top of Page

tpratyush
Starting Member

India
17 Posts

Posted - 10/10/2013 :  11:13:30  Show Profile  Reply with Quote
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
Go to Top of Page

tpratyush
Starting Member

India
17 Posts

Posted - 10/10/2013 :  11:14:27  Show Profile  Reply with Quote
In desired output the EmpNo will also be there.. mistakenly didnt print that.

Thanks and Regards,
Pratyush Tripathy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2013 :  11:30:34  Show Profile  Reply with Quote
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
Go to Top of Page

tpratyush
Starting Member

India
17 Posts

Posted - 10/10/2013 :  11:33:48  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2013 :  14:49:41  Show Profile  Reply with Quote
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
Go to Top of Page

tpratyush
Starting Member

India
17 Posts

Posted - 10/11/2013 :  04:57:25  Show Profile  Reply with Quote
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

Edited by - tpratyush on 10/11/2013 04:58:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2013 :  08:21:08  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000