Trying to INSERT

Question

Post reply

Trying to INSERT

riverswillbeer

SSC Enthusiast

Points: 194
More actions
December 23, 2008 at 12:49 pm

#73419

Happy Holidays everyone .
I am trying to insert some values into an exsisting table based on a contractid. The key for the target table is dbo.alpstatements.contractid.
CREATE TABLE AlpStatements
(
contractid varchar(50)
,lettername varchar(50) default 'STAL'
,debtor varchar(100)
,name1 varchar(100)
,name2 varchar(100)
,address varchar(150)
,city varchar(50)
,state varchar(50)
,zip varchar(50)
,acct varchar(50)
,pastdue varchar(50)
,servicedate varchar(50)
,duedate varchar(50)
,pastduedate varchar(50)
,annualamt varchar(50)
,currentact varchar(50)
,newbalance varchar(50)
,transtop3 varchar(50)
,typetop3 varchar(50)
,trxnotop3 varchar(50)
,amounttop3 varchar(50)
,transtop2 varchar(50)
,typetop2 varchar(50)
,trxnotop2 varchar(50)
,amounttop2 varchar(50)
,transtop1 varchar(50)
,typetop1 varchar(50)
,trxnotop1 varchar(50)
,amounttop1 varchar(50))
Im first INSERT'ing data into the table with this .....
INSERT INTO alpstatements (contractid
,lettername
,Debtor
,name1
,name2
,address
,city
,state
,zip
,acct
,pastdue
,servicedate
,duedate
,pastduedate
,annualamt
,currentact
,newbalance)
select
contractid
,lettername
,Debtor
,name1
,name2
,address
,city
,state
,zip
,acct
,pastdue
,servicedate
,duedate
,pastduedate
,annualamt
,currentact
,newbalance
FROM (
SELECT contact.fname + ' '+ contact.lname AS Debtor
,contact.fname + ' '+ contact.lname AS name1
,case
when contact.fname2 is NULL then convert(char(1), '')
else contact.fname2 + ' ' + contact.lname2
end as name2,
'STAL' as lettername,
contact.address as address,
contact.city as city,
contact.state as state,
contact.zip as zip,
salescontract.id as contractid,
convert(varchar(5), salescontract.ownerid) + '-' + convert(varchar(6), salescontract.contractid) as acct,
CONVERT(NVARCHAR(10), GETDATE(), 101) as servicedate,
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE())),101) as duedate,
CONVERT(NVARCHAR(10), DATEADD(Day, 10, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE())),101)), 101) as pastduedate,
(select (convert(decimal(9,2),mdtranstype.calculation )*4) from mdtranstype where salescontract.user3 = mdtranstype.name and salescontract.resortid ='ALP') as annualamt,
(select (convert(decimal(9,2),mdtranstype.calculation )) from mdtranstype where salescontract.user3 = mdtranstype.name and salescontract.resortid ='ALP') as currentact,
(select (convert(decimal(9,2),mdtranstype.calculation )) from mdtranstype where salescontract.user3 = mdtranstype.name)+(convert(decimal(9,2),mdtrans_total + COALESCE(payments_total,.0000))) as newbalance,
convert(decimal(9,2),mdtrans_total + COALESCE(payments_total,.0000)) As pastdue
FROM contact
INNER
JOIN salescontract
ON salescontract.ownerid = contact.id and salescontract.resortid ='ALP' and user1 = 'ALP'
AND salescontract.canceldate IS NULL
INNER
JOIN ( SELECT contractid
, SUM(amount) AS mdtrans_total
FROM mdtrans
WHERE DATEDIFF("dd",transdate,GETDATE()) > 10
GROUP
BY contractid ) AS Humpty
ON Humpty.contractid = salescontract.id and salescontract.resortid ='ALP' and user1 = 'ALP'
LEFT OUTER
JOIN ( SELECT contractid
, SUM(amount) as payments_total
FROM mdtrans
WHERE DATEDIFF("dd",transdate,GETDATE()) <= 10
AND mdtrans.amount < 0
GROUP
BY contractid ) AS Dumpty
ON Dumpty.contractid = salescontract.id and salescontract.resortid ='ALP' and user1 = 'ALP'
) AS data
WHERE newbalance > 3 AND address is not null
Everything is groovy so far
But to get the rest of the data I have to make a temp table and Im unable to figure out how to get the data into the dbo.alpstatements.
I have be able to pick one contractid to insert but if I try anything else it just works and works and so far I havent seen it finish.
Below is what I cant get to work ..... If anyone has any ideas please post .... thanks and be safe!
declare @tbl table (contractidd int, type varchar(20), transdate datetime, amount money, trxno int, resortid varchar(10),trans_id int identity)
insert @tbl (contractidd, type, transdate, amount, trxno, resortid)
select mdtrans.contractid, type, transdate, amount, trxno,resortid from dbo.mdtrans where DATEDIFF("dd",transdate,GETDATE()) < 90 and resortid = 'ALP'
UPDATE AlpStatements
SET transtop3=convert(varchar,top3.transdate,101) , transtop2=top2.transdate, transtop1=top1.transdate
FROM
(
SELECT
contractidd,
type,
transdate,
amount,
trxno,
resortid
FROM
@tbl tbl
WHERE
0 = (
SELECT
count(*)
FROM
@tbl earlier
WHERE
earlier.contractidd = tbl.contractidd
and (
earlier.transdate > tbl.transdate
or (
earlier.transdate = tbl.transdate
and earlier.trans_id > tbl.trans_id ) ) ) ) top1
left join (
SELECT
contractidd,
type,
transdate,
amount,
trxno,
resortid
FROM
@tbl tbl
WHERE
1 = (
SELECT
count(*)
FROM
@tbl earlier
WHERE
earlier.contractidd = tbl.contractidd
and (
earlier.transdate > tbl.transdate
or (
earlier.transdate = tbl.transdate
and earlier.trans_id > tbl.trans_id ) ) ) ) top2
on top2.contractidd = top1.contractidd
left join (
SELECT
contractidd,
type,
transdate,
amount,
trxno,
resortid
FROM
@tbl tbl
WHERE
2 = (
SELECT
count(*)
FROM
@tbl earlier
WHERE
earlier.contractidd = tbl.contractidd
and (
earlier.transdate > tbl.transdate
or (
earlier.transdate = tbl.transdate
and earlier.trans_id > tbl.trans_id ) ) ) ) top3
on top3.contractidd = top2.contractidd
where top1.contractidd = alpstatements.contractid or
top2.contractidd = alpstatements.contractid or
top3.contractidd = alpstatements.contractid

Viewing 2 posts - 1 through 2 (of 2 total)

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

I can't run this to look at your query plan, or even the results of all your subqueries,

but here's something to try in your update. It looks like your contractidd is

always going to be the same for alpstatements, top1, top2, and top3. Change your

FROM clause to this format

FROM AlpStatements a -- (yes it's legal)

LEFT JOIN (select contractidd, type, etc, etc,) top1

ON top1.contractidd = alpstatements.contractid

LEFT JOIN (select contractidd, type, etc, etc,) top2

ON top2.contractidd = alpstatements.contractid

LEFT JOIN (select contractidd, type, etc, etc,) top3

ON top3.contractidd = alpstatements.contractid

Leave your WHERE clause at the bottom, or, in a fit of holiday madness, use COAELESCE! :w00t:

WHERE COALESCE(top1.contractiddd, top2.contractidd,top3.contractidd) IS NOT NULL

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills