CrossTab Query... Pls 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 2000 Forums

SQL Server Development (2000)

CrossTab Query... Pls Help

Author

Topic

trusted4u
Posting Yak Master

109 Posts

Posted - 2005-12-30 : 23:26:36

Hello everybody,

CREATE TABLE [dbo].[timediff] (
[mydate] [datetime] NULL ,
[driverid] [int] NULL ,
[meter] [tinyint] NULL
) ON [PRIMARY]
GO

MyDate DriverID Meter

2005-12-16 12:10:00.000 3 1
2005-12-16 14:20:00.000 3 2
2005-12-16 16:10:00.000 3 1
2005-12-16 16:50:00.000 3 2

Assuming that the value of 1 in meter stands for meteron & 2 for meterOff, I want to get the results as follows :
DriverID MeterOn MeterOff
3 2005-12-16 12:10:00.000 2005-12-16 14:20:00.000
3 2005-12-16 16:10:00.000 2005-12-16 16:50:00.000

I tried using this query but it doesn't work.

SELECT DRIVERID,
MIN ( case METER WHEN 1 then mydate end ) 'mtron',
MIN ( case METER WHEN 2 then mydate end ) 'mtroFF'
FROM TIMEDIFF
WHERE DRIVERID = 3 --and mtron is not null
GROUP BY DRIVERID,MYDATE

This is the output of the above query :
DriverID MeterOn MeterOff
3 2005-12-16 12:10:00.000 NULL
3 NULL 2005-12-16 14:20:00.000
3 2005-12-16 16:10:00.000 NULL
3 NULL 2005-12-16 16:50:00.000

Please help...

Thanks,
- Marjo.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-31 : 10:46:29

Don't group by MYDATE.

trusted4u
Posting Yak Master

109 Posts

Posted - 2006-01-01 : 09:37:41

Thanks JSMITH for replying. I tried it but it gives me only the first row. i.e.
DriverID MeterOn MeterOff
3 2005-12-16 12:10:00.000 2005-12-16 14:20:00.000

I want to get the rest of the rows too. For Eg:

DriverID MeterOn MeterOff
3 2005-12-16 12:10:00.000 2005-12-16 14:20:00.000
3 2005-12-16 16:10:00.000 2005-12-16 16:50:00.000

Regards,
- Marjo.

trusted4u
Posting Yak Master

109 Posts

Posted - 2006-01-01 : 23:31:31

Any help will really be appreciated.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-02 : 09:33:00

I can suggest u a way, but with cursors

[Cursors are said to be for those who cannot find a solution with Queries alone]

Get all the data to a cursor, order by MyDate & Meter
Get pairs of records data to variables and insert to a temp table
(ie. 1st pair to 2 sets of variables, insert those to the temp table, then get the next pair, insert to the temp table and so on)

If there rn't better suggestions and if u couldn't understand this, I can help u.

trusted4u
Posting Yak Master

109 Posts

Posted - 2006-01-02 : 09:54:48

Thanks for the suggestion Srinika. I don't want to use cursors coz it is a huge table and and besides the meteron and meteroff, I have various other tables and columns also to take care of. I am sure there must be a way to get the desired results through sql itself.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-02 : 11:07:59

C whether the following helps:

http://www.sqlteam.com/item.asp?ItemID=11021
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 00:36:54

If possible use this and filter data

Select T1.driverid,T1.mydate as date1,T2.mydate as date2 from timediff T1
inner join timediff T2 on T1.meter<T2.meter where T1.driverid=T2.driverid

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-03 : 09:04:24

The problem with this model is that there is no way to absolutely associate a mtrOn row with a mtrOff row other than comparing dates and driverid. I don't know how efficient this will be with your large table but here is a possibility:


select	driverid
	,myDate mtron
	,mtroff = (select min(mOff.myDate) 
		from 	timediff mOff
		where	mOff.driverid = 3
		and	mOff.meter = 2
		and	mOff.myDate > mOn.myDate)
from	timediff mOn
where	driverid = 3
and	meter = 1

Be One with the Optimizer
TG

trusted4u
Posting Yak Master

109 Posts

Posted - 2006-01-03 : 11:58:50

Awesome TG

, finally you did it through sql.
While it was not working, I created few tables splitting the main table i.e. timediff and tried the following sql query
to get the same results but I am getting 20rows with this query. Where am I going wrong ???

select t1.driverid, cast(FLOOR(CAST(t1.mydate as float))AS DATETIME) , min(m1.mydate) 'MtrOn', min(m2.mydate) 'MtrOff',
t1.gpslocation 'Start Location', t2.gpslocation 'End Location'
from timediff t1
inner join timediff t2 on t1.driverid = t2.driverid
inner join mtron m1 on t1.timediffkey = m1.timediffkey --, timediffb t2
inner join mtroff m2 on t2.timediffkey = m2.timediffkey
where t1.driverid = 3 and t1.meter = 1 and t2.meter = 2
group by t1.driverid, t1.mydate , t2.mydate , t1.gpslocation, t2.gpslocation

My tables are :
CREATE TABLE [timediff] (
[TimeDiffKey] [int] IDENTITY (1, 1) NOT NULL ,
[mydate] [datetime] NULL ,
[driverid] [int] NULL ,
[meter] [tinyint] NULL,
[GPSLocation] varchar(30) NULL
) ON [PRIMARY]
GO

INSERT INTO TIMEDIFF
select '2005-12-16 08:10:00.000', 1, 1, '25 03.408N 55 10.811' UNION
select '2005-12-16 10:50:00.000', 1, 2, '25 03.410N 55 10.815' UNION
select '2005-12-16 12:10:00.000', 3, 1, '25 05.275N 55 09.315' UNION
select '2005-12-16 14:20:00.000', 3, 2, '25 05.290N 55 09.334' UNION
select '2005-12-16 16:10:00.000', 3, 1, '25 13.581N 55 17.068' UNION
select '2005-12-16 16:50:00.000', 3, 2, '25 10.427N 55 24.839' UNION
select '2005-12-16 17:00:00.000', 3, 1, '25 12.789N 55 16.636' UNION
select '2005-12-12 15:30:00.000', 8, 1, '25 10.419N 55 24.856' UNION
select '2005-12-16 17:10:00.000', 3, 2, '25 16.154N 55 18.399' UNION
select '2005-12-16 17:12:00.000', 3, 2, '25 08.491N 55 11.439' UNION
select '2005-12-16 17:15:00.000', 3, 1, '25 15.062N 55 19.933' UNION
select '2005-12-16 17:22:00.000', 3, 2, '25 15.044N 55 19.978'

CREATE TABLE [mtron] (
[MtrOnKey] [int] IDENTITY (1, 1) NOT NULL ,
[TimeDiffKey] [int],
[mydate] [datetime] NULL ,
[meter] [tinyint] NULL
) ON [PRIMARY]
GO

INSERT INTO MTRON
SELECT timediffkey,mydate,meter FROM TIMEDIFF where meter = 1

CREATE TABLE [mtroff] (
[MtrOffKey] [int] IDENTITY (1, 1) NOT NULL ,
[TimeDiffKey] [int],
[mydate] [datetime] NULL ,
[meter] [tinyint] NULL
) ON [PRIMARY]
GO

INSERT INTO MTROff
SELECT timediffkey,mydate,meter FROM TIMEDIFF where meter = 2

CREATE TABLE [eventkey] (
[EventKey] [tinyint] NULL,
[Description] [varchar(30)] NULL
) ON [PRIMARY]
GO

insert eventkey
select 1,'MeterOn' union
select 2,'MeterOff'

trusted4u
Posting Yak Master

109 Posts

Posted - 2006-01-05 : 04:43:18

Will the creation of few tables and splitting the data give me the expected results ?

- Plz guide.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources