2. PHPRunner General questions
  3. [SOLVED]  Numeric difference between two dates
This topic is locked
[SOLVED]

 Numeric difference between two dates

 		7/24/2012 1:11:45 PM
PHPRunner General questions
P
 procheck author

Hi,

This is more of an SQL question than a PHPRunner question. Hopefully someone can steer me in the right direction. I have two working SQL's. The first one selects the row with the most current date. The second selects the rows 8 days from the current date.
What I want to do is find the difference in points between the two dates. So if the player has 20 points as of today and 14 points on the date 8 days ago, I want the result to be 6. Now I can run this query twice, save the results to a variable and take the difference.
Is there a way to combine these queries to calculate the points difference and return one result?

Thanks
Al
SELECT

A.GameDate,

a.PlayerID,

A.Points,

A.IceTimePerGm

FROM poolpoints A, your_team B

WHERE A.ppPoolID = B.ytPoolID AND A.UserName = B.UserName AND A.PlayerID = B.PlayerID

AND A.GameDate = (SELECT MAX(C.GameDate) FROM poolpoints C)

;
SELECT

A.GameDate,

a.PlayerID,

A.Points,

A.IceTimePerGm

FROM poolpoints A, your_team B

WHERE A.ppPoolID = B.ytPoolID AND A.UserName = B.UserName AND A.PlayerID = B.PlayerID AND

A.GameDate > (SELECT DATE_SUB(MAX(C.GameDate),INTERVAL 8 DAY) FROM poolpoints C)

ORDER BY A.GameDate DESC

;
Sergey Kornilov admin 7/25/2012

Each database comes with a number of date related functions.
Google "<your database> data functions" or "<your database> date difference" for more info.
P
 		procheck author 7/25/2012

Hi Sergey,
I just realized that the first SQL is probably not required because it is included as part of the results from the 2nd SQL.
The examples I've found so far are based on finding elapsed time or days which is something that's easier to do. I already do that elsewhere but it's not what I want. I'm looking for an example that gives the different for a numeric field for Col A today and Col A 3 days ago. Still searching.
C
 		charlesfolder 7/25/2012

Hi, prochecksports
I did that with mysql
in PHPR Query Designer
SELECT

DealNumber,

DealDate,

TotPymts,

BuyerName,

BankName,

SerialNumber,

StockNumber,

LicenseNumber,

SalesmanName1,

CurrentStatusDesc,

(DATEDIFF(NOW(),generalinformation.DealDate)) AS DIT,

FIName,

INSURANCE,

ID

FROM generalinformation
I hope this help you.
P
 		procheck author 7/25/2012

Hi Charles,
Thanks for the reply but I'm not looking for a date function. I'm looking to find the difference in points between the two dates. So to simplify it, I need to calculate the difference in points between two rows of the same column in the same table.
Al
Sergey Kornilov admin 7/26/2012

Al,
it's totally not clear how do you want to reference those two rows in the database. SQL functions work best with data that come from the same record. My guess is that you need to write a piece of PHP code that goes through all or several records and calculates the difference between points in different rows.
P
 		procheck author 7/26/2012

Since it's easy enough to find the difference between two dates, I thought that maybe there was a way to also check other non-date fields. I've seen many examples that apparently do what I want but I was unable to make them work. I'll be going the PHP route. Thanks
Al