473,848 Members | 1,568 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Problem with date ranges

Hello,

how do I write this SQL:
I would like to check the Main table for invalid rows. An invalid row
is: any row where the Start_date to stop_date range overlaps an invalid
date in the Code table. For example, Row#2 is invalid because the
Start_date-Stop_Date range overlaps 2 days in the code table where the
code AA was not valid (12/30/2000 - 12/31/2000)

Main Table
Row# emp_id code start_date stop_date
1 98233 AA 05/05/2000 12/28/2000
2 98235 AA 05/05/2000 04/21/2001
Code Table
Code Description eff_Date end_Date
AA Hospitals 01/01/2000 12/29/2000
AA Hospitals, Schools 01/01/2001 12/31/9999
XX N/A 01/01/2009 12/31/9999

Thanks
Alex

Nov 30 '05 #1
6 3242
al************* @hotmail.com wrote:
Hello,

how do I write this SQL:
I would like to check the Main table for invalid rows. An invalid row
is: any row where the Start_date to stop_date range overlaps an invalid
date in the Code table. For example, Row#2 is invalid because the
Start_date-Stop_Date range overlaps 2 days in the code table where the
code AA was not valid (12/30/2000 - 12/31/2000)

Main Table
Row# emp_id code start_date stop_date
1 98233 AA 05/05/2000 12/28/2000
2 98235 AA 05/05/2000 04/21/2001
Code Table
Code Description eff_Date end_Date
AA Hospitals 01/01/2000 12/29/2000
AA Hospitals, Schools 01/01/2001 12/31/9999
XX N/A 01/01/2009 12/31/9999


This looks suspiciously like an exercise at school to me. So I don't want
to give you the answer (because it is very simple) but rather like to know
what you have already tried? I.e. what's your SQL statement so far? Based
on that, we can surely help you to figure out misconceptions if there are
any.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 1 '05 #2
Knut,
I have a solution. If there is a more efficient method let me know:

--1) create tables and insert data

drop table main;
create table main
(row integer, emp_id integer, code char(2), start_date date,
stop_date date );

insert into main
(row, emp_id, code,start_date , stop_date)
values
(1,98233,'AA',' 05/05/2000','12/28/2000'),
(2,98235,'AA',' 05/05/2000','04/21/2001');

select * from main;

drop table code;
drop table code_ct;
create table code_ct
(code char(2), desc varchar(40), eff_date date, end_date date);

insert into code_ct
values
('AA', 'Hospitals', '01/01/2000', '12/29/2000'),
('AA', 'Hospitals, Schools', '01/01/2001', '12/31/9999'),
('XX', 'N/A', '01/01/2009', '12/31/9999');

select * from code_ct;
--2a) find the rows in the code table with a gap between end_date and
start_date. it doesnt matter how large the gap is
--2b) find any gap date
--2c) finally, return any rows where the gap date falls
-- between the main.start_date and main.stop_date

select a.row, a.emp_id, a.code,a.start_ Date, GapRows.GapDate ,
a.stop_date
from main a,
(
select code,EFF_DATE,E ND_DATE,next_ef f_Date, NEXT_EFF_DATE - 1 day as
GapDate
from table (select CT.code, CT.EFF_DATE, CT.END_DATE,
max(eff_date) over (partition by code order by
code, eff_date rows between 1 following and 1 following) as
next_eff_Date
from code_ct CT ) as tmp
(code,EFF_DATE, END_DATE,next_e ff_Date)
where (next_eff_Date - END_DATE) > 1
) as GapRows (code, EFF_DATE, END_DATE, NEXT_EFF_DATE, GapDate )
where a.code = GapRows.code
and GapDate between a.start_date and a.stop_date;
Knut Stolze wrote:
al************* @hotmail.com wrote:
Hello,

how do I write this SQL:
I would like to check the Main table for invalid rows. An invalid row
is: any row where the Start_date to stop_date range overlaps an invalid
date in the Code table. For example, Row#2 is invalid because the
Start_date-Stop_Date range overlaps 2 days in the code table where the
code AA was not valid (12/30/2000 - 12/31/2000)

Main Table
Row# emp_id code start_date stop_date
1 98233 AA 05/05/2000 12/28/2000
2 98235 AA 05/05/2000 04/21/2001
Code Table
Code Description eff_Date end_Date
AA Hospitals 01/01/2000 12/29/2000
AA Hospitals, Schools 01/01/2001 12/31/9999
XX N/A 01/01/2009 12/31/9999


This looks suspiciously like an exercise at school to me. So I don't want
to give you the answer (because it is very simple) but rather like to know
what you have already tried? I.e. what's your SQL statement so far? Based
on that, we can surely help you to figure out misconceptions if there are
any.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany


Dec 1 '05 #3
Knut, I found a solution. if there is a more efficient method let me
know.

The key is to find a gap date between dates in the code table, for a
specific code; and figure out if that gap date falls between the
start_date and stop_date in the main table.
drop table main;
create table main
(row integer, emp_id integer, code char(2), start_date date,
stop_date date );

insert into main
(row, emp_id, code,start_date , stop_date)
values
(1,98233,'AA',' 05/05/2000','12/28/2000'),
(2,98235,'AA',' 05/05/2000','04/21/2001');

select * from main;

drop table code;
drop table code_ct;
create table code_ct
(code char(2), desc varchar(40), eff_date date, end_date date);

insert into code_ct
values
('AA', 'Hospitals', '01/01/2000', '12/29/2000'),
('AA', 'Hospitals, Schools', '01/01/2001', '12/31/9999'),
('XX', 'N/A', '01/01/2009', '12/31/9999');

select * from code_ct;

select a.row, a.emp_id, a.code,a.start_ Date, GapRows.GapDate ,
a.stop_date
from main a,
(
select code,EFF_DATE,E ND_DATE,next_ef f_Date, NEXT_EFF_DATE - 1 day as
GapDate
from table (select CT.code, CT.EFF_DATE, CT.END_DATE,
max(eff_date) over (partition by code order by
code, eff_date rows between 1 following and 1 following) as
next_eff_Date
from code_ct CT ) as tmp
(code,EFF_DATE, END_DATE,next_e ff_Date)
where (next_eff_Date - END_DATE) > 1
) as GapRows (code, EFF_DATE, END_DATE, NEXT_EFF_DATE, GapDate )
where a.code = GapRows.code
and GapDate between a.start_date and a.stop_date;

Dec 1 '05 #4
al************* @hotmail.com wrote:
Knut Stolze wrote:
al************* @hotmail.com wrote:
> how do I write this SQL:
> I would like to check the Main table for invalid rows. An invalid row
> is: any row where the Start_date to stop_date range overlaps an invalid
> date in the Code table. For example, Row#2 is invalid because the
> Start_date-Stop_Date range overlaps 2 days in the code table where the
> code AA was not valid (12/30/2000 - 12/31/2000)
If I got this right, then an "invalid date range" is a range that is not
covered by the dates in the code table, correct?
> Main Table
> Row# emp_id code start_date stop_date
> 1 98233 AA 05/05/2000 12/28/2000
> 2 98235 AA 05/05/2000 04/21/2001
>
>
> Code Table
> Code Description eff_Date end_Date
> AA Hospitals 01/01/2000 12/29/2000
> AA Hospitals, Schools 01/01/2001 12/31/9999
> XX N/A 01/01/2009 12/31/9999
This looks suspiciously like an exercise at school to me. So I don't
want to give you the answer (because it is very simple) but rather like
to know
what you have already tried? I.e. what's your SQL statement so far?
Based on that, we can surely help you to figure out misconceptions if
there are any.

--2a) find the rows in the code table with a gap between end_date and
start_date. it doesnt matter how large the gap is
--2b) find any gap date
--2c) finally, return any rows where the gap date falls
-- between the main.start_date and main.stop_date

select a.row, a.emp_id, a.code,a.start_ Date, GapRows.GapDate ,
a.stop_date
from main a,
( select code, EFF_DATE, END_DATE, next_eff_Date,
NEXT_EFF_DATE - 1 day as GapDate
from ( select CT.code, CT.EFF_DATE, CT.END_DATE,
max(eff_date) over (partition by code order by
code, eff_date rows between 1 following and
1 following) as next_eff_Date
from code_ct CT ) as tmp(code, EFF_DATE, END_DATE,
next_eff_Date)
where (next_eff_Date - END_DATE) > 1 ) AS gapRows(code,
EFF_DATE, END_DATE, NEXT_EFF_DATE, GapDate )
where a.code = GapRows.code AND
GapDate between a.start_date and a.stop_date;


That does look quite fine to me. I'd probably do it that way:
(1) check for each interval/range in the main table if it overlaps with a
range in the code table, and
(2) if it overlaps, then check that the date right before and after the
overlaps are covered by another range from the code table

SELECT ...
FROM main AS m JOIN code_table AS ct ON
m.code = ct.code
WHERE m.start_date < ct.end_date AND
m.end_date > ct.eff_date AND
-- check if another range in CT lies before the current one
-- (only if the range in main exceeds the range in CT)
CASE
WHEN m.start_date < ct.eff_date
THEN ( SELECT MAX(1)
FROM code_table AS b
WHERE b.code = m.code AND
b.end_date >= m.start_date AND
b.eff_date <= m.start_date )
ELSE 0
END = 1 AND
-- check if another range in CT lies after the current one
-- (only if the range in main exceeds the range in CT)
CASE
WHEN m.end_date > ct.end_date
THEN ( SELECT MAX(1)
FROM code_table AS a
WHERE b.code = m.code AND
b.eff_date <= m.end_date AND
b.end_date >= m.end_date )
ELSE 0
END = 1

The subselects in the case expressions could also be rewritten, I'd say,
Maybe that would make things more clearer.


--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 2 '05 #5
You also need to check if anyone is before the earliest date.

B.

Dec 2 '05 #6
This was a good exercise. It took me a while to come up with a
solution, and that's how i learn things. Here's my solution, which is
just what i came up with, not that it is better than anybody else's.

The CASE expressions are because the DATE given is the final valid DATE
in db2, and adding a DAY results in SQL0183N. I add a DAY (or subtract)
because the end date of one range can be one day before the begin date
of the next range, and there will be no missed days. Since your example
provide the final valid DATE, i assumed there could be the earliest one
too.

The GROUP BY at the end is simply so only one row shows up per row in
the Main TABLE, otherwise more than one may appear. In this example,
two rows would appear instead.

I like my solution because it is straightforward . The WITH provides the
bad days being check for, and then a simple BETWEEN is used.

DECLARE GLOBAL TEMPORARY TABLE Main
(
Row INTEGER,
Emp_Id INTEGER,
Code CHAR(0002),
Start_Date DATE,
Stop_Date DATE
) WITH REPLACE

DECLARE GLOBAL TEMPORARY TABLE Code_CT
(
Code CHAR(2),
Desc VARCHAR(0040),
Eff_Date DATE,
End_Date DATE
) WITH REPLACE

INSERT INTO SESSION.Main
VALUES
(1, 98233, 'AA', '05/05/2000', '12/28/2000'),
(2, 98235, 'AA', '05/05/2000', '04/21/2001')

INSERT INTO SESSION.Code_CT
VALUES
('AA', 'Hospitals', '01/01/2000', '12/29/2000'),
('AA', 'Hospitals, Schools', '01/01/2001', '12/31/9999'),
('XX', 'N/A', '01/01/2009', '12/31/9999')

WITH Bad_Day (Code, The_Day) AS
(
SELECT
Code,
End_Date + 1 DAY
FROM
SESSION.Code_CT Outer
WHERE
End_Date < DATE('12/31/9999')
AND NOT EXISTS
(
SELECT
*
FROM
SESSION.Code_CT Inner
WHERE
Inner.Code = Outer.Code
AND Outer.End_Date BETWEEN
CASE Inner.Eff_Date
WHEN DATE('01/01/0001') THEN Inner.Eff_Date
ELSE Inner.Eff_Date - 1 DAY
END
AND CASE Inner.End_Date
WHEN DATE('01/01/0001') THEN Inner.End_Date
ELSE Inner.End_Date - 1 DAY
END
)
UNION ALL
SELECT
Code,
Eff_Date - 1 DAY
FROM
SESSION.Code_CT Outer
WHERE
Eff_Date > DATE('01/01/0001')
AND NOT EXISTS
(
SELECT
*
FROM
SESSION.Code_CT Inner
WHERE
Inner.Code = Outer.Code
AND Outer.Eff_Date BETWEEN
CASE Inner.Eff_Date
WHEN DATE('12/31/9999') THEN Inner.Eff_Date
ELSE Inner.Eff_Date + 1 DAY
END
AND CASE Inner.End_Date
WHEN DATE('12/31/9999') THEN Inner.End_Date
ELSE Inner.End_Date + 1 DAY
END
)
)
SELECT
Main.Row,
Main.Emp_Id,
Main.Code,
Main.Start_Date ,
Main.Stop_Date,
MIN(Bad_Day.The _Day)
FROM
SESSION.Main Main,
Bad_Day
WHERE
Main.Code = Bad_Day.Code
AND Bad_Day.The_Day BETWEEN Main.Start_Date AND Main.Stop_Date
GROUP BY
Main.Row,
Main.Emp_Id,
Main.Code,
Main.Start_Date ,
Main.Stop_Date

DROP TABLE SESSION.Main
DROP TABLE SESSION.Code_CT
B.

Dec 2 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
6497
by: Maksim Kasimov | last post by:
there are few of a time periods, for example: 2005-06-08 12:30 -> 2005-06-10 15:30, 2005-06-12 12:30 -> 2005-06-14 15:30 and there is some date and time value: 2005-06-11 12:30 what is the "pythonic" way to check is the date/time value in the given periods range? something like xrange:
2
776
by: bobb | last post by:
Is there a way to select count(*) by grouping by date, and having multiple date ranges? combining... select field,count(*) from table where datefield > 2004/1/1 and datefield < 2004/1/31 and select field,count(*) from table where datefield > 2004/2/1 and datefield < 2004/2/29
18
17550
by: LarsM | last post by:
Hi all, I am new to XML, but I use it for an RSS feed. I have one problem, which I have really been struggling with. My XML document is generated from the contents of a MySQL database. It is UTF-8 encoded. However, the Danish special characters appear wrong.
1
3366
by: poohnie08 | last post by:
i have a excel spreadsheet showing staff name, date,work hour, ot hour, slot1, slot2, slot3, slot4 and others). The "()" will keep repeating from day 1 until end of month. eg in excel spreadsheet, ============================================================================== A1 |A2 A3 A4 A5 A6 A7 A8 |A9 A10 A11 | 01/02/04 |02/02/04 StaffName |Work Hr OT Hr Slot1...
12
6405
by: Steve Elliott | last post by:
I have a query set up to gather together data between two specified dates. Shown in the query column as: Between #24/09/2004# And #01/10/2004# Is it possible to enter several different date ranges, ie between 24/09/2004 and 01/10/2004 together with 05/10/2004 and 07/10/2004 ? If I enter the "Between" criteria on different lines it returns no data.
2
2025
by: junkaccount | last post by:
Hello, Using Access 2000 I would like to create one report that returns sales data for various date ranges input by the user i.e. weekly, monthly, etc. This report is bound to a query that pulls the info from a table containing all data. How would I set up my report to prompt for the multiple data ranges I want to see? Thanks, Jason
67
7736
by: PC Datasheet | last post by:
Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 4/30/06 I am looking for suggestions on how to find the date ranges where there were no transactions.
0
2055
by: =?Utf-8?B?TGV0emRvXzF0?= | last post by:
I'd like to create a Macro that will sort some raw data, apprx 20k lines, remove some lines based upon a condition in a certain column. Then copy this data into a new spreadsheet and sort the data again and delete the unwanted data and repeat few more times in new sheets. End product will be apprximately 7 or 8 sheets - 1 for Active Customers, Inactive Customers, Pending Installs, Etc... I'm getting hung up I believe with naming...
16
7257
by: Alex30093 | last post by:
OK This is my 1st post to any forum, but I rely on forums all the time. Short story is I find myself needing to become a Access DBA for work. Situation: I want to use one table of events that Track (Employee ID, Employee Name, Event Date and Trip Hours) And be able to add the fields(Week #1, Week #2, Week#3, week #4 and Week #5). *****There is also an Autonumbered (Trip Numebr). What I have tried: I can run this as a query (Employee ID,...
5
13399
matheussousuke
by: matheussousuke | last post by:
Hello, I'm using tiny MCE plugin on my oscommerce and it is inserting my website URL when I use insert image function in the emails. The goal is: Make it send the email with the URL http://mghospedagem.com/images/controlpanel.jpg instead of http://mghospedagem.comhttp://mghospedagem.com/images/controlpanel.jpg As u see, there's the website URL before the image URL.
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9735
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10661
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10347
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7067
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5731
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5917
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4542
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.