SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select based on results of another select		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dibblejon
Starting Member

35 Posts
Posted - 06/27/2012 :  09:22:40  Show Profile  Reply with Quote
Hi

We have two tables and I would like to try and create some form of lookup between the two - but am having issues.

Table syscontrol

SQL

select * from syscontrol
where keyfield = 316 or keyfield = 317

316;1;0;"";"Sales";1;0;"11";"Period";"Period";0
317;1;0;"";"Sales";2;0;"2012";"Year";"Year";0

Table ststats

Results from simple query

124108;1;"01";"TAN0018";1;2012
124109;1;"01";"TAN0018";2;2012
124110;1;"01";"TAN0018";4;2012
124111;1;"01";"TAN0018";6;2012
124112;1;"01";"CTS5001";1;2012
124113;1;"01";"CTS5001";2;2012
124114;1;"01";"CTS5001";3;2012
124115;1;"01";"CTS5001";5;2012
124116;1;"01";"CTS5001";6;2012
124117;1;"01";"JOH0610";1;2012
124118;1;"01";"JOH0610";2;2012
124119;1;"01";"JOH0610";3;2012
124120;1;"01";"JOH0610";4;2012
124121;1;"01";"JOH0610";5;2012
124122;1;"01";"JOH0610";6;2012

What I want to try and achieve is one query that has logic like:

Select results from ststats where results of query on syscontrol table for Period and Year equal current Year and Period (which is what is returned in the 1st query) and show also show the preceding 6 months.

So for example if the Period is 7 and the Year is 2012 I would like to see froms ststats all records that have 7,2012 6,2012 5,2012 and so on

Any help would be fantastic

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 06/27/2012 :  10:09:11  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
You can use a cte to reference a derived table
;with t1 as
(
select ...
) ,
t2 as
(
select ...
)
select *
from t1 join t2
on ...


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

dibblejon
Starting Member

35 Posts
Posted - 06/27/2012 :  10:47:28  Show Profile  Reply with Quote
Thanks will try this

Had also tried this but is falling over 

SELECT 
  ststats.company, 
  ststats.depot, 
  ststats.part, 
  ststats.sale_period, 
  ststats.sale_year, 
  ststats.units, 
  "CurrentPeriod".notes, 
  "CurrentPeriod".code_value::smallint AS "CurrentPeriod", 
  "CurrentYear".notes, 
  "CurrentYear".code_value::smallint AS "CurrentYear"
FROM 
  public.ststats, 
  public."CurrentPeriod", 
  public."CurrentYear"
WHERE 
  ststats.company = "CurrentPeriod".company AND
  ststats.company = "CurrentYear".company AND
  ststats.sale_year >= '2011' AND
  public.CurrentPeriod.code_value = sale_period -1


With this error - where I am going wrong?

ERROR: missing FROM-clause entry for table "currentperiod"
LINE 20: public.CurrentPeriod.code_value = sale_period -1
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000