title: Creating a tsql query to group history of receipts(individual IDs) as the same base on the column descriptions
body: <p>I am brand new to this website. I've been searching for answers of my problem and could not find a solution so I created an account hoping someone will guide me or show me to the right path. Here is the business problem: We have receipts that can be printed with Value amount and can also be inserted in machines to redeem the value amount. So a user will say print a receipt from machine 1 that holds 20 dollars, insert it to machine 2 to 'Play'the 20 dollars, then print out the remaining value if not all are used as a receipt. For simplicity, i will be removing Play transaction and only leaving the inserted and print transaction:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP Table #MyTable
  
  CREATE TABLE #MyTable(LocationNo Int, SessionNo Int, ReceiptNo Varchar(MAX), TransDateTime DateTime, Value Decimal, EventDescription VARCHAR(MAX))
  
  INSERT INTO #MyTable 
  VALUES (02, 888888, '1A1A1A', '2017-08-27 12:18:10.000', '20.00', 'Printed'),
  (01, 111111, '1A1A1A', '2017-08-27 12:18:11.000', '20.00', 'Inserted'),
  (01, 111111, '2A2A2A', '2017-08-27 12:18:59.000', '20.00', 'Printed'),
  
  (01, 555555, '1B1B1B', '2017-08-27 09:18:11.000', '100.00', 'Printed'),
  
  (03, 222222, '2A2A2A', '2017-08-30 04:59:02.000', '20.00', 'Inserted'),
  (03, 222222, '3A3A3A', '2017-08-30 04:59:10.000', '10.00', 'Printed'),
  (03, 222222, '4A4A4A', '2017-08-30 04:59:11.000', '10.00', 'Printed'),
  
  (05, 999999, '1B1B1B', '2017-08-29 11:59:30.000', '100.00', 'Inserted'),
  (05, 999999, '2B2B2B', '2017-08-29 11:59:32.000', '50.00', 'Printed'),
  
  (07, 777777, '1C1C1C', '2017-08-27 11:59:30.000', '20.00', 'Printed'),
  
  (08, 444444, '4A4A4A', '2017-09-02 07:00:30.000', '10.00', 'Inserted'),
  (08, 444444, '5A5A5A', '2017-09-02 07:00:40.000', '9.00', 'Printed')
  
  
  
  ;
  
  
  Select * from #MyTable
  
  
  DROP TABLE #MyTable
  </code></pre>
  
  <p>Producing this table:</p>
  
  <pre><code>LocationNo  SessionNo  ReceiptNo    TransDateTime   Value   EventDescription
  2   888888  1A1A1A  2017-08-27 12:18:10.000 20  Printed
  1   111111  1A1A1A  2017-08-27 12:18:11.000 20  Inserted
  1   111111  2A2A2A  2017-08-27 12:18:59.000 20  Printed
  1   555555  1B1B1B  2017-08-27 09:18:11.000 100 Printed
  3   222222  2A2A2A  2017-08-30 04:59:02.000 20  Inserted
  3   222222  3A3A3A  2017-08-30 04:59:10.000 10  Printed
  3   222222  4A4A4A  2017-08-30 04:59:11.000 10  Printed
  5   999999  1B1B1B  2017-08-29 11:59:30.000 100 Inserted
  5   999999  2B2B2B  2017-08-29 11:59:32.000 50  Printed
  7   777777  1C1C1C  2017-08-27 11:59:30.000 20  Printed
  8   444444  4A4A4A  2017-09-02 07:00:30.000 10  Inserted
  8   444444  5A5A5A  2017-09-02 07:00:40.000 9   Printed
  </code></pre>
  
  <p>If we follow say Receipt 1A1A1A, we see that it happens first in SessionNo 888888. SessionNo represent multiple transactions consisting of inserted and printed receipts. A person can insert then print is one Session. That same Receipt 1A1A1A will then be inserted in Machine 1 (Row 2). looking at SessionNo 11111 you will see that user inserted the 1a1a1a receipt in machine 1, then printed out the value which now becomes receipt 2A2A2A.. 2A2A2A got inserted in machine 3 (row 5), then printed out as two Receipts as 3A3A3A and 4A4A4A, and so on... But all through that process, we know that 1a1a1a are the same receipt as 2A2A2A, which is the same receipt as (3A3A3A and 4A4A4A). Basically, what I want is a query that creates a temporary id to group these multiple transaction of receipts as same receipts - showing the history of receipts being converted from inserted to being printed as new receipt No. Ultimately, I want to see this result:</p>
  
  <pre><code>TempID  LocationNo  SessionNo   ReceiptNo   TransDateTime   Value   EventDescription
  123 2   888888  1A1A1A  2017-08-27 12:18:10.000 20  Printed
  123 1   111111  1A1A1A  2017-08-27 12:18:11.000 20  Inserted
  123 1   111111  2A2A2A  2017-08-27 12:18:59.000 20  Printed
  123 3   222222  2A2A2A  2017-08-30 04:59:02.000 20  Inserted
  123 3   222222  3A3A3A  2017-08-30 04:59:10.000 10  Printed
  123 3   222222  4A4A4A  2017-08-30 04:59:11.000 10  Printed
  123 8   444444  4A4A4A  2017-09-02 07:00:30.000 10  Inserted
  123 8   444444  5A5A5A  2017-09-02 07:00:40.000 9   Printed
  245 1   555555  1B1B1B  2017-08-27 09:18:11.000 100 Printed
  245 5   999999  1B1B1B  2017-08-29 11:59:30.000 100 Inserted
  245 5   999999  2B2B2B  2017-08-29 11:59:32.000 50  Printed
  679 7   777777  1C1C1C  2017-08-27 11:59:30.000 20  Printed
  </code></pre>