Share via

not able to get a max date from my script

Systems Administration 1 Reputation point
2021-06-10T12:07:36.98+00:00

My result of my script , see below, I am trying to get only the max chng date for that column only with the associated ss state column. I just want to have one row with ee_id , dateworked(grouped), chng date, state. My script is

SELECT distinct(Cast(ss.stime as date)) ,
ee.ed_EE_Id,
--ss.STime AS ss_DateWorked,
cast(ss.ChgDate as date) as max_chgdate ,
lk.Description AS ss_State,
-- ss.UnitId,

ss.ShiftId as Shift_ID,
ss.ShiftLength AS ss_ShiftHrs,
ss.Reason AS ss_Reason,
sa.Activity_Abbr AS ss_Activity_Abbr,
sa.TypeLkId_Abbr AS ss_TypeLkId,

--lk2.Description AS ss_ExpensesState,
pd.pn_Occ_Desc AS Emp_Occ_Desc,
pd.pn_Occ_Abbr AS Emp_Occ_Abbr,
od.Description AS ss_AltUnit_Desc,
pd.pn_Posn_Desc AS ss_AltPosn_Desc,
pd.od_Site_Abbr AS ss_Unit_Abbr,
pd.od_Site_Desc AS ss_Unit_Desc,
--ss.ApprovedBy AS ss_ApprovedBy,
ss.ApprovedDate AS ss_ApprovedOn,
lt.Description AS ss_ShiftAttribute,
ee.EmpId, ee.PersType,
ee.ed_PersType_Desc,
--ee.ed_FullName,
ee.ed_LastName,
ee.ed_FirstName,

ee.ed_Gender,
ee.PayrollGrpId,
ee.es_Payroll_Group,
ee.es_Employee_Type,
ee.ed_Age,
pd.PosnId,
-- pd.pn_Posn_Abbr,
pd.pn_Posn_Desc, pd.pn_EmpGrp_Abbr, pd.pn_EmpGrp_Desc,
--pd.pn_Occ_Abbr,
pd.pn_Occ_Desc,
--pd.pn_PGrp_Abbr,
pd.pn_PGrp_Desc,
--pd.pn_OccGrp1_Abbr, pd.pn_OccGrp1_Desc, pd.pn_OccGrp2_Abbr, pd.pn_OccGrp2_Desc,
pd.od_OrgUnit_Abbr, pd.od_OrgUnit_Desc,
pd.od_OrgUnit_AcctgCode,
--pd.od_OrgUnit_UserCode,
pd.od_Site_Abbr, pd.od_Site_Desc, pd.od_Site_AcctgCode,
--pd.od_Site_UserCode,
pd.od_CON_Abbr, --pd.od_CON_Desc, pd.od_CON_AcctgCode,
--pd.od_CON_UserCode, pd.od_Subsidiary_Abbr, pd.od_Subsidiary_Desc,
pd.od_Subsidiary_AcctgCode, pd.od_Subsidiary_UserCode,
--pd.od_GeoLoc_Abbr, pd.od_GeoLoc_Desc, pd.od_GeoLoc_AcctgCode,
--pd.od_GeoLoc_UserCode, pd.od_Dept_Abbr,
pd.od_Dept_Desc, pd.od_Dept_AcctgCode, pd.od_Dept_UserCode, pd.od_CtrctGrp_Abbr, pd.od_CtrctGrp_Desc, pd.od_CtrctGrp_AcctgCode, pd.od_CtrctGrp_UserCode,
--pd.od_SubsiduarB_Abbr, pd.od_SubsiduarB_Desc, pd.od_SubsiduarB_AcctgCode, pd.od_SubsiduarB_UserCode, pd.od_GeoLocBalS_Abbr, pd.od_GeoLocBalS_Desc, pd.od_GeoLocBalS_AcctgCode,
--pd.od_GeoLocBalS_UserCode, pd.OrgLvl10_Abbr, pd.OrgLvl10_Desc, pd.OrgLvl10_AcctgCode, pd.OrgLvl10_UserCode, pd.OrgLvl11_Abbr, pd.OrgLvl11_Desc, pd.OrgLvl11_AcctgCode, pd.OrgLvl11_UserCode,
pd.OrgLvl12_Abbr, pd.OrgLvl12_Desc, pd.OrgLvl12_AcctgCode, pd.OrgLvl12_UserCode, pm.Description AS ss_Activity_Desc,
--pd.OrgLvl13_Abbr, pd.OrgLvl13_Desc, pd.OrgLvl13_AcctgCode, pd.OrgLvl13_UserCode, pd.OrgLvl14_Abbr,
--pd.OrgLvl14_Desc, pd.OrgLvl14_AcctgCode, pd.OrgLvl14_UserCode, pd.OrgLvl15_Abbr, pd.OrgLvl15_Desc, pd.OrgLvl15_AcctgCode, pd.OrgLvl15_UserCode,
CASE WHEN (SS.LastVersion IN (1, 2) AND SS.ParentShiftId <> 0) OR ss.LastVersion NOT IN (1, 2) THEN 'Yes' ELSE 'No' END AS With_Exception

--LK3.Description AS FunctionLevel,
--LK4.Description AS AltFunctionLevel
FROM Shift_Schedule AS ss INNER JOIN

shift_schedule t2 ON ss.empid = t2.empid and
t2.EmpId ='32614'
inner join 

                     ss_ShiftType_Activity AS sa ON ss.ActivityId = sa.ActivityId inner join  
	  
                     SS_ShiftType AS st ON st.ShiftTypeId = ss.ShiftTypeId LEFT OUTER JOIN  
                     Org_Details AS od ON ss.AltUnitID = od.OrgId LEFT OUTER JOIN  
                     ss_shiftclass AS sssc ON sssc.ssshiftClassID = st.ShiftClassLkId LEFT OUTER JOIN  
                    -- Positions_Active AS pa2 ON ss.AltPosnID = pa2.PosnId LEFT OUTER JOIN  
                     Employees_Active AS ee ON ss.EmpId = ee.EmpId AND (ee.ed_PersType_Desc = 'Active Employee') and (ee.ed_TermDate IS NULL)  INNER JOIN  
                     Positions_Active AS pd ON ss.PosnId = pd.PosnId AND (pd.od_Site_AcctgCode = '079') LEFT OUTER JOIN  
                    -- Positions_Active AS pa ON ee.PrimaryPosn = pa.PosnId AND (pa.od_Site_AcctgCode = '079')  JOIN  
                     LK_Table AS lk ON ss.StateLkId = lk.LookupId INNER JOIN  
                     LK_Table AS lk2 ON ss.ExpensesStateLkId = lk2.LookupId LEFT OUTER JOIN  
                     LK_Table AS lt ON lt.TypeId = 5478 AND sa.TypeLkId = lt.LookupId INNER JOIN  
                     Prep_Methods AS pm ON pm.PMethodId = sa.PMethodId INNER JOIN  
                     Positions AS POS1 ON pd.PosnId = POS1.PosnId LEFT OUTER JOIN  
                   --  Positions AS POS2 ON pd.PosnId = POS2.PosnId LEFT OUTER JOIN  
                     Ag_Occ AS AO1 ON POS1.AgOccId = AO1.AgOccId LEFT OUTER JOIN  
                  --   Ag_Occ AS AO2 ON POS2.AgOccId = AO2.AgOccId INNER JOIN  
                     LK_Table AS LK3 ON AO1.FunctionLvl = LK3.LookupId --LEFT OUTER JOIN  
                   --  LK_Table AS LK4 ON AO2.FunctionLvl = LK4.LookupId

WHERE
--exists (select max(ss.ChgDate) from shift_schedule ) and
ss.chgdate > t2.chgdate
and (pd.od_Site_AcctgCode = '079')
--AND (dbo.fn_GetDateOnly(t2.STime) > DATEADD(YEAR, - 1, GETDATE()))
--AND (t2.chgdate) >= convert(varchar, getdate(), 21)

AND (ee.ed_PersType_Desc = 'Active Employee')
AND (ee.ed_TermDate IS NULL)
and ee.ed_ee_id = '22833'
--and dbo.fn_GetDateOnly(ss.chgdate) > --'2021-05-23 00:00:00.000'
--and '30-may-2021' )

--and lk.Description = 'approved'
--and sa.TypeLkId_Abbr = 'WKD'

group by
ee.ed_ee_id ,
-- dbo.fn_GetDateOnly
--Cast(ss.stime as date) as
Cast(ss.stime as date),
cast(ss.ChgDate as date),
lk.Description,
ss.ShiftLength,

--max(ss.shiftid),
ss.ShiftId,
ss.Reason ,
sa.Activity_Abbr ,
sa.TypeLkId_Abbr ,
pd.pn_Occ_Desc ,
pd.pn_Occ_Abbr ,
od.Description ,
pd.pn_Posn_Desc ,
pd.od_Site_Abbr ,
pd.od_Site_Desc ,

ss.ApprovedDate ,
lt.Description ,
ee.EmpId, ee.PersType,
ee.ed_PersType_Desc,ee.ed_LastName,
ee.ed_FirstName,

ee.ed_Gender,
ee.PayrollGrpId,
ee.es_Payroll_Group,
ee.es_Employee_Type,
ee.ed_Age,
pd.PosnId,
pd.pn_Posn_Desc, pd.pn_EmpGrp_Abbr, pd.pn_EmpGrp_Desc,

pd.pn_Occ_Desc,

pd.pn_PGrp_Desc,

pd.od_OrgUnit_Abbr, pd.od_OrgUnit_Desc,
pd.od_OrgUnit_AcctgCode,

pd.od_Site_Abbr, pd.od_Site_Desc, pd.od_Site_AcctgCode,

pd.od_CON_Abbr,

pd.od_Subsidiary_AcctgCode, pd.od_Subsidiary_UserCode,

