problem with group by

Question

Post reply

problem with group by

elham_azizi_62

SSCertifiable

Points: 5095
More actions
December 6, 2015 at 5:21 am

#313726

hello all.

I have this query:

WITH CTE AS (SELECT * FROM( SELECT m.EntityCode, u.FirstName ,u.LastName,M.CreateDate as RequestDate,m.requset2 as RoleID,m.ReqUestType as RequestType,'0' as FormType,m.Accept1Sign as Accept1Sign,m.Accept2ign as Accept2ign

from Mission m inner join Roles r on m.requset2=r.Role_ID inner join Users u on r.UserID=u.User_ID

UNION ALL SELECT v.EntityCode,u.FirstName,u.LastName,V.reqDate as RequestDate ,v.reqName as RoleID,'2' as RequestType,'1' as FormType,V.signofboss as Accept1Sign,V.sigboss as Accept2ign

from Vacation v inner join Roles r on v.reqName=r.Role_ID inner join Users u on r.UserID=u.User_ID

) AS x)

SELECT cte.EntityCode,cte.FirstName,cte.LastName,cte.RequestDate,cte.RoleID,V.vacHourltDate as HourlyDat,CONVERT(varchar, DATEADD(ms, V.begintime * 1000, 0), 114) as HourlyVacationStartTime,CONVERT(varchar, DATEADD(ms, V.endtime * 1000, 0), 114) as HourlyVacationEndTime, V.vacDailtSDate as DailyStartDate ,V.vacDailyEDate as DailyEndDate,M.StartDate as MissionStartDate,M.EndDate as MissionEndtDate,CONVERT(varchar, DATEADD(ms, M.FromH * 1000, 0), 114) as MissionStartTime,CONVERT(varchar, DATEADD(ms, M.ToH * 1000, 0), 114) as MissionEndTime ,cte.FormType,cte.Accept1Sign,cte.Accept2ign FROM cte LEFT OUTER JOIN Entity_vacation AS V ON cte.RequestDate = V.reqDate AND cte.RoleID=v.reqName LEFT OUTER JOIN Entity_M72 AS M ON cte.RequestDate = M.CreateDate AND cte.RoleID = M.requset2

where cte.FirstName+ +cte.LastName like N'%‍ار‍ژندي%' and FormType='1' and ((V.vacHourltDate>='Oct 23 2015 12:00AM' and V.vacHourltDate<='Nov 21 2015 12:00AM') or ( V.vacDailtSDate >='Oct 23 2015 12:00AM' and V.vacDailyEDate<='Nov 21 2015 12:00AM') )

and V.signofboss is not null and V.signofboss!='' and V.sigboss is not null and V.sigboss!=''

group by cte.EntityCode, cte.FirstName,cte.LastName,cte.RequestDate

, cte.RequestType,cte.FormType,cte.RoleID, V.vacHourltDate,V.begintime,V.endtime, V.vacDailtSDate, V.vacDailyEDate,M.StartDate, M.EndDate, M.FromH,M.ToH ,cte.Accept1Sign,cte.Accept2ign

order by 1 desc

and I have this result:

1 276 اعظم ارژندي 11/16/2015 0:00 427 11/18/2015 0:00 14:00:00:000 17:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

2 276 اعظم ارژندي 11/16/2015 0:00 427 11/19/2015 0:00 08:00:00:000 12:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

3 276 اعظم ارژندي 11/16/2015 0:00 427 11/21/2015 0:00 08:00:00:000 13:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

4 275 اعظم ارژندي 11/16/2015 0:00 427 11/18/2015 0:00 14:00:00:000 17:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

5 275 اعظم ارژندي 11/16/2015 0:00 427 11/19/2015 0:00 08:00:00:000 12:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

6 275 اعظم ارژندي 11/16/2015 0:00 427 11/21/2015 0:00 08:00:00:000 13:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

7 274 اعظم ارژندي 11/16/2015 0:00 427 11/18/2015 0:00 14:00:00:000 17:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

8 274 اعظم ارژندي 11/16/2015 0:00 427 11/19/2015 0:00 08:00:00:000 12:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

9 274 اعظم ارژندي 11/16/2015 0:00 427 11/21/2015 0:00 08:00:00:000 13:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

10 229 اعظم ارژندي 11/5/2015 0:00 427 11/4/2015 0:00 09:55:00:000 17:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

But this result is false and the below result is true:

1 اعظم ارژندي 11/5/2015 0:00 427 11/4/2015 0:00 09:55:00:000 17:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

2 اعظم ارژندي 11/16/2015 0:00 427 11/18/2015 0:00 14:00:00:000 17:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

3 اعظم ارژندي 11/16/2015 0:00 427 11/19/2015 0:00 08:00:00:000 12:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

4 اعظم ارژندي 11/16/2015 0:00 427 11/21/2015 0:00 08:00:00:000 13:00:00:000 NULL NULL NULL NULL NULL NULL 1 E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695] E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

this is result of query without cte.EntityCode but I need to have cte.EntityCode (m.EntityCode ,v.EntityCode ) essentially.

Please help me to correct this query with EntityCode and true result.

thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 1

false result:

1,276,sara,john,11/16/2015 0:00,427,11/18/2015 0:00,14:00:00:000,17:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

2,276,sara,john,11/16/2015 0:00,427,11/19/2015 0:00,08:00:00:000,12:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

3,276,sara,john,11/16/2015 0:00,427,11/21/2015 0:00,08:00:00:000,13:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

4,275,sara,john,11/16/2015 0:00,427,11/18/2015 0:00,14:00:00:000,17:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

5,275,sara,john,11/16/2015 0:00,427,11/19/2015 0:00, 08:00:00:000,12:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

6,275,sara,john,11/16/2015 0:00,427,11/21/2015 0:00,08:00:00:000,13:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

7,274,sara,john,11/16/2015 0:00,427,11/18/2015 0:00,14:00:00:000,17:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

8,274,sara,john,11/16/2015 0:00,427,11/19/2015 0:00,08:00:00:000,12:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

9,274,sara,john,11/16/2015 0:00,427,11/21/2015 0:00,08:00:00:000,13:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

10,229,sara,john,11/5/2015 0:00,427,11/4/2015 0:00,09:55:00:000,17:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

true result:

1,sara,john,11/5/2015 0:00 ,427,11/4/2015 0:00,09:55:00:000,17:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

2,sara,john,11/16/2015 0:00,427,11/18/2015 0:00,14:00:00:000,17:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

3,sara,john,11/16/2015 0:00,427,11/19/2015 0:00,08:00:00:000,12:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]

4,sara,john,11/16/2015 0:00,427,11/21/2015 0:00,08:00:00:000,13:00:00:000,NULL,NULL,NULL,NULL,NULL,NULL,1,E:\Farzin\Upload\PersonnelSignature\1393\8\20\Untitled-1[17-1-59-695],E:\Farzin\Upload\PersonnelSignature\1394\1\23\image0000001A[8-49-7-711]