Looking for some examples maybe.....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Looking for some examples maybe.....

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2008-10-17 : 15:53:30

I am looking to show the accounts that are decreasing their quantity in a particular security. For example, I have a table that stores the following:

Account
Security
PositionDate
Quantity

I would like to run some query that will show me when a particular account has sold out of, or decreased the quantity in a particular Security on a day/day basis if that makes sense.

SimpleSQL
Yak Posting Veteran

85 Posts

Posted - 2008-10-17 : 23:40:11

Can you share some sample data? It is hard to understand the question without it since we do not know the business meaning of these terms.

For example, are there many securities per account. Would you have multiple securities per positiondate etc..

Share table schema and any constrainsts on thsi table as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 00:37:43

May be something like this

SELECT t.Account,t.Quantity-prev.Quantity AS Diff
FROM Table t
OUTER APPLY(SELECT TOP 1 Quantity
            FROM Table
            WHERE Account=t.Account
            AND Security=t.Security
            AND PositionDate <t.PositionDate
            ORDER BY PositionDate DESC)prev
GROUP BY t.Account
HAVING SUM(CASE WHEN t.Quantity-prev.Quantity >0 THEN 1 ELSE 0 END)=0

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-19 : 00:45:48

try it if it helps

select b.account,b.qty,a.positiondt from acc b
join (select max(positiondt) as positiondt,account from acc group by account having count(*)>1) a
on a.account=b.account where a.positiondt=b.positiondt
and (datediff(dd,getdate(),a.positiondt)=1 or datediff(dd,getdate(),a.positiondt)=0)

malay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources