Data Manipulation

Data Manipulation

Okay gang, I’m getting a little confused trying to build my latest app.  In concept it’s pretty simple but I’m having trouble figuring out how to implement some of the functionality.

When a logged in user goes to the web page I want them to see a list of their time clock punches for the current week as well as the previous week.  I’d also like to see totals for each day and for the current day it would be nice to see how many hours they would have if the clocked out ‘now’.

It’s a Paychex Time in a Box system and the data is punch date, punch time (in minutes from midnight), and badge.  Here’s a basic query and resulting data.

SELECT dat, punch, badge
FROM TiaBdata...jtm4012
WHERE badge = @badge_num AND dat BETWEEN DateAdd(dd, ((DateDiff(dd, '20000102', GetDate()) -8) / 7)*7, '20000102') AND DateAdd(dd, ((DateDiff(dd, '20000101', GetDate()) +6) / 7)*7, '20000101')
ORDER BY dat, punch
2010-05-24 00:00:00.000                599         69975
2010-05-24 00:00:00.000                806         69975
2010-05-24 00:00:00.000                848         69975
2010-05-24 00:00:00.000                961         69975
2010-05-25 00:00:00.000                513         69975
2010-05-25 00:00:00.000                728         69975
2010-05-25 00:00:00.000                771         69975
2010-05-25 00:00:00.000                1107       69975
2010-05-26 00:00:00.000                481         69975
2010-05-26 00:00:00.000                798         69975
2010-05-26 00:00:00.000                850         69975
2010-05-26 00:00:00.000                1001       69975
2010-05-27 00:00:00.000                546         69975
2010-05-27 00:00:00.000                768         69975
2010-05-27 00:00:00.000                804         69975
2010-05-27 00:00:00.000               1047       69975
2010-05-28 00:00:00.000                 691         69975
2010-05-28 00:00:00.000                828         69975
2010-05-28 00:00:00.000                900         69975
2010-05-28 00:00:00.000                1081       69975
2010-06-01 00:00:00.000                566         69975
2010-06-01 00:00:00.000                805         69975
2010-06-01 00:00:00.000                883         69975
2010-06-01 00:00:00.000                1043       69975
2010-06-02 00:00:00.000                568         69975
The guys over at Experts Exchange helped me get a PIVOT query to make it look nice.
SELECT convert(char(11),dat) AS PunchDate, PunchTime1, PunchTime2, PunchTime3, PunchTime4, PunchTime5, PunchTime6, PunchTime7, PunchTime8
SELECT dat, convert(char(5),dateadd(n,punch,0),108) AS punch, 'PunchTime' + CAST(ROW_NUMBER() OVER (PARTITION BY dat ORDER BY punch) as varchar(5)) AS RowID
FROM TiaBdata...jtm4012
WHERE badge = @badge_num AND dat BETWEEN DateAdd(dd, ((DateDiff(dd, '20000102', GetDate()) -8) / 7)*7, '20000102') AND DateAdd(dd, ((DateDiff(dd, '20000101', GetDate()) +6) / 7)*7, '20000101')
) AS S
PIVOT (MAX(punch) FOR RowID IN ([PunchTime1], [PunchTime2], [PunchTime3], [PunchTime4], [PunchTime5], [PunchTime6], [PunchTime7], [PunchTime8])) AS P
But I’m having trouble getting the totals working because the number of punches are dynamic depending on how many breaks people take during the day.  I’ve standardized on 8 max as there are rarely more than that.  But mostly I can’t figure out how to handle the current day math because it’s going to be an odd number until they leave for the day and I want to show them how many hours they would have if they clocked out when they loaded the web page.
I’m not using any entities just a stored procedure to get the data from the linked server and use a structure to display the results.
I’m wondering if I should do more in the stored procedure or keep it simple and do more in the application.  I’m not a DBA or programmer so I can’t tell which would be more effective/efficient.
Basically the stumbling block is how to handle the current day calculations.  I was thinking it might be helpful to add an integer flag to the PIVOT query to tell how many non null values there are per row but I have no idea how to do that.
Thanks in advance for your time,

Hi David,

In your case I would prefer to retrieve more simple data from the database and build the logic within the application.
Because of the stress in the database with all these calculations and, since you are connecting to a linked server, when the logic is too complex you may get timeout (I work here with linked server and sometimes I get timeout even with just a join with 3-4 tables.).

You must think
(1) if the logic to get the result is really something that must come from the database (for instance when you have 2-3 tables and you need a result from a combination of these and the logic to get this result is really from the database - nobody needs to know the 3 tables and the calculations, just the result - for that you can use the combination of views and stored procedures) or
(2) if the complex calculation is a result that you want just because of a screen layout requirement (the user wants to see the information in a particular way), using search options and these things. So the rule is in the screen.

In your example you should leave some logic in the stored procedure and some other for the application.
Everything that is changeble (like the conditions) should be within the application logic.
For the DateAdd part within the WHERE, I would create a FUNCTION in the Database and use it in the Stored Procedure.

Also important, it's good to always check in the database the Execution Plan for the query. There you can see the performance and where the query can be improved.

You must divide the big problem into small problems (hehehe), even if  you end with all the logic in the database (no problem on that). Because when do you do it, you help the database too (because when you don't do, it will do it anyway in the background, and for every line, the database will make the calculations).

In my case, I had to do lots of times queries from a linked server and for most of them I use stored procedures and they return exacly the result I want.. so all the businees logic is inside them - the application doesn't need to know that I select data from 4 different tables.
But inside the stored procedure, I use more than one query, use variables and temporary tables for the calculations (no cursors please!)... it's better then trying to create one and only highlander query. :D  And it's still one Stored Procedure. (I've attached one example)

Now about this issue: You said that 8 columns is not fixed, and should be dinamic. Hum for that you need to include in the application yes. And you can't use TableRecords for that. You need to use the ListRecords and the columns would be an expression.

To be honest the subject of including business logic in store procedures or within the application is always a big question when we start to build an application. And of course the answer depends on how complex is the rule, preformance needs, how big is the table and how many data will be selected and specially how frequent this data is changed (tables that are constantly having inserts and updates are less benefited even when in Stored Procedures - indexes).

In this MSDN link you can read more about Stored Procedures
In this website you can see different comments that might help your decision.

For youR WHERE statement, when there're calculations, try to use variables too. So the database will calculate it once and keep the value in the variable.

"dat BETWEEN DateAdd(dd, ((DateDiff(dd, '20000102', GetDate()) -8) / 7)*7, '20000102') AND DateAdd(dd, ((DateDiff(dd, '20000101', GetDate()) +6) / 7)*7, '20000101')"

can be

SET @DAT1 = DateAdd(dd, ((DateDiff(dd, '20000102', GetDate()) -8) / 7)*7, '20000102')
SET @DAT2 = DateAdd(dd, ((DateDiff(dd, '20000101', GetDate()) +6) / 7)*7, '20000101')


WoW, thanks Carolina.  Lots of great info there, appreciate the insight.

Right now I'm sorta going with the simple query and trying to build the list in the application to have more control over the final dataset.

I like some of your concepts and will endeavor to incorporate them.

Thanks Again,