Get minimum value per user

Question

I have this SQL code here, my wish is to calculate the latest fee balance per student for each deposit made. So far I have been able to achieve this but now the question is how I'm I to select only the latest balance while disregarding the other previous entries/Balances

(select

     f.Totals -SUM(Total) OVER(ORDER BY pay_Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Balance,
     p.Total as 'TotalPaid',
c.class_id as 'ClassID',
p.std_ID as 'STDID',
c.class_name as 'Class',
a.ad_stdname as 'Name',
t.term_id as 'TermID',
t.term_name as 'Term',
p.Date as 'Date',
case when (st.str_id = null) then '-' else st.str_id end as 'StrID',
case when (p.Tution_fee = null) then '0' else p.Tution_fee end as 'Tution',
case when (p.adm_fee = null) then '0' else p.adm_fee end as 'Admission',
case when (p.Activity_fee = null) then '0' else p.Activity_fee end as 'Activity',
case when (p.Textbk_Statio = null) then '0' else p.Textbk_Statio end as 'Texbooks',
case when (p.Transport_fee = null) then '0' else p.Transport_fee end as 'Transport'
from Payments p
 inner join Admissions a 
 on a.ad_id = p.std_ID
 inner join classes c 
 on c.class_id =p.class_id
   inner join fees f on f.fee_classID = p.class_id and f.Term_id = p.Term_id
 left join streams st
 on st.str_id = p.str_id
 inner join terms t 
 on p.Term_id = t.term_id
 where a.ad_id = 29
 )
 order by  p.std_name,Balance,p.Date  desc

This is my current output

how are you identifying which is latest row?..datetime wise..? drill down problem to small example.. — Always_a_learner, Jun 20 '20 at 13:37
@Always_a_learner I have tried using max(date) but it returns an error — John Rohosafi, Jun 20 '20 at 14:10
can you consolidate your problem to small table? i can see all date is same so which row is latest? — Always_a_learner, Jun 20 '20 at 14:29
@Always_a_learner I may have asked the wrong question; what I need is to get the least/min balance only from the balance column — John Rohosafi, Jun 20 '20 at 15:51

Always_a_learner · Answer 1 · 2020-06-20T17:09:30.010

I have selected min value for each user in below example:

user date value

Sonu 1/2/2010 1.5
Monu 1/3/2010 2.5
Arun 8/4/2009 3.5
Sonu 2/2/2010 1.0
Monu 12/2/2009 0.5

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, min(value) as value
    from MyTable
    group by username
) tm on t.username = tm.username and t.value = tm.value

Demo sql Fiddle

Use above logic to implement in your code

I tried to accommodate above logic in you query. for now i have just used stdid for grouping, you may add more accordingly. you might get some syntax error:

with MyTable as (select   
f.Totals -SUM(Total) OVER(ORDER BY pay_Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Balance,
p.Total as 'TotalPaid',
c.class_id as 'ClassID',
p.std_ID as 'STDID',
c.class_name as 'Class',
a.ad_stdname as 'Name',
t.term_id as 'TermID',
t.term_name as 'Term',
p.Date as 'Date',
case when (st.str_id = null) then '-' else st.str_id end as 'StrID',
case when (p.Tution_fee = null) then '0' else p.Tution_fee end as 'Tution',
case when (p.adm_fee = null) then '0' else p.adm_fee end as 'Admission',
case when (p.Activity_fee = null) then '0' else p.Activity_fee end as 'Activity',
case when (p.Textbk_Statio = null) then '0' else p.Textbk_Statio end as 'Texbooks',
case when (p.Transport_fee = null) then '0' else p.Transport_fee end as 'Transport'
from Payments p
 inner join Admissions a 
 on a.ad_id = p.std_ID
 inner join classes c 
 on c.class_id =p.class_id
 inner join fees f on f.fee_classID = p.class_id and f.Term_id = p.Term_id
 left join streams st
 on st.str_id = p.str_id
 inner join terms t 
 on p.Term_id = t.term_id
 where a.ad_id = 29
 order by  p.std_name,Balance,p.Date  desc) select * from MyTable 
 inner join
 (
    select STDID, min(Balance) as value
    from MyTable
    group by STDID
) tm on t.STDID = tm.STDID and t.Balance = tm.Balance

hello, this is not working. I don't have a column for the balance. It is being calculated from the running total — John Rohosafi, Jun 20 '20 at 16:58
store your above query in nested query and give alias Mytable..your query result will be treated as table for this query — Always_a_learner, Jun 20 '20 at 16:59
i tried to make your final query run and let me know if it is working — Always_a_learner, Jun 20 '20 at 17:10
Replicate you issue in sqlfiddler and share I will look into it — Always_a_learner, Jun 20 '20 at 18:22

Get minimum value per user

1 Answers1

user date value