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 6 3228 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
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
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; 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
You also need to check if anyone is before the earliest date.
B.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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:
|
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
|
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.
|
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 ...
|
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...
| |
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
|
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.
|
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...
|
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...
|
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...
|
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...
| |
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...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
| |
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...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |