General information
Category:
Coding, syntax & commands
I'm trying to make a cross-tab report. It is to show the number of times an order for a report was requested - by month and state.
For example:
---USAGE REPORT---
Worker's Comp.
State Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
AK
AL
AZ
CA
NY
OH
PA
WY
with the appropriate frequency counts in each row/col.
I thought I could do this with one SQL, but it's not working like I thought. Here's the SQL
SELECT;
Orderitems.report_nam,;
Orderitems.state,;
COUNT(MONTH(Orderitems.closed)==1) as January,;
COUNT(MONTH(Orderitems.closed)==2) as Febuary;
COUNT(*) as Total;
FROM ofax!orderitems;
WHERE NOT EMPTY(Orderitems.state) = .T.;
AND NOT EMPTY(Orderitems.closed) = .T.;
GROUP BY Orderitems.report_nam, Orderitems.state;
ORDER BY Orderitems.report_nam, Orderitems.state
The first 2 COUNT()'s give me the same value as the Total count for that report for that state.
Is there any way to get the count for each month and the total?
Can it be done in one REASONABLE SQL? or will I need one for each month?
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only