Insert based on a date condition

Question

Post reply

Insert based on a date condition

Stu-635214

Old Hand

Points: 313
More actions
July 28, 2008 at 7:51 am

#70479

Hi All,
Does anyone know of a way to insert records into one table from another table based on the difference in dates between two related records in the different tables?
What i have is a table which holds records of positive bacteria tests. Which looks like like this
tableEpisodes
ID Source DateSampleTaken
1 Source1 21/06/2008
2 Source2 21/06/2008
I also recieve a download from an organisation with all the samples taken which are positive like the following
tablePositiveSamples
ID Source DateSampleTaken
1 Source1 22/06/2008
2 Source1 24/06/2008
3 Source1 27/07/2008
4 Source2 23/06/2008
The problem is that i only want to insert records from this second table if the date difference between the related record in the first is more than 28 days?? So in the above example the only record i would want to insert would be row 3 as samples taken from source1 in rows 1 and 2 are less than 28 days from the sample recorded in tableEpisodes.
Hope this makes sense to someone.
Any help greatly appreciated

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I think this does what you need (I set dateformat because my machine is not set to dmy):

[font="Courier New"]SET DateFormat dmy

DECLARE @episodes TABLE(id INT, Source VARCHAR(20), DateSampleTaken DATETIME)

DECLARE @PositiveSamples TABLE(id INT, Source VARCHAR(20), DateSampleTaken DATETIME)

INSERT INTO @episodes

SELECT

1, 'Source1', '21/06/2008'

UNION ALL

SELECT

2, 'Source2', '21/06/2008'

INSERT INTO @PositiveSamples

SELECT

1 , 'Source1' , '22/06/2008'

UNION ALL

SELECT

2 ,'Source1' , '24/06/2008'

UNION ALL

SELECT

3 ,'Source1' , '27/07/2008'

UNION ALL

SELECT

4 ,'Source2', '23/06/2008'

INSERT INTO @episodes

SELECT

P.*

FROM

@episodes E JOIN

@PositiveSamples P ON

E.Source = P.Source AND

E.DateSampleTaken <= DATEADD(DAY, -28, P.DateSampleTaken)

SELECT * FROM @episodes[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Stu-635214 Old Hand Points: 313 More actions · Answer 2

Great - thanks for that. There is just one thing, any idea how you would go about checking the difference in days just from the most recent record in the episodes table. as this table will become a fair size and will include multiple recordings for the same source across the year

So if there are two records in the episodes table like this

ID Source DateSampleTaken

1 source1 01/01/2008

2 source1 10/02/2008

and then a record in the positiveSamples table like this

ID Source DateSampleTaken

1 source1 11/02/2008

this record should not be inserted into the episodes table as it is less than 28 days after the most recent record in the episodes table.

Any Ideas?

Thanks for any help.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

You will want to use a derived table in the FROM clause that gets the pertinent information from the episodes table in place of the episodes table. In this case Source and Max(DateSampleTaken) grouped by Source. Then use the existing join to PositiveSamples. I'll could provide you the code, but I think you can figure it out.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Stu-635214 Old Hand Points: 313 More actions · Answer 4

Jack - Will go and give this a try. Once again thanks for all your help!