Left outer join to find nearest date?

mercon · June 5, 2018, 3:16pm

I am not certain I am doing this correctly. I have many records being grouped together, and on another table there exist a due date for each one which vary, I just want to include the nearest due date.

This is my original query to display the grouped records:

SELECT Burning.PBurning, Burning.PlateTag, Plate.Crop, Plate.MachTime, SUM(CONVERT(INT, Burning.PQty)) AS SumPqty, SUM(CONVERT(INT, Burning.PQtyBrn)) AS SumPQtyBrn
FROM Burning, Plate
WHERE Burning.PMachine='Accukut' AND Burning.PlateTag = Plate.Tag GROUP BY PlateTag, PBurning, Crop, MachTime

Trying to get the nearest due date on another table by adding a left outer join:

SELECT Burning.PBurning, Burning.PlateTag, Plate.Crop, Plate.MachTime, SUM(CONVERT(INT, Burning.PQty)) AS SumPqty, SUM(CONVERT(INT, Burning.PQtyBrn)) AS SumPQtyBrn
FROM Burning, Plate 
LEFT OUTER JOIN (SELECT Drawings.Job, Drawings.Series, Drawings.DwgDue FROM Drawings WHERE CONVERT(Date, SUBSTRING(Drawings.DwgDue,1,10), 103) <= CONVERT(Date, GETDATE(), 103) ) d ON Burning.PJob=Drawings.Job AND Burning.PSeries=Drawings.Series
WHERE Burning.PMachine='Accukut' AND Burning.PlateTag = Plate.Tag AND Burning.PJob = Drawings.Job AND Burning.PSeries = Drawings.Series GROUP BY PlateTag, PBurning, Crop, MachTime

I am getting continuous errors about columns not being bound, etc. I have a feeling I am not on the right track at all. Any advice appreciated!

bitsmed · June 5, 2018, 5:55pm

Without tables and sample data to work with, this is my guess:

select b.pburning
      ,b.platetag
      ,p.crop
      ,p.machtime
      ,sum(convert(int,b.pqty)) as sumpqty
      ,sum(convert(int,b.pqtybrn)) as sumpqtybrn
      ,d.dwgdue
  from burning as b
       inner join plate as p
               on p.tag=a.platetag
       left outer join (select top(1) with ties
                               job
                              ,series
                              ,dwgdue
                          from drawings
                         where convert(date,substring(dwgdue,1,10),103)<=cast(getdate() as date)
                         order by row_number() over(partition by job
                                                                ,series
                                                        order by convert(date,substring(dwgdue,1,10),103) desc
                                                   )
                       ) as d
                    on d.job=b.pjob
                   and d.series=b.pseries
 where b.pmachine='Accukut'
 group by b.platetag
         ,b.pburning
         ,p.crop
         ,p.machtime
         ,d.dwgdue
;

ScottPletcher · June 5, 2018, 6:08pm

Never use a function on a table column in a WHERE or ON clause if it can be avoided.
Also, in general you should never convert dates/datetimes to chars for comparison. In particular you should never use format '103', since it's dd mm yyyy, which will never compare correctly.

SELECT Burning.PBurning, Burning.PlateTag, Plate.Crop, Plate.MachTime, 
    SUM(CONVERT(INT, Burning.PQty)) AS SumPqty, SUM(CONVERT(INT, Burning.PQtyBrn)) AS SumPQtyBrn,
    MAX(d.DwgDue) AS MostRecentDwgDue
FROM Burning
INNER JOIN Plate ON Burning.PlateTag = Plate.Tag
OUTER APPLY (
    SELECT TOP (1) Drawings.Job, Drawings.Series, Drawings.DwgDue 
    FROM Drawings 
    WHERE 
        Drawings.Job=Burning.PJob AND 
        Drawings.Series=Burning.PSeries AND
        Drawings.DwgDue < DATEADD(DAY, 1, CAST(GETDATE() AS date))
    ORDER BY Drawings.DwgDue DESC
) AS d
WHERE Burning.PMachine='Accukut'
GROUP BY PlateTag, PBurning, Crop, MachTime

mercon · June 5, 2018, 9:02pm

For both of the examples above I am getting "Expected 'Case'" error.

Maybe I will post some example data (relevant columns only):

Burning:
PJob PSeries PlateTag
15468 500 12469
15968 600 12490
19584 100 12469

Plate:
PTag
12469

Drawings:
Job Series DwgDue
15468 500 05/06/2018
19584 500 01/03/2018
15968 600 15/09/2018

From the query, the grouping for Plate PTag 12469 should display the nearest date of 05/06/2018

ScottPletcher · June 5, 2018, 9:05pm

It needs to be (directly) usable sample data, i.e. CREATE TABLE and INSERT statements, so queries can be run against it, not just a splat of data.

mercon · June 5, 2018, 9:24pm

My apologies. I have the create table below. I am unsure how to export the sql for inserting data.

CREATE TABLE "Burning" (
	"PartID" INT NOT NULL,
	"PMachine" CHAR(10) NULL DEFAULT NULL,
	"PJob" INT NULL DEFAULT NULL,
	"PSeries" CHAR(10) NULL DEFAULT NULL,
	"PlateTag" CHAR(5) NULL DEFAULT NULL,
	"Ppart" CHAR(25) NULL DEFAULT NULL,
	"PQty" INT NULL DEFAULT NULL,
	"PQtyBrn" INT NULL DEFAULT NULL,
	"PDateCom" CHAR(18) NULL DEFAULT NULL,
	"PBurning" CHAR(3) NULL DEFAULT NULL,
	PRIMARY KEY ("PartID")
)
;

