在我的SQL select语句中汇总并添加Summary Rows [英] Summarise and add Summary Rows in my SQL select statement
本文介绍了在我的SQL select语句中汇总并添加Summary Rows的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的查询是用于汇总表格中的总数,但我需要一些帮助来自定义查询以生成我正在寻找的结果。到目前为止,这是我所拥有的查询,但它缺少2行结算股票和差异
这些是计算行。我如何提出结算股票和差异
< pre class ="prettyprint"> SELECT TxDate,TxStage,ItemCode,ItemName,SupplierWeight,CustomerOrderedWeight,DecanterWeight,
SupplierWeight + CustomerOrderedWeight + DecanterWeight AS Quantity,
CASE WHEN TxStage ='BITUTAINER IN'THEN'Inbound'
WHEN TxStage ='BITUTAINER OUT'那么'出境'
当TxStage ='STOCK TAKE ON'那么'股票买入'
当TxStage ='BOX接受'那么'实物股票'
WHEN TxStage ='DECANTER IN'那么'Decanter'
END作为TypeOfTransaction,
情况当TxStage ='BITUTAINER IN'那么'3'
当TxStage ='BITUTAINER OUT'时那么'5'
当TxStage ='STOCK TAKE ON'那么'1'
当TxStage ='BOX TAKE ON'那么'6'
当TxStage ='DECANTER''那么'4 '
END AS DisplayOrder
来自dbo.Transactions_Transactions _Main
上图显示了我在上面运行查询时得到的结果
如果你认为你可以实现它
解决方案
看看这是否适合你
WITH cte
AS
(
SELECT TxDate,TxStage,ItemCode,ItemName,SupplierWeight,CustomerOrderedWeight,DecanterWeight,
SupplierWeight + CustomerOrderedWeight + DecanterWeight AS数量,
情况当TxStage ='BITUTAINER IN'那么'入境'
当TxStage ='BITUTAINER OUT'那么'Outbound'
当TxStage ='STOCK TAKE ON'那么'股票上涨'¥b $ b当TxStage ='BOX TAKE ON'那么'实体股票'为
WHEN TxStage ='DECANTER IN'那么'Decanter'
结束作为TypeOfTransaction,
案例当TxStage ='BITUTAINER''那么'3'
当TxStage ='BITUTAINER OUT'然后'5'
当TxStage ='STOCK TAKE ON'那么'1'
当TxStage ='BOX TAKE ON'那么'6'时
WHEN TxStage ='DECANTER IN'THEN'4'
$
END作为DisplayOrder
来自dbo.Transactions_Transactions_Main
)
SELECT *,variance = closing_stocks +' - '+ TypeOfTransaction ='Outbound'时的情况 然后TypeOfTransaction 否则''结束
FROM
(
SELECT *,closing_stocks =当TypeOfTransaction ='Stock Take On'时的情况'然后TypeOfTransaction + '+'其他''
结束+案例,当TypeOfTransaction ='入境' 然后TypeOfTransaction +'+'else''结束+
案例何时TypeOfTransaction ='Decanter' 然后TypeOfTransaction +'+'else''结束
+ CaseOfTransaction ='Outbound'时的情况 然后TypeOfTransaction +'+'else''结束,
FROM cte
)AS
I have my query that I am using to summarize the totals in my table but I need some help customizing the query to produce the results that I am looking for. So far this is the query That I have , but it is missing 2 Rows the closing stock and the Variance which are calculated rows. How do I come up with the Closing Stock and the Variance
SELECT TxDate, TxStage, ItemCode, ItemName, SupplierWeight, CustomerOrderedWeight,DecanterWeight, SupplierWeight+CustomerOrderedWeight+DecanterWeight AS Quantity, CASE WHEN TxStage='BITUTAINER IN' THEN 'Inbound' WHEN TxStage='BITUTAINER OUT' THEN 'Outbound' WHEN TxStage='STOCK TAKE ON' THEN 'Stock Take On' WHEN TxStage='BOX TAKE ON' THEN 'Physical Stock' WHEN TxStage='DECANTER IN' THEN 'Decanter' END AS TypeOfTransaction, CASE WHEN TxStage='BITUTAINER IN' THEN '3' WHEN TxStage='BITUTAINER OUT' THEN '5' WHEN TxStage='STOCK TAKE ON' THEN '1' WHEN TxStage='BOX TAKE ON' THEN '6' WHEN TxStage='DECANTER IN' THEN '4' END AS DisplayOrder FROM dbo.Transactions_Transactions_Main
The image above shows the result I get when I run the query above
If you think it you can achieve it
解决方案
See if this works for you
WITH cte
AS
(
SELECT TxDate, TxStage, ItemCode, ItemName, SupplierWeight, CustomerOrderedWeight,DecanterWeight,
SupplierWeight+CustomerOrderedWeight+DecanterWeight AS Quantity,
CASE WHEN TxStage='BITUTAINER IN' THEN 'Inbound'
WHEN TxStage='BITUTAINER OUT' THEN 'Outbound'
WHEN TxStage='STOCK TAKE ON' THEN 'Stock Take On'
WHEN TxStage='BOX TAKE ON' THEN 'Physical Stock'
WHEN TxStage='DECANTER IN' THEN 'Decanter'
END AS TypeOfTransaction,
CASE WHEN TxStage='BITUTAINER IN' THEN '3'
WHEN TxStage='BITUTAINER OUT' THEN '5'
WHEN TxStage='STOCK TAKE ON' THEN '1'
WHEN TxStage='BOX TAKE ON' THEN '6'
WHEN TxStage='DECANTER IN' THEN '4'
END AS DisplayOrder
FROM dbo.Transactions_Transactions_Main
)
SELECT * ,variance =closing_stocks+ '-'+case when TypeOfTransaction ='Outbound' then TypeOfTransaction else '' end
FROM
(
SELECT * , closing_stocks =case when TypeOfTransaction ='Stock Take On' then TypeOfTransaction+'+' else ''
end + case when TypeOfTransaction ='Inbound' then TypeOfTransaction +'+' else '' end +
case when TypeOfTransaction ='Decanter' then TypeOfTransaction +'+' else '' end
+case when TypeOfTransaction ='Outbound' then TypeOfTransaction +'+' else '' end ,
FROM cte
) AS der
这篇关于在我的SQL select语句中汇总并添加Summary Rows的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文