need results of full work week two weeks back

[vancoug]

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: “AND "A"."CREATED_DATE" > SYSDATE – 16” was used to limit the results from a huge table and ‘16’ 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 don’t 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) – 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 { installerContactCreatedDate } is populated late Friday afternoon and field { dateScheduledCreatedDate } 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 { installerContactCreatedDate } is populated the 9th and the { dateScheduledCreatedDate } 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.

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

 

[kskid]

to grab the last two business weeks, how about
(
{Command.INSTALLERCONTACTCREATEDDATE} in [minimum(lastfullweek) + 1 to Maximum(lastfullweek) - 1] or
{Command.INSTALLERCONTACTCREATEDDATE} in [minimum(lastfullweek) - 6 to Minimum(lastfullweek) - 2]
)

 

[lbass]

Why not use the following?

{Command.INSTALLERCONTACTCREATEDDATE} in
CurrentDate - DayofWeek(CurrentDate) - 12
to CurrentDate - DayofWeek(CurrentDate) – 8

-LB

 

[vancoug]

LB-

after I posted last night in a couple of forums I could see it was being viewed but no replies so I reread the post to see if it made any sense and thought the answer was right in front of my face- as your post confirmed. But when I tried it I didnt get the results i thought I would. I think it puts me on the right track though.

Thanks-

 

[lbass]

For further help, you need to explain the results you did get, and how they are different from what you are looking to get.

-LB