Click here to Skip to main content
14,694,465 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Data of Element table
ID_element Element1 Element2 DATE_Ele ID_Personne
        1       NULL     25      2002      1
        2       46     NULL      2003      1
        3       2      NULL      2004      1
        4       3       NULL     2005      1
        5       5      NULL      2006      1
        6      178      NULL    2007       1

DATA of table Salary
ID_Personne       Date
1                  2007

I do the join between the two table ID_Personne the link and I took the two date corresponding like this
select ID_Personne,Date_Per,Element1 ,Element2 ,DATE_Ele       
from Personne as P
Inner Join element as c
ON C.ID_Personne=P.ID_Personne
AND Date_Per=DATE_Ele

So in our example 2007 trigger the departure of the loop to decrement-1 but we must verify that in 2007 if element1 is not null and element2 is null, as long as this condition is verified, continue to decrement the date_Pers -1 which is always equal to the date_Eleme until the condition is respected, I mean , in this example 2002 does not respond to our criterion because element1 is null and is not null element2 so we stopped our loop.

What I have tried:

this is my query but it does not decrement the Date . Any help would be much appreciated
;With CTE_AS
AS 
(
       Select  Distinct  Dem.IdPersonne, Element1,Element2,Date_Pers as AnneeFct
     --  into #test
           From  element AS Dem 
                Inner Join Element as cpr WITH (NOLOCK)
                ON      IdPersonne = .IdPersonne
              AND Date_Pers =Date_Ele
              where Element1is not null and Element2 is null
       UNION ALL
              Select  Distinct  Dem.IdPersonne, Element1,Element2, AnneeFct
     --  into #test
           From  CTE_AS AS Dem 
                Inner Join Element as cpr WITH (NOLOCK)
                ON      Id_Personne = .Id_Personne
              AND Date_Pers =Date_Ele
              AND Date_Pers = AnneeFct-1
              where Element1is not null and Element2 is null
)
Select  Distinct  Dem.IdPersonne, Element1,Element2, AnneeFct
FROM         CTE_AS 
OPTION (MAXRECURSION 100)
Posted
Updated 4-Nov-16 7:06am
v4
Comments
Wendelius 4-Nov-16 12:08pm
   
In your example, what is the expected output?
Member 10971811 4-Nov-16 14:01pm
   
the expected data is to get that result after the condition is not respected like this data.
ID_element Element1 Element2 DATE_Ele ID_Personne
1 NULL 25 2002 1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900