Group By or CTE?

Transact-SQL
1

I'm very confused about this one. can someone help? My issue:

I want to turn this query and table data

Query:

select A.DispatchNote, B.MStockCode, B.NComment
from MdnMaster A 
  left join MdnDetail B on A.DispatchNote = B.DispatchNote
A.DispatchNote B.MStockCode B.NComment
258579/0001 RFK2-8520-150-25
258579/0001 FREIGHT
258579/0001 1 Parcel UPS Ground 2/28/2020
258579/0001 Trk#: 1Z8R9V900342021397 -- 0.9 lb
258585/0001 CAW2-1832-25L
258585/0001 FREIGHT
258585/0001 6 Parcels UPS Ground 2/28/2020
258585/0001 Trk#: 1Z8R9V900342126962 -- 15 lb
258585/0001 Trk#: 1Z8R9V900342515176 -- 15 lb
258585/0001 Trk#: 1Z8R9V900340703781 -- 15 lb
258585/0001 Trk#: 1Z8R9V900340988792 -- 15 lb
258585/0001 Trk#: 1Z8R9V900340706204 -- 15 lb
258585/0001 Trk#: 1Z8R9V900342832014 -- 15 lb

into something like this query and table

Query:

select 
   A.DispatchNote, 
   MAX(B.MStockCode) as StockCode, 
   MAX(case when B.NComment like 'Trk%' then B.NComment end) as NComment,
   MAX(A.CustomerPoNumber) as CustomerPO
from MdnMaster A 
left join MdnDetail B on A.DispatchNote = B.DispatchNote 
      and (B.NComment LIKE 'Trk%' OR B.MStockCode is not null)
	  and A.Customer = 'LAWSON'
	  and B.MLineShipDate = 
		case 
			when datepart(weekday, getdate() -1) = '7' 
				then DATEADD(hh,0,dateadd(DAY, datediff(day, 0, getdate()),-2)) -- if yesterday was Saturday, set to Friday
			when datepart(weekday, getdate() -1) = '1' 
				then DATEADD(hh,0,dateadd(DAY, datediff(day, 0, getdate()),-3)) -- if yesterday was Sunday, set to Friday
			else DATEADD(hh,0,dateadd(DAY, datediff(day, 0, getdate()),-1))
			end
group by A.DispatchNote

The above will also select LAWSON only customers and will look for ship dates that are YESTERDAY.

A.DispatchNote B.MStockCode B.NComment
258579/0001 RFK2-8520-150-25 Trk#: 1Z8R9V900342021397 -- 0.9 lb
258585/0001 CAW2-1832-25L Trk#: 1Z8R9V900342126962 -- 15 lb

But I'm not sure how. When I run my query I still get rows that have random info in the NComment Column (like Blank NComments or things other than Trk#) and a whole lot of NULL in StockCode and NComment.. I think I'm closer, but I'm still missing something and haven't been able to find the query to match the above table.

Basically I want to clear the empty rows in MStockCode (or essentially condense them) and either pull ONE or ALL tracking numbers attached to a dispatchnote. (Whatever is easier)

Sample data from above query:

DispatchNote StockCode NComment CustomerPO
165532/0006 NULL NULL 340
165532/0007 NULL NULL 340
165532/0008 NULL NULL 340
169247/0003 NULL NULL 50992-A
170298/0010 NULL NULL 51054

Am i able to correct my current query or should I take another approach like a CTE? I really just need to condense the information and grab either ONE tracking number or ALL of them for a specfic dispatchnote. It does not matter if I do one or the other, as long as I can clear out the blank rows and only have tracking info on the NComment column.

2

I still get rows that have random info in the NComment Column

Yes, because you have an OR in the where: if B.MStockCode is not null, then a row can still be select'ed no matter what's in B.Ncomment.

Btw, I think the existing code is much clearer and more resilient for accuracy if you write it this way:


select 
   A.DispatchNote, 
   MAX(B.MStockCode) as StockCode, 
   MAX(case when B.NComment like 'Trk%' then B.NComment end) as NComment,
   MAX(A.CustomerPoNumber) as CustomerPO
