Using the DateDiff command - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Using the DateDiff command

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.

Jim

Everyday 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 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!!

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?

Jim

Everyday 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 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

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 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/

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

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 MVP
http://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_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

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_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

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 MVP
http://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-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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources