Date queries

[Raj]

I am a novice at working with queries and need some quick
help in getting a few queries for a project.
I have 3 primary fields that I will be working with
[CircuitID], [BillFromDate], [BillToDate].

I need queries that will pull overlaps in billing dates
for [CircuitID], gaps in billing dates for [CircuitID],
Ex. [CircuitID] BG4890X874 [BillFromDate] 3/1/03,
[BillToDate] 4/18/03
[CircuitID] BG4890X874 [BillFromDate] 4/1/03,
[BillToDate] 4/30/03
** [BillToDate] extended into April**

ex. [CircuitID] BG4890X874 [BillFromDate] 3/1/03,
[BillToDate] 3/31/03, [BillFromDate] 5/1/03, [BillToDate]
5/31/03. Missing billing for the Month of April.

I also need a query for will find the date range between
[BillFromDate] and [BillToDate] days more than 30 days
per billing period.
Ex. 4/1/03 - 5/15/03 45
Thank you

 

[Brian Camire]

You might try queries whose SQL looks something like those embedded below.

I am a novice at working with queries and need some quick
help in getting a few queries for a project.
I have 3 primary fields that I will be working with
[CircuitID], [BillFromDate], [BillToDate].

I need queries that will pull overlaps in billing dates
for [CircuitID], gaps in billing dates for [CircuitID],
Ex. [CircuitID] BG4890X874 [BillFromDate] 3/1/03,
[BillToDate] 4/18/03
[CircuitID] BG4890X874 [BillFromDate] 4/1/03,
[BillToDate] 4/30/03
** [BillToDate] extended into April**

SELECT
[Your Table].[CircuitID],
[Your Table].[BillFromDate],
[Your Table].[BillToDate]
[Self].[BillFromDate] AS [OverlappingBillFromDate],
[Self].[BillToDate] AS [OverlappingBillToDate]
FROM
[Your Table]
INNER JOIN
[Your Table] AS [Self]
ON
[Your Table].[CircuitID] = [Self].[CircuitID]
WHERE
[Self].[BillFromDate] < [Your Table].[BillToDate]
AND
[Self].[BillToDate] > [Your Table].[BillFromDate]

ex. [CircuitID] BG4890X874 [BillFromDate] 3/1/03,
[BillToDate] 3/31/03, [BillFromDate] 5/1/03, [BillToDate]
5/31/03. Missing billing for the Month of April.

SELECT
[Your Table].[CircuitID],
[Your Table].[BillFromDate],
[Your Table].[BillToDate]
Min([Self].[BillFromDate]) AS [NextBillFromDate]
FROM
[Your Table]
INNER JOIN
[Your Table] AS [Self]
ON
[Your Table].[CircuitID] = [Self].[CircuitID]
WHERE
[Self].[BillFromDate] >= [Your Table].[BillToDate]
HAVING
Min([Self].[BillFromDate]) > [Your Table].[BillToDate]

I also need a query for will find the date range between
[BillFromDate] and [BillToDate] days more than 30 days
per billing period.
Ex. 4/1/03 - 5/15/03 45

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[BillToDate] > [Your Table].[BillFromDate] + 30