Apply Commission based on Date Range

Hi,

I have 2 tables;

Table1: CALCULATIONS containing fields [Date], [time], Final_Destination, pstn
Table2: s_TrackRealCDR containing fields DateID, SDate, EDate, Commission

I want to make a VIEW where I want CACULATIONS table to display together with Commission column from s_TrackRealCDR table where Date is between EDate (start date) and  EDate (end date).

I hope I am able to explain what I need. Nevertheless I am available to give some examples.

Nick
NickHowardAsked:
Who is Participating?
 
adatheladConnect With a Mentor Commented:
Hi,

It sounds like this is what you need:

SELECT c.Date, c.Time, c.Final_Destination, c.pstn, (SELECT TOP 1 t.Commission FROM s_TrackRealCDR t WHERE c.Date BETWEEN t.SDate AND t.EDate ORDER BY t.SDate) AS Commission
FROM CALCULATIONS c

0
 
Brian CroweDatabase AdministratorCommented:
SELECT CALCULATIONS.[Date],
     CALCULATIONS.[time],
     CALCULATIONS.Final_Destination,
     CALCULATIONS.pstn,
     s_TrackRealCDR.Commission
FROM CALCULATIONS
INNER JOIN s_TrackRealCDR
     ON CALCULATIONS.[Date] BETWEEN s_TrackRealCDR.SDate AND s_TrackRealCDR.EDate
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.