Using mulitple datetime fields for record selection purpose

[ceceperfect10]

Hi:

I am a new babe in crystal so be gentle....Using ODBC/SQL...I have a table that has multiple datetime fields. I am trying to pull in the records for which both my create date and resolved date have a criteria set as LastFullWeek...Both dates are reporting on my entry-id field. I have tried creating formulas and have tried using the UNION to join 'cause if I do the reports separately I don't have a problem.....ex.
SELECT
SD_Problem."Entry-Id", SD_Problem."Group Assigned", SD_Problem."Region", SD_Problem."Resolved Status Date"
FROM
"SD.Problem" SD_Problem
WHERE
SD_Problem."Region" = 'North America' AND
SD_Problem."Resolved Status Date" >=
{ts '2003-10-26 00:00:00.00'} AND
SD_Problem.&quot;Resolved Status Date&quot; <
{ts '2003-11-02 00:00:00.00'}
ORDER BY
SD_Problem.&quot;Group Assigned&quot; ASC
UNION
SELECT
SD_Problem.&quot;Created Date&quot;, SD_Problem.&quot;Entry-Id&quot;, SD_Problem.&quot;Group Assigned&quot;, SD_Problem.&quot;Region&quot;
FROM
&quot;SD.Problem&quot; SD_Problem
WHERE
SD_Problem.&quot;Region&quot; = 'North America' AND SD_Problem.&quot;Created Date&quot; >=
{ts '2003-10-26 00:00:00.00'} AND
SD_Problem.&quot;Created Date&quot; <
{ts '2003-11-02 00:00:00.00'}
ORDER BY
SD_Problem.&quot;Group Assigned&quot; ASC

Thanking you in advance for your kind assistance....

 

[bdreed35]

I don't think that you need a UNION here.

In your Records Seleciton criteria, put this:

{SD_Problem.Region} = "North America"
AND
{SD_Problem.Created Date} IN LastFullWeek
AND
{SD_Problem.Resolved Status Date} IN LastFullWeek

It should auto create the SQL for you.

~Brian

 

[ceceperfect10]

Thanks Brian....I tried that and it's not working....I've even tried creating groups on my two date fields and it not separating the two....for each group I need to see a count of the entry_ids and the group assigned...it's showing the create date and resolved date for each entry_id...Any other suggestions would be greatly appreciated...

 

[ceceperfect10]

Sorry Brian: I should have said I that for each date field I need to see the number of entry_ids assigned to each group.....

 

[lbass]

If you are trying to report on records where the created date or the resolved date is in the last full week, you could try:

{SD_Problem.Region} = &quot;North America&quot;
AND
(
{SD_Problem.Created Date} IN LastFullWeek
or
{SD_Problem.Resolved Status Date} IN LastFullWeek
)

Then you could create a formula (or group) {@Status} using the following formula:

if isnull({SD_Problem.Resolved Status Date}) and
{SD_Problem.Created Date} IN LastFullWeek then &quot;Created&quot; else
if {SD_Problem.Created Date} in LastFullWeek and{SD_Problem.Resolved Status Date} in LastFullWeek then &quot;Created and Resolved&quot; else
if {SD_Problem.Resolved Status Date} in LastFullWeek then &quot;Resolved&quot;

Then you could do two running totals, using {SD_Problem.Entry ID}, distinctcount, evaluate based on a formula:

{@Status} in [&quot;Created&quot;,&quot;Created and Resolved&quot;] //for the created running total

{@Status} in [&quot;Resolved&quot;,&quot;Created and Resolved&quot;] //for the resolved running total

Reset never.

-LB