Joining 2 Queries - 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

Joining 2 Queries

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2009-03-06 : 14:38:33

How can I join these 2 queries based on Customer_No so it only returns a dataset for customers that have data for both 2008 and 2009? I can't seem to get the syntacx right...

Select Client.Company,Client.City,YEAR(BOF_Date) AS Year,
	MONTH(BOF_Date) AS Month,
	SUM(Actual_WT) AS Actual_WT,SUM(LF_WT_Target) AS Target_WT,
	MIN(BOF_Date) AS Min_Date,MAX(BOF_Date) AS Max_Date
FROM WashRun
INNER JOIN CLIENT on Client.Customer_No = WashRun.Customer_No
WHERE (BOF_Date BETWEEN '1/1/09' AND '3/1/09') 
GROUP BY Client.Company,Client.City,YEAR(BOF_Date),MONTH(BOF_Date)

Select Client.Company,Client.City,YEAR(BOF_Date) AS Year,
	MONTH(BOF_Date) AS Month,
	SUM(Actual_WT) AS Actual_WT,SUM(LF_WT_Target) AS Target_WT,
	MIN(BOF_Date) AS Min_Date,MAX(BOF_Date) AS Max_Date
FROM WashRun
INNER JOIN CLIENT on Client.Customer_No = WashRun.Customer_No
WHERE (BOF_Date BETWEEN '1/1/08' AND '3/1/08') 
GROUP BY Client.Company,Client.City,YEAR(BOF_Date),MONTH(BOF_Date)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 14:55:47

[code]WHERE (BOF_Date BETWEEN '1/1/09' AND '3/1/09')AND((BOF_Date BETWEEN '1/1/08' AND '3/1/08')) [/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 16:43:55

I guess it should be OR ....no?

WHERE (BOF_Date BETWEEN '1/1/09' AND '3/1/09')OR((BOF_Date BETWEEN '1/1/08' AND '3/1/08'))

If you want to have two queries...do a UNION ALL in between

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 16:54:29

This is from OP:

so it only returns a dataset for customers that have data for both 2008 and 2009?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 16:56:29

Oops sorry Sodeep..dint realize that..I guess that y u r flowing fount of Yak knowledge huh...

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2009-03-06 : 20:02:58

Thanks guys - I got it - was a little brain dead today!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources