SQL question - 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)

SQL question

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-10 : 08:53:37

I am trying to get a report as summary below:

1. Year of Death
2. Number of Debtor
3. Total Liabilities

For example: How many debtor in the range of 10 years of death and its liabilities.

I have a query run in detail, but I was not sure how to summary as the above requirement. Please see my current query and show me how to:

select a.id, sum(a.balance) as TotalLiabilities, datediff(year, b.date_of_death,getdate()) as Years_of_Death
from dba.tb1 a
inner join dba.tb2 b on (cast(b.soc_sec_num as varchar(10))=a.id)
group by a.id, b.date_of_death
having sum(a.balance)>100
order by 2 desc

Thank you,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 08:58:08

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.25"
N 56°04'39.16"

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-10 : 09:12:31

quote:
Originally posted by Peso

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.25"
N 56°04'39.16"

I need to find out how to summary that query as the requirement. So why did you send me that link?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 09:35:55

Because we don't know your table's layout.

E 12°55'05.25"
N 56°04'39.16"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-10 : 09:36:09

It was sent so that you would read it and have a better understanding
of what is needed for people to best help you. Your data and needs may be obvious to you, but not to the people here. The more detail you can provide, the faster you will get a reply.

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 09:39:55

Try this

SELECT		y.ID,
		y.TotalLiabilities,
		p.Years_of_Death
FROM		(
			SELECT		ID,
					SUM(Balance) AS TotalLiabilities
			FROM		dba.tb1
			GROUP BY	ID
			HAVING		SUM(Balance) > 100
		) AS y
INNER JOIN	(
			SELECT	CAST(Soc_Sec_Num AS VARCHAR(10)) AS ID,
				DATEDIFF(YEAR, Date_Of_Death, GETDATE()) AS Years_of_Death
			FROM	dba.tb2
		) AS p ON p.ID = y.ID
ORDER BY	y.TotalLiabilities DESC

E 12°55'05.25"
N 56°04'39.16"

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-10 : 11:01:39

quote:

Originally posted by Peso

Try this

SELECT		y.ID,
		y.TotalLiabilities,
		p.Years_of_Death
FROM		(
			SELECT		ID,
					SUM(Balance) AS TotalLiabilities
			FROM		dba.tb1
			GROUP BY	ID
			HAVING		SUM(Balance) > 100
		) AS y
INNER JOIN	(
			SELECT	CAST(Soc_Sec_Num AS VARCHAR(10)) AS ID,
				DATEDIFF(YEAR, Date_Of_Death, GETDATE()) AS Years_of_Death
			FROM	dba.tb2
		) AS p ON p.ID = y.ID
ORDER BY	y.TotalLiabilities DESC

E 12°55'05.25"
N 56°04'39.16"

this query came out the same result like my query. That's correct, but I need to summary. For example:

debtor-------Liabilities---years of death
111-11-1111 $300,000 2
222-22-2222 $150,000 1
333-33-3333 $200,000 0
444-44-4444 $150,000 2
555-55-5555 $500,000 1
666-66-6666 $100,000 0

i want to count how many debtor in the range of 2 years death or vice versa...and their total liabilites. As above data, we can see two debtors fall in the 2 years of death, and total liabilities = $450,000. I believe this will clarify more on my question.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 11:07:40

[code]SELECT COUNT(ID) AS DebtorCount,
SUM(TotalLiabilities) AS TotalLiabilities,
Years_of_Death
FROM
(
SELECT y.ID,
y.TotalLiabilities,
p.Years_of_Death
FROM (
SELECT ID,
SUM(Balance) AS TotalLiabilities
FROM dba.tb1
GROUP BY ID
HAVING SUM(Balance) > 100
) AS y
INNER JOIN (
SELECT CAST(Soc_Sec_Num AS VARCHAR(10)) AS ID,
DATEDIFF(YEAR, Date_Of_Death, GETDATE()) AS Years_of_Death
FROM dba.tb2
) AS p ON p.ID = y.ID
)t
GROUP BY Years_of_Death
ORDER BY DebtorCount DESC[/code]

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-10 : 11:23:59

quote:

Originally posted by visakh16

SELECT COUNT(ID) AS DebtorCount,
SUM(TotalLiabilities) AS TotalLiabilities,
Years_of_Death
FROM
(
SELECT		y.ID,
		y.TotalLiabilities,
		p.Years_of_Death
FROM		(
			SELECT		ID,
					SUM(Balance) AS TotalLiabilities
			FROM		dba.tb1
			GROUP BY	ID
			HAVING		SUM(Balance) > 100
		) AS y
INNER JOIN	(
			SELECT	CAST(Soc_Sec_Num AS VARCHAR(10)) AS ID,
				DATEDIFF(YEAR, Date_Of_Death, GETDATE()) AS Years_of_Death
			FROM	dba.tb2
		) AS p ON p.ID = y.ID
)t
GROUP BY Years_of_Death
ORDER BY DebtorCount DESC

Yes It works like a charm! Thank you so much...I did try a count function before, but I did not get the right result...I forget to declare on the top about total liabilities and year of death as yours...:-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 11:26:57

quote:

Originally posted by ntn104

quote:

Originally posted by visakh16

SELECT COUNT(ID) AS DebtorCount,
SUM(TotalLiabilities) AS TotalLiabilities,
Years_of_Death
FROM
(
SELECT		y.ID,
		y.TotalLiabilities,
		p.Years_of_Death
FROM		(
			SELECT		ID,
					SUM(Balance) AS TotalLiabilities
			FROM		dba.tb1
			GROUP BY	ID
			HAVING		SUM(Balance) > 100
		) AS y
INNER JOIN	(
			SELECT	CAST(Soc_Sec_Num AS VARCHAR(10)) AS ID,
				DATEDIFF(YEAR, Date_Of_Death, GETDATE()) AS Years_of_Death
			FROM	dba.tb2
		) AS p ON p.ID = y.ID
)t
GROUP BY Years_of_Death
ORDER BY DebtorCount DESC

Yes It works like a charm! Thank you so much...I did try a count function before, but I did not get the right result...:-)

Your thanks should go to Peso. I just tweaked it to fit your requirements.

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-10 : 11:28:16

quote:

Originally posted by Peso

Try this

SELECT		y.ID,
		y.TotalLiabilities,
		p.Years_of_Death
FROM		(
			SELECT		ID,
					SUM(Balance) AS TotalLiabilities
			FROM		dba.tb1
			GROUP BY	ID
			HAVING		SUM(Balance) > 100
		) AS y
INNER JOIN	(
			SELECT	CAST(Soc_Sec_Num AS VARCHAR(10)) AS ID,
				DATEDIFF(YEAR, Date_Of_Death, GETDATE()) AS Years_of_Death
			FROM	dba.tb2
		) AS p ON p.ID = y.ID
ORDER BY	y.TotalLiabilities DESC

E 12°55'05.25"
N 56°04'39.16"

Thank you Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources