How to insert and calculate the ttc amount in sqlserver

Question

0.00/5 (No votes)

See more:

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

What I have tried:

SQL

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 TOP 1 marche.Id_marche from marche , bon_reception_marche br ,decompte where br.Id_marche=marche.Id_marche and decompte.Montant_ttc=br.Montant*br.TVA),
"+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)

Posted 21-Mar-17 12:21pm

Member 13055644

Updated 21-Mar-17 23:04pm

CHill60

v2

Add a Solution

Comments

[no name] 21-Mar-17 19:56pm

If you don't listen to what people tell you, we end up just wasting our time.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ramesh Kumar Barik · Accepted Answer · 2017-03-21T23:04:00

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