Create report - 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

Create report

Author

Topic

hayko98
Starting Member

29 Posts

Posted - 2011-11-30 : 16:14:01

Hi All.
I have a question and I hope you can help me.
I have table like this:

Sh_ID Shift_St_Date Shift_End_Date Bus_ID Odom_OUT Odom_IN
001 2011-01-01 12:15:00.000 2011-01-01 18:00:00.000 111 100 220
002 2011-01-02 10:00:00.000 2011-01-02 12:00:00.000 111 221 290
003 2011-01-03 16:00:00.000 2011-01-03 22:00:00.000 111 120 155
004 2011-01-04 09:00:00.000 2011-01-04 11:00:00.000 111 320 400
...
This table shows odometer reading per shift .For example when bus driver starts shift odometer reads 100 miles and 200 By the end of the shift.If you notice there is a bad data in row #3.i want to write a query that will pull all garbage data.
Tnx advance

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-30 : 16:26:27

I assume bad data means odom_out < odom_in for previous shift?

Declare @t table (Sh_ID int, Shift_st_Date datetime, shift_end_date datetime, bus_id int, odom_out int, odom_in int)
insert @t values(001, '2011-01-01 12:15:00.000', '2011-01-01 18:00:00.000', 111, 100, 220);
insert @t values(002, '2011-01-02 10:00:00.000', '2011-01-02 12:00:00.000', 111, 221, 290);
insert @t values(003, '2011-01-03 16:00:00.000', '2011-01-03 22:00:00.000', 111, 120, 155);
insert @t values(004, '2011-01-04 09:00:00.000', '2011-01-04 11:00:00.000', 111, 320, 400);

With	c
as	(
	SELECT	Sh_ID, Bus_ID, Odom_Out, Odom_In, row_number() over(partition by bus_id order by Sh_ID) ord
	FROM	@t
)
SELECT	c2.Sh_ID, c2.Bus_ID, c2.Odom_Out, c2.Odom_In
FROM	c c1
JOIN	c c2
On	c1.bus_id = c2.bus_id
And	c1.sh_id = c2.sh_id - 1
WHERE	c2.odom_out < c1.odom_in;

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-11-30 : 16:47:31

Small modification to russell's solution. In case sh_id is not sequential.

Declare @t table (Sh_ID int, Shift_st_Date datetime, shift_end_date datetime, bus_id int, odom_out int, odom_in int)
insert @t values(001, '2011-01-01 12:15:00.000', '2011-01-01 18:00:00.000', 111, 100, 220);
insert @t values(002, '2011-01-02 10:00:00.000', '2011-01-02 12:00:00.000', 111, 221, 290);
insert @t values(003, '2011-01-03 16:00:00.000', '2011-01-03 22:00:00.000', 111, 120, 155);
insert @t values(004, '2011-01-04 09:00:00.000', '2011-01-04 11:00:00.000', 111, 320, 400);

With	c
as	(
	SELECT	Sh_ID, Bus_ID, Odom_Out, Odom_In, row_number() over(partition by bus_id order by Sh_ID) ord
	FROM	@t
)
SELECT	c2.Sh_ID, c2.Bus_ID, c2.Odom_Out, c2.Odom_In
FROM	c c1
JOIN	c c2
On	c1.bus_id = c2.bus_id
And	c1.ord = c2.ord - 1
WHERE	c2.odom_out < c1.odom_in

hayko98
Starting Member

29 Posts

Posted - 2011-11-30 : 17:00:49

Answering your question: Yes.
It works.
thank you very much

hayko98
Starting Member

29 Posts

Posted - 2011-11-30 : 17:08:59

Good catch.It sh_id is not sequential.
thank you Vijayisonly

hayko98
Starting Member

29 Posts

Posted - 2011-12-01 : 14:23:15

What does "c1.ord = c2.ord - 1" do?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-01 : 14:27:32

quote:
Originally posted by hayko98

What does "c1.ord = c2.ord - 1" do?

It compares the current row with the previous row.

hayko98
Starting Member

29 Posts

Posted - 2011-12-01 : 14:33:45

which column is it comparing?

hayko98
Starting Member

29 Posts

Posted - 2011-12-01 : 14:34:34

Row_number?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-01 : 14:40:31

quote:
Originally posted by hayko98

which column is it comparing?

c2.odom_out < c1.odom_in

It compares the odom_in field from the previous row with the odom_out of the current row. If odom_out < odom_in for previous shift, it is considered invalid.

hayko98
Starting Member

29 Posts

Posted - 2011-12-01 : 14:44:58

Now it is all clear for me.I am new and sorry if asked too many Q .Tnx a lot

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-12-02 : 13:50:53

Not at all. Ask away. That's what this place is for

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources