Transfer data from another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transfer data from another table

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-08-07 : 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

52326 Posts

Posted - 2012-08-07 : 13:18:13

sounds like scenario 2 here

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-08-07 : 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

58 Posts

Posted - 2012-08-07 : 14:49:34

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 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/

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-08-07 : 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

52326 Posts

Posted - 2012-08-07 : 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

52326 Posts

Posted - 2012-08-07 : 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

58 Posts

Posted - 2012-08-07 : 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

52326 Posts

Posted - 2012-08-07 : 16:03:36

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources