Confused by a complicated query dealing with date ranges.
Hi,
I've got a SQL 7 (yes SQL 7) view of a table which provides the start and end dates for a Database/Contract/Year/Per
There may be no weeks for a particular contract or it may have shifted from period only to weekly ranges or from weekly to period only ranges.
The snippet below is the data for one of the many contracts.
My requirement is to be able to extract the date range for what ever criteria I supply.
So, I will always know the database name and the contract.
I'll have to supply a year. The ranges are being calculated for historic data from January 2007.
The period supplied will be 0 for the entire year (and that implies a zero for the week) or a value from 1 to 13.
The week supplied will be 0 for the entire period or a value from 1 to 5.
If the supplied params don't match a valid range (i.e. asking for period 9 week 5 in the supplied data is invalid as there is no week 5), the result would need to be 2008/09/19 00:00:00.000 to 2008/09/19 00:00:00.000 - effectively a range of 0 time.
The range will be used to select orders and associated details authorised in that range, so a 0 time daterange for invalid criteria is fine as no date would be selected.
As I'm on SQL 7 I can't use a UDF.
I think I need to link to the view below twice, once to get the start of the date range and once to get the end of the date range, but I'm lost.
Any ideas?
Oh. Each contract can have a different period dates. They represent their accounting periods for which we need to produce reports from our data (fleet management and vehicle profiling data).
TIA.
Richard.
I've got a SQL 7 (yes SQL 7) view of a table which provides the start and end dates for a Database/Contract/Year/Per
There may be no weeks for a particular contract or it may have shifted from period only to weekly ranges or from weekly to period only ranges.
The snippet below is the data for one of the many contracts.
My requirement is to be able to extract the date range for what ever criteria I supply.
So, I will always know the database name and the contract.
I'll have to supply a year. The ranges are being calculated for historic data from January 2007.
The period supplied will be 0 for the entire year (and that implies a zero for the week) or a value from 1 to 13.
The week supplied will be 0 for the entire period or a value from 1 to 5.
If the supplied params don't match a valid range (i.e. asking for period 9 week 5 in the supplied data is invalid as there is no week 5), the result would need to be 2008/09/19 00:00:00.000 to 2008/09/19 00:00:00.000 - effectively a range of 0 time.
The range will be used to select orders and associated details authorised in that range, so a 0 time daterange for invalid criteria is fine as no date would be selected.
As I'm on SQL 7 I can't use a UDF.
I think I need to link to the view below twice, once to get the start of the date range and once to get the end of the date range, but I'm lost.
Any ideas?
Oh. Each contract can have a different period dates. They represent their accounting periods for which we need to produce reports from our data (fleet management and vehicle profiling data).
TIA.
Richard.
InDatabase Contract FromYear FromPeriod FromWeek FromDate ToYear ToPeriod ToWeek ToDate CurrentYear CurrentPeriod CurrentWeek CurrentOpened
CONTRACTS ALLROUND TSU 2008 7 0 2008/06/30 2008 10 0 2008/09/26
CONTRACTS ALLROUND TSU 2008 7 0 2008/06/30 2008 8 0 2008/07/30 2008 10 0 2008/09/26
CONTRACTS ALLROUND TSU 2008 8 0 2008/07/30 2008 9 0 2008/08/29 2008 10 0 2008/09/26
CONTRACTS ALLROUND TSU 2008 9 0 2008/08/29 2008 9 2 2008/09/04 2008 10 0 2008/09/26
CONTRACTS ALLROUND TSU 2008 9 2 2008/09/04 2008 9 3 2008/09/12 2008 10 0 2008/09/26
CONTRACTS ALLROUND TSU 2008 9 3 2008/09/12 2008 9 4 2008/09/19 2008 10 0 2008/09/26
CONTRACTS ALLROUND TSU 2008 9 4 2008/09/19 2008 10 0 2008/09/26 2008 10 0 2008/09/26
CONTRACTS ALLROUND TSU 2008 10 0 2008/09/26 2008 11 0 2008/10/29 2008 10 0 2008/09/26
CONTRACTS ALLROUND TSU 2008 11 0 2008/10/29 2008 12 0 2008/11/27 2008 10 0 2008/09/26
CONTRACTS ALLROUND TSU 2008 12 0 2008/11/27 2009 1 0 2008/12/31 2008 10 0 2008/09/26
ASKER
Ok.
How do I get the data out quickly? I need to preserve unique IDs as they are used through the tables.
How do I get the data out quickly? I need to preserve unique IDs as they are used through the tables.
ASKER
I used DTS to Excel and have put the SQL script in a separate tab.
I've had trouble importing it again, but I think that's due to foreign keys, so maybe temporarily drop them before the import.
Once you've got this in, I'm trying to get the start and end date for a contract and for an arbitrary period range.
The SP which needs the range will be supplied with Database, Contract and From/To Year/Period/Week (8 params in total).
So, From 2008 to 2008 would be for the entire year of 2008.
From 2008 to 2008/10 would be from the beginning of 2008 (or at least the first period logged) to the end of Period 10 for 2008.
RPM.XLS
I've had trouble importing it again, but I think that's due to foreign keys, so maybe temporarily drop them before the import.
Once you've got this in, I'm trying to get the start and end date for a contract and for an arbitrary period range.
The SP which needs the range will be supplied with Database, Contract and From/To Year/Period/Week (8 params in total).
So, From 2008 to 2008 would be for the entire year of 2008.
From 2008 to 2008/10 would be from the beginning of 2008 (or at least the first period logged) to the end of Period 10 for 2008.
RPM.XLS
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
will look at this this evening, eventually...
ASKER
Ta!
>From 2008 to 2008/10 would be from the beginning of 2008 (or at least the first period logged) to the end of Period 10 for 2008.
I read: period=month, right?
so, in resume: you want all the rows for the given Database, Contract,
starting from from_year/from_period/from
with:
if from_period / from_week is given as 0, the whole year/period is to be returned to start "from".
if to_period / to_week is given as 0, the whole year/period is to be set as end "to".
you can have both from_period/from_week vs to_period/to_week set to 0, or either.
what does period=13 mean?
week=1: that's the first week of the period/month...
question: what day does the week start with?
should we take @@firstdayofweek ?
week=5, in case there is no week 5, should be considered = 4, right?
does not look so hard...
please confirm, and I will write this for you...
I read: period=month, right?
so, in resume: you want all the rows for the given Database, Contract,
starting from from_year/from_period/from
with:
if from_period / from_week is given as 0, the whole year/period is to be returned to start "from".
if to_period / to_week is given as 0, the whole year/period is to be set as end "to".
you can have both from_period/from_week vs to_period/to_week set to 0, or either.
what does period=13 mean?
week=1: that's the first week of the period/month...
question: what day does the week start with?
should we take @@firstdayofweek ?
week=5, in case there is no week 5, should be considered = 4, right?
does not look so hard...
please confirm, and I will write this for you...
ASKER
Period sort of equals month.
Period 1 is the start of the contract's financial year.
So, Contract A Year 2008 Period 1 may be in January if they follow the calendar for their accounts.
Contract B Year 2008 Period 2 may be in April if they follow the Tax year.
My previous employer started their financial year in October.
Some companies run to 13 periods. These are NORMALLY (but not necessarily) 4 week periods with period 13 being short.
Some companies report on weeks. The week may start with any day of the week. A week 5 would start with the fifth occurrence of the start of the week.
How is all this being used?
I need to extract orders for a contract. In the main, the orders have the contract/year/period/week logged against the order, so these are easy.
Part of the data is a vehicle number.
Unfortunately, due to bugs in the code (a shrinked wrapped GUI application), the operators can sometime select the wrong vehicle. Trailers may have duplicate registrations, so the wrong one gets picked.
So I also need to pick all orders within a timeframe where the vehicles belonging to the contract have been used.
I need both routes as they determine different things.
I can't use just the y/p/w because contract a may be in 2008/5/0 and a vehicle used in that period may have been used in a contract which is in 2008/12/3
I didn't write this system. I just have to work with it.
With regard to the @@firstdayofweek, this is not appropriate.
The date ranges assigned to a period and/or a week are not our settings. They are supplied to us for by the contract. I have a spreadsheet which update the DB and reports inconsistencies in the patterns.
The dates the period/week start are the only things we have to work with.
Period 1 is the start of the contract's financial year.
So, Contract A Year 2008 Period 1 may be in January if they follow the calendar for their accounts.
Contract B Year 2008 Period 2 may be in April if they follow the Tax year.
My previous employer started their financial year in October.
Some companies run to 13 periods. These are NORMALLY (but not necessarily) 4 week periods with period 13 being short.
Some companies report on weeks. The week may start with any day of the week. A week 5 would start with the fifth occurrence of the start of the week.
How is all this being used?
I need to extract orders for a contract. In the main, the orders have the contract/year/period/week logged against the order, so these are easy.
Part of the data is a vehicle number.
Unfortunately, due to bugs in the code (a shrinked wrapped GUI application), the operators can sometime select the wrong vehicle. Trailers may have duplicate registrations, so the wrong one gets picked.
So I also need to pick all orders within a timeframe where the vehicles belonging to the contract have been used.
I need both routes as they determine different things.
I can't use just the y/p/w because contract a may be in 2008/5/0 and a vehicle used in that period may have been used in a contract which is in 2008/12/3
I didn't write this system. I just have to work with it.
With regard to the @@firstdayofweek, this is not appropriate.
The date ranges assigned to a period and/or a week are not our settings. They are supplied to us for by the contract. I have a spreadsheet which update the DB and reports inconsistencies in the patterns.
The dates the period/week start are the only things we have to work with.
ASKER
Oh. With regard to ...
"week=5, in case there is no week 5, should be considered = 4, right?"
No. If a specific period and / or week has been requested and it does not exist, then the request is invalid.
Some contracts change their period/weeks structure.
So, they may not actually have a week 1 for a period.
Also, it forces the request to only ask for accurate data.
Because ultimately the request has to return a date range, an invalid request has to result in no data being selected further down the chain.
e.g.
Total for 2008/1/1 = £1000
Total for 2008/1/2 = £2000
Total for 2008/1/3 = £3000
Total for 2008/1/4 = £4000
Assume that 2008/1/5 is invalid, returning the range for 2008/1/4 would result in an extra £4000.
"week=5, in case there is no week 5, should be considered = 4, right?"
No. If a specific period and / or week has been requested and it does not exist, then the request is invalid.
Some contracts change their period/weeks structure.
So, they may not actually have a week 1 for a period.
Also, it forces the request to only ask for accurate data.
Because ultimately the request has to return a date range, an invalid request has to result in no data being selected further down the chain.
e.g.
Total for 2008/1/1 = £1000
Total for 2008/1/2 = £2000
Total for 2008/1/3 = £3000
Total for 2008/1/4 = £4000
Assume that 2008/1/5 is invalid, returning the range for 2008/1/4 would result in an extra £4000.
in regards to the first part of your response:
WHERE is that information coded? I mean that a company starts on january/march/october/what
>Part of the data is a vehicle number.
that's again where you lost me, as I could not find any such information in the data anywhere?! ...
>I can't use just the y/p/w because contract a may be in 2008/5/0 and a vehicle used in that period may have been used in a contract which is in 2008/12/3
lost^2 ... :/
>With regard to the @@firstdayofweek, this is not appropriate.
ok, so here the question is same as above: where is this configured, per client/customer?
WHERE is that information coded? I mean that a company starts on january/march/october/what
>Part of the data is a vehicle number.
that's again where you lost me, as I could not find any such information in the data anywhere?! ...
>I can't use just the y/p/w because contract a may be in 2008/5/0 and a vehicle used in that period may have been used in a contract which is in 2008/12/3
lost^2 ... :/
>With regard to the @@firstdayofweek, this is not appropriate.
ok, so here the question is same as above: where is this configured, per client/customer?
ASKER
in regards to the first part of your response:
WHERE is that information coded? I mean that a company starts on january/march/october/what
RAQ: It isn't. We get the data from the clients and they simply say when the period starts.
>Part of the data is a vehicle number.
that's again where you lost me, as I could not find any such information in the data anywhere?! ...
RAQ: Sorry. I think I'm just confusing the issue here. Don't worry about this.
>I can't use just the y/p/w because contract a may be in 2008/5/0 and a vehicle used in that period may have been used in a contract which is in 2008/12/3
lost^2 ... :/
>With regard to the @@firstdayofweek, this is not appropriate.
ok, so here the question is same as above: where is this configured, per client/customer?
RAQ: It isn't. We have Periods/Weeks and start dates. Some customers have 8 and 6 day weeks if there is a bank holiday!
RAQ: The only info that is valid is the Contract/Year/Period/Week/
WHERE is that information coded? I mean that a company starts on january/march/october/what
RAQ: It isn't. We get the data from the clients and they simply say when the period starts.
>Part of the data is a vehicle number.
that's again where you lost me, as I could not find any such information in the data anywhere?! ...
RAQ: Sorry. I think I'm just confusing the issue here. Don't worry about this.
>I can't use just the y/p/w because contract a may be in 2008/5/0 and a vehicle used in that period may have been used in a contract which is in 2008/12/3
lost^2 ... :/
>With regard to the @@firstdayofweek, this is not appropriate.
ok, so here the question is same as above: where is this configured, per client/customer?
RAQ: It isn't. We have Periods/Weeks and start dates. Some customers have 8 and 6 day weeks if there is a bank holiday!
RAQ: The only info that is valid is the Contract/Year/Period/Week/
ASKER
It doesn't matter what the month name is.
No-one involved thinks in terms of Jan/Feb/Mar/etc. It is always "Period 4" or "last period", etc.
No-one involved thinks in terms of Jan/Feb/Mar/etc. It is always "Period 4" or "last period", etc.
so, you mean that, depending on the customer, the 2008/1 could mean 2008-jan for one customer, and 2008-march for another, and it remains relative to that customer? ...
that would also clarify the "day_of_week", as "you" don't work/handle those, and I now am 100% clear of what you meant by:
>We have Periods/Weeks ....
it's because it's only relevant to the data as it is stored, and not in regards to the calendar we use.
getdate() does not mean "anything" to that data...
>Assume that 2008/1/5 is invalid, returning the range for 2008/1/4 would result in an extra £4000.
just to clarify 2 things here:
does "invalid" mean that there is no such record in the database?
does this mean that we HAVE to "duplicate" the /4 data in that case?
that would also clarify the "day_of_week", as "you" don't work/handle those, and I now am 100% clear of what you meant by:
>We have Periods/Weeks ....
it's because it's only relevant to the data as it is stored, and not in regards to the calendar we use.
getdate() does not mean "anything" to that data...
>Assume that 2008/1/5 is invalid, returning the range for 2008/1/4 would result in an extra £4000.
just to clarify 2 things here:
does "invalid" mean that there is no such record in the database?
does this mean that we HAVE to "duplicate" the /4 data in that case?
ASKER
If the DB hasn't got a year/period/week for the requested year/period/week, then it is invalid.
So, if a contract is running weekly and a period has 4 weeks recorded for it in the DB, then asking for week 5 is wrong and shouldn't return a date range greater than 0s (essentially).
As the date range is what I'm going to use to get further data, I don't want to be retrieving anything for an invalid year/period/week.
So, no duplication.
So, if a contract is running weekly and a period has 4 weeks recorded for it in the DB, then asking for week 5 is wrong and shouldn't return a date range greater than 0s (essentially).
As the date range is what I'm going to use to get further data, I don't want to be retrieving anything for an invalid year/period/week.
So, no duplication.
I have not forgotten you... just have a rough week on the job ...
ASKER
No problem. I had my dev machine main data hard drive crash on me. Replace drive and memory was dead. Replaced memory and no longer boot.
Using a completely new machine and it is taking a while to rebuild everything.
Using VirtualBox for the live system now as I have the power to do so.
So a LONG road to PC Recovery.
Using a completely new machine and it is taking a while to rebuild everything.
Using VirtualBox for the live system now as I have the power to do so.
So a LONG road to PC Recovery.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok. Yes. I see where you're going with that.
Will not be able to look at this this week.
We've just shifted all our servers to VMs and I'm currently getting all my software operational again, so new work is on hold.
So, not abandoned, just delayed.
Will not be able to look at this this week.
We've just shifted all our servers to VMs and I'm currently getting all my software operational again, so new work is on hold.
So, not abandoned, just delayed.
same here... just migration the billing software...
ASKER
Big pause in the project. Supplied code gives me the basis to move forward when I return to the project.
could you send me the CREATE / INSERT sql stuff to create the table on my side?
and then, what exact data do you expect back? not 100% sure ...
angel eyes...