data rollup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

data rollup

Author

Topic

SteveBrown
Starting Member

3 Posts

Posted - 2002-02-08 : 15:34:28

here's what i got. a table that contains detail records. each record contains licensecode, reportnbr, transtype and licensenbr. i need to rollup the data by reportnbr. but here's the trick, the licenses are in ranges.
example:
reportnbr licensecode licensenbr transtype
123456 A001 1 V
123456 A001 2 V
123456 A001 3 S
123456 A001 4 V
123456 A001 5 V
so i need the data to look like this:
reportnbr licensecode StartNum EndNum transtype
123456 A001 1 2 V
123456 A001 3 3 S
123456 A001 4 5 V
this seems like it should be easy, but i'm just missing something here.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-08 : 15:48:03

SELECT reportnbr, licensecode, min(licensenbr) AS StartNum, max(licensenbr) AS EndNum, transtype
FROM myTable
GROUP BY reportnbr, licensecode, transtype

SteveBrown
Starting Member

3 Posts

Posted - 2002-02-08 : 16:00:33

i tried that. it only returns 2 rows instead of 3. it rolls up the 4 rows with status of "V" in to a single row.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-08 : 16:13:46

Is licensenbr unique?

SteveBrown
Starting Member

3 Posts

Posted - 2002-02-08 : 16:15:04

yes

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-09 : 10:44:08

Hmm. I'm struggling with this one a bit. On the assumption that licensenbr is contiguous for each (reportnbr, licensenbr), this should work, albeit slowly:


SELECT s.reportnbr, s.licensecode, startnum, MIN(endnum), s.transtype
FROM (
    SELECT a.reportnbr, a.licensecode, a.licensenbr startnum, a.transtype
    FROM det a
    LEFT JOIN det b ON a.reportnbr = b.reportnbr
      AND a.licensecode = b.licensecode AND a.transtype = b.transtype
      AND a.licensenbr - 1 = b.licensenbr
    WHERE b.licensenbr IS NULL) AS s
INNER JOIN (
    SELECT a.reportnbr, a.licensecode, a.licensenbr endnum, a.transtype
    FROM det a
    LEFT JOIN det b ON a.reportnbr = b.reportnbr
      AND a.licensecode = b.licensecode AND a.transtype = b.transtype
      AND a.licensenbr + 1 = b.licensenbr
    WHERE b.licensenbr IS NULL) AS e
  ON s.reportnbr = e.reportnbr
    AND s.licensecode = e.licensecode AND s.transtype = e.transtype
WHERE startnum <= endnum
GROUP BY s.reportnbr, s.licensecode, s.transtype, startnum

You may find it works better with NOT EXISTS instead of the LEFT JOINs:


SELECT s.reportnbr, s.licensecode, startnum, MIN(endnum), s.transtype
FROM (
    SELECT reportnbr, licensecode, licensenbr startnum, transtype
    FROM det a
    WHERE NOT EXISTS (
      SELECT * FROM det b
      WHERE a.reportnbr = b.reportnbr AND a.licensecode = b.licensecode
        AND a.transtype = b.transtype AND a.licensenbr - 1 = b.licensenbr)) s
INNER JOIN (
    SELECT reportnbr, licensecode, licensenbr endnum, transtype
    FROM det a
    WHERE NOT EXISTS (
      SELECT * FROM det b
      WHERE a.reportnbr = b.reportnbr AND a.licensecode = b.licensecode
        AND a.transtype = b.transtype AND a.licensenbr + 1 = b.licensenbr)) e
  ON s.reportnbr = e.reportnbr
    AND s.licensecode = e.licensecode AND s.transtype = e.transtype
WHERE startnum <= endnum
GROUP BY s.reportnbr, s.licensecode, s.transtype, startnum

Edited by - Arnold Fribble on 02/09/2002 12:14:47

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-09 : 11:43:35

Without the contiguous assumption:


SELECT reportnbr, licensecode, MIN(startnum) startnum, endnum, transtype
FROM (
  SELECT s.reportnbr, s.licensecode, s.licensenbr startnum,
    MAX(e.licensenbr) endnum, s.transtype
  FROM det s
  INNER JOIN det e ON s.reportnbr = e.reportnbr
    AND s.licensecode = e.licensecode
    AND s.transtype = e.transtype
    AND s.licensenbr <= e.licensenbr
  WHERE NOT EXISTS (
    SELECT *
    FROM det t
    WHERE t.reportnbr = s.reportnbr
      AND t.licensecode = s.licensecode
      AND t.licensenbr > s.licensenbr
      AND t.licensenbr < e.licensenbr
      AND t.transtype <> s.transtype)
  GROUP BY s.reportnbr, s.licensecode, s.licensenbr, s.transtype) a
GROUP BY reportnbr, licensecode, endnum, transtype

* Get all the possible compatible extrema
* Filter out any with intervening incompatible rows
* Find the last end for each start
* For those ends, find the first start

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-10 : 08:22:10

Hmm. Can't get away from this thread, it seems!

Rob, you were looking at pp 206-209 of your favorite book, yes?
I think it would come out like this for the contiguous case (ref. p207):


SELECT s.reportnbr, s.licensecode,
  s.licensenbr AS startnum,
  ISNULL(
    MIN(CASE WHEN e.licensenbr > s.licensenbr
               AND e.transtype != s.transtype THEN e.licensenbr
        ELSE NULL END) - 1,
    MAX(CASE WHEN e.licensenbr > s.licensenbr THEN e.licensenbr
        ELSE s.licensenbr END)) AS endnum,
  s.transtype
FROM det s
INNER JOIN det e ON s.reportnbr = e.reportnbr
  AND s.licensecode = e.licensecode
GROUP BY s.reportnbr, s.licensecode, s.licensenbr, s.transtype
HAVING
  ISNULL(
    MAX(CASE WHEN e.licensenbr < s.licensenbr
               AND e.transtype != s.transtype THEN e.licensenbr
        ELSE NULL END) + 1,
    MIN(CASE WHEN e.licensenbr < s.licensenbr THEN e.licensenbr
        ELSE s.licensenbr END)) = s.licensenbr

(If you have a copy of the book The Guru's Guide to T-SQL, you'll notice that the first HAVING clause has disappeared. That's because we want to keep single-row ranges. And yes, I changed the table aliases because I didn't think a and v particularly mnemonic.)

YMMV, but I'm finding that slower than my solution (2) probably because of a large amount of intermediate data. It's cool anyway, just because it does it with one join!

The non-contiguous case (ref. p208) comes out like this (or thereabouts):


SELECT s.reportnbr, s.licensecode, s.licensenbr AS startnum,
  e.licensenbr AS endnum, s.transtype
FROM det s
INNER JOIN det e ON s.reportnbr = e.reportnbr
  AND s.licensecode = e.licensecode AND s.licensenbr <= e.licensenbr
INNER JOIN det l ON s.reportnbr = l.reportnbr
  AND s.licensecode = l.licensecode
GROUP BY s.reportnbr, s.licensecode, s.licensenbr, e.licensenbr, s.transtype
HAVING
  SUM(
    CASE WHEN l.transtype = s.transtype THEN 0
         WHEN s.licensenbr <= l.licensenbr
          AND l.licensenbr <= e.licensenbr THEN 1
         ELSE 0 END) = 0
AND ISNULL(MIN(
        CASE WHEN l.licensenbr > e.licensenbr THEN 2*l.licensenbr +
                  CASE WHEN l.transtype<>s.transtype THEN 1 ELSE 0 END
             ELSE null END),1)%2 != 0
AND ISNULL(MAX(
        CASE WHEN l.licensenbr < s.licensenbr THEN 2*l.licensenbr +
                  CASE WHEN l.transtype<>s.transtype THEN 1 ELSE 0 END
             ELSE null END),1)%2 != 0

Although it's not well-explained in the book, the last two HAVING clauses are conjugating the transtype comparison into the bottom bit of the aggregate and then pulling it back out with the modulus. Hmm, smells like Celko to me!
(Book-readers note: In the first HAVING, it seems easier to me to fold the work done by the ABS multiplication into the CASE. And there's no point in the subtraction in the other two: just need to change one MIN to a MAX).

This goes really slow for me, but can be speeded up somewhat by the simple expedient of matching the endpoints' transtype in the join instead as part of the first HAVING clause.


SELECT s.reportnbr, s.licensecode, s.licensenbr AS startnum,
  e.licensenbr AS endnum, s.transtype
FROM det s
INNER JOIN det e ON s.reportnbr = e.reportnbr
  AND s.licensecode = e.licensecode AND s.licensenbr <= e.licensenbr
  AND s.transtype = e.transtype
INNER JOIN det l ON s.reportnbr = l.reportnbr
  AND s.licensecode = l.licensecode
GROUP BY s.reportnbr, s.licensecode, s.licensenbr, e.licensenbr, s.transtype
HAVING
  SUM(
    CASE WHEN l.transtype = s.transtype THEN 0
         WHEN s.licensenbr < l.licensenbr
          AND l.licensenbr < e.licensenbr THEN 1
         ELSE 0 END) = 0
AND ISNULL(MIN(
        CASE WHEN l.licensenbr > e.licensenbr THEN 2*l.licensenbr +
                  CASE WHEN l.transtype<>s.transtype THEN 1 ELSE 0 END
             ELSE null END),1)%2 != 0
AND ISNULL(MAX(
        CASE WHEN l.licensenbr < s.licensenbr THEN 2*l.licensenbr +
                  CASE WHEN l.transtype<>s.transtype THEN 1 ELSE 0 END
             ELSE null END),1)%2 != 0

But as I say, the performance characteristics will probably be affected by the data, so it's worth seeing what works best for you.

Edited by - Arnold Fribble on 02/10/2002 08:26:16

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-10 : 08:48:26

quote:
Rob, you were looking at pp 206-209 of your favorite book, yes?

Didn't quite get that far, I was looking at pg. 202. I did get a solution much like yours, but it broke on discontinuous runs and I simply couldn't (alright, didn't want to!) figure it out after that.

Damn you Arnold! Now I want to try this thing again! Back in a few, I have a weird idea I want to try...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources