Professional Web Applications Themes

SQL help needed - Microsoft SQL / MS SQL Server

There are 2 tables: CREATE TABLE [dbo].[T1] ( [T1] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[T2] ( [T2] [int] NULL ) ON [PRIMARY] GO The values in both the tables are as follows: Table T1: T1 --- 3 7 Table T2: T2 --- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 The output needed is: T1 T2 ----- ----- 3 1 3 2 3 3 3 4 3 5 3 6 3 7 7 8 7 9 7 10 7 11 7 12 7 13 7 15...

  1. #1

    Default SQL help needed

    There are 2 tables:

    CREATE TABLE [dbo].[T1] (
    [T1] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[T2] (
    [T2] [int] NULL
    ) ON [PRIMARY]
    GO

    The values in both the tables are as follows:

    Table T1:

    T1
    ---
    3
    7

    Table T2:

    T2
    ---
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15


    The output needed is:

    T1 T2
    ----- -----
    3 1
    3 2
    3 3
    3 4
    3 5
    3 6
    3 7
    7 8
    7 9
    7 10
    7 11
    7 12
    7 13
    7 15



    Ajit Guest

  2. #2

    Default Re: SQL help needed

    here is a convoluted way...

    declare t1 table(i int)
    insert t1 select 3 union all select 7 union all select 4

    declare t2 table(i int)
    insert t2
    select 1 union all select 2 union select 3 union all select 4 union all
    select 5 union all select 6
    union all select 7 union all select 8 union all select 9 union all select 10
    union all select 11
    union all select 12 union all select 13 union all select 14 union all select
    15

    select t1.i,t2.i
    from t1 t1 left join t2 t2
    on t2.i between case when (select max(i) from t1 t1a where t1a.i<t1.i) is
    null then (select min(i) from t2)
    else (select min(i) from t2 where i>t1.i) end
    and isnull((select min(i) from t1 t1b where t1b.i>t1.i),(select max(i) from
    t2))
    order by t1.i


    --
    -oj
    Rac v2.2 & QALite!
    http://www.rac4sql.net


    "Ajit" <net> wrote in message
    news:phx.gbl... 


    oj Guest

  3. #3

    Default Re: SQL help needed

    SELECT CASE WHEN T2<=7
    THEN MIN(T1)
    ELSE MAX(T1)
    END AS T1 ,
    T2
    FROM T1
    CROSS JOIN T2
    GROUP BY T2

    but for this to be applicable to any other data set you'll have to define
    what your rule is for the join.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  4. #4

    Default Re: SQL help needed

    Thanks for so many solutions. Due to lack of time, I could not elaborate the
    requirement in detail so I created two simple tables T1 and T2. The first
    table, T1, is a table of Month ids which stores the months on which one
    fixed asset is transferred to another place. Hence, it can have multiple
    transfer month ids (not just 3 and 7..). The other table, T2 contains again
    the month ids which are continuous months for the asset life. In the query
    result, I wanted the asset transfer month from table T1 till the first break
    and then the second transfer month and so on. Hence the first table T1 can
    have many breaks.

    Thanks again for your helpful responses. I used the following query finally
    for my requirement:

    SELECT
    ISNULL(
    (SELECT TOP 1 T1 FROM T1 TAB1 WHERE TAB1.T1<TAB2.T2 ORDER BY TAB1.T1 DESC),
    (SELECT MIN(T1) FROM T1)
    ),T2
    FROM T2 TAB2

    Ajit Singh
    Host ytics Inc.
    www.hostytics.com


    "Ajit" <net> wrote in message
    news:phx.gbl... 


    Ajit Guest

Similar Threads

  1. help needed...
    By Roger Abell [MVP] in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 6th, 10:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139