|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
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 Try,
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 Show quote "MS User" wrote: > 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. > > /***************************************************** > 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 > > > One car can have multiple Trip, each car start with TripType 'A' and end
with Triptype 'D', ie for a given cycle, TripDate will be minimum for TripType A and maximum for TripType D Thanks M Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:E80D20D4-E296-416F-BE2A-5ED16D9518DB@microsoft.com... > Try, > > 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" wrote: > >> 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. >> >> /***************************************************** >> 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 >> >> >> SELECT carid,
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 -- One car can have multiple Trip, each car start with TripType 'A' and end
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 Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1122046977.459551.69070@g49g2000cwa.googlegroups.com... > SELECT carid, > 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 > -- > On Fri, 22 Jul 2005 10:18:27 -0500, MS User wrote:
(snip) >For a given date, I want Cars with that date for any different TripType Hi Mike,> >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 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) Thanks Hugo:
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 Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:a6l2e1h7o57nmsekgs8ll71gvbh1tiic7e@4ax.com... > On Fri, 22 Jul 2005 10:18:27 -0500, MS User wrote: > > (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) The reason I asked for the PreviousTripDate is
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 Show quote "MS User" <sql***@sql.com> wrote in message news:ee6GNMwjFHA.3608@TK2MSFTNGP12.phx.gbl... > Thanks Hugo: > > 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:a6l2e1h7o57nmsekgs8ll71gvbh1tiic7e@4ax.com... >> On Fri, 22 Jul 2005 10:18:27 -0500, MS User wrote: >> >> (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) > > On Fri, 22 Jul 2005 16:39:41 -0500, MS User wrote:
(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 ;-> Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) 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. Hope I am clear Thanks again Mike Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:q2t2e11195lbl6jel2vlscmqfhqromd9o6@4ax.com... > On Fri, 22 Jul 2005 16:39:41 -0500, MS User wrote: > > (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 ;-> > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) 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 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 Show quote "MS User" <sql***@sql.com> wrote in message news:Oe1kM1MkFHA.2344@TK2MSFTNGP10.phx.gbl... > 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. > > Hope I am clear > > Thanks again > Mike > > > > "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message > news:q2t2e11195lbl6jel2vlscmqfhqromd9o6@4ax.com... >> On Fri, 22 Jul 2005 16:39:41 -0500, MS User wrote: >> >> (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 ;-> >> >> Best, Hugo >> -- >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) > > On Sun, 24 Jul 2005 22:59:43 -0500, MS User wrote:
>The SQL you provided did return all the trips for the GivenDate, but now the Hi Mike,>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. 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 Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) 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 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 Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:57lae1ti5gk34n4um6elpja4qou9ib9kf3@4ax.com... > On Sun, 24 Jul 2005 22:59:43 -0500, MS User wrote: > >>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 > > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) On Wed, 27 Jul 2005 14:19:07 -0500, MS User wrote:
>Many Thanks Hugo. Hi Mike,> >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) 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? Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) 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,'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. Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:e1qfe1llmvki4e4bqkpvhifbun3prspl2p@4ax.com... > On Wed, 27 Jul 2005 14:19:07 -0500, MS User wrote: > >>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? > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) Also forget to mention, this is to create a datawarehouse report from the
existing application db. Thanks Show quote "MS User" <sql***@sql.com> wrote in message news:ukHty4ukFHA.708@TK2MSFTNGP09.phx.gbl... > 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,'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:e1qfe1llmvki4e4bqkpvhifbun3prspl2p@4ax.com... >> On Wed, 27 Jul 2005 14:19:07 -0500, MS User wrote: >> >>>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? >> >> Best, Hugo >> -- >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) > > On Wed, 27 Jul 2005 15:59:12 -0500, MS User wrote:
(snip DDL and sample data - thanks for providing it, though!) Show quote >For a given date, I want Cars with that date for any different TripType and Hi Mike,>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 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 -- (Remove _NO_ and _SPAM_ to get my e-mail address) 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' Thanks again Mike Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:aovfe1tcj4tf2p1mkkgdqlcagkvno7bnif@4ax.com... > On Wed, 27 Jul 2005 15:59:12 -0500, MS User wrote: > > (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 > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) On Wed, 27 Jul 2005 16:49:57 -0500, MS User wrote:
>There are some intermediate Trip like 'A1' and 'A2' which are valid to have Hi Mike,>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' 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. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) CASE WHEN COUNT(*) - COUNT(DISTINCT a.TripType) > 0
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 Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:hj3ge19a3k7k3t0e2ci8a4tv8ibupfqr9v@4ax.com... > On Wed, 27 Jul 2005 16:49:57 -0500, MS User wrote: > >>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. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) 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,'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. Show quote "MS User" <sql***@sql.com> wrote in message news:%23tq9pMykFHA.3656@TK2MSFTNGP09.phx.gbl... > CASE WHEN COUNT(*) - COUNT(DISTINCT a.TripType) > 0 > 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:hj3ge19a3k7k3t0e2ci8a4tv8ibupfqr9v@4ax.com... >> On Wed, 27 Jul 2005 16:49:57 -0500, MS User wrote: >> >>>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. >> >> Best, Hugo >> -- >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) > > On Thu, 28 Jul 2005 14:48:15 -0500, MS User wrote:
(snip) >insert into TripMovement values(1,'A1','2005-01-04T01:15:00') Hi Mike,>insert into TripMovement values(1,'A1','2005-01-04T01:30:00') 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 So where does the TripType 'A1' mentioned above fit into your model?>four different 'TripType' (A, B, C and D) Also, the table design you posted is: >create table TripMovement( TripType is defined as "char", without length. This defaults to a length> CarID int, > TripType char, > TripDate smalldatetime >) 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!!) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) From Hugo
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 Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:m8iqe1pog6bibt5mq5mlsdl001m0in3kv7@4ax.com... > On Thu, 28 Jul 2005 14:48:15 -0500, MS User wrote: > > (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!!) > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) (Followup set to .programming only - there is really no need to keep
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 . Hi Mike,>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. 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!!) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Thanks Hugo for the help. We are still in the development/testing stage ,
soon will move into production. Thanks Again MIke Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:745te1d03guvf4b7asga6g9hfd0e0tu1va@4ax.com... > (Followup set to .programming only - there is really no need to keep > 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!!) > > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) On Tue, 2 Aug 2005 12:53:21 -0500, MS User wrote:
>Thanks Hugo for the help. We are still in the development/testing stage , Hi Mike,>soon will move into production. 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. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hugo,
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 Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:raqve1dg42avuc35cqn8tuv9tl2jra4e8s@4ax.com... > On Tue, 2 Aug 2005 12:53:21 -0500, MS User wrote: > >>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. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics