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.
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 efgsql server 7.0 2013-03-25 ertIS THIS POSSIBLE IN 1 QUERY CONSIDERING MILLIONS OF ROWScreate 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 thisSELECT i.*FROM #machine mINNER JOIN #installs iON i.inventdate = m.softscanWHERE m.softscan > = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-120,0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|