select data with date condition, choosing latest values from selected dates 2

[kingz2000]

Hi everyone,

I have the following table below:

artikelnr dates a b c
1 01.01.2020 10 7 1
1 02.01.2020 null 5 2
1 03.01.2020 null 4 3
1 04.01.2020 null 9 4
2 01.01.2020 20 10 4
2 02.01.2020 30 20 3
2 03.01.2020 null 15 2


I would like a select giving me the "latest" values of a,b and c

so if the condition is dates between "02.01.2020" and "04.01.2020" then the result should be

1 04.01.2020 null, 9, 4
2 03.01.2020 30 , 15, 2

is that possible with one command?

Thanks ever so much in advance.

Kingsley

 

[Andrzejek]

If this is your data:

[pre]
artikelnr dates a b c
1 01.01.2020 10 7 1
1 02.01.2020 null 5 2
1 03.01.2020 null 4 3[blue]
1 04.01.2020 null 9 4[/blue]
2 01.01.2020 20 10 4
2 02.01.2020 30 20 3
2 03.01.2020 null 15 2[/pre]

You can easily get: [pre]
[blue]1 04.01.2020 null 9 4[/blue][/pre]
with the MAX(dates) GROUP BY artikelnr

But to get :
2 03.01.2020 30 , 15, 2
What's the logic? Could you elaborate on "the "latest" values of a,b and c"?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[kingz2000]

Hi, first of thanks for the prompt answer.

For the artikelnr = 2, the first result should be a=30, because the "last" given value was 30 (a day before), then b=15 because on the 3rd of january there was a value of 15, and c hence 2.
)
SO the thing is, if there are no data for that given last date, then it should take the last values for that given artikel, and date.

 

[kingz2000]

do xou know what I mean? Shall I elaborate?

 

[Andrzejek]

I think I've got it
One more question - do you have a PK (Primary Key) on that table?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Starting point would be:
[tt]
SELECT artikelnr, MAX(dates) AS dates, [red]a[/red], b, c
FROM MyTable t
WHERE dates BETWEEN "02.01.2020" AND "04.01.2020"
GROUP BY artikelnr, a, b, c[/tt]

and since only [tt][red]a[/red][/tt] is the 'problem' column (right?), it needs another SQL to retrieve 'last' value for that [tt]artikelnr[/tt], something to the effect of:
[tt]
CASE [blue]WHEN a IS NULL [/blue]THEN (SELECT a FROM MyTable WHERE artikelnr = t.artikelnr [red]AND ...[/red])
ELSE a END AS a[/tt]

... and that's where I got stuck. Hopefully somebody with more knowledge will jump in and continue...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TamarGranor]

I think you're saying that for each article, you want to retrieve the record with the latest date. Assuming the combination of article number and date is unique, this should do it:

[pre]SELECT *
FROM YourTable
JOIN (SELECT artikelnr, MAX(dates) AS MaxDate FROM YourTable GROUP BY 1) MaxDates
ON YourTable.artikelnr = MaxDates.artikelnr
AND YourTable.dates = MaxDates.MaxDate
[/pre]

Tamar

 

[Andrzejek]

Tamar,
I think Kingsley wants to "retrieve the record with the latest date" and the latest entry of a, b, and c that are not NULLs if there is a not NULL entry, otherwise get the NULL

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

So, if this is his data:
[pre]
artikelnr dates a b c[red]
1 01.01.2020 10 7 1[/red]
1 02.01.2020 null 5 2
1 03.01.2020 null 4 3
[highlight #FCE94F]1 04.01.2020 null 9 4[/highlight]
2 01.01.2020 20 10 4
2 02.01.2020 [highlight #FCE94F]30[/highlight] 20 3
[highlight #FCE94F]2 03.01.2020[/highlight] null [highlight #FCE94F]15 2[/highlight] [/pre]

and he wants to get the data between "02.01.2020" and "04.01.2020" (ignore the very [red]first record[/red], out of range of dates)

"the [highlight #FCE94F]result[/highlight] should be"
[pre]
1 04.01.2020 null, 9, 4
2 03.01.2020 30, 15, 2[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TamarGranor]

Andy, if you're right, that seems like a job for OVER, but before I figure out the code, let's wait for him to clarify.

Tamar

 

[kingz2000]

Hi guys,

thanks ever so much for your input so far..so glad you'Re on the case...yes Andrzejek is right with what I need.

Due to the selected date criteria, the first record needs to be omitted.

So I guess, I need to be able to add a date condition at the end of it somehow.

 

[kingz2000]

...so just to clarify, I would need a datum criteria where if I say

datum between '2020-01-01' and '2020-01-03', then it should give me:

1 10 4 3
2 30 15 2


and if I give datum between '2020-01-03' and '2020-01-04', then it should give me:

1 null 9 4
2 null 15 2

so if the value is null, it gets the latest value for the kw (calendar year) in the given dates (datum).

 

[Andrzejek]

kingz2000,
We know you have NULLs in a
Is it the same in b and c? Or NULLs are not allowed in b and c?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[kingz2000]

Hi Andrzejek,

nulls can occur in a, b or c.

In fact, a,b, and c are calender weeks which generally go on for 50 columns, say.

Maybe an update , filling the nulls with last values, would be an adequate first step to simplify things? Basically, I don't mind if its several steps. To avoid changing the original data maybe I would have to copy the table first or something..?

Cheers guys.

 

[Andrzejek]

a,b, and c are calender weeks which generally go on for 50 columns

Actually, for 52 columns! A year usually have 52 weeks.
That indicates something (very) wrong with the data base design.

Could you tell us what is this data?
[pre]
artikelnr dates a b c
1 01.01.2020 10 7 1
[/pre]
a - 10 of what?
b - 7 of what?
3 - 1 of.... ?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[kingz2000]

Hi guys,

yes you are completely right, I had a bad table structure. Sorry, I am learning.

At first, I falsily had a,b, and c as columns although they are calender weeks, which would have meant 52 columns, which is obviously stupid. So I will have a column "Calender week", where I have the entries as rows.
sorry about that..It was still interesting to make this progress.

 

[Andrzejek]

Sometimes you have to go the wrong way to realize that it is not a way to go.
Properly designed normalized relational database is the way to go. Invest in learning it. It will save you a LOT of work and a LOT of headache. Setting up a proper data base is a skill, some say it is an art. But it is a pleasure to work with.
A good starting point is here

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson