Skip to Content
0
Former Member
Jan 22, 2009 at 01:35 AM

need to pull records for full work week two weeks back

29 Views

I wanted to pull the results from two date fields and calculate the lapsed time. Both dates in the calculation are from the same field, so I used the command feature in Crystal to differentiate the two

SELECT max("A"."CREATED_DATE") as installerContactCreatedDate,

TO_NUMBER("A"."OBJECT_KEY")

FROM "OMS"."AUDITS" "A"

WHERE "A"."ATTRIBUTE_NAME" = 'installerContact'

AND "A"."CREATED_DATE" > SYSDATE - 16

group by "A"."OBJECT_KEY"

SELECT max("A"."CREATED_DATE") as dateScheduledCreatedDate,

TO_NUMBER( "A"."OBJECT_KEY")

FROM "OMS"."AUDITS" "A"

WHERE "A"."ATTRIBUTE_NAME" = 'dateScheduled'

AND "A"."CREATED_DATE" > SYSDATE - 16

group by "A"."OBJECT_KEY"

Incidently: u201CAND "A"."CREATED_DATE" > SYSDATE u2013 16u201D was used to limit the results from a huge table and u201816u2019 has no real significance other than trying to return the minimal results for the report.

once I had the above I used this:

datediff("H",{Command.INSTALLERCONTACTCREATEDDATE},{Command_1.DATESCHEDULEDCREATEDDATE})

Now to what I need to do; if I provided too much info I am sorry, I just was hoping to give enough.

I need the results for a full work week; the catch is I donu2019t need it for the previous week but for two weeks back; i.e. if I ran the report on Monday the 19th I want to pull records from the 5th to the 9th

I know that this formula:

{Command.INSTALLERCONTACTCREATEDDATE} in

CurrentDate - DayofWeek(CurrentDate) - 5

to CurrentDate - DayofWeek(CurrentDate) u2013 1

will pull the prior full business week (m-f); i.e. ran on Monday the 19th- pulls records for the 12th to 16th, no good for me.

Furthermore, if the field is populated late Friday afternoon and field is not populated until the next week I need to capture that on the following weeks report- eventhough the first part was populated the prior week.

So, report runs Mon. the 19th captures 5th to 9th, but is populated the 9th and the is not populated til the 12th, 13th, or 14th then that cant show on report for week of 5th to 9th, but on the report for 12th to 16th.

Iu2019d greatly appreciate any assistance and apologize if I made little to no sense when describing my issue.