Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: Help needed on wrting SQL query

    Hi Friends,

    I am new to SQL 2005,I need small help in writing a query.

    I have a table as below

    Col1(varchar) Col2 (bit)

    Ramu 1

    Raju 0

    Ravi 1

    Mohan 1

    Sham 0

    Sayed 1

    From the above table I need to only those rows which are not in sequence.
    Means if the previous row is '1' ,the next row should be '0'.If it is not '0'then I need to get that row.If it is '1' then no need to get that row.

    Can any one please help me in this .

    Thanks in Advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no such thing as the "next" row without reference to a sequencing column, which you don't have

    you need to rethink your approach
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    5
    Can I do this using Row_number function.

    Regards,
    Sandeep

  4. #4
    Join Date
    May 2011
    Posts
    5
    Can I do this using Row_Number Function?

    Regards,
    Sandeep

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, because ROW_NUMBER requires an ORDER BY clause, and you gots no column that you can sort on (like i said earlier)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You will first have to give an answer to r937's question, otherwise there is no solution to your problem.

    I gave it a go, by arbitrary ordering your data by Col1 Asc
    Code:
    Col1	Col2	Nr	Seq
    Mohan	1	1	1	
    Raju	0	2	0
    Ramu	1	3	1
    Ravi	1	4	0
    Sayed	1	5	1
    Sham	0	6	0
    Nr is the RowNumber.
    Seq is the expected sequence (1 0 1 0 1 ...). I arbitrary took the first Col2 with Nr = 1 as the start of the sequence. One could easily take 0 as the start of the sequence (0 1 0 1 0 ...) with a totally different solution.

    To fit in the sequence, the value of all the odd record Nrs should be 1 and the value of all the even record Nrs should be 0. The ones you want are "the others".
    Code:
    WITH CTE
    AS
    (SELECT Col1, 
    	Col2, 
    	ROW_NUMBER() OVER (ORDER BY Col1 ASC) AS Nr
    FROM #DaTable
    )
    SELECT CTE.*
    FROM (SELECT CTE.Col2 as StartSequence From CTE WHERE CTE.Nr = 1) as T,
    	CTE
    WHERE (CTE.Nr % 2 = 1 AND CTE.col2 <> T.StartSequence)
    	OR
    	(CTE.Nr % 2 = 0 AND CTE.col2 = T.StartSequence)
    The nonconformist is Ravi.

    I had to make two arbitrary choices. You will have to redefine your problem to make it deterministic.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    May 2011
    Posts
    5

    Help needed to write SQL query

    Sorry Friends(Wim & r937) in replying lately.
    And thanks for ur reply.

    Wim ,I have a column to use order by column 1.e ItemId and Datetime(not shown in the table) also.

    I have attached Table in the Image File Table.bmp.
    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
    Attached Thumbnails Attached Thumbnails Table.bmp  

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sandeepsangshetty View Post
    How can I make this doable .
    i have no idea

    your problem doesn't look very much like your original post, does it

    there's no "floor" column, so your problem statement is incomplete already

    if you want us to look at this any further, please show a proper table layout

    (in sql server, this is called scripting the table)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2011
    Posts
    5

    Help needed on wrting SQL query

    Hi r937,
    Thanks for ur reply.

    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'

    I tried to attach .sql files but not succeeded.
    Below are attached files for table structure in Word document
    MainTable
    ScanningStation
    Employee

    Note : Here Floor is Scanning Station.
    Attached Files Attached Files

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I think you can solve all your different cases by adapting my initial solution:

    - Find out how all sequences should Start (I presume that will be a 'log IN') and End (I presume that will be a 'log OUT')

    - Adapt the
    ROW_NUMBER() OVER (ORDER BY Col1 ASC)
    Do some research on ROW_NUMBER() OVER, you will have add PARTITION BY to group per user and floor.

    Then find anomalies within each group:
    (1. first record in a group should not be a log OUT, ) see anomaly 3.
    (2. last record in a group should not be a log IN) see anomaly 4.
    3. check for anomalies by doing modulo 2 calculation, odd rowNr: must be log IN, even RowNr must be log OUT
    4. each group should have an even number of records

    If you get stuck halfway, show us what you got so far. And add a few CREATE TABLE and INSERT commands, so we can easily reproduce your model and data for testing.
    Last edited by Wim; 05-24-11 at 13:12.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •