Not able to Group By Date Column

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 2005 Forums

Transact-SQL (2005)

Not able to Group By Date Column

Author

Topic

jassimmunir
Starting Member

9 Posts

Posted - 2011-02-13 : 05:15:33

hi all
i want to Group by Date Column. my query is as below:

SELECT SUM(md.Amount) AS TotalAmount, dt.Title, dt.CreditGLAccountID, dt.DebitGLAccountID
FROM TB_PR_MonthlyDeduction AS md INNER JOIN
TB_PR_Lookup_DeductionType AS dt ON md.DeductionTypeID = dt.ID
GROUP BY dt.Title, dt.CreditGLAccountID, dt.DebitGLAccountID, dt.Active, YEAR(md.DeductionDate), MONTH(md.DeductionDate)
HAVING (dt.Active = 1) AND (md.DeductionDate BETWEEN @startDate AND @endDate)
when i try to run the above statement, i get following Error:

if in Group by clause, i remove Year and Month Function then i will get the results, by those results will not be according to the requirement.
Can any body tell me where i am doing wrong?

Best Regards,
Jassim Munir

jassimmunir
Starting Member

9 Posts

Posted - 2011-02-13 : 05:23:25

i got my answer. just after posting this topic, i tried following query, and it worked:

SELECT SUM(md.Amount) AS TotalAmount, dt.Title, dt.CreditGLAccountID, dt.DebitGLAccountID
FROM TB_PR_MonthlyDeduction AS md INNER JOIN
TB_PR_Lookup_DeductionType AS dt ON md.DeductionTypeID = dt.ID
GROUP BY dt.Title, dt.CreditGLAccountID, dt.DebitGLAccountID, dt.Active, YEAR(md.DeductionDate), MONTH(md.DeductionDate)
HAVING (dt.Active = 1) AND (YEAR(md.DeductionDate) BETWEEN YEAR(@startDate) AND YEAR(@endDate) OR
MONTH(md.DeductionDate) BETWEEN MONTH(@startDate) AND MONTH(@endDate))

in Having Claus, i should also use Year and Date Functions. :-)

Can any body suggest me some more good ways? is this the right way to fetch the records?

Best Regards,
Jassim Munir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources