Exclude Holidays and Weekends 1

[dadyswat]

I'm currently working on a report in V10 that needs to exclude the holidays and weekends. I've created a new table in SQL Server 2000 and holidays =1 all other days =0.

I used a formula this formula in testing and as long as there is only one holiday in the reporting period it seems to work, however, it creates duplicates in the report. If the holidays prtion is removed it works fine except for the holidays. I tried using a global variable with a counter but then the formula asks for a boolean datatype. At this point I'm totally lost as to why I get the duplicates. Running the SQL query in Query Analyser the data seems fine.

Local DateTimeVar d1 := {AX_0001_A.StartDate};
Local DateTimeVar d2 := {AX_0001_B.EndDate};
Local NumberVar d3:= if {Datestable.Dates} in[{AX_0001_A.StartDate} to {AX_0001_B.EndDate}]
and {Datestable.Holiday}=1 then d3:=1
else
d3:=0;



DateDiff ("d", d1, d2) -
DateDiff ("ww", d1, d2, crSaturday) -
DateDiff ("ww", d1, d2, crSunday) - d3

 

[synapsevampire]

If you have a table of dates, and you join it by the date and set the Report->Selection Formula->Record to:

{Datestable.Holiday} = 0
and
day({Datestable.Dates}) in [1,7]

Then you should only get business days.

-k

 

[dadyswat]

OK, I gave this a try and the duplicates are back??

 

[synapsevampire]

Crystal doesn't fabricate data, look at what is in the Database->Show SQL to learn what Crystal is using as it's query.

If you can't figure it out, you should post what is in there, as well as what is in the Report->Selection Formula->Record

-k

 

[dadyswat]

Just let me clarify want you're recommending. You're saying drop the variable d3 and them add the selection criteria in the formula area of the select expert? Would it make a difference that these are datetime fields vs date fields?

 

[pelajhia]

I think synapsev. is asking you to post what you have in your selection formula and to give some sample data if possible.

 

[dadyswat]

OK, here we go. First I'll post the Query from Crystal and then the sql Data. The next post I'll add the Holiday date formula. Looks like the word wrap makes it a little messy. This works fine but doesn't correct for the holidays.


SELECT DISTINCT "AX_0001_B"."refobjid",
"IX_0001"."CLAIM_NO",
"AX_0001_LUT"."valuetext", "IX_0001"."NON_UM",
"AX_0001_LUT"."id",
"OB_0001"."vccid", "IX_0001"."EMP_TIER",
"AX_0001_A"."StartDate",
"AX_0001_B"."EndDate", "IX_0001"."CASE_STATUS",
"IX_0001"."TEAM_NO",
"AX_0001"."ReceivedDate", "AX_0001"."C9Type",
"AX_0001_LUT2"."valuetext",
"AX_0001_LUT2"."id", "IVUEAPPVCCNODEPATHS"."appid",
"IVUEAPPVCCNODEPATHS"."nodevalue",
"AX_0001"."refobjid"
FROM (((((("Careworks"."dbo"."AX$0001_A" "AX_0001_A"
INNER JOIN "Careworks"."dbo"."AX$0001_B" "AX_0001_B"
ON "AX_0001_A"."refobjid"="AX_0001_B"."refobjid")
INNER JOIN "Careworks"."dbo"."IX$0001" "IX_0001"
ON "AX_0001_A"."fid"="IX_0001"."fid")
INNER JOIN "Careworks"."dbo"."OB$0001" "OB_0001" ON ("AX_0001_A"."refobjid"="OB_0001"."objid")
AND ("IX_0001"."fid"="OB_0001"."fid")) INNER JOIN "Careworks"."dbo"."AX$0001_LUT" "AX_0001_LUT"
ON "AX_0001_B"."C9Status"="AX_0001_LUT"."valuecode")
INNER JOIN "Careworks"."dbo"."AX$0001" "AX_0001" ON "AX_0001_B"."refobjid"="AX_0001"."refobjid")
INNER JOIN "Careworks"."dbo"."AX$0001_LUT2" "AX_0001_LUT2" ON "AX_0001"."C9Type"="AX_0001_LUT2"."valuecode")
INNER JOIN "Careworks"."dbo"."IVUEAPPVCCNODEPATHS"
"IVUEAPPVCCNODEPATHS" ON "OB_0001"."vccid"="IVUEAPPVCCNODEPATHS"."nodeid"
WHERE "IX_0001"."NON_UM"<>'Y' AND "IX_0001"."EMP_TIER"<>'A'
AND "IX_0001"."CASE_STATUS" LIKE 'C%' AND ("AX_0001_A"."StartDate">={ts '2006-07-09 00:00:00'}
AND "AX_0001_A"."StartDate"<{ts '2006-07-14 00:00:01'}) AND "AX_0001_LUT"."id"=5
AND ("OB_0001"."vccid"=22 OR "OB_0001"."vccid"=32) AND "AX_0001_LUT2"."id"=6 AND "IVUEAPPVCCNODEPATHS"."appid"=1
ORDER BY "IX_0001"."TEAM_NO", "AX_0001"."C9Type",
"AX_0001_LUT"."valuetext", "AX_0001_B"."refobjid"

