SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 extract installs using machine latest scan date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sachingovekar
Yak Posting Veteran

99 Posts

Posted - 04/29/2013 :  07:42:31  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/29/2013 :  07:49:15  Show Profile  Reply with Quote
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
Go to Top of Page

sachingovekar
Yak Posting Veteran

99 Posts

Posted - 04/29/2013 :  08:07:28  Show Profile  Reply with Quote
#INSTALLS is not a table....but its is a result of several joins.

For simplicity i have shown it is a installs table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/29/2013 :  08:11:23  Show Profile  Reply with Quote
then replace it with actual query within ()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000