Joining table to itself - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Joining table to itself

Author

Topic

MattHeid
Starting Member

7 Posts

Posted - 2007-03-19 : 17:09:26

Hello, I am new to this and need a little help. I have one table that has three years of data in it. I want to build an SP that allows the user to pull one years worth of data but broken out by month. The report should sum up by sales person and account and then give the dollar amount for each month. I tried grabbing the whole year and then left joining a month at a time but that messed up the totals.. Is that the right way to do it? Any Ideas?

Thanks in advance!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-19 : 17:50:20

hard to answer without seeing the DDL for the table, some sample data, and expected results.

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 17:51:29

[code]-- Cross tab
SELECT SalesPerson,
Account,
SUM(CASE WHEN MonthName = 'January' THEN Sales ELSE 0 END) AS [January],
SUM(CASE WHEN MonthName = 'February' THEN Sales ELSE 0 END) AS [February],
SUM(CASE WHEN MonthName = 'March' THEN Sales ELSE 0 END) AS [March],
SUM(CASE WHEN MonthName = 'April' THEN Sales ELSE 0 END) AS [April],
SUM(CASE WHEN MonthName = 'May' THEN Sales ELSE 0 END) AS [May],
SUM(CASE WHEN MonthName = 'June' THEN Sales ELSE 0 END) AS [June],
SUM(CASE WHEN MonthName = 'July' THEN Sales ELSE 0 END) AS [July],
SUM(CASE WHEN MonthName = 'August' THEN Sales ELSE 0 END) AS [August],
SUM(CASE WHEN MonthName = 'September' THEN Sales ELSE 0 END) AS [September],
SUM(CASE WHEN MonthName = 'October' THEN Sales ELSE 0 END) AS [October],
SUM(CASE WHEN MonthName = 'November' THEN Sales ELSE 0 END) AS [November],
SUM(CASE WHEN MonthName = 'December' THEN Sales ELSE 0 END) AS [December]
FROM (
SELECT DATENAME(MONTH, OrderDate) AS MonthName,
SalesPerson,
Account,
Sales
FROM Table1
WHERE OrderDate >= '20060101'
AND OrderDate < '20070101'
) AS x
GROUP BY SalesPerson,
Account
ORDER BY SalesPerson,
Account

-- Normalized
SELECT SalesPerson,
Account,
DATENAME(MONTH, OrderDate) AS MonthName,
SUM(Sales) AS Sales
FROM Table1
WHERE OrderDate >= '20060101'
AND OrderDate < '20070101'
GROUP BY SalesPerson,
Account,
DATENAME(MONTH, OrderDate)
ORDER BY SalesPerson,
Account,
3[/code]
Peter Larsson
Helsingborg, Sweden

MattHeid
Starting Member

7 Posts

Posted - 2007-03-19 : 18:04:16

This is more or less what I was trying to do. Was trying to get the whole list and then join the second month to the unique records that have a policy and producer in common.

Select
a.Department,
a.ProducerCode,
a.ClientCode,
a.ClientName,
a.PolicySeqNo,
a.MajorLine,
a.YearComm,
b.FirstMonthComm

FROM

(Select
Department,
ProducerCode,
ClientCode,
ClientName,
PolicySeqNo,
MajorLine,
AgencyCommAmount as YearComm

From dbo.Reports_ProductionDetail
Where Laterofdate between @Enddate and @BeginDate

Group by
Department,
ProducerCode,
ClientCode,
ClientName,
PolicySeqNo,
MajorLine) a

Left Join

(Select
Department,
ProducerCode,
ClientCode,
ClientName,
PolicySeqNo,
MajorLine,
sum(AgencyCommAmount) as FirstMonthComm

From dbo.Reports_ProductionDetail
Where Laterofdate between @FirstMonth and @FirstMonthEnd

Group by
Department,
ProducerCode,
ClientCode,
ClientName,
PolicySeqNo,
MajorLine) b

On a.policySeqNo = b.policyseqno
AND a.ProducerCode = b.ProducerCode
AND a.ClientCode = b.ClientCode

MattHeid
Starting Member

7 Posts

Posted - 2007-03-19 : 18:18:13

Peso,
That is pretty rad, thank you. I will just need to modify it so that they can pick any month and it rolls back twelve months. That is a very good start and I think a much different direction than where I was headed.

Thanks Much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources