Max(Date) question

  • Hi there,

    I need help with writing part of query. Here's what I have

    create table first_table(empid [varchar] (10) primary key not null,

    DateInserted Datetime)

    insert into first_table('1001','2012-02-13');

    insert into first_table('1002','2013-02-13');

    insert into first_table('1003','2014-02-11');

    insert into first_table('1004','2012-02-13');

    insert into first_table('2001','2012-02-12');

    insert into first_table('2002','2014-02-13');

    insert into first_table('5001','2014-02-13');

    create table second_table(empid [varchar] (10) not null, CompanyID [varchar] (10) not null)

    insert into second_table('1001','1');

    insert into second_table('1002','1');

    insert into second_table('1003','1');

    insert into second_table('1004','1');

    insert into second_table('2001','2');

    insert into second_table('2002','2');

    insert into second_table('5001','5');

    create table valid_companies(CompanyID [varchar] (10) not null)

    insert into valid_companies('1');

    insert into valid_companies('2');

    I want to select records from first_table that are valid_companies and with max date.

    query should print 1003,1,2014-02-11

    2002,2,2014-02-13

    I am able to get

    select DateInserted,ni.empID

    ,CompanyID

    from

    [dbo].[second_table] vw

    inner join [dbo].[first_table] ni on ni.EmpID=vw.EmpID

    where CompanyID in(

    SELECT DISTINCT [CompanyID]

    FROM [dbo].[Valid_Companies]

    ) . How to include max(DateInserted) in this query

  • Is this what you are looing for?

    SELECT first_table.empid

    ,second_table.CompanyID

    ,MAX(DateInserted)

    FROM first_table

    INNER JOIN second_table

    ON second_table.empid = first_table.empid

    INNER JOIN valid_companies

    ON valid_companies.CompanyID = second_table.CompanyID

    GROUP BY first_table.empid, second_table.CompanyID

    empid CompanyID

    ---------- ---------- -----------------------

    1001 1 2012-02-13 00:00:00.000

    1002 1 2013-02-13 00:00:00.000

    1003 1 2014-02-11 00:00:00.000

    1004 1 2012-02-13 00:00:00.000

    2001 2 2012-02-12 00:00:00.000

    2002 2 2014-02-13 00:00:00.000

  • I only need to print Max date for each valid company ID.

    For Company ID=1; 1003,1,2014-02-11

    CompanyID=2; 2002,2,2014-02-13

  • Kudos on the attempt at posting ddl and sample data. However, the sample data is unusable because the insert statements are all invalid. Secondly, you mention in your query TagValue but that column is not in your sample tables.

    This query is pretty straight forward to write and I can think about several ways to write it. With no usable data though it is a lot more difficult.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I think there is a better way to do this. but this will work.

    WITH MaxDates AS

    (

    SELECT second_table.CompanyID

    ,MAX(DateInserted) MaxDateInserted

    FROM first_table

    INNER JOIN second_table

    ON second_table.empid = first_table.empid

    INNER JOIN valid_companies

    ON valid_companies.CompanyID = second_table.CompanyID

    GROUP BY second_table.CompanyID

    )

    SELECT *

    FROM first_table

    INNER JOIN second_table

    ON second_table.empid = first_table.empid

    INNER JOIN MaxDates

    ON MaxDates.CompanyID = second_table.CompanyID

    AND MaxDates.MaxdateInserted = first_table.DateInserted

    empid DateInserted empid CompanyID CompanyID MaxDateInserted

    ---------- ----------------------- ---------- ---------- ---------- -----------------------

    1003 2014-02-11 00:00:00.000 1003 1 1 2014-02-11 00:00:00.000

    2002 2014-02-13 00:00:00.000 2002 2 2 2014-02-13 00:00:00.000

    (2 row(s) affected)

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

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