November 24, 2008 at 2:17 pm
Im looking for a way to pull and export the last three transactions for a specific contract. The table 'mdtrans' has a key 'mdtrans.id' that is hooked to the 'salescontract' table.
The fields I would like to pull are mdtrans.transdate, mdtrans.amount, mdtrans.type and mdtrans.TRXNO. I can find the Max(transdate) but after that I cant seem to google anything to help. Any direction would be so much appreciated. Here is what I have sofar......
SELECT lettername
,Debtor
,Debtor
,nameline2
,addressLine3
,city
,state
,zip
,EDaccount
,PastDueToday
,servicedate
,DueDate
,PastDueDate
,annualamount
,currentactivity
,newbalance
,newestdate
FROM (
SELECT 'stal' as lettername,contact.fname + ' '+ contact.lname AS Debtor
,case
when contact.fname2 is NULL then convert(char(1), '')
else contact.fname2 + ' ' + contact.lname2
end as nameLine2,
contact.address as addressLine3,
contact.city as city,
contact.state as state,
contact.zip as zip,
salescontract.resortid as resort,
convert(varchar(5), salescontract.ownerid) + '-' + convert(varchar(6), salescontract.contractid) as EDaccount,
CONVERT(NVARCHAR(10), GETDATE(), 101) as servicedate,
convert(varchar(2), MONTH(CURRENT_TIMESTAMP)+1)+ '/'+convert(varchar(2),'01')+'/'+('2009') as DueDate,
convert(varchar(2), MONTH(CURRENT_TIMESTAMP)+1)+ '/'+convert(varchar(2),'11')+'/'+('2009') as PastDueDate,
(select (convert(decimal(9,2),mdtranstype.calculation )*4) from mdtranstype where salescontract.user3 = mdtranstype.name) as annualamount,
(select (convert(decimal(9,2),mdtranstype.calculation )) from mdtranstype where salescontract.user3 = mdtranstype.name) as currentactivity,
(select (convert(decimal(9,2),mdtranstype.calculation )) from mdtranstype where salescontract.user3 = mdtranstype.name)+(mdtrans_total + COALESCE(payments_total,.0000)) as newbalance,
(select CONVERT(NVARCHAR(10), max(transdate), 101) from mdtrans where DATEDIFF("dd",transdate,GETDATE()) < 45 and contact.id = mdtrans.ownerid) as newestdate,
mdtrans_total + COALESCE(payments_total,.0000) As PastDueToday
FROM contact
INNER
JOIN salescontract
ON salescontract.ownerid = contact.id
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
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
) AS data
WHERE PastDueToday > 3 AND addressLine3 is not null and resort = 'ALP'
Thx in advance for any help.
Bob
November 25, 2008 at 3:33 pm
I generally use TOP to do something like that. For example:
SELECT TOP 3
id,
desc,
trans_date
FROM
tbl
WHERE
id = 'specific ID'
ORDER BY
trans_date DESC
But that seems too simple of an answer. So I bet you're looking for something else. Try simplifying your question and providing table structure and sample data.
December 1, 2008 at 12:04 pm
Hey thanks for the reply .... Actually I guess I should have explained that I need everything that is returned all on the same line. This is to send to the printers and get statements made. Hope everyone had a great Turkeyday weekend.
December 1, 2008 at 12:18 pm
I guess we really could use the DDL for your tables, some sample data, and the expected results based on the sample data. To see how you should post all that info, please read the article referenced in my signature block about How to ask for help.
It provides guidelines that will help you get the help you need.
December 1, 2008 at 12:23 pm
What version of SQL Server? SQL2005+ has some new stuff that helps will these kind of queries.
December 1, 2008 at 12:33 pm
Thanks Lynn for the post. I can certainly appreciate the need for the information spoke of in the links you posted. Im not a very good SQL'er .... more of a "google it and make it work and if that leads to a dead end ask on forum" kind of guy. I guess this will be a rite of passage for me as I have a bit of a learning curve to get past to comply with the links you sent. Thanks for the info and I will get started 🙂
December 1, 2008 at 1:26 pm
snow.surfer (12/1/2008)
What version of SQL Server? SQL2005+ has some new stuff that helps will these kind of queries.
Right now we are using SQL Server 2000 🙁
December 3, 2008 at 8:12 am
It's not particularly simple to do that stuff. But here's a sample you can work with.
-- sample table and data.
declare @tbl table (id int, trans_desc varchar(20), trans_date datetime, trans_id int identity)
insert @tbl (id, trans_desc, trans_date)
select id = 1, trans_desc = 'a', trans_date = '20080101'
union select id = 1, trans_desc = 'b', trans_date = '20080102'
union select id = 1, trans_desc = 'c', trans_date = '20080103'
union select id = 1, trans_desc = 'd', trans_date = '20080104'
-- return top 3 by trans_date for id=1 in one row.
SELECT
top1.trans_desc, top1.trans_date,
top2.trans_desc, top2.trans_date,
top3.trans_desc, top3.trans_date
FROM
(
SELECT
id,
trans_desc,
trans_date
FROM
@tbl tbl
WHERE
id = 1
and 0 = (
SELECT
count(*)
FROM
@tbl earlier
WHERE
earlier.id = tbl.id
and (
earlier.trans_date < tbl.trans_date
or (
earlier.trans_date = tbl.trans_date
and earlier.trans_id < tbl.trans_id ) ) ) ) top1
inner join (
SELECT
id,
trans_desc,
trans_date
FROM
@tbl tbl
WHERE
id = 1
and 1 = (
SELECT
count(*)
FROM
@tbl earlier
WHERE
earlier.id = tbl.id
and (
earlier.trans_date < tbl.trans_date
or (
earlier.trans_date = tbl.trans_date
and earlier.trans_id < tbl.trans_id ) ) ) ) top2
on top2.id = top1.id
inner join (
SELECT
id,
trans_desc,
trans_date
FROM
@tbl tbl
WHERE
id = 1
and 2 = (
SELECT
count(*)
FROM
@tbl earlier
WHERE
earlier.id = tbl.id
and (
earlier.trans_date < tbl.trans_date
or (
earlier.trans_date = tbl.trans_date
and earlier.trans_id < tbl.trans_id ) ) ) ) top3
on top3.id = top2.id
December 16, 2008 at 1:44 pm
Thanks dongadoy ..... this is exactly what Im looking for! I do have one question though. based on your table ... right now ....if an 'id' didnt have 3 entries it returns nothing. How could I alter your code to make it show up to 3 entries? In other words, allow the code to return the info it has even if there is only 1 or 2 entries.
Ive been diligently trying to get this to work and Im failing. Eventually, I will be trying to make this only display the entries of the last 90 days but I need to figure out this issue first. For what its worth ... here is what I have so far .........
-- sample table and data.
declare @tbl table (contractid int, type varchar(20), transdate datetime, amount money, trxno int, trans_id int identity)
insert @tbl (contractid, type, transdate, amount, trxno)
select contractid, type, transdate, amount, trxno from dbo.mdtrans
SELECT
convert(varchar,top3.transdate,101), (select description from mdtypedescription where top3.type = mdtypedescription.id), top3.trxno, convert(decimal(9,2),top3.amount),
convert(varchar,top2.transdate,101), (select description from mdtypedescription where top2.type = mdtypedescription.id), top2.trxno, convert(decimal(9,2),top2.amount),
convert(varchar,top1.transdate,101), (select description from mdtypedescription where top1.type = mdtypedescription.id), top1.trxno, convert(decimal(9,2),top1.amount)
FROM
(
SELECT
contractid,
type,
transdate,
amount,
trxno
FROM
@tbl tbl
WHERE
contractid = 23277
and 0 = (
SELECT
count(*)
FROM
@tbl earlier
WHERE
earlier.contractid = tbl.contractid
and (
earlier.transdate > tbl.transdate
or (
earlier.transdate = tbl.transdate
and earlier.trans_id > tbl.trans_id ) ) ) ) top1
inner join (
SELECT
contractid,
type,
transdate,
amount,
trxno
FROM
@tbl tbl
WHERE
contractid = 23277
and 1 = (
SELECT
count(*)
FROM
@tbl earlier
WHERE
earlier.contractid = tbl.contractid
and (
earlier.transdate > tbl.transdate
or (
earlier.transdate = tbl.transdate
and earlier.trans_id > tbl.trans_id ) ) ) ) top2
on top2.contractid = top1.contractid
inner join (
SELECT
contractid,
type,
transdate,
amount,
trxno
FROM
@tbl tbl
WHERE
contractid = 23277
and 2 = (
SELECT
count(*)
FROM
@tbl earlier
WHERE
earlier.contractid = tbl.contractid
and (
earlier.transdate > tbl.transdate
or (
earlier.transdate = tbl.transdate
and earlier.trans_id > tbl.trans_id ) ) ) ) top3
on top3.contractid = top2.contractid
December 16, 2008 at 2:59 pm
Use "left join" instead of "inner join" on the derived tables.
December 16, 2008 at 9:26 pm
dongadoy (12/16/2008)
Use "left join" instead of "inner join" on the derived tables.
HOLY COW ... this is exactly what I need ... dongadoy .. thank you so much (_)>
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply