Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    46

    Unhappy Unanswered: Rozenshtein Method

    If you not familiar with it, the <a href="http://www.stephenforte.net/owdasblog/permalink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8">Rozenshtein Method</a> uses SQL to create a crosstab. The concept is a stroke of genius but I'm having trouble getting it to work on one of production databases.

    I successfully used the Northwind example explained at Stephen Forte's site (see the link above)...but no luck on my real world problems.

    I can get the date statements to resolve to 0 correctly, but when I try to aggregate the data - the statements are turning into 1 multiplying the aggregate data for each cell...which fills in the same data across the entire row.

    For example (the columns represent the time period),
    GROUP T1 T2 T3 T4
    group1 9 9 9 9
    group2 3 3 3 3
    group3 5 5 5 5

    My sql code is:
    SELECT dbo.tblHassBatch.ProdLine,
    COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(dd,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS Today,
    COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(ww,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Week],
    COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Month],
    COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-1)))) AS [Last Month]
    FROM dbo.tblHassBatch INNER JOIN
    dbo.tblHassUUT ON dbo.tblHassBatch.BatchID = dbo.tblHassUUT.BatchID
    GROUP BY dbo.tblHassBatch.ProdLine

    Am I overlooking something here? I'm pulling my hair out b/c if this works it's really going to provide a great solution for a project I'm working on...but I can't seem to figure it out.

    Any help is appreciated.

    Thanks,

    Alex8675

  2. #2
    Join Date
    Dec 2003
    Posts
    46

    Talking Figured it out

    The COUNT aggregate was messing the whole thing up...even if the TRUE/FALSE (0/1) test worked properly, I was counting the result - no matter whether it was true or false.

    The corrected code is below:


    SELECT dbo.tblHassBatch.ProdLine,
    sum((1-ABS(SIGN(datediff(dd,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS Today,
    sum((1-ABS(SIGN(datediff(ww,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Week],
    sum((1-ABS(SIGN(datediff(ww,dbo.tblHassBatch.StartTime,ge tdate())-1)))) AS [Last Week],
    sum((1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Month],
    sum((1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-1)))) AS [Last Month]
    FROM dbo.tblHassBatch INNER JOIN
    dbo.tblHassUUT ON dbo.tblHassBatch.BatchID = dbo.tblHassUUT.BatchID
    GROUP BY dbo.tblHassBatch.ProdLine

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So, Rozenshtein wants to claim credit for discovering that 1-ABS(SIGN(X-Y)) determines whether X = Y? That discovery is significant enough to be called a "Method"?

    That is one of the most pretentious things I have ever heard. Has he applied for a patent on the wheel yet?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would imagine that he doesn't called it his method (although to be honest i have not read his book)

    it is probably just a label that other people have applied to the application of the math to the problem of producing a crosstab report

    you gotta admit, it's a really sexy way of producing a crosstab report
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    3
    Someone else dubbed it the Rozenshtein method after reading about it in one of his books.

    It is elegant, but a CASE statement would be better for writing code that isn't so cryptic to those that don't know about the Rozenshtein Method.


  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rudy, I guess I just don't see any fundamental difference between that method and the CASE method mentioned in Books Online. Now, if he had a simple and generic method of creating dynamic crosstabs - that's the Holy Grail of SQL...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does that mean I get to say I invented Data Warehousing?

    Because we've all been doing it before they came up with the jargon....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, 'cause I'm sure I was doing it before you.

    I am the true father of Data Warehousing. Bill Inmon is, in reality, merely my illegitimate son, and Ralph Kimbal was a neighbor kid who used to come over and bother me while I was tinkering around in my data garage.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by blindman
    No, 'cause I'm sure I was doing it before you.

    I am the true father of Data Warehousing. Bill Inmon is, in reality, merely my illegitimate son, and Ralph Kimbal was a neighbor kid who used to come over and bother me while I was tinkering around in my data garage.
    Blindman, your memory fails you! That wasn't your data garage, that was your data outhouse, where you worked with streaming media and core dumps!

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dudes, i started data warehousing in 1987 with DB2 and QMF

    relational database for user-driven ad-hoc querying of production data

    y'all are free to go and look up when inmon started



    blindman, crosstab is the holy grail of sql? pshaw, i say, 'tis a piffle

    all the so-called industrial strength relational databases can't do it, but access can!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dudes AND (especially) Dudettes:

    BDAM warehouses.....

    Rudy...did you save them as QUERIES, FORMS and PROCS?

    DB2 V2.3?

    Ah the days....

    Can't wait for V8 though...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, queries, not sure about the forms and procs, though

    do you remember qmf's DRAW command?

    here's an example of my work from those early days: http://r937.com/datadict.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    Buffalo, NY
    Posts
    1
    Quote Originally Posted by blindman
    So, Rozenshtein wants to claim credit for discovering that 1-ABS(SIGN(X-Y)) determines whether X = Y? That discovery is significant enough to be called a "Method"?

    That is one of the most pretentious things I have ever heard. Has he applied for a patent on the wheel yet?

    No, of course Dr. Rozenshtein doesn't call it "The Rozenshtein Method"...
    I attended his seminar back in like '97, and the only label he gave this technique was "Conditional Function Expressions". In that seminar, he expanded it to include other operators (<, >, <=, <>, etc) and datatypes like dates and strings, which was fairly cool.

    Besides, with the inclusion of the CASE expression in ANSI standard SQL, the whole "1-abs(sign(x-y))" thing is obsolete anyway... You can just do:
    (CASE when X=Y then 1 else 0 END)

    ...which is SO much easier to read and comprehend.

    PHoran
    Sr. Architect
    VCI Springfield, Mass

Posting Permissions

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