Query: How can I get this result?

[Yannick Turgeon]

Hello all,

I tryed to simplify the problem as much as possible. I'll start with the
DDL:

----------------------------------
CREATE TABLE #MyTable(
NoID INT,
Type CHAR,
DateTransaction DATETIME
)

INSERT INTO #MyTable (NoID, Type, DateTransaction)
SELECT 1 AS NoID, 'A' AS Type, '2004-01-01' AS DateTransaction UNION ALL
SELECT 2, 'C', '2004-01-01' UNION ALL
SELECT 3, 'B', '2004-01-01' UNION ALL
SELECT 4, 'C', '2004-01-02' UNION ALL
SELECT 5, 'B', '2004-01-02' UNION ALL
SELECT 6, 'C', '2004-01-02' UNION ALL
SELECT 7, 'A', '2004-01-03' UNION ALL
SELECT 8, 'B', '2004-01-03' UNION ALL
SELECT 9, 'A', '2004-01-03' UNION ALL
SELECT 10, 'C', '2004-01-03' UNION ALL
SELECT 11, 'B', '2004-01-03'
----------------------------------

What I want is all the same Type which, for a same DateTransaction, as a
different Type inserte beetween them when data is sorted by DateTransaction
and NoID. In this case I would like:

Type DateTransaction
------ -----------------
C 2004-01-02 /* B is between two C (NoID = 5) */
A 2004-01-03 /* B is between tow A (NoID = 8) */
B 2004-01-03 /* A and C are between two B (NoID = 9 and
10) */

All of these have for the corresponding date at least one transaction with a
different type between.

In the real situation NoID is an autoincrement field, the PK. And the
DateTransaction hasn't any time, just a round date.

Any suggestion?

Thanks for your time.

Yannick

[David Portas]

This is what I think you are asking for:

SELECT DISTINCT M2.type, M2.datetransaction
FROM #MyTable AS M1
JOIN
(SELECT type, datetransaction,
MIN(noid) AS minid, MAX(noid) AS maxid
FROM #MyTable
GROUP BY type, datetransaction
HAVING COUNT(*)>1) AS M2
ON M1.type <> M2.type
AND M1.datetransaction = M2.datetransaction
AND M1.noid > M2.minid
AND M1.noid < M2.maxid

However, I think it's unwise to base your sequence on an IDENTITY column
(NoId). The point of an IDENTITY column is that it's a system-generated
surrogate key which isn't supposed to have a meaning in your data model. For
multiple row inserts you can't gurarantee in what order the values will be
assigned so the result you get may not be what you expected. In short, it
appears that you may be missing the necessary information to define the
correct sequence of rows.

--
David Portas
SQL Server MVP
--

[John Gilson]

"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:T%********************@news20.bellglobal.com. ..

Hello all,

I tryed to simplify the problem as much as possible. I'll start with the
DDL:

----------------------------------
CREATE TABLE #MyTable(
NoID INT,
Type CHAR,
DateTransaction DATETIME
)

INSERT INTO #MyTable (NoID, Type, DateTransaction)
SELECT 1 AS NoID, 'A' AS Type, '2004-01-01' AS DateTransaction UNION ALL
SELECT 2, 'C', '2004-01-01' UNION ALL
SELECT 3, 'B', '2004-01-01' UNION ALL
SELECT 4, 'C', '2004-01-02' UNION ALL
SELECT 5, 'B', '2004-01-02' UNION ALL
SELECT 6, 'C', '2004-01-02' UNION ALL
SELECT 7, 'A', '2004-01-03' UNION ALL
SELECT 8, 'B', '2004-01-03' UNION ALL
SELECT 9, 'A', '2004-01-03' UNION ALL
SELECT 10, 'C', '2004-01-03' UNION ALL
SELECT 11, 'B', '2004-01-03'
----------------------------------

What I want is all the same Type which, for a same DateTransaction, as a
different Type inserte beetween them when data is sorted by DateTransaction
and NoID. In this case I would like:

Type DateTransaction
------ -----------------
C 2004-01-02 /* B is between two C (NoID = 5) */
A 2004-01-03 /* B is between tow A (NoID = 8) */
B 2004-01-03 /* A and C are between two B (NoID = 9 and
10) */

All of these have for the corresponding date at least one transaction with a
different type between.

In the real situation NoID is an autoincrement field, the PK. And the
DateTransaction hasn't any time, just a round date.

Any suggestion?

Thanks for your time.

Yannick

TYPE is a Standard SQL key word and should be avoided when naming.

SELECT DISTINCT T1."Type", T1.DateTransaction
FROM #MyTable AS T1
INNER JOIN
#MyTable AS T2
ON T1.DateTransaction = T2.DateTransaction AND
T1."Type" = T2."Type" AND
T1.NoID < T2.NoID - 1 AND
EXISTS (SELECT *
FROM #MyTable AS T3
WHERE T1.DateTransaction = T3.DateTransaction AND
T3."Type" <> T1."Type" AND
T3.NoID > T1.NoID AND
T3.NoID < T2.NoID)
ORDER BY T1.DateTransaction, T1."Type"

