Link to home
Start Free TrialLog in
Avatar of Takamasa
TakamasaFlag for Japan

asked on

LINQ to Dataset - Subquery

I am writing a query in LINQ and I am stuck with the subquery part.  

Original SQL:
SELECT account, bal_date, ccy, balance
FROM Balance A
WHERE
A.balance < 0 AND
A.date =  ( SELECT
        MIN(B.bal_date)
        FROM Balance B
        WHERE balance < 0 AND 
                     A.account = B.account AND
                     A.ccy = B.ccy
        )

LINQ:
Dim query = From bal In dt.AsEnumerable() _
                    Where bal.Item("balance") < 0 _
                    And bal.Item(“bal_date”) = ( subquery? )
                    Select bal


I learned that if I do Grouping by account and currency, I can get the MIN (bal_date),  but the I need the balance of such date, not the  MIN(balance) or MAX(balance).

I would appreciate if I get some help from Experts...
Avatar of Ramprakash Jeyabalan
Ramprakash Jeyabalan

What do you mean "balance of such date"? Can you give an example?
Avatar of Takamasa

ASKER

Hi Ramprakash

Thank you for your comment.
I want to get the balance as of MIN(B.bal_date) - the result of the subquery.
ASKER CERTIFIED SOLUTION
Avatar of Ramprakash Jeyabalan
Ramprakash Jeyabalan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ramprakash,

Thank you so much ! It worked.