• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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/Period/Week.

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

Open in new window

0
Richard Quadling
Asked:
Richard Quadling
  • 11
  • 8
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Hi,

  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...
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Ok.

How do I get the data out quickly? I need to preserve unique IDs as they are used through the tables.
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
will look at this this evening, eventually...
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Ta!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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_week earliest until to_year/to_period/to_week latest
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...


0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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.

0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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.




0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in regards to the first part of your response:
WHERE is that information coded? I mean that a company starts on january/march/october/whatever, ie has 12/13 periods etc?

>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?




0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
in regards to the first part of your response:
WHERE is that information coded? I mean that a company starts on january/march/october/whatever, ie has 12/13 periods etc?


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/StartDate. That's all there is to work with.

0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?


0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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.



0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have not forgotten you... just have a rough week on the job ...
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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.


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, let's see if this is a starter:
SELECT ...
  FROM ...
 WHERE ThisYear >= @From_Year
   AND ThisYear <= @To_Year
   AND (   ( @From_Period = 0 AND @To_Period = 0  )
        OR ( @From_Period = 0 AND @To_Period > 0 AND ( ThisYear < @To_Year OR ThisMonth <= @To_Month) )
        OR ( @From_Period > 0 AND @To_Period = 0 AND ( ThisYear > @From_Year OR ThismMonth >= @From_Period ) )
        OR ( @From_Period > 0 AND @To_Period > 0 AND ( ThisYear > @From_Year OR ThisMonth >= @From_Period ) AND ( ThisYear < @To_Year OR ThisMonth <= @To_Period ) )
       )
   AND (    ( @From_Week = 0 AND @To_Week = 0 AND ThisWeek = 0 )
         OR ( @From_Week > 0 AND @To_Week > 0 AND ( ThisYear > @From_Year OR ThisMonth > @FromPeriod OR ThisWeek >= @From_Week ) AND ( ThisYear < @To_Year OR ThisMonth < @To_Period OR ThisWeek <= @To_Week ) )
       )

Open in new window

0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
same here... just migration the billing software...
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Big pause in the project. Supplied code gives me the basis to move forward when I return to the project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now