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 |
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2013-01-14 : 16:51:23
|
I have a DB that stores when a PC ran a hardware scan as well as a software scan. I have two separate SQL scripts. One returns the last Hardware Scan the other returns the last Software Scan. Both SQL scripts use DATEDIFF. Example:datediff(dd, LastScanDate, GETDATE()) AS [DaysSinceLastScan]Can I have 2 DateDiff commands under the same SELECT statement and if so how? |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-14 : 16:58:02
|
You can have as many DATEDIFFs as you want. Are you trying to get the DATEDIFFs from two different tables? You can still do it one SELECT, as long as there's some relationship between the tables. You'd have to supply some sample data and what your expected results are, and also include the 2 scripts you are currently using, to get a precise answer.JimEveryday I learn something that somebody else already knew |
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2013-01-14 : 17:18:42
|
Both queries are pulling data from SCCM DB. SCCM is the tool Microsoft uses to control/monitor PC's. Here is my Software Scan Query. SELECT DISTINCTgs.Name0 AS [PC Name],gs.Domain0 AS [Domain],gs.Manufacturer0 AS [Manufacturer],gs.Model0 AS [Model],gs.UserName0 AS [User Name],sw.LastScanDate AS [Last SW Scan],datediff(dd, LastScanDate, GETDATE()) AS [DaysSinceLastScan]FROMv_GS_COMPUTER_SYSTEM gs INNER JOIN v_GS_LastSoftwareScan sw ON gs.ResourceID = sw.ResourceIDWHERE gs.Name0 LIKE '%-PC%'AND DateDiff(dd, sw.LastScanDate, GETDATE()) >=31ORDER BY gs.Name0, gs.Domain0 --------------------------------------------------------------Here is my Hardware Scan Query. In this one I do have some added logic to look for machines with a certain .EXE installed. SELECT DISTINCT a.Name0 AS [Machine Name],b.SiteCode,c.FileVersion AS [Java],d.Operating_System_Name_and0,c.FilePath,v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HWScan],DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS [Days Since Last HWScan] FROM v_GS_SoftwareFile c INNER JOIN v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN v_FullCollectionMembership b ON a.ResourceID = b.ResourceIDINNER JOIN v_GS_WORKSTATION_STATUS ON v_GS_WORKSTATION_STATUS.ResourceID = a.ResourceID WHERE (c.FileName = 'java.exe') AND (c.FileVersion LIKE '6.0.%' )AND (c.FilePath LIKE 'c:\program files%') AND (a.Name0 NOT LIKE 'N1%')AND (a.Name0 NOT LIKE 'N2%') ORDER BY [Last HWScan] DESCAny help would be awesome, thank you!! |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-14 : 18:10:06
|
The only connection I'm seeing between the 2 tables is Name0. Do you want all the columns from each table in your result set and is there a one-to-one relationship between the 2 queries based on Name0?JimEveryday I learn something that somebody else already knew |
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2013-01-14 : 18:19:52
|
Yes, Name0 is the NetBIOS name of the PC's. And I query the VIEWS and both VIEWS have a RESOURCE_ID so they can be joined on that. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-14 : 19:15:01
|
Maybe something like this thenSELECT * -- don't use the *, I'm just being lazyFROM(SELECT a.Name0 AS [Machine Name],,Resource_IDb.SiteCode,c.FileVersion AS [Java],d.Operating_System_Name_and0,c.FilePath,v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HWScan],DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS [Days Since Last HWScan]FROM v_GS_SoftwareFile c INNER JOIN v_GS_SYSTEM a ON c.ResourceID = a.ResourceIDINNER JOIN v_R_System d ON a.ResourceID = d.ResourceIDINNER JOIN v_FullCollectionMembership b ON a.ResourceID = b.ResourceIDINNER JOIN v_GS_WORKSTATION_STATUS ON v_GS_WORKSTATION_STATUS.ResourceID = a.ResourceIDWHERE (c.FileName = 'java.exe')AND (c.FileVersion LIKE '6.0.%' )AND (c.FilePath LIKE 'c:\program files%')AND (a.Name0 NOT LIKE 'N1%')AND (a.Name0 NOT LIKE 'N2%')GROUP BYa.Name0 AS [Machine Name],,Resource_IDb.SiteCode,c.FileVersion AS [Java],d.Operating_System_Name_and0,c.FilePath,v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HWScan],DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS [Days Since Last HWScan])t1INNER JOIN(SELECT gs.Name0 AS [PC Name],resource_idgs.Domain0 AS [Domain],gs.Manufacturer0 AS [Manufacturer],gs.Model0 AS [Model],gs.UserName0 AS [User Name],sw.LastScanDate AS [Last SW Scan],datediff(dd, LastScanDate, GETDATE()) AS [DaysSinceLastScan]FROMv_GS_COMPUTER_SYSTEM gs INNER JOIN v_GS_LastSoftwareScan sw ON gs.ResourceID = sw.ResourceIDWHERE gs.Name0 LIKE '%-PC%'AND DateDiff(dd, sw.LastScanDate, GETDATE()) >=31GROUP BYgs.Name0 AS [PC Name],resource_id,gs.Domain0 AS [Domain],gs.Manufacturer0 AS [Manufacturer],gs.Model0 AS [Model],gs.UserName0 AS [User Name],sw.LastScanDate AS [Last SW Scan],datediff(dd, LastScanDate, GETDATE()) AS [DaysSinceLastScan]) t2 ON t1.RESOURCE_ID = t2.RESOURCE_IDJimEveryday I learn something that somebody else already knew |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-15 : 00:36:01
|
Depending on whether you'll always have HW scan and S/W scan records you should be using one out of INNER,LEFT or FULL JOINif both scan records will always be present use INNERif any one of them is present, do a LEFT JOIN using it as left tableIf both can be absent use FULL JOIN------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2013-01-15 : 10:09:33
|
ok, I am starting to see what you did. You have a Parent SELECT and within this Parent SELECT you JOIN two child SELECTS. I am using the SQL Server Management Studio so it will highlight with a red line where your syntax is wrong. I modified your code a little and I know I'm close but I still get a few syntax errors. I put in comments where I get syntax errors. example: -- ERROR on thisUSE SMS_NA1SELECTrid1.ResourceID,rid2.ResourceIDFROM v_GS_computer_system rid1 INNER JOIN v_GS_softwareFile rid2( -- ERROR on thisSELECT -- ERROR on thisa.Name0 AS [Machine Name],b.SiteCode,c.FileVersion AS [Java],d.Operating_System_Name_and0,c.FilePath,v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HWScan],DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS [Days Since Last HWScan]FROM v_GS_SoftwareFile c INNER JOIN v_GS_SYSTEM a ON c.ResourceID = a.ResourceIDINNER JOIN v_R_System d ON a.ResourceID = d.ResourceIDINNER JOIN v_FullCollectionMembership b ON a.ResourceID = b.ResourceIDINNER JOIN v_GS_WORKSTATION_STATUS ON v_GS_WORKSTATION_STATUS.ResourceID = a.ResourceIDWHERE (c.FileName = 'java.exe')AND (c.FileVersion LIKE '6.0.%' )AND (c.FilePath LIKE 'c:\program files%')AND (a.Name0 NOT LIKE 'N1%')AND (a.Name0 NOT LIKE 'N2%')) t1 -- ERROR on the )INNER JOIN(SELECT gs.Name0 AS [PC Name],gs.Domain0 AS [Domain],gs.Manufacturer0 AS [Manufacturer],gs.Model0 AS [Model],gs.UserName0 AS [User Name],sw.LastScanDate AS [Last SW Scan],datediff(dd, LastScanDate, GETDATE()) AS [DaysSinceLastScan]FROMv_GS_COMPUTER_SYSTEM gs INNER JOIN v_GS_LastSoftwareScan sw ON gs.ResourceID = sw.ResourceIDWHERE gs.Name0 LIKE '%-TC-7%'AND DateDiff(dd, sw.LastScanDate, GETDATE()) >=31)WHERE -- ERROR on this wheret1.resourceID = t2.ResourceID |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-15 : 11:00:44
|
you're missing an alias for second derived table after INNER JOIN. Also no ON condition is present for INNER JOIN------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2013-01-15 : 11:57:09
|
Not sure I follow you. If I take each inner SELECT and run them they work. It is only when I join them do I get errors. Where do I need the alias and which ON is wrong? |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-15 : 13:02:49
|
quote: Originally posted by mqh7 Not sure I follow you. If I take each inner SELECT and run them they work. It is only when I join them do I get errors. Where do I need the alias and which ON is wrong?
USE SMS_NA1GOSELECTrid1.ResourceID,rid2.ResourceIDFROM v_GS_computer_system rid1 INNER JOIN v_GS_softwareFile rid2 ON-- you need to specify your join here ON RID1... = RID2...the below that you have the derived table T1 so you need to join again to that and so on |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-15 : 13:17:15
|
Don't have your tables or data to test, but try the below after specifying a few join ON's, it should at least give you less errors:USE SMS_NA1GOSELECT rid1.ResourceID ,rid2.ResourceIDFROM v_GS_computer_system rid1 INNER JOIN v_GS_softwareFile rid2 --ON rid1.ResourceId = rid2.ResourceID INNER JOIN -- should fix the two below errors now you specificed the above join ( -- ERROR on this SELECT -- ERROR on this a.Name0 AS [Machine Name], b.SiteCode,c.FileVersion AS [Java], d.Operating_System_Name_and0, c.FilePath, v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HWScan], DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS [Days Since Last HWScan] FROM v_GS_SoftwareFile c INNER JOIN v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN v_FullCollectionMembership b ON a.ResourceID = b.ResourceID INNER JOIN v_GS_WORKSTATION_STATUS ON v_GS_WORKSTATION_STATUS.ResourceID = a.ResourceID WHERE (c.FileName = 'java.exe') AND (c.FileVersion LIKE '6.0.%' ) AND (c.FilePath LIKE 'c:\program files%') AND (a.Name0 NOT LIKE 'N1%') AND (a.Name0 NOT LIKE 'N2%') ) t1 -- ERROR on the ) --ON t1..... = rid1/rid2.... INNER JOIN ( SELECT gs.Name0 AS [PC Name], gs.Domain0 AS [Domain], gs.Manufacturer0 AS [Manufacturer], gs.Model0 AS [Model], gs.UserName0 AS [User Name], sw.LastScanDate AS [Last SW Scan], datediff(dd, LastScanDate, GETDATE()) AS [DaysSinceLastScan] FROM v_GS_COMPUTER_SYSTEM gs INNER JOIN v_GS_LastSoftwareScan sw ON gs.ResourceID = sw.ResourceID WHERE gs.Name0 LIKE '%-TC-7%'AND DateDiff(dd, sw.LastScanDate, GETDATE()) >=31 )-- the derived table needs a tame and the join I.E T2 ON T2.RsourceID = T1.ResourceID |
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2013-01-16 : 10:24:32
|
Your code gives no syntax errors but it fails when I run it. Saying there is an error on ')' line 51. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-16 : 22:31:37
|
quote: Originally posted by mqh7 Your code gives no syntax errors but it fails when I run it. Saying there is an error on ')' line 51.
can you post your full query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2013-01-18 : 11:50:45
|
I have it working. I talked to a DBA at work and they explained it to me. -- What I was told and did not know. That whatever you put in the Parent SELECT but be defined in each child-SELECTSELECT hw.[Machine Name],hw.Operating_System_Name_and0 AS 'OS Type',hw.[Days Since Last HWScan], sw.DaysSinceLastScanFROM (SELECT DISTINCT v_GS_WORKSTATION_STATUS.ResourceID, a.Name0 AS [Machine Name], b.SiteCode, d.Operating_System_Name_and0, v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HWScan], DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS [Days Since Last HWScan] FROM v_GS_SoftwareFile c INNER JOIN v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN v_FullCollectionMembership b ON a.ResourceID = b.ResourceID INNER JOIN v_GS_WORKSTATION_STATUS ON v_GS_WORKSTATION_STATUS.ResourceID = a.ResourceID WHERE DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) >=31 ) AS hw INNER JOIN (SELECT DISTINCT gs.ResourceID, gs.Name0 AS [PC Name], gs.Domain0 AS [Domain], gs.Manufacturer0 AS [Manufacturer], gs.Model0 AS [Model], gs.UserName0 AS [User Name], sw.LastScanDate AS [Last SW Scan], datediff(dd, LastScanDate, GETDATE()) AS [DaysSinceLastScan] FROM v_GS_COMPUTER_SYSTEM gs INNER JOIN v_GS_LastSoftwareScan sw ON gs.ResourceID = sw.ResourceID WHERE (gs.Name0 NOT LIKE '%N1%') AND (gs.Name0 NOT LIKE '%N2%') AND (gs.Name0 NOT LIKE '%E1%') AND (gs.Name0 NOT LIKE '%E2%') AND (gs.Name0 NOT LIKE '%A1%') AND DateDiff(dd, sw.LastScanDate, GETDATE()) >=31 ) AS sw ON hw.ResourceID = sw.ResourceID |
|
|
|
|
|
|
|