I would like results from the first query to override results from the second query:

SELECT 
    CONVERT(DATE,a.ins_timestamp) AS 'Date',  
    a.Prod_code, 
    a.Curr_boxes, 
    a.Label_barcode, 
    b.From_ord_no, 
    NULL AS To_ord_no, 
    CASE
        WHEN a.From_batch >= a.To_batch THEN a.From_batch 
        WHEN a.To_batch >= a.From_batch THEN a.To_batch 
        ELSE a.From_batch
    END AS 'Batch',  
    a.Weight, 
    'IN' AS 'Direction' 
FROM 
    a 
JOIN 
    b ON a.Label_barcode = b.Label_barcode 
WHERE 
    (a.ins_timestamp BETWEEN ? AND ?) 
    AND (a.To_batch = ?) 
    AND (a.From_batch = 0) 
    AND (a.Type = 'Consumption') 
    AND (a.To_status <> 'STOCK') 
    AND (b.From_status = 'PORDER') 
GROUP BY 
    CONVERT(DATE,a.ins_timestamp),  
    a.Prod_code, 
    a.Curr_boxes, 
    a.Label_barcode, 
    b.From_ord_no, 
    a.From_batch, 
    a.To_batch, 
    a.Weight, 
    a.From_status,
    a.To_status

UNION 

SELECT 
    CONVERT(DATE,b.ins_timestamp) AS 'Date',  
    b.Prod_code, 
    b.Curr_boxes, 
    b.Label_barcode, 
    NULL AS From_ord_no, 
    NULL AS To_ord_no, 
    CASE
        WHEN b.From_batch >= b.To_batch THEN b.From_batch 
        WHEN b.To_batch >= b.From_batch THEN b.To_batch 
        ELSE                             b.From_batch
    END AS 'Batch',  
    b.Weight, 
    'IN' AS 'Direction' 
FROM 
    b 
WHERE 
    (b.From_batch = 0) 
    AND (b.Type = 'Consumption') 
    AND (b.ins_timestamp BETWEEN ? AND ?) 
    AND (b.To_batch = ?) 
    AND (b.To_status<>'STOCK') 
GROUP BY 
    CONVERT(DATE,b.ins_timestamp),  
    b.Prod_code, 
    b.Curr_boxes, 
    b.Label_barcode,  
    b.From_batch, 
    b.To_batch, 
    b.Weight, 
    b.From_status,
    b.To_status

Please note: The question marks are parameters that will be passed in Excel.

Current Output:

The results from the second query will return nulls for the 'From_order_no' column, but everything else in the select statement will be the exact same. In this case the union will fail to remove duplicate rows and so there will be two rows of data for the same label_barcode.

Desired Output:

So the question is - how do I remove the result from the second query, that shows nulls for From_order_no and keep the results from the first query?

CodePudding user response：

Provided From_ord_no is the only difference to be ignored you can group the result of your query

select 'Date',  
    Prod_code, 
    Curr_boxes, 
    Label_barcode, 
    max(From_ord_no) From_ord_no, 
    To_ord_no, 
    'Batch',  
    Weight, 
    'Direction' 
from (
  <your query>
) t
group by 'Date',  
    Prod_code, 
    Curr_boxes, 
    Label_barcode, 
    To_ord_no, 
    'Batch',  
    Weight, 
    'Direction'

CodePudding user response：

It appears the answer is to create a temporary table. In the below example this is called #newtable. The hashtag is important as this is actually what makes it a 'temporary' table (not everyone explains this).

The below might prove useful to others as it includes WHERE conditions which most examples do not have online:

-- First create your temp table 
SELECT CONVERT(DATE,a.ins_timestamp) AS 'Date',  
a.Prod_code, 
a.Curr_boxes, 
a.Label_barcode, 
b.From_ord_no, 
NULL AS To_ord_no, 
CASE
    WHEN a.From_batch >= a.To_batch THEN a.From_batch 
    WHEN a.To_batch >= a.From_batch THEN a.To_batch 
    ELSE                             a.From_batch
END AS 'Batch',  
a.Weight, 
'IN' AS 'Direction' 

INTO #newtable 

FROM a 

JOIN b ON a.Label_barcode = b.Label_barcode 

WHERE (a.ins_timestamp Between ? And ?) AND (a.To_batch = ?) AND (a.From_batch = 0) AND (a.Type='Consumption') AND (a.To_status<>'STOCK') AND (b.From_status = 'PORDER') 

-- Now we insert the second query into the already created table
    INSERT INTO #newtable 

SELECT CONVERT(DATE,b.ins_timestamp) AS 'Date',  
b.Prod_code, 
b.Curr_boxes, 
b.Label_barcode, 
NULL AS From_ord_no, 
NULL AS To_ord_no, 
CASE
    WHEN b.From_batch >= b.To_batch THEN b.From_batch 
    WHEN b.To_batch >= b.From_batch THEN b.To_batch 
    ELSE                             b.From_batch
END AS 'Batch',  
b.Weight, 
'IN' AS 'Direction' 

FROM b 

WHERE (b.From_batch = 0) AND (b.Type='Consumption') AND (b.ins_timestamp Between ? And ?) AND (b.To_batch = ?) AND (b.To_status<>'STOCK') 

-- Now we can select whatever we want from our temp table
SELECT Date,  
    Prod_code, 
    Curr_boxes, 
    Label_barcode, 
    max(From_ord_no) From_ord_no, 
    To_ord_no, 
    Batch,  
    Weight, 
    Direction 

FROM #newtable 

GROUP BY Date,  
    Prod_code, 
    Curr_boxes, 
    Label_barcode, 
    To_ord_no, 
    Batch,  
    Weight, 
    Direction