i have table Like that :- P_id, R_Number, Date_Reservation ,Exame_id , Doctor_id
P_id is FK ---- -------- ---------------- -------- ---------
50 , 1 , 20-2-2016 , 1 , 2
20 , 2 , 20-2-2016 , 1 , 1
60 , 3 , 20-2-2016 , 1 , 3
50 , 1 , 21-2-2016 , 2 , 4
30 , 2 , 21-2-2016 , 2 , 2
this is my insert query to add new P_id :-
insert into ResrvationData (P_id ,R_Number ,Date_Reservation ,Exame_id , Doctor_id)
values (1 ,(select MAX(R_Number) from ResrvationData where Date_Reservation = '2016-02-22' and Exame_id = 1 ) +1 ,'2016-02-22' ,1 ,1 )
this new P_id become :- 1 , Null , 22-2-2016 , 2 , 3
i want make R_number count using 2 values in Date_Reservation and exam_id
What I have tried:
How i solve this problem when i add a new P_id