Discussion :

[jubourbon]

Bonjour à tous,

Je développe une base de donnée pour calculer le cout d'un travail selon un contrat.
Pour faire simple, j'ai un contrat qui défini le cout des travaux de façon unitaire. Pour une quantité donnée, ces travaux ont un cout net, au dessus de cette quantité, ils sont majorés.

Par exemple:
Un contrat spécifie qu'il est prévu 5 jours de travaux. Chacun de ces jours coutera 10 euros, au delà ils couteront 20 euros.
Donc si la somme des travaux réalisés est égale à 6 on aura 5x10 +1x20

Voila ce que j'aimerai faire avec une requête. Mais je ne vois pas comment m'y prendre.

Voici les sources de mes tables ainsi qu'un jeux de test:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE TABLE [dbo].[Contrat2](
	[idContrat] [int] NOT NULL,
	[CoutNormal] [float] NULL,
	[CoutSUpplementaire] [float] NULL,
	[QuantiteInitiale] [int] NULL,
 CONSTRAINT [PK_Contrat2] PRIMARY KEY ([idContrat])
) 
 
INSERT [dbo].[Contrat2] ([idContrat], [CoutNormal], [CoutSUpplementaire], [QuantiteInitiale]) VALUES (1, 10, 15, 5)
 
CREATE TABLE [dbo].[Travaux2](
	[IDTravaux] [int] NOT NULL,
	[DateTrav] [date] NULL,
	[CoutTravaux] [float] NULL,
	[IDContrat] [int] NOT NULL,
 CONSTRAINT [PK_Travaux2] PRIMARY KEY CLUSTERED([IDTravaux]),
 CONSTRAINT [FK_Travaux_Contrat2] FOREIGN KEY([IDContrat]) REFERENCES [dbo].[Contrat] ([idContrat])
 )
 
INSERT [dbo].[Travaux2] ([IDTravaux], [DateTrav], [CoutTravaux], [IDContrat]) VALUES (1, CAST(0x75330B00 AS Date), 3, 1)
INSERT [dbo].[Travaux2] ([IDTravaux], [DateTrav], [CoutTravaux], [IDContrat]) VALUES (2, CAST(0x76330B00 AS Date), 3, 1)

Si quelqu'un peut m'indiquer une piste, merci d'avance.

[jubourbon]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
select 
YEAR(dbo.Travaux.DateTrav) AS Annee,
MONTH(dbo.Travaux.DateTrav) AS I_Mois, 
dbo.Contrat.idContrat AS IDContrat,
 'Cout' = case 
WHEN SUM(travaux.CoutTravaux)<=Contrat.QuantiteInitiale THEN SUM(dbo.Contrat.CoutNormal * dbo.Travaux.CoutTravaux)
ELSE (SUM(dbo.Contrat.CoutSUpplementaire * dbo.Travaux.CoutTravaux))
END
FROM Contrat INNER JOIN Travaux ON dbo.contrat.idContrat = dbo.Travaux.IDContrat
Group by
YEAR(dbo.Travaux.DateTrav),
MONTH(dbo.Travaux.DateTrav), 
dbo.Contrat.idContrat,
Contrat.QuantiteInitiale

Voila lrequete que j'ai faite, mais le probleme c'est que c'est soit normal soit supplementaire, et je voudrais faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SUM(dbo.Contrat.CoutNormal * dbo.Travaux.CoutTravaux)

jusqu'a Contrat.QuantiteInitiale et au dela

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(SUM(dbo.Contrat.CoutSUpplementaire * dbo.Travaux.CoutTravaux)

[jubourbon]

Je pense avoir trouvé une solution:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
select 
YEAR(dbo.Travaux.DateTrav) AS Annee,
MONTH(dbo.Travaux.DateTrav) AS I_Mois, 
dbo.Contrat.idContrat AS IDContrat,
 'Cout' = case 
WHEN SUM(travaux.CoutTravaux)<=Contrat.QuantiteInitiale THEN SUM(dbo.Contrat.CoutNormal * dbo.Travaux.CoutTravaux)
ELSE (Contrat.QuantiteInitiale * Contrat.CoutNormal) + ((SUM(travaux.CoutTravaux)-Contrat.QuantiteInitiale) * Contrat.CoutSUpplementaire)
END
FROM Contrat INNER JOIN Travaux ON dbo.contrat.idContrat = dbo.Travaux.IDContrat
Group by
YEAR(dbo.Travaux.DateTrav),
MONTH(dbo.Travaux.DateTrav), 
dbo.Contrat.idContrat,
Contrat.QuantiteInitiale,
Contrat.CoutNormal,
Contrat.CoutSUpplementaire