Help needed write this complex SQL query

Question

Help needed write this complex SQL query

sangshettysandeep

Grasshopper

Points: 10
More actions
May 23, 2011 at 9:14 am

#145457

Hi Friends,
My task looking complex,I dont know how to loop through the previous and next rows
I am new to SQL .I need small help from you.
I have Table like below
ItemIdScanningStationIDDirectionIsCorrect
AG185106243203943 1 (Right)
AG185106243203943 0 (Right)
AG185106243203943 1 (Right)
AG185106243203943 0 (Right)
AG185106243203943 1 (Right)
AG185106243203943 0 (Right)
AG185106594329393 1 (Right)
AG185106594329393 0 (Right)
AG185106594329393 1 (Wrong)
AG185106243203943 1 (Right)
AG185106243203943 0 (Right)
AG185106243203943 1 (Wrong)
AG185106243203943 1 (Wrong)
AG185106243203943 1 (Wrong)
AG185106243203943 1 (Right)
AG185106243203943 0 (Right)
AG185106243203943 0 (Wrong)
AG185106243203943 0 (Wrong)
AG185106243203943 1 (Right)
AG185106243203943 0 (Right)
AG185106243203943 1 (Right)
AG185106594329393 0 (Wrong)
Here for a particular Item we need to have the scanning details.
Wrong entries will be considered in these below situations
* Logged 'IN' on One scanning station and 'OUT' from other station
* Logged 'IN' on One Floor not logged 'OUT' in that floor
* Consecutive Log 'IN'
* Consecutive Log 'OUT'
So for the above all wrong entries I want insert 'Iscorrect' column with '0'
For the correct entries I want insert 'Is correct column with '1'
How can I make this doable .
Please help me .
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

nigel. SSChampion Points: 11636 More actions · Answer 1

First problem I can see is that you have no column that defines the ordering of the rows in your data, without this there is no way to determine what might be the 'previous' or 'next' record.

Also please see the link in my sig for details of how to post questions in a format that has the greatest chance of quick and accurate answers.

Nigel
How to post questions

WayneS SSC Guru Points: 95460 More actions · Answer 2

What constitutes logging "In" or "Out"? The Direction column? If so, which value maps to which?

or do you just want to change the current IsCorrect column to be either 1 or 0?

if the latter:

UPDATE <mytable>

SET IsCorrect = CASE WHEN IsCorrect = '(Right)' THEN 1

ELSE 0 END;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 3

From PM from the OP:

In the Direction column for IN its '1' for OUT its '0'.
Yes, I want to update the current IsCorrect column with 1(right) and 0(wrong)
Below is the background information about the task.
* When the employee logs IN with his item(laptop..etc) in any floor(scanning station) that needs to be logged OUT from that floor (correct scenario).
* If he is logging IN on One floor(scanning station) and not logged OUT while he is moving to another floor(or while he is leaving the office for that day) ,in this case we will have only IN entry (1) in the table .So this 1 will become wrong entry (because no '0').
* If he is logging IN on One floor(scanning station) and not logged OUT while he is moving to another floor .And trying to logging IN on that floor.In this case we will have Two consecutive IN entry (1) in the table .So the first 1 will become wrong entry (because no '0').
Even if he not logged OUT from that floor also, then the next 1 will also become wrong entry.
* If he is logging IN on One floor(scanning station) and not logged OUT while he is moving to another floor .And trying to logging OUT on that floor .In this case these two entries (1 and 0) will become wrong.
* If he is logging IN on One floor(scanning station) and not logged OUT while he is leaving the office for that day .And trying to logging IN for the next day .In this case we will have Two consecutive IN entry (1) in the table .So the first 1 will become wrong entry (because no '0') .And for the next one also if we don't have OUT(0) entry then that will also become wrong.
Here Iscorrect column is not a physical column on the table.I am trying use this for temp table/view.
This column will column will specify whether the current row is wrong or right.
If its correct then it will become '1' ,for wrong it will become '0'

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 4

So, do we need to recalculate the IsCorrect column with all of the rules that you provided, or just use it's current right/wrong value to update? If the latter, the update statement I supplied earlier should work (after you handle for it already being a 1/0....).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2