| MS User 2005-07-21, 1:23 pm |
| Thanks Steve
Now I got to solve another issue, as you remember TripType start with A->
B-> C-> D
For a given day , if any of the TripType happen (A, B, C or D), I need to
populate that carID and all the previous TripDates till A
Hope I am clear
Thanks
Mike
"Steve Kass" <skass@drew.edu wrote in message
news:uxbMrMMiFHA.2904@tk2msftngp13.phx.gbl...
If you want the number of different CarID values having a
'B', 'C', and 'D' rows with TripDate on a given day, this
should work:
declare @d smalldatetime
set @d = '20050202'
select
count(case when TripType = 'B' then CarID end) as MovedToB,
count(case when TripType = 'C' then CarID end) as MovedToC,
count(case when TripType = 'D' then CarID end) as MovedToD
from (
select distinct
CarID, TripType
from TripMovement
where TripDate = @d
and TripDate < @d + 1
) C
Here is another way to write it, which will probably not run as
efficiently:
select
count(distinct case when TripType = 'B' then CarID end) as MovedToB,
count(distinct case when TripType = 'C' then CarID end) as MovedToC,
count(distinct case when TripType = 'D' then CarID end) as MovedToD
from TripMovement
where TripDate = @d
and TripDate < @d + 1
The DISTINCT keyword makes sure you don't count one CarID
twice for the same day if it moved around a lot.
SK
MS User wrote:
Thanks.
Can I ask you another question, which I tried today and couldn't get right
For a given day, I want the number of Cars moved from A- B and B- C and
C- D
Thanks
Mike
"Steve Kass" <skass@drew.edu wrote in message
news:%23xerRV$hFHA.1308@TK2MSFTNGP10.phx.gbl...
What this query does conceptually is
1. Identify all trip starts ('A' rows)
2. Attach the associated trip start date to each row of your table.
This is
found by matching the CarID, and taking the most recent preceding trip
start.
I thought of this as a "peg date" for the trip segment.
3. Group the results on (CarID, PegDate), i.e., trips, pivoting out the
A, B, C, and D
trip segments into columns
No magic, I'm afraid.
If you have a clustered index on (CarID, TripDate), it shouldn't be too
slow.
An secondary nonclustered index on (TripType) in addition might help,
too.
Better yet might be a data model that identifies the notion of "trip" in
addition
to trip segment. That way you could make the association between a
segment and which trip it was part of when the data is inserted, instead
of
having to sort it all out afterwards.
SK
MS User wrote:
Thanks Steve
This table holds 40 million records and the query is running too long. I
tested in a table with less data and the results are as EXPECTED.
I am still in the process of understanding this MAGIC SQL.
Mike
"Steve Kass" <skass@drew.edu wrote in message
news:OmtKjX9hFHA.1788@TK2MSFTNGP12.phx.gbl...
I remembered the previous thread, so I searched groups.google.com
for my name, sqlserver, and a couple of likely keywords.
SK
MS User wrote:
Steve:
Another question, HOW did you search this previous thread so
fast.
Thanks
Mike
"Steve Kass" <skass@drew.edu wrote in message
news:eLhvhT8hFHA.1372@TK2MSFTNGP10.phx.gbl...
Mike (Joe?),
(I assume you are the same person who started the thread
http://groups.google.co.uk/groups? ...t+carnum
as Joe.)
This should answer your question:
create table TripMovement(
CarID int,
TripType char,
TripDate smalldatetime
)
insert into TripMovement values(1,'A','2005-01-01T02:00:00')
insert into TripMovement values(1,'B','2005-01-01T03:00:00')
insert into TripMovement values(1,'D','2005-01-03T01:00:00')
insert into TripMovement values(2,'A','2005-01-01T06:00:00')
insert into TripMovement values(2,'D','2005-01-05T02:00:00')
insert into TripMovement values(1,'A','2005-01-10T04:00:00')
insert into TripMovement values(3,'A','2005-02-01T05:00:00')
insert into TripMovement values(3,'C','2005-02-02T06:00:00')
insert into TripMovement values(2,'A','2005-02-02T02:00:00')
insert into TripMovement values(2,'B','2005-02-02T03:00:00')
insert into TripMovement values(2,'C','2005-02-02T03:00:00')
select
CarID,
max(case when TripType = 'A' then TripDate end) as TripA_Date,
max(case when TripType = 'B' then TripDate end) as TripB_Date,
max(case when TripType = 'C' then TripDate end) as TripC_Date,
max(case when TripType = 'D' then TripDate end) as TripD_Date
from (
select
Steps.CarID,
max(Starts.TripDate) as PegDate,
Steps.TripType,
Steps.TripDate
from (
select
CarID,
TripDate
from TripMovement
where TripType = 'A'
) as Starts
join TripMovement as Steps
on Steps.CarID = Starts.CarID
and Steps.TripDate = Starts.TripDate
group by
Steps.CarID,
Steps.TripType,
Steps.TripDate
) S
group by CarID, PegDate
order by CarID, PegDate
go
-- drop table TripMovement
-- Steve Kass
-- Drew University
-- AB250E1C-651E-4E40-8E10-744801591B47
MS User wrote:
SQL 2K
I have a table 'TripMovement' with columns
CarID, TripType, TripDate, ....... (These three columns form the
PRIMARY-KEY)
Each trip will have an entry in table 'TripMovement' , there are
four different 'TripType' (A, B, C and D)
For a trip cycle, Trip will start with type 'A' - 'B' - 'C' and
'D' (ie TripType A will have a MIN (TripDate) and TripType D will
have a MAX(TripDate)
Each trip will have a type 'A' but not necessarly 'B' and 'C' and
will end at 'D'.
One CarID can have mutiple trips.
Here is the sample data
CarID TripType TripDate
1 A 01/01/2005 2:00
1 B 01/01/2005 3:00
1 D 01/03/2005 1:00
2 A 01/01/2005 6:00
2 D 01/05/2005 2:00
1 A 01/10/2005 4:00
3 A 02/01/2005 5:00
3 C 02/02/2005 6:00
2 A 02/02/2005 2:00
2 B 02/02/2005 3:00
2 C 02/02/2005 3:00
Desired output
CarID TripA_Date TripB_Date TripC_Date
TripD_Date
1 01/01/2005 2:00 01/01/2005 3:00 NULL 01/03/2005
1:00
1 01/10/2005 4:00 NULL NULL NULL
2 01/01/2005 6:00 NULL NULL
01/05/2005 2:00
2 02/02/2005 2:00 02/02/2005 3:00 02/02/2005 3:00
NULL
3 02/01/2005 5:00 NULL 02/02/2005
6:00 NULL
Thanks In Advance
Mike
|