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
 General SQL Server Forums
 New to SQL Server Programming
 Using the DateDiff command
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mqh7
Yak Posting Veteran

58 Posts

Posted - 01/14/2013 :  16:51:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/14/2013 :  16:58:02  Show Profile  Reply with Quote
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.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

mqh7
Yak Posting Veteran

58 Posts

Posted - 01/14/2013 :  17:18:42  Show Profile  Reply with Quote
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 DISTINCT
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 '%-PC%'AND DateDiff(dd, sw.LastScanDate, GETDATE()) >=31
ORDER 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.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%')


ORDER BY [Last HWScan] DESC



Any help would be awesome, thank you!!
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/14/2013 :  18:10:06  Show Profile  Reply with Quote
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?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

mqh7
Yak Posting Veteran

58 Posts

Posted - 01/14/2013 :  18:19:52  Show Profile  Reply with Quote
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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/14/2013 :  19:15:01  Show Profile  Reply with Quote
Maybe something like this then

SELECT * -- don't use the *, I'm just being lazy
FROM
(
SELECT
a.Name0 AS [Machine Name],
,Resource_ID
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%')
GROUP BY
a.Name0 AS [Machine Name],
,Resource_ID
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]
)t1
INNER JOIN
(
SELECT
gs.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]

FROM
v_GS_COMPUTER_SYSTEM gs INNER JOIN v_GS_LastSoftwareScan sw ON gs.ResourceID = sw.ResourceID

WHERE gs.Name0 LIKE '%-PC%'AND DateDiff(dd, sw.LastScanDate, GETDATE()) >=31
GROUP BY
gs.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_ID

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/15/2013 :  00:36:01  Show Profile  Reply with Quote
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 JOIN

if both scan records will always be present use INNER
if any one of them is present, do a LEFT JOIN using it as left table
If both can be absent use FULL JOIN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mqh7
Yak Posting Veteran

58 Posts

Posted - 01/15/2013 :  10:09:33  Show Profile  Reply with Quote
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 this




USE SMS_NA1
SELECT
rid1.ResourceID,
rid2.ResourceID
FROM v_GS_computer_system rid1 INNER JOIN v_GS_softwareFile rid2



( -- 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 )


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
)

WHERE -- ERROR on this where
t1.resourceID = t2.ResourceID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/15/2013 :  11:00:44  Show Profile  Reply with Quote
you're missing an alias for second derived table after INNER JOIN. Also no ON condition is present for INNER JOIN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mqh7
Yak Posting Veteran

58 Posts

Posted - 01/15/2013 :  11:57:09  Show Profile  Reply with Quote
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?
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/15/2013 :  13:02:49  Show Profile  Reply with Quote
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_NA1
GO

SELECT
rid1.ResourceID,
rid2.ResourceID
FROM 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
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/15/2013 :  13:17:15  Show Profile  Reply with Quote
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_NA1
GO

SELECT
	rid1.ResourceID
	,rid2.ResourceID
FROM 
	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

Go to Top of Page

mqh7
Yak Posting Veteran

58 Posts

Posted - 01/16/2013 :  10:24:32  Show Profile  Reply with Quote
Your code gives no syntax errors but it fails when I run it. Saying there is an error on ')' line 51.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/16/2013 :  22:31:37  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

mqh7
Yak Posting Veteran

58 Posts

Posted - 01/18/2013 :  11:50:45  Show Profile  Reply with Quote
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-SELECT


SELECT
hw.[Machine Name],
hw.Operating_System_Name_and0 AS 'OS Type',
hw.[Days Since Last HWScan],
sw.DaysSinceLastScan



FROM

(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


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.14 seconds. Powered By: Snitz Forums 2000