Post reply

Comparing two rows in one table for changes in column

  • May 24, 2010 at 2:20 pm

    #1171754

    SELECT machine.MachineID,

    DATEADD(mi,t.Number,'20100522') AS SampleTime,

    CASE WHEN ABS(CHECKSUM(NEWID()))%2 = 0 THEN 'Off' ELSE 'On' END AS [State]

    FROM (SELECT 1 UNION ALL SELECT 2) machine(MachineID)

    CROSS JOIN Master.dbo.spt_Values t

    WHERE t.Type = 'P'

    AND t.Number BETWEEN 0 AND 10

    ORDER BY MachineID, SampleTime

    I'm still here - been on the road. I am trying to get my head around a couple of things and I know you can answer them in a heartbeat.

    First is CHECKSUM. Not included in my Murach book. Google says, "...available in SQL Server 2005 to natively create a unique expression, row or table for comparison or other application needs." NEWID() does kind of the same thing. Why the both of them used together?

    Second, and this one goes back to how values are inserted in 2005 and prior, I don't fully follow:

    SELECT 2,4,56, 'test data input listing' UNION ALL

    To me, UNION ALL is for joining two tables via a UNION. How or why or what's the rational behind using it at the end of insert statements?

    Third, this looks like some sort of shorthand between not only adding data to a table, but also using the added data at the same time:

    FROM (SELECT 1 UNION ALL SELECT 2) machine(MachineID)

    I have never seen this before. Where else and how else can you use this? Am I really reading this correctly that you are allowed to input data into a table at the same time you are retrieving from it in this usage fashion? I know you can obviously nest many statements, but somehow this one really catches me.

    So the other thing that I would ask of you is simple...

    2. Pass the knowledge forward to others

    So I guess I am dating myself here... I remember learning dBase before there was the public internet. All we had were public (and private) BBS. That and the early days of the internet, too, later on. I remember those days. I agree - share the knowledge. I used to be quite active on the boards in helping others. It actually helps to sharpen your own skills when you try to help another - at least that is what I found.

    Anyhow, thanks for all your help once again! With better understanding of the above, I can continue to digest your code.

    Dobermann

Viewing post 61 (of 60 total)

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