How to solve the parameter problem in sql server

Question

Hi friends

I have a table named "attachment_marche"

create table attachement_marche

(

Id_attachement_marche int primary key identity NOT NULL,

Id_decompte int foreign key references decompte (Id_decompte),

Id_bon_reception_marche int foreign key references bon_reception_marche(Id_bon_reception_marche),

Id_marche int foreign key references marche(Id_marche),

Num_attachement varchar(100) COLLATE French_CI_AS,

Date_etablissement date,

Date_debut date,

Date_fin date,

Flag_dernier varchar(20)COLLATE French_CI_AS,

Montant float,

Ind_supp int DEFAULT 1,

User_create varchar(100) COLLATE French_CI_AS NOT NULL,

Date_create datetime DEFAULT getdate() NOT NULL,

User_modif varchar(100) COLLATE French_CI_AS ,

Date_modif varchar(100) COLLATE French_CI_AS

)

 

And a table named bon_reception_marche

create table bon_reception_marche

(

Id_bon_reception_marche int primary key identity NOT NULL,

Id_marche int foreign key references marche(Id_marche),

Designation_bon_reception varchar(100) COLLATE French_CI_AS,

Num_bon_reception varchar(100) COLLATE French_CI_AS,

Date_commande date,

Date_reception date,

Unite varchar(50) COLLATE French_CI_AS,

Qte float,

Prix_unitaire float,

Montant float,

TVA float,

MO varchar(100) COLLATE French_CI_AS,

OT varchar(100) COLLATE French_CI_AS,

Ind_supp int DEFAULT 1,

User_create varchar(100) COLLATE French_CI_AS NOT NULL,

Date_create datetime DEFAULT getdate() NOT NULL,

User_modif varchar(100) COLLATE French_CI_AS ,

Date_modif varchar(100) COLLATE French_CI_AS,

)

I want to display the vouchers of receipts which are between two dates and which are not affected to any attachment of the market

I tested this request but did not work

select * from bon_reception_marche where

Id_bon_reception_marche NOT IN

(select Id_bon_reception_marche from attachement_marche where Id_marche in

(select Id_marche from marche where Num_marche = 'DG/01/2014'))

and Date_reception between '2016-07-07' and '2017-06-19' and

Id_marche in (select TOP 1 Id_marche from marche where Num_marche = 'DG/01/2014')

 

And thanks in advance friends