Reputation: 13
Quick question, using the code below I just want to create a field displaying the records fiscal year and then group the results by the year.
The code to retrieve the results works however it does not want to group them by the fiscal year. I just get errors. I have tried multiple combinations using the forums but I can't get it to work. It is doing my head in!
This could be simple to resolve but I can't see it. Could anyone help?
Thanks!
Select m.jobno, m.premid, m.address,m.COMPDATE,
year(dateadd(month, -3, m.COMPDATE)) as FiscalYear
FROM miscvisit m
----- GROUP BY year(dateadd(month, -3, m.COMPDATE))
Upvotes: 1
Views: 94
Reputation: 1123
Try the below query..
SELECT m.jobno
,m.premid
,m.address
,m.COMPDATE
,YEAR(DATEADD(MONTH, - 3, m.COMPDATE)) AS FiscalYear
FROM miscvisit m
GROUP BY m.jobno
,m.premid
,m.address
,m.COMPDATE
,YEAR(DATEADD(MONTH, - 3, m.COMPDATE))
Upvotes: 1
Reputation: 522752
Given that you seem to want to return the entire record, perhaps you really want an ORDER BY
clause here:
SELECT
jobno,
premid,
address,
COMPDATE,
YEAR(DATEADD(month, -3, COMPDATE)) AS FiscalYear
FROM miscvisit
GROUP BY
YEAR(DATEADD(month, -3, COMPDATE))
ORDER BY
FiscalYear;
Upvotes: 0
Reputation: 4061
Try this:
Select m.jobno, m.premid, m.address,
year(dateadd(month, -3, m.COMPDATE)) as FiscalYear, sum([SalesField]) as Sales
FROM miscvisit m
GROUP BY
m.jobno, m.premid, m.address,
year(dateadd(month, -3, m.COMPDATE))
Upvotes: 1