May 18, 2011 at 5:04 am
Hi Folks,
I have a problem T SQL . Can you Please help me to design a T SQL for this problem ,I have a table with the following records and and I want to get the result given as under :
Table:-
idattribbegda endda
11003/10/20103/31/2010
11004/1/2010 4/10/2010
124/11/20105/15/2010
135/16/20107/10/2010
11154/11/20101/1/2011
11151/2/2011 2/7/2011
11152/8/2011 2/25/2011
11002/26/20113/7/2011
11153/8/2011 3/10/2011
11003/11/20113/20/2011
153/21/20113/28/2011
125/16/20104/4/2011
Result should be :-
idattribbegda endda
124/11/20105/15/2010
125/16/20104/4/2011
135/16/20107/10/2010
153/21/20113/28/2011
11003/10/20104/10/2010
11154/11/20102/25/2011
11002/26/20113/7/2011
11153/8/2011 3/10/2011
11003/11/20113/20/2011
Please let me know if you have any query on this.
Ashok
May 18, 2011 at 5:11 am
can you tell us what is the relation between the rows...any constraints or is each row just a random combination?
May 18, 2011 at 5:19 am
thanks for the reply, there is relation between the rows like attrib 100 and 115 is in a group and endda of one, will be begda + 1 (day) for other if there comes next records in the table.this is just sample data, there can be othere attrib in the same group and that is maintained in a different table. same is case with other attrib, Resolution of this problem wil be highly appreciated..
Ashok
May 18, 2011 at 5:32 am
First I would create the initial table with the following script:
CREATE TABLE X( id INT, attrib INT ,begda DATETIME ,endda DATETIME)
INSERT INTO X ( id,attrib,begda,endda )
SELECT 1, 100, '3/10/2010', '3/31/2010'
UNION ALL
SELECT 1, 100, '4/1/2010', '4/10/2010'
UNION ALL
SELECT 1, 2, '4/11/2010', '5/15/2010'
UNION ALL
SELECT 1, 3, '5/16/2010', '7/10/2010'
UNION ALL
SELECT 1, 115, '4/11/2010', '1/1/2011'
UNION ALL
SELECT 1, 115, '1/2/2011', '2/7/2011'
UNION ALL
SELECT 1, 115, '2/8/2011', '2/25/2011'
UNION ALL
SELECT 1, 100, '2/26/2011', '3/7/2011'
UNION ALL
SELECT 1, 115, '3/8/2011', '3/10/2011'
UNION ALL
SELECT 1, 100, '3/11/2011', '3/20/2011'
UNION ALL
SELECT 1, 5, '3/21/2011', '3/28/2011'
UNION ALL
SELECT 1, 2, '5/16/2010', '4/4/2011'
SELECT * FROM X
May 18, 2011 at 5:36 am
--Sample data--
DECLARE @TABLE AS TABLE(id INT, attrib INT, begda VARCHAR(10), endda VARCHAR(10))
INSERT INTO @TABLE
SELECT 1, 100, '3/10/2010', '3/31/2010'
UNION ALL SELECT 1, 100, '4/1/2010', '4/10/2010'
UNION ALL SELECT 1, 2, '4/11/2010', '5/15/2010'
UNION ALL SELECT 1, 3, '5/16/2010', '7/10/2010'
UNION ALL SELECT 1, 115, '4/11/2010', '1/1/2011'
UNION ALL SELECT 1, 115, '1/2/2011', '2/7/2011'
UNION ALL SELECT 1, 115, '2/8/2011', '2/25/2011'
UNION ALL SELECT 1, 100, '2/26/2011', '3/7/2011'
UNION ALL SELECT 1, 115, '3/8/2011', '3/10/2011'
UNION ALL SELECT 1, 100, '3/11/2011', '3/20/2011'
UNION ALL SELECT 1, 5, '3/21/2011', '3/28/2011'
UNION ALL SELECT 1, 2, '5/16/2010', '4/4/2011'
Don't understand your ordering for the results. Please explain.
e.g. Ordering by the "begda" results in this: -
SELECT * FROM @TABLE
ORDER BY CONVERT(DATETIME,begda,101)
/*
id attrib begda endda
----------- ----------- ---------- ----------
1 100 3/10/2010 3/31/2010
1 100 4/1/2010 4/10/2010
1 2 4/11/2010 5/15/2010
1 115 4/11/2010 1/1/2011
1 3 5/16/2010 7/10/2010
1 2 5/16/2010 4/4/2011
1 115 1/2/2011 2/7/2011
1 115 2/8/2011 2/25/2011
1 100 2/26/2011 3/7/2011
1 115 3/8/2011 3/10/2011
1 100 3/11/2011 3/20/2011
1 5 3/21/2011 3/28/2011
*/
Ordering by attrib then begda is closest to your requirement: -
SELECT * FROM @TABLE
ORDER BY attrib, CONVERT(DATETIME,begda,101)
/*
id attrib begda endda
----------- ----------- ---------- ----------
1 2 4/11/2010 5/15/2010
1 2 5/16/2010 4/4/2011
1 3 5/16/2010 7/10/2010
1 5 3/21/2011 3/28/2011
1 100 3/10/2010 3/31/2010
1 100 4/1/2010 4/10/2010
1 100 2/26/2011 3/7/2011
1 100 3/11/2011 3/20/2011
1 115 4/11/2010 1/1/2011
1 115 1/2/2011 2/7/2011
1 115 2/8/2011 2/25/2011
1 115 3/8/2011 3/10/2011
*/
What are the rules for your ordering?
May 18, 2011 at 5:52 am
🙁 Hi, my problem is simple, if you go through my replies, i hope you got the problem and now..
logic to obtain the result is if the attrib is repeating in rows continously then only single record will be there in the result for that attrib and begda would be of initial record of that attrib and endda would be from that row after which attrib get chaged in the next row. like ..
id attrib begda endda
124/11/20105/15/2010
125/16/20104/4/2011
135/16/20107/10/2010
153/21/20113/28/2011
11003/10/20104/10/2010
11154/11/20102/25/2011
11002/26/20113/7/2011
11153/8/2011 3/10/2011
11003/11/20113/20/2011
and records are sorted on the basis of attrib ....
May 18, 2011 at 5:54 am
Hi,
usage of cursor is avoided...
May 18, 2011 at 6:14 am
It looks very much like this:
SELECT * FROM X
ORDER BY
LEN(attrib) ASC ,
begda,
DATEDIFF(DAY, begda, endda) desc
but why are 3 rows left outside from the result set
(
1 1004/1/20104/10/20109
1 1151/2/20112/7/201136
1 1152/8/20112/25/201117
)
Regards,
Iulian
May 18, 2011 at 6:14 am
ashok.faridabad1984 (5/18/2011)
🙁 Hi, my problem is simple, if you go through my replies, i hope you got the problem and now..logic to obtain the result is if the attrib is repeating in rows continously then only single record will be there in the result for that attrib and begda would be of initial record of that attrib and endda would be from that row after which attrib get chaged in the next row. like ..
id attrib begda endda
124/11/20105/15/2010
125/16/20104/4/2011
135/16/20107/10/2010
153/21/20113/28/2011
11003/10/20104/10/2010
11154/11/20102/25/2011
11002/26/20113/7/2011
11153/8/2011 3/10/2011
11003/11/20113/20/2011
and records are sorted on the basis of attrib ....
I think we're having a language barrier problem, because I still don't understand.
Can you show us what you've tried?
May 18, 2011 at 6:25 am
Here is the full script:
CREATE TABLE #X( id INT, attrib INT ,begda DATETIME ,endda DATETIME)
INSERT INTO #X ( id,attrib,begda,endda )
SELECT 1, 100, '3/10/2010', '3/31/2010'
UNION ALL SELECT 1, 100, '4/1/2010', '4/10/2010'
UNION ALL SELECT 1, 2, '4/11/2010', '5/15/2010'
UNION ALL SELECT 1, 3, '5/16/2010', '7/10/2010'
UNION ALL SELECT 1, 115, '4/11/2010', '1/1/2011'
UNION ALL SELECT 1, 115, '1/2/2011', '2/7/2011'
UNION ALL SELECT 1, 115, '2/8/2011', '2/25/2011'
UNION ALL SELECT 1, 100, '2/26/2011', '3/7/2011'
UNION ALL SELECT 1, 115, '3/8/2011', '3/10/2011'
UNION ALL SELECT 1, 100, '3/11/2011', '3/20/2011'
UNION ALL SELECT 1, 5, '3/21/2011', '3/28/2011'
UNION ALL SELECT 1, 2, '5/16/2010', '4/4/2011'
SELECT * FROM #X
ORDER BY
LEN(attrib) ASC ,
begda ASC,
DATEDIFF(DAY, begda, endda) DESC
DROP TABLE #X
but I get 12 records while your sample result shows only 9 records
the 3 records that are missing are:
1 100 4/1/2010 4/10/2010 9
1 115 1/2/2011 2/7/2011 36
1 115 2/8/2011 2/25/2011 17
so I guess a where clause to exclude the 3 records would solve the problem. But I am not sure what is the condition there.
Regards,
Iulian
May 18, 2011 at 6:32 am
Hi lulian,
Thanks for your efforts, the rows left outside from the result because :-
for attrib 100 begda should be 3/10/2010 and endda should be 4/10/2010 in first two records of the table
for both two records
1 100 3/10/2010 3/31/2010
1 100 4/1/2010 4/10/2010
in result record should be
1 100 3/10/2010 4/10/2010
for attrib 100 begda should be 3/10/2010 and endda should be 4/10/2010
and for below three records
1 115 4/11/2010 1/1/2011
1 115 1/2/2011 2/7/2011
1 115 2/8/2011 2/25/2011
in result record should be
1 115 4/11/2010 2/25/2011
logic behind it attrib is repeating.
May 18, 2011 at 6:34 am
Hi lulian,
Thanks for your efforts, the three rows left outside from the result because :-
for attrib 100 begda should be 3/10/2010 and endda should be 4/10/2010 for first two records in the table.
for both two records
1 100 3/10/2010 3/31/2010
1 100 4/1/2010 4/10/2010
in result record should be
1 100 3/10/2010 4/10/2010
for attrib 100 begda should be 3/10/2010 and endda should be 4/10/2010
and for below three records
1 115 4/11/2010 1/1/2011
1 115 1/2/2011 2/7/2011
1 115 2/8/2011 2/25/2011
in result record should be
1 115 4/11/2010 2/25/2011
logic behind it attrib is repeating ..
May 18, 2011 at 1:12 pm
I think I have got it,
so if they are consecutive records with the same attrib it should combine them and result one single record with begda the begining of the period and the endda the end of the period. Is that right?
Regards,
Iulian
May 18, 2011 at 2:06 pm
If
1 100 3/10/2010 3/31/2010
1 100 4/1/2010 4/10/2010
are merged into
1 100 3/10/2010 4/10/2010
Why aren’t
1 2 4/11/2010 5/15/2010
1 2 5/16/2010 4/4/2011
Merged into
1 2 4/11/2010 4/4/2011
May 18, 2011 at 10:08 pm
Hi lulian,
You are absolutely understood the problem, Please go ahead and may you get the success and don't forget, cursor usage is not allowed.
Regards,
Ashok
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply