I have summary table which contains three columns accountno, datetime, status.data looks like

Question

0.00/5 (No votes)

See more:

Hi,

I have summary table which contains tHREE columns Accountno,datetime,Status.Data looks like

Accountno

DATETIME

Status

1212 2018-03-12 16:13:21 No
1212 2018-02-12 16:13:21 Yes
1212 2018-01-12 16:13:21 Yes
1211 2018-03-12 16:13:21 Yes
1211 2018-02-12 16:13:21 No

I have master table containing Accountno.

Now i want to get top Datetime along with Status='Yes' for the accountno

What I have tried:

Yes but not getting actual data

Posted 7-May-18 20:27pm

krishnaraosan

Updated 7-May-18 20:48pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-05-07T20:33:00

Solution 1

Try:

SQL

SELECT TOP 1 AccountNo, [datetime] FROM MyTable
WHERE Status = 'Yes'
ORDER BY [datetime] DESC

Posted 7-May-18 20:33pm

OriginalGriff

Updated 7-May-18 20:34pm

v2

Comments

krishnaraosan 8-May-18 2:38am

i need to join with master table for all accounts not only for one account

Maciej Los · Answer 2 · 2018-05-07T20:50:00

Solution 2

Try this:

SQL

SELECT S.*, M.*
FROM Summary AS S INNER JOIN Master AS M ON S.Accountno = M.Accountno
WHERE S.Status = 'Yes' AND [datetime] > DATEADD(DD, -1, GETDATE())
--returns last day data

More at: Visual Representation of SQL Joins[^]
DATEADD (Transact-SQL) | Microsoft Docs[^]

Posted 7-May-18 20:50pm

Maciej Los

Updated 7-May-18 20:55pm

v2