如何在sqlserver中插入和计算ttc数量 [英] How to insert and calculate the ttc amount in sqlserver

问题描述

嗨朋友们，

我有三张桌子Countdown，Walk，Bon de reception

Id_marche是倒计时表中的外键和

收据表包含Id_marche

我想在计数表中插入包含金额TTC的记录，以便计算金额TTC = TVA *金额HT

我有接待凭证表中的TVA和金额HT

我无法创建插入和计算金额的请求TTC

我不知道是我所描述的我的情况，但嘿，我将截图三个表，你会看到他们之间的关系

并提前谢谢你



< b>我尝试了什么：

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:

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)

推荐答案

DECLARE @Id_marche int = 8;

INSERT INTO decompte（Id_marche，Montant_ttc）

SELECT

marche.Id_marche

，SUM（b.Montant）* SUM（b.TVA）为TTC

来自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

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

这篇关于如何在sqlserver中插入和计算ttc数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！