vendredi 9 octobre 2015

Crystal CASE Datediff range using getdate

Trying to convert the following SQL into a Case statement in CR: CASE WHEN Datediff(dd, getdate(), THS.Q_LoanInfo.DueDate) BETWEEN - 59 AND - 30 THEN '1-[Delq 30-59]' WHEN Datediff(dd, getdate(), THS.Q_LoanInfo.DueDate) BETWEEN - 179 AND - 60 THEN '2-[Delq 60-179]' WHEN Datediff(dd, getdate(), THS.Q_LoanInfo.DueDate) BETWEEN - 359 AND - 180 THEN '3-[Delq 180-359]' WHEN Datediff(dd, getdate(), THS.Q_LoanInfo.DueDate) < - 360 THEN '5-[Delq 360+]' ELSE NULL

Not sure if CASE or IF/THEN is better suited for this. Replaced 'dd' (not recognized in CR?) with DAY. Also replaced Getdate() with CurrentDate and BETWEEN with TO.

if Datediff(Day,{Q_LoanInfo.DueDate},currentdate) to - 59 AND - 30 THEN '1-[Delq 30-59]' else if Datediff(Day,{Q_LoanInfo.DueDate},CurrentDate) to - 179 AND - 60 THEN '2-[Delq 60-179]' else if Datediff(Day,{Q_LoanInfo.DueDate},CurrentDate) to - 359 AND - 180 THEN '3-[Delq 180-359]' else if Datediff(Day,{Q_LoanInfo.DueDate},CurrentDate) <= - 360 THEN '5-[Delq 360+]' ELSE 'NULL'

Currently getting the following error with this: "Not enough arguments have been given to this function". Can someone tell me what I'm doing wrong? This is supposed to populate date range buckets.

Aucun commentaire:

Enregistrer un commentaire