CODEWITHSUNDEEP
sql
Andypm
Andypm

Reputation: 13

SQL group by fiscal/financial year

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

Answers (3)

Ajeet Verma
Ajeet Verma

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

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

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

zip
zip

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

Related Questions