Adding to previous row based on condition

Question

I am quite new to SQL server and I am trying to add to the previous data that I have based on a condition.

This is an example of the data that I have from 2 books so far.

LATEST6.0b1

EQOF_CNTR_N STATUS_C    LEN_Q    ABBR_VESSEL_M
AHLU1100548    F          20           p
ALQU7004370    F          20           f
AMFU8768914    F          40           g
AMFU8786693    F          40           s
AMFU8832490    F          40           y
AMFU8837379    F           40           g
AMFU8847021    F          40           d

ATB_DT             SNAP_DT           CT     STATUS_C
23/3/2017 19:45 23/3/2017 12:00       B        E
26/3/2017 8:15 23/3/2017 12:00       K        E
23/3/2017 10:15 23/3/2017 12:00       R        F
23/3/2017 3:50 23/3/2017 12:00       V        F
25/3/2017 19:50 23/3/2017 12:00       T        F
26/3/2017 17:00 23/3/2017 12:00       P        F
23/3/2017 5:50 23/3/2017 12:00       G        F

LATEST6.0b2

    EQOF_CNTR_N STATUS_C    LEN_Q    ABBR_VESSEL_M
    AHLU1100548    F          20           p
    ALQU7004370    F          20           f
    AMFU8768914    F          40           g
    AMFU8786693    F          40           s
    AMFU8832490    F          40           y
    AMFU8837379    F             40           g
    AMFU8847021    F          40           d

    ATB_DT             SNAP_DT           CT     STATUS_C
    23/3/2017 19:45 23/3/2017 12:00       B        E
    26/3/2017 8:15 23/3/2017 12:00       K        E
    23/3/2017 10:15 23/3/2017 12:00       R        F
    23/3/2017 3:50 23/3/2017 12:00       V        F
    25/3/2017 19:50 23/3/2017 12:00       T        F
    26/3/2017 17:00 23/3/2017 12:00       P        F
    23/3/2017 5:50 23/3/2017 12:00       G        F
    
    ARRIVAL_TIME
  22/3/2017 18:17
  24/3/2017 14:45
  22/3/2017 12:11
  22/3/2017 4:00
  24/3/2017 16:45
  24/3/2017 9:58
  22/3/2017 15:16

This is the code that I have run so far.

SELECT a.SNAP_DT, a.VOYAGE_OUT_N,a.ABBR_VESSEL_M, a.STATUS_C,a.CT, 
(SELECT count(*) FROM [LATEST6.0b1] y, [LATEST6.0b2] z WHERE ARRIVAL_TIME <= SNAP_DT AND y.EQOF_CNTR_N = z.EQOF_CNTR_N AND y.SNAP_DT = a.SNAP_DT AND y.VOYAGE_OUT_N = a.VOYAGE_OUT_N AND y.STATUS_C = a.STATUS_C AND y.ABBR_VESSEL_M = a.ABBR_VESSEL_M AND y.CT = a.CT) c,
(SELECT count(*) FROM [LATEST6.0b1] y, [LATEST6.0b2] z WHERE ARRIVAL_TIME <= SNAP_DT AND y.EQOF_CNTR_N = z.EQOF_CNTR_N AND y.VOYAGE_OUT_N = a.VOYAGE_OUT_N AND y.STATUS_C = a.STATUS_C AND y.ABBR_VESSEL_M = a.ABBR_VESSEL_M AND y.CT = a.CT) totalc
from
 [LATEST6.0b1] a,
 [LATEST6.0b2] b
WHERE
 a.EQOF_CNTR_N = b.EQOF_CNTR_N
GROUP BY
 a.SNAP_DT,
 a.VOYAGE_OUT_N,
    a.ABBR_VESSEL_M,
 a.STATUS_C,
 a.CT
ORDER BY
    a.CT,
    a.STATUS_C,
 a.VOYAGE_OUT_N,
 a.SNAP_DT asc;

This is the output that I am getting

SNAP_DT          VOYAGE_OUT_N    ABBR_VESSEL_M
2017-03-31 20:00:00 0006W             P
2017-04-01 00:00:00 0006W             P
2017-04-01 04:00:00 0006W             P
2017-04-07 20:00:00 0007S             Q
2017-04-08 00:00:00 0007S             Q
2017-04-10 16:00:00 0007S             Q

c  totalc
12 36
12 36
12 36
0 126
0 126
126 126

If you look at the c and totalc column, I am trying to get the numbers to add to the previous row based on the same VOYAGE_OUT_N and ABBR_VESSEL_M. Therefore, the desired output should look like this.

    SNAP_DT          VOYAGE_OUT_N    ABBR_VESSEL_M
    2017-03-31 20:00:00 0006W             P
    2017-04-01 00:00:00 0006W             P
    2017-04-01 04:00:00 0006W             P
    2017-04-07 20:00:00 0007S             Q
    2017-04-08 00:00:00 0007S             Q
    2017-04-10 16:00:00 0007S             Q

    c  totalc
    12 36
    24 36
    36 36
    0 126
    0 126
    126 126

Do you have any ideas? Thanks in advance.

You have a couple of bad habits in your code here. First you should start using ANSI-92 style joins, they have been around for more than 25 years now. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins Second is your aliases, they should be meaningful instead of a,b,c https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 — Sean Lange, Jul 20 '17 at 14:29
As for the question at hand we need some details. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Jul 20 '17 at 14:31
From sql-server 2012 you can used windowed functions. For older versions you can use a recursive query. Why did you tag ms-access? — Peter, Jul 20 '17 at 14:54
Possible duplicate of [Calculate a Running Total in SQL Server](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — Tab Alleman, Jul 20 '17 at 15:25
@SeanLange Thank you for your feedback, I will try it next time. Unfortunately, I did not create these tables, therefore I have instead added some sample data of the 2 books I am working with, would it be better if I state the possible primary and foreign keys? — Jamie, Jul 20 '17 at 15:49
@Peter I am working with ms-access as well, I was hoping I could get more ideas. — Jamie, Jul 20 '17 at 15:50
https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions — Tab Alleman, Jul 21 '17 at 11:18

Adding to previous row based on condition

0 Answers0