SQL查询与选择案例... | SQL查询与选择案例

本文介绍了SQL查询与选择案例...的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有两张桌子

这是第一张桌子

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.

推荐答案

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查询与选择案例...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！