Desktop productivity for business analysts and programmers

Query for next three days if two set of dates are equal

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Query for next three days if two set of dates are equal

I want to run a query based on the logic below:

If  t1.PlayerDayDate=t2.NewEnrollmentDate then get the next three days of  t1.PlayerDayDate of that respective patron.

I want to see when a customer enrolled on that day and play (hence playdate=new enrollmentdate) then also get the next three days transaction or play days.

Thanks


Accepted Solutions
Solution
‎05-26-2015 10:36 PM
Respected Advisor
Posts: 4,606

Re: Query for next three days if two set of dates are equal

I hope this can be done in EG. Assuming both your dates are datetimes, use INTCK to count the number of days between play and enrolment :

proc sql;

create table nextDays as

select t2.newEnrolmentDate, t1.*

from

  PlayerDays as t1 inner join

  Enrolments as t2 on

       t1.guestNumber=t2.guestNumber and

       intck("DTDAY", t2.newEnrolmentDate, t1.PlayerDayDate) between 0 and 3;

quit;

(untested)

PG

PG

View solution in original post


All Replies
Grand Advisor
Posts: 10,210

Re: Query for next three days if two set of dates are equal

You should provide a couple of small example data sets and what the desired output would look like.

Frequent Contributor
Posts: 104

Re: Query for next three days if two set of dates are equal

Hi,

Below are the results of filtering t1.PlayerDayDate=t2.NewEnrollmentDate.  Now I want the next three PlayerDayDate since the day that they enrolled.

Data are taken from WORK.QUERY_FOR_TOTALGAMINGADDRES_0001

Capture.PNG

I hope this helps.

Solution
‎05-26-2015 10:36 PM
Respected Advisor
Posts: 4,606

Re: Query for next three days if two set of dates are equal

I hope this can be done in EG. Assuming both your dates are datetimes, use INTCK to count the number of days between play and enrolment :

proc sql;

create table nextDays as

select t2.newEnrolmentDate, t1.*

from

  PlayerDays as t1 inner join

  Enrolments as t2 on

       t1.guestNumber=t2.guestNumber and

       intck("DTDAY", t2.newEnrolmentDate, t1.PlayerDayDate) between 0 and 3;

quit;

(untested)

PG

PG
Frequent Contributor
Posts: 104

Re: Query for next three days if two set of dates are equal

Thank you.  You solved my problem.

Trusted Advisor
Posts: 1,052

Re: Query for next three days if two set of dates are equal

I haven't tested it, but I believe it would be doable with the EG query builder.

Tom

Frequent Contributor
Posts: 104

Re: Query for next three days if two set of dates are equal

Thank you!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 461 views
  • 0 likes
  • 4 in conversation