May 14, 2014 at 1:57 pm
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
May 14, 2014 at 2:30 pm
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
May 14, 2014 at 2:33 pm
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
May 14, 2014 at 2:55 pm
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/
May 14, 2014 at 2:58 pm
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