| Author |
 Topic  |
|
|
tantcu
Yak Posting Veteran
USA
58 Posts |
 Posted - 08/07/2012 : 13:07:02
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
|
|
tantcu
Yak Posting Veteran
USA
58 Posts |
Posted - 08/07/2012 : 14:46:30
|
It did not work from your link
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 |
 |
|
|
tantcu
Yak Posting Veteran
USA
58 Posts |
Posted - 08/07/2012 : 14:49:34
|
It did not work from your link
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 08/07/2012 : 15:08:34
|
quote:
Originally posted by tantcu
It did not work from your link
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
it will if you use it properly
SELECT t1.*,t2.[date close]
FROM table1 t1
OUTER APPLY (SELECT TOP 1 [date close]
FROM table2
WHERE id = t1.id
AND sequence_no < t1.sequence_no
ORDER BY sequence_no DESC)t2
for making it column of table1 add new column in table1 and convert above to an update
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 08/07/2012 15:09:34 |
|
|
|
tantcu
Yak Posting Veteran
USA
58 Posts |
Posted - 08/07/2012 : 15:24:19
|
The date closed still return NULL value. I want to combine that 2 table in order it to have one table look like this
id sequence_no deposit_number date_close
80210 236 xxxxx 1/9/2010
80213 175 xxxxx 1/7/2010
........ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 08/07/2012 : 15:46:20
|
quote:
Originally posted by tantcu
The date closed still return NULL value. I want to combine that 2 table in order it to have one table look like this
id sequence_no deposit_number date_close
80210 236 xxxxx 1/9/2010
80213 175 xxxxx 1/7/2010
........
then i'm sure there something else also involved which you've not told us so far.
as i told did you first try select? once you're happy you need to do add the new column and do an update if you want values to be merged onto table1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 08/07/2012 : 16:01:52
|
this was my iilustration on your scenario
declare @Table1 table
(
id int,
sequence_no int,
[deposit number] varchar(100)
)
insert @Table1
values (80210, 236, 'xxxx'),
(80213, 175, 'xxxx'),
(80214, 79, 'xxxx')
declare @Table2 table
(
id int,
sequence_no int,
[deposit number] varchar(100),
[date close] date
)
insert @Table2
values
(80210, 234, 'xxxx', '1/8/2010'),
(80210, 235, 'xxx', '1/9/2010'),
(80210 ,236, 'xxx', NULL),
(80213, 100, NULL,'1/7/2009'),
(80213, 174,NULL, '1/7/2010'),
(80213, 175, NULL,NULL)
SELECT t1.*,t2.[date close]
FROM @table1 t1
OUTER APPLY (SELECT TOP 1 [date close]
FROM @table2
WHERE id = t1.id
AND sequence_no < t1.sequence_no
ORDER BY sequence_no DESC)t2
output
-------------------------------------------------------
id sequence_no deposit number date close
-------------------------------------------------------
80210 236 xxxx 2010-01-09
80213 175 xxxx 2010-01-07
80214 79 xxxx NULL
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
tantcu
Yak Posting Veteran
USA
58 Posts |
Posted - 08/07/2012 : 16:02:33
|
I figured out the problem. This is how it is.
SELECT
[p21_view_cash_drawer].[cash_drawer_id]
,[cash_drawer_description]
,current_sequence_no
,p21_view_cash_drawer_history.sequence_number
,[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 - 1
CROSS APPLY (SELECT TOP 1 date_closed
FROM p21_view_cash_drawer_history
WHERE cash_drawer_id = [p21_view_cash_drawer].cash_drawer_id
AND sequence_number < current_sequence_no
ORDER BY sequence_number DESC) t2
Thank you for bringing me the Cross supply function |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 08/07/2012 : 16:03:36
|
you're welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Transfer data from another table
