qr code c#.net generator sdk Note ANSI SQL provides support for running aggregates by means of aggregate window in C#

Printing QR Code ISO/IEC18004 in C# Note ANSI SQL provides support for running aggregates by means of aggregate window

functions. SQL Server 2005 introduced the OVER clause for aggregate functions only with the PARTITION BY clause, and unfortunately SQL Server 2008 didn t enhance the OVER clause further. Further enhancements are currently planned for the next major release of SQL Server SQL

8

Server 11. Per ANSI SQL and I hope in future versions of SQL Server you could provide a solution relying exclusively on window functions, like so:

SELECT empid, CONVERT(VARCHAR(7), ordmonth, 121) AS ordmonth, qty, SUM(O2.qty) OVER(PARTITION BY empid ORDER BY ordmonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS totalqty, CAST(AVG(1.*O2.qty) OVER(PARTITION BY empid ORDER BY ordmonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NUMERIC(12, 2)) AS avgqty FROM dbo.EmpOrders;

When this code is nally supported in SQL Server, you can expect dramatic performance improvements and obviously much simpler queries. Being familiar with the way ranking calculations based on the OVER clause are currently optimized, you should expect running aggregates based on the OVER clause to be optimized similarly. That is, given a good index to support the request, you should expect the plan to involve a single ordered scan of the data. Then the total number of rows scanned would simply be the number of rows in the table (pn).

You might also be requested to lter the data for example, return monthly aggregates for each employee only for months before the employee reached a certain target. Typically, you ll have a target for each employee stored in a Targets table that you ll need to join to. To make this example simple, I ll assume that all employees have the same target total quantity 1,000. In practice, you ll use the target attribute from the Targets table. Because you need to lter an aggregate, not an attribute, you must specify the lter expression (in this case, SUM(O2.qty) < 1000) in the HAVING clause, not the WHERE clause. The solution is as follows:

SELECT O1.empid, CONVERT(VARCHAR(7), O1.ordmonth, 121) AS ordmonth, O1.qty AS qtythismonth, SUM(O2.qty) AS totalqty, CAST(AVG(1.*O2.qty) AS NUMERIC(12, 2)) AS avgqty FROM dbo.EmpOrders AS O1 JOIN dbo.EmpOrders AS O2 ON O2.empid = O1.empid AND O2.ordmonth <= O1.ordmonth GROUP BY O1.empid, O1.ordmonth, O1.qty HAVING SUM(O2.qty) < 1000 ORDER BY O1.empid, O1.ordmonth;

This query generates the following output, shown here in abbreviated form:

empid ----------1 1 1 1 2 2 2 2 ordmonth -------2006-07 2006-08 2006-09 2006-10 2006-07 2006-08 2006-09 2006-10 qtythismonth -----------121 247 255 143 50 94 137 248 totalqty ----------121 368 623 766 50 144 281 529 avgqty ---------121.00 184.00 207.67 191.50 50.00 72.00 93.67 132.25

2 3 3 3 3 3 3 ... 2006-11 2006-07 2006-08 2006-09 2006-10 2006-11 2006-12 237 182 228 75 151 204 100 766 182 410 485 636 840 940 153.20 182.00 205.00 161.67 159.00 168.00 156.67

Things get a bit tricky if you also need to include the rows for those months in which the employees reached their target. If you specify SUM(O2.qty) <= 1000 (that is, write <= instead of <), you still won t get the row in which the employee reached the target unless the total through that month is exactly 1,000. But remember that you have access to both the cumulative total and the current month s quantity, and using these two values together, you can solve this problem. If you change the HAVING lter to SUM(O2.qty) O1.qty < 1000, you get the months in which the employee s total quantity, excluding the current month s orders, had not reached the target. In particular, the rst month in which an employee reached or exceeded the target satis es this new criterion, and that month will appear in the results. The complete solution follows:

SELECT O1.empid, CONVERT(VARCHAR(7), O1.ordmonth, 121) AS ordmonth, O1.qty AS qtythismonth, SUM(O2.qty) AS totalqty, CAST(AVG(1.*O2.qty) AS NUMERIC(12, 2)) AS avgqty FROM dbo.EmpOrders AS O1 JOIN dbo.EmpOrders AS O2 ON O2.empid = O1.empid AND O2.ordmonth <= O1.ordmonth GROUP BY O1.empid, O1.ordmonth, O1.qty HAVING SUM(O2.qty) - O1.qty < 1000 ORDER BY O1.empid, O1.ordmonth;

This query generates the following output, shown here in abbreviated form:

empid ----------1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3 3 ... ordmonth -------2006-07 2006-08 2006-09 2006-10 2006-11 2006-07 2006-08 2006-09 2006-10 2006-11 2006-12 2006-07 2006-08 2006-09 2006-10 2006-11 2006-12 2007-01 qtythismonth -----------121 247 255 143 318 50 94 137 248 237 319 182 228 75 151 204 100 364 totalqty ----------121 368 623 766 1084 50 144 281 529 766 1085 182 410 485 636 840 940 1304 avgqty ---------121.00 184.00 207.67 191.50 216.80 50.00 72.00 93.67 132.25 153.20 180.83 182.00 205.00 161.67 159.00 168.00 156.67 186.29