Clean up a table & save to another table

I'm trying to filter and update the table AllAgreements (below) by
moving parts of each row into appropriate tables and deleting redundant
rows. This table is just a scratch table used while importing old data
into a new DB.
The situation: A company supplies credits to its customers (other
companies [construction] called Developers) that the customers
(Developers) can use to reduce any bills sent to them by the company.
Each time the credits are supplied they are referred to as a Credit
Agreement. The developers can transfer those credits, or parts of those
credits, to other developers. When a transfer is made they are creating
a new agreement. The Agreement identifier, in correspondence, uses a
user constructed identifier (ID #). The company keeps the original
credit agreement identifier & just adds a sequential letter suffix
representing each iteration of a new agreement off the original (because
the original agreement has one cut-off date that is enforced for all
subsequent agreements that use credits from the original credit agreement).
E.g.:
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
10,'TIF1 MSSH 001A','HARRY',10000.00
11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
12,'TIF1 MSSH 001B','JOE',10000.00
I need to generate a new row in the table Transactions (below) for both
the debit on the original credit agreement and the transfer/credit for
the new credit agreement.
/* BEGIN DDL */
set nocount on
CREATE TABLE AllAgreements ( -- scratch table - no PK
ca_id INTEGER NOT NULL UNIQUE,
"ID #" VARCHAR(20) NOT NULL,
Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
issued_date DATETIME NOT NULL,
amt decimal(9,2) NOT NULL
)
CREATE TABLE Transactions (
ca_id INTEGER NOT NULL REFERENCES AllAgreements,
developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
transaction_type INTEGER NOT NULL
CHECK (transaction_type BETWEEN 1 AND 5),
applied_date DATETIME NOT NULL,
amount DECIMAL(9,2) NOT NULL,
CONSTRAINT PK_Transactions
PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
)
INSERT INTO AllAgreements
VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
INSERT INTO AllAgreements
VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
INSERT INTO AllAgreements
VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
INSERT INTO AllAgreements
VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
INSERT INTO AllAgreements
VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
INSERT INTO AllAgreements
VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
INSERT INTO AllAgreements
VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
INSERT INTO AllAgreements
VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
INSERT INTO AllAgreements
VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
INSERT INTO AllAgreements
VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
INSERT INTO AllAgreements
VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
INSERT INTO AllAgreements
VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
/* desired results in table AllAgreements (I'll remove the Amt column
after the clean up & the values are in the Transactions table. Didn't
show the date in order to display row on one line).
ca_id ID # Developer amt
1 FIF2 GRNBR 001 LUCY $55,000.00
3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
6 TIF1 ACSPA 001 WILLY $30,000.00
7 TIF1 ACSPA 002 WILLY $45,000.00
8 TIF1 MSSH 001 FRED $25,000.00
10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
*/
/* desired results in table Transactions:
(1 = Original credit agreement; 2 = transfer of CR agreement)
E.g.: below the 1st 2 LUCY lines show the original credit
(trans_type=1) and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001"
caused by the transfer to JUDY (line 3). The JUDY line's ca_id, 3,
points to the new credit agreement "FIF2 GRNBR 001A" row in the
AllAgreements table.
ca_id developer transaction_type applied_date amount
-- -- -- -- --
1 LUCY 1 20050115 55000.00
1 LUCY 2 20050122 -20000.00
3 JUDY 2 20050122 20000.00
4 SUSIE 2 20050122 10000.00
3 JUDY 2 20050122 -10000.00
6 WILLY 1 20050211 30000.00
7 WILLY 1 20050211 45000.00
8 FRED 1 20050212 25000.00
8 FRED 2 20050212 -10000.00
10 HARRY 2 20050212 10000.00
10 HARRY 2 20050225 -100000.00
12 JOE 2 20050225 10000.00
*/
DROP TABLE AllAgreements, Transactions
/* END */
QUESTION: What append command will move the amounts to the Transactions
table w/ the correct ca_id value.
Thanks for your help.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)Hi
See inline:
"MGFoster" <mgf00@.earthlink.net> wrote in message
news:8waoe.562$HM.259@.newsread1.news.pas.earthlink.net...
> I'm trying to filter and update the table AllAgreements (below) by moving
> parts of each row into appropriate tables and deleting redundant rows.
> This table is just a scratch table used while importing old data into a
> new DB.
> The situation: A company supplies credits to its customers (other
> companies [construction] called Developers) that the customers
> (Developers) can use to reduce any bills sent to them by the company. Each
> time the credits are supplied they are referred to as a Credit Agreement.
> The developers can transfer those credits, or parts of those credits, to
> other developers. When a transfer is made they are creating a new
> agreement. The Agreement identifier, in correspondence, uses a user
> constructed identifier (ID #). The company keeps the original credit
> agreement identifier & just adds a sequential letter suffix representing
> each iteration of a new agreement off the original (because the original
> agreement has one cut-off date that is enforced for all subsequent
> agreements that use credits from the original credit agreement).
> E.g.:
> 1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
> 2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
> 3,'FIF2 GRNBR 001A','JUDY',20000.00
> 5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
> 4,'FIF2 GRNBR 001B','SUSIE',10000.00
> 6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
> 7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
> 8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
> 9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
> 10,'TIF1 MSSH 001A','HARRY',10000.00
> 11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
> 12,'TIF1 MSSH 001B','JOE',10000.00
> I need to generate a new row in the table Transactions (below) for both
> the debit on the original credit agreement and the transfer/credit for the
> new credit agreement.
> /* BEGIN DDL */
> set nocount on
> CREATE TABLE AllAgreements ( -- scratch table - no PK
> ca_id INTEGER NOT NULL UNIQUE,
> "ID #" VARCHAR(20) NOT NULL,
> Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> issued_date DATETIME NOT NULL,
> amt decimal(9,2) NOT NULL
> )
> CREATE TABLE Transactions (
> ca_id INTEGER NOT NULL REFERENCES AllAgreements,
> developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> transaction_type INTEGER NOT NULL
> CHECK (transaction_type BETWEEN 1 AND 5),
> applied_date DATETIME NOT NULL,
> amount DECIMAL(9,2) NOT NULL,
> CONSTRAINT PK_Transactions
> PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
> )
> INSERT INTO AllAgreements
> VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
> INSERT INTO AllAgreements
> VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
> INSERT INTO AllAgreements
> VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
> INSERT INTO AllAgreements
> VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
> INSERT INTO AllAgreements
> VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
> INSERT INTO AllAgreements
> VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
> INSERT INTO AllAgreements
> VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
> INSERT INTO AllAgreements
> VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
> INSERT INTO AllAgreements
> VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
> INSERT INTO AllAgreements
> VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
> INSERT INTO AllAgreements
> VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
> INSERT INTO AllAgreements
> VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
> /* desired results in table AllAgreements (I'll remove the Amt column
> after the clean up & the values are in the Transactions table. Didn't
> show the date in order to display row on one line).
> ca_id ID # Developer amt
> 1 FIF2 GRNBR 001 LUCY $55,000.00
> 3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
> 4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
> 6 TIF1 ACSPA 001 WILLY $30,000.00
> 7 TIF1 ACSPA 002 WILLY $45,000.00
> 8 TIF1 MSSH 001 FRED $25,000.00
> 10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
> 12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
> */
This seems to be
SELECT A.ca_id,A.[ID #],A.Developer,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.Amt FROM AllAgreements A
WHERE A.Amt > 0
order by A.ca_id

> /* desired results in table Transactions:
> (1 = Original credit agreement; 2 = transfer of CR agreement)
> E.g.: below the 1st 2 LUCY lines show the original credit (trans_type=1)
> and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001" caused by the
> transfer to JUDY (line 3). The JUDY line's ca_id, 3, points to the new
> credit agreement "FIF2 GRNBR 001A" row in the AllAgreements table.
> ca_id developer transaction_type applied_date amount
> -- -- -- -- --
> 1 LUCY 1 20050115 55000.00
> 1 LUCY 2 20050122 -20000.00
> 3 JUDY 2 20050122 20000.00
> 4 SUSIE 2 20050122 10000.00
> 3 JUDY 2 20050122 -10000.00
> 6 WILLY 1 20050211 30000.00
> 7 WILLY 1 20050211 45000.00
> 8 FRED 1 20050212 25000.00
> 8 FRED 2 20050212 -10000.00
> 10 HARRY 2 20050212 10000.00
> 10 HARRY 2 20050225 -100000.00
> 12 JOE 2 20050225 10000.00
> */
>
I can't see the login in the ca_id column as they do not relate to the
transaction concerned! But this is almost what you wanted:
SELECT A.ca_id,--A.[ID #],
B.Developer,
2 Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
B.AMT AS AMT
FROM AllAgreements A
JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
UNION ALL
SELECT A.ca_id,--A.[ID #],
A.Developer AS Developer,
CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.AMT AS AMT
FROM AllAgreements A
LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
ORDER BY A.CA_ID, AMT

> DROP TABLE AllAgreements, Transactions
> /* END */
>
> QUESTION: What append command will move the amounts to the Transactions
> table w/ the correct ca_id value.
>
You will have to decide what the criteria is for the ca_id.
John

> Thanks for your help.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||John Bell wrote:
> I can't see the login [do you mean logic?] in the ca_id column as they do
not relate to the
> transaction concerned! But this is almost what you wanted:
> SELECT A.ca_id,--A.[ID #],
> B.Developer,
> 2 Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> B.AMT AS AMT
> FROM AllAgreements A
> JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> UNION ALL
> SELECT A.ca_id,--A.[ID #],
> A.Developer AS Developer,
> CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> A.AMT AS AMT
> FROM AllAgreements A
> LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> ORDER BY A.CA_ID, AMT
>
> You will have to decide what the criteria is for the ca_id.
>
Ah, but that's just what I need the query to do - figure out which ca_id
goes w/ each transfer's credit transaction.
Think of it this way: Lucy transfers $20K from agreement FIF2 GRNBR 001
(ca_id 1) to Judy, which makes a new agreement FIF2 GRNBR 001A (ca_id 2)
for Judy. I need to show in the Transactions table a debit of $20K from
ca_id 1 and a credit to ca_id 2 (easy to do) of $20K.
The following DML command will get the credits:
INSERT INTO Transactions (ca_id, developer, transaction_type,
applied_date, amount)
SELECT ca_id, developer, 2 As trans_type, issued_date, amt
FROM AllAgreements
WHERE amt > 0
It's the DML command that puts the debits, w/ the proper ca_id, that's
the problem.
Ex. 1:
ca_id, "ID #", developer, amt
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
The 2nd row (ca_id 2) needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
1 Lucy 2 -20000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001.'
Ex. 2:
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
The above row needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
3 Judy 2 -10000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001A.'
IOW, the credits ($) are deducted from the prior agreement:
001A deducts from 001
001B deducts from 001A
I believe I need to add a character "@." to the 001 so the query can
"see" the previous agreement as
Right([current ID #],1) > Right([other ID #],1)
This sorta works:
SELECT C.ca_id, c.[ID #], d.developer, d.transaction_type,
d.issued_date, d.amt
FROM AllAgreements As C,
(SELECT ca_id,[ID #], Developer, 2 AS Transaction_Type,
issued_date, amt
FROM AllAgreements
WHERE amt<0 ) As D
WHERE LEFT(C.[id #],LEN(c.[id #])-1)=LEFT(d.[id #],LEN(d.[id #])-1)
AND ASC(RIGHT(C.[ID #],1)) = ASC(RIGHT(D.[ID #],1))-1
ORDER BY 1, 2
Data from above query (removed the amt & date cols so could display on
one line):
ca_id ID # developer amt
1 FIF2 GRNBR 001@. LUCY ($20,000.00) <- correct
2 FIF2 GRNBR 001A JUDY ($10,000.00) <- incorrect
3 FIF2 GRNBR 001A JUDY ($10,000.00) <- correct
8 TIF1 MSSH 001@. FRED ($10,000.00) <- correct
9 TIF1 MSSH 001A HARRY ($10,000.00) <- incorrect
10 TIF1 MSSH 001A HARRY ($10,000.00) <- correct
Any refinements possible?
Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Circular Reference?

I get this error when I ran the below statement what did I do wrong?

"The definition of MonthRange set contains a circular reference"

WITH SET [MonthRange] AS

{

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].PrevMember

:

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember

}

SELECT {[Measures].[Closing Balance]} on 0,

{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1

FROM [ Bond Analytics OLAP]

WHERE ([MonthRange], [Industry].&[Subprime])

[Dim Originationasofmm] is a regular dimension with members like 200101, 200102, 200103 so on.

What I wanted to do (Note that I do not have a Time Dimension in the cube, just a dimension that simulates this, so would this work just as well? )

Can I have a generic set that would take as input current member and give 3 or 6 or 12 rolling months?

(this is assuming that I cannot switch to using time dimension anytime soon and just have to use a regular dimension for now?)

For example,

given 200101 and say 3 for 3 months rolling period, I would get 200012, 200101, 200102

and

given 200101 and say 7 for 6 months rolling period, I would get 200010, 200011, 200012, 200101, 200102, 200103, 200104

Try this version:

WITH

SET [MonthRange] AS

LastPeriods(3,

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember

)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] as

Aggregate([MonthRange])

SELECT {[Measures].[Closing Balance]} on 0,

{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1

FROM [ Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],

[Industry].&[Subprime])

|||

Hi Deepark,

This query works! but as soon as I add any other measure below (1 or more) to the query, only column that has data would be [Closing Balance] and all other measures are NULL.

Is it because of some SCOPING issue in the calculation?

--Query only returns data for [Closing Balance], all other measures are NULL when they should have data.

WITH SET [MonthRange] AS
LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange])

SELECT {
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS]
} on 0,
{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1
FROM [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
GO

Calculations:

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Bankruptcy] Then
([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Bankruptcy],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Foreclosure] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1;
*/

CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[D BankRuptcy]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim MBA].[Bankruptcy]);
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Foreclosure] Then
([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Foreclosure],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Bankruptcy] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1; */

CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[D Foreclosure]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim MBA].[Foreclosure]);
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 30] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 60] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 90] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Bankruptcy] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Foreclosure] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Current] Then Null
When [Dim OTS].currentmember is [Dim OTS].[All] then
([Dim OTS].[REO],[Measures].[Closing Balance])
/([Dim OTS].[ALL],[Measures].[Closing Balance])
When [Dim OTS].currentmember is [Dim OTS].[REO]
then ([Measures].[Closing Balance]) /([Dim OTS].[REO],[Measures].[Closing Balance])
end,
VISIBLE = 1;
*/

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[Def OTS]);
SCOPE(ROOT([Dim OTS]));
THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim OTS].[REO]);
THIS = 1;
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;

|||

Well, I was able to reproduce this behavior in Adventure Works by creating a calculated measure with default value of NULL (as above); then just assigning it a constant value. But setting the new SP2 SCOPE_ISOLATION property seems to solve it - like:

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

|||

Hi Deepark,

Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!

Perhaps, it is a new feature in SQL Server 2005 SP2.

Maybe it has something to do with calculation order.

Thank you very much.

You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.

I think it's time you get a new title --> SUPER MVP

|||

Hi Deepark,

Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!

Perhaps, it is a new feature in SQL Server 2005 SP2.

Maybe it has something to do with calculation order.

Thank you very much.

You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.

I think it's time you get a new title --> SUPER MVP

by the way, the code that works look like this:

--so if I want another period, I just replace # 3 and [200101] with appropriate member chosen by the user.

--I wonder if I finally do have a Time dimension, would using CurrentMember work so I don't have to hard-code [200101]?

WITH SET [MonthRange] AS

LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

SELECT {

[Measures].[Closing Balance],

[Measures].[D BankRuptcy],

[Measures].[D Foreclosure],

[Measures].[Def OTS],

[Measures].[D30 OTS],

[Measures].[D60 OTS],

[Measures].[D90 OTS]

} on 0,

{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1

FROM [Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])

GO

--this one also works, so if I want another rolling period, I just replace the lag #

WITH SET [MonthRange] AS

{[Dim Originationasofmm].&[200612].lag(1):[Dim Originationasofmm].&[200612].lag(-1)}

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

select

{

[Measures].[Closing Balance],

[Measures].[D BankRuptcy],

[Measures].[D Foreclosure],

[Measures].[Def OTS],

[Measures].[D30 OTS],

[Measures].[D60 OTS],

[Measures].[D90 OTS]

} on 0,

([Dim Asofmm].FIRSTchild:[Dim Asofmm].[200612]) on 1

from [Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])

Checksum computation help

Please execute the script below to understand the problem -

--
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

--id & col1 make up the PK.

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')

select *
from test

select *
from test2

--The rows are identical.
--Script A

select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)

--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)

-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.

TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')

--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.

Thanks

drop table test
drop table test2
go
--Script B is not correct because you have no keys in tables and, of course, it returns rows - col3s are different. There is relation many to many.|||And did you look up CHECKSUM() in BOL?

I know you're trying to accomplish something...but you got me lost..

It's in the same manner as your previous threads...

Can you give us a "big picture" view of what you're trying to accomplish?

I don't mean to offend, but you need to understan what primary keys are for...sounds like your data model is not fitting in quite right with what you're trying to accomplish...|||I think this would give you an idea of the data. Yesterday when I did the processing I had this view of the table -

ID...County...Univ...Dept.....Status

1...A......XYZ...Accounting...Processed - Good
1...A......ABC...Accounting...Processed - Bad
1...A......XYZ...Marketing...Processed - Good
1...B......PQR...HR............Processed - Good
1...C......XXX...HR............Processed - Bad

I have an index on the Status field coz I can see all Bad records on top.

Today I have in my source system -

ID...County...Univ...Dept

1...A......ABC...Accounting
1...A......XYZ...Accounting
1...A......XYZ...Marketing
1...B......PQR...HR
1...C......XXX...HR
2...C......YYY...Training

I want to process only those records that are new/updated since yesterday's version. I get the above records in a separate table and assign a Status to them as 'Not Processed'. I then compare the two tables. And so because of the problem stated before, I end up processing a record that I have processed the previous day.

So how do I go about this problem? Is there a need for another column in here.

CheckQueryProcessorAlive

Please help me determine what could cause the SQL Server 2000 Cluster error
listed below. This is a server with SQL Server 2000 Enterprise Edition as an
Active/Active Cluster and Windows 2003 Server.
Please help me with this error.
Thanks,
00000780.0000149c::2005/01/05-18:22:53.035 INFO [API] User denied access
using default cluster SD. GetLastError() = 0x00000005; dwStatus = 0x00000000.
000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] printODBCError: sqlstate = 01000; native error = 2746;
message = [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionWrite
(send()).
000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = b;
message = [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network
error. Check your network documentation.
000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] OnlineThread: QP is not online.
000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
message = [Microsoft][ODBC SQL Server Driver]Communication link failure
000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
message = [Microsoft][ODBC SQL Server Driver]Communication link failure
000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
message = [Microsoft][ODBC SQL Server Driver]Communication link failure
000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
message = [Microsoft][ODBC SQL Server Driver]Communication link failure
000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
(OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
message = [Microsoft][ODBC SQL Server Driver]Communication link failure
00000780.00000e6c::2005/01/05-18:23:22.676 INFO [CP] CppRegNotifyThread
checkpointing key Software\Microsoft\Microsoft SQL Server\OLTP\MSSQLSERVER to
id 4 due to timer
00000780.00000e6c::2005/01/05-18:23:22.676 INFO [Qfs] QfsGetTempFileName
C:\Temp\, CLS, 41268 => C:\Temp\CLSA164.tmp, status 0
00000780.00000e6c::2005/01/05-18:23:22.676 INFO [Qfs] QfsDeleteFile
C:\Temp\CLSA164.tmp, status 0
00000780.00000e6c::2005/01/05-18:23:22.707 INFO [Qfs] QfsRegSaveKey
C:\Temp\CLSA164.tmp, status 0
The cluster is not running with the required access. Pls check the access by
giving the correct user / pass
Regards
Nirvan
"Joe P." wrote:

> Please help me determine what could cause the SQL Server 2000 Cluster error
> listed below. This is a server with SQL Server 2000 Enterprise Edition as an
> Active/Active Cluster and Windows 2003 Server.
> Please help me with this error.
> Thanks,
>
> 00000780.0000149c::2005/01/05-18:22:53.035 INFO [API] User denied access
> using default cluster SD. GetLastError() = 0x00000005; dwStatus = 0x00000000.
> 000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
> 000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] printODBCError: sqlstate = 01000; native error = 2746;
> message = [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionWrite
> (send()).
> 000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = b;
> message = [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network
> error. Check your network documentation.
> 000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] OnlineThread: QP is not online.
> 000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
> 000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
> message = [Microsoft][ODBC SQL Server Driver]Communication link failure
> 000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
> 000008ec.000007e4::2005/01/05-18:23:17.957 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
> message = [Microsoft][ODBC SQL Server Driver]Communication link failure
> 000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
> 000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
> message = [Microsoft][ODBC SQL Server Driver]Communication link failure
> 000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
> 000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
> message = [Microsoft][ODBC SQL Server Driver]Communication link failure
> 000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
> 000008ec.000007e4::2005/01/05-18:23:17.973 ERR SQL Server <SQL Server
> (OLTP)>: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0;
> message = [Microsoft][ODBC SQL Server Driver]Communication link failure
> 00000780.00000e6c::2005/01/05-18:23:22.676 INFO [CP] CppRegNotifyThread
> checkpointing key Software\Microsoft\Microsoft SQL Server\OLTP\MSSQLSERVER to
> id 4 due to timer
> 00000780.00000e6c::2005/01/05-18:23:22.676 INFO [Qfs] QfsGetTempFileName
> C:\Temp\, CLS, 41268 => C:\Temp\CLSA164.tmp, status 0
> 00000780.00000e6c::2005/01/05-18:23:22.676 INFO [Qfs] QfsDeleteFile
> C:\Temp\CLSA164.tmp, status 0
> 00000780.00000e6c::2005/01/05-18:23:22.707 INFO [Qfs] QfsRegSaveKey
> C:\Temp\CLSA164.tmp, status 0
>
|||This might help explain...
http://support.microsoft.com/default...b;en-us;291255
jg
[quote]Originally posted by Joe P.
[b]Please help me determine what could cause the SQL Server 2000 Cluster error
listed below. This is a server with SQL Server 2000 Enterprise Edition as an
Active/Active Cluster and Windows 2003 Server.
Please help me with this error.
Thanks,|||The service account that MSCS is running under connects to the SQL
instance every 60 seconds by default (configurable in advanced tab of
the SQL Server resource in cluster administrator) and runs
select @.@.server
using its trusted connection. If it's capable of doing that then it's
happy that the SQL instance is alive. It also does a looks alive poll
every 5 seconds (by default) but I'm not sure what it does for a looks
alive poll (probably just a quick check of the status of the MSSQLServer
service on the owner node).
Basically, make sure the cluster service account has a trusted
connection to the SQL server. All it needs is to be a member of the
public role in the master DB (which every login has anyway) so just add
a trusted login for it if it doesn't already exist.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Nirvan Biswas wrote:
[vbcol=seagreen]
>The cluster is not running with the required access. Pls check the access by
>giving the correct user / pass
>Regards
>Nirvan
>"Joe P." wrote:
>

Checking for free disk space and getting mail when it falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

Thanks

Hi Vaddi -

Why not use the Alerts feature in Performance Monitor? The Logical Disk Performance Object has a Counter for % Free Space and you can select which drive letter you'd like to monitor. Once the limit is reached, you can have it email you using a WSH script.

HTH...

checking for free disk space and getting mail , when falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

ThanksHere's what I use:
set nocount on

declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)

create table #FreeSpace(Drive char(1), MB_Free int)

insert into #FreeSpace exec master..xp_fixeddrives

select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold

set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'

if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message

end

drop table #FreeSpace
go

Checking Date column within the same table - DDL included

Hi all,
I have included All sample data, just copy and paste in Query Analizer
I would like to modify the query below to return all that had a prior
"pretest" 30 days prior or equal to
the "test". so the return result that I would like would look like this:
1 A 1997/12/08 pretest
1 A 1997/12/09 test
1 A 1997/12/11 test
3 C 1997/12/18 pretest
3 C 1997/12/19 test
4 D 1997/12/15 pretest
4 D 1997/12/16 test
5 E 1997/12/17 test
5 E 1997/12/17 pretest
6 F 1998/08/03 pretest
6 F 1998/08/04 test
6 F 1998/08/18 test
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'AppointmentTable')
Begin
DROP TABLE AppointmentTable
End
CREATE TABLE [AppointmentTable] (
[tableid] [int] IDENTITY (1, 1) NOT NULL ,
[nameid] [numeric](18, 0) NOT NULL ,
[fullname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[appointmentdate] [datetime] NOT NULL ,
[type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '1997/12/08', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '1997/12/09', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '1997/12/11', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '2003/05/07', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '2003/05/08', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('2','B', '1997/12/12', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('2','B', ' 1998/02/24', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('3','C', '1997/12/18', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('3','C', '1997/12/19', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('4','D', '1997/12/15', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('4','D', '1997/12/16', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('5','E', '1997/12/17', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('5','E', '1997/12/17', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1998/08/03', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1998/08/04', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1998/08/18', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1999/07/07', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1999/08/31', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '2001/05/15', 'test')
SELECT nameid as [Name ID],fullname as [Full Name],
CONVERT(varchar,appointmentdate, 111) as [Appointment Date], type as [Test
Type]
FROM AppointmentTable
ORDER BY [Name ID],[Full Name],[Appointment Date],[Test Type]DESC
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'AppointmentTable')
Begin
DROP TABLE AppointmentTable
End
Thanks for your help
GerryThis works using a UNION!! would there be a better way to write it?
Select u1.nameid as [nameid],u1.fullname as [Full Name], u1.appointmentdate
as [Exam Date], u1.type as [Procedure Type]
from appointmenttable u1
where u1.type = 'pretest'
and exists (SELECT * from appointmenttable u2
where u1.nameid = u2.nameid
and u2.type = 'test'
and u1.appointmentdate <= u2.appointmentdate
and Datediff(day,u1.appointmentdate ,u2.appointmentdate)
<= 30)
UNION
Select u2.nameid as [nameid],u2.fullname as [Full Name], u2.appointmentdate
as [Exam Date], u2.type as [Procedure Type]
from appointmenttable u2
where u2.type = 'test'
and exists (SELECT * from appointmenttable u1
where u1.nameid = u2.nameid
and u1.type = 'pretest'
and u1.appointmentdate <= u2.appointmentdate
and Datediff(day,u1.appointmentdate ,u2.appointmentdate)
<= 30)
ORDER BY [nameid],[Full Name],[Exam Date],[Procedure Type]DESC
thanks
Gerry
"gv" <viatorg@.musc.edu> wrote in message
news:OgIrxTr7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have included All sample data, just copy and paste in Query Analizer
> I would like to modify the query below to return all that had a prior
> "pretest" 30 days prior or equal to
> the "test". so the return result that I would like would look like this:
> 1 A 1997/12/08 pretest
> 1 A 1997/12/09 test
> 1 A 1997/12/11 test
> 3 C 1997/12/18 pretest
> 3 C 1997/12/19 test
> 4 D 1997/12/15 pretest
> 4 D 1997/12/16 test
> 5 E 1997/12/17 test
> 5 E 1997/12/17 pretest
> 6 F 1998/08/03 pretest
> 6 F 1998/08/04 test
> 6 F 1998/08/18 test
>
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
> 'AppointmentTable')
> Begin
> DROP TABLE AppointmentTable
> End
> CREATE TABLE [AppointmentTable] (
> [tableid] [int] IDENTITY (1, 1) NOT NULL ,
> [nameid] [numeric](18, 0) NOT NULL ,
> [fullname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [appointmentdate] [datetime] NOT NULL ,
> [type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '1997/12/08', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '1997/12/09', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '1997/12/11', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '2003/05/07', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '2003/05/08', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('2','B', '1997/12/12', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('2','B', ' 1998/02/24', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('3','C', '1997/12/18', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('3','C', '1997/12/19', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('4','D', '1997/12/15', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('4','D', '1997/12/16', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('5','E', '1997/12/17', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('5','E', '1997/12/17', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1998/08/03', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1998/08/04', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1998/08/18', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1999/07/07', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1999/08/31', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '2001/05/15', 'test')
>
> SELECT nameid as [Name ID],fullname as [Full Name],
> CONVERT(varchar,appointmentdate, 111) as [Appointment Date], type as [Test
> Type]
> FROM AppointmentTable
> ORDER BY [Name ID],[Full Name],[Appointment Date],[Test Type]DESC
>
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
> 'AppointmentTable')
> Begin
> DROP TABLE AppointmentTable
> End
> Thanks for your help
> Gerry
>
>
>
>
>
>
>
>
>
>
>|||See if this helps:
select aouter.*
from #AppointmentTable aouter
where exists
(select * from #AppointmentTable
where aouter.nameid = nameid
and ((aouter.type = 'test'
and type = 'pretest'
and DateDiff(d,appointmentdate,aouter.appointmentdate) between 0 and 30)
or (aouter.type = 'pretest'
and type = 'test'
and DateDiff(d,aouter.appointmentdate,appointmentdate) between 0 and 30))
)
"gv" wrote:

> This works using a UNION!! would there be a better way to write it?
> Select u1.nameid as [nameid],u1.fullname as [Full Name], u1.appointmentdate
> as [Exam Date], u1.type as [Procedure Type]
> from appointmenttable u1
> where u1.type = 'pretest'
> and exists (SELECT * from appointmenttable u2
> where u1.nameid = u2.nameid
> and u2.type = 'test'
> and u1.appointmentdate <= u2.appointmentdate
> and Datediff(day,u1.appointmentdate ,u2.appointmentdat
e)
> <= 30)
> UNION
> Select u2.nameid as [nameid],u2.fullname as [Full Name], u2.appointmentdate
> as [Exam Date], u2.type as [Procedure Type]
> from appointmenttable u2
> where u2.type = 'test'
> and exists (SELECT * from appointmenttable u1
> where u1.nameid = u2.nameid
> and u1.type = 'pretest'
> and u1.appointmentdate <= u2.appointmentdate
> and Datediff(day,u1.appointmentdate ,u2.appointmentdat
e)
> <= 30)
> ORDER BY [nameid],[Full Name],[Exam Date],[Procedure Type]DESC
> thanks
> Gerry
>
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:OgIrxTr7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>
>|||THANKS!!! BIG HELP!!!!
Gerry
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:0EF60332-ADE8-4C4D-9481-A1E8FB733234@.microsoft.com...
> See if this helps:
> select aouter.*
> from #AppointmentTable aouter
> where exists
> (select * from #AppointmentTable
> where aouter.nameid = nameid
> and ((aouter.type = 'test'
> and type = 'pretest'
> and DateDiff(d,appointmentdate,aouter.appointmentdate) between 0 and 30)
> or (aouter.type = 'pretest'
> and type = 'test'
> and DateDiff(d,aouter.appointmentdate,appointmentdate) between 0 and 30))
> )
> "gv" wrote:
>|||thanks again for your help
What if I wanted to only return the latest 65 distinct nameid based on
appointmentdate?
thanks
Gerry
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:0EF60332-ADE8-4C4D-9481-A1E8FB733234@.microsoft.com...
> See if this helps:
> select aouter.*
> from #AppointmentTable aouter
> where exists
> (select * from #AppointmentTable
> where aouter.nameid = nameid
> and ((aouter.type = 'test'
> and type = 'pretest'
> and DateDiff(d,appointmentdate,aouter.appointmentdate) between 0 and 30)
> or (aouter.type = 'pretest'
> and type = 'test'
> and DateDiff(d,aouter.appointmentdate,appointmentdate) between 0 and 30))
> )
> "gv" wrote:
>|||Hopefully this will help u:
select top 65 a.nameid
from #AppointmentTable a
where exists
(select * from #AppointmentTable b
where a.nameid = b.nameid and a.type <> b.type
and
((a.type = 'pretest' and datediff(d,a.appointmentdate,b.appointmentdate)
between 0 and 30)
or
(a.type = 'test' and datediff(d,b.appointmentdate,a.appointmentdate) between
0 and 30))
)
group by a.nameid
order by max(a.appointmentdate) desc
"gv" wrote:

> thanks again for your help
> What if I wanted to only return the latest 65 distinct nameid based on
> appointmentdate?
> thanks
> Gerry|||Thank you so much for your help!!!
Very Close!
The 65 needs to be Distinct from column namid so there could actually be
over 130 rows returned but only 65 different
nameid and within each nameid the pretest needs to be listed first. But
within each different nameid
you have it right where the order of Max appointmentdate is coming first.
Thanks again for your help Absar
Gerry :< )
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:74B787F2-88D4-4120-858B-1F1B8FA8B34A@.microsoft.com...
> Hopefully this will help u:
> select top 65 a.nameid
> from #AppointmentTable a
> where exists
> (select * from #AppointmentTable b
> where a.nameid = b.nameid and a.type <> b.type
> and
> ((a.type = 'pretest' and datediff(d,a.appointmentdate,b.appointmentdate)
> between 0 and 30)
> or
> (a.type = 'test' and datediff(d,b.appointmentdate,a.appointmentdate)
> between
> 0 and 30))
> )
> group by a.nameid
> order by max(a.appointmentdate) desc
> "gv" wrote:
>
>|||On Wed, 23 Nov 2005 14:29:56 -0500, gv wrote:

>Thank you so much for your help!!!
>Very Close!
>The 65 needs to be Distinct from column namid so there could actually be
>over 130 rows returned but only 65 different
>nameid and within each nameid the pretest needs to be listed first. But
>within each different nameid
>you have it right where the order of Max appointmentdate is coming first.
>Thanks again for your help Absar
>Gerry :< )
Hi Gerry,
Maybe this is what you need?
(Note - I also changed the way to determine the date range in order to
have a better chance of using an index -if there is any- on the
appointmentdate column).
SELECT nameid as [Name ID],fullname as [Full Name],
CONVERT(varchar,appointmentdate, 111) as [Appointment
Date],
type as [Test Type]
FROM AppointmentTable AS a
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS b
WHERE b.nameid = a.nameid
AND b.type <> a.type
AND b.appointmentdate BETWEEN CASE WHEN a.type = 'test'
THEN DATEADD (day, -30,
a.appointmentdate)
ELSE a.appointmentdate
END
AND CASE WHEN a.type = 'test'
THEN a.appointmentdate
ELSE DATEADD (day, 30,
a.appointmentdate)
END)
AND a.nameid IN
(SELECT TOP 65 c.nameid
FROM AppointmentTable AS c
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS d
WHERE d.nameid = c.nameid
AND d.type <> c.type
AND d.appointmentdate BETWEEN CASE WHEN c.type = 'test'
THEN DATEADD (day, -30,
c.appointmentdate)
ELSE c.appointmentdate
END
AND CASE WHEN c.type = 'test'
THEN c.appointmentdate
ELSE DATEADD (day, 30,
c.appointmentdate)
END)
GROUP BY c.nameid
ORDER BY MAX(appointmentdate))
ORDER BY [Name ID],[Full Name],[Appointment Date],[Test Type] DESC
I've tested this against the test data you supplied (thanks for that, by
the way!). If I change the TOP 65 to TOP 3, I only get the results for
nameid 1, 4, and 5.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much for your help!
Almost got it.
I've added some more test data and included your query. Doesn't return the c
orrect top 6 based on appointmentdate. And
within each id the appointmentdate should be ordered by earlist date first b
ut it is correct to order the latest date of the id first.
sorry if I was not clear enough before. I would also like it to only return
those that had "checked"
at least once next to "test" within a group. See all sample data below tha
nks again Gerry
Returns this:
7 G 2005/07/07 test checked
7 G 2005/07/06 pretest -
8 H 2005/06/24 test checked
8 H 2005/06/22 test checked
8 H 2005/06/19 pretest -
9 I 2005/05/22 test
9 I 2005/05/20 pretest -
10 J 2005/04/10 test checked
10 J 2005/04/05 pretest -
11 K 2005/03/16 test checked
11 K 2005/03/14 pretest -
7 G 2004/05/05 test
7 G 2004/05/04 pretest -
7 G 2002/09/25 test
7 G 2002/09/04 pretest -
6 F 1998/08/18 test
6 F 1998/08/04 test checked
6 F 1998/08/03 pretest -
7 G 1992/07/30 test
7 G 1992/07/08 pretest -
should return this and in this order:
7 G 2005/07/06 pretest -
7 G 2005/07/07 test checked
8 H 2005/06/19 pretest -
8 H 2005/06/22 test checked
8 H 2005/06/24 test
9 I 2005/05/20 pretest -
9 I 2005/05/22 test checked
10 J 2005/04/05 pretest -
10 J 2005/04/10 test checked
11 K 2005/03/14 pretest -
11 K 2005/03/16 test checked
7 G 2004/05/04 pretest -
7 G 2004/05/05 test
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Appo
intmentTable')
Begin
DROP TABLE AppointmentTable
End
CREATE TABLE [AppointmentTable] (
[tableid] [int] IDENTITY (1, 1) NOT NULL ,
[nameid] [numeric](18, 0) NOT NULL ,
[fullname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[appointmentdate] [datetime] NOT NULL ,
[type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[status] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '1997/12/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '1997/12/09', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '1997/12/11', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '2003/05/07', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '2003/05/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('2','B', '1997/12/12', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('2','B', ' 1998/02/24', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('3','C', '1997/12/18', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('3','C', '1997/12/19', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('4','D', '1997/12/15', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('4','D', '1997/12/16', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('5','E', '1997/12/17', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('5','E', '1997/12/17', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1998/08/03', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1998/08/04', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1998/08/18', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1999/07/07', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1999/08/31', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '2001/05/15', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2005/07/06', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2005/07/07', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2004/05/04', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2004/05/05', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2002/09/04', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2002/09/25', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '1992/07/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '1992/07/30', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('8','H', '2005/06/19', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('8','H', '2005/06/22', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('8','H', '2005/06/24', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('9','I', '2005/05/20', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('9','I', '2005/05/22', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('10','J', '2005/04/05', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('10','J', '2005/04/10', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('11','K', '2005/03/14', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('11','K', '2005/03/16', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('12','L', '2005/02/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('12','L', '2005/03/11', 'test','checked')
SELECT nameid as [Name ID],fullname as [Full Name],
CONVERT(varchar,appointmentdate, 111) as [Appointment Date],
type as [Test Type],status
FROM AppointmentTable AS a
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS b
WHERE b.nameid = a.nameid
AND b.type <> a.type
AND b.appointmentdate BETWEEN CASE WHEN a.type = 'test'
THEN DATEADD (day, -30,a.appointmentdate)
ELSE a.appointmentdate
END
AND CASE WHEN a.type = 'test'
THEN a.appointmentdate
ELSE DATEADD (day, 30,a.appointmentdate)
END)
AND a.nameid IN
(SELECT TOP 6 c.nameid
FROM AppointmentTable AS c
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS d
WHERE d.nameid = c.nameid
AND d.type <> c.type
AND d.appointmentdate BETWEEN CASE WHEN c.type = 'test'
THEN DATEADD (day, -30,c.appointmentdate)
ELSE c.appointmentdate
END
AND CASE WHEN c.type = 'test'
THEN c.appointmentdate
ELSE DATEADD (day, 30,c.appointmentdate)
END)
GROUP BY c.nameid
ORDER BY MAX(appointmentdate)DESC )
ORDER BY [Appointment Date]desc
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Appo
intmentTable')
Begin
DROP TABLE AppointmentTable
End|||On Mon, 28 Nov 2005 15:06:38 -0500, gv wrote:

>Thanks so much for your help!
>Almost got it.
>I've added some more test data and included your query. Doesn't return the
correct top 6 based on appointmentdate. And
>within each id the appointmentdate should be ordered by earlist date first
but it is correct to order the latest date of the id first.
>sorry if I was not clear enough before. I would also like it to only return
those that had "checked"
>at least once next to "test" within a group. See all sample data below thanks
again Gerry
Hi Gerry,
Thanks for posting CREATE TABLE and INSERT statements. Helps a lot!
Before I start writing a query, let me clarify what I think you want to
get from your data. Correct me if I'm wrong.
- You need to find "pretest" with a "test" for the same nameid in the
next 30 days.
- Of those, you only wwant to select the 6 (or 65) most recent rows.
- The "pretest" rows should be returned in the order of most recent row
first.
- But the "test" rows should FOLLOW the accompanying "pretest" row, and
should be presented oldest first.
Finally, in the required output you have given, you have changed the
contents of these rows:

>8 H 2005/06/24 test checked
>8 H 2005/06/22 test checked
to this:

>8 H 2005/06/22 test checked
>8 H 2005/06/24 test
Why did you omit "checked" on this row? Why didn't you omit it on any
other rows? I don't understand that part of your requirement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Check the continuity of dates

I am trying to find coverage dates for a given list of dates that may
have breakage.
For example person 123 has the below records:
PersonID Date
123 10/15/03
123 11/15/03
123 12/15/03
123 3/15/05
123 4/15/05
123 5/15/05
For PersonID 123 I would expect 2 records with coverage dates of
10/1/03 - 12/31/03 and another one of 3/1/05 - 5/31/05. What is the
best approach for this? I am unconcerned about getting the First/End
of the month dates because I have functions to get this info. I need
to know the best way to check the continuity of dates.It won't produce results in the format that you wan't, but this will show yo
u
if there are any gaps in coverage:
select t1.PersonID, DATEADD(mm, 1, t1.[Date]) FROM [YourTable] t1
WHERE NOT EXISTS
(SELECT t2.[Date] FROM [YourTable] t2 WHERE t1.PersonID=t2.PersonID
AND t2.[Date] = DATEADD(mm, 1, t1.[Date])
This will show the months for which a lapse in coverage begins.
Assuming that there was only 1 gap in coverage for all customers, you could
do something like this:
select t1.PersonID, DATEADD(mm, 1, t1.[Date]) AS "LapseBegins",
t5.LapseEnds
FROM [YourTable] t1
WHERE NOT EXISTS
(SELECT t2.[Date] FROM [YourTable] t2 WHERE t1.PersonID=t2.PersonID
AND t2.[Date] = DATEADD(mm, 1, t1.[Date])
INNER JOIN
(
select t3.PersonID, t3.[Date] AS "LapseEnds" FROM [YourTable] t3
WHERE NOT EXISTS
(SELECT t4.[Date] FROM [YourTable] t4 WHERE t3.PersonID=t4.PersonID
AND t3.[Date] = DATEADD(mm, -1, t4.[Date])
) t5
ON t5.PersonID=t5.PersonID
This would return results like
PersonID LapseBegins LapseEnds
-- -- --
123 1/15//04 3/15/05
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"cxg" wrote:

> I am trying to find coverage dates for a given list of dates that may
> have breakage.
> For example person 123 has the below records:
> PersonID Date
> 123 10/15/03
> 123 11/15/03
> 123 12/15/03
> 123 3/15/05
> 123 4/15/05
> 123 5/15/05
> For PersonID 123 I would expect 2 records with coverage dates of
> 10/1/03 - 12/31/03 and another one of 3/1/05 - 5/31/05. What is the
> best approach for this? I am unconcerned about getting the First/End
> of the month dates because I have functions to get this info. I need
> to know the best way to check the continuity of dates.
>|||Considering the following DDL and sample data:
CREATE TABLE TheTable (
PersonID int,
SomeDate smalldatetime,
PRIMARY KEY (PersonID, SomeDate)
)
INSERT INTO TheTable VALUES (123, '20031010')
INSERT INTO TheTable VALUES (123, '20031011')
INSERT INTO TheTable VALUES (123, '20031012')
INSERT INTO TheTable VALUES (123, '20050503')
INSERT INTO TheTable VALUES (123, '20050504')
INSERT INTO TheTable VALUES (123, '20050505')
INSERT INTO TheTable VALUES (124, '20050503')
INSERT INTO TheTable VALUES (124, '20050504')
INSERT INTO TheTable VALUES (124, '20050505')
INSERT INTO TheTable VALUES (124, '20050606')
INSERT INTO TheTable VALUES (125, '20050707')
Let's suppose that we expect the following result:
PersonID StartDate EndDate
-- -- --
123 2003-10-10 2003-10-12
123 2005-05-03 2005-05-05
124 2005-05-03 2005-05-05
124 2005-06-06 2005-06-06
125 2005-07-07 2005-07-07
The following query returns the above result:
SELECT PersonID, SomeDate AS StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=A.PersonID
AND C.SomeDate>=A.SomeDate
AND C.SomeDate<ISNULL((
SELECT MIN(D.SomeDate) FROM TheTable D
WHERE D.PersonID=C.PersonID
AND D.SomeDate>A.SomeDate
AND NOT EXISTS (
SELECT * FROM TheTable E
WHERE D.SomeDate=E.SomeDate+1
)
),C.SomeDate+1)
) AS EndDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
The query can be simplified using a CTE in SQL Server 2005 (or a view):
WITH MyCTE AS (
SELECT PersonID, SomeDate AS StartDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
)
SELECT PersonID, StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=X.PersonID
AND C.SomeDate>=X.StartDate
AND C.SomeDate<ISNULL((
SELECT MIN(Y.StartDate) FROM MyCTE Y
WHERE Y.PersonID=C.PersonID
AND Y.StartDate>X.StartDate
),C.SomeDate+1)
) as EndDate
FROM MyCTE X
The first query was inspired by reading (a few years ago) the following
article:
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp[
/url]
Razvan|||Just a little addition:
I understand that your requirements are a little different: you don't want
to check for consecutive days, but for dates in consecutive months. I will
leave the modifications for you, as an exercise :)
Razvan|||Google the use of an auxilary Calendar table. DATE is both a reserved
word and too vague to be data element name. And the only format allowed
in Standard SQL is ISO-8601.
We need a range of dates to consider for the report, so make them
parameters. We want to find calendar dates in the range that are not
matched to foo_dates in the same range.
SELECT F1.person_id, C1.cal_date,
@.report_start_date, @.report_end_date
FROM Foobar AS F1, Calendar AS C1
WHERE NOT EXISTS
(SELECT *
FROM Foobar AS F2
WHERE C1.cal_date BETWEEN @.report_start_date AND
@.report_end_date
AND F1.cal_date BETWEEN @.report_start_date AND
@.report_end_date
AND C1.cal_date = F1.foo_date);
If you want to express this result as ranges, you can Google some other
postings about gaps shown as (start, end) pairs.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||Consider the following DDL and sample data:
CREATE TABLE TheTable (
PersonID int,
SomeDate smalldatetime,
PRIMARY KEY (PersonID, SomeDate)
)
INSERT INTO TheTable VALUES (123, '20031010')
INSERT INTO TheTable VALUES (123, '20031011')
INSERT INTO TheTable VALUES (123, '20031012')
INSERT INTO TheTable VALUES (123, '20050503')
INSERT INTO TheTable VALUES (123, '20050504')
INSERT INTO TheTable VALUES (123, '20050505')
INSERT INTO TheTable VALUES (124, '20050503')
INSERT INTO TheTable VALUES (124, '20050504')
INSERT INTO TheTable VALUES (124, '20050505')
INSERT INTO TheTable VALUES (124, '20050606')
INSERT INTO TheTable VALUES (125, '20050707')
Let's suppose the expected result is:
PersonID StartDate EndDate
-- -- --
123 2003-10-10 2003-10-12
123 2005-05-03 2005-05-05
124 2005-05-03 2005-05-05
124 2005-06-06 2005-06-06
125 2005-07-07 2005-07-07
The following query returns the above results:
SELECT PersonID, SomeDate AS StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=A.PersonID
AND C.SomeDate>=A.SomeDate
AND C.SomeDate<ISNULL((
SELECT MIN(D.SomeDate) FROM TheTable D
WHERE D.PersonID=C.PersonID
AND D.SomeDate>A.SomeDate
AND NOT EXISTS (
SELECT * FROM TheTable E
WHERE D.SomeDate=E.SomeDate+1
)
),C.SomeDate+1)
) AS EndDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
We can simplify it a bit using a CTE in SQL Server 2005 (or a view):
WITH MyCTE AS (
SELECT PersonID, SomeDate AS StartDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
)
SELECT PersonID, StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=X.PersonID
AND C.SomeDate>=X.StartDate
AND C.SomeDate<ISNULL((
SELECT MIN(Y.StartDate) FROM MyCTE Y
WHERE Y.PersonID=C.PersonID
AND Y.StartDate>X.StartDate
),C.SomeDate+1)
) as EndDate
FROM MyCTE X
I understand that your requirements are different: you do not need to
have consecutive days, but dates in consecutive months. I will leave
the modifications of the query to you, as an exercise.
The above query was inspired by reading (a few years ago) the following
article:
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp[
/url]
Razvan|||A small update:
I understand that your requirements are a little different: you don't
want to check for consecutive days, but for dates in consecutive
months. I will leave the modifications for you, as an exercise :)
Razvan|||Consider the following DDL and sample data:
CREATE TABLE TheTable (
PersonID int,
SomeDate smalldatetime,
PRIMARY KEY (PersonID, SomeDate)
)
INSERT INTO TheTable VALUES (123, '20031010')
INSERT INTO TheTable VALUES (123, '20031011')
INSERT INTO TheTable VALUES (123, '20031012')
INSERT INTO TheTable VALUES (123, '20050503')
INSERT INTO TheTable VALUES (123, '20050504')
INSERT INTO TheTable VALUES (123, '20050505')
INSERT INTO TheTable VALUES (124, '20050503')
INSERT INTO TheTable VALUES (124, '20050504')
INSERT INTO TheTable VALUES (124, '20050505')
INSERT INTO TheTable VALUES (124, '20050606')
INSERT INTO TheTable VALUES (125, '20050707')
Let's suppose the expected result is:
PersonID StartDate EndDate
-- -- --
123 2003-10-10 2003-10-12
123 2005-05-03 2005-05-05
124 2005-05-03 2005-05-05
124 2005-06-06 2005-06-06
125 2005-07-07 2005-07-07
The following query returns this result:
SELECT PersonID, SomeDate AS StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=A.PersonID
AND C.SomeDate>=A.SomeDate
AND C.SomeDate<ISNULL((
SELECT MIN(D.SomeDate) FROM TheTable D
WHERE D.PersonID=C.PersonID
AND D.SomeDate>A.SomeDate
AND NOT EXISTS (
SELECT * FROM TheTable E
WHERE D.SomeDate=E.SomeDate+1
)
),C.SomeDate+1)
) AS EndDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
The query can be simplified by using a CTE in SQL Server 2005 (or a
view):
WITH MyCTE AS (
SELECT PersonID, SomeDate AS StartDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
)
SELECT PersonID, StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=X.PersonID
AND C.SomeDate>=X.StartDate
AND C.SomeDate<ISNULL((
SELECT MIN(Y.StartDate) FROM MyCTE Y
WHERE Y.PersonID=C.PersonID
AND Y.StartDate>X.StartDate
),C.SomeDate+1)
) as EndDate
FROM MyCTE X
I understand that your requirements are a little different: you don't
want to check for consecutive days, but for dates in consecutive
months. I will leave the modifications to you, as an exercise. :)
The above query was inspired by reading (a few years ago) the following
article:
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp[
/url]
Razvan|||Thanks so much for you assistance. This was of immense help to me.|||Thanks Joe!
Your books are great!