I want to turn this:

Query:

select A.DispatchNote, B.MStockCode, B.NComment
from MdnMaster A 
  left join MdnDetail B on A.DispatchNote = B.DispatchNote

into this:

Query:

select A.DispatchNote, B.MStockCode, B.NComment
from MdnMaster A 
  left join MdnDetail B on A.DispatchNote = B.DispatchNote
where A.Customer = 'LAWSON' -- Not visible here, but the whole query only pulls LAWSON orders
  and B.MLineShipDate >= FORMAT(DATEADD(DAY,-4,getdate()),'yyyy-MM-dd 00:00:00.000') -- This pulls only orders from yesterday's date

But I'm not sure how. When I run my query I get nothing, and I think it's because I'm supposed to do something with a group by but I'm not sure.

Basically I want to clear the empty rows in MStockCode (or condense the rows I guess) and only pull the first visible tracking number that shows up for each dispatchnote when I run the query unsorted).

CodePudding user response：

For the grouping, you can start doing this. it will get less records:

select 
   A.DispatchNote, 
   B.MStockCode, 
   B.NComment 
from MdnMaster A 
left join MdnDetail B on A.DispatchNote = B.DispatchNote 
      and (B.NComment LIKE 'Trk%' OR B.MStockCode is not null)

Combining them on 1 line:

select 
   A.DispatchNote, 
   MAX(B.MStockCode) as StockCode, 
   MAX(B.NComment) as Comment 
from MdnMaster A 
left join MdnDetail B on A.DispatchNote = B.DispatchNote 
      and (B.NComment LIKE 'Trk%' OR B.MStockCode is not null)
group by A.DispatchNote

CodePudding user response：

I created a small script in T-SQL so I could run and debug with your data. I added one sample record to verify the behavior when there is no tracking information.

DECLARE @MdnMaster TABLE (DispatchNote VARCHAR(50) NOT NULL);
DECLARE @MdnDetail TABLE (DispatchNote VARCHAR(50) NOT NULL, MStockCode VARCHAR(50) NULL, NComment VARCHAR(50) NULL);

INSERT INTo @MdnMaster(DispatchNote) VALUES ('258579/0001'),('258579/0001'),('258579/0001'),('258579/0001'),
('258585/0001'),('258585/0001'),('258585/0001'),('258585/0001'),('258585/0001'),('258585/0001'),
('258585/0001'),('258585/0001'),('258585/0001'),('999999/TEST');

INSERT INTO @MdnDetail(DispatchNote, MStockCode, NComment) VALUES 
('258579/0001','RFK2-8520-150-25',NULL)
,('258579/0001',NULL,'FREIGHT')
,('258579/0001',NULL,'1 Parcel UPS Ground 2/28/2020')
,('258579/0001',NULL,'Trk#: 1Z8R9V900342021397 -- 0.9 lb')
,('258585/0001','CAW2-1832-25L',NULL)
,('258585/0001',NULL,'FREIGHT')
,('258585/0001',NULL,'6 Parcels UPS Ground 2/28/2020')
,('258585/0001',NULL,'Trk#: 1Z8R9V900342126962 -- 15 lb')
,('258585/0001',NULL,'Trk#: 1Z8R9V900342515176 -- 15 lb')
,('258585/0001',NULL,'Trk#: 1Z8R9V900340703781 -- 15 lb')
,('258585/0001',NULL,'Trk#: 1Z8R9V900340988792 -- 15 lb')
,('258585/0001',NULL,'Trk#: 1Z8R9V900340706204 -- 15 lb')
,('258585/0001',NULL,'Trk#: 1Z8R9V900342832014 -- 15 lb')
,('999999/TEST','TEST-WITHOUT-TRACKING','No tracking');

SELECT 
   m.DispatchNote, 
   MAX(d.MStockCode) as StockCode, 
   MAX(case when d.NComment like 'Trk%' then d.NComment end) as NComment
FROM @MdnMaster AS m 
LEFT OUTER JOIN @MdnDetail AS d on m.DispatchNote = d.DispatchNote 
      AND (d.NComment LIKE 'Trk%' OR d.MStockCode is not null)
GROUP BY m.DispatchNote

This is the output:

There are other approaches, but this should work in most RDBMS. For the tables you have, it is simply:

SELECT 
   m.DispatchNote, 
   MAX(d.MStockCode) as StockCode, 
   MAX(case when d.NComment like 'Trk%' then d.NComment end) as NComment
FROM MdnMaster AS m 
LEFT OUTER JOIN MdnDetail AS d on m.DispatchNote = d.DispatchNote 
      AND (d.NComment LIKE 'Trk%' OR d.MStockCode is not null)
GROUP BY m.DispatchNote