SQL Server sub query

Question

I want to combine 2 SQL queries, the first query i want to pull back employee records for the latest month, and the second query, pull back records for the last 3 months.

There is a method to the madness, employees can change manager each month, but the latest months manager needs to see the previous 3 month records for their employee, even if that employee had a different manager for those months.

This is the 1st select to pull in latest month

/******latest month*****************/
SELECT [REPORT_DT]
      ,[EMPLOYEE_ID]
      ,[EMPLOYEE_NAME]
      ,[LOCATION]
      ,[JOB_DESCRIPTION]
      ,[MANAGER_ID]
  FROM [EMPLOYEE]
  where [REPORT_DT]=
  (select max([REPORT_DT]) from [EMPLOYEE])

this is the select to pull in last 3months

/*********last 3 months*******************/
SELECT [REPORT_DT]
      ,[EMPLOYEE_ID]
      ,[EMPLOYEE_NAME]
      ,[LOCATION]
      ,[JOB_DESCRIPTION]
      ,[MANAGER_ID]
  FROM [EMPLOYEE]
  where [REPORT_DT]>=
  ( DATEADD(M, -3, GETDATE()))

i would be joining the 2 selects on [EMPLOYEE_ID]. Any ideas how i can combined these 2 querys? Thanks!

Presumably, the latest month is one of the last three months, so the second query encompasses the first query. — Gordon Linoff
– Gordon Linoff, Commented Sep 25, 2020 at 10:41

Gordon Linoff · Accepted Answer · 2020-09-25 10:45:13Z

1

If you want to join the two tables, you would use:

select ecurr.*, e.*  -- or whatever columns you like
from employee ecurr join
     employee e
     on ecurr.employee_id = e.employee_id
where ecurr.report_dt = (select max(e2.report_dt) from employee e2) and
      e.report_dt >= dateadd(month, 3, getdate())
order by ecurr.employee_id, e.report_dt;

I will add that this result doesn't make particular sense to me. But this is the question that you specifically asked.

answered Sep 25, 2020 at 10:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Blowers Over a year ago

This is exactly what i needed Gordon! Thank you, much appreciated!

Blowers Over a year ago

I have a follow up question on this. If I want to look at a previous month, and pull back the last 3 months from it, is there a way to alter the query to do this? So at the moment the latest report_dt is september, but if i run this query when looking at the august report_dt no data returns as september is the latest month.

Tim Biegeleisen · Accepted Answer · 2020-09-25 09:36:15Z

1

I would use:

SELECT TOP 1 WITH TIES
    [REPORT_DT],
    [EMPLOYEE_ID],
    [EMPLOYEE_NAME],
    [LOCATION],
    [JOB_DESCRIPTION],
    [MANAGER_ID],
FROM [EMPLOYEE]
WHERE
    [REPORT_DT] >= DATEADD(M, -3, GETDATE()
ORDER BY
    [REPORT_DT] DESC;

The above WHERE clause matches your second query, and we use a TOP 1 WITH TIES approach to obtain the records having the maximum REPORT_DT values in the table.

answered Sep 25, 2020 at 9:36

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Michał Turczyn · Accepted Answer · 2020-09-25 10:54:46Z

1

You can use also EXISTS:

SELECT [REPORT_DT]
      ,[EMPLOYEE_ID]
      ,[EMPLOYEE_NAME]
      ,[LOCATION]
      ,[JOB_DESCRIPTION]
      ,[MANAGER_ID]
FROM [EMPLOYEE]
WHERE [REPORT_DT] = (SELECT MAX([REPORT_DT]) FROM [EMPLOYEE]) AND
      EXISTS( SELECT 1 FROM [EMPLOYEE]
              WHERE [EMPLOYEE_ID] == t.[EMPLOYEE_ID] AND
                    [REPORT_DT] >= (DATEADD(M, -3, GETDATE())))

Regarding comment, this query should solve the problem:

SELECT [REPORT_DT]
      ,[EMPLOYEE_ID]
      ,[EMPLOYEE_NAME]
      ,[LOCATION]
      ,[JOB_DESCRIPTION]
      ,[MANAGER_ID]
FROM [EMPLOYEE]
WHERE [REPORT_DT] >= (DATEADD(M, -3, GETDATE())) AND
      EXISTS( SELECT 1 FROM [EMPLOYEE]
              WHERE [EMPLOYEE_ID] == t.[EMPLOYEE_ID] AND
                    [REPORT_DT] >= (DATEADD(M, -1, GETDATE())))

edited Sep 25, 2020 at 10:54

answered Sep 25, 2020 at 9:52

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

2 Comments

Blowers Over a year ago

This is returning data for the latest month, but i wanted to get a 3 month view of the data, with the latest month manager able to view any of his employees data for the last 3 month. Maybe i'm going about it the wrong way

Michał Turczyn Over a year ago

@Blowers Try another query :)

