How to insert and calculate the amount ttc in sqlserver

Question

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

Nitin Sontakke · Answer

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?

ma ouml · Answer

i tried this script but it's doesn't work insert into decompte (Id_marcheNum_decompteDate_etablissementFlag_dernierMontant_ttcRetenue_garantiePenalite_retardRevision_prix Autres_retenuesRetenue_sourceUser_createDate_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)

Bikesh Srivastava · Answer

Can you shared waht you have tried becz it may be easy to understand exact problem.

ma ouml · Answer

THANKS FOR YOUR SUPPORT

Gopal Sharma · Answer

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

Ramesh Barik · Answer

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

Nitin Sontakke · Answer

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.

Nitin Sontakke · Answer

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.