HI.
I have 3 tables
1-std with :stdID ,programID.
2-Programswith :ProgramID ,Cost
3 -Movements with :stdID ,balance.
the first table contain thestdID andProgramID , some times the std hasn'tprogramID that mean he hasn'tprogramID. then we return null.
if the std hasprogramID there is to cases.
the first one he have a movement on his balance then we get the biggestbalance for the std.
the second case he hasn't any moventen then we get his balance fromPrograms table by theProgramID .
I need sql server function that return table like this
stdID , Balance
that means every std with his Balance.
Regards.
Can you also provide some sample data from each of the tables and expected output, so we understand the samething you are saying.
|||Thank's for replay , I'l write an examples.
std table:
stdID programID
1 888
2 777
3 null 'note the std don't haveprogramID.
4 666
Programs table :
ProgramID Cost
666 900$
777 850$
888 750$
Movements :
MovID StdID Balance
1 1 800$ 'first move
2 1 750$
3 1 700$
4 2 800$
that mean the std number 1 have 3 transaction then his balance is 700$ by the last MovID Field. and the std number 2 has one transaction then his balance is 800$ , and the std number 4 don't have any transaction that mean his balance is 900$ I got is fromPrograms table .
but std number 3 is null because his ProgramID is null.
the output like this
1 700$
2 800$
3 null
4 900$
I know that the Database design is bad but I don't have permission to change it .
|||
Assuming there is DEFINETELY a record either in Movements table or in Programs table for a given stdid, here's a sample. Tweak it as necessary:
Declare @.Stdtable (Stdidint, ProgIdint)Insert into @.StdSelect 1,10unionallSelect 2,15unionallSelect 3,20unionallSelect 4,30Declare @.ProgramTable (ProgIdint, CostDecimal(10,2))Insert into @.ProgramSelect 15, 100.5unionallSelect 20, 50.89Declare @.MovementTable (Stdidint, Balancedecimal(10,2))Insert into @.MovementSelect 1,50.00unionallSelect 1,55.00unionallSelect 4,150.00unionallSelect 4,250.00unionallSelect 4,350.00Select S.Stdid ,Coalesce(M.MBalance,P.Cost)from @.Std SLeftJoin (Select Stdid,Max(Balance)as MBalanceFROM @.Movement M1Group by Stdid) MON S.Stdid = M.StdidLeftJoin @.Program PON S.ProgId = P.ProgId|||
Thank's alot dear.
yeah I must add movid in Movements table instead of chose the biggset value in Movements table for std .
sorry I don't explain my problem correctlly in the main post.
how can I get the last movement.?
Regards.
|||
Test the query.. I think it shoud work...
|||yeah I test it but I need the Movement table like this:
Declare @.Movement Table (Movid int, Stdid int, Balance decimal(10,2))
and get the last movid for every StdID.
Regards.
|||
You want the last moveid (I presume MAX(MovId) or Max(cost)? You can change the query accordingly. Put your efforts and if you cant figure out post back what you tried and any error messages.
|||really thank's alot for your help:
Declare @.Std table (Stdid int, ProgId int)
Insert into @.Std
Select 1,10 union all
Select 2,15 union all
Select 3,20 union all
Select 4,30
Declare @.Program Table (ProgId int, Cost Decimal(10,2))
Insert into @.Program
Select 15, 100.5 union all
Select 20, 50.89
Declare @.Movement Table (movid int, Stdid int, Balance decimal(10,2))
Insert into @.Movement
Select 1,1,50.00 union all
Select 2,1,55.00 union all
Select 3,4,350.00 union all
Select 4,4,250.00 union all
Select 5,4,150.00
Select S.Stdid , Coalesce(M.MBalance,P.Cost)
from @.Std S
Left Join (Select Stdid, Max(Balance) as MBalance
FROM @.Movement M1
Group by Stdid
) M ON S.Stdid = M.Stdid
Left Join @.Program P ON S.ProgId = P.ProgId
in this way I need the output like this
1 55.00
2 100.50
3 50.89
4 150.00
becose the last movement to the std number 4 is 150$
|||
Select S.Stdid ,Coalesce(M2.Balance,P.Cost)from @.Std SLeftJoin (Select Stdid,Max(movid)as MaxIdFROM @.Movement M1Group by Stdid ) MON S.Stdid = M.StdidLeftJoin @.Movement M2ON M2.movid = M.MaxIdLeftJoin @.Program PON S.ProgId = P.ProgId|||
SELECT std.stdID,COELESCE(t1.balance,programs.Cost)FROM stdLEFTJOIN (SELECT *FROM (SELECT stdID,Balance,row_number()OVER (PARTITIONBY StdIDORDER BY MovIDDESC)AS TheRankFROM Movements ) t2WHERE TheRank=1 ) t1ON t1.StdID=std.StdIDLEFTJOIN ProgramsON std.ProgramID=Programs.ProgramIDORDER BY std.StdID|||
Sometimes you say "biggest balance" and other times you say "last movement".
Which is it
No comments:
Post a Comment