Help! - 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 2000 Forums

Transact-SQL (2000)

Help!

Author

Topic

d
Starting Member

26 Posts

Posted - 2005-07-08 : 05:55:38

Hello

Here is my query:
SELECT TOP 100 PERCENT DATENAME(mm, ExpectedDeliveryDate) AS [Month], COUNT(AmountOfLoan) AS [Count], SUM(AmountOfLoan) AS [Value]
FROM dbo.Proposals
WHERE (ExpectedDeliveryDate IS NOT NULL) AND (ExpectedDeliveryDate > GETDATE())
GROUP BY DATENAME(mm, ExpectedDeliveryDate), AmountOfLoan WITH rollup

Here is my result:
August 1 7990.45
August 1 13500
August 1 14837.44
August 3 36327.89
July 1 4000
July 1 7118
July 1 7575.82
July 1 8000
July 1 10000
July 1 11339.34
July 1 12722.57
July 1 15137.67
July 1 15775
July 1 16000
July 1 17336
July 1 24928
July 12 149932.4
October 1 11027.19
October 1 17500
October 2 28527.19
September 1 8800
September 1 17637.73
September 2 26437.73
19 241225.21

How do I get it to 1) Display in Month name order and 2) Get it to total the months so that I only have four months showing with a count and total result??

Any help appreciated, many thanks in advance

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-08 : 06:06:56

Put an ORDER BY clause in (nb. if you order by month name, the order will be alphabetical so you will probably want to order by month number). Remove AmountOfLoan from the GROUP BY clause.
Is the year not important?

d
Starting Member

26 Posts

Posted - 2005-07-08 : 06:18:19

That's great. Removing the amountofloan gives me what I want, however add in the year or month integer to order by it duplicates the months, because of the roll up i think.

This is what i have now:
SELECT TOP 100 PERCENT DATENAME(mm, ExpectedDeliveryDate) AS [Month], COUNT(AmountOfLoan) AS [Count], SUM(AmountOfLoan) AS [Value]
FROM dbo.Proposals
WHERE (ExpectedDeliveryDate IS NOT NULL) AND (ExpectedDeliveryDate > GETDATE())
GROUP BY DATENAME(mm, ExpectedDeliveryDate) WITH rollup

Results:
Month Count Value
August 3 36327.89
July 12 149932.4
October 2 28527.19
September 2 26437.73
19 241225.21

How would I get it to include year and order correctly without it looking like:

SELECT TOP 100 PERCENT MONTH(ExpectedDeliveryDate) AS Expr1, DATENAME(mm, ExpectedDeliveryDate) AS [Month], COUNT(AmountOfLoan) AS [Count],
SUM(AmountOfLoan) AS [Value]
FROM dbo.Proposals
WHERE (ExpectedDeliveryDate IS NOT NULL) AND (ExpectedDeliveryDate > GETDATE())
GROUP BY MONTH(ExpectedDeliveryDate), DATENAME(mm, ExpectedDeliveryDate) WITH rollup
ORDER BY MONTH(ExpectedDeliveryDate)

Expr1 Month Count Value
19 241225.21
7 July 12 149932.4
7 12 149932.4
8 August 3 36327.89
8 3 36327.89
9 September 2 26437.73
9 2 26437.73
10 October 2 28527.19
10 2 28527.19

Thanks again in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources