cancel
Showing results for 
Search instead for 
Did you mean: 

need to pull records for full work week two weeks back

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

search this forum for a work week formula

Former Member
0 Kudos

Try:


SELECT max(case when a.attribute_name = 'InstallerContact' 
                    then "A"."CREATED_DATE" else null end) as installerContactCreatedDate,
             max(case when a.attribute_name = 'dateScheduled' 
                    then "A"."CREATED_DATE" else null end) as dateScheduledCreatedDate,
     TO_NUMBER("A"."OBJECT_KEY")
FROM "OMS"."AUDITS" "A"
WHERE 
 ("A"."ATTRIBUTE_NAME" = 'installerContact'
   AND "A"."CREATED_DATE" > SYSDATE - 45)  
OR
  ("A"."ATTRIBUTE_NAME" = 'dateScheduled'
   AND "A"."CREATED_DATE" between SYSDATE, - datepart("dw", sysdate) - 13
                                             and SYSDATE - datepart("dw", sysdate) - 7 ) 
group by "A"."OBJECT_KEY"
having max(case when a.attribute_name = 'dateScheduled' 
                    then "A"."CREATED_DATE" else null end) is not null

(where datepart("DW", <date>) returns the day of week. It's not clear what DB you are using.)

This assumes that the installer date is within 32 days or so of the scheduled date. Increase the 45 (or remove the date condition altogether - but that would be very slow on a big table) if that's not the case.

sysdate - datepart() returns the prior Saturday,

subtracting 7 days is the end of your week period (Saturday) two weeks ago,

subtracting 13 is the Sunday of your week period two weeks ago.

The case statement within the max() database aggregate functions will be null, and therefore not effect the results, if the attribute name is not the one we are interested in at the moment.

The having clause will restrict the returned records to those objects scheduled within the week of interest. (Since the WHERE clause is using an OR, there may be records with installerContact date > 45 days ago, but the dateScheduled is not within our week of interest. In that case, the max() in the HAVING clause will be null.)

And it's all done with one database query!

HTH,

Carl

Answers (0)