February 3, 2011 at 12:18 am
I have a transaction table where i am maintaining two transaction type status i.e. B = Buy and S = Sell
Here is the structure
CREATE TABLE [dbo].[ipslogic](
[ipsac] [varchar](50) NULL,
[ttype] [char](1) NULL,
[scode] [varchar](50) NULL,
[faceval] [numeric](18, 0) NULL,
[remaining] [numeric](18, 0) NULL,
[balance] [numeric](18, 0) NULL,
[dealref] [varchar](50) NULL
) ON [PRIMARY]
Here is the sample data
-------------------------
INSERT ipslogic(ipsac,ttype,scode,faceval,remaining,balance,dealref) VALUES('10001','B','0000-0001','50000',NULL,NULL,'d1')
INSERT ipslogic(ipsac,ttype,scode,faceval,remaining,balance,dealref) VALUES('10001','B','0000-0001','20000',NULL,NULL,'d2')
INSERT ipslogic(ipsac,ttype,scode,faceval,remaining,balance,dealref) VALUES('10001','B','0001-0000','10000',NULL,NULL,'d3')
INSERT ipslogic(ipsac,ttype,scode,faceval,remaining,balance,dealref) VALUES('10001','B','0001-0000','40000',NULL,NULL,'d4')
Now what i want is when i run the query it should display records based on 2 criterias
Ac #: '10001' SCode = '0000-0001'
and display facevalue of buying status and try to find type 'S = Sell' items of Scode=0000-0001' if not found then remaining should become the equivalent of Buy of the same Scode otherwise display the face value of 'S and calculate the balance based on the formula Balance = facevalue - remaining.
thanks for the help in advance.
February 3, 2011 at 7:51 am
This sounds like a homework problem.
I am not trying to be mean, really, but you are paid (I assume) to solve these kinds of basic query problems. Try to reason it out yourself and make an attempt at least.
The probability of survival is inversely proportional to the angle of arrival.
February 3, 2011 at 12:29 pm
sturner (2/3/2011)
This sounds like a homework problem.I am not trying to be mean, really, but you are paid (I assume) to solve these kinds of basic query problems. Try to reason it out yourself and make an attempt at least.
It's actually not, sturner, if my guess is right. This is a semi-common financial industry problem, and it looks like you're arguing with common bonds, Josh?
We'd need A) sell items in your sample data, B) more explanation as to your business rules on finding a Sell, and C) a clearer description of what happens when there are no sells.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 10:25 pm
sturner you are bad at guessing things and i guess its kinda rude to pass such comments on your presumptions. Anyway its indeed a financial industry related problem (if you can understand my structure) but I guess you never worked for financial industry so you can guess whatever you want to.
Thank you all of you I've solved my problem by myself with this query.
Declare @scode varchar(40) = '0000-0001'
Declare @ipsac varchar(40) = '10001'
SELECT ipsac, ttype, scode, faceval,
CASE WHEN ISNULL((select sum(value) from ipslogic_trans where ipsac=a.ipsac and scode=a.scode and dealref=a.dealref),0)=0 THEN faceval
ELSE faceval - ISNULL((select sum(value) from ipslogic_trans where ipsac=@ipsac and scode=@scode and dealref=a.dealref),0)
END 'Available',
ISNULL((select value from ipslogic_trans where ipsac=a.ipsac and scode=a.scode and dealref=a.dealref),0) 'Entered Value',
dealref
FROM ipslogic a
WHERE ttype = 'B' and ipsac=@ipsac and scode = @scode
February 4, 2011 at 6:28 am
"Thank you all of you I've solved my problem by myself with this query."
I knew you could do it if you just gave a try son.
The probability of survival is inversely proportional to the angle of arrival.
February 4, 2011 at 6:36 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply