如何从另一个表中获取列并插入到其他表中？

问题描述

大家好，

我有两个表'Cal_date'和'RPT_Invoice_Shipped'。

表cal_data有列month_no，start_date和end_date。并且表RPT_Invoice_Shipped具有列Day_No，Date，Div_code，Total_Invoiced，Shipped_Value，Line_Shipped，Unit_Shipped，Transaction_Date。



我使用下面的insert statment在RPT_Invoice_Shipped表中插入数据。





插入[Global_Report_Staging]。[dbo]。[RPT_Invoice_Shipped]

（Day_No，Date，Div_code，Total_Invoiced，Transaction_Date）

select，CONVERT（DATE，Getdate） （））作为日期，LTRIM（RTRIM（div_Code）），

sum（tot_Net_Amt）为Total_Invoiced，（dateadd（day，-1，convert（date，getdate（）））） >来自[Global_Report_Staging]的
。[dbo]。[STG_Shipped_Invoiced]

WHERE CONVERT（DATE，Created_date）= CONVERT（DATE，Getdate（））

group通过div_code



在RPT_Invoice_Shipped表中插入Day_No列时，我必须使用公式（Transaction_Date-start_date + 1），其中来自STG_Shipped_Invoiced的Transaction_Date和来自Cal_date表的start_date。我使用的是datepart（mm，Transaction_Date）所以它给了month_no，而这个月_no我们可以加入Cal_date表的month_no并从Cal_date表中获取start_date，这样我们就可以使用start_date作为公式（Transaction_Date-start_date + 1）。 />


但是我很难在上面的查询中安排这个。能否指导我如何实现这个目标？



提前致谢

Hi All,
I have two table 'Cal_date' and 'RPT_Invoice_Shipped'.
Table cal_data has column month_no, start_date and end_date. And table RPT_Invoice_Shipped has columns Day_No, Date, Div_code, Total_Invoiced, Shipped_Value, Line_Shipped, Unit_Shipped, Transaction_Date.

I am using below insert statment to insert data in RPT_Invoice_Shipped table.


insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped]
(Day_No, Date, Div_code, Total_Invoiced, Transaction_Date)
select , CONVERT(DATE,Getdate()) as Date, LTRIM(RTRIM(div_Code)),
sum(tot_Net_Amt) as Total_Invoiced, (dateadd(day, -1, convert(date, getdate())))
from [Global_Report_Staging].[dbo].[STG_Shipped_Invoiced]
WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate())
group by div_code

while inserting in column Day_No in RPT_Invoice_Shipped table, I have to use formula (Transaction_Date-start_date+1) where Transaction_Date from STG_Shipped_Invoiced and start_date from Cal_date table. I was using datepart (mm, Transaction_Date) so it gives month_no, and this month_no we can join with month_no of Cal_date table and fetch start_date from Cal_date table, so that we can use start_date for formula (Transaction_Date-start_date+1).

But I am getting difficulty to arrange this in above query. Can you please guide me how to achive this?

Thanks in advance

推荐答案

insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped]
(Day_No, Date, Div_code, Total_Invoiced, Transaction_Date)
select cal_data.month_no,CONVERT(DATE,Getdate()) as Date, LTRIM(RTRIM(div_Code)),
sum(tot_Net_Amt) as Total_Invoiced, (dateadd(day, -1, convert(date, getdate())))
from [Global_Report_Staging].[dbo].[STG_Shipped_Invoiced] SSI
Inner join cal_data ON datepart(mm, SSI.Transaction_Date) = cal_data.month_no
WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate())
group by div_code

现在您可以使用两个表中的任何一列。它会对你有用吗？



如果您有任何疑虑或疑问或我遗失了什么，请告诉我。



谢谢

Advay Pandya

Now you can use any of the column from both of the table. Will it work for you ?

Please let me know if you have any concern or query or I am missing something.

Thanks
Advay Pandya

这篇关于如何从另一个表中获取列并插入到其他表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！