query is getting complicated - 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 is getting complicated

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-07-09 : 08:12:57

Having an already quite complex query, I need to pull another column from another table and things are getting too nasty for me:

select ... from
(
select EntregaS as Fecha, TiempoS, SeccionS, WCD.CONTROL AS WORKDAY from TABLE_A LEFT OUTER JOIN WCD ON TABLE_A.EntregaS = WCD.TRANSDATE where WCD.CALENDARID = 'WIKA'
union all
select TRANSDATE, NULL,NULL, CONTROL from WCD where WCD.CALENDARID = 'WIKA'
)dt
WHERE (Fecha BETWEEN GETDATE()-1 AND GETDATE()+13)
GROUP BY Fecha, WORKDAY
ORDER BY Fecha

This query gives exactly one result for each day; there is some additional information regarding that day in another table and I need to add it to the above query.

select TABLE_B.temp
from WCD LEFT OUTER JOIN ... AS TABLE_B ON WCD.TRANSDATE=TABLE_B.dia
where WCD.CALENDARID = 'WIKA' AND (TRANSDATE BETWEEN GETDATE()-1 AND GETDATE()+13)

I've been trying everything, but can't pack the 'temp' column from query2 into query1.

Any suggestion is appreciated.
Martin

Sachin.Nand

2937 Posts

Posted - 2010-07-09 : 10:15:45

You need to post some sample data and expected o/p.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-07-10 : 10:05:16

Query 1 returns: Query 2 returns:
DATE_____VALUE DATE_____temp
---------------- ----------------
10/07/2010 NULL 10/07/2010 2000
11/07/2010 NULL 11/07/2010 2000
12/07/2010 5025 12/07/2010 2000
13/07/2010 5631 13/07/2010 2000
14/07/2010 5833 14/07/2010 2000
15/07/2010 5740 15/07/2010 2000
16/07/2010 5950 16/07/2010 2000
17/07/2010 NULL 17/07/2010 2000
18/07/2010 NULL 18/07/2010 2000
19/07/2010 5026 19/07/2010 2000
20/07/2010 4464 20/07/2010 2000
21/07/2010 4598 21/07/2010 2000
22/07/2010 5156 22/07/2010 2000
23/07/2010 5011 23/07/2010 2000

I need to get the temp column into query 1 in order to do some calculations.
Hope that helps to make it clearer

Martin

Sachin.Nand

2937 Posts

Posted - 2010-07-11 : 05:11:35

[code]
select * from (
Query 1
)t
inner join
(
Query 2
)t1 on t.date=t1.date
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-07-11 : 06:22:11

WHERE (Fecha BETWEEN GETDATE()-1 AND GETDATE()+13)

What will select between yesterday AT THIS EXACT TIME and 13 days hence, also at this EXACT time. Normally when I see this the user wants "From midnight last night until midnight 13 days hence", so I just raise it in case that is what you want too?

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-07-12 : 05:50:07

Great!!! Thank you

I had been successfull with the following query, using a left outer join, but hadn't have the time to post that solution prior to your postings.

select *, query2.temp from (
Query 1
)t
left outer join query2 on t.date=query2.date
where...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources