Query within a Query? - SQL Server Forums

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

Query within a Query?

Author

Topic

Dianne
Starting Member

10 Posts

Posted - 2012-09-13 : 12:53:44

We have a list of customer transactions (Trans) that are grouped by customer #(CompID). Each Trans has a sequence # (Seq1) that is sequenced by a transaction date (TransDate). Then we have each like Trans grouped together for each CompID and sequenced together (Seq2) by that kind of Tran for that CompID. We need a query that will look at the CompID group and find any Trans type of ‘AWAY’ then look at that Trans StartDate then go and find a Trans type of ‘CS%’ with a StartDate that is before the ‘AWAY’ Trans StartDate but has a StopDate that is greater than the ‘AWAY’ Trans StartDate or ‘Null’ and pull that ‘CS%’ Trans HRPerDay into the ‘AWAY’ Trans’ HrsPerDay column. See below table to help clarify:

Trans Start CompID Seq1 Name Seq2 Stop HrsPerDay
CS1 1/1 11 2 Bob 1 4/1 1.4
CS2 4/1 11 4 Bob 2 6/1 3.4
CS3 6/1 11 6 Bob 3 NULL 5.4
AWAY 3/5 11 3 Bob 1 3/20 ?
AWAY 7/3 11 7 Bob 2 NULL ?
MSF 1/1 11 1 Bob 1 5/12 NULL
MSF 5/12 11 5 Bob 2 NULL NULL

We will appreciate any help we can get on this. Thanks

Dianne

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 13:15:43

so what should be output for sample data above?

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

Dianne
Starting Member

10 Posts

Posted - 2012-09-13 : 13:32:28

quote:
Originally posted by visakh16

so what should be output for sample data above?

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

In the above example it would give me 1.4 HrsPerDay for the first AWAY and 5.4 for the 2nd. It would pull the HrsPerDay from the Trans CS1 for the first AWAY and the HrsPerDay from the Trans CS3 for the 2nd AWAY...

Dianne

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 13:41:20

[code]
UPDATE t
SET t.HrsPerDay = t1.HrsPerDay
FROM table t
INNER JOIN table t1
ON t1.CompID = t.CompID
AND t.Trans = 'AWAY'
AND t1.Trans LIKE 'CS%'
AND t.Start >= t1.Start
AND (t.Start <= t1.Stop OR t1.Stop IS NULL)
[/code]

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

Dianne
Starting Member

10 Posts

Posted - 2012-09-13 : 13:59:50

Can this same query be used to create a view? If not how does the query change?

Dianne

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 14:24:57

create a view? your intial requirement was to do an update isnt it? if its view that you want change UPDATE to SELECT

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

Dianne
Starting Member

10 Posts

Posted - 2012-09-13 : 16:31:28

I am trying to create a view. When i put the query in above it says there is a syntax error between the t and SET in the first ROW.

Dianne

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 16:44:35

here you go with select


SELECT t.Trans ,
t.Start ,
t.CompID ,
t.Seq1 ,
t.Name ,
t.Seq2 ,
t.Stop ,
COALESCE(t1.HrsPerDay,t.HrsPerDay)
FROM table t
LEFT JOIN table t1
ON t1.CompID = t.CompID
AND t.Trans = 'AWAY'
AND t1.Trans LIKE 'CS%'
AND t.Start >= t1.Start
AND (t.Start <= t1.Stop OR t1.Stop IS NULL)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources