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.
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) ASBEGINSET 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)ENDGOSay for 2008-2009, the items received in January, February and March of 2009 should come after those received in December 2008, i.e.November 26December 12January 37But 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 12end [/code] |
|
|
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 ) |
|
|
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. |
|
|
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] |
|
|
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. |
|
|
June
Starting Member
18 Posts |
Posted - 2009-03-07 : 02:23:25
|
Thanks bklr, sakets. that was awesome... |
|
|
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 |
|
|
|
|
|
|
|