SQL查询与选择案例... [英] SQL Query with select case...

问题描述

我有两张桌子



这是第一张桌子



Godownid - >活动 - >时间 - >数量

1001 - >正在加载 - >日 - > 10

1002 - >卸货 - >夜晚 - > 20

1003 - >交叉 - >日 - > 30



这是第二张表



GodownId - > LoadingDayRate - > LoadingN8Rate - > UnloadingDayRate - > UnloadingN8rate - > CrossingDayRate - >过夜费率



1001 - > 10 - > 20 - > 30 - > 40 - > 50 - > 60





我希望得到合理的报告，如数量*价格与活动和时间...



请帮帮我...



问候，

Srinivas。

Hi,

I have two tables

This is the first table

Godownid --> Activity --> Time --> Qty
1001 --> Loading --> Day --> 10
1002 --> Unloading --> Night --> 20
1003 --> Crossing --> Day --> 30

This is the second table

GodownId --> LoadingDayRate --> LoadingN8Rate --> UnloadingDayRate --> UnloadingN8rate -->CrossingDayRate --> Crossing Night Rate

1001 --> 10 --> 20 --> 30 --> 40 --> 50 --> 60


I want to get consolidate report like qty * price with activity and time...

Please help me...

With Regards,
Srinivas.

推荐答案

您好，



查看以下查询....

Hi,

Check the below Query....

SELECT T.GodownId, 
       T.Activity,
       T.Time,
       T.Qty,
       T.Amount,
       (T.Qty*T.Amount) 'CalculatedAmount'
FROM (SELECT T1.GodownId,T1.Activity,T1.Time,T1.Qty,
	(CASE WHEN T1.Activity='Loading' AND T1.Time='Day' THEN T2.LoadingDayRate
	      WHEN T1.Activity='Loading' AND T1.Time='Night' THEN T2.LoadingN8Rate
	      WHEN T1.Activity='Unloading' AND T1.Time='Day' THEN T2.UnloadingDayRate
	      WHEN T1.Activity='Unloading' AND T1.Time='Night' THEN T2.UnloadingN8rate
	      WHEN T1.Activity='Crossing' AND T1.Time='Day' THEN T2.CrossingDayRate
	      WHEN T1.Activity='Crossing' AND T1.Time='Night' THEN T2.CrossingNightRate
	 ELSE 0 END) 'Amount' 
FROM First_Table T1
INNER JOIN Second_Table T2 On T2.GodownId=T1.GodownId) T 

问候，

GVPrabu

Regards,
GVPrabu

这篇关于SQL查询与选择案例...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！