Running count of marked records in report

JohnMac328
JohnMac328 used Ask the Experts™
on
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.
<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>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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?

 
select case when floor(count(*)/5) < 1 then 0 
            when floor(count(*)/5) % 2 = 1 then 50 
            else 100 
       end  lastPaid

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...

 
select case when floor(16/5) < 1 then 0 
            when floor(16/5) % 2 = 1 then 50 
            else 100 
       end  lastPaid

Open in new window

Author

Commented:
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::
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 = 111

Open in new window

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

Commented:
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

Open in new window

Author

Commented:
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

Open in new window

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, Lname

Open in new window

Author

Commented:
It shows everyone's last amount paid as 100 even if you only took one test.

I just ran the code through a number generator for numbers 0 to 99, this is the result showing the number (creditsEarned) and the resuling LastPaid amount.  

 
Number	lastPaid
0	0
1	0
2	0
3	0
4	0
5	50
6	50
7	50
8	50
9	50
10	100
11	100
12	100
13	100
14	100
15	50
16	50
17	50
18	50
19	50
20	100
21	100
22	100
23	100
24	100
25	50
26	50
27	50
28	50
29	50
30	100
31	100
32	100
33	100
34	100
35	50
36	50
37	50
38	50
39	50
40	100
41	100
42	100
43	100
44	100
45	50
46	50
47	50
48	50
49	50
50	100
51	100
52	100
53	100
54	100
55	50
56	50
57	50
58	50
59	50
60	100
61	100
62	100
63	100
64	100
65	50
66	50
67	50
68	50
69	50
70	100
71	100
72	100
73	100
74	100
75	50
76	50
77	50
78	50
79	50
80	100
81	100
82	100
83	100
84	100
85	50
86	50
87	50
88	50
89	50
90	100
91	100
92	100
93	100
94	100
95	50
96	50
97	50
98	50
99	50

Open in new window



Since it works given any number, the problem must be in the credits_earned, this query will show the credits_earned for each employee, I suspect the number will be wrong because that is the only way the lastPaid would be wrong..  try to tweak the sub query to get the correct number of credits_earned for each employee..

SELECT   Employees.First_Name AS Fname
       , Employees.Last_Name AS Lname
       , Employees.Role
       , (select SUM(t.Credits_Earned)
        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, Lname


Author

Commented:
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, Lname

Open in new window

Author

Commented:
And I just show the fname, lname and nextamtpaid field

Author

Commented:
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, Lname

Open in new window

Author

Commented:
Wait - got it

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial