SQL Question

[marksk2Aetna]

Hello,

I am converting a database pass thru Query into Alteryx. I have successfully gotten it to run but now the next step is getting the workflow to run without manual intervention. For this particular situation, that means creating a date function that automatically updates itself. The current code is below, we are looking for a solution to replace the "BETWEEN" section with something that takes the previous Saturday to Friday dates. Any help would be appreciated. Thank you. 

SELECT DISTINCT C.EC_ID_PREFIX||C.EC_ID||right(rtrim('0'||char(C.EC_ID_SEG_NO)),2) AS CLAIM_ID,
C.ORSEVNT_CRTN_TSTMP,
C.CTL_NO,
E.EDIT_CD,
L.EDIT_NEW_TXT
FROM GAHPP00D.ECESR EE, GAHPP00D.EDTLG E, GAHPP00D.ORS_CLM C,
GAHPP00D.EDIT_LKP L, GAHPP00D.ORSCLM_DTL D, GAHPP00D.ORSPL_REC P
WHERE C.ORSEVNT_CRTN_TSTMP BETWEEN '2021-01-02-00.00.00.000000'
AND '2021-01-08-23.59.59.999999'
AND C.CLMOFF_CD = 153
AND E.EC_ID = EE.EC_ID
AND E.EC_ID_SEG_NO = EE.EC_ID_SEG_NO
AND TIMESTAMP (EE.ECES_CRTN_DT,EE.ECES_CRTN_TM)
<= C.ORSEVNT_CRTN_TSTMP
AND EE.ER_CD = '351'
AND E.EDIT_DISP_SEQ_NO > 0
AND C.EC_ID_PREFIX = 'E'
AND E.EC_ID = C.EC_ID
AND E.EC_ID_SEG_NO = C.EC_ID_SEG_NO
AND C.ORSEVNT_CRTN_TSTMP = E.EDTLG_PSTD_TSTMP
AND C.EC_ID = D.EC_ID
AND C.EC_ID_SEG_NO = D.EC_ID_SEG_NO
AND C.ORSEVNT_CRTN_TSTMP = D.ORSEVNT_CRTN_TSTMP
AND C.EC_ID = P.EC_ID
AND C.EC_ID_SEG_NO = P.EC_ID_SEG_NO
AND C.ORSEVNT_CRTN_TSTMP = P.ORSEVNT_CRTN_TSTMP
AND E.EDIT_CD = L.EDIT_CD
AND E.EDIT_LKP_SEQ_NO = L.EDIT_LKP_SEQ_NO
WITH UR

[JagdeeshN]

Hi @marksk2Aetna ,

Firstly you can get the previous Friday and the previous Saturday, given the current date.

Previous Friday:-

DateTimeAdd([Date],
Switch(DateTimeFormat([Date],'%a'),0,
'Sat',-1,
'Sun',-2,
'Mon',-3,
'Tue',-4,
'Wed',-5,
'Thu',-6,
'Fri',-7),
"days")

Previous Saturday

DateTimeAdd([Date],
Switch(DateTimeFormat([Date],'%a'),0,
'Sat',-7,
'Sun',-1,
'Mon',-2,
'Tue',-3,
'Wed',-4,
'Thu',-5,
'Fri',-6),
"days")

In both of the above formulas, [Date] is the given date for which the previous values are being calculated. The resulting dates can then be formatted according to what the query needs.

Once you have these values, you can now use the Dynamic Input tool, to replace certain parts of the query. In your case the date range defined by the Between  clause will be replaced by a ranged created by the calculated Previous Friday and Previous Saturday.

Also please find attached a sample workflow demonstrating the above mentioned transformations.

Please do let me know if this helped

Best,

Jagdeesh Narayanan

[ggruccio]

Hi @marksk2Aetna,

Here's one way to do it:

Dynamic input is the tool you want to use.  You can configure your input and paste the SQL similar to what you would do in the Input tool.  .. 

The trick is to add a tool grouping that will bring in the dates that you want to change in the SQL expression - connect it to the dynamic input tool.  Then you can configure a find and replace within the SQL expression by clicking the Modify SQL Query -> SQL: Update WHERE Clause --- as shown below.  

[ggruccio]

Hi I wasn't aware that the solution was being worked.  What @JagdeeshN suggests is a more detailed approach and will likely work for you!