How to insert and calculate the amount ttc in sqlserver

ma ouml

9y1.1k

Hi friends ,

I have three table Countdown, Walk, Bon de reception

The Id_marche is a foreign key in the countdown table and

The Receipt table contains the Id_marche

I want to insert in recordings in the counting table which contains Amount TTC so to calculate Amount TTC = TVA* Amount HT

I have the TVA AND the Amount HT in the voucher table of reception

I could not create a request to insert and calculate the amount TTC

I do not know is what I described well my condition but hey I will screenshot the three table and you will see the relationship between them

And thank you for you in advance

8 Replies

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

Nitin Sontakke9y ago
When you say "It doesn't work" how are we suppose to know what is going wrong? You only have to give us input. Tell us error you are getting. Tell us you are not getting any error but record doesn't get inserted.

Did you try just the select, forget the insert part?

Why not assign the value to a string variable and then print the value of that variable?

How we will know what are the values of the text boxes you have used?

And in the absence of such vital information, how can we possibly help you?
0
ma ouml9y ago
i tried this script but it's doesn't work

insert into decompte
(Id_marche,Num_decompte,Date_etablissement,Flag_dernier,Montant_ttc,Retenue_garantie,Penalite_retard,Revision_prix,
Autres_retenues,Retenue_source,User_create,Date_create)
values
((select TOP 1 Id_marche from marche where Num_marche='"+textBox1.Text+"'),
'"+textBox3.Text+"','"+dateTimePicker1.Value.Date+"','"+cocher+"',
( select m.Id_marche, sum(brm.Montant) * sum(brm.TVA) from marche m left outer join bon_reception_marche brm on m.Id_marche = brm.Id_marche where Num_marche='"+textBox1.Text+"' group by m.Id_marche),
"+Convert.ToDouble(textBox4.Text)+","+Convert.ToDouble(textBox4.Text)+","+Convert.ToDouble(textBox4.Text)+",
"+Convert.ToDouble(textBox4.Text)+","+Convert.ToDouble(textBox4.Text)+","+Convert.ToDouble(textBox4.Text)+",
'"+values.username+"',DateTime.Now.Date)

0
Bikesh Srivastava9y ago
Can you shared waht you have tried ,becz it may be easy to understand exact problem.
0
ma ouml9y ago
THANKS FOR YOUR SUPPORT
0
Gopal Sharma9y ago
Please validate you requirement..

You have three tables
Decomte
Marche
bon_reception_marche

and order is
1.Marche
2.Decomte (where in Decomate Table ID_Marche is unique key) (Base Table)
3.bon_reception_marche (Transaction Table)
Where Decomate is Base Table
and bon_reception_marche is Transaction Table
on any insert/update/delete you want to update Montant_TTC column in Decomate Table

0
Ramesh Barik9y ago
DECLARE @Id_marche int = 8;
INSERT INTO decompte (Id_marche, Montant_ttc)
SELECT
marche.Id_marche
,SUM(b.Montant) * SUM(b.TVA) as TTC
FROM marche WITH (NOLOCK)
LEFT JOIN bon_reception_marche b WITH (NOLOCK)
ON marche.Id_marche = brm.Id_marche
WHERE marche.Id_marche = @Id_marche
GROUP BY marche.Id_marche
0
Nitin Sontakke9y ago
And in case you wish to group them together use sum as shown below:
insert into decompte (Id_marche, Montant_ttc)
select m.Id_marche, sum(brm.Montant) * sum(brm.TVA)
from marche m
left outer join bon_reception_marche brm on m.Id_marche = brm.Id_marche
where m.Id_marche = ?
group by m.Id_marche
You can always comment out the first line with -- and just use the select to see what you are getting and is it correct.
0
Nitin Sontakke9y ago
Next time, please paste the text from the query analyzer here in addition to screen shot, please. Easy for us to copy and paste table names, column names, etc. Hope you get the point.

May be you want something like following:
insert into decompte (Id_marche, Montant_ttc)
select m.Id_marche, brm.Montant * brm.TVA
from marche m
left outer join bon_reception_marche brm on m.Id_marche = brm.Id_marche
where m.Id_marche = ?
Substitute ? with proper value or a parameter.
I am assuming that Id_decompte is an Identity column.
0