Query Help!! - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help!!

Author

Topic

Devnull49437
Starting Member

3 Posts

Posted - 2011-04-21 : 11:09:49

Hey all I have a bunch of History Tables in my database the tables contain an exact replica of the data table with 3 extra columns action timestamps user. I want to write some stored procedures to Display the data in a certain way [old data] [ new data] so I would send in a id of the item I want history for and lets say there are 3 rows for that item I can join the rows based off the time stamp and return two rows.
Original Insert | Updated First time
Updated First time | Updated second time.

But I cannot figure out how to get the date time to work correctly. how can I look at my current records date and say give me the next on in seq. any help would be much appreciated.

Brian

This is close except I get 2 rows for the first record becuase there are two timestamps greater.

select tbl1.*, tbl2.*
from table1 as tbl1
left join table2 as tbl2 on tbl1.col_c = tbl2.col_c
where tbl1.timestamp < tbl2.timestamp and tbl1.col2_c = 117 order by tbl1.timestamp asc;

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-21 : 11:41:23

SELECT * FROM
(
SELECT col1,col2,col3, timestamp_column FROM table1 -- Get Orignal Row from Table1
WHERE col2_c = 117
UNION ALL
SELECT col1,col2,col3, timestamp_column FROM table2 -- Get Rows from history table
WHERE col2_c = 117
AND timestamp_column >=
(SELECT timestamp_column FROM table1
WHERE col2_c = 117)
)
ORDER BY timestamp_column

--------------------------
http://connectsql.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-21 : 12:34:17

If I understand the issue (big IF) then you need to write your query differently so that you aren't joining to multiple rows or, at least, not returing multiple rows. But, without knowing your data or anything it's pretty hard to offer a reasonable suggestion. Here is a link that will help you organize your question and prepare your DDL, DML and expected output:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Devnull49437
Starting Member

3 Posts

Posted - 2011-04-21 : 13:56:21

quote:
Originally posted by lionofdezert

SELECT * FROM
(
SELECT col1,col2,col3, timestamp_column FROM table1 -- Get Orignal Row from Table1
WHERE col2_c = 117
UNION ALL
SELECT col1,col2,col3, timestamp_column FROM table2 -- Get Rows from history table
WHERE col2_c = 117
AND timestamp_column >=
(SELECT timestamp_column FROM table1
WHERE col2_c = 117)
)
ORDER BY timestamp_column

--------------------------
http://connectsql.blogspot.com/

You seem to be on the right track here but I can't quite get it to work.

here is what I have working so far.

SELECT ptpj.* FROM part_types_per_job_h as ptpj-- Get Orignal Row from Table1
WHERE ptpj.part_type_per_job_c = 117
UNION
SELECT ijptpj.* FROM part_types_per_job_h as ijptpj -- Get Rows from history table
WHERE ijptpj.part_type_per_job_c = 117 AND ijptpj.action_timestamp >=(SELECT distinct action_timestamp FROM part_types_per_job_h WHERE part_type_per_job_c = 117 and part_type_per_job_h_c = ijptpj.part_type_per_job_h_c)
ORDER BY ptpj.action_timestamp

The outer select * from ( ) produces an error and so does the order by WHen I run this query this is what I get.

Row 1

24 117 21 101 12 2011-03-15 2011-05-10 1 NULL 0 2 12 2011-03-21 11:09:58.000

Row 2

17 117 21 101 12 2011-03-15 2011-05-10 1 Needed verification on bottle fill heights to get 0 2 4 2011-03-30 08:13:03.000

Row 3

22 117 21 101 12 2011-03-15 2011-05-10 1 Finished it two days ago, but it won't go away. 0 2 4 2011-03-31 15:45:55.000

What I am looking for is this.

ROW 1

24 117 21 101 12 2011-03-15 2011-05-10 1 NULL 0 2 12 2011-03-21 11:09:58.000 17 117 21 101 12 2011-03-15 2011-05-10 1 Needed verification on bottle fill heights to get 0 2 4 2011-03-30 08:13:03.000

Row 2

17 117 21 101 12 2011-03-15 2011-05-10 1 Needed verification on bottle fill heights to get 0 2 4 2011-03-30 08:13:03.000 22 117 21 101 12 2011-03-15 2011-05-10 1 Finished it two days ago, but it won't go away. 0 2 4 2011-03-31 15:45:55.000

Devnull49437
Starting Member

3 Posts

Posted - 2011-04-25 : 09:00:37

Okay Now I have it where I am getting the correct data the correct way however I am getting an extra row that I cannot seem to get rid of here is what I have

part_type_per_job_h_c part_type_per_job_c job_c part_type_c responsible_user_c release_date due_date is_finished notes is_deleted action_code_c action_user_c action_timestamp part_type_per_job_h_c part_type_per_job_c job_c part_type_c responsible_user_c release_date due_date is_finished notes is_deleted action_code_c action_user_c action_timestamp
24 117 21 101 12 2011-03-15 2011-05-10 1 NULL 0 2 12 2011-03-21 11:09:58.000 17 117 21 101 12 2011-03-15 2011-05-10 1 Needed verification on bottle fill heights to get 0 2 4 2011-03-30 08:13:03.000
17 117 21 101 12 2011-03-15 2011-05-10 1 Needed verification on bottle fill heights to get 0 2 4 2011-03-30 08:13:03.000 22 117 21 101 12 2011-03-15 2011-05-10 1 Finished it two days ago, but it won't go away. 0 2 4 2011-03-31 15:45:55.000
24 117 21 101 12 2011-03-15 2011-05-10 1 NULL 0 2 12 2011-03-21 11:09:58.000 22 117 21 101 12 2011-03-15 2011-05-10 1 Finished it two days ago, but it won't go away. 0 2 4 2011-03-31 15:45:55.000

This row here is not correct and not needed.

24 117 21 101 12 2011-03-15 2011-05-10 1 NULL 0 2 12 2011-03-21 11:09:58.000 22 117 21 101 12 2011-03-15 2011-05-10 1 Finished it two days ago, but it won't go away. 0 2 4 2011-03-31 15:45:55.000

however I cannot do a select top(2) or anything like that because It may very depending on what I am running the history for. Any Ideas?? Here is the SP

Alter PROCEDURE [dbo].[Select_old_and_Current_Ptpj_h]
@part_type_per_job_c int = 117
AS

SELECT distinct
ptpj.*, ijptpj.*
FROM part_types_per_job_h as ptpj-- Get Orignal Row from History Table
left join part_types_per_job_h as ijptpj -- Get Rows from history table
on ijptpj.part_type_per_job_c = @part_type_per_job_c AND ijptpj.action_timestamp >=
(
SELECT distinct action_timestamp
FROM part_types_per_job_h
WHERE part_type_per_job_c = @part_type_per_job_c and part_type_per_job_h_c = ijptpj.part_type_per_job_h_c and action_timestamp > ptpj.action_timestamp
)
WHERE ptpj.part_type_per_job_c = @part_type_per_job_c and ptpj.part_type_per_job_c = ijptpj.part_type_per_job_c

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources