I have the following query that calculates accrued leave time for the month:
SELECT username AS ltUser,
CASE WHEN FLOOR(DATEDIFF(NOW(), hiredate) / 365) < 1
THEN '0'
WHEN FLOOR(DATEDIFF(NOW(), hiredate) / 365) <= 5
THEN '14'
WHEN FLOOR(DATEDIFF(NOW(), hiredate) / 365) >= 6
THEN '18'
END AS ltHours
FROM tblusers
WHERE cl_ccrual = '1' AND hiredate IS NOT NULL
ORDER BY hiredate
I then display the results on my page like this:
<form action="" method="post">
<table border="1" cellpadding="1" cellspacing="1">
<tr>
<td><input type="checkbox" name="checkbox" id="checkbox" title="Check this box and click Submit button to add Accruals to database." /></td>
<td>ltUser</td>
<td>ltHours</td>
</tr>
<cfoutput query="rsLeaveTimeManagerAccruals">
<tr>
<td>#rsLeaveTimeManagerAccruals.ltUser#</td>
<td>#rsLeaveTimeManagerAccruals.ltHours#</td>
</tr>
</cfoutput>
</table>
</form>
<input type="submit" name="Submit" id="Submit" value="Submit" title="Check box & Submit to insert the Hours listed above for each User."/>
Here is what the page looks like: (just an HTML table that dynamically displays the records to view before inserting)
Question: How do I Insert the "x" number of query results for (ltUser & ltHours) into tblLeaveTime?
- WHERE "checkbox" = 1
Note: There can be a varying number of records output by the query.
AdChoices