View of Select from (select ...)

[kingz2000]

Hi Guys,

I have the next part of my riddle

I have a table like this :

with the select below I can obtain my desired result:

select distinct artikelnr, calender_week, imputed_calender_week from (
select distinct artikelnr
, datum
, calender_week
, first_value(menge) over (partition by artikelnr,calender_week
order by (case when menge is not null then 1 else 2 end), datum desc
) as imputed_calender_week

from test_table
where datum between '2021-02-01' and '2021-03-01') x

Now my problem is creating this as a view so that I can select the datum criteria. How can I do that?

Thanks in advance for any help.

Kingz

 

[Andrzejek]

Why don't you include [blue]datum[/blue] in your view so you can query on it?
[tt]
CREATE VIEW MyNewView AS
select distinct artikelnr, [blue]datum,[/blue] calender_week, imputed_calender_week
from (
select distinct artikelnr
, [blue]datum[/blue]
, calender_week ...[/tt]

---- Andy

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

 

[Andrzejek]

I guess datum is just a date (right?), but what is calender_week? Is it a week number of the date in datum field? Doesn't look like that is it...
And what is menge?

---- Andy

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

 

[Mike Lewis]

Andy, I was also confused about calendar_week. Menge is German for quantity.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[kingz2000]

sorry, calender week is basically the week number. So The first week of the year is 1 and the last week in december is 52..or sometimes 53..

This was my cw1, cw2 and cw3 before..or maybe I called it a,b and c. Either way, I decided it would be better to structure the table this way.

 

[Andrzejek]

But if you have this data:

[pre]
datum calendar_week
01.01.2021 1
01.01.2021 2
01.01.2021 3
...[/pre]
(I assume 01.Jan is January 1, 2021)
How come the same date have 3 different calendar_week numbers?
01.Jan should all have calendar_week of 1

>calender week is basically (?) the week number
You can get the WEEK NUMBER from any DATE this way. You do not need to have a separate column for it, if the calender week IS basically the week number

---- Andy

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

 

[Auguy]

Be careful with the date range, if there are times in the datum column you might miss some of them.
If datum is 2021-03-01 11:05:00, then it will not be included.
I usually add one day to the ending date and then do something like this instead of using between.

IM.InvoiceDate >= @InvoiceDateStart AND IM.InvoiceDate < dateadd(day, 1, @InvoiceDateEnd)'

Maybe one of the experts here can confirm the date issue using between, and correct me if I'm wrong.

Auguy
Sylvania/Toledo Ohio

 

[Andrzejek]

If you have a DATE with no TIME 'portion', that's the midnight of that DATE, and since BETWEEN is 'inclusive' (equals to >= and <=), the DATEs are included in the request.
When you do have TIME 'portion' in the DATE you need to be more specific when using BETWEEN (or trick it as Auguy does)

---- Andy

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