January 2, 2003 at 1:32 pm
Hello!
I need help with SQL Query where ever there are breaks in the date it should insert a record into the table.
For example, according to the table below for the product_Code "REL03H/SECSTD" and Order_Num "100" the first row should have Begin_date = 2/9/2003 and End_Date = 2/11/2003
And Second row should have dates as 2/12/2003 and 2/14/2003.
So, for order_Num 100 I should insert two records and for 5 records.
TABLE:
Product_Code,Order_Num,Begin_Date,End_Date
REL03H/SECSTD,100,2/9/2003 15:00,2/10/2003 15:00
REL03H/SECSTD,100,2/10/2003 15:00,2/11/2003 15:00
REL03H/SECSTD,100,2/12/2003 15:00,2/13/2003 15:00
REL03H/SECSTD,100,2/13/2003 15:00,2/14/2003 15:00
REL03H/SECSTD,104,2/6/2003 15:00,2/7/2003 15:00
REL03H/SECSTD,104,2/7/2003 15:00,2/8/2003 15:00
REL03H/SECSTD,104,2/9/2003 15:00,2/10/2003 15:00
REL03H/SECSTD,104,2/10/2003 15:00,2/11/2003 15:00
REL03H/SECSTD,104,2/15/2003 15:00,2/16/2003 15:00
REL03H/SECSTD,104,2/16/2003 15:00,2/17/2003 15:00
REL03H/SECSTD,104,2/17/2003 15:00,2/18/2003 15:00
REL03H/SECSTD,104,2/28/2003 15:00,3/1/2003 15:00
REL03H/SECSTD,104,3/3/2003 15:00,3/4/2003 15:00
REL03H/SECSTD,104,3/4/2003 15:00,2/5/2003 15:00
ThankYou,
Mkumari
January 2, 2003 at 1:51 pm
That isn't quite clear. Can you be a little more specific in what you are looking for? Is this in insert statement you need help with or a select?
Steve Jones
January 2, 2003 at 2:20 pm
I want to merger the records and then insert them into another table.
REL03H/SECSTD,100,2/9/2003 15:00,2/10/2003 15:00
REL03H/SECSTD,100,2/10/2003 15:00,2/11/2003 15:00
REL03H/SECSTD,100,2/12/2003 15:00,2/13/2003 15:00
REL03H/SECSTD,100,2/13/2003 15:00,2/14/2003 15:00
The two records should get mergerd into one record and look like this,since they have consequitive dates.
REL03H/SECSTD,100,2/9/2003 15:00,2/11/2003 15:00
REL03H/SECSTD,100,2/12/2003 15:00,2/14/2003 15:00
So whenever there is a break in the begin_date for a particular order_num it should get inserted as a new record.In this case there is no begin_date with 2/11/2003 which is considered as a break.
Thanks,
Mkumari
January 3, 2003 at 3:37 am
select a.product_Code,a.Order_Num,a.Begin_date,b.End_Date
from
a
inner join
b on b.product_Code = a.product_Code
and b.Order_Num = a.Order_Num
and b.Begin_date = a.End_Date
Edited by - davidburrows on 01/03/2003 03:38:12 AM
Far away is close at hand in the images of elsewhere.
Anon.
January 7, 2003 at 12:58 am
I don't think the solution from David solves this one. It will work, if you never have more than two records that need to be merged. It also won't take records without a consecutive record into account.
My personal first approach would be to put this logic in a cursor. Easiest to program, but not really good for performance of course.
A set based approach is more difficult. Maybe you can get away with the following logic :
So for the temp table (#TEMPTABLE)
SELECT P1.Product_Code, P1.Order_Num, P1.End_Date
FROM TABLE P1 LEFT OUTER JOIN TABLE P2
ON P1.Product_Code = P2.Product_Code
AND P1.Order_Num = P2.Order_Num
P1.End_Date = P2.Begin_Date
WHERE P2.Product_Code IS NULL
And then the join of both tables
SELECT P.Product_Code, P.Order_Num,
P.Begin_Date,
(SELECT min(T.End_Date)
FROM #TEMPTABLE
WHERE P.Product_Code = T.Product_Code
AND P.Order_Num = T.Order_Num
AND P.Begin_Date < T.End_Date)
FROM TABLE P
This solution will still give you too much records. You can solve this, by building a second temp table that holds the Begin_Date of all records that have no matching previous record.
PS: Long post. I hope you get the idea, and there is a lot of room for improving the query syntax.
Edited by - NPeeters on 01/07/2003 12:59:29 AM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply