August 30, 2014 at 12:41 pm
Hi -- I am writing a query to with left excluding join.
TBL1 is a location / date range table with location key (LOCATN_K). A location is considered "alive" on a specific date if STRT_D is earlier than the date and XCHG_OUT_D is later than it (or missing). This table contains ALL locations by date.
TBL2 is a location / date table with location specific columns. On a given date, this table contains a subset of tables implied by TBL1.
The goal of the query is to find out all missing locations not in TBL2 according to TBL1 by date. I am able to use the query below for a specific date and it works fine.
SELECT A.LOCATN_K FROM TBL1 A
WHERE A.STRT_D < '2013-07-08'
AND (A.XCHG_OUT_D > '2013-07-08' OR A.XCHG_OUT_D IS NULL)
EXCEPT
SELECT C.LOCATN_K FROM TBL2 C
WHERE C.DATE = '2013-07-08'
Now my question is, without using a cursor (to loop all dates outside the query), is there a way to write one query that lists all dates and missing locations (by date)? The available dates can be found by select distinct date from TBL2.
Your help will be highly appreciated!
Bo
August 30, 2014 at 3:34 pm
Does this do what you need?
SELECT A.LOCATN_K FROM TBL1 as A WHERE not exists (
select 1 from TBL2 as C where
C.LOCATN_K = A.LOCATN_K and
A.STRT_D < C.DATE and
(A.XCHG_OUT_D > C.DATE OR A.XCHG_OUT_D IS NULL)
) ;
If not, could you explain more clearly what is needed?
Tom
August 30, 2014 at 4:29 pm
Thanks Tom. Not exactly -- I need dates along with missing locations on those dates.
August 30, 2014 at 5:04 pm
It would help if you provided some DDL (CREATE TABLE statement) for the table(s) involved, some sample data representative of the problem domain for the tables (as INSERT INTO statements) and your expected results based on the sample data.
Pretty sure what you need can be done without a cursor but seeing the DDL, sample data, and expected results will go a long way in helping us help you.
August 30, 2014 at 5:27 pm
luboxing (8/30/2014)
Thanks Tom. Not exactly -- I need dates along with missing locations on those dates.
well, a first step towards that is
SELECT A.LOCATN_K, A.STRT_D, A,XXCHG_OUT_D FROM TBL1 as A WHERE not exists (
select 1 from TBL2 as C where
C.LOCATN_K = A.LOCATN_K and
A.STRT_D < C.DATE and
(A.XCHG_OUT_D > C.DATE OR A.XCHG_OUT_D IS NULL)
) ;
Using that as a CTE or a subquery a join with a tally table (or a tally CTE) can be used to blow up each row into a row for each day its range covers, with the results sorted by date and location code; if you then want to denormalise the result of that to get rows each with a date and a list of location codes that's probably best left to formatting outside the SQL system, although it can be done in the query if necessary.
edit: doing that of course requires replacing NULL XCHG_OUT dates by a specific end date, which could be either handed in as a constant or calculated in the query, for example as the latest of any date mentioned in any of the three date columns accessed by the query, or the day the query is run on if that is later.
Tom
August 30, 2014 at 7:59 pm
Thanks again... However I need the date column from TBL1 so that I get results like:
DATE LOCATN_K
2013-07-08 123
2013-07-08 126
Sorry I should have used the example below.
In TBL1 I have:
LOCATN_K STRT_D XCHG_OUT_D
1 1998-09-02 2004-01-02
2 2014-03-02
3 2009-04-01
123 2009-04-01 2014-01-09
126 2011-04-22
Now in this table, 3 locations (3, 123, 126) cover the date of '2013-07-08' and they are considered "alive".
However, in TBL2 I have:
DATE LOCATN_K
....
2013-07-08 3
2013-07-09 188
...
Only one (LOCATN_K=3) of the three locations has data in TBL2.
So the result (missing locations) for date "2013-07-08" is:
DATE LOCATN_K
2013-07-08 123
2013-07-08 126
For the job, I need all dates and missing locations associated with them. They should look like:
DATE LOCATN_K
2013-01-01 19
2013-01-02 127
2013-01-02 125
...
2013-07-08 123
2013-07-08 126
...
2013-12-31 209
Actually I did find a solution after posting the question:
SELECT B.DATE, A.LOCATN_K
FROM TBL1 AS A INNER JOIN
(SELECT DISTINCT DATE FROM TBL2) AS B
ON A.STRT_D < B.DATE AND (A.MTR_XCHG_OUT_D > B.DATE OR A.MTR_XCHG_OUT_D IS NULL)
LEFT OUTER JOIN TBL2 AS C
ON B.DATE = C.DATE AND A.LOCATN_K = C.LOCATN_K
WHERE C.LOCATN_K IS NULL AND C.MTR_NB IS NULL
However I am not sure it can still be improved. (such as removing the select distinct sub query)
Thanks for all your inputs!
Bo
August 30, 2014 at 8:04 pm
luboxing (8/30/2014)
Thanks again... However I need the date column from TBL1 so that I get results like:DATE LOCATN_K
2013-07-08 123
2013-07-08 126
Sorry I should have used the example below.
In TBL1 I have:
LOCATN_K STRT_D XCHG_OUT_D
1 1998-09-02 2004-01-02
2 2014-03-02
3 2009-04-01
123 2009-04-01 2014-01-09
126 2011-04-22
Now in this table, 3 locations (3, 123, 126) cover the date of '2013-07-08' and they are considered "alive".
However, in TBL2 I have:
DATE LOCATN_K
....
2013-07-08 3
2013-07-09 188
...
Only one (LOCATN_K=3) of the three locations has data in TBL2.
So the result (missing locations) for date "2013-07-08" is:
DATE LOCATN_K
2013-07-08 123
2013-07-08 126
For the job, I need all dates and missing locations associated with them. They should look like:
DATE LOCATN_K
2013-01-01 19
2013-01-02 127
2013-01-02 125
...
2013-07-08 123
2013-07-08 126
...
2013-12-31 209
Actually I did find a solution after posting the question:
SELECT B.DATE, A.LOCATN_KFROM TBL1 AS A INNER JOIN
(SELECT DISTINCT DATE FROM TBL2) AS B
ON A.STRT_D < B.DATE AND (A.MTR_XCHG_OUT_D > B.DATE OR A.MTR_XCHG_OUT_D IS NULL)
LEFT OUTER JOIN TBL2 AS C
ON B.DATE = C.DATE AND A.LOCATN_K = C.LOCATN_K
WHERE C.LOCATN_K IS NULL AND C.MTR_NB IS NULL
However I am not sure it can still be improved. (such as removing the select distinct sub query)
Thanks for all your inputs!
Bo
Nope, doesn't help me as it isn't what I asked you for. Nothing you posted can be used to setup a test environment and work on a potential solution to your problem.
Guess I leave it to Tom for now.
August 31, 2014 at 7:02 am
I am finding the requirement hard to understand because the results don't appear to match the description, and teh code which you said works refers to columns whci aren't shown in your data so presumably aren't in the tables!
However, going on the description I can see roughly what you are getting at; there are two possibilities, depending on whether something in table B for which there is nothing in table A with the same location code (regardless of date) is a date that has to be include in the result. If it is, I think that this works;
select B.date, A.locatn_k from tbl1 A, tbl2 B
where A.strt_d <= B.date and IsNull(A.xchg_out_d,B.date) >= B.date
and not exists (select 1 from tbl2 C where C.date = B.date and C.locatn_k = A.locatn_k)
;
and if it isn't then I think this works:-
select B.date, A.locatn_k from tbl1 A, tbl2 B
where A.strt_d <= B.date and IsNull(A.xchg_out_d,B.date) >= B.date
and B.locatn_k in (select locatn_k from tbl1)
and not exists (select 1 from tbl2 C where C.date = B.date and C.locatn_k = A.locatn_k)
;
I hope that's useful.
Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply