Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

View of Select from (select ...)

Status
Not open for further replies.

kingz2000

Programmer
May 28, 2002
304
DE
Hi Guys,

I have the next part of my riddle:)

I have a table like this :

cw_gh1boc.png


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
 
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
 
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
 
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.
 
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
 
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.
Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top