Type DateTransaction
C 2004-01-02 00:00:00.000
A 2004-01-03 00:00:00.000
B 2004-01-03 00:00:00.000

--
JAG

[David Portas]

This is what I think you are asking for:

SELECT DISTINCT M2.type, M2.datetransaction
FROM #MyTable AS M1
JOIN
(SELECT type, datetransaction,
MIN(noid) AS minid, MAX(noid) AS maxid
FROM #MyTable
GROUP BY type, datetransaction
HAVING COUNT(*)>1) AS M2
ON M1.type <> M2.type
AND M1.datetransaction = M2.datetransaction
AND M1.noid > M2.minid
AND M1.noid < M2.maxid

However, I think it's unwise to base your sequence on an IDENTITY column
(NoId). The point of an IDENTITY column is that it's a system-generated
surrogate key which isn't supposed to have a meaning in your data model. For
multiple row inserts you can't gurarantee in what order the values will be
assigned so the result you get may not be what you expected. In short, it
appears that you may be missing the necessary information to define the
correct sequence of rows.

--
David Portas
SQL Server MVP
--

[John Gilson]

"David Portas" <RE****************************@acm.org> wrote in message
news:f5********************@giganews.com...

This is what I think you are asking for:

SELECT DISTINCT M2.type, M2.datetransaction
FROM #MyTable AS M1
JOIN
(SELECT type, datetransaction,
MIN(noid) AS minid, MAX(noid) AS maxid
FROM #MyTable
GROUP BY type, datetransaction
HAVING COUNT(*)>1) AS M2
ON M1.type <> M2.type
AND M1.datetransaction = M2.datetransaction
AND M1.noid > M2.minid
AND M1.noid < M2.maxid

However, I think it's unwise to base your sequence on an IDENTITY column
(NoId). The point of an IDENTITY column is that it's a system-generated
surrogate key which isn't supposed to have a meaning in your data model. For
multiple row inserts you can't gurarantee in what order the values will be
assigned so the result you get may not be what you expected. In short, it
appears that you may be missing the necessary information to define the
correct sequence of rows.

--
David Portas
SQL Server MVP
--

David, like the GROUP BY approach. Following that through, we can simplify
your query to

SELECT "Type", DateTransaction
FROM #MyTable
GROUP BY "Type", DateTransaction
HAVING MIN(NoID) < MAX(NoID) - 1

--
JAG

[David Portas]

John,

Yannick said he wanted Types with a *different* Type inserted beetween them.
Your simplified GROUP BY will give us any group with more than 3 rows of the
*same* type. Also I think you've assumed there will be no gaps in the NoId
sequence - which is another reason why one might not want to rely on NoId to
drive this query.

--
David Portas
SQL Server MVP
--

[Yannick Turgeon]

David,

Your solution worked perfectly. Thanks for that. You were right too saying
that I was looking for a *different* type and that you cannot assumed there
was no gaps between NoId.

Yes our NoId has a meaning in our data and programming code which is "the
inserted order". The insertion is always one at a time. Sort of Invoice #.
But, just to express of I feel since I work here (4 years now), this table
has a much more important design problem: there is a field (PreviousBalance)
which contains data dependent on "previous" rows. This is a pain since I'm
here and, actually, this design flaw is at the source of the problem
conserning this thread and some other before. The NoID I can easily leave
with but the other one... it's tougher!

Thanks again for your time to both of you.

Yannick
"David Portas" <RE****************************@acm.org> wrote in message
news:va********************@giganews.com...

John,

Yannick said he wanted Types with a *different* Type inserted beetween them. Your simplified GROUP BY will give us any group with more than 3 rows of the *same* type. Also I think you've assumed there will be no gaps in the NoId
sequence - which is another reason why one might not want to rely on NoId to drive this query.

--
David Portas
SQL Server MVP
--

[John Gilson]

"David Portas" <RE****************************@acm.org> wrote in message
news:va********************@giganews.com...

John,

Yannick said he wanted Types with a *different* Type inserted beetween them.
Your simplified GROUP BY will give us any group with more than 3 rows of the
*same* type. Also I think you've assumed there will be no gaps in the NoId
sequence - which is another reason why one might not want to rely on NoId to
drive this query.
Yes, sorry, you're absolutely right David. I suppose I'd have to do the
following with this approach to make it work:

SELECT "Type", DateTransaction
FROM #MyTable AS T1
GROUP BY "Type", DateTransaction
HAVING MIN(NoID) < MAX(NoID) - 1 AND
EXISTS (SELECT *
FROM #MyTable AS T2
WHERE T2.DateTransaction = T1.DateTransaction AND
T2."Type" <> T1."Type" AND
T2.NoID > MIN(T1.NoID) AND
T2.NoID < MAX(T1.NoID))

--
JAG
--
David Portas
SQL Server MVP
--