Select Query / Sub Select Queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query / Sub Select Queries

Author

Topic

dwjenkins
Starting Member

6 Posts

Posted - 2012-02-28 : 11:25:09

Hello,

Please can someone provide me with some assistance with the following?
This is my current SQL Query.
[dbo].[tmGetTimeInMotion]
@DATE Date
AS
BEGIN

SET NOCOUNT ON;

SELECT dp.DeptID, dp.DeptName,

(SELECT TOP 1 Tim.TimID FROM tmTimeInMotion WHERE DATEDIFF(dd,TIM.OptimisedDateTime,@DATE)=0 or DATEDIFF(dd,TIM.ShortDateTime,@DATE)=0) AS TimID,
(SELECT TOP 1 OP.PLCName FROM iHUBUsers WHERE DATEDIFF(dd,TIM.OptimisedDateTime,@DATE)=0) AS [Optimised],
(SELECT TOP 1 TIM.OptimisedDate FROM tmTimeInMotion WHERE DATEDIFF(dd,TIM.OptimisedDateTime,@DATE)=0) AS [OptimisedDate],
(SELECT TOP 1 ST.PLCName FROM iHUBUsers WHERE DATEDIFF(dd,TIM.ShortDateTime,@DATE)=0) AS [Short Term]

FROM pbDepts as dp

FULL JOIN tmTimeInMotion TIM ON dp.DeptID = TIM.DeptID
FULL JOIN iHUBUsers ST ON TIM.ShortUserID = ST.iHUBUserID
FULL JOIN iHUBUsers OP ON TIM.OptimisedUserID = OP.iHUBUserID
WHERE

dp.IntradayUse = 1 and dp.Deleted = 0

What I want to do is always return the Department Name – and then other sub select statements to return the data if its there if not it can return it as null. Basically at the start of everyday there shouldn’t be any data other than the Department Names, as they are worked the data updates..
A brief mock of up would look like this …
Today without Data

DeptID Department TimID Optimised Optimised Date Short Term
1 Bannana Sorting
2 Apple Eaters
3 Pear Pickers
4 Orange Organisers
5 Strawberry Tasters

Today with Data

DeptID Department TimID Optimised Optimised Date Short Term
1 Bannana Sorting 1 Ken 28/02/2012
2 Apple Eaters 2 Mike
3 Pear Pickers
4 Orange Organisers 3 Mike 30/02/2012 Ken
5 Strawberry Tasters

My above statement doesn’t work like the way I wanted to and can’t fathom out how to do it!
Any help would be really appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 12:49:34

sound like this


[dbo].[tmGetTimeInMotion]
@DATE Date
AS
BEGIN

SET NOCOUNT ON;

SELECT dp.DeptID, dp.DeptName,

(SELECT TOP 1 Tim.TimID FROM tmTimeInMotion WHERE DATEDIFF(dd,TIM.OptimisedDateTime,@DATE)=0 or DATEDIFF(dd,TIM.ShortDateTime,@DATE)=0) AS TimID,
(SELECT TOP 1 OP.PLCName FROM iHUBUsers WHERE DATEDIFF(dd,TIM.OptimisedDateTime,@DATE)=0) AS [Optimised],
(SELECT TOP 1 TIM.OptimisedDate FROM tmTimeInMotion WHERE DATEDIFF(dd,TIM.OptimisedDateTime,@DATE)=0) AS [OptimisedDate],
(SELECT TOP 1 ST.PLCName FROM iHUBUsers WHERE DATEDIFF(dd,TIM.ShortDateTime,@DATE)=0) AS [Short Term]

FROM pbDepts as dp

WHERE

dp.IntradayUse = 1 and dp.Deleted = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwjenkins
Starting Member

6 Posts

Posted - 2012-02-28 : 13:29:38

Hello,

Thank you for your reply - it is sooooo close. I had to put a couple of joins into the sub select statements (is that what you call them?).

However, the optimiseddate comes back for all of the departments. its like the select top 1 isnt working.... it should only be for that one dept. Do i need to do another join on the deptid in where clause...

SELECT dp.DeptID, dp.DeptName,

(SELECT TOP 1 tmTimeInMotion.TimID FROM tmTimeInMotion WHERE DATEDIFF(dd,tmTimeInMotion.OptimisedDateTime,@DATE)=0 or DATEDIFF(dd,tmTimeInMotion.ShortDateTime,@DATE)=0) AS TimID,
(SELECT TOP 1 PLCName FROM iHUBUsers FULL JOIN tmTimeInMotion ON dp.DeptID = tmTimeInMotion.DeptID WHERE DATEDIFF(dd,tmTimeInMotion.OptimisedDateTime,@DATE)=0) AS [Optimised],
(SELECT TOP 1 OptimisedDate FROM tmTimeInMotion WHERE DATEDIFF(dd,tmTimeInMotion.OptimisedDateTime,@DATE)=0) AS [OptimisedDate],
(SELECT TOP 1 PLCName FROM iHUBUsers FULL JOIN tmTimeInMotion ON dp.DeptID = tmTimeInMotion.DeptID WHERE DATEDIFF(dd,tmTimeInMotion.ShortDateTime,@DATE)=0) AS [Short Term]

FROM pbDepts as dp

WHERE

dp.IntradayUse = 1 and dp.Deleted = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 13:46:17

quote:
Originally posted by dwjenkins

Hello,

Thank you for your reply - it is sooooo close. I had to put a couple of joins into the sub select statements (is that what you call them?).

However, the optimiseddate comes back for all of the departments. its like the select top 1 isnt working.... it should only be for that one dept. Do i need to do another join on the deptid in where clause...

SELECT dp.DeptID, dp.DeptName,

(SELECT TOP 1 tmTimeInMotion.TimID FROM tmTimeInMotion WHERE DATEDIFF(dd,tmTimeInMotion.OptimisedDateTime,@DATE)=0 or DATEDIFF(dd,tmTimeInMotion.ShortDateTime,@DATE)=0) AS TimID,
(SELECT TOP 1 PLCName FROM iHUBUsers FULL JOIN tmTimeInMotion ON dp.DeptID = tmTimeInMotion.DeptID WHERE DATEDIFF(dd,tmTimeInMotion.OptimisedDateTime,@DATE)=0) AS [Optimised],
(SELECT TOP 1 OptimisedDate FROM tmTimeInMotion WHERE DATEDIFF(dd,tmTimeInMotion.OptimisedDateTime,@DATE)=0) AS [OptimisedDate],
(SELECT TOP 1 PLCName FROM iHUBUsers FULL JOIN tmTimeInMotion ON dp.DeptID = tmTimeInMotion.DeptID WHERE DATEDIFF(dd,tmTimeInMotion.ShortDateTime,@DATE)=0) AS [Short Term]

FROM pbDepts as dp

WHERE

dp.IntradayUse = 1 and dp.Deleted = 0

may be this?


SELECT dp.DeptID, dp.DeptName,

(SELECT TOP 1 tmTimeInMotion.TimID FROM tmTimeInMotion WHERE DATEDIFF(dd,tmTimeInMotion.OptimisedDateTime,@DATE)=0 or DATEDIFF(dd,tmTimeInMotion.ShortDateTime,@DATE)=0) AS TimID,
(SELECT TOP 1 PLCName FROM iHUBUsers FULL JOIN tmTimeInMotion ON dp.DeptID = tmTimeInMotion.DeptID WHERE DATEDIFF(dd,tmTimeInMotion.OptimisedDateTime,@DATE)=0) AS [Optimised],
(SELECT TOP 1 OptimisedDate FROM tmTimeInMotion WHERE DATEDIFF(dd,tmTimeInMotion.OptimisedDateTime,@DATE)=0) AS [OptimisedDate],
(SELECT TOP 1 PLCName FROM iHUBUsers FULL JOIN tmTimeInMotion ON iHUBUsers.DeptID = tmTimeInMotion.DeptID WHERE DATEDIFF(dd,tmTimeInMotion.ShortDateTime,@DATE)=0 AND dp.DeptID= tmTimeInMotion.DeptID ) AS [Short Term]

FROM pbDepts as dp

WHERE

dp.IntradayUse = 1 and dp.Deleted = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwjenkins
Starting Member

6 Posts

Posted - 2012-02-28 : 16:23:44

Spot on

A couple of minor tweaks and that is working perfectly.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 18:28:57

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwjenkins
Starting Member

6 Posts

Posted - 2012-03-04 : 07:30:52

I am sucessfully using the above query to bind to a GridView in C# - thank you again.

However, I want to now use the above query with a start date and end date.

I am trying to attach the following where clause.

tmTimeInMotion.OptimisedDateTime >= @StartDate and tmTimeInMotion.OptimisedDateTime <= @EndDate and
tmTimeInMotion.ShortDateTime >= @StartDate and tmTimeInMotion.ShortDateTime <= @EndDate

but it cant be bound... how would I go about it? Basically, I want to provide a start and end date and if eith the Optimised Date or the Short Date falls within in there then it will return the results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 10:20:31

you need add above conditions to each of select statements

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwjenkins
Starting Member

6 Posts

Posted - 2012-03-05 : 06:23:22

Yes, once you said that I realised that I was putting in the wrong place. I changed it to look like this.

SELECT dp.DeptID, dp.DeptName,

(SELECT TOP 1 tmTimeInMotion.TimID FROM tmTimeInMotion WHERE dp.DeptID = tmTimeInMotion.DeptID AND tmTimeInMotion.OptimisedDateTime >= @StartDate and tmTimeInMotion.OptimisedDateTime <= @EndDate or tmTimeInMotion.ShortDateTime >= @StartDate and tmTimeInMotion.ShortDateTime <= @EndDate) AS TimID,
(SELECT TOP 1 PLCName FROM iHUBUsers FULL JOIN tmTimeInMotion ON dp.DeptID = tmTimeInMotion.DeptID WHERE tmTimeInMotion.OptimisedDateTime >= @StartDate and tmTimeInMotion.OptimisedDateTime <= @EndDate) AS [Optimised],
(SELECT TOP 1 OptimisedDate FROM tmTimeInMotion WHERE dp.DeptID = tmTimeInMotion.DeptID AND tmTimeInMotion.OptimisedDateTime >= @StartDate and tmTimeInMotion.OptimisedDateTime <= @EndDate or tmTimeInMotion.ShortDateTime >= @StartDate and tmTimeInMotion.ShortDateTime <= @EndDate) as [OptimisedDate],
(SELECT TOP 1 PLCName FROM iHUBUsers FULL JOIN tmTimeInMotion ON dp.DeptID = tmTimeInMotion.DeptID WHERE tmTimeInMotion.OptimisedDateTime >= @StartDate and tmTimeInMotion.ShortDateTime >= @StartDate and tmTimeInMotion.ShortDateTime <= @EndDate) AS [Short Term]

FROM pbDepts as dp

WHERE
dp.IntradayUse = 1 and dp.Deleted = 0

But then realised...
How would I use the DATEDIFF(dd,)=0 function with the >= @StartDate and <= @EndDate?

dwjenkins
Starting Member

6 Posts

Posted - 2012-03-05 : 09:03:45

:) Got it working after a little thinking about it. If =0 is the exact date the >=0 would accomplish the same thing :)

Thank you again for your help :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-07 : 19:26:02

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources