Post Author: edy80y
CA Forum: Crystal Reports
Hi All,
I have successfully dumped an SQL into a 'Add Command' function and it returns the data exactly how i want it. But now i want to add a parameter so that i dont have to manually update the date which is referenced 8 times.
The TIME_KEY field is in this format YYYYMMDDHHMMWST (for 20/12/2007 2:30PM) = 200712201430WST
I have used LIKE '20071220%' so it returns all rows within that one date.
Now, i created a string parameter {?date) so i can enter '20071220' and i want to attach the wildcard (%) to the parameter.
If i do any of the following it doesn't work:
LIKE {?date} + '%'
LIKE {?date} || '%'
LIKE {?date} & '%'
Here is the code:
SELECT "T1"."OBJECT_ID", "T1"."TIME_KEY", (SELECT SUM("N_ANSWERED") AS "ANSWERED_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ABANDONED_RING") AS "ABANDONED_RING_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ABANDONED_QUEUE") AS "ABANDONED_QUEUE_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ENTERED") AS "ENTERED_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ABANDONED_SHORT") AS "ABANDONED_SHORT_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ANS_IN_THRESH") AS "ANS_IN_THRESH_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ABANDONED") AS "ABANDONED_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%')FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T1"WHERE "T1"."TIME_KEY" LIKE '20071220%'GROUP BY "T1"."OBJECT_ID", "T1"."TIME_KEY"ORDER BY "T1"."OBJECT_ID", "T1"."TIME_KEY"