Thursday, May 30, 2013

[T-SQL] Flagging Records within a table of sequenced numbers

[T-SQL] Flagging Records within a table of sequenced numbers

Flagging Records within a table of sequenced numbers

Posted: 29 May 2013 06:18 AM PDT

I have a problem I'm hoping someone can help me with. I have a table of sequenced numbers. In this table I need to look at two columns; am_sw and cc_sw. If the am_sw is flagged, I need to check the next 7 sequential records for that number for a cc_sw that is flagged. If i don't find a cc_sw flagged within the next 7 records, I wan't to pull this number out for later.Below is some sample code to give an idea of what I am working with. In the code below I would want to pull number 201200001 as it has the pattern of 1 am_sw followed by at least 7 records without a cc_sw. The second number would not be flagged as it does not meet this pattern. Any ideas? I'm banging my head on my desk right now and it doesn't seem to be helping. :crazy:[code="sql"]DECLARE @numbers TABLE (number INT, am_sw INT, cc_sw INT, sequence_number INT)INSERT @numbers(number, am_sw, cc_sw,sequence_number)SELECT 201200001,1,0,1 UNION ALL SELECT 201200001,0,1,2 UNION ALLSELECT 201200001,0,0,3 UNION ALLSELECT 201200001,1,0,4 UNION ALLSELECT 201200001,0,0,5 UNION ALLSELECT 201200001,0,0,6 UNION ALLSELECT 201200001,0,0,7 UNION ALLSELECT 201200001,0,0,8 UNION ALLSELECT 201200001,0,0,9 UNION ALLSELECT 201200001,0,0,10 UNION ALLSELECT 201200001,0,0,11 UNION ALLSELECT 201200001,0,1,12 UNION ALLSELECT 201200002,1,0,1 UNION ALLSELECT 201200002,1,0,2 UNION ALLSELECT 201200002,0,0,3 UNION ALLSELECT 201200002,0,0,4 UNION ALLSELECT 201200002,0,0,5 UNION ALLSELECT 201200002,0,1,6 UNION ALLSELECT 201200002,0,0,7 select * FROM @numbers[/code]

using row_number() over partition by to get datediff by row

Posted: 29 May 2013 03:42 AM PDT

I have a list of events that occur for a patient. I need to count the number of events that occur by patient by type but ONLY if the timestamp on the events are greater than 60 min. apart. (i.e. I need to ignore duplicates that occur within one hour of each other.) Initially, I approached it by getting the MIN and comparing each row to it to see if it was more than 60 minutes apart but I quickly realized that was dumb. I need to compare each consecutive row against the previous row to see if it is within 60 minutes. I am able to get the rowId but I'm not quite sure how to proceed. Should I create a temp table and fill it as I go? Here's the code to create a sample table and fill it will data.CREATE TABLE [dbo].[la_test_endcall]( [pmd_patient_id] [bigint] NOT NULL, [endcallcd] [int] NOT NULL, [eventid] [bigint] NOT NULL, [createdat] [datetime] NOT NULL) ON [PRIMARY]INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999995, '2013-04-16 12:02:14.000')INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999996, '2013-04-16 17:02:14.000')INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128137, 1001, 9999997, '2013-04-16 17:12:14.000')INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5093634, '2013-04-16 17:17:14.000')INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1001, 5099268, '2013-04-26 13:48:53.000')INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5121175, '2013-05-16 16:43:51.000')INSERT [dbo].[la_test_endcall] ([pmd_patient_id], [endcallcd], [eventid], [createdat]) VALUES (100128138, 1002, 5133281,'2013-05-23 17:53:46.000')Here's how I'm getting a RowID by patient by type:SELECT pmd_patient_Id,EndCallCd,EventId,CreatedAt,ROW_NUMBER() OVER (PARTITION BY PMD_Patient_ID, endcallcd ORDER BY CreatedAt) AS RowIdFROM la_test_endcallORDER BY pmd_patient_id,endcallcd, CreatedAt

Process flow not functioning properly in stored proc

Posted: 29 May 2013 07:38 AM PDT

I have a stored procedure which processes clock events from employees times for compliance with California's labor laws. It essentially identifies any violations and updates the record with the violation which will then be printed on a report and paid by our company.Anyway, There are some identified errors on clock events that are not being caught by the code. It used to run correctly until SP2 was applied to our SQL2K8 installation, 64 bit on Windows Server 2008R2 64bit. But since then, it doesn't appear to run the code snippet below properly. I say appears not to run because as far as I know it did prior.What I found is in the code snippet below, that code isn't being executed when it should. During debugging, I put in some print statements, and it began working. I kept removing the print statements until it stopped working again to find out where the issue was/is. I have it down to one line, the line where the print statement is commented out. If I leave it commented out, the code below it won't execute, if I uncomment it, it runs fine. Totally bizarre.I've checked for hidden control characters in a hex editor, tried deleting and retyping those lines, checked for hanging/missing Begin….End block statements, etc. I can't figure it out.I also realize there is probably some better ways to do this processing, however, the business rules are written in such a way that i have to process record by record as I have compare to the prior shift and check break times between shifts, etc. :-)... Else --Different Date, so first check for no break condition, update record, --then reset hold variables Begin If @ShiftHours >= '05:00:00' OR (@ShiftHours >= '05:00:00' AND @RecsProcessed >= @RecCnt) [highlight=#ffff11] --print ''[/highlight] Begin If @HoldHoursWorked > '05:01:00' Begin --update the entries for the current hold record Update stage_EmployeeCheckInOut Set ViolationType = ViolationType + 'No Meal Period, ', ViolationPay = ViolationPay + 1, Violation = 'Y' Where RecordID = @HoldRecID End End --at this point, this variable should only hold splitshift violations if applicable. If @strViolation <> '' Begin Update stage_EmployeeCheckInOut Set ViolationType = ViolationType + @strViolation, ViolationPay = ViolationPay + @intViolationCnt, Violation = 'Y' Where RecordID = @HoldRecID End --reset shift hours since we are on a different date for the employee Set @ShiftHours = @HoursWorked Set @BreakTime = '00:00:00' --get record count for this day Select @RecCnt = COUNT(*) from stage_EmployeeCheckInOut Where SchoolID = @SchoolID AND EmplID = @EmplID AND SSN = @SSN AND ProcareEmployeeID = @ProcareEmployeeID AND dbo.GetDateOnly(CheckIn) = dbo.GetDateOnly(@CheckIn) Set @RecsProcessed = 1 End Thanks,Pat

T-SQL help

Posted: 29 May 2013 04:49 AM PDT

I want to get email alert if the following changes happen on the table in the database:1. Table deleted2. Column added/deleted3. Column datatype/datalength changesPlease advise. Thanks.

Parsing a variable for a where clause

Posted: 29 May 2013 01:35 AM PDT

I have a variable that gets set by a user on a web form, and the user can set that value to something along the lines of '2K + 1A3B'. I need to break this up for my where clause, and filter the query on each part of the string. For example, if the variable equals 2K + 1A3B, I want to a where clause similar to:LIKE '%2K%' OR '%1A%' OR '%3B%'I know this doesn't work, but is there an alternative?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog