I am looking for the best method the extract "Supposed to make" - "Did
Make", where "Supposed to make" and "Did Make" come from different queries.
       I need to show when parts are scheduled and not made, and when parts
are made and not scheduled.
I have no trouble pulling the data, the way I do it is to use a left join on
one table, then another left join on the other and union them together to
eliminate dupes. It works fine but I just wonder if it's the most efficient?
Any thoughts ?
I appreciate your help.

Paul

It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 )



The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.


Show quoteHide quote

I have seperate views) that pull parts needed and parts produced along with
dates due, then use the left join method to give me parts needed and not
produced and the parts produced and not needed.
Views contain a date part number & qty.

Output I get now... it's correct but I'm wondering as this scales will it be
efficient. (I may add a 3rd column to the output)
query code below.... output

D_DATE                                                     PART
NEEDED      PRODUCED
------------------------------------------------------ -------------------------
----------- -----------
2006-08-28 00:00:00.000                                18514
0           870
2006-08-28 00:00:00.000                                19014
0           204
2006-08-28 00:00:00.000                                19041
280         0
2006-08-28 00:00:00.000                                19099
220         0
2006-08-28 00:00:00.000                                19134
220         0
2006-08-28 00:00:00.000                                19139
220         0
2006-08-28 00:00:00.000                                19140
220         0
2006-08-28 00:00:00.000                                19141
2952        0
2006-08-28 00:00:00.000                                21063
220         0
2006-08-28 00:00:00.000                                21064
220         0
2006-08-28 00:00:00.000                                21065
700         0
2006-08-28 00:00:00.000                                21066
220         0
2006-08-28 00:00:00.000                                21068
220         0
2006-08-28 00:00:00.000                                21400
1160        884
2006-08-28 00:00:00.000                                21727
220         0
2006-08-28 00:00:00.000                                21909
220         0
2006-08-28 00:00:00.000                                21943
290         0

SELECT PN.PLAN_DATE AS D_DATE, PN.PART, PN.NEEDED,
  ISNULL(PP.PRODUCED,0) AS PRODUCED
  FROM LABEL_AUDIT..V_PARTS_NEEDED PN
  LEFT JOIN
  (
    SELECT PROD_DATE, PART, PRODUCED
    FROM LABEL_AUDIT..V_PRODUCED_II
  ) PP
ON PP.PROD_DATE = PN.PLAN_DATE
AND PN.PART = PP.PART
UNION
    SELECT PROD_DATE, PP.PART,
    ISNULL(PN.NEEDED,0) AS NEEDED, PP.PRODUCED
    FROM LABEL_AUDIT..V_PRODUCED_II PP
    LEFT JOIN
    (
      SELECT PLAN_DATE, PART, NEEDED
      FROM LABEL_AUDIT..V_PARTS_NEEDED
    )PN
   ON PP.PROD_DATE = PN.PLAN_DATE
   AND PP.PART = PN.PART




Show quoteHide quote

On Sat, 2 Sep 2006 18:27:44 -0400, Paul Ilacqua wrote:

(snip)
Show quoteHide quoteHi Paul,

I can't test to confirm or deny my suspicions, since you didn't post
CREATE TABLE statements for the tables used in the query and INSERT
statements with some sample data. But I have the suspicion that the SQL
below is equivalent to your query, yet faster.

SELECT          COALESCE (PN.PLAN_DATE, PP.PROD_DATE) AS D_DATE,
                COALESCE (PN.PART, PP.PART) AS PART,
                COALESCE (PN.NEEDED, 0) AS NEEDED,
                COALESCE (PP.PRODUCED, 0) AS PRODUCED
FROM            LABEL_AUDIT..V_PARTS_NEEDED AS PN
FULL OUTER JOIN LABEL_AUDIT..V_PRODUCED_II AS PP
           ON   PP.PROD_DATE = PN.PLAN_DATE
           AND  PP.PART = PN.PART;

HUGO,
  Thanks for the reply... I believe I have my fix.... thank you very much. I
added the date criteria line and I'll convert it to an SP. Thanks again. I
have never used COALESCE  in a where clause ! I'll never stop learning.
Paul

SELECT  COALESCE (PN.PLAN_DATE, PP.PROD_DATE) AS D_DATE,
        COALESCE (PN.PART, PP.PART) AS PART,
        COALESCE (PN.NEEDED, 0) AS NEEDED,
        COALESCE (PP.PRODUCED, 0) AS PRODUCED
FROM LABEL_AUDIT..V_PARTS_NEEDED AS PN
FULL OUTER JOIN LABEL_AUDIT..V_PRODUCED_II AS PP
           ON PP.PROD_DATE = PN.PLAN_DATE
           AND PP.PART = PN.PART

WHERE COALESCE (PN.PLAN_DATE, PP.PROD_DATE) BETWEEN '20060828' and
'20060903'
ORDER BY 1







Show quoteHide quote

On Sun, 3 Sep 2006 20:24:28 -0400, Paul Ilacqua wrote:

Hi Paul,

I'm sorry - I must have missed the date requirement in your original
post.

COALESCE is basically the same as ISNULL, but with these differences:

- ISNULL is proprietary T-SQL syntax; COALESCE is more portable since
it's defined in the ANSI standard for SQL.
- COALESCE accepts more than two arguments: the first non-NULL argument
is returned.
- ISNULL uses a method for determining the result datatype that isn't
used anywhere else in SQL Server - all data is converted to the datatype
of the first argument. COALESCE uses the standard method of converting
everything to the datatype with highest precedence.

Hugo ,
  Am I correct to assume then COALESCE  is almost required when using a FULL
OUTER JOIN especially with several fields?
Paul

Show quoteHide quote

On Mon, 4 Sep 2006 19:49:38 -0400, Paul Ilacqua wrote:

Hi Paul,

I don't recall ever using FULL OUTER JOIN without also using COALESCE in
the same query. I've been tying to think up a case where it would be
warranted, but no luck yet.

So yes, when using FULL OUTER JOIN, COALESCE is definitely "almost
required" - and probably even "required".

Marking (Not just Finding) Duplicate Values ......
Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string