Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Topic: SQL Nightmare!! Posted: 04 Nov 2013 at 8:56pm
Hello
I'm a beginner at SQL to say the least and am hoping someone can help me!!
I have two fields, created date and created time, for each customer I need to identify the latest date and then the latest time for that date (there may be multiple records) I'm hoping to display the info in a cross tab so SQL seems the only way to go but however I try and write the logic Crystal just doesn't like it, any ideas?
Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Posted: 05 Nov 2013 at 4:48am
Hello
This is what i'd got to work correctly:
( SELECT MAX (MHD_CRED) FROM "SIPR"."MEN_MHD" MHD WHERE "MEN_MHD"."MHD_UDF1" = MHD.MHD_UDF1 AND "MEN_MHD"."MHD_UDF2" = MHD.MHD_UDF2 AND "MEN_MHD"."MHD_UDF3" = MHD.MHD_UDF3 )
but within the MHD_CRED there could be multiple MHD_CRET and I need to take the maximum of both values, any ideas?
Joined: 26 Dec 2012
Online Status: Offline
Posts: 24
Posted: 05 Nov 2013 at 5:16am
Hi :)
I might be talking rubbish but if your dataset is in order of date and time then you might be better using LAST instead of MAX, and then it'll bring back the date and time relating to the last record for each customer? Or changing your SQL so it sorts the data by date and time, and then selects the last record for each customer?
Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Posted: 05 Nov 2013 at 5:24am
Thanks, how does the SQL query know it is the last record for the customer? I had used MAX under the assumption that the SQL would recognise it was a date field and select the relevant record, is this how it works?
Joined: 26 Dec 2012
Online Status: Offline
Posts: 24
Posted: 05 Nov 2013 at 5:30am
Yep you'd think that would make sense but if you have 2 records for a customer like below:
Customer Date Time A 10/11/2013 20:00:00 A 15/11/2013 17:00:00
if you use MAX you'll get date of 15/11/2013 and time of 20:00 which is mixing up records, if you use LAST it searches for the last record, which is what you want assuming your data is in date then time order, and would give you date of 15/11/2013, time of 17:00. Your data has to be in date then time order for it to work though, or you can have an ORDER BY in the SQL query, you can have a kind of query within a query to do this, but I think if LAST works for you then that's the easiest way? I can't check this properly at my end though as I'm querying an iSeries and it doesn't let me use the LAST aggregate.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
