0

In our application we have one transaction table which contains huge data. So we decided to move old data to archive table based on the cutoff date provided(e.g. if cutoff date is 2013, then move all records before 2013 to archive table). We have following three tables Transaction_Details, Transaction_Headers and Bill_Mapping( I haven't included all tables and all columns):

enter image description here

Transaction_Details table contains ID (Transaction ID), Transaction_Header_ID and other details.

Transaction_Headers contains additional details about that transaction (e.g. Payment_Type_ID and GL_Date (we want to compare cutoff with this column) etc.)

Bill_Mapping table contains transaction mapping with other transactions (credit and debit) and Amount.

I need a stored procedure which will return all transactions based on cutoff date provided (all transactions below cutoff date).

Example: Cutoff date is 2013-11-01 00:00:00.000

Now if you see in above Transaction_Details table (screenshot), ID which are marked green have a GL_Date (which is in the Transaction_Headers table) less than cutoff date.

Now in Bill_Mapping table, you can see transaction id 5434125 is linked to 5420422 (GL_Date : 2013-10-05) and 5415415 (GL_Date: 2013-12-01). So 5420422 is below cutoff date and 5415415 is above cutoff date. Here, I don't want to move these 3 transaction ids to archive table (because even though 5434125 and 5420422 is below cutoff date. but 5434125 is partially linked to 5415415 which is above cutoff date).

So rule is, based on bill_mapping table even though if one transaction id is below cutoff date and partially mapped to any transaction which is above cutoff date. Then altogether we have don't have to move all linked transactions to archive table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mahesh
  • 61
  • 1
  • 10
  • And what about connections deeper than 1 node (hierarchical)? Do you have them as well? Eg: trans A depends on trans B which depends on trans C, hence trans A depends on trans C. – Kamil Gosciminski May 28 '18 at 17:52
  • what's the main reason for archiving? – RoMEoMusTDiE May 28 '18 at 21:15
  • It's legacy application. Transaction table contains large amount of data. So to display transaction data on site for longer transaction duration e.g. from 2008 to 2018 stored procedure is taking lot of time. So we decided to move old data to archive table. We have created one page in site for archiving where user needs to pass from date and to date(.e.g 2008 to 2010). so we will move that data to archive table and we are not going to display that data on transactions details page on site, just we have to taken care of total balance (based on gross amount). – Mahesh May 29 '18 at 06:55
  • @KamilG. Yes, we hierarchical connections. A->B->C etc. – Mahesh Jun 03 '18 at 20:01
  • I found the answer in below post: https://stackoverflow.com/questions/50619401/how-to-group-hierarchical-relationships-together-in-sql-server – Mahesh Nov 22 '18 at 19:58
  • I found the answer in below post: https://stackoverflow.com/questions/50619401/how-to-group-hierarchical-relationships-together-in-sql-server – Mahesh Feb 23 '20 at 12:55

0 Answers0