Finding a matching data from another table with conditions

  • Hi guys,

    i'm encountering the problem on SQL. I am working on the cash balance to find the date closed based on sequence_no. The data comes from 2 table and this is how I joined them:

    SELECT

    *

    FROM

    (

    SELECT

    [p21_view_cash_drawer].[cash_drawer_id]

    ,[cash_drawer_description]

    ,current_sequence_no

    ,[opening_balance]

    ,[withdrawals]

    ,[deposits]

    ,[current_balance]

    ,[drawer_open]

    ,[p21_view_cash_drawer].[bank_no]

    ,[p21_view_cash_drawer].[cash_on_hand_account_number]

    ,[p21_view_cash_drawer_history].[date_opened]

    ,[p21_view_cash_drawer_history].date_closed

    ,[p21_view_cash_drawer].[last_maintained_by]

    FROM [P21].[dbo].[p21_view_cash_drawer]

    LEFT OUTER JOIN [p21_view_cash_drawer_history] ON ([p21_view_cash_drawer_history].cash_drawer_id = [p21_view_cash_drawer].cash_drawer_id

    AND [p21_view_cash_drawer_history].sequence_number = [p21_view_cash_drawer].current_sequence_no)

    GROUP BY

    p21_view_cash_drawer.cash_drawer_id

    ,[cash_drawer_description]

    ,current_sequence_no

    ,[opening_balance]

    ,[withdrawals]

    ,[deposits]

    ,[current_balance]

    ,[drawer_open]

    ,[p21_view_cash_drawer].[bank_no]

    ,[p21_view_cash_drawer].[cash_on_hand_account_number]

    ,[p21_view_cash_drawer_history].[date_opened]

    ,[p21_view_cash_drawer_history].date_closed

    ,[p21_view_cash_drawer].[last_maintained_by]

    ) current_cash_drawer

    the p21_cash_drawer table does not have the date closed so I joined that table with the p21_view_cash_drawer_history table. The condition is matching with id and sequence no. My question is is there a formula that can help me pull out the date closed from p21_view_cash_drawer_history where the table contain the sequence number = the current_sequen_no from the p21_cash_drawer table minus 1 (-1). For example, I look at the current_sequence_no from table cash_drawer 236. I want that sequence_no (236) has the date closed of the sequence_no 235 from the table cash_drawer_history.

    I might not explain well but help me on this.

  • What may help is if could post the DDL (CREATE TABLE) statements for the tables, some sample data that is representative of the problem you are trying to solve, and the expected results based on that sample data.

  • For example I have 2 table

    Table 1 data

    id sequence_no deposit number

    80210 236 xxxx

    80213 175 xxxx

    80214 79 xxxx

    Table 2 data

    id sequence_no deposit number date close

    80210 234 xxxx 1/8/2010

    80210 235 xxx 1/9/2010

    80210 236 xxx NULL

    80213 100 1/7/2009

    80213 174 1/7/2010

    80213 175 NULL

    Is there a way that I can have date close from sequence_no 235 from table 2 become a new field or column of table 1 that match with sequence_no 236 the same with 175 and 75. it is like date close of sequence no from table 1 (236) will have the date close of sequence no + 1 (235+1) from table 2

  • For example I have 2 table

    Table 1 data

    id sequence_no deposit number

    80210 236 xxxx

    80213 175 xxxx

    80214 79 xxxx

    Table 2 data

    id sequence_no deposit number date close

    80210 234 xxxx 1/8/2010

    80210 235 xxx 1/9/2010

    80210 236 xxx NULL

    80213 100 1/7/2009

    80213 174 1/7/2010

    80213 175 NULL

    Is there a way that I can have date close from sequence_no 235 from table 2 become a new field or column of table 1 that match with sequence_no 236 the same with 175 and 75. it is like date close of sequence no from table 1 (236) will have the date close of sequence no + 1 (235+1) from table 2

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply