[SQL] - Return last record with 30 days missing. - Tilted Forum Project Discussion Community

intecel · 05-25-2006, 02:12 PM

I am not sure this is possible to do without code, but I figured I'd throw it up here for yall..

Say I have this table:

SaleDate SaleAmt
01/01/2003 5
01/15/2003 10
02/26/2003 5
02/27/2003 7

In this query, I want to return 02/26/2003 because there have been more than 30 days since the last sale. I also only want the result to show the last time there were 30 days... previous ones do not matter.

Is this possible?

insidious_machinae · 05-25-2006, 10:25 PM

Assuming you're using PHP, loop through the results and call strtotime on the SaleDate values, then put the results into an array. Then, using the unix timestamps, loop backwards through the loop looking for a delta >= 30 days (in seconds). When you find a pair of values which match this condition, you can break out of the loop and use whatever your incrementer value was to perform whatever operations you're wanting to do.

intecel · 05-26-2006, 09:32 AM

I forgot to mention that i'm using Firebird with Delphi. I was hoping there was something I could do without actually storing values or using code to run through the query.

There's about 4 million lines in this query between around 5000 companies. It's an insanely long process.

Dilbert1234567 · 05-26-2006, 09:47 AM

you can do it in a query, I’m working on it, take the top date and compare it to the current date getdate() by year and month and day, year(getdate()) month(getdate()) and day(getdate()). Then after that you would have to compare the table against it self, looking for when there are 2 dates that are within 30 days of each other, and returning all other dates that aren't

Give me a bit of time on the code, finding people who have not made a sale in 30 days is easy, the other bit is much trickier, it may be beyond me.

Dilbert1234567 · 05-26-2006, 11:03 AM

Code:

select * from testdate where 
(
	((year(SaleDate )-1)*365)+
	(month(SaleDate )-1)*30+
	(day(SaleDate ))
)
<
(
	((year(getdate())-1)*365)+
	(month(getdate())-1)*30+
	(day(getdate()))-30
)

its not perfect, but its close this will return the dates that are about 30 days old. its all in MSSQL so you'll have to convert it to your flavor of SQL

leftwingx · 05-26-2006, 11:09 AM

I can only comment based on a SQL Server background but you should be able to do it if you can a) Create a temp table or memory table (easiest) or b) use a cursor. This is necessary to create the sub selects and their respective ID's. Then you can use something like this to link your sub queries together (note: @dates is either a temporary table built using your data table and either ):

SELECT TOP 1
d1,
d2,
d1 - d2 [difference]
FROM (SELECT [ID = AUTONUMBER],Date1 [d1] FROM @dates WHERE Date1 NOT IN (SELECT MIN(Date1) FROM @dates)) t1
INNER JOIN (SELECT [ID = AUTONUMBER],Date1 [d2] FROM @dates WHERE Date1 NOT IN (SELECT MAX(Date1) FROM @dates)) t2
ON t1.ID = t2.ID
WHERE d1 - d2 > 30
ORDER BY d1 DESC

So where I wrote [ID = AUTONUMBER] this is an ID that is generated via cursor or as an auto-generated field if you use a temp table. I used this just to illustrate the basic structure of the query not knowing what your capabilities are with your software platform.

leftwingx · 05-26-2006, 11:23 AM

To clarify further my code in SQL using memory tables would look like this (note: I'm using the @dates mem table to simulate your actual data table):

Code:

DECLARE @dates TABLE (OrigDate SMALLDATETIME)

INSERT INTO @dates (OrigDate) VALUES ('01/01/2003')
INSERT INTO @dates (OrigDate) VALUES ('01/15/2003')
INSERT INTO @dates (OrigDate) VALUES ('02/26/2003')
INSERT INTO @dates (OrigDate) VALUES ('02/27/2003')

DECLARE @d1 TABLE (ID INT IDENTITY, Date1 SMALLDATETIME)
DECLARE @d2 TABLE (ID INT IDENTITY, Date2 SMALLDATETIME)

INSERT INTO @d1 (Date1)
SELECT OrigDate [d1] FROM @dates WHERE OrigDate NOT IN (SELECT MIN(OrigDate) FROM @dates)
INSERT INTO @d2 (Date2)
SELECT OrigDate [d2] FROM @dates WHERE OrigDate NOT IN (SELECT MAX(OrigDate) FROM @dates)

SELECT *
FROM @dates
WHERE OrigDate IN (
			SELECT TOP 1
				Date1
			FROM @d1 t1
				INNER JOIN @d2 t2
					ON t1.ID = t2.ID
			WHERE DATEDIFF(DAY,Date2,Date1) > 30
			ORDER BY Date1 DESC)

05-25-2006, 02:12 PM	#1 (permalink)
intecel Psycho	[SQL] - Return last record with 30 days missing. I am not sure this is possible to do without code, but I figured I'd throw it up here for yall.. Say I have this table: SaleDate SaleAmt 01/01/2003 5 01/15/2003 10 02/26/2003 5 02/27/2003 7 In this query, I want to return 02/26/2003 because there have been more than 30 days since the last sale. I also only want the result to show the last time there were 30 days... previous ones do not matter. Is this possible?

05-25-2006, 10:25 PM	#2 (permalink)
insidious_machinae Psycho	Assuming you're using PHP, loop through the results and call strtotime on the SaleDate values, then put the results into an array. Then, using the unix timestamps, loop backwards through the loop looking for a delta >= 30 days (in seconds). When you find a pair of values which match this condition, you can break out of the loop and use whatever your incrementer value was to perform whatever operations you're wanting to do. Last edited by insidious_machinae; 05-25-2006 at 10:28 PM..

05-26-2006, 09:47 AM	#4 (permalink)
Dilbert1234567 Devils Cabana Boy Location: Central Coast CA	you can do it in a query, I’m working on it, take the top date and compare it to the current date getdate() by year and month and day, year(getdate()) month(getdate()) and day(getdate()). Then after that you would have to compare the table against it self, looking for when there are 2 dates that are within 30 days of each other, and returning all other dates that aren't Give me a bit of time on the code, finding people who have not made a sale in 30 days is easy, the other bit is much trickier, it may be beyond me. __________________ Donate Blood! "Love is not finding the perfect person, but learning to see an imperfect person perfectly." -Sam Keen

05-26-2006, 11:03 AM	#5 (permalink)
Dilbert1234567 Devils Cabana Boy Location: Central Coast CA	Code: select * from testdate where ( ((year(SaleDate )-1)365)+ (month(SaleDate )-1)30+ (day(SaleDate )) ) < ( ((year(getdate())-1)365)+ (month(getdate())-1)30+ (day(getdate()))-30 ) its not perfect, but its close this will return the dates that are about 30 days old. its all in MSSQL so you'll have to convert it to your flavor of SQL __________________ Donate Blood! "Love is not finding the perfect person, but learning to see an imperfect person perfectly." -Sam Keen

05-26-2006, 11:09 AM	#6 (permalink)
leftwingx Upright Location: Calgary, AB	I can only comment based on a SQL Server background but you should be able to do it if you can a) Create a temp table or memory table (easiest) or b) use a cursor. This is necessary to create the sub selects and their respective ID's. Then you can use something like this to link your sub queries together (note: @dates is either a temporary table built using your data table and either ): SELECT TOP 1 d1, d2, d1 - d2 [difference] FROM (SELECT [ID = AUTONUMBER],Date1 [d1] FROM @dates WHERE Date1 NOT IN (SELECT MIN(Date1) FROM @dates)) t1 INNER JOIN (SELECT [ID = AUTONUMBER],Date1 [d2] FROM @dates WHERE Date1 NOT IN (SELECT MAX(Date1) FROM @dates)) t2 ON t1.ID = t2.ID WHERE d1 - d2 > 30 ORDER BY d1 DESC So where I wrote [ID = AUTONUMBER] this is an ID that is generated via cursor or as an auto-generated field if you use a temp table. I used this just to illustrate the basic structure of the query not knowing what your capabilities are with your software platform. __________________ Happiness is just a roughing penalty away.

05-26-2006, 09:32 AM	#3 (permalink)
intecel Psycho	I forgot to mention that i'm using Firebird with Delphi. I was hoping there was something I could do without actually storing values or using code to run through the query. There's about 4 million lines in this query between around 5000 companies. It's an insanely long process.