Top N question
-
I have to do a reporte of a table that have the finacial movements per employee and company, of the transactions of certain bonus card. The transaction look like this:
Cia Emple Balance Date
9990 123 100 10/11/2006
9990 123 80 10/13/2006
9990 123 120 11/21/2006
9990 114 522 10/22/2006
9990 114 501 10/30/2006
9990 114 201 11/30/2006
If they want the balance at 10/31/2006 of CIA 9990 I have to return
Cia Emple Balance
9990 123 80
9990 114 501
How can I do that, I try the top N and of course it doesn't return what I spect, is there a special function do uses in this cases?
-
Select Cia, Emple, Balance FROM dbo.Table WHERE CIA = 9990 AND DATE >= '10/31/2006' AND DATE < '11/01/2006'
-
I think she means that she wants to get the last balance before the @as_of_date. Hopefully, there is just one entry per date (or we are dealing with a date & time).
-
Good point. But I can only answer the posted questions. I already tried to answer all possible implied questions but my post count started rising about 200 posts / day. And well, I got work to do here to
. -
Whe are not dealing with date & Time, just Date.
And how "Dave I" said I need the balance at certain day. One row per employee.
-
Without the time part noone can say which is the last/top balance of the day. We need some way of defining that, otherwise there's not much we can do (effectively)!
-
You are right I have a colum (varchar 8) that have the time
02:02:34 for 2 am
14:21:21 for 2 pm
This the query that I do but I only obtain the information of one employee
select top 1 tbmovimientos.nocliente, tbmovimientos.noemple, tbmovimientos.SaldoAnterior + tbmovimientos.montoulttrans as Saldo
from tbmovimientos
where tbmovimientos.nocliente=9990 and
tbmovimientos.noemple=114 and
fechaTrans <='2006-10-31 00:00:00.000'
order by nocliente, fechaTrans desc, hrTrans desc
-
And they are split because???
Date and time are the same thing, a point in time. Time by itself means pretty much nothing in this context.
What iare the clustered index and primary keys on that table?
-
Assuming that tblTest contains your sample data:
SELECT distinct t1.Cia,t1.Emple,t2.balance FROM dbo.tblTest t1
left
outer join (select top 1 emple,balance from dbo.tblTest t3 where t3.date_dd<='20061031' and t3.Cia=9990) t2
on
t2.emple=t1.emple
where
t1.date_dd<='20061031' and t1.Cia=9990
-
Sorry, you cannot see all top 1 balance values with the previous script.
SELECT distinct t1.cia,t1.Emple,t2.balance
into #temp
FROM dbo.tblTest t1
left outer join (select top 1 emple,balance from dbo.tblTest t3 where t3.date_dd<='20061031' and t3.Cia=9990) t2
on t2.emple=t1.emple
where t1.date_dd<='20061031' and t1.Cia=9990
update #temp set balance=(select top 1 balance from dbo.tblTest t where t.date_dd<='20061031' and t.Cia=9990 and t.emple=#temp.emple order by balance)
where #temp.balance is NULL
select * from #temp
drop table #temp
--
Good luck!
-
how about
select Cia, Emple , (select top 1 balance from table b where b.Emple = m.emple and b.cia=m.cia where date <= @date order by date desc) from table m
or somehting along those lines?
-
Thanks for the help!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply