Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Select query having a table input for a condition

Posted on 2012-03-15
9
Medium Priority
?
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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
 

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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

660 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