web editor web editor - 6 months ago 9
SQL Question

Query to get result from 3 tables

What I need is the stored procedure and according to 3 tables give me the below result:

What I have is :

Table 1: TBL_USER

userid Client_name
------ --------
2101 client 1
2102 client 2
2105 client 3
2106 client 4


Table2: Accountbalance

Acbc_UserID Acbc_Remained
----------- -------------
2101 2.32
2102 2.727
2105 223.783
2106 26.77


Table 3 = opper_price

Opprice_UserID Opprice_Price
------- -------
2101 0.008
2101 0.008
2101 0.008
2101 0.008
2102 0.009
2102 0.009
2102 0.009
2102 0.009
2105 0.009
2105 0.009
2105 0.009
2105 0.009
2106 0.010
2106 0.010
2106 0.010
2106 0.010


(here we need an average for example
user_id 2106
has 4 price so we need to get the average which is again 0.009)

According to the table above i would like to get a below result:

if the user id does not exist in table 3 to use the average price of 0.006

Client_Name average_price Acbc_Remained SMS_remain
Client 1 0.008 2.32 ( Acbc_Remained /average_price)
Client 2 0.009 2.727 (2.727/0.009=303)
Client 3 0.009 223.783 24864.7778
Client 4 0.010 26.77 2677

========
Total of column SMS_remain


So what I want to do is to have this stored procedure and add it into the SQL Server email so the whole result table will be send as well.

This is what i have done till now but the price is NULL:

BEGIN
SET NOCOUNT ON;

SELECT Prj_Users.Users_ID
,Prj_Users.Users_Name AS prj_users
,Prj_AccountBalance.Acbc_Remained AS Prj_AccountBalance
,Prj_OpperPrice.Opprice_Price AS Price

FROM Prj_Users LEFT JOIN Prj_AccountBalance
ON Prj_Users.Users_ID = Prj_AccountBalance.Acbc_UserID
LEFT JOIN Prj_OpperPrice
ON Prj_OpperPrice.Opprice_UserID = Prj_OpperPrice.Opprice_Price

END


Thank you

Answer
Select Client_Name
      ,average_price = avg(Opprice_Price)
      ,Acbc_Remaind  = avg(Acbc_Remained)
      ,SMS_Remain    = case when avg(Opprice_Price)=0 then 0 else avg(Acbc_Remained)/avg(Opprice_Price) end
 From TBL_User A 
 Join opper_price B on (A.userid=B.Opprice_UserID)
 Join Accountbalance C on (A.userid=C.Acbc_UserID)
 Group By Client_Name

Returns

Client_Name average_price   Acbc_Remaind    SMS_Remain
client 1    0.008           2.32            290.00
client 2    0.009           2.727           303.00
client 3    0.009           223.783         24864.7777
client 4    0.01            26.77           2677.00

This one includes the Total and is reduces the redundant calculations:

Select A.*
      ,SMS_Remain    = case when average_price=0 then 0 else Acbc_Remaind/average_price end
      ,Total_SMS_Remain =SUM(case when average_price=0 then 0 else Acbc_Remaind/average_price end) OVER () 
 From (
          Select Client_Name
                ,average_price = isnull(avg(Opprice_Price),.0006)
                ,Acbc_Remaind  = isnull(avg(Acbc_Remained),0)
            From @TBL_User A 
            Left Join @opper_price B on (A.userid=B.Opprice_UserID)
            Left Join @Accountbalance C on (A.userid=C.Acbc_UserID)
            Group By Client_Name

      ) A

Returns

Client_Name average_price   Acbc_Remaind    SMS_Remain  Total_SMS_Remain
client 1    0.008           2.32            290.00      28134.7777
client 2    0.009           2.727           303.00      28134.7777
client 3    0.009           223.783         24864.7777  28134.7777
client 4    0.01            26.77           2677.00     28134.7777
client 5    0.0006          0.00            0.00        28134.7777

Note on Client 5 the .0006 amd the 0 on Acbc_Remaind this is done by using the ISNULL() function

Comments