Joing Three tables data together

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

SQL Server 2005 Forums

Transact-SQL (2005)

Joing Three tables data together

Author

Topic

SaraDob
Starting Member

3 Posts

Posted - 2009-01-07 : 15:29:41

Hi all,
I'm a first time forum user and a newbie to SQL queries.
I want to write a Sql Query to Display Sales data
Here is brief description:
I have Three tables
1)SalesUser table:
SalesUserID FirstName lastName
=====================================
1 John Smith
2 rob brown

Yearly Forecast table:

SalesUSerId Financial Year Target
==========================================
1 2008-2009 250000
2 2008-2009 450000

Monthlybooking table

SalesUSerId Month Year ActualBooking
=============================================
1 1 2009 1200
1 10 2008 2500
1 11 2008 3000
1 12 2008 5000
1 2 2007 400
2 10 2008 1000
2 11 2008 2000
2 12 2008 500

The compnay i write this application for ,follows a financial year from Oct 1 -Sept 30 th next year (Eg: Oct 1 st 2008 to Sept 30th 2009)
I have written this query which has to get the Total(Actualbooking) as YTD ,till date I want the output like this:
SalesUSerid Lastname FirstName YTD FinancialYear
==========================================================
1 John Smith 11700 2008-2009
2 rob brown 3500 2008-2009

But i get a query for each month in Monthlybooking

Here is the query:
select SalesUserId,LastName,FirstName,YTD,TargetAmount
from
(
SELECT SalesUsers.SalesUserId,SalesUsers.LastName,SalesUsers.FirstName,SUM(MonthlyBooking.ActualBooking) AS YTD,
YearlyForecast.TargetAmount
FROM SalesUsers INNER JOIN
MonthlyBooking ON SalesUsers.SalesUserId = MonthlyBooking.SalesUserId INNER JOIN
YearlyForecast ON SalesUsers.SalesUserId = YearlyForecast.SalesUserId Where MonthlyBooking.Month In ('10','11','12') AND MonthlyBooking.Year=convert(varchar(4),year(dateadd(yy,-1,getdate()))) AND YearlyForecast.TargetYear='2008-2009'
Group BY SalesUsers.SalesUserId, SalesUsers.LastName,SalesUsers.FirstName,MonthlyBooking.ActualBooking,YearlyForecast.TargetAmount
union all
SELECT SalesUsers.SalesUserId,SalesUsers.LastName,SalesUsers.FirstName,SUM(MonthlyBooking.ActualBooking) AS YTD,
YearlyForecast.TargetAmount
FROM SalesUsers INNER JOIN
MonthlyBooking ON SalesUsers.SalesUserId = MonthlyBooking.SalesUserId INNER JOIN
YearlyForecast ON SalesUsers.SalesUserId = YearlyForecast.SalesUserId Where MonthlyBooking.Month Not In ('10','11','12') AND MonthlyBooking.Year=year(getdate()) AND YearlyForecast.TargetYear='2008-2009'
Group BY SalesUsers.SalesUserId, SalesUsers.LastName,SalesUsers.FirstName,MonthlyBooking.ActualBooking,YearlyForecast.TargetAmount
)as salesdata

Please help!!!Please be patient , as i'm very new to SQL query

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-01-07 : 23:54:12

check this


-- Prepare sample data
DECLARE	@SalesUsers TABLE(SalesUserID INT, FirstName VARCHAR(100), LastName VARCHAR(100))
DECLARE	@YearlyForecast TABLE(SalesUSerId INT, TargetYear VARCHAR(100), TargetAmount INT)
DECLARE	@Monthlybooking TABLE (SalesUSerId INT, [Month] INT, [Year] INT, ActualBooking INT)

INSERT INTO @SalesUsers
		SELECT 1, 'John', 'Smith'
UNION ALL	SELECT 2, 'rob', 'brown'


INSERT INTO	@YearlyForecast
		SELECT	1, '2008-2009', 250000
UNION ALL	SELECT	2, '2008-2009', 450000


INSERT INTO	@Monthlybooking
		SELECT	1, 1, 2009, 1200
UNION ALL	SELECT	1, 10, 2008, 2500
UNION ALL	SELECT	1, 11, 2008, 3000
UNION ALL	SELECT	1, 12, 2008, 5000
UNION ALL	SELECT	1, 2, 2007, 400
UNION ALL	SELECT	2, 10, 2008, 1000
UNION ALL	SELECT	2, 11, 2008, 2000
UNION ALL	SELECT	2, 12, 2008, 500

-- Prepare Fiscl Year table
DECLARE	@FiscalYear TABLE (YearId INT IDENTITY(1, 1), FromDate DATETIME, ToDate DATETIME)
INSERT INTO	@FiscalYear
SELECT	DATEADD(YEAR, Number, '10/01/1990'), DATEADD(YEAR, Number + 1, '9/30/1990')
FROM		master..spt_values
WHERE		type = 'p'
		AND number <= 100

-- Show desired output
SELECT	SU.SalesUSerId
		, SU.FirstName
		, SU.LastName
		, SUM(MB.ActualBooking) AS 'YTD'
		, YF.TargetYear
		, YF.TargetAmount
FROM		@SalesUsers SU
INNER JOIN	@YearlyForecast YF ON YF.SalesUSerId = SU.SalesUSerId
INNER JOIN	@Monthlybooking MB ON MB.SalesUSerId = SU.SalesUSerId
INNER JOIN	@FiscalYear FY ON CONVERT(VARCHAR(10), MB.[Month]) + '/01/' + CONVERT(VARCHAR(10), MB.[Year]) BETWEEN FY.FromDate AND FY.ToDate
GROUP BY	SU.SalesUSerId, SU.FirstName, SU.LastName, YF.TargetYear, YF.TargetAmount, FY.YearId

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

SaraDob
Starting Member

3 Posts

Posted - 2009-01-09 : 10:03:06

Thanks PeterNeo for replying and willing assistance.

Your query might be right.But i dont have Fiscal Year table.
I have to manage to write this query with only three tables.
Any further thoughts.?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 10:11:12

quote:
Originally posted by SaraDob

Thanks PeterNeo for replying and willing assistance.

Your query might be right.But i dont have Fiscal Year table.
I have to manage to write this query with only three tables.
Any further thoughts.?

fiscal table is actually a temporary table peterneo used to get fiscal dates of year. it uses master..spt_values which is an internal count table used by sql server so you can use it without any problem.

SaraDob
Starting Member

3 Posts

Posted - 2009-01-14 : 16:08:06

Thanks PeterNeo for the perfect query.and thanks visakh16 for helping understand better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources