Limiting inner join in mysql

Question

Table A

Calid |        EffectiveFrom  
---
2       |     10 Jun 2016
---
5       |     20 Nov 2016
------
7       |    05 Jan 2017
--

Calid 2 is to be followed by a person from 10 Jun onwards , from 20 Nov calid 5 is to be followed and so on....

Table B contains details of the calid (all working dates) .

Table B 
CAlid  |  WorkingDate
2      |  1 Jan 2016
2      |  2 Jan 2016
2      |  4 Jan 2016 till 31 Dec all working dates are present
5      |  1 Jan 2016
5      |  2 Jan 2016
5      |  3 Jan 2016
5      |  5 Jan 2016 till 31 dec all working dates are present

and so on all working dates of calid 7 are present. A report is to be generated to find total working days for the person who followed calid as in Table A. Report should contain days like Calid | Date (from Table B). This is to be achieved with select statement (no procedure since it's part of view). In table A i don't have effectiveTo date column.

Details: Table B contains all the working days (read dates) of a given year. Calid is calendar id for a particular calendar. Say calid 2 means list of all the working days in Japan and Calid 5 gives the lists all the working days/dates for a year in Dubai. An employee moves across different locations from Japan to Dubai so his/her working days change accordingly. At the end of the year, it is to be calculated how many days was the employee supposed to work. Table A contains the history of the employee calendar movements during an year. An entry is added on the day on which an employee moves to another location and start following different location calendar. The EffectiveFrom tells the date from which a particular calendar is followed. This piece of logic is to be put together with another code in a view so restrictions of a view are intact.

I’ve edited the question to make it clear as commented by @philipxy .

Your post is not clear. Please use full simple sentences, not fragments like "(all working dates)" & "till 31 Dec all working dates are present". We must *guess* at what you are trying to say. Is table B just for 2016? What does it have to do with A? Is it A 2016 info but with every workday listed? Then why don't 2 & 5 start in Jun & Nov? What does "(no procedure since it's part of view)" mean? Why say "In table A i don't have effectiveTo date column"?--we know. It is also not clear when a row goes into A, B or the result. And--what is your question? What parts can you do? Where are you stuck? — philipxy, Feb 17 '17 at 10:31
@KaranShah i'm very close to getting it done...Hurray! CTE is the clue. Shall provide you final update on Monday. Gn. — EetSandhu, Feb 18 '17 at 16:50
@philipxy Seems like your questions helped me get an answer, thanks to you! Also, I've edited the question text to answer your queries — EetSandhu, Feb 23 '17 at 07:36
A case of "rubber ducking"? People very seldom improve their posts when they are unclear. Yours was so unclear that I downvoted it. Almost never does a question that I downvoted get edited, let alone to enough clarity that I want to undo the downvote, let alone to enough clarity that I want to upvote. So congratulations on effort. (This could be more concise though.) To see how to concisely clearly explain "when a row goes into A, B or the result" see my answers re *predicates*. Eg [this](https://stackoverflow.com/a/42289340/3404097) & [this](https://stackoverflow.com/a/33952141/3404097). — philipxy, Feb 23 '17 at 08:06

score 0 · Accepted Answer · answered Feb 20 '17 at 09:40

Finally i got it ! CTE

WITH Cte (EmpNo, calid, dtEffectiveDate,number)AS
         (SELECT EmpNo, calid, dtEffectiveDate,row_number() over(order by EmpNo,dtEffectiveDate)
               FROM TableA WHere EmpNo in (1741285 ) 
         )
         , OuterCte (EmpNo, calid, dtEffectiveDate,number,dtEffectiveToDate) As
         (Select d.*,IsNull(d2.dteffectiveDate-1,Convert(Date,DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()) + 2, -1)) ) as dtEffectiveToDate
           from Cte d
           left outer join Cte d2 on d.number = (d2.number-1)
         )
    SELECT  EmpNo,WorkingDate,WorkingDescription
    FROM      OuterCte CAL  ON ELA.EmpNo = CAL.EmpNo        
    INNER JOIN TableB HCD WITH(NOLOCK)    ON HCD.calid = CAl.calid
    AND  HCD.dtHolidayDate between cal.dtEffectiveDate and cal.dtEffectiveToDate

Limiting inner join in mysql

1 Answers1