ORCAs · Accepted Answer · 2020-09-25 09:44:44Z

0

I thought you want to get [EMPLOYEE_ID] list for the latest month. And get their 3 month work history. According to your current select scripts (I did not understand your first select script if it gives a employee list or not) you can do like this. But it is not an effective solution. You can also add sub selection in the join section. First try this please.

SELECT [REPORT_DT]
      ,[EMPLOYEE_ID]
      ,[EMPLOYEE_NAME]
      ,[LOCATION]
      ,[JOB_DESCRIPTION]
      ,[MANAGER_ID]
  FROM [EMPLOYEE]
  where [REPORT_DT]>=(DATEADD(M, -3, GETDATE()))
AND [EMPLOYEE_ID] IN (SELECT [REPORT_DT]
      ,[EMPLOYEE_ID]
      ,[EMPLOYEE_NAME]
      ,[LOCATION]
      ,[JOB_DESCRIPTION]
      ,[MANAGER_ID]
  FROM [EMPLOYEE]
  where [REPORT_DT]=
  (select max([REPORT_DT]) from [EMPLOYEE]))

answered Sep 25, 2020 at 9:44

ORCAs

1611 gold badge3 silver badges13 bronze badges

2 Comments

Blowers Over a year ago

I get the following error when trying this query; Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

ORCAs Over a year ago

Then try with exists; SELECT [REPORT_DT] ,[EMPLOYEE_ID] ,[EMPLOYEE_NAME] ,[LOCATION] ,[JOB_DESCRIPTION] ,[MANAGER_ID] FROM [EMPLOYEE] WHERE [REPORT_DT] = (SELECT MAX([REPORT_DT]) FROM [EMPLOYEE]) AND EXISTS( SELECT [EMPLOYEE_ID] FROM [EMPLOYEE] WHERE [EMPLOYEE_ID] == t.[EMPLOYEE_ID] AND [REPORT_DT] >= (DATEADD(M, -3, GETDATE())))

Blowers · Accepted Answer · 2020-10-16 08:46:16Z

0

I created a View using the below query, and then combined this with a 3 month date prompt (this is being used on a Business Objects report) and that did the trick.

SELECT 
b.[MANAGER_ID],
a.[EMPLOYEE_ID],
a.[START_DT],
a.[EMPLOYEE_NAME], 
a.[LOCATION],
a.[JOB_DESCRIPTION],
a.[REPORT_DT]
FROM  [EMPLOYEE] b INNER JOIN
      [EMPLOYEE] a
     ON b.[EMPLOYEE_ID] = a.[EMPLOYEE_ID]
WHERE b.[REPORT_DT] = (SELECT MAX(c.[REPORT_DT]) 
FROM [EMPLOYEE] c)

answered Oct 16, 2020 at 8:46

Blowers

771 silver badge11 bronze badges

Collectives™ on Stack Overflow

SQL Server sub query

5 Answers 5

2 Comments

Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related