Solved

SQL Query to Show data within 60 Days

Posted on 2014-12-22
23
224 Views
Last Modified: 2014-12-22
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


Saurabh..
0
Comment
Question by:Saurabh Singh Teotia
  • 12
  • 7
  • 3
23 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40512801
You could use (air code):

Select
    Customer_ID,
    Sales_Date,
    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")
From
    tblYourTable

/gustav
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 40512823
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.

Saurabh...
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40512827
What database server are you querying?  (Access / MS SQL / MySql?)
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 40512831
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40512835
So why have the tagged your question with "Microsoft Access"?

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

/gustav
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 40512837
Gustav,

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.

Saurabh..
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40512851
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40512865
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.

/gustav
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40512873
Might be something like

SELECT 
   Customer_ID,
    Sales_Date, 
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 
FROM 
    tblYourTable

Open in new window

0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40512878
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...
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 40512946
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..

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

Open in new window


Now when tb4.id 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??

Saurabh..
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40512973
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.
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40512996
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.
0
 
LVL 7

Accepted Solution

by:
Robert Sherman earned 500 total points
ID: 40513019
Here's a general idea of what I was thinking:  

SELECT t1.id, 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 id = t1.id) t2 
WHERE t1.salesdate - t2.previousSale > 60

UNION 

SELECT t1.id, 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 id = t1.id) t2 
WHERE t1.salesdate - t2.previousSale <= 60

Open in new window

0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 40513022
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.
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40513031
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.
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40513040
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.
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 40513047
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..
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40513054
.. You can also add the salesman into the subquery that is to the right of the LEFT JOINs in my query above..
0
 
LVL 59

Author Closing Comment

by:Saurabh Singh Teotia
ID: 40513057
Thanks this works about what i'm trying to do..
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40513069
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.id, 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 = t1.id) t2 
WHERE t1.salesdate - t2.previousSale > 60

UNION 

SELECT t1.id, 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 = t1.id) t2 
WHERE t1.salesdate - t2.previousSale <= 60

Open in new window

0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40513079
Awesome.. glad I could help!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

774 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question