Loop, Cursor or both?

Question

Loop, Cursor or both?

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

george.greiner (6/29/2010)
Okay here is the data and tables you asked for assuming I did this correct.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#FileInfo','U') IS NOT NULL
DROP TABLE #FileInfo
--===== Create the test table with
CREATE TABLE #FileInfo
(
CaseNumber varchar(50) PRIMARY KEY CLUSTERED,
BRTNumber varchar(50),
ParcelNumber varchar(50),
Premises varchar(50),
Client varchar(50)
)
--populate table with data
INSERT INTO #FileInfo
(CaseNumber, BRTNumber, ParcelNumber, Premises, Client)
SELECT 'PHI0001','0123456789','13N12-139','1234 Market Street','Linebarger' UNION ALL
SELECT 'PHI0002','1234567890','4N8-193','234 Chestnut Street','Linebarger' UNION ALL
SELECT 'PHI0003','2345678901','6N10-235','1600 JFK Blvd','Linebarger'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#municipalLiens','U') IS NOT NULL
DROP TABLE #municipalLiens
--===== Create the test table with
CREATE TABLE #municipalLiens
(
MuniKey int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
LienNumber varchar(50),
LienDate datetime,
LienReason varchar(50),
LienAmount money
)
--populate table with data
INSERT INTO #municipalLiens
(MuniKey, CaseNumber, LienNumber, LienDate, LienReason, LienAmount)
SELECT '1','PHI0001','05263565','07/29/1982','Replace Curb Stop', 256.32 UNION ALL
SELECT '2','PHI0001','98765436','05/06/1989','Turn off water meter', 34.00 UNION ALL
SELECT '3','PHI0002','69874963','08/28/2008','Replace Curb Stop', 400.65 UNION ALL
SELECT '4','PHI0003','98767655','08/30/1984','Replace Curb Stop', 256.32 UNION ALL
SELECT '5','PHI0003','44444444','05/06/1995','Turn off water meter', 34.00 UNION ALL
SELECT '6','PHI0003','23423521','08/28/2010','Replace Curb Stop', 400.65
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Documents','U') IS NOT NULL
DROP TABLE #Documents
--===== Create the test table with
CREATE TABLE #Documents
(
documentID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
recDate datetime,
docDate datetime,
docAmount varchar(50),
interestID varchar(50)
)
--populate table with data
INSERT INTO #Documents
(documentID, CaseNumber, recDate, docDate, docAmount, interestID)
SELECT '1','PHI0001','07/28/1982','07/29/1982',100000.00, '1' UNION ALL
SELECT '2','PHI0001','05/06/1989','05/06/1989',50000.00, '2' UNION ALL
SELECT '3','PHI0002','08/28/2008','09/15/2008',25000.00, '3' UNION ALL
SELECT '4','PHI0002','08/28/2008','08/30/2008',10000.00, '4' UNION ALL
SELECT '5','PHI0002','05/10/1995','05/06/1995',5000.00, '5' UNION ALL
SELECT '6','PHI0003','08/28/2010','08/28/2010',2500.00, '6'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#eInterests','U') IS NOT NULL
DROP TABLE #eInterests
--===== Create the test table with
CREATE TABLE #eInterests
(
interestID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
eInterestFirst varchar(50),
eInterestMiddle varchar(50),
eInterestLast varchar(50),
eInterestAlias varchar(50)
)
--populate table with data
INSERT INTO #eInterests
(interestID, CaseNumber, eInterestFirst, eInterestMiddle, eInterestLast, eInterestAlias)
SELECT '1','PHI0001','George','Q','Greiner', '' UNION ALL
SELECT '2','PHI0001','Joe','Z','Smith', 'JZS' UNION ALL
SELECT '3','PHI0002','Lynne','','Tymonko', 'Lynn T. Tymonko' UNION ALL
SELECT '4','PHI0002','Bob','P','Pablano', '' UNION ALL
SELECT '5','PHI0002','Aaron','B','Cohen', '' UNION ALL
SELECT '6','PHI0003','Orbit','T','Trommer', ''
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#addresses','U') IS NOT NULL
DROP TABLE #addresses
--===== Create the test table with
CREATE TABLE #addresses
(
AddressID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
interestID int,
iAddress varchar(50),
iCity varchar(50),
iState varchar(50),
izip varchar(10)
)
INSERT INTO #addresses
(AddressID, CaseNumber, interestID, iAddress, iCity, iState, iZip)
SELECT '1','PHI0001','2','234 Walnut Street','Philadelphia', 'PA', '19103' UNION ALL
SELECT '2','PHI0003','6','5722 Filbert STreet','Philadelphia', 'PA', '19115'
--===== If the test table already exists, drop it -- note that this is from another database
IF OBJECT_ID('TempDB..#judgments','U') IS NOT NULL
DROP TABLE #judgments
--===== Create the test table with
CREATE TABLE #judgments
(
judgmentID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
JAmt varchar(50),
CaseID nvarchar(50),
Jdate datetime,
plaintiff_info varchar(255),
defendant_info varchar(255)
)
--populate table with data
INSERT INTO #judgments
(judgmentID, CaseNumber, JAmt, CaseID, Jdate, plaintiff_info, defendant_info)
SELECT '1','PHI0001','789.58','05632132','11/02/2002', 'Jimmy Fallon 1234 Market Street, Philadelphia, PA 19115', 'Philadelphia Traffic Court' UNION ALL
SELECT '2','PHI0001','75211.21','95125154','05/15/2007', 'Smith Bob 56 Trenton STreet', 'IRS' UNION ALL
SELECT '3','PHI0001','123123.25','85214125','03/25/2008', 'George Greiner 265 Felton Street, Glenside, PA 19038', 'Bank of America' UNION ALL
SELECT '4','PHI0002','345345.23','98761232','07/15/2009', 'Cohen Aaron B 345 Lincoln Parkway, Baltimore, MD', 'Wells Fargo' UNION ALL
SELECT '5','PHI0003','98765.36','75855552','08/17/2006', 'Michelle Smith Z', 'City of Philadelphia' UNION ALL
SELECT '6','PHI0003','12311.98','11111111','12/13/2005', 'Donavan McNabb 567 Richmond Lane, Marlton, NJ', 'Internal Revenue Service'

Hi George,

Sorry that I didn't see you post this last week.

One last question: based upon this test data, what does the output need to look like?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

george.greiner Default port Points: 1473 More actions · Answer 2

WayneS (7/6/2010)
george.greiner (6/29/2010)
Okay here is the data and tables you asked for assuming I did this correct.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#FileInfo','U') IS NOT NULL
DROP TABLE #FileInfo
--===== Create the test table with
CREATE TABLE #FileInfo
(
CaseNumber varchar(50) PRIMARY KEY CLUSTERED,
BRTNumber varchar(50),
ParcelNumber varchar(50),
Premises varchar(50),
Client varchar(50)
)
--populate table with data
INSERT INTO #FileInfo
(CaseNumber, BRTNumber, ParcelNumber, Premises, Client)
SELECT 'PHI0001','0123456789','13N12-139','1234 Market Street','Linebarger' UNION ALL
SELECT 'PHI0002','1234567890','4N8-193','234 Chestnut Street','Linebarger' UNION ALL
SELECT 'PHI0003','2345678901','6N10-235','1600 JFK Blvd','Linebarger'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#municipalLiens','U') IS NOT NULL
DROP TABLE #municipalLiens
--===== Create the test table with
CREATE TABLE #municipalLiens
(
MuniKey int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
LienNumber varchar(50),
LienDate datetime,
LienReason varchar(50),
LienAmount money
)
--populate table with data
INSERT INTO #municipalLiens
(MuniKey, CaseNumber, LienNumber, LienDate, LienReason, LienAmount)
SELECT '1','PHI0001','05263565','07/29/1982','Replace Curb Stop', 256.32 UNION ALL
SELECT '2','PHI0001','98765436','05/06/1989','Turn off water meter', 34.00 UNION ALL
SELECT '3','PHI0002','69874963','08/28/2008','Replace Curb Stop', 400.65 UNION ALL
SELECT '4','PHI0003','98767655','08/30/1984','Replace Curb Stop', 256.32 UNION ALL
SELECT '5','PHI0003','44444444','05/06/1995','Turn off water meter', 34.00 UNION ALL
SELECT '6','PHI0003','23423521','08/28/2010','Replace Curb Stop', 400.65
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Documents','U') IS NOT NULL
DROP TABLE #Documents
--===== Create the test table with
CREATE TABLE #Documents
(
documentID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
recDate datetime,
docDate datetime,
docAmount varchar(50),
interestID varchar(50)
)
--populate table with data
INSERT INTO #Documents
(documentID, CaseNumber, recDate, docDate, docAmount, interestID)
SELECT '1','PHI0001','07/28/1982','07/29/1982',100000.00, '1' UNION ALL
SELECT '2','PHI0001','05/06/1989','05/06/1989',50000.00, '2' UNION ALL
SELECT '3','PHI0002','08/28/2008','09/15/2008',25000.00, '3' UNION ALL
SELECT '4','PHI0002','08/28/2008','08/30/2008',10000.00, '4' UNION ALL
SELECT '5','PHI0002','05/10/1995','05/06/1995',5000.00, '5' UNION ALL
SELECT '6','PHI0003','08/28/2010','08/28/2010',2500.00, '6'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#eInterests','U') IS NOT NULL
DROP TABLE #eInterests
--===== Create the test table with
CREATE TABLE #eInterests
(
interestID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
eInterestFirst varchar(50),
eInterestMiddle varchar(50),
eInterestLast varchar(50),
eInterestAlias varchar(50)
)
--populate table with data
INSERT INTO #eInterests
(interestID, CaseNumber, eInterestFirst, eInterestMiddle, eInterestLast, eInterestAlias)
SELECT '1','PHI0001','George','Q','Greiner', '' UNION ALL
SELECT '2','PHI0001','Joe','Z','Smith', 'JZS' UNION ALL
SELECT '3','PHI0002','Lynne','','Tymonko', 'Lynn T. Tymonko' UNION ALL
SELECT '4','PHI0002','Bob','P','Pablano', '' UNION ALL
SELECT '5','PHI0002','Aaron','B','Cohen', '' UNION ALL
SELECT '6','PHI0003','Orbit','T','Trommer', ''
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#addresses','U') IS NOT NULL
DROP TABLE #addresses
--===== Create the test table with
CREATE TABLE #addresses
(
AddressID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
interestID int,
iAddress varchar(50),
iCity varchar(50),
iState varchar(50),
izip varchar(10)
)
INSERT INTO #addresses
(AddressID, CaseNumber, interestID, iAddress, iCity, iState, iZip)
SELECT '1','PHI0001','2','234 Walnut Street','Philadelphia', 'PA', '19103' UNION ALL
SELECT '2','PHI0003','6','5722 Filbert STreet','Philadelphia', 'PA', '19115'
--===== If the test table already exists, drop it -- note that this is from another database
IF OBJECT_ID('TempDB..#judgments','U') IS NOT NULL
DROP TABLE #judgments
--===== Create the test table with
CREATE TABLE #judgments
(
judgmentID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
JAmt varchar(50),
CaseID nvarchar(50),
Jdate datetime,
plaintiff_info varchar(255),
defendant_info varchar(255)
)
--populate table with data
INSERT INTO #judgments
(judgmentID, CaseNumber, JAmt, CaseID, Jdate, plaintiff_info, defendant_info)
SELECT '1','PHI0001','789.58','05632132','11/02/2002', 'Jimmy Fallon 1234 Market Street, Philadelphia, PA 19115', 'Philadelphia Traffic Court' UNION ALL
SELECT '2','PHI0001','75211.21','95125154','05/15/2007', 'Smith Bob 56 Trenton STreet', 'IRS' UNION ALL
SELECT '3','PHI0001','123123.25','85214125','03/25/2008', 'George Greiner 265 Felton Street, Glenside, PA 19038', 'Bank of America' UNION ALL
SELECT '4','PHI0002','345345.23','98761232','07/15/2009', 'Cohen Aaron B 345 Lincoln Parkway, Baltimore, MD', 'Wells Fargo' UNION ALL
SELECT '5','PHI0003','98765.36','75855552','08/17/2006', 'Michelle Smith Z', 'City of Philadelphia' UNION ALL
SELECT '6','PHI0003','12311.98','11111111','12/13/2005', 'Donavan McNabb 567 Richmond Lane, Marlton, NJ', 'Internal Revenue Service'
Hi George,
Sorry that I didn't see you post this last week.
One last question: based upon this test data, what does the output need to look like?

Hey Wayne I have working for the most part just have to get dates looking correct after the varchar conversion and then figure out how to pass 'subIDs' as some of the tables have IDs that relate to tables under them and I have yet to figure out how to pass them to the next table.

Here is what I have so far.

;WITH outpQ

AS

(

FROM CityCollection.dbo.FileInfo

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.municipalLiens B ON B.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.eInterest C ON C.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.addresses D ON D.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.banks E ON E.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.Documents F ON F.CaseNumber = A.CaseNumber

UNION ALL

SELECT 'C', A.CaseNumber, 'C|' + IsNull(G.grantorDef,' ') + '|' + IsNull(G.grantor, ' ') AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.documents G ON G.CaseNumber = A.CaseNumber

UNION ALL

SELECT 'C', A.CaseNumber, 'C|' + IsNull(H.TADef,' ') + '|' + IsNull(H.TaxAccts, ' ') AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.FileInfo H ON H.CaseNumber = A.CaseNumber

UNION ALL

SELECT 'C', A.CaseNumber, 'C|' + IsNull(I.RegDef,' ') + '|' + IsNull(I.Registry, ' ') AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.FileInfo I ON I.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN Acme.new_judgment_system.dbo.selected_compiled_clean J ON J.CaseNumber = A.CaseNumber

--SELECT dbo_selected_compiled_clean.plaintiffName, dbo_selected_compiled_clean.plaintiffAdd1, dbo_selected_compiled_clean.plaintiffCity, dbo_selected_compiled_clean.plaintiffState, dbo_selected_compiled_clean.plaintiffZip, dbo_selected_compiled_clean.plaintiffCountry, dbo_selected_compiled_clean.defendantName, dbo_selected_compiled_clean.defendantAdd1, dbo_selected_compiled_clean.defcity, dbo_selected_compiled_clean.defState, mpiled_clean.Court, dbo_selected_compiled_clean.CaseID, dbo_selected_compiled_clean.JAmt, Format(dbo_selected_compiled_clean.JDate,'mmddyyyy') AS JDate, Format(dbo_selected_compiled_clean.revivedDate,'mmddyyyy') AS revivedDate FROM dbo_selected_compiled_clean WHERE "

)

SELECT Extract FROM outpQ ORDER BY CaseNumber, Ord

The output looks like this:

A|09-0403|062195300|ALL THAT CERTAIN lot or piece of ground with the buildings and improvements thereon erected, lot or piece of ground with the buildings and improvements thereon erected, described according to a sruvey thereof made by Joseph Johnson, Esq., Surveyor and Regulator of the 11th District as follows, to wit:

SITUATE on the Northeasterly sside of Lediy Avenue at the distance of 310 feet 2-5/8 inches Northwestwardly from the point of intersection of the said Northeastwardly side of Leidy Avenue with the North side of Girard Avenue.

CONTAINING in front or breadth on the said Leidy Avenue 17 feet and extending of that breadth in length or depth Northeastwardly between lines at right angles to the said Leidy Avenue, 160 feet to Viola Street.

BEING the same premises which Robert H. Mitchell and O'Kella E. Mitchell, h/w by Indenture bearing the date 10/21/1974 and recorded 02/14/1975 at Philadelphia, PA in Deed Book DCC 803 - 221, granted and conveyed to Christ Community Baptist Church, a PA Non-Profit Corporation.

Known By BRT as: 4131 LEIDY AVE

06-2-195300|03272009|Vacant land -- address not adequate for notice of foreclosure.

Possible City Transfer Tax due if Grantee was not granted exemption claimed in 1974 Deed.

Comm of PA to be notified of foreclosure for possible outstanding tax liability (corporate or fiduciary record owner).[1]098 N 01 - 059[2][3]-[4][5]310'2 5/8"" W GIRARD" 17'X160' KITCHEN 2 FAMILY 17'X160'[6]-[7]0[8]|

A1||00050002|375.77|03312000||

A1||02011152|372.80|12182001||

A1||03050708|640.09|04242003||

B|O|||||Christ Community Baptist Church, a PA Non-Profit Corporation|||||||1224 North 41st Street||Philadelphia|PA|19104||DCC 803 - 221|117|

B3|Deed|1800|10211974|02141975|DCC 803 - 221|Christ Community Baptist Church, a PA Non-Profit Corporation|51|

C|G|Robert H. Mitchell and O'Kella E. Mitchell, h/w|

C|RB|CHRIST COMMUNITY BAPTIST CHURCH|

C|TA||

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

There are many different ways to do it with SSIS. It depends really on your preference and experience in it. SSIS is a tool designed for this kind of tasks.

It's hard to say how I would do it, may be, actually, I would use SSIS to run the T-SQL query which just the dumps complete pre-formatted results into the flat file... But, may be not 😀

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

george.greiner Default port Points: 1473 More actions · Answer 4

Eugene Elutin (7/6/2010)
There are many different ways to do it with SSIS. It depends really on your preference and experience in it. SSIS is a tool designed for this kind of tasks.
It's hard to say how I would do it, may be, actually, I would use SSIS to run the T-SQL query which just the dumps complete pre-formatted results into the flat file... But, may be not 😀

Lol well as soon as I get these dates formatted correctly and figuring out how to pass the identifiers to the subsets I am going to attempt doing just that =). I would have to have a query that queries that correct data for the A line correct? I tried putting a WHERE Clause for the first line but it did not work as intended. Is there any reason that should not work?

AS

(

FROM CityCollection.dbo.FileInfo

WHERE fileDone >= dateadd(hour,-24,getdate()) and fileDone <= getdate()

george.greiner Default port Points: 1473 More actions · Answer 5

I got all the date conversions figured out now I jsut need to figure this next part out and this is done!

I have ae key "interstID" that is passed from the "B" line to the "B1" and "B2" lines.

How would I go about passing that ID so that the record looks as such:

A

B (defendant)

B1 (defendants multiple addresses if applicable)

B2 (bankruptcies in regards to defendant)

B

B1

B2

The code posted above does this:

A

B

B1

B2

I am not entirely sure if it would be another join? I tried doing this:

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.eInterest C ON C.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.addresses D ON D.CaseNumber = A.CaseNumber AND D.interestID = C.interestID

UNION ALL

But I get the error:

"Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "C.interestID" could not be bound."

Not sure what that means exactly.

Any help would be great!

Thanks,

George

george.greiner Default port Points: 1473 More actions · Answer 6

george.greiner

Default port

Points: 1473

July 7, 2010 at 10:57 am

#1189751

:crazy:

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

george.greiner (7/7/2010)
:crazy:

I was on holiday, so couldn't reply. Do you still have issues with your extract?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

george.greiner Default port Points: 1473 More actions · Answer 8

Eugene Elutin (7/12/2010)
george.greiner (7/7/2010)
:crazy:
I was on holiday, so couldn't reply. Do you still have issues with your extract?

Hope you had a great holiday! I still have issues because I need to be able to pass a second key from the B line to B1 and B2 so that they can be grouped together. For instance:

A (FileInfo) Only one of these per file.

A1 (Liens) May be many per file.

A1

B (defendant) May be many.

B1 (address) May be many per defendant.

B2 (bankruptcy) May be many per defendant.

B

B1

B

B1

B3 (Documents) May be many per file.

B3

C (Judgments and other miscelaneous files that the client wants in these lines) May be many per file.

C

Current export :

A

B

B1

B2

C

It appears I overlooked that or did not explain it very well when asking for help with this in the first place. I tried a few things to no avail and am not sure how to do it using this method.

Thanks,

George

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

The following query is based on your test setup, your first attempt and my attempt to understand relationship between your tables:

/* --Setup

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#FileInfo','U') IS NOT NULL

DROP TABLE #FileInfo

--===== Create the test table with

CREATE TABLE #FileInfo

(

CaseNumber varchar(50) PRIMARY KEY CLUSTERED,

BRTNumber varchar(50),

ParcelNumber varchar(50),

Premises varchar(50),

Client varchar(50)

)

--populate table with data

INSERT INTO #FileInfo

(CaseNumber, BRTNumber, ParcelNumber, Premises, Client)

SELECT 'PHI0001','0123456789','13N12-139','1234 Market Street','Linebarger' UNION ALL

SELECT 'PHI0002','1234567890','4N8-193','234 Chestnut Street','Linebarger' UNION ALL

SELECT 'PHI0003','2345678901','6N10-235','1600 JFK Blvd','Linebarger'

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#municipalLiens','U') IS NOT NULL

DROP TABLE #municipalLiens

--===== Create the test table with

CREATE TABLE #municipalLiens

(

MuniKey int PRIMARY KEY CLUSTERED,

CaseNumber varchar(50),

LienNumber varchar(50),

LienDate datetime,

LienReason varchar(50),

LienAmount money

)

--populate table with data

INSERT INTO #municipalLiens

(MuniKey, CaseNumber, LienNumber, LienDate, LienReason, LienAmount)

SELECT '1','PHI0001','05263565','07/29/1982','Replace Curb Stop', 256.32 UNION ALL

SELECT '2','PHI0001','98765436','05/06/1989','Turn off water meter', 34.00 UNION ALL

SELECT '3','PHI0002','69874963','08/28/2008','Replace Curb Stop', 400.65 UNION ALL

SELECT '4','PHI0003','98767655','08/30/1984','Replace Curb Stop', 256.32 UNION ALL

SELECT '5','PHI0003','44444444','05/06/1995','Turn off water meter', 34.00 UNION ALL

SELECT '6','PHI0003','23423521','08/28/2010','Replace Curb Stop', 400.65

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#Documents','U') IS NOT NULL

DROP TABLE #Documents

--===== Create the test table with

CREATE TABLE #Documents

(

documentID int PRIMARY KEY CLUSTERED,

CaseNumber varchar(50),

recDate datetime,

docDate datetime,

docAmount varchar(50),

interestID varchar(50)

)

--populate table with data

INSERT INTO #Documents

(documentID, CaseNumber, recDate, docDate, docAmount, interestID)

SELECT '1','PHI0001','07/28/1982','07/29/1982',100000.00, '1' UNION ALL

SELECT '2','PHI0001','05/06/1989','05/06/1989',50000.00, '2' UNION ALL

SELECT '3','PHI0002','08/28/2008','09/15/2008',25000.00, '3' UNION ALL

SELECT '4','PHI0002','08/28/2008','08/30/2008',10000.00, '4' UNION ALL

SELECT '5','PHI0002','05/10/1995','05/06/1995',5000.00, '5' UNION ALL

SELECT '6','PHI0003','08/28/2010','08/28/2010',2500.00, '6'

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#eInterests','U') IS NOT NULL

DROP TABLE #eInterests

--===== Create the test table with

CREATE TABLE #eInterests

(

interestID int PRIMARY KEY CLUSTERED,

CaseNumber varchar(50),

eInterestFirst varchar(50),

eInterestMiddle varchar(50),

eInterestLast varchar(50),

eInterestAlias varchar(50)

)

--populate table with data

INSERT INTO #eInterests

(interestID, CaseNumber, eInterestFirst, eInterestMiddle, eInterestLast, eInterestAlias)

SELECT '1','PHI0001','George','Q','Greiner', '' UNION ALL

SELECT '2','PHI0001','Joe','Z','Smith', 'JZS' UNION ALL

SELECT '3','PHI0002','Lynne','','Tymonko', 'Lynn T. Tymonko' UNION ALL

SELECT '4','PHI0002','Bob','P','Pablano', '' UNION ALL

SELECT '5','PHI0002','Aaron','B','Cohen', '' UNION ALL

SELECT '6','PHI0003','Orbit','T','Trommer', ''

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#addresses','U') IS NOT NULL

DROP TABLE #addresses

--===== Create the test table with

CREATE TABLE #addresses

(

AddressID int PRIMARY KEY CLUSTERED,

CaseNumber varchar(50),

interestID int,

iAddress varchar(50),

iCity varchar(50),

iState varchar(50),

izip varchar(10)

)

INSERT INTO #addresses

(AddressID, CaseNumber, interestID, iAddress, iCity, iState, iZip)

SELECT '1','PHI0001','2','234 Walnut Street','Philadelphia', 'PA', '19103' UNION ALL

SELECT '2','PHI0003','6','5722 Filbert STreet','Philadelphia', 'PA', '19115'

--===== If the test table already exists, drop it -- note that this is from another database

IF OBJECT_ID('TempDB..#judgments','U') IS NOT NULL

DROP TABLE #judgments

--===== Create the test table with

CREATE TABLE #judgments

(

judgmentID int PRIMARY KEY CLUSTERED,

CaseNumber varchar(50),

JAmt varchar(50),

CaseID nvarchar(50),

Jdate datetime,

plaintiff_info varchar(255),

defendant_info varchar(255)

)

--populate table with data

INSERT INTO #judgments

(judgmentID, CaseNumber, JAmt, CaseID, Jdate, plaintiff_info, defendant_info)

SELECT '1','PHI0001','789.58','05632132','11/02/2002', 'Jimmy Fallon 1234 Market Street, Philadelphia, PA 19115', 'Philadelphia Traffic Court' UNION ALL

SELECT '2','PHI0001','75211.21','95125154','05/15/2007', 'Smith Bob 56 Trenton STreet', 'IRS' UNION ALL

SELECT '3','PHI0001','123123.25','85214125','03/25/2008', 'George Greiner 265 Felton Street, Glenside, PA 19038', 'Bank of America' UNION ALL

SELECT '4','PHI0002','345345.23','98761232','07/15/2009', 'Cohen Aaron B 345 Lincoln Parkway, Baltimore, MD', 'Wells Fargo' UNION ALL

SELECT '5','PHI0003','98765.36','75855552','08/17/2006', 'Michelle Smith Z', 'City of Philadelphia' UNION ALL

SELECT '6','PHI0003','12311.98','11111111','12/13/2005', 'Donavan McNabb 567 Richmond Lane, Marlton, NJ', 'Internal Revenue Service'

*/

;WITH outpQ

AS

(

SELECT 1 AS grpOrd, CAST(null AS VARCHAR(255)) AS posInGrp, 'A' AS Ord, CaseNumber, 'A|' + CaseNumber + '|' + CAST(BRTNumber AS VARCHAR(11)) + '|' + IsNull('LegalDescription', ' ') + '|' + IsNull(Cast('CoverDate' AS VARCHAR(10)), ' ') + '|' + IsNull('SumOut', ' ') AS Extract

FROM #FileInfo

UNION ALL

FROM #FileInfo A

JOIN #municipalLiens B ON B.CaseNumber = A.CaseNumber

UNION ALL

FROM #FileInfo A

JOIN #eInterests C ON C.CaseNumber = A.CaseNumber

UNION ALL

FROM #FileInfo A

JOIN #eInterests C ON C.CaseNumber = A.CaseNumber

JOIN #addresses D ON D.CaseNumber = A.CaseNumber AND D.interestid = C.interestid

--UNION ALL

--FROM #FileInfo A

--JOIN #banks E ON E.CaseNumber = A.CaseNumber

UNION ALL

SELECT 2 AS grpOrd, CAST(C.interestid AS VARCHAR(11)) AS posInGrp, 'B3', A.CaseNumber, 'B3|' + '|' + 'F.docType' + '|' + IsNull(Cast(F.DocAmount AS VARCHAR(50)), ' ') + '|' + CAST(F.docDate AS VARCHAR(11)) + '|' + CAST(F.recDate AS VARCHAR(11)) + '|' + IsNull('F.bkpage', ' ') + '|' + 'F.grantee' + Cast(F.documentID AS VARCHAR(Max)) AS Extract

FROM #FileInfo A

JOIN #eInterests C ON C.CaseNumber = A.CaseNumber

JOIN #Documents F ON F.CaseNumber = A.CaseNumber AND F.interestid = C.interestid

UNION ALL

SELECT 3 AS grpOrd, null AS posInGrp, 'C', A.CaseNumber, 'C|' + IsNull('G.grantorDef',' ') + '|' + IsNull('G.grantor', ' ') AS Extract

FROM #FileInfo A

JOIN #documents G ON G.CaseNumber = A.CaseNumber

UNION ALL

SELECT 3 AS grpOrd, null AS posInGrp, 'C1', A.CaseNumber, 'C|' + IsNull('H.TADef',' ') + '|' + IsNull('H.TaxAccts', ' ') AS Extract

FROM #FileInfo A

JOIN #FileInfo H ON H.CaseNumber = A.CaseNumber

UNION ALL

SELECT 3 AS grpOrd, null AS posInGrp, 'C2', A.CaseNumber, 'C|' + IsNull('I.RegDef',' ') + '|' + IsNull('I.Registry', ' ') AS Extract

FROM #FileInfo A

JOIN #FileInfo I ON I.CaseNumber = A.CaseNumber

--UNION ALL

--FROM #FileInfo A

--JOIN #selected_compiled_clean J ON J.CaseNumber = A.CaseNumber

--SELECT dbo_selected_compiled_clean.plaintiffName, dbo_selected_compiled_clean.plaintiffAdd1, dbo_selected_compiled_clean.plaintiffCity, dbo_selected_compiled_clean.plaintiffState, dbo_selected_compiled_clean.plaintiffZip, dbo_selected_compiled_clean.plaintiffCountry, dbo_selected_compiled_clean.defendantName, dbo_selected_compiled_clean.defendantAdd1, dbo_selected_compiled_clean.defcity, dbo_selected_compiled_clean.defState, mpiled_clean.Court, dbo_selected_compiled_clean.CaseID, dbo_selected_compiled_clean.JAmt, Format(dbo_selected_compiled_clean.JDate,'mmddyyyy') AS JDate, Format(dbo_selected_compiled_clean.revivedDate,'mmddyyyy') AS revivedDate FROM dbo_selected_compiled_clean WHERE "

)

SELECT Extract FROM outpQ ORDER BY CaseNumber, grpOrd, posInGrp, Ord

Missed columns are reaplaced with constant strings representing which column should be taken.

You can see from above query returns records in required order:

A

A1

B

B1

B

B1

B3

A

...

Where is trick?

Trick is in introducing artificial "order" columns as you need them.

To order B, B1, B2 & B3 records as you want, it should be something which group them together. First of all, the grpOrd column introduced to group all A's, B's and C's, then, inside the group, it shold be something to group B1, B2 and B3 to the parent B record. In the above case it is a InterestId, therefore you will see that in order to get B1, B2 and B3 records you cannot just join #FileInfo table to its "grand-child", you should do it via its first "child" #eInterests. Otherwise there is nothing to advise which B1's, B2's and B3's relate to which B record.

Please post a code you have done so far...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

george.greiner Default port Points: 1473 More actions · Answer 10

;WITH outpQ

AS

(

FROM CityCollection.dbo.FileInfo

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.municipalLiens B ON B.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.eInterest C ON C.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.addresses D ON D.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.banks E ON E.CaseNumber = A.CaseNumber

UNION ALL

SELECT 'B3', A.CaseNumber, 'B3|' + '|' + F.docType + '|' + IsNull(Cast(F.DocAmount AS VARCHAR(50)), '') + '|' + IsNull(replace(convert(char(10), docDate, 101), '/', ''), '') + '|' + IsNull(replace(convert(char(10), recDate, 101), '/', ''), '') + '|' + IsNull(F.bkpage, '') + '|' + F.grantee + Cast(F.documentID AS VARCHAR(Max)) AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.Documents F ON F.CaseNumber = A.CaseNumber

UNION ALL

SELECT 'C', A.CaseNumber, 'C|' + IsNull(G.grantorDef,'') + '|' + IsNull(G.grantor, '') AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.documents G ON G.CaseNumber = A.CaseNumber

UNION ALL

SELECT 'C', A.CaseNumber, 'C|' + IsNull(H.TADef,'') + '|' + IsNull(H.TaxAccts, '') AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.FileInfo H ON H.CaseNumber = A.CaseNumber

UNION ALL

SELECT 'C', A.CaseNumber, 'C|' + IsNull(I.RegDef,'') + '|' + IsNull(I.Registry, '') AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.FileInfo I ON I.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN Acme.new_judgment_system.dbo.selected_compiled_clean J ON J.CaseNumber = A.CaseNumber

)

SELECT Extract FROM outpQ ORDER BY CaseNumber, Ord

That is my current code. I am going to take a stab at applying that method to my code and see what I come up with =). Thanks again for your help it is much appreciated!

george.greiner Default port Points: 1473 More actions · Answer 11

;WITH outpQ

AS

(

SELECT 1 AS grpOrd, CAST(null AS VARCHAR(255)) AS posInGrp, 'A' AS Ord, CaseNumber, 'A|' + CaseNumber + '|' + CAST(BRTNumber AS VARCHAR(11)) + '|' + IsNull(LegalDescription, '') + '|' + IsNull(replace(convert(char(10), CoverDate, 101), '/', ''), '') + '|' + IsNull(SumOut, '') AS Extract

FROM CityCollection.dbo.FileInfo

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.municipalLiens B ON B.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.eInterest C ON C.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.eInterest C ON C.CaseNumber = A.CaseNumber

JOIN CityCollection.dbo.addresses D ON D.CaseNumber = A.CaseNumber AND D.interestid = C.interestid

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.eInterest C ON C.CaseNumber = A.CaseNumber

JOIN CityCollection.dbo.banks E ON E.CaseNumber = A.CaseNumber AND E.interestid = C.interestid

UNION ALL

SELECT 3 As grpOrd3, 'B3', A.CaseNumber, 'B3|' + '|' + F.docType + '|' + IsNull(Cast(F.DocAmount AS VARCHAR(50)), '') + '|' + IsNull(replace(convert(char(10), docDate, 101), '/', ''), '') + '|' + IsNull(replace(convert(char(10), recDate, 101), '/', ''), '') + '|' + IsNull(F.bkpage, '') + '|' + F.grantee + Cast(F.documentID AS VARCHAR(11)) AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.Documents F ON F.CaseNumber = A.CaseNumber

UNION ALL

SELECT 4 AS grpOrd, null AS posInGrp, 'C', A.CaseNumber, 'C|' + IsNull(G.grantorDef,'') + '|' + IsNull(G.grantor, '') AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.documents G ON G.CaseNumber = A.CaseNumber

UNION ALL

SELECT 4 AS grpOrd, null AS posInGrp, 'C', A.CaseNumber, 'C|' + IsNull(H.TADef,'') + '|' + IsNull(H.TaxAccts, '') AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.FileInfo H ON H.CaseNumber = A.CaseNumber

UNION ALL

SELECT 4 AS grpOrd, null AS posInGrp, 'C', A.CaseNumber, 'C|' + IsNull(I.RegDef,'') + '|' + IsNull(I.Registry, '') AS Extract

FROM CityCollection.dbo.FileInfo A

JOIN CityCollection.dbo.FileInfo I ON I.CaseNumber = A.CaseNumber

UNION ALL

FROM CityCollection.dbo.FileInfo A

JOIN Acme.new_judgment_system.dbo.selected_compiled_clean J ON J.CaseNumber = A.CaseNumber

)

