Ok here are my current queries, thanks to Ben I got that part working.
Query 1:
I pass a date range:
<CFQUERY datasource="#DSN#" name="qgetsi">
SELECT *
FROM SelfInspection
WHERE SI_dt BETWEEN #CREATEODBCDate(form.date1)# AND
#CREATEODBCDate(form.date2)#
</CFQUERY>
That works fine. then I have this next query, with a sub query, that checks
the SI_Items2 table
to see what items where checked for each qgetsi.SI_OPSRPT.ID.
<cfquery name="qgetitems" datasource="#DSN#">
SELECT SI_ITEM_NAME
FROM SI_ITEMS
WHERE si_item_id IN (
SELECT si_item_id
FROM SI_ITEMS2
WHERE SI_ITEM_ID IN (#SI_Item_ID#)
AND SI_Opsrpt_ID = #qgetsi.SI_Opsrpt_ID#)
Group by si_item_name
</cfquery>
Ok for the query above its deal with 2 tables one is SI_ITEMS which
simply have an SI_ITEM_ID and a name SI_ITEM_NAME
the 2nd table SI_ITEMS2 has the item id SI_ITEM_ID and the report
number SI_OPSRPT_ID this table gets updated when someone fills out
a report if they select 3 items when filling out the report the ONE report
number is plopped in to SI_ITEMS2 with each item id. SO if there were
3 items selected when entering report 5 it would enter:
SI_OPSRPT_ID SI_ITEM_ID
5 12
5 15
5 16
And then you can refer back to table SI_ITEMS and/or SelfInspection
to get the particulars about the ITEM ID or the REPORT ID. So now
I want to run a report between specific dates that will show how many times
an item
was reported on within that period.
Ok lets say the item I wanted to run a report on was Rubber. (although
someone may want to
run a report on multiple items too_
The 2nd query I posted above will run for EACH qgetsi.si_opsrpt_id since
I have it within the <cfoutput query="qgetsi"> tags.
So the results look something like:
qgetitems (Records=1, Time=0ms)
SQL = SELECT SI_ITEM_NAME
FROM SI_ITEMS
WHERE si_item_id IN (
SELECT si_item_id
FROM SI_ITEMS2
WHERE SI_ITEM_ID IN (5)
AND SI_Opsrpt_ID = 5)
and then if it finds it again:
qgetitems (Records=1, Time=0ms)
SQL = SELECT SI_ITEM_NAME
FROM SI_ITEMS
WHERE si_item_id IN (
SELECT si_item_id
FROM SI_ITEMS2
WHERE SI_ITEM_ID IN (5)
AND SI_Opsrpt_ID = 14)
So say it found rubber twice. Now in my output. How do I get that count. I
messed around with COUNT
but being that it runs each query separately for eacy report id i didnt see
a way to count it properly.
I just want the output to be something like
>From this date to that date: there were 2 Rubber problems.
what am i missing?
Kelly
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
