Geek asks and answers

Left join with condition on right table

I am trying to use LEFT JOINwith a condition in the right table and I am having big problems with this.

I have two tables:

  • projects{project_id,start_date}
  • projectForeCast{project_id,year_number,month_number,hours}

I am trying to get all the projects that opened last week, and the watches that were recorded in the last month.

SELECT      dbo.Project.PROJECT_ID, dbo.ProjectForeCast.HOURS AS F0
FROM         dbo.Project LEFT JOIN  dbo.ProjectForeCast ON dbo.Project.PROJECT_ID = dbo.ProjectForeCast.PROJECT_ID
WHERE   (dbo.ProjectForeCast.YEAR_NUMBER = DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
                      (dbo.ProjectForeCast.MONTH_NUMBER = DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
                      (DATEPART(WK,dbo.Project.START_DATE) = DATEPART(WK, DATEADD(WK, - 1, GETDATE())))AND
                      (DATEPART(YYYY,dbo.Project.START_DATE) = DATEPART(YYYY, DATEADD(WK, - 1, GETDATE())))

It works fine, but if the project has no entry in projectForeCastthe latter month_number, I don’t get the project at all. I want to get an empty cell or null in a column F0in this case. It is for this reason that I tried LEFT JOIN, but it did not work.

+3
source share
6 answers

Like my previous experience, I would write your SQL query:

SELECT p.PROJECT_ID, pfc.HOURS AS F0
FROM 
(   SELECT dbo.Project.PROJECT_ID FROM dbo.Project q 
    WHERE (DATEPART(WK,dbo.Project.START_DATE) = DATEPART(WK, DATEADD(WK, - 1, GETDATE())))AND
    (DATEPART(YYYY,dbo.Project.START_DATE) = DATEPART(YYYY, DATEADD(WK, - 1, GETDATE())))
) p
LEFT JOIN  
(   SELECT dbo.ProjectForeCast.HOURS FROM dbo.ProjectForeCast 
    WHERE (dbo.ProjectForeCast.YEAR_NUMBER = DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
    (dbo.ProjectForeCast.MONTH_NUMBER = DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE()))))
) pfc
ON p.PROJECT_ID = pfc.PROJECT_ID

, :

SELECT p.PROJECT_ID, pfc.HOURS AS F0
FROM 
(   SELECT pr.PROJECT_ID FROM dbo.Project pr 
    WHERE (DATEPART(WK,pr.START_DATE) = DATEPART(WK, DATEADD(WK, - 1, GETDATE())))AND
    (DATEPART(YYYY,pr.START_DATE) = DATEPART(YYYY, DATEADD(WK, - 1, GETDATE())))
) p
LEFT JOIN  
(   SELECT pf.HOURS FROM dbo.ProjectForeCast pf
    WHERE (pf.YEAR_NUMBER = DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
    (pf.MONTH_NUMBER = DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE()))))
) pfc
ON p.PROJECT_ID = pfc.PROJECT_ID

, .

+2

WHERE LEFT JOIN.

DECLARE @YEAR_NUMBER1 INT; SET @YEAR_NUMBER1=DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())));
DECLARE @MONTH_NUMBER1 INT; SET @MONTH_NUMBER1=DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())));
DECLARE @YEAR_NUMBER2 INT; SET @YEAR_NUMBER2=DATEPART(YYYY, DATEADD(WK, - 1, GETDATE()));
DECLARE @WEEK_NUMBER1 INT; SET @WEEK_NUMBER1=DATEPART(WK, DATEADD(WK, - 1, GETDATE()));

SELECT p.PROJECT_ID
, pfc.HOURS AS F0
FROM  project p
LEFT JOIN  projectForeCast pfc 
    ON p.PROJECT_ID = pfc.PROJECT_ID
    AND pfc.YEAR_NUMBER = @YEAR_NUMBER1
    AND pfc.MONTH_NUMBER = @MONTH_NUMBER1
    AND DATEPART(YYYY,p.[START_DATE]) = @YEAR_NUMBER2
    AND DATEPART(WK,p.[START_DATE]) = @WEEK_NUMBER1;
GO

:

  • WHERE JOIN , .
+2

NULl, where.

, . COALESCE NULL.

0

. , , , where, , YEAR_NUMBER () , , , , , -, null.

, , - YEAR_NUMBER = the_year_you_want YEAR_NUMBER null, , projectForeCast

()

0

LEFT JOIN RIGHT JOIN, , JOIN WHERE.

:
?

- > , projectForeCast, JOIN WHERE.

0

LEFT JOIN , WHERE, , , DATEPART 'WEEK' . "ISOWEEK" SQL2008.

DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime

--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())

, LEFT JOIN, where. .

- , , . , , DATEPART(WK...) .

One more note: you subtract 1 week, which will give you different results when you spend on Monday versus Tuesday. When you say "last week", then you mean literally "Today" - 7, or do you mean last week, as in Sun-Sat?

Try this query to see if it fixes your problem.

DECLARE @Today DATETIME,
        @TodayDayOfWeek INT, 
        @EndOfPrevWeek DATETIME, 
        @StartOfPrevWeek DATETIME, 
        @MonthPart1 INT,
        @MonthPart2 INT,
        @YearPart1 INT,
        @YearPart2 INT 

-- get a date range consisting of 'last week'
SET @Today = GETDATE()
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday) 
SET @TodayDayOfWeek = datepart(dw, @Today) 
--get the last day of the previous week (last Sunday) 
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, @Today) 
--get the first day of the previous week (the Monday before last) 
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), @Today) 

--last week could span months or even years (i.e. Dec/Jan)
SET @MonthPart1 = DATEPART(MM, @StartOfPrevWeek)
SET @MonthPart2 = DATEPART(MM, @EndOfPrevWeek)

SET @YearPart1 = DATEPART(YYYY, @StartOfPrevWeek)
SET @YearPart2 = DATEPART(YYYY, @EndOfPrevWeek)

SELECT      
            dbo.Project.PROJECT_ID, 
            dbo.ProjectForeCast.HOURS AS F0
FROM         
            dbo.Project 
LEFT JOIN   dbo.ProjectForeCast ON dbo.Project.PROJECT_ID = dbo.ProjectForeCast.PROJECT_ID
WHERE       dbo.ProjectForeCast.YEAR_NUMBER IN (@YearPart1, @YearPart2) 
AND         dbo.ProjectForeCast.MONTH_NUMBER IN (@MonthPart1, @MonthPart2) 
AND         dbo.Project.START_DATE BETWEEN @StartOfPrevWeek AND @EndOfPrevWeek
0
source

More articles:


All Articles