SELECT Extract FROM outpQ ORDER BY CaseNumber,grpOrd, posInGrp, Ord

Ok done with that really useless meeting lol.

Here is the code I came up with trying to apply that method but I am getting this error from it : "Msg 205, Level 16, State 1, Line 1

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

EDIT: nvm fixed it.. just noticed that I left out posingrp in 2 places!

george.greiner Default port Points: 1473 More actions · Answer 12

Is it possible to use a WHERE clause using this method? If not what is the best method to query the files I need out as this will provide me with ALL of the files in the database. For instance :

WHERE DateFinished >= dateadd(hour,-24,getdate()) and DateFinished <= getdate() (note that DateFinished is a Column in the A line or FileInfo table).

Thanks,

George

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 13

george.greiner (7/13/2010)
Is it possible to use a WHERE clause using this method? If not what is the best method to query the files I need out as this will provide me with ALL of the files in the database. For instance :
WHERE DateFinished >= dateadd(hour,-24,getdate()) and DateFinished <= getdate() (note that DateFinished is a Column in the A line or FileInfo table).
Thanks,
George

First of all I would advise to introduce the variables to hold the required period boundaries and then use it in where clause. Something like :

Declare @PeriodStart Datetime

Declare @PeriodEnd Datetime

SELECT @PeriodEnd = GetDate()

,@PeriodStart = dateadd(hour,-24,getdate())

There are few ways to filter records:

1. Add DateFinished column into the select list of each statement then use WHERE clause in a final query

2. Use WHERE clause in every "unioned" select query

3. It may be the most effective way, first to select all required rows with required columns from FileInfo into temp table. Then use this temp table which will only contain required record in your UNION query.

You may find that creating indexes on the temp table will be benefitial to performance.

I don't know which method will be the best in your case. It depends on the data vloume you have and available indexes.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

george.greiner Default port Points: 1473 More actions · Answer 14

Eugene Elutin (7/13/2010)
george.greiner (7/13/2010)
Is it possible to use a WHERE clause using this method? If not what is the best method to query the files I need out as this will provide me with ALL of the files in the database. For instance :
WHERE DateFinished >= dateadd(hour,-24,getdate()) and DateFinished <= getdate() (note that DateFinished is a Column in the A line or FileInfo table).
Thanks,
George
First of all I would advise to introduce the variables to hold the required period boundaries and then use it in where clause. Something like :
Declare @PeriodStart Datetime
Declare @PeriodEnd Datetime
SELECT @PeriodEnd = GetDate()
,@PeriodStart = dateadd(hour,-24,getdate())
There are few ways to filter records:
1. Add DateFinished column into the select list of each statement then use WHERE clause in a final query
2. Use WHERE clause in every "unioned" select query
3. It may be the most effective way, first to select all required rows with required columns from FileInfo into temp table. Then use this temp table which will only contain required record in your UNION query.
You may find that creating indexes on the temp table will be benefitial to performance.
I don't know which method will be the best in your case. It depends on the data vloume you have and available indexes.

I have been able to filter the A row but all subsequent lines pull all the records as they do not have a "DateFinished" column. How do I go about pulling those? Even if I drop the FileInfo records into a temp table I am still going to get ALL of the other records in the other tables.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 15

george.greiner (7/14/2010)
...
I have been able to filter the A row but all subsequent lines pull all the records as they do not have a "DateFinished" column. How do I go about pulling those? Even if I drop the FileInfo records into a temp table I am still going to get ALL of the other records in the other tables.

What you meen by "subsequent lines pull all the records as they do not have a "DateFinished" column"? Each of your SELECT query select from FileInfo table, you said that DateFinished is a column of FileInfo. So just use it.

Something along the line:

SELECT [All Required Columns]

INTO #ReqFileInfo

FROM FileInfo

WHERE DateFinished BETWEEN @PeriodStart AND @PeriodEnd

Then use #ReqFileInfo instead of FileInfo in every query of your UNION query (CTE).

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]