Help guys with the request. There are 2 tables. The 1st table has a date start dateStart and ID 2nd ID and 4 date fields. datePlanStart, dateFactStart, datePlanEnd, dateFactEnd

How to make a request that will issue an id for which dateStart will not intersect with dates from the second table (not included in the datePlanStart interval, dateFactStart, datePlanEnd, dateFactEnd). At what dateFact (the beginning and the end) will be more priority than datePlan. Those. this is the date after the fact (what happened), but not planned, but the dates of fact may not yet be filled. Example

id dateStart 1 05.03.19 2 01.03.19 3 02.03.19 4 28.02.19 id datePlanStart dateFactStart datePlanEnd dateFactEnd 1 12.02.19 11.02.19 02.03.19 04.03.19 2 14.02.19 12.02.19 08.03.19 01.03.19 1 12.02.19 11.02.19 02.03.19 null 2 14.02.19 null 27.02.19 null

must find id = 1, because (start 11.02, and end 04.03) and id = 4 (dateStart> datePlanEnd), and id = 2 will not choose, because dateFactEnd = dateStart (must <=), id = 3 will not choose because datePlanEnd = dateStart

the query is working, but probably it can be improved like that

The request may take some time, so I made the requestStartDate and requestFinishDate dateStart

 select v.ID from tab1 v left join tab2 t on t.ID=v.ID where (@requestStartDate) between isnull(t.dateFactStart, t.datePlanStart) and isnull(t.dateFactEnd, t.datePlanEnd) and (@requestFinishDate) between isnull(t.dateFactStart, t.datePlanStart) and isnull(t.dateFactEnd, t.datePlanEnd) or (@requestStartDate)<= isnull(t.dateFactStart, t.datePlanStart) and (@requestFinishDate)>=isnull(t.dateFactEnd, t.datePlanEnd) or (@requestStartDate)<= isnull(t.dateFactStart, t.datePlanStart) and (@requestFinishDate)between isnull(t.dateFactStart, t.datePlanStart) and isnull(t.dateFactEnd, t.datePlanEnd) or (@requestStartDate) between isnull(t.dateFactStart, t.datePlanStart) and isnull(t.dateFactEnd, t.datePlanEnd) and (@requestFinishDate)>=isnull(t.dateFactEnd, t.datePlanEnd)"
  • one
    "... I need to make a request .." - hmm, I also need a lot of things. This forum is a place where someone else’s work is not done for free, but help each other. - Alexander Muksimov 1:02 pm
  • It means how to do. This is not a job, this is not a freebie, .... this is just a workout .. I need to understand how it works ... and I train - Elena
  • "... and I train" is commendable, but then you need to attach to the question what you did (the code made by you personally, perhaps not working) during the training. After that, it is already possible to help and suggest what has been done wrong. - Alexander Muksimov
  • added a working query, if someone tells me how to improve it at all WoW - Elena

0