How to encorporate Date and Time comparison to pull a field
-
I'm trying to pull this data in my first inner join trying to pull the rmsbalance based on the latest date/time combination but not having any luck with my syntax because sql is throwing errors due to my syntax below and I can't figure how how to do the check on the date and time combo:
SELECT rf.RMSTRANCDE,
rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum Tran Amt],
rf10.rmsbalance
FROM RMASTER rm
INNER JOIN <----- Problem starts in this inner join
(
SELECT RMSFILENUM,
rmsbalance
FROM RFINANL a
where rmstrandte + rmstrantim = (select max(rmstrandte) + max(rmstrantm) from RFINANL)
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
GROUP BY rm.rmsacctnum, rf.RMSTRANCDE, rf10.rmsbalance
HAVING rf10.rmsbalance <> SUM(rf.rmstranamt)
AND rf10.rmsbalance <> 0.00
-
What are the datatypes of the 2 columns rmstrandte and rmstrantim ?
-
Also you have a non-aggregate expression in your hAVING, are you sure that isn't the cause of the syntax error ?
-
>>>What are the datatypes of the 2 columns rmstrandte and rmstrantim ?
rmstrandte - numeric(8,0) e.g. 20050512
rmstrantim - numeric(6,0) e.g. 173025
Hit me on the head for this stupid question but what do you mean by non-aggregate expression...learning every day here...
-
The expression "AND rf10.rmsbalance <> 0.00" belongs in a WHERE clause, not a HAVING.
Also, since you are storing date and time separately, taking a max of both independantly and then adding them does not give you the max date/time combo and gives you a rather meaningless numeric expression.
You'd be better choosing a true date/time datatype, or convert them to varchars for comparison:
SELECT RMSFILENUM,
rmsbalance
FROM RFINANL a
where cast(rmstrandte as char(8)) + cast(rmstrantim As char(6))
= (select Max( cast(rmstrandte as char(8)) + cast(rmstrantim As char(6))) from RFINANL)
Even that may fail if you don't have your time column zero-padded for hours less than 12.
-
Thanks very much for your explanations, they helped a TON. Now, yes, it would have been nice if this 3rd party ERP system would have just kept the damn date as a datetime but they split it out into 2 varchar fields because the stupid database is denormalized.
Also, I just found in my results there there isn't one unique date/time...so i'm trying to find out if either the time has seconds or if there's another second field I can include matchin on but so far I'm not sure, researching the ERP system...
So I have this at this point but it's not returning results anymore after putting in the casts
SELECT rf.RMSTRANCDE,
rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum Tran Amt],
rf10.rmsbalance
FROM RMASTER rm
INNER JOIN
(
SELECT RMSFILENUM,
rmsbalance
FROM RFINANL a
where rmstrandte = (select max(rmsbalance) from RFINANL)
and cast(rmstrandte as char(8)) + cast(rmstrantim As char(6))
= (select Max( cast(rmstrandte as char(8)) + cast(rmstrantim As char(6))) from RFINANL)
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
WHERE rf10.rmsbalance <> 0.00
AND rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, rf.RMSTRANCDE, rf10.rmsbalance
HAVING rf10.rmsbalance <> SUM(rf.rmstranamt)
-
Take a closer look at this:
SELECT RMSFILENUM,
rmsbalance
FROM RFINANL a
where rmstrandte = (select max(rmsbalance) from RFINANL)
and
cast(rmstrandte as char(8)) + cast(rmstrantim As char(6))
= (select Max( cast(rmstrandte as char(8)) + cast(rmstrantim As char(6))) from RFINANL)
The only possible way this will give you any records is if the MAX() balance happens to occur on the MAX() date/time.
What is it exactly that you need to join to ? The date on which the MAX() balance occurred ?
-
>>>The only possible way this will give you any records is if the MAX() balance happens to occur on the MAX() date/time.
YES, this is exactly what I want. Let's say I have these 2 records below:
acct# rmstranamt rmstrandte rmstrantim rmsbalance
4313030999894992 85.00 20051106 225602 3265.12
4313030999894992 178.31 20051106 225603 3528.43
What I'm saying is that I want to pull rmsblance for the 225603 since that record has the latest rmstrandte/rmstrantim combination
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply