Solved

SQL Select query having a table input for a condition

Posted on 2012-03-15
9
297 Views
Last Modified: 2012-03-15
Hello,

In SQL SERVER 2008 I have a table called [Trips].

Select [iVehicleID],SUM(fTripDistance) AS KM
  FROM Trips
  WHERE [iVehicleID]=5 
and [dtDateTime]>='2011-11-17 19:01:46.300'
  GROUP BY [iVehicleID]

Open in new window

Results:
iVehicleID      KM
5                    9972

I have another table called [Conditions] where the vehicleID only appears once:

SELECT [iVehicleID],[dtDateTime]
  FROM [Conditions]

Open in new window

Results:
iVehicleID      dtDateTime
2                      2012-03-15 17:52:17.020
3                      2012-03-16 00:08:52.077
4                      2012-03-15 20:03:59.020
5                      2011-11-17 19:01:46.300
6                      2012-03-15 19:09:44.020

Question:

Conceptually, I'd like to do the following:

Select [iVehicleID],SUM(fTripDistance) AS KM
  FROM Trips
  
WHERE [iVehicleID]=(SELECT [iVehicleID] FROM [Conditions])

and [dtDateTime]>=(SELECT [dtDateTime] FROM [Conditions])

  GROUP BY [iVehicleID]

Open in new window


obviously, this is not allowed:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

but how can I make this work and such that one record is returned for each row in [Conditions]:

Final Expected results:
iVehicleID      KM
2                    8974
3                    2654
4                    8954
5                    9972
6                    8564


Thanks in advanced
0
Comment
Question by:sequel_prequel
  • 5
  • 4
9 Comments
 
LVL 7

Expert Comment

by:micropc1
ID: 37727668
I believe this is what you want...

SELECT T.iVehicleID, SUM(T.fTripDistance) AS KM
FROM [Trips] T
INNER JOIN [Conditions] C
ON T.iVehicleID = C.iVehicleID
GROUP BY T.iVehicleID

Open in new window

0
 

Author Comment

by:sequel_prequel
ID: 37727696
thanks for the response.  I think you meant to Group by T.iVehicleID instead of SUM(T.fTripDistance).

Having done so the query works but how is the dtDatetime condition met?

tnx
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37727701
Lol...yes, I saw that and fixed it right before you posted...  :)

Added the date/time condition...

SELECT T.iVehicleID, SUM(T.fTripDistance) AS KM
FROM [Trips] T
INNER JOIN [Conditions] C
ON T.iVehicleID = C.iVehicleID
WHERE [C.dtDateTime]>='2011-11-17 19:01:46.300'
GROUP BY T.iVehicleID

Open in new window

0
 

Author Comment

by:sequel_prequel
ID: 37727717
Yes you fixed the Group by but I still have the issue of satisfying the second condition:

and [dtDateTime]>= {the  [dtDateTime] in the Conditions table for that iVehicleid}
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:sequel_prequel
ID: 37727731
haha, no prob. but still not what I need.  The dtDatetime is not constant it needs to be the dtDatetime in the Conditions table:

iVehicleID      dtDateTime
2                      2012-03-15 17:52:17.020
3                      2012-03-16 00:08:52.077
4                      2012-03-15 20:03:59.020
5                      2011-11-17 19:01:46.300
6                      2012-03-15 19:09:44.020

For this example if I was to do things the long way:

Select * FROM
(

Select [iVehicleID],SUM(fTripDistance) AS KM
  FROM Trips
  
WHERE [iVehicleID]=2

and [dtDateTime]>='2012-03-15 17:52:17.020'

  GROUP BY [iVehicleID]
) a

UNION ALL

SELECT * FROM

(

Select [iVehicleID],SUM(fTripDistance) AS KM
  FROM Trips
  
WHERE [iVehicleID]=3

and [dtDateTime]>='2012-03-16 00:08:52.077'

  GROUP BY [iVehicleID]

) as b

.
.
.

Open in new window

0
 
LVL 7

Accepted Solution

by:
micropc1 earned 500 total points
ID: 37727738
ah...i think I understand now. You need a subquery - not a join...

SELECT A.iVehicleID, (SELECT SUM(fTripDistance) from [Trips] B 
			where B.iVehicleID = A.iVehicleID
			and B.dtDateTime >= A.dtDateTime) AS KM
FROM [Conditions] A

Open in new window

0
 

Author Comment

by:sequel_prequel
ID: 37727769
Sure let me clarify.  Yes the Trips table does have a dtDatetime field.  Here is what the trips table will look like

iVehicleID,   dtDateTime,                      fTripDistance
3               2011-11-16 19:01:46.300    100
5               2011-11-17 19:01:46.300    200
3               2011-11-18 19:01:46.300    100
3               2011-11-19 19:01:46.300    200
5               2011-11-20 19:01:46.300    100
....

From my original post:
Select [iVehicleID],SUM(fTripDistance) AS KM
  FROM Trips
  WHERE [iVehicleID]=5 
and [dtDateTime]>='2011-11-17 19:01:46.300'
  GROUP BY [iVehicleID]

Open in new window


The above gives me the total KMs traveled by the vehicleid 5 since '2011-11-17 19:01:46.300'.  For results I get say [5, 9972] (see first post).


Now I have the Conditions table which is unique by vehicleid.  

iVehicleID      dtDateTime
2                      2012-03-15 17:52:17.020
3                      2012-03-16 00:08:52.077
4                      2012-03-15 20:03:59.020
5                      2011-11-17 19:01:46.300
6                      2012-03-15 19:09:44.020


What I want is the total KMs traveled by each vehcile in the Conditions table but the KMs added should only be since the Trips.dtDateTime>=Conditons.dtDateTime.

but... I just tried something working off your suggestion and it seemed to work :)

  SELECT T.iVehicleID, SUM(T.fTripDistance) AS KM

FROM [Trips] T

INNER JOIN [Conditions] C

ON T.iVehicleID = C.iVehicleID

AND T.dtDateTime>=C.dtDateTime

GROUP BY T.iVehicleID

Open in new window


I think the using WHERE instead of ANDing the innerjoin also works:

 SELECT T.iVehicleID, SUM(T.fTripDistance) AS KM

FROM [Trips] T

INNER JOIN [Conditions] C

ON T.iVehicleID = C.iVehicleID

WHERE T.dtDateTime>=C.dtDateTime

GROUP BY T.iVehicleID

Open in new window

0
 
LVL 7

Expert Comment

by:micropc1
ID: 37727784
ha - I thought I had changed my previous comment fast enough before you actually saw it. O well... I think i understand now what you are asking. My previous post should be what you need. I hope...
0
 

Author Closing Comment

by:sequel_prequel
ID: 37727794
Perfect. I like your's better as it returns all rows in Condition table where mine only returns the rows where the condition is satisfied. Thanks for your help.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now