from MdnMaster A 
left join MdnDetail B on A.DispatchNote = B.DispatchNote 
      and (B.NComment LIKE 'Trk%' OR B.MStockCode is not null)
	  and A.Customer = 'LAWSON'
	  and B.MLineShipDate = DATEADD(hour,0,dateadd(DAY, datediff(day, 0, getdate()),
          case
            when datediff(day, 0, getdate()) % 7 = 6 /*if today is Sunday, set to Friday*/ then -2 
            when datediff(day, 0, getdate()) % 7 = 0 /*if today is Monday, set to Friday*/ then -3
            else -1 
          end))
group by A.DispatchNote
3

I made some changes that I think have me closer, but take a look at the issue I'm still having. I need it to still grab all the stockcodes for the specific dispatchnote which it only is grabbing one (for example)

SELECT 
   A.DispatchNote, 
   MAX(B.MStockCode) AS StockCode, 
   MAX(case when B.NComment like 'Trk%' then B.NComment end) AS NComment,
   MAX(A.CustomerPoNumber) AS CustomerPO
FROM MdnMaster A LEFT JOIN MdnDetail B 
ON A.DispatchNote = B.DispatchNote 
WHERE A.Customer = 'LAWSON' AND
      B.MLineShipDate = 
        CASE 
           WHEN DATENAME(WEEKDAY, DATEADD(DAY, -1, GETDATE())) = 'Saturday' THEN CAST(DATEADD(DAY, -3, GETDATE()) AS DATE)
           WHEN DATENAME(WEEKDAY, DATEADD(DAY, -1, GETDATE())) = 'Friday' CAST(DATEADD(DAY, -2, GETDATE()) AS DATE)
           ELSE CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
        END
GROUP BY A.DispatchNote



Sample data from above query:

| 294027/0001 | CAT2-3118-25L | NULL | 4501063468 |
|-------------|---------------|------|------------|


But when I run it OPEN:


| DispatchNote | MStockCode        | NComment                          | CustomerPoNumber | MLineShipDate           |
|--------------|-------------------|-----------------------------------|------------------|-------------------------|
| 294027/0001  | CAK2-3716-150-25L | NULL                              | 4501063468       | 2023-01-09 00:00:00.000 |
| 294027/0001  | CAK2-3118-150-25L | NULL                              | 4501063468       | 2023-01-09 00:00:00.000 |
| 294027/0001  | CAT2-3118-25L     | NULL                              | 4501063468       | 2023-01-09 00:00:00.000 |
| 294027/0001  |                   | NULL                              | 4501063468       | NULL                    |
| 294027/0001  |                   | NULL                              | 4501063468       | NULL                    |
| 294027/0001  |                   | Trk#: 1Z8R9V900342610526 -- 6 lb  | 4501063468       | NULL                    |
| 294027/0001  |                   | Trk#: 1Z8R9V900340733139 -- 15 lb | 4501063468       | NULL                    |
| 294027/0001  |                   | Trk#: 1Z8R9V900340112149 -- 35 lb | 4501063468       | NULL                    |
| 294027/0001  |                   | NULL                              | 4501063468       | NULL                    |
| 294027/0001  |                   | NULL                              | 4501063468       | NULL                    |
| 294027/0001  |                   | NULL                              | 4501063468       | NULL                    |
| 294027/0001  |                   | NULL                              | 4501063468       | NULL                    |
| 294027/0001  |                   | NULL                              | 4501063468       | NULL                    |
| 294027/0001  |                   | NULL                              | 4501063468       | NULL                    |
| 294027/0001  |                   | NULL                              | 4501063468       | NULL                    |


So it appears it's not grabbing all the stockcodes and for some reason isn't showing the tracking number on some of these. 294027 is an example.
4

Hi

do have any idea how to debug ?

you have to examine the data

for each join
for each where clause

there could be multiple reasons why its happening

Build the query one step at a time and examine the results

When you do not get the expected results after adding the join or where clause
You have to look closer at that step

Please post data .. definitely i can help

Provide Sample Data DDL
Result

5

image
image1381×299 9.89 KB

6

another thing is please check Your

THINKING

Errors

if ANY