Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to do between in where clause of join ..plz look at the sample

Answered how to do between in where clause of join ..plz look at the sample

  • Wednesday, May 02, 2012 9:36 AM
     
     
    Sign In to Vote
    0

    i have the follwoing two tables

    SELECT PolicyID,ValuationDate FROM dbo_Stage.AdhocLoad
    SELECT ContractID,StartTranId,EndTranID FROM tblsource

    and i want to achive the following query in SSIS...How to do that


    SELECT t1.ContractID,t1.StartTranId,t1.EndTranID FROM tblsource t1
    INNER JOIN dbo_Stage.AdhocLoad t2
    ON t1.ContractID=t2.PolicyID
    WHERE (t2.ValuationDate BETWEEN t1.StartTranId and t1.EndTranID)
    OR (t1.EndTranID IS NULL AND t2.ValuationDate>=t1.StartTranId)
    OR(t1.EndTranID IS NULL)

    ilikemicrosoft

     

All Replies

  • Wednesday, May 02, 2012 9:46 AM
     
     
    Sign In to Vote
    0
    You copy paste the code into an Execute SQL Task or OLE DB source?

    MCTS, MCITP - Please mark posts as answered where appropriate.

     
  • Wednesday, May 02, 2012 9:59 AM
     
     
    Sign In to Vote
    0
    i cant do this bcoz tblSource is from mainframe... just for sample i have placed it

    ilikemicrosoft


    • Edited by surendiran Wednesday, May 02, 2012 10:02 AM
    •  
     
  • Wednesday, May 02, 2012 10:06 AM
     
     
    Sign In to Vote
    0
    You should mention stuff like that in your question.
    How big are the tables?

    MCTS, MCITP - Please mark posts as answered where appropriate.

     
  • Wednesday, May 02, 2012 10:12 AM
     
     
    Sign In to Vote
    0
    source table is from mainframe..which is big one but the Adhoc table is small one which may have 500 records..

    ilikemicrosoft

     
  • Wednesday, May 02, 2012 10:28 AM
     
     
    Sign In to Vote
    0
    What is big one? About how many rows/gigabytes are we talking here?

    MCTS, MCITP - Please mark posts as answered where appropriate.

     
  • Wednesday, May 02, 2012 10:57 AM
     
     
    Sign In to Vote
    0

    I have the follwoing query

    SELECT t1.ContractID,t1.StartTranId,t1.EndTranID FROM tblsource t1
    INNER JOIN dbo_Stage.AdhocLoad t2
    ON t1.ContractID=t2.PolicyID
    WHERE (t2.ValuationDate BETWEEN t1.StartTranId and ISNULL(t1.EndTranID,t2.ValuationDate))

    in the above query t1 table is from mainframe so i can use oldedb datasource to connect that ..and another oledbdatasource for t2 which sql server table...

    after the join condition i dont know how to do the conditional split for the condtion in the where clause

    ilikemicrosoft

     
  • Wednesday, May 02, 2012 11:10 AM
     
     
    Sign In to Vote
    0

    Duplicate question.

    Original question with replies can be found here:

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/1173741f-c026-4bdd-861c-388c48290eae

    MCTS, MCITP - Please mark posts as answered where appropriate.

     
  • Wednesday, May 02, 2012 11:28 AM
     
     
    Sign In to Vote
    0

     have the follwoing query --

    in the above query everything is date datatype

    SELECT t1.ContractID,t1.StartTranId,t1.EndTranID FROM tblsource t1
    INNER JOIN dbo_Stage.AdhocLoad t2
    ON t1.ContractID=t2.PolicyID
    WHERE (t2.ValuationDate BETWEEN t1.StartTranId and ISNULL(t1.EndTranID,t2.ValuationDate))

    in the above query t1 table is from mainframe so i can use oldedb datasource to connect that ..and another oledbdatasource for t2 which sql server table...

    after the join condition i dont know how to do the conditional split for the condtion in the where clause..

    am trying to do the above using Condittional statement....[ValuationDate] >= [StartTranId] &&   [ValuationDate]<= ISNULL( [EndTranID]  )  ? [ValuationDate]  :  [EndTranID]..but this gives me error message

    ilikemicrosoft

     
  • Wednesday, May 02, 2012 11:28 AM
     
     
    Sign In to Vote
    0

    am trying to do the above using Condittional statement....[ValuationDate] >= [StartTranId] &&   [ValuationDate]<= ISNULL( [EndTranID]  )  ? [ValuationDate]  :  [EndTranID]..but this gives me error message

    ilikemicrosoft

     
  • Wednesday, May 02, 2012 11:42 AM
     
     Proposed Answer
    Sign In to Vote
    0

    ISNULL( [EndTranID]  ) results in a Boolean value. It doesn't convert null values, it checks if that column is NULL or not.
    So you cannot compare it against a date value.

    Again: how big is the table?
    Next time, also post the error message.

    MCTS, MCITP - Please mark posts as answered where appropriate.

     
  • Wednesday, May 02, 2012 11:55 AM
     
     
    Sign In to Vote
    0

    Hi Surendiran,

    You are not much clear with your question. Could you please make it understable and put your all required stuffs for above condition.

    As Koen has already told, it is quite easy to obtain your above said condition with script task only (just to copy and paste)

    If this doesn't suits you requirement.../then please revert with full description so that we can help you...!!!

    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

     
  • Wednesday, May 02, 2012 3:32 PM
     
     Answered
    Sign In to Vote
    0

    Hi Surendiran,

    Use Merge Join or lookup to get the result for following query: SELECT t1.ContractID,t1.StartTranId,t1.EndTranID FROM tblsource t1 INNER JOIN dbo_Stage.AdhocLoad t2 ON t1.ContractID=t2.PolicyID

    After that, have a conditional split checking: (ISNULL(EndTranId) and Valuation Date >=StartTranId ) OR (Valuaiton Date Between StartTranId and EndTranId)

    Nitesh Rai- Please mark the post as answered if it answers your question