June 3, 2009 at 2:49 pm
Hi All,
I have 2 tables as follows
Table1
ID-----NO------------Edate---
50----001------------2008-07-15
Table2
ID-----Edate---
50----2008-01-15
50----2008-07-13
REQUIRED Result should be a table with coloum 3 in Table2.If a record exists in Table1 within 5 days of Edate in Table2 it should display that record else it should display the same Edate as 3rd cloum.
ID-----Edate-------Result
50----2008-01-15-- 2008-01-15
50----2008-07-13---2008-07-15
I am writing a query like this but this results in only one row ie if only a record exsists then its displaying elses it not:-
select Table2.Insured,Table2.EDOs,(select max(Table1.EDOs) from Table1,Table2
WHERE Table1.Insured=Table2.Insured
and(Table1.EDOs BETWEEN Table2.EDOs AND Table2.EDOs+5)
group by Table2.Insured,Table2.EDOs
MY RESULT:-
ID-----Edate-------Result
50----2008-01-15-- 2008-01-15
How can i do this.
Thanks
sh
June 3, 2009 at 3:04 pm
shipra20 (6/3/2009)
Hi All,I have 2 tables as follows
Table1
ID-----NO------------Edate---
50----001------------2008-07-15
Table2
ID-----Edate---
50----2008-01-15
50----2008-07-13
REQUIRED Result should be a table with coloum 3 in Table2.If a record exists in Table1 within 5 days of Edate in Table2 it should display that record else it should display the same Edate as 3rd cloum.
ID-----Edate-------Result
50----2008-01-15-- 2008-01-15
50----2008-07-13---2008-07-15
I am writing a query like this but this results in only one row ie if only a record exsists then its displaying elses it not:-
select Table2.Insured,Table2.Edate,(select max(Table1.Edate) from Table1,Table2
WHERE Table1.Insured=Table2.Insured
and(Table1.Edate BETWEEN Table2.Edate AND Table2.Edate+5)
group by Table2.Insured,Table2.Edate
MY RESULT:-
ID-----Edate-------Result
50----2008-01-15-- 2008-01-15
How can i do this.
Thanks
sh
June 3, 2009 at 3:23 pm
I think this will work:
IF OBJECT_ID('dbo.Table1') IS NOT NULL
DROP TABLE table1
IF OBJECT_ID('dbo.Table2') IS NOT NULL
DROP TABLE table2
CREATE TABLE Table1 (id INT, NO CHAR(3), Edate SMALLDATETIME)
CREATE TABLE Table2 (id INT, edate SMALLDATETIME)
INSERT INTO Table1 (
id,
[NO],
Edate
) VALUES (
50,
'001',
'2008-08-17' )
INSERT INTO Table2 (
id,
edate
)
SELECT
50,
'2008-01-15'
UNION ALL
SELECT
50,
'2008-07-13'
select
T.id,
T.edate,
CASE
WHEN T2.Edate BETWEEN T.edate AND DATEADD(DAY, 5, T.edate) THEN T2.Edate
ELSE T.Edate
END AS result
FROM
Table2 AS T JOIN
Table1 AS T2 ON
T.id = T2.id
Please be sure that the query you post matches the example data you post. Posting your query was not very helpful because it did not match the example provided.
Also notice how I provided table structures with sample data. Please read the articles linked in my signature. Doing what they suggest makes it much easier to provide accurate help.
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
June 3, 2009 at 4:28 pm
Thank you.You are awe some.
This is a great help for beginner like me.
June 3, 2009 at 4:59 pm
Thank you Jack.
But this does not work if i have more ids.
For each id its giving me all dates.But it should only give me max date within 5 days in other table if exists.
How can i do this.
Thanks
June 3, 2009 at 5:53 pm
pvasudha (6/3/2009)
Thank you Jack.But this does not work if i have more ids.
For each id its giving me all dates.But it should only give me max date within 5 days in other table if exists.
How can i do this.
Thanks
I believe at this point that you need to provide sufficient sample data. Look at how Jack did it, and read the link in either his signature block or mine. When you provide the sample data (as Jack demonstrated how to), then we all can easily jump in and help you out. Please remember that all of us are un-paid volunteers, and we pick-and-choose those we will help. Those that help themselves by providing good test data get the best (and quickest) responses.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply