SQLTeam.com | Weblogs | Forums

How can I query my DB with basic math to return problem positions


I am a total newbie to SQL. I just started two weeks ago. I can execute basic select functions, order by, etc..but, the the more complicated stuff, I am completely lost.

Effectively, I want to compare two days of inventory (T1, T0 and purchases sells on T0) and return all the results, for all of my accounts, by product_id that does not satisfy this equation:

T1 - (T0 + Purch/SellsT0). I want to return all results that are not zero.

Can someone point me in the right direction/provide some guidance on the issue?



This is what I have so far but, it just queries the DB. No math.

declare @Date DATE --Business Date--
set @Date =(select (CONVERT(VARCHAR(10),(select BUSINESSDATE from XXX_YYYYY.dbo.T_REF_PRICE_DATE where ID = 1),101)))

declare @Date1 DATE --Business Date--
set @Date1 =(select (CONVERT(VARCHAR(10),(select PREVIOUSBUSINESSDATE from XXX_YYYYY.dbo.dbo.T_REF_PRICE_DATE where ID = 1),101)))

SELECT XXXInstrumentID,XXXTransactionID,BusinessFunctionSource,TransactionDate,TransactionStatus,XXXPortfolioID,XXXPortfolioCode,PortfolioName,XXXLeadCashPortfolioID,InstrumentDescription,SettlementDate,XXXCounterpartyName,Quantity,GrossAmountInPortfolioCurrency
from SQLOpenDoor.Services.TransactDB where TransactionDate = @Date
and TransactionStatus = 'Active'

select distinct a.EvaluationDate, a.XXXPortfolioID,a.XXXPortfolioCode,a.PortfolioName, a.XXXInstrumentID,a.InstrumentDescription,a.TotalQuantityNominalAmount,a.MarketValueInCAD

from SQLOpenDoor.Services.Inventory_v6 a
JOIN SQLOpenDoor.Services.Services.Inventory_v6 b ON a.XXXPortfolioID = b.XXXPortfolioID and a.XXXPortfolioCode = b.XXXPortfolioCode

where a.EvaluationDate = @Date
and b.EvaluationDate = @Date1