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)
 Displaying months ordered as in financial year

Author  Topic 

June
Starting Member

18 Posts

Posted - 2009-03-06 : 02:46:54
Hi,
I need to display the total no. of items received in a year monthwise. The financial year is from 1st April to 31st March. My stored procedure is like this:

CREATE PROCEDURE RcvdByYear
@MCode VARCHAR(20),
@year VARCHAR(10)

AS
BEGIN
SET NOCOUNT ON


SELECT datename(month,RcvDate) As Month, SUM(Accepted) AS Quantity, SUM(TotalValue) AS Value from ReceiptItems WHERE (right(YEAR(DATEADD(month, DATEDIFF(month, 0, RcvDate) -3, 0)),2) = @year)
and ReceiptItems.Code = @MCode
GROUP BY DATENAME(month, RcvDate), DATEPART( month, RcvDate )
Order by DATEPART(month, RcvDate)

END
GO

Say for 2008-2009, the items received in January, February and March of 2009 should come after those received in December 2008, i.e.

November 26
December 12
January 37

But January, February and March is coming first. I guess it must be for the monthcode. How can I display them in the desired order? thanks for any help.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-06 : 05:41:21
[code]SELECT datename(month,RcvDate) As Month, SUM(Accepted) AS Quantity, SUM(TotalValue) AS Value from ReceiptItems WHERE (right(YEAR(DATEADD(month, DATEDIFF(month, 0, RcvDate) -3, 0)),2) = @year)
and ReceiptItems.Code = @MCode
GROUP BY DATENAME(month, RcvDate), DATEPART( month, RcvDate )
Order by
case
when datename(month,RcvDate)='april' then 1
when datename(month,RcvDate)='may' then 2
when datename(month,RcvDate)='june' then 3
when datename(month,RcvDate)='july' then 4
when datename(month,RcvDate)='august' then 5
when datename(month,RcvDate)='september' then 6
when datename(month,RcvDate)='october' then 7
when datename(month,RcvDate)='november' then 8
when datename(month,RcvDate)='decmeber' then 9
when datename(month,RcvDate)='january' then 10
when datename(month,RcvDate)='february' then 11
when datename(month,RcvDate)='march' then 12
end [/code]
Go to Top of Page

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-06 : 06:02:13
SELECT datename(month,RcvDate) As Month, SUM(Accepted) AS Quantity, SUM(TotalValue) AS Value from ReceiptItems WHERE (right(YEAR(DATEADD(month, DATEDIFF(month, 0, RcvDate) -3, 0)),2) = @year)
and ReceiptItems.Code = @MCode
GROUP BY DATENAME(month, RcvDate), DATEPART( month, RcvDate )
Order by DATEPART( month, RcvDate )
Go to Top of Page

June
Starting Member

18 Posts

Posted - 2009-03-06 : 06:07:59
[u]Saket[\u]

Doesn't work.

Error 8127: Column name 'ReceiptItems.RcvDate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 06:20:17
[code]
SELECT datename(month,RcvDate) As Month, SUM(Accepted) AS Quantity, SUM(TotalValue) AS Value from ReceiptItems WHERE (right(YEAR(DATEADD(month, DATEDIFF(month, 0, RcvDate) -3, 0)),2) = @year)
and ReceiptItems.Code = @MCode
GROUP BY DATENAME(month, RcvDate), DATEPART( month, RcvDate )
Order by [month]
[/code]
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-06 : 06:29:26
quote:
Originally posted by June

[u]Saket[\u]

Doesn't work.

Error 8127: Column name 'ReceiptItems.RcvDate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.



Sorry, I editted the post. Pick that one.
Go to Top of Page

June
Starting Member

18 Posts

Posted - 2009-03-07 : 02:23:25
Thanks bklr, sakets. that was awesome...
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-07 : 03:40:43
quote:
Originally posted by June

Thanks bklr, sakets. that was awesome...


welcome
Go to Top of Page
   

- Advertisement -