problem with group by

  • 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:0042711/18/2015 0:0014:00:00:00017:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/19/2015 0:0008:00:00:00012:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/21/2015 0:0008:00:00:00013:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/18/2015 0:0014:00:00:00017:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/19/2015 0:0008:00:00:00012:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/21/2015 0:0008:00:00:00013:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/18/2015 0:0014:00:00:00017:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/19/2015 0:0008:00:00:00012:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/21/2015 0:0008:00:00:00013:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/4/2015 0:0009:55:00:00017:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/4/2015 0:0009:55:00:00017:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/18/2015 0:0014:00:00:00017:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/19/2015 0:0008:00:00:00012:00:00:000NULLNULLNULLNULLNULLNULL1E:\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:0042711/21/2015 0:0008:00:00:00013:00:00:000NULLNULLNULLNULLNULLNULL1E:\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

  • 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]

  • True result 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

  • hello all.

    I need to this query immediately.please guide me.

  • create table scripts? insert scripts? expected results?

    I'm holding up some fingers. Guess how many.

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

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