Solved

SQL Query to Show data within 60 Days

Posted on 2014-12-22
23
216 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
Comment Utility
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
Comment Utility
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
Comment Utility
What database server are you querying?  (Access / MS SQL / MySql?)
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Expert Comment

by:Robert Sherman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
.. 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
Comment Utility
Thanks this works about what i'm trying to do..
0
 
LVL 7

Expert Comment

by:Robert Sherman
Comment Utility
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
Comment Utility
Awesome.. glad I could help!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now