pd.od_Dept_Desc, pd.od_Dept_AcctgCode, pd.od_Dept_UserCode, pd.od_CtrctGrp_Abbr, pd.od_CtrctGrp_Desc, pd.od_CtrctGrp_AcctgCode, pd.od_CtrctGrp_UserCode,
pd.OrgLvl12_Abbr, pd.OrgLvl12_Desc, pd.OrgLvl12_AcctgCode, pd.OrgLvl12_UserCode,
pm.Description ,
CASE WHEN (SS.LastVersion IN (1, 2) AND SS.ParentShiftId <> 0) OR ss.LastVersion NOT IN (1, 2) THEN 'Yes' ELSE 'No' END
having cast(ss.stime as date) > (convert(varchar, dateadd(year, -1,getdate() ) , 20) )
and max(cast(ss.ChgDate as date)) > '2021-01-01 00:00:00'
order by cast(ss.stime as date) --, ss.ChgDate desc

104237-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,624 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,196 Reputation points
    2021-06-11T02:43:23.35+00:00

    Hi @Systems Administration ,

    Welcome to Microsoft Q&A!

    Please refer below example and check whether it is helpful to you. 

    create table mytable  
([ed DD ID] int,  
Dateworked varchar(30),  
[Chng Date] varchar(40),  
[ss State] varchar(20))  
  
insert into mytable values  
(22833,CONVERT(VARCHAR(12),CAST('2021-05-19' AS DATETIME),107),'3-5-2021 12:00:00 AM','Posted'),  
(22833,CONVERT(VARCHAR(12),CAST('2021-05-19' AS DATETIME),107),'5-20-2021 12:00:00 AM','Timecard'),  
(22833,CONVERT(VARCHAR(12),CAST('2021-05-20' AS DATETIME),107),'3-5-2021 12:00:00 AM','Posted'),  
(22833,CONVERT(VARCHAR(12),CAST('2021-05-20' AS DATETIME),107),'5-20-2021 12:00:00 AM','Timecard'),  
(22833,CONVERT(VARCHAR(12),CAST('2021-05-23' AS DATETIME),107),'3-5-2021 12:00:00 AM','Posted'),  
(22833,CONVERT(VARCHAR(12),CAST('2021-05-23' AS DATETIME),107),'6-1-2021 12:00:00 AM','Approved'),  
(22833,CONVERT(VARCHAR(12),CAST('2021-05-24' AS DATETIME),107),'3-5-2021 12:00:00 AM','Posted'),  
(22833,CONVERT(VARCHAR(12),CAST('2021-05-24' AS DATETIME),107),'6-1-2021 12:00:00 AM','Approved')  
  
select [ed DD ID]  
,min(Dateworked) Dateworked  
,CONVERT(VARCHAR(12),CAST([Chng Date] AS DATETIME),107) [Chng Date]  
,[ss State]   
from mytable  
group by [ed DD ID],[Chng Date],[ss State]

    Output: 

    ed DD ID	Dateworked	Chng Date	ss State  
22833	May 19, 2021	Mar 05, 2021	Posted  
22833	May 19, 2021	May 20, 2021	Timecard  
22833	May 23, 2021	Jun 01, 2021	Approved

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. MelissaMa-MSFT 24,196 Reputation points
    2021-06-15T02:48:04.097+00:00

    Hi @Systems Administration ,

    Thanks for your update.

    You could have a try to update your query according to the example I provided above since I did not have your original table and data.

    I tried with it from my side and it worked.

    105577-rs1.png
    105654-rs2.png
    If you still face any issue, please provide CREATE TABLE statements for your tables together with INSERT statements with sample data, your complete query and expected output.

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  3. MelissaMa-MSFT 24,196 Reputation points
    2021-06-16T01:41:14.263+00:00

    Hi @Systems Administration ,

    Please refer below: 

    select [ed DD ID]  
,min(Dateworked) Dateworked  
,CONVERT(VARCHAR(12),CAST([Chng Date] AS DATETIME),107) [Chng Date]  
,'Timecard' [ss State]  
from mytable  
where [ss State]='Timecard'  
group by [ed DD ID],[Chng Date]

    OR 

    select a.[ed DD ID],a.Dateworked  
,CONVERT(VARCHAR(12),CAST(a.[Chng Date] AS DATETIME),107) [Chng Date],[ss State]  
from mytable a   
inner join   
(select b.[ed DD ID],b.Dateworked,max([Chng Date]) max from mytable b  
inner join (select [ed DD ID],min(Dateworked) min from mytable group by [ed DD ID]) c  
on b.Dateworked=c.min and b.[ed DD ID]=c.[ed DD ID]  
group by b.[ed DD ID],b.Dateworked) d  
on a.[ed DD ID]=d.[ed DD ID] and a.[Chng Date]=d.max and a.Dateworked=d.Dateworked

    Output: 

    ed DD ID	Dateworked	Chng Date	ss State  
22833	May 19, 2021	May 20, 2021	Timecard

    If above is still not working, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem.

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.