Left Outer Join Query Help 1

[fmrock]

hey everyone,

Just wondering if you could give me a little insite on this query, which seems to be very simple, yet not returning the respected results

The locations table has 99 locations, however only 53 locations had accounts in Jan 2007. I am doing a left outer join to hopefully have all the locations listed and the number of accounts for each location. if the location has 0, I would still like it listed.

Here is an example of the query. I have tried it a few different ways, all with the same results

SELECT  
	L.loccpcode,
	count(A.acctcode) MnthTotal
FROM  
	locations L
	LEFT OUTER JOIN ACCOUNT A ON (L.loccpcode = A.acctcpcode)
WHERE
	TO_CHAR(TO_DATE(A.ACCTLOGDATE,'J'),'YYYY') = '2007'
	AND TO_CHAR(TO_DATE(A.ACCTLOGDATE,'J'),'mm') = '01'
	AND (A.ACCTDEACTIVE IS NULL)
GROUP BY 
	L.loccpcode

 

[SantaMufasa]

fmrock,

I believe your problem is that despite Oracle's dutifully OUTER JOINing your tables (thus starting out with all 99 rows from LOCATIONS), your WHERE clause eliminates those 46 outer-joined rows because they do not match your DATE requirements on the ACCOUNT table.

Therefore, I believe you can get the results you want via this query:

SELECT L.loccpcode
      ,count(A.acctcode) MnthTotal
  FROM locations L
      ,(SELECT * FROM ACCOUNT
         WHERE TO_CHAR(TO_DATE(A.ACCTLOGDATE,'J'),'YYYY')
               = '2007'
           AND TO_CHAR(TO_DATE(A.ACCTLOGDATE,'J'),'mm')
               = '01'
           AND (A.ACCTDEACTIVE IS NULL))A
 WHERE L.loccpcode = A.acctcpcode(+)
 GROUP BY L.loccpcode;

Let us know how that works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

BTW, you can simplify/contract your DATE check by saying, instead:

...WHERE TO_CHAR(TO_DATE(A.ACCTLOGDATE,'J'),'YYYYMM') = '200701'

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

I just left work, so i will try this first thing in the morning. That makes way more sense. Been a very long day.

 

[fmrock]

Thanks SantaMufasa... This worked great. Now time to keep adding to this. This is just a start of this query.

Thanks for your help.

 

[fmrock]

Here is the next step I am having some problmes with.

I want to run the report for a time frame. however, of the 99 locations, some of those locations may not have been active durning the time frame selected.

We want to know the count, per location, per month. And even if there are no accounts for that location/month would like it in the query as 0.

SELECT L.loccpcode,L.loccode, count(C.Account) MnthTotal
FROM medlocations L
LEFT JOIN 
(SELECT DISTINCT CPCODE, ACCOUNT, DOCLOC FROM MEDCHARGES WHERE SPLITFLAG IS NULL AND TYPE = 'C'
AND TO_CHAR(TO_DATE(SERVICEDATE,'J'),'YYYYMMDD') BETWEEN '20070101' AND '20070131') C
ON L.loccpcode = C.CPCODE AND L.loccode = C.docloc
GROUP BY L.loccpcode, L.loccode
ORDER BY L.loccpcode, L.loccode;

The goal will be to run this for the entire year and get the count per location, per month.

The date logic we have will change, it is there just to test 1 month, the charges table has 12 million records, and trying to get results in a timely fashon while testing.

Thanks

 

[SantaMufasa]

fmrock,

Obviously, I cannot test out your code due to my not having sample tables, but I believe that the following should do what your want:

SELECT L.loccpcode,L.loccode, count(C.Account) MnthTotal
  FROM medlocations L
  LEFT JOIN 
   (SELECT DISTINCT CPCODE
                   ,ACCOUNT
                   ,DOCLOC
                   ,TO_CHAR(TO_DATE(SERVICEDATE,'J')
                            ,'YYYY-MM') SERVICE_MONTH
      FROM MEDCHARGES
     WHERE SPLITFLAG IS NULL
       AND TYPE = 'C'
   ) C
    ON L.loccpcode = C.CPCODE AND L.loccode = C.docloc
 GROUP BY L.loccpcode, L.loccode, c.service_month
 ORDER BY L.loccpcode, L.loccode, c.service_month;

Try it and let us know how it works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

I guess where we are having a problem is that i need a record for each location for months 1 though 12.

For example, Say location 123456 started service in March. Month 1 and 2 should have a 0 count and still be in the report.

The way the query is now, those months are not in the query.

 

[SantaMufasa]

Before I propose an enhancement to the script to resolve your need, please advise the following:[ul][li]For any given run of the script, how many years of 12 months do you want to see for each location? (e.g., 12 months for all years on file, or 12 months for just the current year?)[/li][li]How many rows are in the location table?[/li][/ul]Thanks.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

Mufasa,

There are 99 different locations.

We would at most go back to get 3 years. So, 2007,2006,2005.

The current report which a department creates is a very manual process.

Also, once we get the data we were going to try and pivot the results so they are displayed like the following

L.loccpcode,L.loccode,Month,Year-2,Year-1,Current Year

 

[fmrock]

There would be a count under each year that corrisponds with the number of accounts for each month.

 

[SantaMufasa]

A picture is worth a thousand words.

Could you please post (inside a Tek-Tips CODE block, which uses monospacing) a sample of what you want the output to look like? (You can liberally use ellipses ["..."] in your sample listing to keep your sample to a manageable size.)


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

Hope this comes out looking correct. Here is example data for one location... you can see they didnt start until feb of this year.

L.loccpcode	L.loccode	themonth	Year-1	Year-1	Current_Year
111111	1	1	0	0	0
111111	1	2	0	0	99
111111	1	3	0	0	503
111111	1	4	0	0	547
111111	1	5	0	0	639
111111	1	6	0	0	677
111111	1	7	0	0	692
111111	1	8	0	0	650
111111	1	9	0	0	0
111111	1	10	0	0	0
111111	1	11	0	0	0
111111	1	12	0	0	0

 

[SantaMufasa]

Thanks, fm, for the output sample.

There would be a count under each year that corrisponds with the number of accounts for each month.

Did that not end up in the sample, or am I missing something?


I must spend a little bit of time presently for "paying customers", but as soon as I've done that, I'll put together some code for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

The example of results is what we are wanting for the final result, and i was taking baby steps to get to that spot.

All the questions before had to do with jsut the current year i guess... sorry if this is confusing, but you have been a great help.

L.loccpcode    L.loccode    themonth    2005    2006    2007
111111    1    1    0    0    0
111111    1    2    0    0    99
111111    1    3    0    0    503
111111    1    4    0    0    547
111111    1    5    0    0    639
111111    1    6    0    0    677
111111    1    7    0    0    692
111111    1    8    0    0    650
111111    1    9    0    0    0
111111    1    10    0    0    0
111111    1    11    0    0    0
111111    1    12    0    0    0

 

[SantaMufasa]

FMRock,

Code that, I believe, does what you want follows. Since I don't have actual data against which to test, I'm hoping that the syntax is correct. (If you receive any syntax errors, let me know and I'll be happy to help fix the problem.)

Since the code is pretty complex, I've liberally seasoned the code with remarks/documentation, which I hope helps you to internalise the technique.

To hyper-simplify the code for your business users (and, in fact, to hide all the complexity from your users), you can place a "CREATE VIEW <some name> AS <my code>" in front of the query, below. (BTW, the output self-adjusts to give the three most recent years of output regardless of the current date. Therefore, when the new year rolls around, the code automatically changes from "2005, 2006, 2007" to "2006, 2007, 2008" output results.

Here is the script:

select yr_0.loccpcode,yr_0.loccode, yr_0.mnth, cnt_2, cnt_1, cnt_0
-- ======================================================================================
-- Above code formats output rows from 3 years of summary rows from in-line views, below.
-- ======================================================================================
  from
-- ======================================================================================
-- Section 1: "Current-year" in-line view follows:
-- ======================================================================================
       (select loc.loccpcode, loc.loccode, loc.mnth, count(account) cnt_0
-- ======================================================================================
-- Sub-Section 1a: following in-line view produces 12 months of rows for each
--                 "loccpcode" and "loccode" combination:
-- ======================================================================================
          from (select loccpcode, loccode, mnth
-- ======================================================================================
-- Sub-Section 1a1: Following in-line view produces 12-row driver for months
--                  (Note: Code uses "all_tab_columns" simply as a generic object
--                         that we know has at least 12 rows from which to create
--                         a 12-row driver object to produce 12 months.)
-- ======================================================================================
                  from (select rownum mnth from all_tab_columns
                         where rownum <= 12) mnths
-- ======================================================================================
--                Cartesian join of each "medlocation", below, with the 12-row in-line 
--                     view, above, creates 12 months of rows for each "medlocation".
-- ======================================================================================
                       ,medlocations l) loc
-- ======================================================================================
-- Sub-Section 1b: In-line view, below, harvests from medcharges, just the rows that
--                 fit the business criteria: Last three years of medcharges that
--                 are Type 'C' and splitflag is null 
-- ======================================================================================
              ,(select *
-- ======================================================================================
-- Sub-Section 1b1: Following in-line view results in all columns from "medcharges"
--                  plus it simplifies the "ServiceDate" into an Oracle DATE,
--                  "trans_dt", and simplifies the Month, "mnth" for proper placement
--                  in the final output.
-- ======================================================================================
                  from (select x.*
                              ,to_date(servicedate,'j') trans_dt
                              ,to_char(to_date(servicedate,'j'),'mm') mnth
                          from medcharges x) c
                 where type = 'C' and splitflag is null
                   and to_char(trans_dt,'yyyy')
                     = to_char(sysdate,'yyyy')) txn -- means "this year's transactions"
-- ======================================================================================
-- Following WHERE outer joins all Location+Month rows with "Medcharges"
-- ======================================================================================
         where loc.loccpcode = txn.cpcode(+)
           and loc.loccode   = txn.docloc(+)
           and loc.mnth      = txn.mnth(+)
         group by loc.loccpcode, loc.loccode, loc.mnth) yr_0
-- ======================================================================================
-- Section 2: Following code matches logic for "Current-year", but for "One Year Ago"
-- ======================================================================================
      ,(select loc.loccpcode, loc.loccode, loc.mnth, count(account) cnt_1
          from (select loccpcode, loccode, mnth
                  from (select rownum mnth from all_tab_columns
                         where rownum <= 12) mnths
                       ,medlocations l) loc
              ,(select *
                  from (select x.*
                              ,to_date(servicedate,'j') trans_dt
                              ,to_char(to_date(servicedate,'j'),'mm') mnth
                          from medcharges x) c
                 where type = 'C' and splitflag is null
                   and to_char(trans_dt,'yyyy')
                     = to_char(sysdate,'yyyy')-1) txn -- "-1" means "one year ago"
         where loc.loccpcode = txn.cpcode(+)
           and loc.loccode   = txn.docloc(+)
           and loc.mnth      = txn.mnth(+)
         group by loc.loccpcode, loc.loccode, loc.mnth) yr_1
-- ======================================================================================
-- Section 3: Following code matches logic for "Current-year", but for "Two Years Ago"
-- ======================================================================================
      ,(select loc.loccpcode, loc.loccode, loc.mnth, count(account) cnt_2
          from (select loccpcode, loccode, mnth
                  from (select rownum mnth from all_tab_columns
                         where rownum <= 12) mnths
                       ,medlocations l) loc
              ,(select *
                  from (select x.*
                              ,to_date(servicedate,'j') trans_dt
                              ,to_char(to_date(servicedate,'j'),'mm') mnth
                          from medcharges x) c
                 where type = 'C' and splitflag is null
                   and to_char(trans_dt,'yyyy')
                     = to_char(sysdate,'yyyy')-2) txn -- "-2" means "two years ago"
         where loc.loccpcode = txn.cpcode(+)
           and loc.loccode   = txn.docloc(+)
           and loc.mnth      = txn.mnth(+)
         group by loc.loccpcode, loc.loccode, loc.mnth) yr_2
-- ======================================================================================
-- following WHERE joins data from three years onto one row of output,
-- ======================================================================================
 where (yr_0.loccpcode = yr_1.loccpcode and
        yr_0.loccode   = yr_1.loccode   and
        yr_0.mnth      = yr_1.mnth)
   and (yr_0.loccpcode = yr_2.loccpcode and
        yr_0.loccode   = yr_2.loccode   and
        yr_0.mnth      = yr_2.mnth)
/
*****************************************************************************************

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

Mufasa,

I would just have to say that you are the man!

It worked great, the only thing we had to change is to do a Count(Distinct Account)... Each account can have multiple charges. We ran the report and the numbers are right on with the old reports the user manually created.

Thanks for all your help, the logic from this query has already given me ideas on how to do other requests that have come in.