Running count of marked records in report
I found a little problem with this procedure. It does work mostly. Just a review - take 5 tests get paid 50 - take another 5 get paid 100 - take another 5 and get paid 50 and so on. If a report is run and a person has taken 15 tests it shows that they are going to get paid $50 - $100 - and $50 which is correct. Now in practice a person takes 5 tests and is paid 50. Those are marked as paid and the counts starts over because when they take another 5 it wants to pay them $50 but they are entitled to $100. The code works on a hugh group of records that is able to count the groups of 5. What code would work to keep a running total of what they have taken. I have the groups marked off with a "paid" field checkbox to remove them from the report after they have been paid for a test.
Pretty convoluted and any help is appreciated.
Pretty convoluted and any help is appreciated.
<cfif isDefined("FORM.form_submitted")>
<cfloop list="#form.fieldnames#" index="this_field">
<!--- Check if the field is a chkboxPaid field and see if the value is 1,
if all is TRUE enter into the the table based on that video --->
<cfif this_field contains "chkboxPaid">
<!--- Get video id from fieldname --->
<cfset this_videoid = listlast(this_field,"_")>
<!--- Update table based on video id --->
<cfquery datasource="#datasource#">
UPDATE Test
SET Paid = 1
where EmployeeID=#FORM.EmployeeID# and videoid = <cfqueryparam value="#this_videoid#" cfsqltype="cf_sql_integer">
</cfquery>
</cfif>
</cfloop>
</cfif>
<!--- <cfquery datasource="#datasource#">
UPDATE Test
SET Paid='#FORM.chkboxPaid#'
where EmployeeID=#FORM.EmployeeID# and VideoID=#FORM.VideoID#
</cfquery> --->
<cfquery name="tests" datasource="#datasource#">
SELECT
e.EmployeeID,
e.First_Name,
e.Last_Name,
e.Paid_Credits,
v.Video_Name,
t.Test_Status,
t.Credits_Earned,
t.Test_Date,
t.TestID,
t.Paid,
t.VideoID
FROM Employees e
INNER JOIN Test t ON t.EmployeeID = e.EmployeeID
INNER JOIN VideoList v on v.VideoID = t.VideoID
WHERE (t.Test_Status = 1) AND (DATEDIFF(dd, t.Test_Date, GETDATE()) < 400) AND (t.Paid IS NULL)
ORDER BY e.EmployeeID, test_date, t.TestID
</cfquery>
<cfif tests.recordcount EQ 0>
<h1>No Payouts this week.</h1>
</cfif>
<!--- Copy over data to nice datastructure for output --->
<cfset employees = arrayNew(1)>
<cfoutput query="tests" group="EmployeeID">
<cfset employee = structNew()>
<cfset employee.EmployeeID = EmployeeID>
<cfset employee.First_Name = First_Name>
<cfset employee.Last_Name = Last_Name>
<cfset employee.Paid_Credits = Paid_Credits>
<cfset employee.Test_Groups = arrayNew(1)>
<cfset employee.Total_Amount_Paid = 0>
<!--- Loop tests and put them in groups --->
<cfset testGroup = structNew()>
<cfset testGroup.tests = arrayNew(1)>
<cfset testGroup.completed = false>
<cfset testCount = 0>
<cfoutput group="TestID">
<cfset testCount = testCount + Credits_Earned>
<cfset test = structNew()>
<cfset test.Test_Count = testCount>
<cfset test.TestID = TestID>
<cfset test.Test_Date = Test_Date>
<cfset test.Test_Status = Test_Status>
<cfset test.Video_Name = Video_Name>
<cfset test.Credits_Earned = Credits_Earned>
<cfset test.Paid = Paid>
<cfset test.VideoID = VideoID>
<!--- Test groups --->
<cfset arrayAppend(testGroup.tests, test)>
<cfif testCount mod 5 eq 0>
<cfset testGroup.Completed = true>
<cfset testGroup.Total_Tests = testCount>
<!--- Calculate amount paid --->
<cfif testCount mod 10 eq 0>
<cfset testGroup.Amount_Paid = 100>
<cfelse>
<cfset testGroup.Amount_Paid = 50>
</cfif>
<cfset employee.Total_Amount_Paid = employee.Total_Amount_Paid + testGroup.Amount_Paid>
<!--- Create a new test group for every five tests --->
<cfset arrayAppend(employee.Test_Groups, testGroup)>
<cfset testGroup = structNew()>
<cfset testGroup.tests = arrayNew(1)>
<cfset testGroup.completed = false>
</cfif>
</cfoutput>
<!--- Add incomplete group (no payout yet) --->
<cfif arrayLen(testGroup.tests) neq 0>
<cfset testGroup.Total_Tests = testCount>
<cfset testGroup.Amount_Paid = 0>
<cfset arrayAppend(employee.Test_Groups, testGroup)>
</cfif>
<cfset employee.Total_Tests = testCount>
<!--- Add to employee array --->
<cfset arrayAppend(employees, employee)>
</cfoutput>
<!---<cfdump var="#employees#">--->
<!--- Output report --->
<cfoutput>
<cfloop array="#employees#" index="employee">
<table width="600" class="displayTable">
<tr>
<td><h3>#employee.First_Name# #employee.Last_Name#</h3></td>
<td><h3>#employee.Total_Tests# tests taken, $#employee.Total_Amount_Paid# paid total</h3></td>
</tr>
</table>
<!--- Loop test groups --->
<cfloop array="#employee.Test_Groups#" index="testGroup">
<cfform action="PayOutCheckbox.cfm" method="post">
<input TYPE="hidden" NAME="EmployeeID" VALUE="#employee.EmployeeID#">
<table border="1" cellpadding="4" style="margin-bottom:15px;" class="displayTable">
<!--- Loop tests --->
<cfloop array="#testGroup.tests#" index="test">
<tr>
<td>#dateFormat(test.Test_Date, "medium")#</td>
<td>#test.VideoID# - #test.Video_Name#</td>
<td>
<cfif #test.Paid# eq "paid">
<cfinput type="checkbox" name="chkboxPaid_#test.VideoID#" checked="yes">
<cfelse>
<cfinput type="checkbox" name="chkboxPaid_#test.VideoID#">
</cfif>
</td>
</tr>
</cfloop>
<tr>
<!--- If you add more columns, update colspan to the number of columns --->
<th align="center" colspan="2">
<cfif testGroup.Completed>
$#testGroup.Amount_Paid# paid for this group
<cfelse>
No payout
</cfif>
</th>
<td><input name="markPaid" type="submit" value="Paid"></td>
</tr>
</table>
<input type="hidden" name="form_submitted" value="1" />
</cfform>
</cfloop>
</cfloop>
</cfoutput>ASKER
Would it be possible to fit some code in that would just start counting from now on and not worry about the previous tests? They have been paid so it is of no concern. I was looking for something that would keep a running count even though they were marked paid.
Oh, I misunderstood. I thought the issue was that if a person had performed 5 tests already and was paid $50, the next time they should be paid $100, but because they were already paid, they are not getitng credit for the first 5 so they are only getting paid $50.
Shouldn't the total tests take into account all test paid or not so you can see if they get $100 or $50?
In either case, this type of select statement can count whatever you like, it really depends on the where clause whether it is counting paid only, unpaid only or all tests.
Looks like this where clause counts unpaid:
WHERE (t.Test_Status = 1) AND (DATEDIFF(dd, t.Test_Date, GETDATE()) < 400) AND (t.Paid IS NULL)
This could do it perhaps::
Shouldn't the total tests take into account all test paid or not so you can see if they get $100 or $50?
In either case, this type of select statement can count whatever you like, it really depends on the where clause whether it is counting paid only, unpaid only or all tests.
Looks like this where clause counts unpaid:
WHERE (t.Test_Status = 1) AND (DATEDIFF(dd, t.Test_Date, GETDATE()) < 400) AND (t.Paid IS NULL)
This could do it perhaps::
select count(*) as totalTests
, case when floor(count(*)/5) < 1 then 0
when floor(count(*)/5) % 2 = 1 then 50
else 100
end lastPaid
from Test t
where t.Test_Status = 1
AND DATEDIFF(dd, t.Test_Date, GETDATE()) < 400
AND t.Paid IS NULL
and t.EmployeeID = 111ASKER
This gives a running total of how much they have been paid and what they are due after the next 5 are taken. I can't figure out how to shoehorn the "Next Amt Paid" portion into my original posted code to show the next amt to be paid with the individual test details displaying.
SELECT Employees.First_Name AS Fname, Employees.Last_Name AS Lname, SUM(Test.Credits_Earned) AS Credits, ROUND(SUM(Test.Credits_Earned) / 5, 0,
1) * 50 + ROUND(SUM(Test.Credits_Earned) / 10, 0, 1) * 50 AS AmountPaid, CASE WHEN { fn mod(SUM(Test.Credits_Earned), 10) } BETWEEN 0 AND
4 THEN 50 ELSE 100 END AS NextAmtPaid, Employees.Role
FROM Employees INNER JOIN
Test ON Employees.EmployeeID = Test.EmployeeID
GROUP BY Employees.First_Name, Employees.Last_Name, Employees.EmployeeID, Employees.Role
HAVING (Employees.EmployeeID IS NOT NULL) AND (SUM(Test.Credits_Earned) > 0) AND (Employees.Role <> 2)
ORDER BY Fname, LnameASKER
This gives a running total of how much they have been paid and what they are due after the next 5 are taken. I can't figure out how to shoehorn the "Next Amt Paid" portion into my original posted code to show the next amt to be paid with the individual test details displaying.
SELECT Employees.First_Name AS Fname, Employees.Last_Name AS Lname, SUM(Test.Credits_Earned) AS Credits, ROUND(SUM(Test.Credits_Earned) / 5, 0,
1) * 50 + ROUND(SUM(Test.Credits_Earned) / 10, 0, 1) * 50 AS AmountPaid, CASE WHEN { fn mod(SUM(Test.Credits_Earned), 10) } BETWEEN 0 AND
4 THEN 50 ELSE 100 END AS NextAmtPaid, Employees.Role
FROM Employees INNER JOIN
Test ON Employees.EmployeeID = Test.EmployeeID
GROUP BY Employees.First_Name, Employees.Last_Name, Employees.EmployeeID, Employees.Role
HAVING (Employees.EmployeeID IS NOT NULL) AND (SUM(Test.Credits_Earned) > 0) AND (Employees.Role <> 2)
ORDER BY Fname, Lname
plug my code into your employee query, it will tell you the last amount the person was paid
SELECT Employees.First_Name AS Fname
, Employees.Last_Name AS Lname
, Employees.Role
, (select case when floor(SUM(t.Credits_Earned)/5) < 1 then 0
when floor(SUM(t.Credits_Earned)/5) % 2 = 1 then 50
else 100
end lastPaid
from Test t
where t.Test_Status = 1
AND DATEDIFF(dd, t.Test_Date, GETDATE()) < 400
AND t.Paid IS NULL
and t.EmployeeID = employees.employeeID) as LastPaid
FROM Employees
HAVING Employees.EmployeeID IS NOT NULL
ORDER BY Fname, LnameASKER
It shows everyone's last amount paid as 100 even if you only took one test.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I may do this. I added this table at the bottom after the array runs. It lists the same people except just shows what they next payout amount they are due. The table data is from this query
SELECT Employees.First_Name AS Fname, Employees.Last_Name AS Lname, SUM(Test.Credits_Earned) AS Credits, ROUND(SUM(Test.Credits_Earned) / 5, 0, 1)
* 50 + ROUND(SUM(Test.Credits_Earned) / 10, 0, 1) * 50 AS AmountPaid, CASE WHEN { fn mod(SUM(Test.Credits_Earned), 10) } BETWEEN 0 AND
4 THEN 50 ELSE 100 END AS NextAmtPaid, Employees.Role, Test.Paid
FROM Employees INNER JOIN
Test ON Employees.EmployeeID = Test.EmployeeID
GROUP BY Employees.First_Name, Employees.Last_Name, Employees.Role, Test.Paid
HAVING (SUM(Test.Credits_Earned) > 0) AND (Employees.Role <> 2) AND (Test.Paid IS NULL) AND (MAX(Employees.EmployeeID) IS NOT NULL)
ORDER BY Fname, LnameASKER
And I just show the fname, lname and nextamtpaid field
ASKER
I need to filter the new query and I am getting "The multi-part identifier "t.Test_Date" could not be bound" Why can't I restrict by date?
SELECT Employees.EmployeeID, Employees.First_Name AS Fname, Employees.Last_Name AS Lname, SUM(Test.Credits_Earned) AS Credits,
ROUND(SUM(Test.Credits_Earned) / 5, 0, 1) * 50 + ROUND(SUM(Test.Credits_Earned) / 10, 0, 1) * 50 AS AmountPaid,
CASE WHEN { fn mod(SUM(Test.Credits_Earned), 10) } BETWEEN 0 AND 4 THEN 50 ELSE 100 END AS NextAmtPaid, Employees.Role, Test.Paid,
Test.Test_Status, Test.Test_Date
FROM Employees INNER JOIN
Test ON Employees.EmployeeID = Test.EmployeeID
GROUP BY Employees.First_Name, Employees.Last_Name, Employees.EmployeeID, Employees.Role, Test.Paid, Test.Test_Status, Test.Test_Date
HAVING (Employees.EmployeeID IS NOT NULL) AND (SUM(Test.Credits_Earned) > 0) AND (Employees.Role <> 2) AND (Test.Paid IS NULL) AND
(Test.Test_Status = 1) AND (DATEDIFF(dd, t.Test_Date, GETDATE()) < 400)
ORDER BY Fname, LnameASKER
Wait - got it
You can add some math to a query that counts all the past tests taken, this select statement will return a value of $50, $100 (or zero) as the last amount the person was paid... does that help?
Open in new window
You can try it out here with hard coded numbers, the 16 is the number of tests this person has taken, you can change it to 21, 5, 0, etc to see what the result would be...
Open in new window