June 26, 2008 at 10:25 am
We have a client that has approximately 150 employees that check in with us each day, 3 times a day, to ensure their safety.
Our thinking is to have 3 separate tables. Using the employees name as KEY
Table 1. Would have the names of the people checking in and columns that would collect the time and any comments.
Table 2. Would be written with a Stored Procedure when (Table 1 is written)that would copy the information taken in the first table next to the second table in the next available set of fields/cells. Now Table 1 is available to be overwritten for the next check in.
Table 3. Would be written each night adding all the records from Table 2 to Table 3.
Being new to SQL, I have done some research, but I am not sure which command to use. Insert seems to add new rows, so maybe UPDATE?
Any help on what I should be educating myself about would be appreciated.
June 26, 2008 at 7:35 pm
Why would you want to do it this way? Why not just write records into one table?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 27, 2008 at 8:15 am
The information is being gathered by a program that will write to the SQL database. For programming that script, it is much easier to have all DATABASE saves go to the same table and same fields. Our thinking is that we can then manipulate the information with SQL procs. This would allow us in SQL to address 7 days a week rather than trying to do the programming in our Decision Tree Software.
Thanks for your interest.
June 27, 2008 at 2:10 pm
dwagnon (6/27/2008)
The information is being gathered by a program that will write to the SQL database. For programming that script, it is much easier to have all DATABASE saves go to the same table and same fields. Our thinking is that we can then manipulate the information with SQL procs. This would allow us in SQL to address 7 days a week rather than trying to do the programming in our Decision Tree Software.
Umm, ... this answer does not seem to have anything to do with my question.
You started with: ...have 3 different tables..
I asked: Why 3 tables? Why not just one (specifically, your Table #3)?
You answered: it is much easier for all database saves to go to the same table.
... ???
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 28, 2008 at 11:35 am
We have a client that has approximately 150 employees that check in with us each day, 3 times a day, to ensure their safety
Strictly an assumption on my part but it seems like you want to check if an employee who has checked in at the start of the day, checks in at prescribed intervals. If this is a correct assumption you can do this and accumulate your data over an infinite time period by the proper design of a single table. For instance the table can have a date time column with a default value for current time using the GetDate() function so that each time your script indicates the employee has checked in that column is completed by SQL rather than in your script. Then you can set up a job to run periodically to select those employees who have checked in at the start of the day but have not checked in within the allowable time period, thus alerting whom ever is monitoring the system of a suspect safety problem. Or am I being too simplistic? If my assumption follows along the line of what you desire to do may I suggest hiring on a consulting basis an individual versed in SQL Server.
July 10, 2008 at 8:04 am
I am sorry to be so slow in responding. The reason we need at least 2 tables is for reporting. I think we have better explained our scenerio below. Any help would be greatly appreciated. We think we are close, but maybe not.
We answer for a “Lone Worker Check-In Program”. There are approximately 150 employees. Some are required to check-in every 2-4 hours. Others are required to check-in if they leave the office or plant to go out on location. If we have not heard from one of the required persons within the specified time frame (2 hours after office hours and 4 hours during office hours), we are to contact the person or their supervisor. Every 24 hours we are to report the previous day’s activity for each employee checking in to our client.
The data is collected by an enhanced SQL software and saved to SQL. Our thoughts have been to have 2 tables: Activity – has name, required/not, area, status, timestamp, call initiated by – for each check-in all columns except name are updated – this table is used to determine if the last check-in is within the appropriate time frame; Daily – has all of the above columns plus area, status, timestamp, call initiated by for up to 10 possible check-ins – for each checkin-in area, status, timestamp, and call initiated by are appended in the “next available” columns – this table is used to report to client every 24 hours.
We have written multiple UPDATE statements that work independently.
CREATE PROC stor_checkinA
AS
UPDATE Daily
SET Type=Activity.Type, AArea=Activity.Area, AStatus=Activity.Status, ACheckIn=Activity.CheckIn
FROM Activity
WHERE (Activity.Who=Daily.Who)
CREATE PROC stor_checkinB
AS
UPDATE Daily
SET Type=Activity.Type, BArea=Activity.Area, BStatus=Activity.Status, BCheckIn=Activity.CheckIn
FROM Activity
WHERE (Activity.Who=Daily.Who)
Etc.
When we attempt to apply conditions to locate the “next available” columns, we get errors. We believe we should be able to apply conditions with IF..ELSE or CASE but we have been unable to write something that works.
We have even attempted to add a column with a value to avoid IS NULL/NOT NULL and still do not get the desired result. This PROC attempts to do all IF instead of only the first true IF.
ALTER PROC stor_who
@Who AS char (25)
AS
SELECT Type, Area, Status, CheckIn
FROM Activity
WHERE @Who=Who
IF (SELECT ritea FROM Daily)=1 EXEC stor_checkinA
ELSE
IF (SELECT riteb FROM Daily)=1 EXEC stor_checkinB
ELSE EXEC stor_checkinC
July 10, 2008 at 10:44 am
I am still not getting this. I write to and report from the same tables all of the time, why do you think that you need seperate tables for reporting?
These "next avaialable" columns are not clear at all. I am not sure what you are trying to do, but it sounds very un-normalized. Could you please supply your tables definitions? Also please see this articale about how to get better results from these forums: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 10, 2008 at 12:06 pm
OK, let us assume we are now using one table. We may have up to 10 checkins per day per person. We need to begin the first checkin for that person at the first set of columns (status, time, comment are columns for each checkin) We have been unable to write to "next available" set of columns.
The tables have no formulas on any column.
AStatus = char (25) allows nulls,
ATime = datetime (8) allows nulls,
AComment = char (25) allows nulls,
BStatus = char (25) allows nulls, etc for up to 10 sets
We believe we need an IF..ELSE..IF environment but we either get errors on the table name or it tries to do all IFs.
Can you tell us if IF..ELSE..IF or CASE would be better for our selecting conditions?
July 10, 2008 at 12:17 pm
Please see Jaustin's reply - she and I work together.
July 10, 2008 at 12:47 pm
OK, I think I've got where you are going with this and Barry is absolutely correct. What you are trying to do can, and should, be done in one table. From what you've posted, you are going to have one table that holds up to 10 sets of Status, Time, Comments attributes all for the same Employee in the form of:
AStatus ATime AComments BStatus BTime BComments....up to 10
Is this correct?
If so, what you've done is violated first normal form. You are basically trying to store spreadsheet like data in a table. This is going to come around and bite you in the long run....trust me. You need to create a normalized (to 3rd normal form) data model that will work with your solution. Here's a good link on normalization basics. http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html.
For starters, you're going to at least need an Employee table, a checkin schedule table and a checkin table. It is important to get your data model correct on this before moving forward. This is especially true if you plan on writing reports off of this data. Your reporting queries will be a God awful mess if you don't normalize your data.
July 10, 2008 at 12:49 pm
Ah, I never finished one of my thoughts in my previous post. Instead of having 10 sets of columns, your table should only have one Status, Time, and Comment column and each Employee should have up to 10 rows per day in that table. Make sense?
July 10, 2008 at 1:30 pm
Our reporting is to provide our client a copy of the checkin table. There will be no queries of the checkin table. The table will be rebuilt each 24 hour period.
You are correct in your understanding of our situation. We believe we have the three tables you mentioned.
I will continue to digest the normal information you directed me toward. Are we correct in thinking that the IF..ELSE..IF statement is the correct syntax for conditional posting of data?
July 10, 2008 at 1:47 pm
Are we correct in thinking that the IF..ELSE..IF statement is the correct syntax for conditional posting of data?
No. Once you've normalized your tables, you won't need to worry about any of this nonsense. You just add a row in your table to represent the checkin activity. Here's an example of what we're trying to say:
SET NOCOUNT ON
DECLARE @Employee TABLE (EmpID int, EmpName varchar(20))
INSERT INTO @Employee
SELECT 1, 'John Rowan' UNION ALL
SELECT 2, 'jaustin' UNION ALL
SELECT 3, 'dwagnon '
DECLARE @YourTable TABLE (EmpID int, AStatus int, ATime datetime, AComments varchar(35), BStatus int, BTime datetime, BComments varchar(255))
INSERT INTO @YourTable (EmpID, AStatus, ATime, AComments)
SELECT 1, 0, GETDATE(), 'First checkin' UNION ALL
SELECT 2, 0, GETDATE(), 'First checkin' UNION ALL
SELECT 3, 0, GETDATE(), 'First checkin'
SELECT * FROM @YourTable
UPDATE @YourTable
SET BStatus = 0,
BTime = GETDATE(),
BComments = 'Second checkin - done w/ update statement'
WHERE EmpID = 1
UPDATE @YourTable
SET BStatus = 0,
BTime = GETDATE(),
BComments = 'Second checkin - done w/ update statement'
WHERE EmpID = 2
UPDATE @YourTable
SET BStatus = 0,
BTime = GETDATE(),
BComments = 'Second checkin - done w/ update statement'
WHERE EmpID = 3
SELECT * FROM @YourTable
DECLARE @NormalizedTable TABLE (EmpID int, Status int, Time datetime, Comments varchar(35))
--first checkin
INSERT INTO @NormalizedTable (EmpID, Status, Time, Comments)
SELECT 1, 0, GETDATE(), 'First checkin' UNION ALL
SELECT 2, 0, GETDATE(), 'First checkin' UNION ALL
SELECT 3, 0, GETDATE(), 'First checkin'
SELECT * FROM @NormalizedTable
--second checkin
INSERT INTO @NormalizedTable (EmpID, Status, Time, Comments)
SELECT 1, 0, GETDATE(), 'Second checkin' UNION ALL
SELECT 2, 0, GETDATE(), 'Second checkin' UNION ALL
SELECT 3, 0, GETDATE(), 'Second checkin'
SELECT e.EmpName,
t.Status,
t.Time,
t.Comments
FROM @NormalizedTable t
INNER JOIN @Employee e
ON e.EmpID = t.EmpID
ORDER BY e.EmpID
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply