Membership Timeline Spanning

Span example:
--M--
--Rx--
Needs to b converted to this:
--M--|--M & Rx--|--Rx--
/* What current base data displays
MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
-- -- -- -- --
27 20050101 20050412 Y N
27 20050201 20050813 N Y
27 20050603 99991231 Y N
*/
/* Should be converted as this?
MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
-- -- -- -- --
27 20050101 20050131 Y N
27 20050201 20050412 Y Y
27 20050413 20050602 N Y
27 20050603 20050813 Y Y
27 20050814 99991231 Y N
*/
DDL to create the table and then some sample data
CREATE TABLE [dbo].[ODS_BLK_Member] (
[BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID, MEM_ID,
CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY, MEM_COUNTY,
MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, [GROUP], SUBGROUP,
COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST,
MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND,
MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC,
PLN_CAT_PRF, FILE_IDENTIFIER)
SELECT
'123','012001','1','1','ODS','19820706',
'19348','US','FIL','F','NON','1','1'
,'5','20060306','99991231','1','1','1','
A','B','B','1','B','Y','Y','Y','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','1'
,'5','20050101','20050912','1','1','2','
A','B','B','1','B','N','Y','Y','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','1'
,'5','20050310','20051120','1','1','2','
A','B','B','1','B','Y','Y','N','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3','
3','7','20050101','20050412','1','1','27
','A','B','B','1','B','N','Y','Y','Y
','2001','2001','12','12','M'
UNION
SELECT
'123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3','
3','7','20050201','20050813','1','1','27
','A','B','B','1','B','Y','Y','N','Y
','2001','2001','12','12','M'
UNION
SELECT
'123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3','
3','7','20050603','99991231','1','1','27
','A','B','B','1','B','N','Y','Y','Y
','2001','2001','12','12','M'
UNION
SELECT
'123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','2'
,'6','20060101','99991231','1','1','4','
A','B','B','1','B','N','Y','Y','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','2'
,'6','20060301','99991231','1','1','4','
A','B','B','1','B','Y','Y','N','Y','
2001','2001','12','12','M'
If Itzak is out there I would appreciate your magic.Forgot to mention this is a Medical and Pharmacy benefits table.
"FredG" wrote:

> Span example:
> --M--
> --Rx--
> Needs to b converted to this:
> --M--|--M & Rx--|--Rx--
> /* What current base data displays
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050412 Y N
> 27 20050201 20050813 N Y
> 27 20050603 99991231 Y N
> */
>
> /* Should be converted as this?
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050131 Y N
> 27 20050201 20050412 Y Y
> 27 20050413 20050602 N Y
> 27 20050603 20050813 Y Y
> 27 20050814 99991231 Y N
> */
> DDL to create the table and then some sample data
> CREATE TABLE [dbo].[ODS_BLK_Member] (
> [BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID, MEM_ID
,
> CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY, MEM_COUNTY
,
> MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, [GROUP], SUBGROUP,
> COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST,
> MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND,
> MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC,
> PLN_CAT_PRF, FILE_IDENTIFIER)
> SELECT
> '123','012001','1','1','ODS','19820706',
'19348','US','FIL','F','NON','1','
1','5','20060306','99991231','1','1','1'
,'A','B','B','1','B','Y','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050101','20050912','1','1','2'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050310','20051120','1','1','2'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050101','20050412','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050201','20050813','1','1','
27','A','B','B','1','B','Y','Y','N',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050603','99991231','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060101','99991231','1','1','4'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060301','99991231','1','1','4'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> If Itzak is out there I would appreciate your magic.
>
>|||Fred,
This is an example for a task I'd probably end up using a cursor for. I
might be wrong, but experience with similar problems and intuition tells me
that set-based solutions using existing features in the product (both 2000
and 2005) are going to be more expensive than the cursor solution because of
excessive I/O. Using features from ANSI SQL:2003 that were not yet
implemented in SQL Server (particularly OVER clause with ORDER BY for
aggregations) there might be a set-based solution that would run faster than
the cursor. But that's for future versions of SQL Server... :-)
Anyhow, I'd declare a cursor based on the following query:
SELECT MEM_ID, COV_BEGIN_DT AS DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
0 AS BEGIN_END
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_BEGIN_DT
UNION ALL
SELECT MEM_ID, COV_END_DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
1
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_END_DT
ORDER BY MEM_ID, DT, BEGIN_END;
The idea is to produce a series of events in chronological order: events
that increase or decrease the count of benefit conditions.
Here's what the cursor's query produces:
MEM_ID DT MED_CHANGE PHRM_CHANGE BEGIN_END
-- -- -- -- --
1 20060306 1 1 0
1 99991231 -1 -1 1
2 20050101 1 0 0
2 20050310 0 1 0
2 20050912 -1 0 1
2 20051120 0 -1 1
27 20050101 1 0 0
27 20050201 0 1 0
27 20050412 -1 0 1
27 20050603 1 0 0
27 20050813 0 -1 1
27 99991231 -1 0 1
4 20060101 1 0 0
4 20060301 0 1 0
4 99991231 -1 -1 1
The cursor should scan the events in chronological order and produce a new
period whenever there's a change in the state of benefits.
I just scribbled the following code as an example of how such cursor code
might look like. But note that I didn't bother to test it thoroughly, and as
with most code snippets that contain more than 0 characters, this one most
probably has bugs. So please use this just to get the general idea of the
logic, but make sure you examine, understand, and test it thoroughly, and
make the required revisions before putting it into production.
SET NOCOUNT ON;
DECLARE
@.MEM_ID AS VARCHAR(22),
@.DT AS CHAR(8),
@.MED_CHANGE AS INT,
@.PHRM_CHANGE AS INT,
@.BEGIN_END AS INT,
@.COV_BEGIN_DT AS CHAR(8),
@.MED_CNT AS INT,
@.PHRM_CNT AS INT,
@.PRV_MEM_ID AS VARCHAR(22),
@.PRV_MED_CNT AS INT,
@.PRV_PHRM_CNT AS INT;
DECLARE @.Results TABLE
(
MEM_ID VARCHAR(22) NOT NULL,
COV_BEGIN_DT CHAR(8) NOT NULL,
COV_END_DT CHAR(8) NOT NULL,
MED_BEN_IND CHAR(1) NOT NULL,
PHRM_BEN_IND CHAR(1) NOT NULL
--PRIMARY KEY(MEM_ID, COV_BEGIN_DT)
);
DECLARE CEvents CURSOR FAST_FORWARD FOR
SELECT MEM_ID, COV_BEGIN_DT AS DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
0 AS BEGIN_END
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_BEGIN_DT
UNION ALL
SELECT MEM_ID, COV_END_DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
1
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_END_DT
ORDER BY MEM_ID, DT, BEGIN_END;
OPEN CEvents;
SELECT
@.PRV_MEM_ID = NULL,
@.PRV_MED_CNT = 0,
@.PRV_PHRM_CNT = 0;
FETCH NEXT FROM CEvents
INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- New MEM_ID or new period; init variables
IF @.MEM_ID <> @.PRV_MEM_ID
OR @.PRV_MEM_ID IS NULL
OR @.PRV_MED_CNT + @.PRV_PHRM_CNT = 0
SELECT
@.MED_CNT = @.MED_CHANGE,
@.PHRM_CNT = @.PHRM_CHANGE,
@.COV_BEGIN_DT = @.DT;
ELSE
-- New period
BEGIN
SELECT
@.MED_CNT = @.MED_CNT + @.MED_CHANGE,
@.PHRM_CNT = @.PHRM_CNT + @.PHRM_CHANGE;
-- Change in MED or PHRM benefit state
-- means close of existing period and open of new one
IF SIGN(@.MED_CNT) <> SIGN(@.PRV_MED_CNT)
OR SIGN(@.PHRM_CNT) <> SIGN(@.PRV_PHRM_CNT)
BEGIN
INSERT INTO @.Results
(MEM_ID, COV_BEGIN_DT, COV_END_DT, MED_BEN_IND, PHRM_BEN_IND)
VALUES(
@.MEM_ID,
@.COV_BEGIN_DT,
CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END-1, @.DT), 112),
CASE WHEN @.PRV_MED_CNT > 0 THEN 'Y' ELSE 'N' END,
CASE WHEN @.PRV_PHRM_CNT > 0 THEN 'Y' ELSE 'N' END);
IF @.MED_CNT + @.PHRM_CNT > 0
SET @.COV_BEGIN_DT =
CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END, @.DT), 112);
END
END
SELECT
@.PRV_MEM_ID = @.MEM_ID,
@.PRV_MED_CNT = @.MED_CNT,
@.PRV_PHRM_CNT = @.PHRM_CNT
FETCH NEXT FROM CEvents
INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
END
CLOSE CEvents;
DEALLOCATE CEvents;
SELECT * FROM @.Results;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:67C2CD4A-D517-418A-A11B-BE53773B831A@.microsoft.com...
> Span example:
> --M--
> --Rx--
> Needs to b converted to this:
> --M--|--M & Rx--|--Rx--
> /* What current base data displays
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050412 Y N
> 27 20050201 20050813 N Y
> 27 20050603 99991231 Y N
> */
>
> /* Should be converted as this?
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050131 Y N
> 27 20050201 20050412 Y Y
> 27 20050413 20050602 N Y
> 27 20050603 20050813 Y Y
> 27 20050814 99991231 Y N
> */
> DDL to create the table and then some sample data
> CREATE TABLE [dbo].[ODS_BLK_Member] (
> [BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID,
> MEM_ID,
> CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY,
> MEM_COUNTY,
> MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, [GROUP], SUBGROUP,
> COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST,
> MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND,
> MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC,
> PLN_CAT_PRF, FILE_IDENTIFIER)
> SELECT
> '123','012001','1','1','ODS','19820706',
'19348','US','FIL','F','NON','1','
1','5','20060306','99991231','1','1','1'
,'A','B','B','1','B','Y','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050101','20050912','1','1','2'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050310','20051120','1','1','2'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050101','20050412','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050201','20050813','1','1','
27','A','B','B','1','B','Y','Y','N',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050603','99991231','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060101','99991231','1','1','4'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060301','99991231','1','1','4'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> If Itzak is out there I would appreciate your magic.
>
>|||Hi Itzik, First, my applogizes for butchering your first name, I feel ashame
d.
Secondly, thank you very much for taking the time to respond.
When I first read that you have steered away from set based solution to
cursor based I was a suprised. All of your writings clearly state to use
cursors as a last resort. In any event, you are the MASTER of T-SQL and I
will adher to your suggestions.
The table in question contains 5 million rows, using a cursor may just take
a very long time. However, I will give it a go at it.
If you see Kalen Delaney and Andrew Kelly tell them I said hello.
Alfredo Giotti
"Itzik Ben-Gan" wrote:

> Fred,
> This is an example for a task I'd probably end up using a cursor for. I
> might be wrong, but experience with similar problems and intuition tells m
e
> that set-based solutions using existing features in the product (both 2000
> and 2005) are going to be more expensive than the cursor solution because
of
> excessive I/O. Using features from ANSI SQL:2003 that were not yet
> implemented in SQL Server (particularly OVER clause with ORDER BY for
> aggregations) there might be a set-based solution that would run faster th
an
> the cursor. But that's for future versions of SQL Server... :-)
> Anyhow, I'd declare a cursor based on the following query:
> SELECT MEM_ID, COV_BEGIN_DT AS DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
> 0 AS BEGIN_END
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_BEGIN_DT
> UNION ALL
> SELECT MEM_ID, COV_END_DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> 1
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_END_DT
> ORDER BY MEM_ID, DT, BEGIN_END;
> The idea is to produce a series of events in chronological order: events
> that increase or decrease the count of benefit conditions.
> Here's what the cursor's query produces:
> MEM_ID DT MED_CHANGE PHRM_CHANGE BEGIN_END
> -- -- -- -- --
> 1 20060306 1 1 0
> 1 99991231 -1 -1 1
> 2 20050101 1 0 0
> 2 20050310 0 1 0
> 2 20050912 -1 0 1
> 2 20051120 0 -1 1
> 27 20050101 1 0 0
> 27 20050201 0 1 0
> 27 20050412 -1 0 1
> 27 20050603 1 0 0
> 27 20050813 0 -1 1
> 27 99991231 -1 0 1
> 4 20060101 1 0 0
> 4 20060301 0 1 0
> 4 99991231 -1 -1 1
> The cursor should scan the events in chronological order and produce a new
> period whenever there's a change in the state of benefits.
> I just scribbled the following code as an example of how such cursor code
> might look like. But note that I didn't bother to test it thoroughly, and
as
> with most code snippets that contain more than 0 characters, this one most
> probably has bugs. So please use this just to get the general idea of the
> logic, but make sure you examine, understand, and test it thoroughly, and
> make the required revisions before putting it into production.
> SET NOCOUNT ON;
> DECLARE
> @.MEM_ID AS VARCHAR(22),
> @.DT AS CHAR(8),
> @.MED_CHANGE AS INT,
> @.PHRM_CHANGE AS INT,
> @.BEGIN_END AS INT,
> @.COV_BEGIN_DT AS CHAR(8),
> @.MED_CNT AS INT,
> @.PHRM_CNT AS INT,
> @.PRV_MEM_ID AS VARCHAR(22),
> @.PRV_MED_CNT AS INT,
> @.PRV_PHRM_CNT AS INT;
> DECLARE @.Results TABLE
> (
> MEM_ID VARCHAR(22) NOT NULL,
> COV_BEGIN_DT CHAR(8) NOT NULL,
> COV_END_DT CHAR(8) NOT NULL,
> MED_BEN_IND CHAR(1) NOT NULL,
> PHRM_BEN_IND CHAR(1) NOT NULL
> --PRIMARY KEY(MEM_ID, COV_BEGIN_DT)
> );
> DECLARE CEvents CURSOR FAST_FORWARD FOR
> SELECT MEM_ID, COV_BEGIN_DT AS DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
> 0 AS BEGIN_END
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_BEGIN_DT
> UNION ALL
> SELECT MEM_ID, COV_END_DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> 1
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_END_DT
> ORDER BY MEM_ID, DT, BEGIN_END;
> OPEN CEvents;
> SELECT
> @.PRV_MEM_ID = NULL,
> @.PRV_MED_CNT = 0,
> @.PRV_PHRM_CNT = 0;
> FETCH NEXT FROM CEvents
> INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- New MEM_ID or new period; init variables
> IF @.MEM_ID <> @.PRV_MEM_ID
> OR @.PRV_MEM_ID IS NULL
> OR @.PRV_MED_CNT + @.PRV_PHRM_CNT = 0
> SELECT
> @.MED_CNT = @.MED_CHANGE,
> @.PHRM_CNT = @.PHRM_CHANGE,
> @.COV_BEGIN_DT = @.DT;
> ELSE
> -- New period
> BEGIN
> SELECT
> @.MED_CNT = @.MED_CNT + @.MED_CHANGE,
> @.PHRM_CNT = @.PHRM_CNT + @.PHRM_CHANGE;
> -- Change in MED or PHRM benefit state
> -- means close of existing period and open of new one
> IF SIGN(@.MED_CNT) <> SIGN(@.PRV_MED_CNT)
> OR SIGN(@.PHRM_CNT) <> SIGN(@.PRV_PHRM_CNT)
> BEGIN
> INSERT INTO @.Results
> (MEM_ID, COV_BEGIN_DT, COV_END_DT, MED_BEN_IND, PHRM_BEN_IND)
> VALUES(
> @.MEM_ID,
> @.COV_BEGIN_DT,
> CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END-1, @.DT), 112),
> CASE WHEN @.PRV_MED_CNT > 0 THEN 'Y' ELSE 'N' END,
> CASE WHEN @.PRV_PHRM_CNT > 0 THEN 'Y' ELSE 'N' END);
> IF @.MED_CNT + @.PHRM_CNT > 0
> SET @.COV_BEGIN_DT =
> CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END, @.DT), 112);
> END
> END
> SELECT
> @.PRV_MEM_ID = @.MEM_ID,
> @.PRV_MED_CNT = @.MED_CNT,
> @.PRV_PHRM_CNT = @.PHRM_CNT
> FETCH NEXT FROM CEvents
> INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
> END
> CLOSE CEvents;
> DEALLOCATE CEvents;
> SELECT * FROM @.Results;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:67C2CD4A-D517-418A-A11B-BE53773B831A@.microsoft.com...
>
>|||Hi Fred,
No need to apologize. :-)
It's true that for the most part, set-based solutions are faster than
cursor-based ones. There's a lot of overhead involved with the
record-by-record manipulation of the cursor. However, there are types of
problems where using cursors, your code ends up incurring much less I/O than
the set-solution. Remember that a cursor can rely on sorted data while set
manipulation cannot.
Take running aggregates as an example; the set-based solutions have an
O(N^2) complexity since portions of the data need to be rescanned in order
to calculate the running aggregates. The cursor on the other hand performs a
single scan of the data.
In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
option). It is really brilliant, and I wonder if the designers of the
feature themselves knew how profound it is. I believe this option to be the
bridge between cursors and sets; sort of the holy grail of SQL. :-)
You can perform ordered calculations without forcing any particular order of
the output, and without involving the cursor overhead. The technology
already exists in the SQL Server 2005 engine; it's just that OVER + ORDER BY
with aggregates was not implemented yet, and this combination is where the
real power lies.
Anyhow, today, set-based solutions to some problems (including some
solutions to temporal problems) have complexities that end up being much
more expensive than cursor solutions.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:9228A601-9A06-43B8-8AA1-7CE4AA5B91C1@.microsoft.com...
> Hi Itzik, First, my applogizes for butchering your first name, I feel
> ashamed.
> Secondly, thank you very much for taking the time to respond.
> When I first read that you have steered away from set based solution to
> cursor based I was a suprised. All of your writings clearly state to use
> cursors as a last resort. In any event, you are the MASTER of T-SQL and I
> will adher to your suggestions.
> The table in question contains 5 million rows, using a cursor may just
> take
> a very long time. However, I will give it a go at it.
> If you see Kalen Delaney and Andrew Kelly tell them I said hello.
> Alfredo Giotti
> "Itzik Ben-Gan" wrote:
>|||"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net|||Here's another method, you'll need SQL Server 2005
and a calendar table as per
http://www.aspfaq.com/show.asp?id=2519
Ensure the calendar covers all dates in your data.
(Do not put '99991231' into the calendar!)
I don't know how the performance of this compares
to the cursor based solution already posted.
WITH Daily(MEM_ID,dt,MED_PHRM)
AS
(SELECT m.MEM_ID,
t.dt,
MAX(CASE m.MED_BEN_IND WHEN 'Y' THEN 1 ELSE 0 END) +
MAX(CASE m.PHRM_BEN_IND WHEN 'Y' THEN 2 ELSE 0 END)
FROM Calendar t
INNER JOIN ODS_BLK_Member m ON CAST(t.dt AS DATETIME) BETWEEN
CAST(m.COV_BEGIN_DT AS DATETIME)
AND
CAST(m.COV_END_DT AS DATETIME)
GROUP BY t.dt,m.MEM_ID),
RankedDaily(MEM_ID,dt,MED_PHRM,RankDiff)
AS
(SELECT MEM_ID,
dt,
MED_PHRM,
DATEADD(day,-RANK() OVER (PARTITION BY MEM_ID,MED_PHRM ORDER BY
dt),dt)
FROM Daily)
SELECT MEM_ID,
CONVERT(CHAR(8),MIN(dt),112) AS COV_BEGIN_DT,
CASE WHEN MAX(dt) = (SELECT MAX(dt) FROM Calendar)
THEN '99991231'
ELSE CONVERT(CHAR(8),MAX(dt),112)
END AS COV_END_DT,
CASE WHEN MED_PHRM % 2 <> 0 THEN 'Y' ELSE 'N' END AS
MED_BEN_IND,
CASE WHEN MED_PHRM / 2 <> 0 THEN 'Y' ELSE 'N' END AS
PHRM_BEN_IND
FROM RankedDaily
GROUP BY MEM_ID,MED_PHRM,RankDiff
ORDER BY 1,2;
You can create a simplified calendar using this
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '20040101'
WHILE @.dt < '20080101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END|||"Truly, you have A dizzying intellect." 8-)
Doesn't change the way I feel about OVER + ORDER BY, though.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:u8%23pGNSXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> "Itzik Ben-Gan" writes
> To quote Bob Dylan:
> 'I would not feel so alone if everyone where getting stoned':)
> Yes I agree with you in principal.The 'real' paradign shift has
> little to do with the clr and everything to do with exploding
> the perverted myth of the exclusivity of'set based' constructs.
> The idea one can legitimately think in terms of rows without being
> labelled an sql Jodus has arrived.But calling this windowing a
> 'profound' kind of insight and bestowing on the designers the aura
> of 'brilliance' would be a mistake.It is at best an example of
> 'better late than never'.Calling this state of affairs profound
> would surely overshadow the accountability that the commericial
> database world should be held to.The fact that this mindset change
> has taken almost 30 years should be seen as appalling.Neo-cons of
> the industry had hijacked sense with sql creationism and marketing.
> WMD was replaced with client/server and a tiered approach.A theory
> was misapplied to a retrival mechanism and unapplied to a design
> mechanism.An approach that vendors marketted that allowed them to
> hide both their intellectual and creative shortcomings.Their db
> failures made for the 'client'.And now the clr in the db has replaced
> the client.And of course the dreaded cursor.This demanded regime
> change and the field was bankrupted for 30 years.For this we are to
> praise Ceasar?I think not.
> It is interesting to look at the fanfare that vendors are using
> to usher in this new paradign.In their documentation Oracle refers
> to their analytic functions in windows as an example of
> 'data densification'.This phrase is supposed to illustrate the
> flip side of the Group By.It was obviously borrowed from the idea
> of pacification,right out of the Pentagon.This is the best they could
> come up with?Any army of engineers berefit of language and concepts.
> Not to be out done,MS in its highly touted BOL offers the next best
> thing - absolutely Nothing!No explanations,no history no seqways.
> The functions are thrown around like so much spaghetti on a wall.
> If you write about concepts someone may quote you.MS needn't worry
> now.Least I be accused of favortism,IBM was too busy pleasing its
> shareholders to write anything intelligible.
> Finally,to your point about MS leaving out a large chunk of analytic
> material this was obviously not an oversight but just insurance
> that anything done with sql-99 could most definitly be easily ported
> to the competition.Less is more.Please!If they weren't sure of
> what they were doing they could have at least looked at Oracle
> which is probably about 8 years ahead.Or even looked at RAC to see what
> you and I are really talking about :)
> Interested readers maybe surprised that many of the ideas in sql
> analytics can be found in the SAS (Statistical Analysis System) Data
> Step...introduced about 20 years ago!Many of the Oracle extensions
> (First/Last) can also be found here.MySql allows mixing of variables
> and columns in a SELECT.Most of the analytics can be easily simulated
> in a single SELECT.And of course little RAC, way ahead of its time:)
> Some musing from:
> www.rac4sql.net
>
>|||We really are of the same mind! :)
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uhToaBfXGHA.1476@.TK2MSFTNGP03.phx.gbl...
> "Truly, you have A dizzying intellect." 8-)
> Doesn't change the way I feel about OVER + ORDER BY, though.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "Steve Dassin" <rac4sqlnospam@.net> wrote in message
> news:u8%23pGNSXGHA.3936@.TK2MSFTNGP05.phx.gbl...
>