Using SELECT part in OUTER JOIN

Question

This is my SQL-query:

SELECT 
    b.MaakArtikel, 
    b.Bewerking,
    [pp].dbo.WORKINGDAYADD('2013-06-27 00:00:00.000',0-b.Startdag_backwards) AS Startdatum, 
    i.Class_06 AS Afdeling, 
    b.Minuten*10+ISNULL(br.Tijd,0) AS Minuten, 
    1+ISNULL(br.Orders,0) AS Aantal 
FROM [pp].dbo.VW_BEWERKINGSTRUCTUUR b 
    LEFT OUTER JOIN [211].dbo.Items i 
        ON b.MaakArtikel = i.ItemCode 
    LEFT OUTER JOIN [pp].dbo.VW_BEZETTING_RAW br 
        ON [pp].dbo.WORKINGDAYADD('2013-06-27 00:00:00.000',0-b.Startdag_backwards) = br.Start 
        AND i.Class_06 = br.Afdeling 
WHERE MaakArtikel = 'HT 10.038'

The query works properly, but it's a little bit slow. That's because of my second OUTER JOIN. I have to join the view by Startdatum (that's selected at line 4). As it is not a real column name, I can't use it directly in my OUTER JOIN. That means that the function [pp].dbo.WORKINGDAYADD() has to be triggered twice (once in my selection which is not a problem, and once in my OUTER JOIN which is double work).

I could write a stored procedure and use the result of function [pp].dbo.WORKINGDAYADD() in a variable, but that is not disireable. Is there a way to use Startdatum in my OUTER JOIN expression? Or do I really have to use a stored procedure for this?

@yvytty You mean to join it with a real column? That's because there is no real column with that value, I have to calculate the right date en then join on it — Jovano, May 29 '13 at 09:41
@GarethD `Startdag_backwards` comes from `[pp].dbo.VW_BEWERKINGSTRUCTUUR`, I will edit my post and add some aliasses to make this clear — Jovano, May 29 '13 at 09:43

GarethD · Accepted Answer · 2013-05-29T10:12:07.750

You could just move the function to a subquery, as below:

SELECT  MaakArtikel, 
        Bewerking,
        b.Startdatum, 
        i.Class_06 AS Afdeling, 
        Minuten * 10 + ISNULL(br.Tijd,0) AS Minuten, 
        1 + ISNULL(br.Orders, 0) AS Aantal 
FROM    (   SELECT  *, 
                    StartDatum = [pp].dbo.WORKINGDAYADD('2013-06-27 00:00:00.000', 0 - Startdag_backwards)
            FROM    [pp].dbo.VW_BEWERKINGSTRUCTUUR b 
        ) b
        LEFT OUTER JOIN [211].dbo.Items i 
            ON b.MaakArtikel = i.ItemCode 
        LEFT OUTER JOIN [pp].dbo.VW_BEZETTING_RAW br 
            ON b.Startdatum = br.Start 
            AND i.Class_06 = br.Afdeling 
WHERE   MaakArtikel = 'HT 10.038';

NOTE

I do not condone using SELECT *, and in your working query you should replace this with just the columns you need from [pp].dbo.VW_BEWERKINGSTRUCTUUR.

score 0 · Answer 2 · answered May 29 '13 at 09:42

How about storing your results of

[pp].dbo.WORKINGDAYADD('2013-06-27 00:00:00.000',0-Startdag_backwards) AS Startdatum

Into a SQL Variable

DECLARE @myVariable DATETIME
SET @myVariable = [pp].dbo.WORKINGDAYADD('2013-06-27 00:00:00.000',0-Startdag_backwards)

Then on your outer join do this

ON @myVariable = br.Start

Or something to that effect. Bobby

score 0 · Answer 3 · answered May 29 '13 at 09:44

You can store the output of [pp].dbo.WORKINGDAYADD() in a variable then use the variable in the code. (I assume the result data type of the function is datetime..)

DECLARE @StartDatum datetime
SET @StartDatum = ([pp].dbo.WORKINGDAYADD('2013-06-27 00:00:00.000',0-Startdag_backwards))

SELECT 
    MaakArtikel, 
    Bewerking,
    @StartDatum, 
    i.Class_06 AS Afdeling, 
    Minuten*10+ISNULL(br.Tijd,0) AS Minuten, 
    1+ISNULL(br.Orders,0) AS Aantal 
FROM [pp].dbo.VW_BEWERKINGSTRUCTUUR b 
    LEFT OUTER JOIN [211].dbo.Items i 
        ON b.MaakArtikel = i.ItemCode 
    LEFT OUTER JOIN [pp].dbo.VW_BEZETTING_RAW br 
        ON br.Start = @StartDatum 
        AND i.Class_06 = br.Afdeling 
WHERE MaakArtikel = 'HT 10.038'

Using SELECT part in OUTER JOIN

3 Answers3