Forgot a comma after the closing parenthis and y
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Smith,
Mike
Sent: Montag, 30. März 2020 17:23
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] RE: sql troubles
Thanks Birgitta
I'm trying to test this out.
I get a Token Y was not valid.
I thought maybe it needed a 'With' before the Y , but no.
Then I thought maybe it needed a ',' before the Y, but then it wanted a
group by.
Not sure what I might be missing.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Birgitta Hauser
Sent: Monday, March 30, 2020 11:14 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: sql troubles
Sorry did not read the eMail correctly.
Here the modified SQL statement:
With x as (Select *
From YourTable
Where rdate between 20181212 and 20190101
and batch <> 'ORTE'
and prem = '100')
y as (Select PREM, Max(RDate concat RTIME) rtime
From x)
Select x.*
from x join y on x.Prem = y.Prem
and x.Rdate concat x.Rtime = y.rtime;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is
worse than training your staff and losing them? Not training them and
keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to." (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Birgitta Hauser
Sent: Montag, 30. März 2020 17:07
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: sql troubles
Try:
With x as (Select *
From YourTable
Where rdate between 20181212 and 20190101 and batch <> 'ORTE'
and prem = '100')
y as (Select PREM, RDATE, Max(RTIME) rtime
From x)
Select x.*
from x join y on x.Prem = y.Prem
and x.Rdate = y.Rdate
and x.RTime = y.RTime; Mit freundlichen Grüßen / Best
regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is
worse than training your staff and losing them? Not training them and
keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to." (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin
Taylor
Sent: Montag, 30. März 2020 16:32
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: sql troubles
Would this work:
select PREM, max( digits(RDATE) concat digits(RTIME) )
from hstread00
where rdate between 20181212 and 20190101 and batch <> 'ORTE' and prem =
'100'
group by PREM
-----Original Message-----
From: Smith, Mike [mailto:Mike_Smith@xxxxxxxxxxxxxxxx]
Sent: Monday, March 30, 2020 9:13 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: sql troubles
I am trying to select data from a history file. There may be multiple
records on a day, and I only want to select the record with the last time
for the each date in the date range.
PREM Batch RDATE RTIME
100 MR07 20181212 100000
100 MR07 20181212 110020
100 MR07 20181213 100000
101 MR08 20181212 100000
The basics are
Select * from hstread00 where rdate between 20181212 and 20190101 and batch
<> 'ORTE' and prem = '100'
I've can manage a single max date, or a single max time for the PREM, but I
need the max time for each date for each PREM
Any help appreciated.
Mike
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this message
in error, or are not the named recipient(s), please immediately notify the
sender and delete this message.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2flists.midrange.com%2fmai
lman%2flistinfo%2fmidrange-l&c=E,1,ivF-zHylcNmL7rkj4v0HHyQ47oxFGepqxWwuPyDrT
1o1p-e1qDw07KDR4bnBRV2qpVkH90Bx6UDDOSDBR9LMBgpel9ldWtuh_ZIIzNxpCiut3Jn8&typo
=1
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2farchive.midrange.com%2fm
idrange-l.&c=E,1,XxRBFVbegFjuOAam67QLIVh3JMjSBw5HFVqlTN7kKbzvZiEq4oKCS7z2R1M
Yg5d_m4O1R3j3vskmSPRMN3TMY5Nwattvi34APaA3F0YPtqVk3VbuinA,&typo=1
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fmidrange.com&c=E,1,PU3Hi
IfzlpjORvf8RtnhVVvphq37dGZ2FJ42gm2R3nJK-YOi4F__jgoh2j800yihIQkDFaQrPBnpODhIM
SIdZ4byJhitjKzMiBWYIdDfp_HxxxArc_ldEj4kfsY,&typo=1 by shopping at amazon.com
with our affiliate link:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2famazon.midrange.com&c=E,
1,DKKGoe2CTw5NZjk-8wcVl7gK2IBZurU8sBVpc1dQYabWvVAUlC-5TAA6mfoEBr0NCr4LsBiBte
Q0Q51aWonYH_yDhh_5dW5I2_ad0YGYJLit32tj4tWz&typo=1
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2flists.midrange.com%2fmai
lman%2flistinfo%2fmidrange-l&c=E,1,7Y9zR8wEMyd9Yy0mzKgpcxIMYH_7zVHM7BQGQxVS5
KR8omvQX3CvHQewi_AjR4XbEWMZDLN8LVABRuyk3euoGrkPbWYoShyvgp7_gw4U6Ny0E_pOTEw,&
typo=1
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2farchive.midrange.com%2fm
idrange-l.&c=E,1,40_IGG5GLuINViqLx3eCg3DR743vwNrZ11ej8W_74DnFzYVjWNVnzoMumDI
ouMEnmyBFaaZyXJXCic39VvK8aIsQdNuYMf9mXLbj9_fVLTmTOg,,&typo=1
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fmidrange.com&c=E,1,vXCLy
PUh2p2KIgLbcCXjKRwPzJeCHSfF8fHcgva-vV7LfejwV7Urxo8NE4JAxcgCOysBvDbJQw-Y8d8F2
6oRVfdMk9wK2SNbWD5F6rDNBi_7tF5VUeQf2BvdTs8,&typo=1 by shopping at amazon.com
with our affiliate link:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2famazon.midrange.com&c=E,
1,UtQdpK0rmjegc5rVRszjrNMCOWPm1jLpwpIZlmeckDyPirC5GCa4E5908U0yMMEG0BsOGyYcQG
U8MXLq4JlgYcBbmdicL8HeHLcqP5w8XrRjG_QB&typo=1
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2flists.midrange.com%2fmai
lman%2flistinfo%2fmidrange-l&c=E,1,EWNocDpir3PcuRrKrb_rnbb932U4TAjETCHpTtaT_
eFbCNN1KPQncjCek57M0MqE29dlOi0qgHL-nzqgzpYineIhQ4FaFnn-NvzTdnzr5iQR45pb&typo
=1
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2farchive.midrange.com%2fm
idrange-l.&c=E,1,J2-4w-SyRMsSdy21VRZHVj2ybkM7tGaUF-hf4yMgaYsfob8cZCq9pp-imGI
6DZTNxFaYH6_DFYHx5CNRxWa1g3q3TMPH1mAw19E0OhHt1izicWfNcgkv3Hazy879&typo=1
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fmidrange.com&c=E,1,flwET
AqM0nDi4wQEkQJq7XnQcgWw0QYEbXO0HVmPCZzMbl0_VZYTPX-uXalaC2Vv0LqJg6BgizMUJ01dr
1TbrJRZg0B_yWZNkQfBwBgVbV6-xVY,&typo=1 by shopping at amazon.com with our
affiliate link:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2famazon.midrange.com&c=E,
1,ffvIfMmhhDXnsZ7-bXA2ZHqFX68egUxjQucK84a2aQBCi7eMh8B59621v7DvYycYIxYBgZYSpC
8egYzniQOW3jVjTBB4jBm470iW7Je3HuC1XZOW2CSzA1EJYQ,,&typo=1
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this message
in error, or are not the named recipient(s), please immediately notify the
sender and delete this message.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
