Need Help to Combine Two Tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help to Combine Two Tables

Author

Topic

bala_kal
Starting Member

6 Posts

Posted - 2011-08-08 : 03:37:24

Dear All,

I am very new to the SQL Server and I got a requirement in one of my current project and lack of resource I need to develop the part of SQL development.
Here is my scenario I have two tables. In Table1 it will insert the records for every 1 minute. and columns will be ( Tagname, Value, Description, Timestamp)..Similarly Table2 also having same Structure. But in Table2 record will come oftenly not like the case with Table1. Whenever a new record gets created in Table2(record will contain Previoustime and Current Time), Depending upon previoustime and currenttime of Table2 I need to fetch the records of Table1..and put in Table3 with same structure...

Kindly help me how toachieve this.

Thanks,
Kalyan

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-08-08 : 04:24:31

You should look up "trigger". Something like this:

CREATE TRIGGER trg_name ON dbo.table2
FOR INSERT
AS

INSERT INTO table3 (...)
SELECT Col1, Col2
FROM Table1 a
  INNER JOIN inserted b
    ON a.ID = b.ID
WHERE ...

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 04:45:15

you mean put the records from table1 which happens between previous and currenttime in table2? some sample data with output will help us to understand your reqmnt better

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

bala_kal
Starting Member

6 Posts

Posted - 2011-08-08 : 05:46:38

bala_kal
Starting Member

6 Posts

Posted - 2011-08-09 : 05:12:53

Can anyone suggest me How to approach this as this is very urgent.

Thanks in Advance.

Kalyan

quote:
Originally posted by bala_kal

Yes Visakh Please find the data in Table1 and Table2..This gives more clarity...

Table1

Tagname | Value | Description | Timestamp
tank1-level| 24.5665 | Good | 2011-06-14 10:10:29
tank1-temp | 25 | Good | 2011-06-14 10:10:29
tank2-level| 26.886 | Good | 2011-06-14 10:11:29
tank2-temp | 25 | Good | 2011-06-14 10:11:29
tank3-level| 29.777 | Good | 2011-06-15 11:11:20
tank3-temp | 25 | Good | 2011-06-15 11:11:20
tank4-level| 28.99 | Good | 2011-06-15 11:11:20
tank4-temp | 26 | Good | 2011-06-15 11:11:20

Table2

Tagname | Value | Description | Timestamp
Openingtime | 14/06/11/10:10:29 | Good | 2011-06-14 12:10:10
Closingtime | 15/06/11/11:11:20 | Good | 2011-06-15 12:10:10
Ticket No | 1470-1-4 | Good | 2011-06-15 12:10:10
Delivery Date| 15/06/11 | Good | 2011-06-15 12:10:10
Averate Flow | 0 bbls | Good | 2011-06-15 12:10:10
Averagetemp | -17.78 | Good | 2011-06-15 12:10:10

If you see in Table2 there are tagnames like Opening and Closing times as ( 14/06/11/10:10:29 and 15/06/11/11:11:20) this date will be created whenever new Ticket number generates at digital flowmeter.
So depending upon Opening and closing time we need to fetch the records from table1.

and put it in Table3...and later on covert these rows into columns vise.

This the actual requirment.
Kindly help me in how to acheive this.

Thanks,
Kalyan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 10:14:04

do you mean this?


select t1.*
from Table1 t1
join (select max(case when tagname = 'Openingtime' then value else null end) as start,
      max(case when tagname = 'Closingtime' then value else null end) as end
      from table2 )t2
on t1.Timestamp between t1.start and t2.end

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

bala_kal
Starting Member

6 Posts

Posted - 2011-08-09 : 18:06:15

Hi Visakh,

Could you please explain me what this query does?i am confused.

Thanks,
Kalyan

quote:
Originally posted by visakh16

do you mean this?

select t1.*
from Table1 t1
join (select max(case when tagname = 'Openingtime' then value else null end) as start,
max(case when tagname = 'Closingtime' then value else null end) as end
from table2 )t2
on t1.Timestamp between t1.start and t2.end

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

bala_kal
Starting Member

6 Posts

Posted - 2011-08-09 : 18:43:13

Dear Visakh,
Thanks for the reply. Seems you have given the query to fetch the records between opening and closing timings of table2...but not like that, just we need to fetch the records at opening time and fetch the records at closing time.
We can keep all records related to opening time in one table. and other table for records at closing time.

Thanks,
Kalyan

quote:
Originally posted by visakh16

do you mean this?

select t1.*
from Table1 t1
join (select max(case when tagname = 'Openingtime' then value else null end) as start,
max(case when tagname = 'Closingtime' then value else null end) as end
from table2 )t2
on t1.Timestamp between t1.start and t2.end

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

bala_kal
Starting Member

6 Posts

Posted - 2011-08-11 : 08:27:49

Hi All,

Can anyone correct about the statement

[b]ALTER TRIGGER [dbo].[trg_name] ON [dbo].[DFM_1]
FOR INSERT
AS
INSERT INTO Table_3(TestName3,TestValue3,TestQuality3,TestTimeStamp3)

select t1.*
from Table1 t1
join (select max(case when tagname = 'Openingtime' then value else null end) as start,
from table2 )t2
on t1.Timestamp = start;

But this statement is not inserting any values in Table_3. Only at "openingtime" of table2(at columns Tagname) get value in "Value" column(which is Date and time) and i need to fetch the records from table1(Should match with "Timestamp") and put in table TABLE_3.

Kindly provide me some inputs.

Thanks,
Kalyan

quote:
Originally posted by bala_kal

Dear Visakh,
Thanks for the reply. Seems you have given the query to fetch the records between opening and closing timings of table2...but not like that, just we need to fetch the records at opening time and fetch the records at closing time.
We can keep all records related to opening time in one table. and other table for records at closing time.

Thanks,
Kalyan

quote:
Originally posted by visakh16

do you mean this?

select t1.*
from Table1 t1
join (select max(case when tagname = 'Openingtime' then value else null end) as start,
max(case when tagname = 'Closingtime' then value else null end) as end
from table2 )t2
on t1.Timestamp between t1.start and t2.end

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 11:17:28

quote:
Originally posted by bala_kal

Dear Visakh,
Thanks for the reply. Seems you have given the query to fetch the records between opening and closing timings of table2...but not like that, just we need to fetch the records at opening time and fetch the records at closing time.
We can keep all records related to opening time in one table. and other table for records at closing time.

Thanks,
Kalyan

quote:
Originally posted by visakh16

do you mean this?

select t1.*
from Table1 t1
join (select max(case when tagname = 'Openingtime' then value else null end) as start,
max(case when tagname = 'Closingtime' then value else null end) as end
from table2 )t2
on t1.Timestamp = t1.start
or t1.Timestamp =t2.end

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

then change it like above

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources