Apply Commission based on Date Range

Posted on 2005-05-16
Last Modified: 2010-03-19

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.

Question by:NickHoward
    LVL 23

    Accepted Solution


    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

    LVL 34

    Expert Comment

    by:Brian Crowe
    INNER JOIN s_TrackRealCDR
         ON CALCULATIONS.[Date] BETWEEN s_TrackRealCDR.SDate AND s_TrackRealCDR.EDate

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now