SQL Query (Help)

[sssibrahim]

Hi!,
I'm new to this SQL Querying. I'm having the following question:

My table MACHINE contains the following columns:
MachineID
PurchaseDate
Brand
ServiceDate
Day1
Day2
Day3
Day4
Day5
Day6
Day7

where Day1 to Day7 gives the respective Online Hours of that machine From Monday to Sunday. Now I want a Single Query that will return me the OnlineHours of all the Machines in a Month or a year (i.e. the date must increment automatically and the corresponding Day(OnlineHours)value must be selected based on the DAY(Mon-Sun) of the Date.

Can anyone help me with a piece of example code. Its very urgent pls.

Thanks in advance.
Regards,
Ibrahim

 

[MikeJones]

Hi Ibrahim,

How do you know which week within a month year a row is for?

 

[THoey]

This doesn't solve your problem, but it might make it easier. I would suggest you split this into two tables. The first one being MACHINE with the following fields:
MachineID
PurchaseDate
Brand
ServiceDate

And the second one being MACHINEHOURS with the following fields:
MachineID
Day
StartHour
EndHour

If you do it this way, you can keep info for each machine for more than just the last week. Your way, you are going to have to overwrite your data every week or keep redundant data (PurchaseDate, Brand, ServiceDate) in each record for a week.
Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[sssibrahim]

Hi Jones,

The OnlineHours (ie Day1 - Day7) are based on DAY (MONDAY - SUNDAY) and not on DateWise. So if we want to find the OnlineHour on a Particular Date, then we've to get the DAY of the Date and should chose from Day1 - Day7 based on the DAY of the Date.

To be precise, I want to run a loop from 1st date to Last Date in a month to find the OnlineHours in a whole month.

Can you pls help me in this regard.
My mailID : sssibrahim@hotmail.com

With Regards,
Ibrahim

 

[ljspop]

Hi Ibrahim,

I'm not sure exactly how the structure you
describe works... can I assume from your
post that online hours is a static number
week to week?

Simple SQL doesn't really have a mechanism for
looping other than iterating through rows in a
table. That type of procedural construct would
require something like PL/SQL.

It is possible to get the day of the week by
using a to_char() function and ask for 'DAY'
formating, i.e. to_char(sysdate, 'DAY') would
return THURDSDAY.

So, were it me, I would build a package with
a function that accepts a begining and ending
date, as well as a machine id, and returns the
sum of online hours based on the static amounts
for the day of the week from your table. You
could than add a pragma for the function and
call it within a query.

That last part is probably pretty confusing for
someone new to Oracle... feel free to ask more
questions if necessary.

Bob Bob Lowell
ljspop@yahoo.com

 

[karluk]

You may find helpful the following rather complicated function.

SIGN(NEXT_DAY(TRUNC(SYSDATE),'MONDAY')-TRUNC(SYSDATE)-7)+1

It has the useful property of evaluating to 1 if sysdate is a Monday, and to 0 for all the other days of the week. There are similar functions for Tuesday through Sunday. Then the following query will return the total online hours for a given date, regardless of the day of the week.

select
sum(day1*(sign(next_day(trunc(sysdate),'MONDAY')-TRUNC(SYSDATE)-7)+1)) +
sum(day2*(sign(next_day(trunc(sysdate),'TUESDAY')-TRUNC(SYSDATE)-7)+1)) +
sum(day3*(sign(next_day(trunc(sysdate),'WEDNESDAY')-TRUNC(SYSDATE)-7)+1)) +
sum(day4*(sign(next_day(trunc(sysdate),'THURSDAY')-TRUNC(SYSDATE)-7)+1)) +
sum(day5*(sign(next_day(trunc(sysdate),'FRIDAY')-TRUNC(SYSDATE)-7)+1)) +
sum(day6*(sign(next_day(trunc(sysdate),'SATURDAY')-TRUNC(SYSDATE)-7)+1)) +
sum(day7*(sign(next_day(trunc(sysdate),'SUNDAY')-TRUNC(SYSDATE)-7)+1))
from MACHINE;

You can build a loop to calculate the total online hours for the time period you are interested in. Probably you will also need a where clause to exclude machines that weren't puchased or put in service by the date being evaluated.