SQL HELP
MS User
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.
/*****************************************************
create table TripMovement(
CarID int,
TripType char,
TripDate smalldatetime
)
insert into TripMovement values(1,'A','2005-01-01 02:00:00')
insert into TripMovement values(1,'B','2005-01-01 03:00:00')
insert into TripMovement values(1,'D','2005-01-03 01:00:00')
insert into TripMovement values(2,'A','2005-01-01 06:00:00')
insert into TripMovement values(2,'D','2005-01-05 02:00:00')
insert into TripMovement values(1,'A','2005-01-10 04:00:00')
insert into TripMovement values(3,'A','2005-02-01 05:00:00')
insert into TripMovement values(3,'C','2005-02-02 06:00:00')
insert into TripMovement values(2,'A','2005-02-02 02:00:00')
insert into TripMovement values(2,'B','2005-02-02 03:00:00')
insert into TripMovement values(2,'C','2005-02-02 03:00:00')
*********************************************************************
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
For a given date, I want Cars with that date for any different TripType
For example , if the given date is '01/01/2005' , the desired output
CarID ATripDate BTripDate CTripDate
DTripDate
1 01/01/2005 2:00 01/01/2005 3:00 NULL NULL
2 01/01/2005 6:00 NULL NULL
NULL
Thanks In Advance
Mike
David Portas
MAX(CASE WHEN triptype = 'A' THEN tripdate END),
MAX(CASE WHEN triptype = 'B' THEN tripdate END),
MAX(CASE WHEN triptype = 'C' THEN tripdate END),
MAX(CASE WHEN triptype = 'D' THEN tripdate END)
FROM TripMovement AS T
WHERE tripdate >= '20050101'
AND tripdate < '20050102'
GROUP BY carid ;
--
David Portas
SQL Server MVP
--
Alejandro Mesa
declare @dt datetime
set @dt = '20050101'
select
carid,
max(case when TripType = 'A' then TripDate end) as ATripDate,
max(case when TripType = 'B' then TripDate end) as BTripDate,
max(case when TripType = 'C' then TripDate end) as CTripDate
from
TripMovement
where
TripDate >= convert(varchar(8), @dt, 112)
and TripDate < dateadd(day, 1, convert(varchar(8), @dt, 112))
group by
carid
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574&Product=sql
AMB
MS User
with Triptype 'D',
ie for a given cycle, TripDate will be minimum for TripType A and maximum
for TripType D
Thanks
M
"Alejandro Mesa" <Alejan...@discussions.microsoft.com> wrote in message
news:E80D20D4-E296-416F...@microsoft.com...
MS User
with Triptype 'D',
ie for a given cycle, TripDate will be minimum for TripType A and maximum
for TripType D
** For a given date , one car can have multiple trips and return set should
have mutiple records for the same car.
Mike
"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message
news:1122046977....@g49g2000cwa.googlegroups.com...
Hugo Kornelis
(snip)
>For a given date, I want Cars with that date for any different TripType
>
>For example , if the given date is '01/01/2005' , the desired output
>
>CarID ATripDate BTripDate CTripDate
>DTripDate
>1 01/01/2005 2:00 01/01/2005 3:00 NULL NULL
>2 01/01/2005 6:00 NULL NULL
>NULL
Hi Mike,
I believe you posted a similar problem before, back in january. The
table was then called CarMovements. And I suggested that you change your
table design to include some easier way to identify the individual
trips, or (if redesigning is not an option) use a view to get a trip
identifier. In case you've lost that view, here it is again, adapted for
the new table and column names:
CREATE VIEW BetterTripMovement
AS
SELECT CarID,
(SELECT COUNT(*)
FROM TripMovement AS b
WHERE b.CarID = a.CarID
AND b.TripType = 'A'
AND b.TripDate <= a.TripDate) AS TripNo,
TripType,
TripDate
FROM TripMovement AS a
With this view, your requirement is easy. First, to get the raw data you
need:
DECLARE @TheDate smalldatetime
SET @TheDate = '20050101'
SELECT CarID, TripNo, TripType, TripDate
FROM BetterTripMovement
WHERE TripDate >= @TheDate
AND TripDate < @TheDate + 1
Then use the standard pivotting technique to get it in the format you
specified (though it might be better to handle that in the front end):
DECLARE @TheDate smalldatetime
SET @TheDate = '20050101'
SELECT CarID,
MAX(CASE WHEN TripType = 'A' THEN TripDate END) AS ATripDate,
MAX(CASE WHEN TripType = 'B' THEN TripDate END) AS BTripDate,
MAX(CASE WHEN TripType = 'C' THEN TripDate END) AS CTripDate,
MAX(CASE WHEN TripType = 'D' THEN TripDate END) AS DTripDate
FROM BetterTripMovement
WHERE TripDate >= @TheDate
AND TripDate < @TheDate + 1
GROUP BY CarID, TripNo
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
MS User
After populating the table like
Carid ATripDate BTripDate CTripDate DTripDate
I need to fill the columns, ie if we find a DTripDate, fill the remaining
TripDates for that record.
If we find AtripDate, nothing to compute (since no previous move for that
trip)
Thanks
Mike
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:a6l2e1h7o57nmsekg...@4ax.com...
MS User
The requirement is to compute the days between each Trip, for a given day if
anyone of the Trip take-place for a carid then that carid needs to be
computed
for time taken between TripA, TripB, TripC and TripD
So on '01/01/2005' , ATrip took place - nothing to compute
BTrip took place - compute the previous
ATripDate and update the column for that car
CTrip took place - compute previous
ATripDate , BtripDate and update the column for that car
DTrip took place - compute previous
ATripDate , BtripDate, CTripDate and update the column for that car
Do I have to use a cursor to scroll each car and process it and update.
Thanks for your time.
Mike
"MS User" <sql...@sql.com> wrote in message
news:ee6GNMwj...@TK2MSFTNGP12.phx.gbl...
Hugo Kornelis
(snip)
>Do I have to use a cursor to scroll each car and process it and update.
Hi Mike,
Probably not. But you do have to provide better specs.
You already gave the CREATE TABLE statements for your table and the
INSERT statements with some sample data, and the expected output for one
specific date. My query matched your required output for that date, and
matched the output I *thought* you expected for other dates. Apparently,
my thinking was wrong :-)
Please show the expected output for some other dates, to clarify the
difference between my solution and what you need. Add extra INSERTS with
more rows of sample data if that's needed to clarify. As long as I can
copy and paste, I don't care about a few rows more ;->
MS User
requirement is to fillup the other columns in a row.
ie if the car has got a Triptype 'C' for that date, find the previous
B-TripDate and A-TripDate for the car and update the row.
Hope I am clear
Thanks again
Mike
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:q2t2e11195lbl6jel...@4ax.com...
MS User
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 C 01/03/2005 4:00
2 D 01/05/2005 2:00
1 A 01/04/2005 1:00
1 C 01/05/2005 4:00
3 A 01/02/2005 5:00
3 D 01/05/2005 7:00
2 A 01/05/2005 7:00
For a given date, I want Cars with that date for any different TripType and
it's previous TripDates (A trip start with 'A' and end with 'D')
For example , if the given date is '01/05/2005' , the desired output
CarID ATripDate BTripDate CTripDate
DTripDate
1 01/04/2005 1:00 NULL 01/05/2005 4:00 NULL
2 01/01/2005 6:00 NULL 01/03/2005 4:00
01/05/2005 2:00
2 01/05/2005 7:00 NULL NULL
NULL
3 01/02/2005 5:00 NULL NULL
01/05/2005 7:00
Thanks
Mike
"MS User" <sql...@sql.com> wrote in message
news:Oe1kM1Mk...@TK2MSFTNGP10.phx.gbl...
Hugo Kornelis
>The SQL you provided did return all the trips for the GivenDate, but now the
>requirement is to fillup the other columns in a row.
>ie if the car has got a Triptype 'C' for that date, find the previous
>B-TripDate and A-TripDate for the car and update the row.
Hi Mike,
Well, the sample data + expected output in the next message helps to
clarify it. I think I understand your requirement.
However, since you changed the sample data, but didn't provide it as a
set of INSERT statements, I couldn't test it against your new sample
data. Instead, I tested it against your original set of test data and
compared it to my interpretation of your requirements.
Oh, BTW - your specification is still unclear on some points. If the
given date is not 1/5, but 1/4, ow many rows of output would you want?
Only one row (trips with at least one row ON the date specified), or
three rows (also include trips that have at least one row BEFORE *and*
at least one row AFTER the date specified)? The solution below should
return one row in this case.
Here's my solution, again using the BetterTripMovement view I gave you
in a previous message:
DECLARE @TheDate smalldatetime
SET @TheDate = '20050101'
SELECT a.CarID,
MAX(CASE WHEN a.TripType = 'A' THEN a.TripDate END) AS
ATripDate,
MAX(CASE WHEN a.TripType = 'B' THEN a.TripDate END) AS
BTripDate,
MAX(CASE WHEN a.TripType = 'C' THEN a.TripDate END) AS
CTripDate,
MAX(CASE WHEN a.TripType = 'D' THEN a.TripDate END) AS
DTripDate
FROM BetterTripMovement AS a
WHERE EXISTS (SELECT *
FROM BetterTripMovement AS b
WHERE b.TripDate >= @TheDate
AND b.TripDate < @TheDate + 1
AND b.CarID = a.CarID
AND b.TripNo = a.TripNo)
GROUP BY a.CarID, a.TripNo
MS User
Now I have to mark other column 'IsValid' depending on
If multiple 'B' or 'C' trip in a Cycle , then that record should be marked
with 'N' for column 'IsValid'
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 C 01/03/2005 4:00
2 C 01/04/2005 2:00
2 D 01/05/2005 2:00
1 A 01/04/2005 1:00
1 C 01/05/2005 4:00
3 A 01/02/2005 5:00
3 D 01/05/2005 7:00
2 A 01/05/2005 7:00
For a given date, I want Cars with that date for any different TripType and
it's previous TripDates (A trip start with 'A' and end with 'D')
For example , if the given date is '01/05/2005' , the desired output
CarID ATripDate BTripDate CTripDate
DTripDate IsValid
1 01/04/2005 1:00 NULL 01/05/2005 4:00
NULL
2 01/01/2005 6:00 NULL 01/03/2005 4:00
01/05/2005 2:00 N
2 01/05/2005 7:00 NULL NULL
NULL
3 01/02/2005 5:00 NULL NULL
01/05/2005 7:00
Thanks Again
Mike
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:57lae1ti5gk34n4um...@4ax.com...
Hugo Kornelis
>Many Thanks Hugo.
>
>Now I have to mark other column 'IsValid' depending on
>If multiple 'B' or 'C' trip in a Cycle , then that record should be marked
>with 'N' for column 'IsValid'
>
>Here is the sample data
(snip)
Hi Mike,
And yet again, you post your sample data in a tabular format. I have
asked you several times already to post your sample data as INSERT
statements. I think some other posted have requested the same.
Repost with the sample data in the requird format (and don't forget to
use an unambiguous format for the datetimes - yyyy-mm-ddThh:mm:ss is
prefered). Then I'll have a look at your new problem.
BTW, seeing how many essentially simple requests are made hard by the
way your table is designed, shouldn't you consider changing your design?
MS User
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,'C','2005-01-03T04:00:00')
insert into TripMovement values(2,'C','2005-01-04T02:00:00')
insert into TripMovement values(2,'D','2005-01-05T02:00:00')
insert into TripMovement values(1,'A','2005-01-04T01:00:00')
insert into TripMovement values(1,'C','2005-01-05T04:00:00')
insert into TripMovement values(3,'A','2005-01-02T05:00:00')
insert into TripMovement values(3,'C','2005-01-05T07:00:00')
insert into TripMovement values(3,'C','2005-01-05T09:00:00')
insert into TripMovement values(2,'A','2005-01-05T07:00:00')
For a given date, I want Cars with that date for any different TripType and
it's previous TripDates (A trip start with 'A' and end with 'D')
Now I have to mark other column 'IsValid' depending on
If multiple 'B' or 'C' trip in a Cycle , then that record should be marked
with 'N' for column 'IsValid'
For example , if the given date is '01/05/2005' , the desired output
CarID ATripDate BTripDate CTripDate
DTripDate IsValid
1 01/04/2005 1:00 NULL 01/05/2005 4:00 NULL
2 01/01/2005 6:00 NULL 01/04/2005 2:00
01/05/2005 2:00 N
2 01/05/2005 7:00 NULL NULL
NULL
3 01/02/2005 5:00 NULL 01/05/2005 9:00 NULL
N
Thanks for your time.
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:e1qfe1llmvki4e4bq...@4ax.com...
MS User
existing application db.
Thanks
"MS User" <sql...@sql.com> wrote in message
news:ukHty4u...@TK2MSFTNGP09.phx.gbl...
Hugo Kornelis
(snip DDL and sample data - thanks for providing it, though!)
>For a given date, I want Cars with that date for any different TripType and
>it's previous TripDates (A trip start with 'A' and end with 'D')
>
>Now I have to mark other column 'IsValid' depending on
>If multiple 'B' or 'C' trip in a Cycle , then that record should be marked
>with 'N' for column 'IsValid'
>
>
>For example , if the given date is '01/05/2005' , the desired output
>
>CarID ATripDate BTripDate CTripDate
>DTripDate IsValid
>1 01/04/2005 1:00 NULL 01/05/2005 4:00 NULL
>2 01/01/2005 6:00 NULL 01/04/2005 2:00
>01/05/2005 2:00 N
>2 01/05/2005 7:00 NULL NULL
>NULL
>3 01/02/2005 5:00 NULL 01/05/2005 9:00 NULL
>N
Hi Mike,
Starting from the query I posted as reply to your previous message, we
only need to add a simple CASE expression to get the IsValid column:
DECLARE @TheDate smalldatetime
SET @TheDate = '20050105'
SELECT a.CarID,
MAX(CASE WHEN a.TripType = 'A' THEN a.TripDate END) AS
ATripDate,
MAX(CASE WHEN a.TripType = 'B' THEN a.TripDate END) AS
BTripDate,
MAX(CASE WHEN a.TripType = 'C' THEN a.TripDate END) AS
CTripDate,
MAX(CASE WHEN a.TripType = 'D' THEN a.TripDate END) AS
DTripDate,
CASE WHEN COUNT(*) - COUNT(DISTINCT a.TripType) > 0
THEN 'N' ELSE '' END AS IsValid
FROM BetterTripMovement AS a
WHERE EXISTS (SELECT *
FROM BetterTripMovement AS b
WHERE b.TripDate >= @TheDate
AND b.TripDate < @TheDate + 1
AND b.CarID = a.CarID
AND b.TripNo = a.TripNo)
GROUP BY a.CarID, a.TripNo
Best, Hugo
MS User
multiple occurrence in a cycle.
The rule is with specific TripType 'B' or 'C' having multiple occurrence in
a cycle to mark as 'N' under column 'IsValid'
Thanks again
Mike
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:aovfe1tcj4tf2p1mk...@4ax.com...
Hugo Kornelis
>There are some intermediate Trip like 'A1' and 'A2' which are valid to have
>multiple occurrence in a cycle.
>The rule is with specific TripType 'B' or 'C' having multiple occurrence in
>a cycle to mark as 'N' under column 'IsValid'
Hi Mike,
I don't understand the remark above. My query produces the expected
results from the sample data you posted. If it doesn't satisfy your real
requirements, then
1. Try to restate your requirements in another way. The explanations you
have given so far either support my query, or (in case of the above
remark) make no sense to me. Probably because English is not my native
tongue.
AND (not or!)
2. Post some more sample data (again, as INSERT statements!) and
accompanying expected results that my query fails on.
MS User
THEN 'N' ELSE '' END AS IsValid
Above condition you provided will look into all different TripTypes between
a Cycle, where I am ONLY looking for TripType 'B' and 'C'
I will provide sample data and expected results in the A.M (it's almost
midnight here)
Thanks again
Mike
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:hj3ge19a3k7k3t0e2...@4ax.com...
MS User
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,'C','2005-01-03T04:00:00')
insert into TripMovement values(2,'C','2005-01-04T02:00:00')
insert into TripMovement values(2,'D','2005-01-05T02:00:00')
insert into TripMovement values(1,'A','2005-01-04T01:00:00')
insert into TripMovement values(1,'A1','2005-01-04T01:15:00')
insert into TripMovement values(1,'A1','2005-01-04T01:30:00')
insert into TripMovement values(1,'C','2005-01-05T04:00:00')
insert into TripMovement values(3,'A','2005-01-02T05:00:00')
insert into TripMovement values(3,'C','2005-01-05T07:00:00')
insert into TripMovement values(3,'C','2005-01-05T09:00:00')
insert into TripMovement values(2,'A','2005-01-05T07:00:00')
For a given date, I want Cars with that date for any different TripType and
it's previous TripDates (A trip start with 'A' and end with 'D')
Now I have to mark other column 'IsValid' depending on
If multiple 'B' or 'C' trip in a Cycle , then that record should be marked
with 'N' for column 'IsValid'
** Please note, for carid 1 , it got multiple TripType 'A1' for a Cycle but
NOT required to mark as 'N' for column 'IsValid' .
This is only for TripType 'B' and 'C'
For example , if the given date is '01/05/2005' , the desired output
CarID ATripDate BTripDate CTripDate
DTripDate IsValid
1 01/04/2005 1:00 NULL 01/05/2005 4:00 NULL
2 01/01/2005 6:00 NULL 01/04/2005 2:00
01/05/2005 2:00 N
2 01/05/2005 7:00 NULL NULL
NULL
3 01/02/2005 5:00 NULL 01/05/2005 9:00 NULL
N
Thanks for your time.
"MS User" <sql...@sql.com> wrote in message
news:%23tq9pMy...@TK2MSFTNGP09.phx.gbl...
Hugo Kornelis
(snip)
>insert into TripMovement values(1,'A1','2005-01-04T01:15:00')
>insert into TripMovement values(1,'A1','2005-01-04T01:30:00')
Hi Mike,
Thanks for posting the sample data. However, this only has me more
confused than I was before.
In one of your previous messages, you stated:
> there are
>four different 'TripType' (A, B, C and D)
So where does the TripType 'A1' mentioned above fit into your model?
Also, the table design you posted is:
>create table TripMovement(
> CarID int,
> TripType char,
> TripDate smalldatetime
>)
TripType is defined as "char", without length. This defaults to a length
of 1 byte. As a result, the INSERT statements above would result in an
error message and no data being inserted.
I have a feeling that the problem you posted is either a simplified
version of your real problem, or an analogy to the real problem. And
now, with this next question, you're running in to the shortcomings of
the simplification / analogy. Am I right?
Anyway, since the data you posted can't be inserted in the table you
posted, AND doesn't fit well in the description of your problem you
included at the start of the thread, it doesn't help me understand where
the solution I provided earlier fails to bring up the requested output.
Using some wild guesses and probably unwarranted assuptions, I will post
a query that might maybe even do what you want. Or not - so you should
really test it very carefully.
DECLARE @TheDate smalldatetime
SET @TheDate = '20050105'
SELECT a.CarID,
MAX(CASE WHEN a.TripType = 'A' THEN a.TripDate END) AS
ATripDate,
MAX(CASE WHEN a.TripType = 'B' THEN a.TripDate END) AS
BTripDate,
MAX(CASE WHEN a.TripType = 'C' THEN a.TripDate END) AS
CTripDate,
MAX(CASE WHEN a.TripType = 'D' THEN a.TripDate END) AS
DTripDate,
CASE WHEN COUNT(*) - COUNT(DISTINCT a.TripType) > 0
THEN 'N' ELSE '' END AS IsValid
FROM BetterTripMovement AS a
WHERE EXISTS (SELECT *
FROM BetterTripMovement AS b
WHERE b.TripDate >= @TheDate
AND b.TripDate < @TheDate + 1
AND b.CarID = a.CarID
AND b.TripNo = a.TripNo)
AND a.TripType IN ('A', 'B', 'C', 'D')
GROUP BY a.CarID, a.TripNo
(untested!!)
MS User
I have a feeling that the problem you posted is either a simplified
version of your real problem, or an analogy to the real problem. And
now, with this next question, you're running in to the shortcomings of
the simplification / analogy. Am I right?
Reply
Hugo, you are right (as always)
Actually I have got many (~15) TripTypes , and each cycle got a starting
TripType and Ending TripType.
In a cycle, certain TripTypes are valid to be repeated and certain not .
TripType 'B' and 'C' are not valid to get repeated in a cycle and need to
flag as 'N' for column 'IsValid'.
Your SQL will return 'N' for column 'IsValid' for any TripTypes which are
repeated in a cycle.
Thanks Again
Mike
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:m8iqe1pog6bibt5mq...@4ax.com...
Hugo Kornelis
crossposting this to .server)
On Mon, 1 Aug 2005 09:45:43 -0500, MS User wrote:
>In a cycle, certain TripTypes are valid to be repeated and certain not .
>TripType 'B' and 'C' are not valid to get repeated in a cycle and need to
>flag as 'N' for column 'IsValid'.
>
>Your SQL will return 'N' for column 'IsValid' for any TripTypes which are
>repeated in a cycle.
Hi Mike,
If a TripType is repeated, will each repetition have a different value
for TripDate? I can't tell, since there is no PRIMARY KEY defined in the
CREATE TABLE statement you posted upthread.
If the values will be different, you can try:
DECLARE @TheDate smalldatetime
SET @TheDate = '20050105'
SELECT a.CarID,
MAX(CASE WHEN a.TripType = 'A' THEN a.TripDate END) AS
ATripDate,
MAX(CASE WHEN a.TripType = 'B' THEN a.TripDate END) AS
BTripDate,
MAX(CASE WHEN a.TripType = 'C' THEN a.TripDate END) AS
CTripDate,
MAX(CASE WHEN a.TripType = 'D' THEN a.TripDate END) AS
DTripDate,
CASE WHEN MAX(CASE WHEN a.TripType = 'B' THEN a.TripDate END)
<> MIN(CASE WHEN a.TripType = 'B' THEN a.TripDate END)
OR MAX(CASE WHEN a.TripType = 'C' THEN a.TripDate END)
<> MIN(CASE WHEN a.TripType = 'C' THEN a.TripDate END)
THEN 'N' ELSE '' END AS IsValid
FROM BetterTripMovement AS a
WHERE EXISTS (SELECT *
FROM BetterTripMovement AS b
WHERE b.TripDate >= @TheDate
AND b.TripDate < @TheDate + 1
AND b.CarID = a.CarID
AND b.TripNo = a.TripNo)
GROUP BY a.CarID, a.TripNo
(untested!!)
If a repeated trip might even have the same TripDate, then this might
work:
DECLARE @TheDate smalldatetime
SET @TheDate = '20050105'
SELECT a.CarID,
MAX(CASE WHEN a.TripType = 'A' THEN a.TripDate END) AS
ATripDate,
MAX(CASE WHEN a.TripType = 'B' THEN a.TripDate END) AS
BTripDate,
MAX(CASE WHEN a.TripType = 'C' THEN a.TripDate END) AS
CTripDate,
MAX(CASE WHEN a.TripType = 'D' THEN a.TripDate END) AS
DTripDate,
CASE WHEN SUM(CASE WHEN a.TripType = 'B' THEN 1 ELSE 0 END) > 1
OR SUM(CASE WHEN a.TripType = 'C' THEN 1 ELSE 0 END) > 1
THEN 'N' ELSE '' END AS IsValid
FROM BetterTripMovement AS a
WHERE EXISTS (SELECT *
FROM BetterTripMovement AS b
WHERE b.TripDate >= @TheDate
AND b.TripDate < @TheDate + 1
AND b.CarID = a.CarID
AND b.TripNo = a.TripNo)
GROUP BY a.CarID, a.TripNo
(also untested!!)
MS User
soon will move into production.
Thanks Again
MIke
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:745te1d03guvf4b7a...@4ax.com...
Hugo Kornelis
>Thanks Hugo for the help. We are still in the development/testing stage ,
>soon will move into production.
Hi Mike,
You WHAT?????
Gosh. I though you were trying to make the best of an inherited design
that you can't change (yet). I'm quite shocked to find that you are in
development, and yet -after all the queries I and others have provided,
kludging our way around the shortcomings of the design- have not changed
your table design.
You might have noticed that the view I introduced makes a lot of the
queries easier to write. But if you're testing with limited amounts of
data, you'll not yet be aware of the performance hit you;re taking each
time you resort to that view.
I urge you - PLEASE change your table design. Make sure that there is an
easy way to identify each TripCycle. My view (BetterTripMovement)
dynamically assigns a TripNo (a better name would have been CycloNo,
though), that can be combined with CarNo to identify individual
TripCycles. But it's far better to store the CycloNo in the table, since
it will dramatically increase the performance of many of the queries I
posted over the last weeks, and it will also probably help reduce the
chance of bad data in your system.
MS User
As I mentioned earlier, this is for a data warehousing project which
pulls data from an existing application database.
Application db cannot be changed at this point , and that is why I am forced
to use the VIEW (BetterTripMovement) .
Thanks
Mike
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:raqve1dg42avuc35c...@4ax.com...