How do I write a query to retrieve the date of the latest row for each combination of fund, strategy and market in each calendar month

Question

1.00/5 (1 vote)

See more:

CREATE TABLE ProfitAndLoss (
 ProfitAndLossId BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 ValueDate DATE NOT NULL,
 FundName VARCHAR(50) NOT NULL,
 StrategyName VARCHAR(50) NOT NULL,
 MarketName VARCHAR(50) NOT NULL,
 DailyPnlUsd FLOAT NOT NULL,
 MtdPnlUsd FLOAT NOT NULL,
 YtdPnlUsd FLOAT NOT NULL

What I have tried:

how do i write a query to retrieve the date of the latest row for each combination of fund, strategy and market in each calendar month. And how would i test if it is correct?

Posted 10-Jul-21 4:15am

johnathan indigo

Updated 10-Jul-21 4:26am

Add a Solution

1 solution

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 · 2021-07-10T04:26:00

Solution 1

If you use a GROUP BY clause[^] you can partition your data into separate months.

You can then use the aggregate function MAX[^] to give you the highest date in each month.

Then it's a simple matter to use a JOIN[^] to get the row for each month that contains that date.

Posted 10-Jul-21 4:26am

OriginalGriff

v2

Comments

johnathan indigo 10-Jul-21 10:43am

something like this?
SELECT FundName, StrategyName, MarketName
FROM profitAndLoss
GROUP BY mtdPnlUsd
SELECT MAX(MtdPnlUsd)

thoughts on this? im stuck on the JOIN as im not sure what ill used that for?

OriginalGriff 10-Jul-21 11:39am

My first though on this is simpel: you didn't read the link on GROUP BY well at all - that won't even run.

Go back to GROUP BY and read up on what it does and what it gives you.

It should be fairly obvious what the JOIN is for when you understand what GROUP BY does.

OriginalGriff 10-Jul-21 12:45pm

"summary rows" is the important bit: it means that you can only return the GROUP BY row and aggregate data: SUM, AVERAGE, MAX. MIN, ...

To combine that with individual row values, you need to JOPIN those results back into the original table to "pick out" the rows you want.

This may help you understand:

SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

johnathan indigo 10-Jul-21 13:03pm

do you mind writing the query and explaining through it, I am lost..

OriginalGriff 10-Jul-21 13:26pm

Yes, I do mind - you learn nothing from it if I do! :laugh:

Give it a try: use your DB manager software (SSMS for SQL Server for example) and build just the GROUP BY query to get the top date of each month.
Then see if you can JOIN that when it works.

johnathan indigo 10-Jul-21 13:42pm

on the right track?

SELECT FundName, StrategyName, MarketName
FROM ProfitAndLoss
WHERE ValueDate IN( SELECT max( ValueDate) FROM ProfitAndLoss);

OriginalGriff 10-Jul-21 13:49pm

Does that generate what you wanted?

Not according to your description ...
Seriously: try building a GROUP BY query. It's not complicated really!

johnathan indigo 10-Jul-21 13:52pm

closer?

SELECT FundName, StrategyName, MarketName
FROM ProfitAndLoss
WHERE ValueDate IN( SELECT max( ValueDate) FROM ProfitAndLoss);
GROUP BY FundName, StrategyName, MarketName
ORDER BY MAX(ValueDate) DESC;

OriginalGriff 10-Jul-21 14:01pm

Stop guessing.
Read the links, and pay attention to the examples instead of throwing SQL together and hoping it'll do something usefull!

johnathan indigo 10-Jul-21 13:53pm

now im confused as to what i am joining?