CREATE TABLE "Drawings" (
	"ID" INT NOT NULL,
	"Job" CHAR(10) NULL DEFAULT NULL,
	"Series" CHAR(10) NULL DEFAULT NULL,
	"DwgDue" CHAR(10) NULL DEFAULT NULL,
	PRIMARY KEY ("ID")
)
;

CREATE TABLE "Plate" (
	"ID" INT NOT NULL,
	"Tag" CHAR(10) NULL DEFAULT NULL,
	PRIMARY KEY ("ID")
)
;

yosiasz · June 6, 2018, 12:44am

please also include dml

insert into Plate etc

bitsmed · June 6, 2018, 5:46am

In the query I wrote earlier, I had a mistake. Replace

inner join plate as p
               on p.tag=a.platetag

with

inner join plate as p
               on p.tag=b.platetag

From the table definitions, I don't see crop and machtime fields (which should be in plate table.

Also, what @ScottPletcher wrote about not using functions on where/on clause, is really a performance killer. I see you dwgdue field is char(10) which is not optimal format, but from your first post, I figured it was just that. If at all possible, this should be changes to date or datetime field.

mercon · June 6, 2018, 3:09pm

This did function but I am getting some off behaviour that I can't understand. Sometimes the date is displayed, sometimes it is not and I can't identify why. I an re-save a new date in the application and it will display sometimes, but other times it will not.

Also I am not sure I understand what you guys mean about using functions in the where clause, what does this mean? I know the date column is not ideal but it caused many problems with integration on a third party program, it's been working well this way.

mercon · June 6, 2018, 4:01pm

I've isolated the issue. I am wanting to display the minimum date but this date can be past today's date. It's not necessarily only past due dates. I've removed this line:

where convert(date,substring(dwgdue,1,10),103)<=cast(getdate() as date)

...and it appears to show all the dates, however if the are multiple due dates it's not grouping them and selecting the nearest one, it's simply showing multiple records where ever it finds varying due dates.

It's so close!

bitsmed · June 6, 2018, 8:02pm

Storing a date in char format makes the db server work that much harder.
Ex: you have 2 rows

dt
11/01/2018
09/02/2018

You want to get dates heigher than january 20th.

If you filter directly on the field, without using functions to convert the char field to a date field, you'd write:

where dt>'20/01/2018'

but you won't get any rows, because the char field is filteres char by char. So first char of the filter date is '2'. None of the 2 date i the table is higher or equal to '2', so no rows.
You first have to convert the char field into something usefull (a date).
Now imagine you have 1 mill. rows, the db server has to convert all rows fore it can filter, so you see, performance it not optimal (even if you slap an index on).
If the field was a date field with an index, you'd get your rows way way faster.

bitsmed · June 6, 2018, 8:02pm

Before we try and come up with a solution, I need to understand your requirements regarding how to choose the due date.

If today is 06/06/2018 and we have the following due dates:
01/01/2018
01/06/2018
10/06/2018
which one to pick?

If today is 06/06/2018 and we have the following due dates:
01/01/2018
03/06/2018
10/06/2018
which one to pick?

If today is 06/06/2018 and we have the following due dates:
10/06/2018
11/06/2018
12/06/2018
which one to pick?

ScottPletcher · June 6, 2018, 9:32pm

Wow, yeah, storing a date as dd/mm/yyyy makes it useless for comparisons without converting it to a date -- but then leave it there for the actual compare, like so:

CONVERT(Date, Drawings.DwgDue, 103) < DATEADD(DAY, 1, CAST(GETDATE() AS date))

mercon · June 6, 2018, 9:46pm

Sorry if this was unclear. Essentially what we are trying to do is identify which job is next in line, and we have overdue work which needs to be addressed first. All our dates are in dd/mm/yyyy to match third party software.

If today is 06/06/2018 and we have the following due dates:
01/01/2018 - THIS ONE
01/06/2018
10/06/2018
which one to pick?

If today is 06/06/2018 and we have the following due dates:
01/01/2018 - THIS ONE
03/06/2018
10/06/2018
which one to pick?

If today is 06/06/2018 and we have the following due dates:
10/06/2018 - THIS ONE
11/06/2018
12/06/2018
which one to pick?

mercon · June 6, 2018, 9:47pm

Yup, that's why we convert it where needed. Though unconventional, this works well for us doing it this way.

ScottPletcher · June 6, 2018, 10:01pm

It really doesn't. If you insist on storing dates as chars, you should YYYYMMDD, which is 100% clear, sorts correctly and compares correctly.

bitsmed · June 6, 2018, 10:27pm

Remove the where statement (like you wrote earlier) and replace this:

order by convert(date,substring(dwgdue,1,10),103) desc

with this:

order by convert(date,substring(dwgdue,1,10),103)

mercon · June 6, 2018, 11:22pm

Hmm, I'm still getting unexplained results. Sometimes the date doesn't appear at all though I've confirmed there is a value present. Working with two rows, one date 30/05/2018 and the other 11/06/2018 I get no value shown at all, and the 11/06/2018 shown if I use DESC, and none shown if I use ASC.

I am quite confused... maybe this function will not work as I had hoped.