August 7, 2012 at 11:08 am
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.
August 7, 2012 at 11:11 am
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.
August 7, 2012 at 12:45 pm
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
August 7, 2012 at 12:45 pm
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