Link to home
Start Free TrialLog in
Avatar of Saurabh Singh Teotia
Saurabh Singh TeotiaFlag for India

asked on

SQL Query to Show data within 60 Days

Hi All,

I'm trying to extract data from customer sales table where if the customer is placing sales for the first time it should show as Y basis of sales date but if the same customer return back within 60 days of sales date i want all the entries show as N,However if it returns > 60 days the entry should be Y.

Customer_ID	        Sales_Date
1	                        10-Jan-14
1	                        15-Jan-14
1	                        17-Mar-14
1	                         15-Apr-14

Open in new window

The answer that i'm looking for..

Customer_ID	          Sales_Date	        Answer
1	                          10-Jan-14	          Y
1	                          15-Jan-14	          N
1	                          25-Mar-14	          Y
1	                          15-Apr-14	          N

Open in new window

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

You could use (air code):

    IIf(DateDiff("d", Nz((Select Max(Sales_Date) Form tblYourTable As T Where T.Sales_Date < tblYourTable.Sales_Date And T.CustomerID=tblYourTable.Customer_ID),Sales_Date), Sales_Date) > 60, "N", "Y")

Avatar of Saurabh Singh Teotia


Hi  Gustav

Thanks for the reply but the SQL interface which i'm using dont support IIF function as it doesn't exist in the same.

What database server are you querying?  (Access / MS SQL / MySql?)
I'm working on Netezza Database using Aginity frontend where most of SQL commands run leaving some functions which are only  available in SQL/Access.

I can do my joins and other things like SQL.
So why have the tagged your question with "Microsoft Access"?

In T-SQL you can replace IIf with a CASE expression.


The reason why i tagged this in Access i can write my queries in SQL formatting baring one or two functionality which is limited to that software only.

Judging by the Netezza SQL Reference I'm looking at right now, it's a little bit more complex than 'one or two functionality' differences.  :D
OK. I don't know anything about Netezza ...

You'll equivalents to IIf, DateDiff, and Nz.

IIf: From a boolean expression, choose either of two expressions.
DateDiff: Find the count of days between two date values.
Nz: To replace a Null value with something else.

Should be doable.

Might be something like

CASE WHEN duration_subtract(
           Select Max(Sales_Date) FROM tblYourTable As T Where T.Sales_Date < tblYourTable.Sales_Date And  
                         T.CustomerID=tblYourTable.Customer_ID, tblYourTable.CustomerID ) > 60 THEN 'Y' 
          ELSE 'N' END 

Open in new window

Do note that I'm just going on a quick perusal of the online reference for Netezza...  also, the "duration_subtract" function seems to be part of the "SQL Extensions" package so it likely depends on having that installed on top of the base database install.

Perhaps what I've provided above can at least get you pointed in the right direction.. if you get any specific errors that you don't understand as a result, post them up and either myself or someone more knowledgeable with that product will try to help further...
Hi All,

First of all sorry for confusion...What i was looking for basic idea to write my SQL logic as i was getting struck to define the logic for it.. As once i'm able to define the logic i can re-write the query myself. I should be more specific about the same at the first case itself..Apologies for not being so..

Second the Netezza Environment that i'm working is yes different then the sql/access environment but like i can tweak the query to do my necessary result that i'm looking for..

Also i don't have duration_subtract present in my aginity which i'm using to access the netezza Environment...

At the last i ended up writing the below mentioned query to do this... If you know a better example to do this let me know...will incorporate that..presently i'm doing this only for 1 account that's why i have that filter built in the query..

from customersales tb3
left join (SELECT DISTINCT,tb1.salesdate,tb1.salesman
from customersales as tb1
left join customersales as tb2 on and tb2.status='Sales'
WHERE'1' AND tb1.STATUS='Sales' 
AND TB2.Salesdate >= TB1.salesdate - 60  AND TB2.salesdate < TB1.salesdate) TB4 on = and TB4.saledate = TB3.salesdate
WHERE'1' AND tb3.STATUS='Sales' 
ORDER BY tb3.Salesdate 

Open in new window

Now when is null i know that value is either fresh value or not within the range i'm looking for...

Is their a better way to write the above query??

Do you have the age() function?  (I bet not since I think it is a part of the same extension pack)

And since what you are looking for is two distinct groups for your final output (Those marked 'Y' and those marked 'N' from your initial example), you might be able to get to that result by doing a UNION query of two separate SELECTs where one has the WHERE clause for the 'Y' cases with a literal 'Y' in a column and the other for the remaining with a literal 'N' in the same column place.

EDIT: This is to say, following the long SQL statement you just posted, it would be a similar matter of complex WHEREs to get at the specific records for each set.
Just another thought... I think you still want to be using MAX(salesdate) < thisRecord.salesdate (pseudo-code there, not actual), so that for each record you are pulling in a column that looks for the Maximum date for that customer that is LESS THAN the current record your query is processing.
Avatar of Robert Sherman
Robert Sherman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Robert,

Thanks for the input and i did looked at my logic and no i don't want to count from the max date i want to basically count from his last date when the sale is been made..

So for Instance if the sales is made on 1st Jan by Salesman-X on account 1 from that his time starts if he makes the sale again on the same account with in 60 days it doesn't count..However if he makes the sales on the 61th day it counts... In this query you will just see the filter on date and account..When i wrote my query i further applied filter on salesman...

So basically i won't to compare sales from previous date rather then looking at max date.
Of course, you'll need to add some null-value handling in there as well..  but I think the general idea I presented might be a little more manageable than having to join 4 copies of the same table..   I'll try to help more later today if you're still stuck.
the idea of using max is combined with the date of the current record of the query, thus it should be thought of as MAX date prior to the one I'm looking at now from the perspective of the query.
Ahh my bad sorry now i noticed what you are doing in the query when i re-read again..You are finding a max date basically for the entries which are smaller then that..But one of the reasons again if i use max date i also need to combine the salesperson...

I have pretty much solved this query as the one that i wrote is doing the results what i was looking for..

Basically i have this huge sales tables where i'm trying to run some sales analysis by the sales person did on the same account within 60 days to see how many orders which im getting are handled by the same sales person from a commission prospective and how much money i'm paying on this duplicate orders which emerge on the same account within 60 days..However on the same account if it's a different sales person i'm fine with it..
.. You can also add the salesman into the subquery that is to the right of the LEFT JOINs in my query above..
Thanks this works about what i'm trying to do..
Actually, is the field "id" from t1 the key for that table or is it a foreign key such as "customerid"?  I think it represents customerid, but I'm getting a bit confused myself, to be honest. :D

SELECT, t1.salesdate , t1.salesman, t2.previousSale, 'Y' as Answer FROM customersales t1 
LEFT JOIN (SELECT max(salesdate) AS previousSale from customersales WHERE salesdate < t1.salesdate AND salesman = t1.salesman AND id = t2 
WHERE t1.salesdate - t2.previousSale > 60


SELECT, t1.salesdate , t1.salesman, t2.previousSale, 'N' as Answer FROM customersales t1 
LEFT JOIN (SELECT max(salesdate) AS previousSale from customersales WHERE salesdate < t1.salesdate AND salesman = t1.salesman and id = t2 
WHERE t1.salesdate - t2.previousSale <= 60

Open in new window

Awesome.. glad I could help!