Advertisement

10.02.2008 at 03:45AM PDT, ID: 23781068 | Points: 500
[x]
Attachment Details

Confused by a complicated query dealing with date ranges.

Asked by RQuadling in MS SQL Server, SQL Reporting, SQL Query Syntax

Tags: , , ,

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.Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

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
 
Keywords: Confused by a complicated query deali…
Title
1 Complicated query
2 Historical math
3 How much?? $$$$$   Hourly Con…
4 Complicated Form
5 What is the philosophical meaning of thi…
6 Complicated Nested If
 
Loading Advertisement...
 
[+][-]10.03.2008 at 03:25AM PDT, ID: 22632469

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.03.2008 at 04:36AM PDT, ID: 22632785

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.03.2008 at 05:05AM PDT, ID: 22632969

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.03.2008 at 05:48AM PDT, ID: 22633277

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.03.2008 at 06:14AM PDT, ID: 22633488

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.06.2008 at 01:53PM PDT, ID: 22654327

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.06.2008 at 02:42PM PDT, ID: 22654662

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.06.2008 at 02:46PM PDT, ID: 22654695

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.06.2008 at 02:55PM PDT, ID: 22654756

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.06.2008 at 03:01PM PDT, ID: 22654797

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.06.2008 at 03:04PM PDT, ID: 22654821

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.06.2008 at 03:16PM PDT, ID: 22654907

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.06.2008 at 04:46PM PDT, ID: 22655489

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.09.2008 at 03:56PM PDT, ID: 22683203

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.10.2008 at 12:49AM PDT, ID: 22684966

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.11.2008 at 12:36PM PDT, ID: 22695070

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.13.2008 at 04:39AM PDT, ID: 22701552

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.13.2008 at 05:15AM PDT, ID: 22701709

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081112-EE-VQP-44 - Hierarchy / EE_QW_EXPERT_20070906