generate week numbers in a query

  • Thread starter Thread starter jon
  • Start date Start date
J

jon

I need a colum in a query to generate 1 row for the last 12 weeks but I have
no idea
Any andvise please.
Eg
17
16
15
14
13
12
11
10
9
8 ETC

Thanks

Jon
 
jon said:
I need a colum in a query to generate 1 row for the last 12 weeks but
I have no idea
Any andvise please.
Eg
17
16
15
14
13
12
11
10
9
8 ETC

Thanks

Jon
Click to expand...

I think we need a little more information about what you want to do.

In addition, it does sound like you are moving into a normalization
issue, but without knowing more about what you really want it is difficult
to say.
 
I have a table with machine down time in which is from a date and time to an
end date and time.
So in queering this down time I need to check the amount of time per week
the process in not working.
So when I want a report I need the last 12 weeks by week and was hoping to
take one column in a query to select week No to work against and to put the
results against as well.

Thanks

Jon
 
jon said:
I have a table with machine down time in which is from a date and time
to an end date and time.
So in queering this down time I need to check the amount of time per
week the process in not working.
So when I want a report I need the last 12 weeks by week and was
hoping to take one column in a query to select week No to work
against and to put the results against as well.

Thanks

Jon
Click to expand...

OK, lets see if I understand.

You want a report to show the total amount of down time for each of a
number of machines and you want this totaled by week for the last twelve
weeks. Sort of like

Machine A
Week Time
1 1
2 5
3 4
....
12 0
Machine Total 10

Machine B
Week Time
1 10
2 5
3 9
....
12 0
Machine Total 24

etc.

Total for all machines 34
 
Within reason yes.
The user will select a machine and then the query will work the hours as you
show but there are several faults which I will have to group and total.

Thanks

Jon
 
Here are some relevant pieces.

weeknumber = datepart("ww",supplyadate)

1) compute a date range to use. today - 84 is 12 weeks ago from
today. and use that range for accepting dates.

or

1) compute todays weeknumber and compute a weeknumber to use as
criteria in the query.

2) compute weeknumber in the query and summarize by the computed
weeknumber
 
Back
Top