SQL Query Comparing Dates - 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

SQL Query Comparing Dates

Author

Topic

nanigbogu
Starting Member

3 Posts

Posted - 2012-06-20 : 16:26:18

Hey all,

I am trying to write a query to find all records that are within a month of each other in access.

One table has 2 columns (VIN and FailDate). Other table has 2 columns (VIN and ProcessDate). The VIN (vehicle number) is only listed once in the first table and only has one faildate. On the second table the VIN (same VINS as the first table) can be listed multiple times because there can be multiple process dates for one VIN.

They query is supposed to find out where the VIN is the same and the difference between the faildate and process date is less than 30. I know im supposed to use the datediff function but i am confused as to how to go about it.

This is all i have that works so far.

SELECT A.VIN, B.VIN, A.MonthP AS PMonth, B.MonthP AS FailMth
FROM Not_Matched AS A INNER JOIN Not_Matched_Ras_Claim AS B ON A.VIN = B.VIN;

(Not_Matched and Not_Matched_Ras_Claim are the tables)

Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-20 : 17:54:55

I'm not sure I follow youe Logic, but here is a slight change on your query that will show the difference in days:

SELECT 
	A.VIN, 
	B.VIN, 
	A.MonthP AS PMonth, 
	B.MonthP AS FailMth,
	DATEDIFF(DAY, A.MonthP, B.MonthP) AS DifferenceInDays
FROM 
	Not_Matched AS A 
INNER JOIN 
	Not_Matched_Ras_Claim AS B 
	ON A.VIN = B.VIN;

If that is not what you ae looking for, please supply DDL, DML and Expected Output. Here are two link that can help you supply that information if you do not know how:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
http://www.sqlservercentral.com/articles/Best+Practices/61537/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-06-20 : 18:06:08

[code]
SELECT A.VIN
,B.VIN
,A.MonthP AS PMonth
,B.MonthP AS FailMth
FROM Not_Matched AS A
INNER JOIN Not_Matched_Ras_Claim AS B
ON A.VIN=B.VIN
AND datediff('d',a.FailDate,b.ProcessDate)<30
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 21:53:09

[code]
SELECT t1.VIN
,t2.VIN
,t1.MonthP AS PMonth
,t2.MonthP AS FailMth
FROM Table1 t1
CROSS APPLY (SELECT *
FROM Table2
WHERE VIN = t1.VIN
AND ProcessDate > DATEADD(dd,-30,t1.FailDate)
)t2
WHERE Cnt > 0
[/code]

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

nanigbogu
Starting Member

3 Posts

Posted - 2012-06-21 : 08:30:26

Thanks for the replies. Looking at the code it looks like it should work. However when i run it in access a dialog box pops up and asks me to "enter parameter value" for Day. If i put day in single quotes 'day', it doesnt pop up the dialog box but it shows error in the Differenceindays column.

I used this code

SELECT
A.VIN,
B.VIN,
A.MonthP AS PMonth,
B.MonthP AS FailMth,
DATEDIFF(DAY, A.MonthP, B.MonthP) AS DifferenceInDays
FROM
Not_Matched AS A
INNER JOIN
Not_Matched_Ras_Claim AS B
ON A.VIN = B.VIN;

nanigbogu
Starting Member

3 Posts

Posted - 2012-06-21 : 08:42:50

I figured it out. Turns out that access just uses a slightly different syntax than others.

The formula doesnt work with this

DATEDIFF(DAY, A.MonthP, B.MonthP) AS DifferenceInDays

but works with this

DATEDIFF('d', A.MonthP, B.MonthP) AS DifferenceInDays

Thanks again for all the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 17:42:19

you should have told that you're after access query. as you've posted this in t-sql forum

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources