Group with Time Values - Please Help

I have two tables that are joined by a left outer join.
Table A (Hours) just has 24 records that represent each our in the day. For example:
00:00, 01:00, ...23:00. Table B (Data) has the data I need to report off of. I joined
Table A to Table B.

I created a group on Table A because I always want to display all 24 hours even if there
is no data in Table B for that hour. So I now have 24 sections in my group.

My problem is that when I put data into the details section, I'm only getting data where
the hours exactly match. For example, Group section 08:00 is only returning data where
the hour is 08:00. I actually need it to return all data where the hour is between
08:00 and 08:59. I've been working on this for a while and I'm really stuck.

I'm using an access database and the hours field in both table is a date/time field.

Any help would be greatly appreciated. Thanks so much.

- StephanieHi,

I had experienced the same problem. I had to display the data for all the days in a month regardless of the data they have.

Used the same left outer join concept. But it didn't work. Then I had created a temp table and written code to get the result.

If any body knows why the left outer join concept is not working in crystal, please share with us

sample

table1 : contains simply all the dates from 1 to lastday
table2 : contains data for the dates in table1 (not for all the days)|||Hi Stephanie
One possible solution would be to create a report based only on Table A(with hours registered) and subreport based on Table B.Don't make any links between them.In main report insert group for field that holds hours.If you view preview now you would see all records from Table B for each hour.
In main report create a formula and add shared variable and assign only first two characters from group field.Values will be 00,01,02 etc.
Now,in subreport supress all records where first two characters of your hour fiels in Table B are not equal to shared variable.
I tested this in CR 8.0 and it worked fine.|||Thanks Denan, that's a very interesting suggestion! I'm going to try that right now!

Stephanie|||Good idea!

But what about the performance?

For a single day, the report will be called 24 times?|||Hi
Performance is definetly not optimal here.Best solution would be to filter data in subreport but unfortunately CR (at least 8.0) doesn't allow shared variables in record selection.
Biggest problem here is that you can not link those two tables.Left outer join doesn't help since it also requires a match in both tables.
Another solution would be to add another field in Table B that would hold values like 08:00,09:00 etc.That means you would need to add application logic to compute that value.I think that solution is more expensive.

Đenan

GROUP BY Bit in Integer

I have a table where each row contain a unique individual and it's scrap code. The scrap code is an integer where each of the 32 bits represent a unique scrap cause and each individual may have more than one scrap cause.

As an example: the indidual may be both too heavy and too high

Lets say these two scrap causes are represented by bit 0 and bit 1.

That again converts to the integer value 1 and 2. If both bits are true the value of the integer for that row/individual is 3. I use a support table that contain each bit (representetd as an integer value) and a description.

ScrapCode(ScrapCode, Description)

with upto 32 rows.

With this it is easy to decode the scrap code for a selected individual into separate causes, but that is for one individual.
Now I would like to create a report that count codes based on the unique scrap codes in the scrap code table. In other words I would like to group by each bit in the integer.

Table(Id, date, ScrapCode)

Select Count(*) from Table

WHERE ScrapCode <> 0

Group By "Bit"

Any suggestions?

can u supply an example of the expected outcome.|||

One way to do it is to take advantage of the POWER function, the bitwise '&' operator and a table of numbers. I mocked up the data with this:

declare @.aTable table
( rid integer,
scrapCode integer
)

insert into @.aTable
select iter,
-1000000000 + 2147483646*dbo.rand()
from small_iterator (nolock)

The definition of the SMALL_ITERATOR and DBO.RAND() objects can be found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

I computed the results with this:

Select iter-1 as [Cause],
Count(*) as[Cause Count]
from @.aTable
inner join small_iterator (nolock)
on iter <= 32
WHERE ScrapCode <> 0
and ( iter < 32 and
ScrapCode & Power (2, iter-1) > 0 or
iter = 32 and
ScrapCode < 0
)
group by iter-1
order by iter-1

/*
Cause Cause Count
-- --
0 16333
1 16494
...
30 16470
31 15336
*/

|||

Found it myself after some trial and error:

Code Snippet

SELECT v.ScrapCode, COUNT(p.Snr) AS NoOfScrap

FROM [table] p, [ScrapCodes] v

WHERE p.date > '2007-04-20'

AND p.Scrapcode <> 0

AND p.ScrapCode & v.ScrapCode <> 0

GROUP BY v.ScrapCode

Output:

1 15

2 250

4 5

8 98

512 23

....