extract installs using machine latest scan date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

extract installs using machine latest scan date

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2013-04-29 : 07:42:31

Hi,

There are 2 tables.

I want to show output from #installs table.

Logic:
1. Machine should be within 120 days (use softscan column).
2. Once you get machine withl softscan date then match it with inventdate and show installs.

OUTPUT has to be:
adobe 7.0 2013-03-21 efg
sql server 7.0 2013-03-25 ert

IS THIS POSSIBLE IN 1 QUERY CONSIDERING MILLIONS OF ROWS

create table #installs
(
app nvarchar(1000),
ver nvarchar(10),
inventdate date ,
machine nvarchar(10))

insert into #installs values ('adobe','7.0','21-MAR-13','efg')
insert into #installs values ('excel','7.0','26-JUN-12','efg')
insert into #installs values ('sql server','7.0','25-MAR-13','ert')
insert into #installs values ('toad','7.0','20-OCT-12','ert')

create table #machine
( machine nvarchar(10),
softscan date)

insert into #machine values ('EFG','21-MAR-13')
insert into #machine values ('ERT','25-MAR-13')

Regards,
Sachin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 07:49:15

sounds like this


SELECT i.*
FROM #machine m
INNER JOIN #installs i
ON i.inventdate = m.softscan
WHERE m.softscan > = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-120,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sachingovekar
Posting Yak Master

101 Posts

Posted - 2013-04-29 : 08:07:28

#INSTALLS is not a table....but its is a result of several joins.

For simplicity i have shown it is a installs table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 08:11:23

then replace it with actual query within ()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources