Solved

Confused by a complicated query dealing with date ranges.

Posted on 2008-10-02
20
245 Views
Last Modified: 2012-05-05
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
Comment
Question by:RQuadling
  • 11
  • 8
20 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
Ok.

How do I get the data out quickly? I need to preserve unique IDs as they are used through the tables.
0
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
will look at this this evening, eventually...
0
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
Ta!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I have not forgotten you... just have a rough week on the job ...
0
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
same here... just migration the billing software...
0
 
LVL 40

Author Closing Comment

by:RQuadling
Comment Utility
Big pause in the project. Supplied code gives me the basis to move forward when I return to the project.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now