[Resolved] Select records highest combined value

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)

[Resolved] Select records highest combined value

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-25 : 16:42:03

Have following query that that connects to db2/400:

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''select   shkcoo,
           shdoco,
           shdcto,
           shmcu, 
           shan8,
           shshan,
           shaft,
           wajobn,
           shurcd
from       VGISOEDTA.F42019 as f1
left join (select distinct wajob, wajobn
            from VGIWRQLIB.WRQASI) 
            as f2 on f2.wajob = f1.shvr01
where      shupmj >= ''''' + @JdeDateFrom + ''''' 
order by   shkcoo, 
           shdoco '')'

In the f1 table there are 2 fields: DateUpdated (SHUPMJ) and TimeUpDated (SHTDAY), they are both numeric 6.0.

Example:

111220 161244
111220 161244
111221 85121
111221 85121
111221 93016

I need to be able to select only record with highest combined value (DateUpdate and TimeUpdated)within Company (SHKCOO) and Order Number (SHDOCO). Not sure how to do that. Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-25 : 17:36:45

What does the data in the Date and Time columns represent?

Looking at the last column
Is 111221 = 2011-12-21? Meaning YYMMDD?
Is 93016 = 09:30:16? Meaning (H)HMMSS?

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-26 : 08:28:57

Date: 111221 means position 1 = century, position 2 and 3 = year, positions 4 thru 6 day of year (cyyddd). This is how JDEdwards stores the date.

[url]http://peoplesoft.macek.cc/dateconverter.php?[/url]

[url]http://www.kirix.com/stratablog/jd-edwards-date-conversions-cyyddd[/url]

Time: 93016 means 09:30:16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 08:44:21

use like


SET @SQLSTRING = 'SELECT
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY SHKCOO,SHDOCO ORDER BY SHUPMJ DESC,SHTDAY DESC) AS Seq FROM OPENQUERY( AS400SRV_MSDASQL, 
''select   shkcoo,
           shdoco,
           shdcto,
           shmcu, 
           shan8,
           shshan,
           shaft,
           wajobn,
           shurcd
from       VGISOEDTA.F42019 as f1
left join (select distinct wajob, wajobn
            from VGIWRQLIB.WRQASI) 
            as f2 on f2.wajob = f1.shvr01
where      shupmj >= ''''' + @JdeDateFrom + ''''' 
order by   shkcoo, 
           shdoco '')))t WHERE Seq = 1'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-26 : 09:57:05

Running the above query I get a couple of syntax errors, I've tried but cannot locate where and what they are:

quote:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 10:07:50

[code]
SET @SQLSTRING = 'SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY SHKCOO,SHDOCO ORDER BY SHUPMJ DESC,SHTDAY DESC) AS Seq FROM OPENQUERY( AS400SRV_MSDASQL,
''select shkcoo,
shdoco,
shdcto,
shmcu,
shan8,
shshan,
shaft,
wajobn,
shurcd
from VGISOEDTA.F42019 as f1
left join (select distinct wajob, wajobn
from VGIWRQLIB.WRQASI)
as f2 on f2.wajob = f1.shvr01
where shupmj >= ''''' + @JdeDateFrom + '''''
order by shkcoo,
shdoco '')))t WHERE Seq = 1'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-26 : 11:06:08

Sorry but still one sytax to go. here is a print of the sql string:

SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY SHKCOO,SHDOCO ORDER BY SHUPMJ DESC,SHTDAY DESC) AS Seq FROM OPENQUERY( AS400SRV_MSDASQL, 
'select   shkcoo,
           shdoco,
           shdcto,
           shmcu,    
           shan8,
           shshan,
           shaft,
           wajobn,
           shurcd
from       VGISOEDTA.F42019 as f1
left join (select distinct wajob, wajobn
            from VGIWRQLIB.WRQASI) 
            as f2 on f2.wajob = f1.shvr01
where      shupmj >= ''111182'' 
order by   shkcoo, 
           shdoco ')))t WHERE Seq = 1

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-27 : 06:07:40

remove one closing braces



Posted - 08/26/2011 :  10:07:50          
 
SET @SQLSTRING = 'SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY SHKCOO,SHDOCO ORDER BY SHUPMJ DESC,SHTDAY DESC) AS Seq FROM OPENQUERY( AS400SRV_MSDASQL, 
''select   shkcoo,
           shdoco,
           shdcto,
           shmcu, 
           shan8,
           shshan,
           shaft,
           wajobn,
           shurcd
from       VGISOEDTA.F42019 as f1
left join (select distinct wajob, wajobn
            from VGIWRQLIB.WRQASI) 
            as f2 on f2.wajob = f1.shvr01
where      shupmj >= ''''' + @JdeDateFrom + ''''' 
order by   shkcoo, 
           shdoco ''))t WHERE Seq = 1'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-29 : 10:30:04

Got it working but had to do this:

SET @SQLSTRING = 'SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY SHKCOO,SHDOCO ORDER BY SHUPMJ DESC,SHTDAY DESC) AS Seq FROM OPENQUERY( AS400SRV_MSDASQL, 
''select   shupmj,      
           shtday,  
           shkcoo,
           shdoco,
           shdcto,
           shmcu, 
           shan8,
           shshan,
           shaft,
           wajobn,
           shurcd
from       VGISOEDTA.F42019 as f1
left join (select distinct wajob, wajobn
            from VGIWRQLIB.WRQASI) 
            as f2 on f2.wajob = f1.shvr01
where      shupmj >= ''''' + @JdeDateFrom + ''''' 
order by   shkcoo, 
           shdoco ''))t WHERE Seq = 1'

Nice of you, visakh16, to help out. Thanks very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 10:48:28

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources