need it in summarized form

[Former Member]

Hi experts

i had created a query in sql

pls find the below attached one

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT dISTINCT OHEM.U_DeptName, OHEM.termReason,OHEM.termDate FROM OHEM

WHERE OHEM.termReason IN ('2','3','1') AND OHEM.termDate BETWEEN '2015-01-01 00:00:00.000' AND '2015-09-06 00:00:00.000'

when i run this query am getting the result in Single employees wise insted of that i need it in summarized Dept wise way lik this

i need this query to be used in Crystal report by adding the query as SPT in sql

u_dept name        TOTAL          YEAR

A                          10                  2015

B                            25                 2015

C                            30                 2015

Pls help to sort it out

Regards

[Former Member · Accepted solution]

Try this:

Select U_DeptName, count(U_DeptName), year(termdate)

From OHEM

where TermReason in('1','2','3') and termdate between '2015-01-01 00:00:00.000' and '2015-09-06 00:00:00.000)

Group by U_DeptName, year(termdate)

Order by U_DeptName

Regards,

JC

[Former Member · Accepted solution]

Hi juan

This was  very helpfull to me

if u r not trouble pls i need one more help in the same

when i made this report in crystal i get in this format

i need it in this format

i had attached both format

pls could u help me out of this

This is the present query am using :

------------------------------------------------------------------------------------------------

Select U_DeptName, count(U_DeptName), year(termdate)

From OHEM

where TermReason in('1','2','3') and termdate between '2015-01-01 00:00:00.000' and '2015-09-06 00:00:00.000'

Group by U_DeptName, year(termdate)

Order by U_DeptName

-----------------------------------------------------------------------------------------------------------------------