Sql Data

15723838 06-822545 Approved 5 22 Z 7/3/06 11:17 7/6/06 8:43 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15723838
15723838 06-822545 Approved 5 22 Z 7/3/06 11:17 7/6/06 8:34 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15723838
15734479 94-23347 Approved 5 22 Z 7/3/06 20:19 7/6/06 10:27 CNWK 1 00:00.0 1 Retro 6 1 Original C-9 15734479
15748670 02-823725 Approved 5 22 D 7/5/06 14:29 7/6/06 15:27 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15748670
15748677 02-823725 Approved 5 22 D 7/5/06 14:29 7/6/06 15:28 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15748677
15828590 04-310055 Approved 5 22 D 7/6/06 15:01 8/18/06 10:19 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15828590
15829863 06-318829 Approved 5 22 C 7/6/06 15:49 7/10/06 13:11 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15829863
15906398 72-36547 Approved 5 22 Z 7/7/06 15:24 7/12/06 9:17 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15906398
15733139 05-388796 Denied 5 22 E 7/3/06 14:49 7/6/06 8:50 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15733139
15723334 85-9818 Duplicate 5 22 Z 7/3/06 10:53 7/5/06 14:18 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15723334
15829650 90-31829 Duplicate 5 22 Z 7/6/06 15:43 7/10/06 11:12 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15829650
15726750 99-560064 Information Only 5 22 Z 7/3/06 13:00 7/5/06 14:20 CNWK 1 00:00.0 1 Retro 6 1 Original C-9 15726750
15828590 04-310055 Pended for Reactivation 5 22 D 7/6/06 15:01 7/10/06 10:33 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15828590
15828590 04-310055 Pended for Reactivation 5 22 D 7/6/06 15:01 7/7/06 14:17 CWRK 1 00:00.0 1 Retro 6 1 Original C-9 15828590
15747471 93-17015 Amended 5 22 Z 7/5/06 13:48 7/7/06 9:32 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15747471
15722391 94-10504 Approved 5 22 Z 7/3/06 10:12 7/6/06 8:49 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15722391
15722491 77-39763 Approved 5 22 Z 7/3/06 10:19 7/6/06 9:08 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15722491
15723608 77-9853 Approved 5 22 Z 7/3/06 11:06 7/6/06 8:13 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15723608
15724339 00-571232 Approved 5 22 B 7/3/06 11:37 7/6/06 11:06 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15724339
15724744 06-821917 Approved 5 22 E 7/3/06 11:56 7/5/06 15:39 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15724744
15733145 03-377887 Approved 5 22 C 7/3/06 14:51 7/6/06 12:09 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15733145
15733146 03-377887 Approved 5 22 C 7/3/06 14:51 7/6/06 12:13 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15733146
15733273 988906-22 Approved 5 22 Z 7/3/06 18:40 7/6/06 8:47 CMMI 1 00:00.0 2 Current 6 1 Original C-9 15733273
15734230 89-60608 Approved 5 22 Z 7/3/06 19:50 7/5/06 17:03 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15734230
15744506 67-26779 Approved 5 22 Z 7/5/06 11:41 7/7/06 9:26 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15744506
15745494 89-28747 Approved 5 22 Z 7/5/06 12:28 7/6/06 14:48 CMMI 1 00:00.0 2 Current 6 1 Original C-9 15745494
15748042 92-83868 Approved 5 22 D 7/5/06 14:06 7/7/06 12:28 CNWK 1 00:00.0 2 Current 6 1 Original C-9 15748042
15748647 02-823725 Approved 5 22 D 7/5/06 14:28 7/6/06 15:27 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15748647
15748683 02-823725 Approved 5 22 D 7/5/06 14:29 7/6/06 15:28 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15748683
15748836 06-348011 Approved 5 22 B 7/5/06 14:35 7/6/06 13:14 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15748836
15749189 06-312199 Approved 5 22 E 7/5/06 14:49 7/7/06 14:16 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15749189
15819573 00-811603 Approved 5 22 C 7/6/06 8:53 7/10/06 13:28 CMMI 1 00:00.0 2 Current 6 1 Original C-9 15819573
15823941 05-887750 Approved 5 22 E 7/6/06 11:31 7/19/06 11:24 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15823941
15829182 01-415277 Approved 5 22 Z 7/6/06 15:26 7/7/06 14:43 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15829182
15835878 93-44397 Approved 5 22 E 7/6/06 20:28 7/10/06 15:57 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15835878
15899402 04-414604 Approved 5 22 E 7/7/06 10:15 7/11/06 13:10 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15899402
15900057 99-555644 Approved 5 22 D 7/7/06 10:35 7/12/06 12:29 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15900057
15904382 99-426685 Approved 5 22 B 7/7/06 13:52 7/11/06 8:48 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15904382
15905233 82-40467 Approved 5 22 Z 7/7/06 14:27 7/11/06 8:54 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15905233
15907478 06-347935 Approved 5 22 Z 7/7/06 16:31 7/11/06 10:45 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15907478
15762758 01-364326 Denied 5 22 Z 7/5/06 21:09 7/21/06 15:17 CMMI 1 00:00.0 2 Current 6 1 Original C-9 15762758
15902949 03-436115 Denied 5 22 B 7/7/06 12:32 7/11/06 10:26 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15902949
15750334 93-8730 Denied at PA Review 5 22 Z 7/5/06 15:49 7/18/06 16:41 CNWK 1 00:00.0 2 Current 6 1 Original C-9 15750334
15977180 82-10694 Duplicate 5 22 Z 7/8/06 8:50 7/11/06 12:39 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15977180
15749644 92-87231 Information Only 5 22 Z 7/5/06 15:14 7/6/06 12:00 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15749644
15821263 72-36547 Information Only 5 22 Z 7/6/06 10:07 7/7/06 10:14 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15821263
15821699 77-9404 Information Only 5 22 Z 7/6/06 10:22 7/6/06 14:34 CPTD 1 00:00.0 2 Current 6 1 Original C-9 15821699
15907063 01-415277 Information Only 5 22 Z 7/7/06 16:06 7/10/06 11:48 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15907063
15750334 93-8730 Pended for Med 5 22 Z 7/5/06 15:49 7/6/06 13:03 CNWK 1 00:00.0 2 Current 6 1 Original C-9 15750334
15762758 01-364326 Pended for Med 5 22 Z 7/5/06 21:09 7/6/06 9:45 CMMI 1 00:00.0 2 Current 6 1 Original C-9 15762758
15823941 05-887750 Pended for Med 5 22 E 7/6/06 11:31 7/7/06 18:24 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15823941
15750334 93-8730 Pended for Phys Rev 5 22 Z 7/5/06 15:49 7/17/06 13:04 CNWK 1 00:00.0 2 Current 6 1 Original C-9 15750334
15749644 92-87231 Routed to Specialist 5 22 Z 7/5/06 15:14 7/6/06 9:46 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15749644
15821263 72-36547 Routed to Specialist 5 22 Z 7/6/06 10:07 7/7/06 8:46 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15821263
15821699 77-9404 Routed to Specialist 5 22 Z 7/6/06 10:22 7/6/06 12:03 CPTD 1 00:00.0 2 Current 6 1 Original C-9 15821699
15907063 01-415277 Routed to Specialist 5 22 Z 7/7/06 16:06 7/10/06 9:37 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15907063
15724356 00-571232 Withdrawn 5 22 B 7/3/06 11:38 7/7/06 10:40 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15724356
15762758 01-364326 Withdrawn 5 22 Z 7/5/06 21:09 7/21/06 15:27 CMMI 1 00:00.0 2 Current 6 1 Original C-9 15762758
15827829 00-507974 Withdrawn 5 22 Z 7/6/06 14:28 7/11/06 7:30 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15827829
15901519 95-613949 Withdrawn 5 22 B 7/7/06 11:19 7/10/06 15:29 CWRK 1 00:00.0 2 Current 6 1 Original C-9 15901519
15824737 04-806814 Denied 5 22 C 7/6/06 12:17 7/11/06 13:38 CWRK 1 00:00.0 5 Inactive Retro 6 1 Original C-9 15824737
15747097 06-353298 Approved 5 22 B 7/5/06 13:31 7/7/06 10:39 CWRK 2 00:00.0 1 Retro 6 1 Original C-9 15747097
15822582 01-411850 Approved 5 22 B 7/6/06 10:50 7/10/06 8:08 CWRK 2 00:00.0 1 Retro 6 1 Original C-9 15822582
15823403 05-869687 Approved 5 22 E 7/6/06 11:13 7/10/06 10:03 CWRK 2 00:00.0 1 Retro 6 1 Original C-9 15823403
15823426 05-869687 Approved 5 22 E 7/6/06 11:14 7/10/06 10:03 CWRK 2 00:00.0 1 Retro 6 1 Original C-9 15823426
15825921 05-869687 Approved 5 22 E 7/6/06 13:17 7/10/06 10:24 CWRK 2 00:00.0 1 Retro 6 1 Original C-9 15825921
15742838 95-366143 Withdrawn 5 22 Z 7/5/06 10:40 7/7/06 10:02 CWRK 2 00:00.0 1 Retro 6 1 Original C-9 15742838
15828058 04-340374 Withdrawn 5 22 C 7/6/06 14:39 7/10/06 9:47 CWRK 2 00:00.0 1 Retro 6 1 Original C-9 15828058
15900448 05-893421 Amended 5 22 E 7/7/06 10:47 7/11/06 11:05 CWRK 2 00:00.0 2 Current 6 1 Original C-9 15900448
15720705 02-403569 Approved 5 22 Z 7/3/06 8:57 7/5/06 15:46 CWRK 2 00:00.0 2 Current 6 1 Original C-9 15720705
15724237 01-456099 Approved 5 22 B 7/3/06 11:30 7/6/06 8:46 CWRK 2 00:00.0 2 Current 6 1 Original C-9 15724237
15724691 06-323250 Approved 5 22 D 7/3/06 11:54 7/5/06 17:59 CWRK 2 00:00.0 2 Current 6 1 Original C-9 15724691
15725005 93-22856 Approved 5 22 Z 7/3/06 12:27 7/5/06 14:31 CWRK 2 00:00.0 2 Current 6 1 Original C-9 15725005
15733561 81-27514 Approved 5 22 E 7/3/06 19:37 7/6/06 7:55 CWRK 2 00:00.0 2 Current 6 1 Original C-9 15733561
15734424 06-311746 Approved 5 22 B 7/3/06 20:12 7/5/06 16:43 CWRK 2 00:00.0 2 Current 6 1 Original C-9 15734424
15739312 86-21304 Approved 5 22 Z 7/3/06 21:12 7/18/06 14:32 CPTD 2 00:00.0 2 Current 6 1 Original C-9 15739312

 

[synapsevampire]

So what is the issue now, first you mention dupes, now "This works fine but doesn't correct for the holidays."

I don't see anything in the WHERE clause about holidays = 0, so we need the record selection formula.

-k

 

[dadyswat]

Right, I have to add that clause again, I'll been tied up on a couple of other things. When I linked the tables I got no return.


SELECT DISTINCT "AX_0001_B"."refobjid", "IX_0001"."CLAIM_NO",
"AX_0001_LUT"."valuetext", "IX_0001"."NON_UM",
"AX_0001_LUT"."id", "OB_0001"."vccid", "IX_0001"."EMP_TIER", "AX_0001_A"."StartDate", "AX_0001_B"."EndDate", "IX_0001"."CASE_STATUS", "IX_0001"."TEAM_NO", "AX_0001"."ReceivedDate", "AX_0001"."C9Type", "AX_0001_LUT2"."valuetext", "AX_0001_LUT2"."id", "IVUEAPPVCCNODEPATHS"."appid", "IVUEAPPVCCNODEPATHS"."nodevalue", "AX_0001"."refobjid", "Datestable"."Holiday", "Datestable"."Dates"
FROM ((((((("Careworks"."dbo"."Datestable" "Datestable" INNER JOIN "Careworks"."dbo"."AX$0001_A" "AX_0001_A" ON "Datestable"."Dates"="AX_0001_A"."StartDate") INNER JOIN "Careworks"."dbo"."AX$0001_B" "AX_0001_B" ON "AX_0001_A"."refobjid"="AX_0001_B"."refobjid") INNER JOIN "Careworks"."dbo"."IX$0001" "IX_0001" ON "AX_0001_A"."fid"="IX_0001"."fid") INNER JOIN "Careworks"."dbo"."OB$0001" "OB_0001" ON ("AX_0001_A"."refobjid"="OB_0001"."objid") AND ("IX_0001"."fid"="OB_0001"."fid")) INNER JOIN "Careworks"."dbo"."AX$0001_LUT" "AX_0001_LUT" ON "AX_0001_B"."C9Status"="AX_0001_LUT"."valuecode") INNER JOIN "Careworks"."dbo"."AX$0001" "AX_0001" ON "AX_0001_B"."refobjid"="AX_0001"."refobjid") INNER JOIN "Careworks"."dbo"."AX$0001_LUT2" "AX_0001_LUT2" ON "AX_0001"."C9Type"="AX_0001_LUT2"."valuecode") INNER JOIN "Careworks"."dbo"."IVUEAPPVCCNODEPATHS" "IVUEAPPVCCNODEPATHS" ON "OB_0001"."vccid"="IVUEAPPVCCNODEPATHS"."nodeid"
WHERE "IX_0001"."NON_UM"<>'Y' AND "IX_0001"."EMP_TIER"<>'A' AND "IX_0001"."CASE_STATUS" LIKE 'C%' AND ("AX_0001_A"."StartDate">={ts '2006-07-02 00:00:00'} AND "AX_0001_A"."StartDate"<{ts '2006-07-09 00:00:01'}) AND "AX_0001_LUT"."id"=5 AND ("OB_0001"."vccid"=22 OR "OB_0001"."vccid"=32) AND "AX_0001_LUT2"."id"=6 AND "IVUEAPPVCCNODEPATHS"."appid"=1 AND "Datestable"."Holiday"=0
ORDER BY "IX_0001"."TEAM_NO", "AX_0001"."C9Type", "AX_0001_LUT"."valuetext", "AX_0001_B"."refobjid"

 

