How to code a subquery with MAX() in WHERE clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to code a subquery with MAX() in WHERE clause

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-10 : 17:24:05

This code is giving me multiple LAST_SYNCHRONIZED values and I want just the MAX of each one. The results are otherwise identical except for the LAST_SYNCHRONIZED. I want to grab only the record with the MAX(LAST_SYNCHRONIZED). I think there will be a sub-query in the WHERE clause, probably with a GROUP BY. But I don't know how to code it.

DECLARE  @STARTDATE DATETIME
DECLARE  @ENDDATE DATETIME
SET @STARTDATE = Dateadd(d,-1,Convert(DATETIME,Convert(VARCHAR(11),Getdate(),109),109))
SET @ENDDATE = @STARTDATE + 1
SELECT DISTINCT    O_OPERATOR.OPERATOR_NAME,
	O_WORKSTATION.STATION_NAME,
	O_WORKSTATION.LAST_SYNCHRONIZED,
	SYS_SKED_CONFLICT.DATE_PROVIDED,
    CASE 
      WHEN SYS_SYNCH_LOG.END_DATE_TIME BETWEEN @STARTDATE AND @ENDDATE
      THEN 'YES'
      ELSE 'NO'
    END SCHEDULEDYESNO
FROM O_WORKSTATION 
INNER JOIN SYS_SYNCH_LOG ON O_WORKSTATION.STATION_ID = SYS_SYNCH_LOG.STATION_ID 
AND   O_WORKSTATION.LAST_SYNCHRONIZED = SYS_SYNCH_LOG.END_DATE_TIME 
INNER JOIN SYS_SYNCH_CONFLICT ON SYS_SYNCH_LOG.SYNCH_LOG_ID = SYS_SYNCH_CONFLICT.SYNCH_ID 
INNER JOIN O_OPERATOR ON SYS_SYNCH_LOG.OPERATOR_ID = O_OPERATOR.OPERATOR_ID 
INNER JOIN SYS_SKED_CONFLICT ON SYS_SYNCH_CONFLICT.RESOURCE_ID = SYS_SKED_CONFLICT.RESOURCE_ID
WHERE LAST_SYNCHRONIZED = (SELECT MAX(LAST_SYNCHRONIZED) FROM O_WORK_STATION) --????????????
ORDER BY STATION_NAME

The query otherwise works, but returns 10 times the results I want. Thanks for any help.

Duane

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-10 : 23:37:34

[code]
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME

SET @STARTDATE = Dateadd(d,-1,Convert(DATETIME,Convert(VARCHAR(11),Getdate(),109),109))
SET @ENDDATE = @STARTDATE + 1

SELECT *
FROM
(
SELECT DISTINCT O_OPERATOR.OPERATOR_NAME,
O_WORKSTATION.STATION_NAME,
O_WORKSTATION.LAST_SYNCHRONIZED,
SYS_SKED_CONFLICT.DATE_PROVIDED,
CASE
WHEN SYS_SYNCH_LOG.END_DATE_TIME BETWEEN @STARTDATE AND @ENDDATE
THEN 'YES'
ELSE 'NO'
END SCHEDULEDYESNO,
ROW_NO = ROW_NUMBER() OVER (ORDER BY O_WORKSTATION.STATION_NAME,
O_OPERATOR.OPERATOR_NAME,
SYS_SKED_CONFLICT.DATE_PROVIDED,
O_WORKSTATION.LAST_SYNCHRONIZED DESC
)
FROM O_WORKSTATION
INNER JOIN SYS_SYNCH_LOG ON O_WORKSTATION.STATION_ID = SYS_SYNCH_LOG.STATION_ID
AND O_WORKSTATION.LAST_SYNCHRONIZED = SYS_SYNCH_LOG.END_DATE_TIME
INNER JOIN SYS_SYNCH_CONFLICT ON SYS_SYNCH_LOG.SYNCH_LOG_ID = SYS_SYNCH_CONFLICT.SYNCH_ID
INNER JOIN O_OPERATOR ON SYS_SYNCH_LOG.OPERATOR_ID = O_OPERATOR.OPERATOR_ID
INNER JOIN SYS_SKED_CONFLICT ON SYS_SYNCH_CONFLICT.RESOURCE_ID = SYS_SKED_CONFLICT.RESOURCE_ID
~~WHERE LAST_SYNCHRONIZED = (SELECT MAX(LAST_SYNCHRONIZED) FROM O_WORK_STATION) --????????????~~
) d
WHERE ROW_NO = 1
ORDER BY STATION_NAME
[/code]

KH
[spoiler]Time is always against us[/spoiler]

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-11 : 08:59:53

Thank you very much for your response and it does get me closer. With the row_no restriction, I get one row, but I need one for each station in O_WORKSTATION. It will automatically give me the LAST_SYNCHRONIZED as it is in O_WORKSTATION. But it will only give me the K.DATE_PROVIDED through those JOINs. When I remove the line WHERE ROW_NO = 1, I get all the DATE_PROVIDED records from SYS_SKED_CONFLICT which brings back 30,000 rows, not the one for each O_WORKSTATION that should be there.
The idea is that if the Station had any appointments (Date_Provided) the day they should have synchronized, we want to know about it. If they had appointments (Date_Provided) any other day, it does not matter. Actually, there should be 1 record for each STATION_ID that did not synchronize between @StartTime and @endtime - 1 and only 1 and it should show the last day they DID synchronize - This date will be outside (before) the period. THEN, the Yes or No should be a result of a subquery (I guess) that queries ultimately the SYS_SKED_CONFLICT table to determine whether they had appointments between @StartDate and @EndDate (there could be one or several, it doesn't matter). But whether there is one or more, the result should be 'Yes'. If not, 'No'. There still should be only one record returned for each Station_ID, though with a 'Yes' or 'No' at the end. Thank you for the thoughtful response. We are getting there.

Duane

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-11 : 09:04:26

quote:
but I need one for each station in O_WORKSTATION

change to OVER (PARTITION BY O_WORKSTATION ORDER BY . . )

KH
[spoiler]Time is always against us[/spoiler]

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-11 : 09:23:21

Thanks again - I changed WHERE ROW_NO = 1 to:
OVER (PARTITION BY O_WORKSTATION)
ORDER BY STATION_NAME
I also tried:
OVER (PARTITION BY O_WORKSTATION
ORDER BY STATION_NAME)
I am getting "Incorrect syntax near the keyword 'OVER'". Thanks for the help.

Duane

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-11 : 15:14:11

What version of SQL are you using? the ROW_NUMBER() function is only for 2005 and later.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-11 : 16:08:55

I am using 2005, and the Row_Number() function worked fine. It is just giving me results from one Station_ID and I need one for each Station_ID.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-11 : 19:00:14

I have found a subquery that works for the 'Yes' or 'No', but I don't know how to incorporate it into this one. Maybe someone can help me plug this in, probably without the Resource_ID value in it:

SELECT
Resource_ID
,CASE COUNT(CASE WHEN date_provided BETWEEN @StartDate AND @EndDate THEN 1 END)
WHEN 0 THEN 'No'
ELSE 'Yes'
END AS Scheduled
FROM SYS_SKED_CONFLICT
GROUP BY RESOURCE_ID

Thanks in advance for any more help.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources