static crosstab

[unknown]

I created a crosstab report in infomaker to show total # of
attempts an employee made on a certain group by date

so it looks something this:
monday tuesday wed
Joe Smith
Office 1 1 0
Admin 2 0 0
Policy 0 1 1
Janet Cook
Office 5 5 0
Admin 0 1 1

Now if Janet didn't do any policy work then that doesn't
appear on my crosstab & i want it to appear regardless of
whether she did the work or not.

Any suggestions???

Please note, that I'm not that SQL savy - beginner

thanks

[Paul Horan[Sybase]]

You didn't post your table structure, so I'll take a guess...
You'll want a LEFT OUTER JOIN from employee to hours_worked so that you
don't eliminate employees that didn't work any hours of that type.

Another approach - do it all in SQL and forget about using crosstabs. Since
you know there are only seven days in a week, this is easy. The key is
aggregation and conditional function expressions.

Select
employee.name,
hours_worked.description,
SUM(hours_worked.amount *
(Case hours_worked.day_of_week
when 1 then 1 else 0 end)) as MONDAY_hours,
SUM(hours_worked.amount *
(Case hours_worked.day_of_week
when 2 then 1 else 0 end)) as TUESDAY_hours,
SUM(hours_worked.amount *
(Case hours_worked.day_of_week
when 3 then 1 else 0 end)) as WEDNESDAY_hours,
...
SUM(hours_worked.amount *
(Case hours_worked.day_of_week
when 7 then 1 else 0 end)) as SUNDAY_hours
FROM
employee LEFT OUTER JOIN hours_worked
on employee.emp_id = hours_worked.emp_id
GROUP BY
employee.name, hours_worked.description
ORDER BY
employee.name, hourse_worked.description ;

This causes all the hours for Monday to fall in the MONDAY_hours column,
Tuesday into TUESDAY_hours, etc.
--
Paul Horan[Sybase]
paulhoran.pbdjmagazine.com

Post by unknown
I created a crosstab report in infomaker to show total # of
attempts an employee made on a certain group by date
monday tuesday wed
Joe Smith
Office 1 1 0
Admin 2 0 0
Policy 0 1 1
Janet Cook
Office 5 5 0
Admin 0 1 1
Now if Janet didn't do any policy work then that doesn't
appear on my crosstab & i want it to appear regardless of
whether she did the work or not.
Any suggestions???
Please note, that I'm not that SQL savy - beginner
thanks

[unknown]

Post by Paul Horan[Sybase]
You didn't post your table structure, so I'll take a
guess... You'll want a LEFT OUTER JOIN from employee to
hours_worked so that you don't eliminate employees that
didn't work any hours of that type.
Another approach - do it all in SQL and forget about using
crosstabs. Since you know there are only seven days in a
week, this is easy. The key is aggregation and
conditional function expressions.
Select
employee.name,
hours_worked.description,
SUM(hours_worked.amount *
(Case hours_worked.day_of_week
when 1 then 1 else 0 end)) as MONDAY_hours,
SUM(hours_worked.amount *
(Case hours_worked.day_of_week
when 2 then 1 else 0 end)) as TUESDAY_hours,
SUM(hours_worked.amount *
(Case hours_worked.day_of_week
when 3 then 1 else 0 end)) as WEDNESDAY_hours,
...
SUM(hours_worked.amount *
(Case hours_worked.day_of_week
when 7 then 1 else 0 end)) as SUNDAY_hours
FROM
employee LEFT OUTER JOIN hours_worked
on employee.emp_id = hours_worked.emp_id
GROUP BY
employee.name, hours_worked.description
ORDER BY
employee.name, hourse_worked.description ;
This causes all the hours for Monday to fall in the
MONDAY_hours column, Tuesday into TUESDAY_hours, etc.
--
Paul Horan[Sybase]
paulhoran.pbdjmagazine.com

crosstab report in infomaker to show total # of attempts

an employee made on a certain group by date >

monday tuesday wed
Joe Smith
Office 1 1 0
Admin 2 0 0
Policy 0 1 1
Janet Cook
Office 5 5 0
Admin 0 1 1
Now if Janet didn't do any policy work then that doesn't
appear on my crosstab & i want it to appear regardless
of whether she did the work or not.
Any suggestions???
Please note, that I'm not that SQL savy - beginner
thanks

[unknown]

hello
thank you soooooo much for your quick response. I kind of
understand what you are suggesting but I think I have a
problem with one of my columns because its a date.

I pasted the original script I was working from in
infomaker. I just don't think I can sum my attempt date. I
was doing that in infomaker. Let me know what you think.
Thanks!

Select
validfoldergroup.foldergroupdesc,
FolderProcessAttempt.attemptby,
to_char(FolderProcessAttempt.attemptdate,'DD-MON-YYYY')

from
folder, FolderProcessAttempt, FolderProcess, ValidFolder,
ValidFolderGroup, ValidProcess, ValidSub, ValidWork

where
ValidFolder.FolderType = Folder.FolderType
and ValidFolderGroup.FolderGroupCode =
ValidFolder.FolderGroupCode
and ValidWork.WorkCode = Folder.WorkCode
and ValidSub.SubCode = Folder.SubCode
and Folder.FolderRSN = FolderProcess.FolderRSN
and ValidProcess.ProcessCode = FolderProcess.ProcessCode
and ValidFolderGroup.FolderGroupCode =
ValidFolder.FolderGroupCode and ValidFolder.FolderType =
Folder.FolderType and Folder.FolderRSN =
FolderProcess.FolderRSN and FolderProcess.ProcessRSN =
FolderProcessAttempt.ProcessRSN

and FolderProcessAttempt.attemptdate between :Date1MMDDYY
AND :Date2MMDDYY
and folder.foldertype in
('AV','BD','TX','CV','DI','LO','TO','TP','TW')
and FolderProcess.processcode in ('9020', '9030','9140')

[Paul Horan[Sybase]]

Oracle... yuck. OK, I'll do my best.

Let's simplify this 8 table join as best we can...

1) You're not retrieving any data from ValidWork, ValidSub, or
ValidProcess, nor are you using them in any intermediate join steps. Let's
remove them from the FROM and WHERE clauses.

2) It appears that the "truth" table here is FolderProcessAttempt. I'll
assume that "attemptBy" is the person's name (not the best design pattern,
but that's a different topic of discussion). Let's try a LEFT OUTER JOIN to
that table, so that you'll see all the FolderGroupDesc values, whether or
not they have corresponding rows in FolderProcessAttempt. We have to move
the date comparison from the WHERE clause here too, otherwise we'll
eliminate the null-supplying rows (making the left outer join useless)

3) You had a number of WHERE clause conditions duplicated. That's not a
problem for the Oracle parser, it just muddies the SQL a lot.


SELECT
validfoldergroup.foldergroupdesc,
FolderProcessAttempt.attemptby,
to_char(FolderProcessAttempt.attemptdate,'DD-MON-YYYY')

FROM
Folder, ValidFolder, ValidFolderGroup, FolderProcess
LEFT OUTER JOIN FolderProcessAttempt
ON FolderProcess.ProcessRSN = FolderProcessAttempt.ProcessRSN
and FolderProcessAttempt.attemptdate
between :Date1MMDDYY AND :Date2MMDDYY

WHERE
ValidFolder.FolderType = Folder.FolderType and
ValidFolderGroup.FolderGroupCode = ValidFolder.FolderGroupCode and
Folder.FolderRSN = FolderProcess.FolderRSN and
ValidProcess.ProcessCode = FolderProcess.ProcessCode and

folder.foldertype in
('AV','BD','TX','CV','DI','LO','TO','TP','TW') and
FolderProcess.processcode in ('9020', '9030','9140') ;


Start with that, and see if it doesn't get you closer. We'll tackle the
other approach if this doesn't work.
--
Paul Horan[Sybase]
paulhoran.pbdjmagazine.com

Post by unknown
hello
thank you soooooo much for your quick response. I kind of
understand what you are suggesting but I think I have a
problem with one of my columns because its a date.
I pasted the original script I was working from in
infomaker. I just don't think I can sum my attempt date. I
was doing that in infomaker. Let me know what you think.
Thanks!
Select
validfoldergroup.foldergroupdesc,
FolderProcessAttempt.attemptby,
to_char(FolderProcessAttempt.attemptdate,'DD-MON-YYYY')
from
folder, FolderProcessAttempt, FolderProcess, ValidFolder,
ValidFolderGroup, ValidProcess, ValidSub, ValidWork
where
ValidFolder.FolderType = Folder.FolderType
and ValidFolderGroup.FolderGroupCode =
ValidFolder.FolderGroupCode
and ValidWork.WorkCode = Folder.WorkCode
and ValidSub.SubCode = Folder.SubCode
and Folder.FolderRSN = FolderProcess.FolderRSN
and ValidProcess.ProcessCode = FolderProcess.ProcessCode
and ValidFolderGroup.FolderGroupCode =
ValidFolder.FolderGroupCode and ValidFolder.FolderType =
Folder.FolderType and Folder.FolderRSN =
FolderProcess.FolderRSN and FolderProcess.ProcessRSN =
FolderProcessAttempt.ProcessRSN
and FolderProcessAttempt.attemptdate between :Date1MMDDYY
AND :Date2MMDDYY
and folder.foldertype in
('AV','BD','TX','CV','DI','LO','TO','TP','TW')
and FolderProcess.processcode in ('9020', '9030','9140')

[unknown]

Hello

Sorry I haven't responded to your reply sooner. Thank you
for all of your help. I was still having problems, so I am
now waiting for our DB Administrator to assist me with the
scripts you suggested. The problem is I have too many
joins, so I'll let you know how I make out.