[synapsevampire]

We still need the record selection formula, but the join does show that it's linking to some date and you are filtering.

I'd grab the above SQL and go into the SQL Server Enterprise Manager, right click Views and select New, then paste in this SQL and look at what it's doing there to learn how the tables should be joined.

-k

 

[dadyswat]

Ok, I think I know where the problem is. First I've simplified the report. Second, the reason things aren't working properly is these are datetime fields. When I tried joining tables through Query Analyser the results where all wrong. And direct query against the dates table the results where correct. So it has something to do with the date time fields, just not sure why its not working yet.

Here is the simplified query. But I removed the datestable link at the moment.


SELECT DISTINCT "AX_0001_B"."refobjid", "IX_0001"."CLAIM_NO", "AX_0001_LUT"."valuetext", "IX_0001"."NON_UM", "AX_0001_LUT"."id", "OB_0001"."vccid", "IX_0001"."EMP_TIER", "AX_0001_B"."EndDate", "IX_0001"."CASE_STATUS", "IX_0001"."TEAM_NO", "AX_0001"."ReceivedDate", "AX_0001"."C9Type", "AX_0001_LUT2"."valuetext", "AX_0001_LUT2"."id", "IVUEAPPVCCNODEPATHS"."appid", "IVUEAPPVCCNODEPATHS"."nodevalue", "AX_0001"."refobjid", "OB_0001"."createtime"
FROM ("Careworks"."dbo"."IX$0001" "IX_0001" INNER JOIN (((("Careworks"."dbo"."AX$0001_B" "AX_0001_B" INNER JOIN "Careworks"."dbo"."AX$0001" "AX_0001" ON "AX_0001_B"."refobjid"="AX_0001"."refobjid") INNER JOIN "Careworks"."dbo"."AX$0001_LUT" "AX_0001_LUT" ON "AX_0001_B"."C9Status"="AX_0001_LUT"."valuecode") INNER JOIN "Careworks"."dbo"."OB$0001" "OB_0001" ON "AX_0001"."refobjid"="OB_0001"."objid") INNER JOIN "Careworks"."dbo"."AX$0001_LUT2" "AX_0001_LUT2" ON "AX_0001"."C9Type"="AX_0001_LUT2"."valuecode") ON "IX_0001"."fid"="OB_0001"."fid") INNER JOIN "Careworks"."dbo"."IVUEAPPVCCNODEPATHS" "IVUEAPPVCCNODEPATHS" ON "OB_0001"."vccid"="IVUEAPPVCCNODEPATHS"."nodeid"
WHERE "IX_0001"."EMP_TIER"<>'A' AND ("OB_0001"."vccid"=22 OR "OB_0001"."vccid"=32) AND "AX_0001_LUT2"."id"=6 AND "AX_0001_LUT"."id"=5 AND "IX_0001"."NON_UM"<>'Y' AND "IVUEAPPVCCNODEPATHS"."appid"=1 AND "IX_0001"."CASE_STATUS" LIKE 'C%' AND ("OB_0001"."createtime">={ts '2006-07-02 00:00:00'} AND "OB_0001"."createtime"<{ts '2006-07-09 00:00:01'})
ORDER BY "IX_0001"."TEAM_NO", "AX_0001"."C9Type", "AX_0001_LUT"."valuetext", "AX_0001_B"."refobjid"

 

[synapsevampire]

OK, I give. I've asked for the record selection enough times (3).

But adding back in the holiday information is critical.

-k

 

[dadyswat]

Here is the section from the record select in Crystal:

{IX_0001.EMP_TIER} <> "A" and
{OB_0001.vccid} in [32, 22] and
{AX_0001_LUT2.id} = 6 and
{AX_0001_LUT.id} = 5 and
{IX_0001.NON_UM} <> "Y" and
{IVUEAPPVCCNODEPATHS.appid} = 1 and
{IX_0001.CASE_STATUS} like "C*" and
{OB_0001.createtime} in {?AXStartDate} to {?AXEndDate}
and
{Datestable.Holiday} = 0
and
day({Datestable.Dates}) in [1,7]

There are two other formulas that may have a bearing

1. StartDay_of_the_Week:

crSunday= DatePart ("W",{OB_0001.createtime} ) +1 and
crSaturday= DatePart ("W",{OB_0001.createtime} ) - 5
The following is the SQL Query from Crystal:

2.EndDay_of_the_weeK:

CrSunday=DatePart ("W",{AX_0001_B.EndDate} ) +1 and
CrSaturday=DatePart ("W",{AX_0001_B.EndDate} ) - 5

SELECT DISTINCT "AX_0001_B"."refobjid", "IX_0001"."CLAIM_NO", "AX_0001_LUT"."valuetext", "IX_0001"."NON_UM", "AX_0001_LUT"."id", "OB_0001"."vccid", "IX_0001"."EMP_TIER", "AX_0001_B"."EndDate", "IX_0001"."CASE_STATUS", "IX_0001"."TEAM_NO", "AX_0001"."ReceivedDate", "AX_0001"."C9Type", "AX_0001_LUT2"."valuetext", "AX_0001_LUT2"."id", "IVUEAPPVCCNODEPATHS"."appid", "IVUEAPPVCCNODEPATHS"."nodevalue", "AX_0001"."refobjid", "OB_0001"."createtime", "Datestable"."Dates", "Datestable"."Holiday"
FROM (("Careworks"."dbo"."IX$0001" "IX_0001" INNER JOIN (((("Careworks"."dbo"."AX$0001_B" "AX_0001_B" INNER JOIN "Careworks"."dbo"."AX$0001" "AX_0001" ON "AX_0001_B"."refobjid"="AX_0001"."refobjid") INNER JOIN "Careworks"."dbo"."AX$0001_LUT" "AX_0001_LUT" ON "AX_0001_B"."C9Status"="AX_0001_LUT"."valuecode") INNER JOIN "Careworks"."dbo"."OB$0001" "OB_0001" ON "AX_0001"."refobjid"="OB_0001"."objid") INNER JOIN "Careworks"."dbo"."AX$0001_LUT2" "AX_0001_LUT2" ON "AX_0001"."C9Type"="AX_0001_LUT2"."valuecode") ON "IX_0001"."fid"="OB_0001"."fid") INNER JOIN "Careworks"."dbo"."IVUEAPPVCCNODEPATHS" "IVUEAPPVCCNODEPATHS" ON "OB_0001"."vccid"="IVUEAPPVCCNODEPATHS"."nodeid") INNER JOIN "Careworks"."dbo"."Datestable" "Datestable" ON "OB_0001"."createtime"="Datestable"."Dates"
WHERE "IX_0001"."EMP_TIER"<>'A' AND ("OB_0001"."vccid"=22 OR "OB_0001"."vccid"=32) AND "AX_0001_LUT2"."id"=6 AND "AX_0001_LUT"."id"=5 AND "IX_0001"."NON_UM"<>'Y' AND "IVUEAPPVCCNODEPATHS"."appid"=1 AND "IX_0001"."CASE_STATUS" LIKE 'C%' AND ("OB_0001"."createtime">={ts '2006-07-02 00:00:00'} AND "OB_0001"."createtime"<{ts '2006-07-09 00:00:01'}) AND "Datestable"."Holiday"=0
ORDER BY "IX_0001"."TEAM_NO", "AX_0001"."C9Type", "AX_0001_LUT"."valuetext", "AX_0001_B"."refobjid"

I'm very new to this and have on had a basic class on selecting records so I'm learning as I go. I do appreaciate any help I receive.

 

[dpatrickcollins]

This is a shot in the dark, but it seems to me you would want to abandon the dates table altogether and adopt the following formula:

//Excluded Holidays this year (list incomplete for demo)
Local datevar array holidays := [date(2005,12,31), date(2005,11,24), date(2005,07,04),date(2005,02,14)];
Local datetimevar start := {Table.StartDate};
Local datetimevar end := {Table.EndDate};
Local numbervar num_valid_days;

//Calculate days between start and end excluding weekends/holidays
While start < end Do (
//Is it holiday or weekend?
If not (dayofweek(start) in[1,7]) and not (start in holidays) Then (
num_valid_days := num_valid_days + 1;
);
start := dateadd("d",1, start);
);
num_valid_days

This calculates the total number of days from StartDate to EndDate excluding weekends and holidays. Two disclaimers: it is more conceptual, meaning easy to follow, but you may wish to tweak it if you are counting partial days for example. It also does require hard-coding of holidays for a particular year. That said, I cannot think the alternative, creating and linking to a listing of dates that need to be maintained, is any less maintenance intensive, and certainly messy in terms of duplicate rows in your report.

 

[synapsevampire]

dpats solution is similar to what Crystal used to espouse years ago, however I urge you to take the table approach, which is how datamarts handle this sort of thing.

Not sure why this would cause row inflation, but I think that I messed up the weekday part, it should read:

...
{Datestable.Holiday} = 0
and
NOT(day({Datestable.Dates}) in [1,7])

To eliminate the weekends.

-k

 

[dadyswat]

dpatrickcollins

Your solution worked. Yes, it will be a little cumbersome hard coating all the holidays in for the next few years but I need to get the report running and available.

synapsevampire

I don't want to give up on the table for the holidays as its something I can use with MS Access to allow users to update the holidays as needed.

Thanks to both of you.

 

[synapsevampire]

It's hard to convince most Crystal people to think long term and take a datacentric approach to solutions, but I would avoid hacks and hardcoding at all costs.

You won't see a data warehouse hardcoding this sort of thing...

Sorry it didn't work out, hard to diagnose row inflation from afar, perhaps you've a dba there that can shed some light on this.

Check out Ken Hamadys site for more on dpats solution:

http://www.kenhamady.com/formula.html

-k

 

[dpatrickcollins]

dadyswat:

Your welcome. Synapsev is right; we are favoring expediency over best practices with this solution.

If in your case you have a date period specified by a begin and end date, I don't see how the data table approach will not lead to either some row inflation --- simply for the fact that each date period "row" could correspond to one or more holiday dates --- or missing holidays by inadvertantly joinding the start date directly to the dates table -- you'd lose any holidays that fell between the date period.

That said, I would suggest implementing the dates table approach with the following join from "work" table to dates table:

dates.date >= work.startdate and
dates.date <= work.enddate

The result will be a row for each day during each work row's work period. Then, just group by work row, suppress the detail, and do a simple count of days. OH: be sure to filter out holidays and weekend days as synapsev originally suggested. That will give you the desired result.

Again, not sure why your particular solution was inflating rows; it should have been dropping off holidays, but the above would allow your users to configure and probably is closer in line with what synapsev is suggesting as best practice.

 

[dadyswat]

Thanks for all the help.

The report is available but I'm going to keep going to get the Date table working. Just makes better sense in the long run. I think part of the row inflation problem is the fact that the data is retrieved from a view that asks for the max audit time with certain additional values. The link to the datestable then creates multiple matches, thus the row inflation.

 

[dadyswat]

By the way dpatrickcollins, you are correct it was dropping the holiday dates in addition to the row inflation. The dates.date >= work.startdate and dates.date <= work.enddate
you're referring to, is that a record select clause or a link to the table?