February 3, 2011 at 11:41 pm
I need to write a query that will get the value of a previous records and add it to the current record.
In detail:
I have a table (Table1) that contains PolicyNum, RecordID, and SequenceID
PolicyNum RecordID SequenceID
1234564560
1234564560
1234567891
1234567891
1234561232
1234561232
I have another table (Table2) with RecordID, CompanyID, Total
RecordIDCompanyID Total
4564321500
7894321700
4568765600
7898765800
1234321900
12387651000
I created a query on a inner join of the two tables on RecordID to get this.
PolicyID RecordID CompanyID SequenceID Total
1234545643210500
1234545687650600
1234578943211700
1234578987651800
1234512343212900
12345123876521000
Now the big question:
If all I have is a RecordID, how can I get the Total's of the Record's with the previous SequenceID
ie
I have RecordID 123
I need Total's from the records with SequenceID = (SequenceID) -1 (700, 800)
Then I need to add the previous amount to the current amounts (900, 1000).
RecordID CompanyID Total
123 4321 1600
123 8765 1800
Any suggestions will be helpful.
Thanks
February 4, 2011 at 2:23 am
the below query should solve your problem. implement and let me know your comments
CREATE TABLE dbo.table1(
PolicyNum INT,
RecordID INT,
SequenceID int
)
CREATE TABLE dbo.table2(
RecordIDINT,
CompanyID INT,
Total int
)
INSERT INTO table1 VALUES (123456, 456, 0)
INSERT INTO table1 VALUES (123456, 456, 0)
INSERT INTO table1 VALUES (123456, 789, 1)
INSERT INTO table1 VALUES (123456, 789, 1)
INSERT INTO table1 VALUES (123456, 123, 2)
INSERT INTO table1 VALUES (123456, 123, 2)
INSERT INTO table2 VALUES (456, 4321, 500)
INSERT INTO table2 VALUES (789, 4321, 700)
INSERT INTO table2 VALUES (456, 8765, 600)
INSERT INTO table2 VALUES (789, 8765, 800)
INSERT INTO table2 VALUES (123, 4321, 900)
INSERT INTO table2 VALUES (123, 8765, 1000)
;WITH cte1 AS (
SELECT DISTINCT table1.policynum, table1.recordid, sequenceid, companyid, total FROM table1
INNER JOIN table2 ON table1.recordid = table2.recordid
)
SELECT A.RECORDID, A.COMPANYID, SUM(ISNULL(B.TOTAL,0)+ISNULL(C.TOTAL,0)) AS total
FROM cte1 A
LEFT JOIN CTE1 B ON A.RECORDID = B.RECORDID AND a.companyid = b.companyid
LEFT JOIN CTE1 C ON b.companyid = C.companyid AND B.SEQUENCEID-1 = C.SEQUENCEID
WHERE B.RECORDID = 123
GROUP BY a.recordid, a.companyid
--DROP TABLE table2
--DROP TABLE table1
Ganesh
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply