Framing the SQL query - SQL Server Forums

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

Framing the SQL query

Author

Topic

visolve
Starting Member

6 Posts

Posted - 2008-07-22 : 01:24:10

Hi,

I have 2 tables master & history. on regular basis i'll update the master. On updating the master with new date, only the affected records are moved to history with the old date.

For instance, A have 4 records with date1. On updating (only 2 records gets affected) records are moved to B with date1 and modified records in A will be updated with date2.

I want to retrieve records combining both the tables as on date1 and as on date2.

Thanks
DEVI.G

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 01:26:05

use UNION or UNION ALL

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 04:33:39

select fields from MAster
UNION ALL
select fields from History

make sure same number of fields are used in both queries and corresponding fields are of same type

visolve
Starting Member

6 Posts

Posted - 2008-07-22 : 04:41:56

Thanks for your inputs..

obviously we need to go for union. The thing is selecting records based on olddate and newdate.

Let me say, A is the master(contains 4 records) with date date1. Now i need to update A. on Updating(only 2 records gets updated) move only the affected records from A to History table B. Now the history table contains the records with olddate. While the A table contains both olddate and newdate rows. I need to retrieve records as on olddate ( so 2 records from A and 2 from be should get selected) and if as on new date (all the records from A should get selected)

.. Hope i made it clear.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 04:43:27

quote:
Originally posted by visolve

Thanks for your inputs..

obviously we need to go for union. The thing is selecting records based on olddate and newdate.

Let me say, A is the master(contains 4 records) with date date1. Now i need to update A. on Updating(only 2 records gets updated) move only the affected records from A to History table B. Now the history table contains the records with olddate. While the A table contains both olddate and newdate rows. I need to retrieve records as on olddate ( so 2 records from A and 2 from be should get selected) and if as on new date (all the records from A should get selected)

.. Hope i made it clear.

dont you have a extracteddate/datein field in history to designate when the record was moved to it?

visolve
Starting Member

6 Posts

Posted - 2008-07-22 : 06:00:46

ya i have..

say table on 2008-07-10

A (master table)

Field1 lastUpdate
------------------
a 2008-07-10
b 2008-07-10
c 2008-07-10
d 2008-07-10

B (history)
Field1 lastUpdate
------------------

On 2008-06-20 i'm updating the Master....(only a & b records gets updated)
So as on 2008-06-20... My table look like
A (Master)
Field1 lastUpdate
------------------
a 2008-07-20
b 2008-07-20
c 2008-07-10
d 2008-07-10
B (history)
Field1 lastUpdate
------------------
a 2008-07-10
b 2008-07-10

...In this senario, i want to retrieve records as on 10th and 20th june. How can i implement this.. It was eating my time from the past 2 days...

visolve
Starting Member

6 Posts

Posted - 2008-07-22 : 06:02:12

sorry it is like .. how can i retrieve records as on 10 th and 20th july.. i wrongly typed as june...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-07-22 : 09:35:58

visolve, a better practice it to have your archive table store a history of all record states, including the current one. Then, you don't need to UNION at all. You use the production table when you only want current values, but you use the archive/history table for all historical reporting.

e4 d5 xd5 Nf6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 09:43:50

quote:
Originally posted by visolve

ya i have..

say table on 2008-07-10

A (master table)

Field1 lastUpdate
------------------
a 2008-07-10
b 2008-07-10
c 2008-07-10
d 2008-07-10

B (history)
Field1 lastUpdate
------------------

On 2008-06-20 i'm updating the Master....(only a & b records gets updated)
So as on 2008-06-20... My table look like
A (Master)
Field1 lastUpdate
------------------
a 2008-07-20
b 2008-07-20
c 2008-07-10
d 2008-07-10
B (history)
Field1 lastUpdate
------------------
a 2008-07-10
b 2008-07-10

...In this senario, i want to retrieve records as on 10th and 20th june. How can i implement this.. It was eating my time from the past 2 days...

select fields
from
(
select otherfields,lastUpdate from Master
union all
select otherfields,lastUpdate from History
)t
where t.lastUpdate=@Date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources