How to get the last input from one day and afterwards make this Join?

[joshuahirsch]

Hey there,

I have a question:

Imagine Salepartners are adding Forecasts for a certain Quarter in a Year.

Now I only want ONE Forecast per YearQuarter per day from a Salespartner (the last one, so something with Max(ForecastId? I don't know, help please)

But I also want to create redundant data in the new table, to see, if a salespartner didn't post a forecast on a certain day, where others made a forecast.

I'll give you an example, I think it explains best.

I'm having this kind of Data:

Forecast ID	Salespartner ID	Date created	YearQuarter
1	2	01.01	19Q1
2	2	01.01	19Q1
3	1	03.01	19Q1
4	2	03.01	19Q2
5	3	05.01	19Q1
6	1	03.01	19Q2
7	3	05.01	19Q1

So, now I want to get a table like this:

Forecast ID	Salespartner ID	Date created	YearQuarter
--	1	01.01	--
3	1	03.01	19Q1
6	1	03.01	19Q2
--	1	05.01	--
2	2	01.01	19Q1
4	2	03.01	19Q2
--	2	05.01	--
--	3	01.01	--
--	3	03.01	--
7	3	05.01	19Q1

So if someone could help me with getting just one/last forecast of a day of a certain YearQuarter from a salespartner and creating this table with NULL values, i'd highly appreciate it!

Thanks a lot,

Josh

[Thiago_Justen_]

You could try this out:

Load
[Forecast ID],
LastValue ([Salespartner ID]) as [Salespartner ID],
LastValue ([Date created]) as [Date created],
LastValue (YearQuarter) as YearQuarter
From YourDB Group By [Forecast ID];