How to solve the parameter problem in sql server

ma ouml

9y1.1k

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

ma ouml9y ago
Hariharan Krishnamoorthi

Here is the relationship between table "marche" and "attachement_marche" and "bon_reception_marche"

create table marche
(
Id_marche int primary key identity NOT NULL,
Id_site int foreign key references site (Id_site),
Num_marche varchar(100) COLLATE French_CI_AS NOT NULL,
Id_fournisseur int foreign key references fournisseur (Id_fournisseur),
Libelle_marche varchar(100) COLLATE French_CI_AS,
Date_demarrage datetime ,
Date_fin datetime,
Ind_supp INT DEFAULT 1,
User_create varchar(100) COLLATE French_CI_AS NOT NULL,
Date_create datetime DEFAULT getdate(),
User_modif varchar(100) COLLATE French_CI_AS ,
Date_modif datetime,
Type_marche varchar(100) COLLATE French_CI_AS ,
Description varchar(100) COLLATE French_CI_AS ,
Montant float NOT NULL,
Devise varchar(100) COLLATE French_CI_AS,
Budget varchar(100) COLLATE French_CI_AS,
Flag_cloture varchar(1) COLLATE French_CI_AS DEFAULT 'N'
)
Hariharan Krishnamoorthi9y ago
Hi,
But you did not explain about marche table. Please remove or correct it to get the expected result. Or share us info about it.

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')

How to solve the parameter problem in sql server

Hariharan Krishnamoorthi