Write a query for a specific record with missing information

Transact-SQL
1

Hello,

I have the following table.

Column1 Column2 date
110913  8048    9/15/2015
110913  8036    9/30/2015
110652  1011    10/19/2015
110652  1011    10/10/2015
110009  1016    7/1/2015
110009  1087    6/20/2015
110888  7171    4/1/2015

I needed it to get the earliest date from the above table based on column 'Column1'. With help from somebody, came up with the following query and works perfectly

with Min_Record as
(
select  Table1.Column1, Table2.Column2, Table2.date, row_number() over(partition by t.Table1.Column1 order by Table2.date asc) as rn
 from    Table2 INNER JOIN Table1  ON  Table1.Entry = Rable2.Entry 
)

select   m.Column1, m.Column2, Table2.Date
from     Min_Record m
where    m.rn = 1;

It generates this output:

Column1 Column2 date
110913  8048    9/15/2015
110652  1011    10/10/2015
110009  1087    6/20/2015
110888  7171    4/1/2015

Now what happens when there are no information exist in Table2 when Column1 = 110888. How to add a code to above query that produces the following output (adding line '----').

Column1 Column2 date
110913  8048    9/15/2015
110652  1011    10/10/2015
110009  1087    6/20/2015
110888  ----     ----

Any help would be greatly appreciated.

2

User a left join

WITH Min_Record
    AS (
          SELECT Table1.Column1
                , isnull(Table2.Column2, '---')
                , isnull(cast(Table2.DATE as varchar(10)), '---')
                , row_number() OVER (
                      PARTITION BY t.Table1.Column1 ORDER BY Table2.DATE ASC
                      ) AS rn
          FROM Table2
          LEFT JOIN Table1
                ON Table1.Entry = Rable2.Entry
          )
    SELECT m.Column1
          , m.Column2
          , Table2.DATE
    FROM Min_Record m
    WHERE m.rn = 1;RETURN;
3

Hi gbritton,
Thanks for your help. Using your query the Column1 skip the value of 110888 and the output return this result:

Column1  Column2 date
110913   8048    9/15/2015
110652   1011    10/10/2015
110009   1087    6/20/2015

Note: Column2 is INT (data type) and does not allow nulls.

4

Please post sample data for table2. Also, your table1 example does not have an "Entry" column. I think that the ORDER BY clause should be on the Table1.DATE column, not Table2.

5

Gbritton,

The Column1 of table1 has a value (always). But Column2 and date in table2 sometime do not have any value at the same time. I need to see if there is no value at Column2 and date then add '---'.