Date manipulations

[QueTech]

Hi,

I am using MS SQL 8.0. I have created a view using two tables both fields are date fields. One of the fields is start date ( always the same) the other field is changedate ( used when staff change position). I need to filter out all changedates except for the most recent. Then I need to compare startdate and changedate to get the datecoparrison.( I know which should be the most recent but I CYA) next I have field named changed reason.

If changereason = "NEW Employee" then Grace = 14days + datecomparrison
else if changereason = " Updated position" then Grace = 7days + datecomparrison.

Can anyone help with the coding,

 

[rac2]

Create a view using a query like this -

SELECT employee_id, MAX(date_position_change) AS LatestDatePositionChanged
FROM the_view_with_all_the_dates_and_reasons
GROUP BY employee_id

Give that view a name, LatestChanges. JOIN it to the view you have now.

SELECT a.employee_id, 
       a.date_start, 
       b.LatestDatePositionChanged,
       CASE
         WHEN a.changereason = "NEW Employee" 
           THEN 
             DATEADD(day, 14, b.LatestDatePositionChanged) 
         WHEN a.changereason = " Updated position" 
           THEN 
             DATEADD(day, 7, b.LatestDatePositionChanged)
         ELSE b.LatestDatePositionChanged
       END AS "Grace"

FROM the_view_with_all_the_dates_and_reasons a
JOIN LatestChanges b ON b.employee_id = a.employee_id
             AND b.LatestDatePositionChanged =
                 a.date_position_change

 

[bborissov]

rac2,
no needs of createing the view, use the same statement as derived table in the select.

Borislav Borissov

 

[rac2]

Boris my friend,

I prefer to use VIEWs for a couple of reasons. Sometimes the need for the summary occurs in another context, so I can re-use it. And using a view instead of a subquery makes it easier to read the outer query.

Separating a problem into smaller pieces is helpful in the forum, I believe.

 

[QueTech]

Hi,

I have created a new view to determine the LateChange and I have joined the two views LatesChange and DatesandReasons however when I add the code:

CASE
WHEN DatesandReasons.CHANGEREASON_I = "NEW Employee"
THEN
DATEADD(day, 14, LatestChnages.LatestDatePositionChanged)
WHEN DatesandReasons.CHANGEREASON_I = " Updated position"
THEN
DATEADD(day, 7, LatestChnages.LatestDatePositionChanged)
ELSE LatestChnages.LatestDatePositionChanged
END AS "Grace"

I get the error message:

The Query Designer does not support the CASE SQL construct.

any suggestions?

 

[QueTech]

I have worked through these errors. Thanks

 

[QueTech]

Here is the Code i have in my View "Grace".

SELECT dbo.DatesandReasons.EMPLOYID, dbo.DatesandReasons.STRTDATE, dbo.LatestChanges.LatestDatePositionChanged,
CASE WHEN DatesandReasons.CHANGEREASON_I = 'NEW EMPLOYEE' THEN DATEADD(day, 14, LatestChanges.LatestDatePositionChanged)
WHEN DatesandReasons.CHANGEREASON_I = 'Update Employee Record' THEN DATEADD(day, 7, LatestChanges.LatestDatePositionChanged)
ELSE LatestChanges.LatestDatePositionChanged END AS [Grace]
FROM dbo.DatesandReasons INNER JOIN
dbo.LatestChanges ON dbo.DatesandReasons.EMPLOYID = dbo.LatestChanges.Expr1 AND
dbo.DatesandReasons.CHANGEREASON_I = dbo.LatestChanges.LatestDatePositionChanged

When I try to run my new view , after clearing all of the syntax errors, I get the following error:

[Microsoft][ODBC Server Driver][SQL Server]Syntax error converting datetime from character String.

 

[vongrunt]

LatestDatePositionChanged is datetime or char/varchar?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[QueTech]

The LatestDatePositionChanged should be datetime and Grace should be a datetime. How so I ensure that LatestDatePositionChanged is a datetime?

 

[vongrunt]

> How so I ensure that LatestDatePositionChanged is a datetime?

"Should be" and "is" are two different things . The best way is to make it datetime in a table, everything else is band-aid.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[QueTech]

The original table is HR2EMP02 and the field is CHANGEDATE_I which is a datetime field. I have obtained the MAX(CHANGEDATE_I) AS LatestDatePositionChanged.

 

[vongrunt]

Looks fine to me then...

But where Expr1 in view came from? Is that source complete? Or maybe error happens outside view?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[QueTech]

Expr1 was an alias for EMPLOYID which I have changed to EMPLOYID.

 

[vongrunt]

AFAIK there is no way that error will happen unless at least one of "date" values is actually string. Check both tables (DatesandReasons and LatestChanges). If everything is OK there then problem is probably in code that calls view.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[QueTech]

Here is the Code for the DateandReason View:

CREATE VIEW dbo.LatestChanges
AS
SELECT TOP 100 PERCENT UPR00100.EMPLOYID, UPR00100.STRTDATE, UPR00100.INACTIVE, HR2EMP02.CHANGEREASON_I, HR2EMP02.CHANGEDATE_I,
HR2EMP02.EMPID_I, UPR00100.FRSTNAME, UPR00100.LASTNAME, UPR00100.USERDEF2
FROM dbo.UPR00100 UPR00100 INNER JOIN
dbo.HR2EMP02 HR2EMP02 ON UPR00100.EMPLOYID = HR2EMP02.EMPID_I
WHERE (UPR00100.INACTIVE = 0) AND (HR2EMP02.CHANGEREASON_I = 'NEW EMPLOYEE' OR
HR2EMP02.CHANGEREASON_I = 'Update Employee Record') OR
(HR2EMP02.CHANGEDATE_I IS NULL)
ORDER BY UPR00100.EMPLOYID


Here is the code for the LatestChanges:

CREATE VIEW dbo.LatestChanges
AS
SELECT DISTINCT EMPLOYID, MAX(CHANGEDATE_I) AS LatestDatePositionChanged
FROM dbo.DatesandReasons
GROUP BY EMPLOYID

Here is the code for the Grace view:

CREATE VIEW dbo.Grace
AS
SELECT dbo.DatesandReasons.EMPLOYID, dbo.DatesandReasons.STRTDATE, dbo.LatestChanges.LatestDatePositionChanged,
CASE WHEN DatesandReasons.CHANGEREASON_I = 'NEW EMPLOYEE' THEN DATEADD(day, 14, dbo.LatestChanges.LatestDatePositionChanged)
WHEN DatesandReasons.CHANGEREASON_I = 'Update Employee Record' THEN DATEADD(day, 7, dbo.LatestChanges.LatestDatePositionChanged)
ELSE LatestChanges.LatestDatePositionChanged END AS [Grace]
FROM dbo.DatesandReasons INNER JOIN
LatestChanges ON dbo.LatestChanges.EMPLOYID = dbo.DatesandReasons.EMPLOYID AND
dbo.LatestChanges.LatestDatePositionChanged = dbo.DatesandReasons.CHANGEREASON_I

I can not see why or where the anything is declared a string.

 

[vongrunt]

> Here is the Code for the [!]DateandReason[/!] View:

> CREATE VIEW dbo.[!]LatestChanges[/!]

Huh?

Also: I don't see where [Grace] view is used...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[QueTech]

OK,

There was a typo in my views Here they are now.

CREATE VIEW dbo.DatesandReasons
AS
SELECT TOP 100 PERCENT UPR00100.EMPLOYID, UPR00100.STRTDATE, UPR00100.INACTIVE, HR2EMP02.CHANGEREASON_I, HR2EMP02.CHANGEDATE_I,
HR2EMP02.EMPID_I, UPR00100.FRSTNAME, UPR00100.LASTNAME, UPR00100.USERDEF2
FROM dbo.UPR00100 UPR00100 INNER JOIN
dbo.HR2EMP02 HR2EMP02 ON UPR00100.EMPLOYID = HR2EMP02.EMPID_I
WHERE (UPR00100.INACTIVE = 0) AND (HR2EMP02.CHANGEREASON_I = 'NEW EMPLOYEE' OR
HR2EMP02.CHANGEREASON_I = 'Update Employee Record') OR
(HR2EMP02.CHANGEDATE_I IS NULL)
ORDER BY UPR00100.EMPLOYID

CREATE VIEW dbo.LatestChanges
AS
SELECT DISTINCT EMPLOYID, MAX(CHANGEDATE_I) AS LatestDatePositionChanged
FROM dbo.DatesandReasons
GROUP BY EMPLOYID

CREATE VIEW dbo.Grace
AS
SELECT dbo.DatesandReasons.EMPLOYID, dbo.DatesandReasons.STRTDATE, dbo.LatestChanges.LatestDatePositionChanged,
CASE WHEN DatesandReasons.CHANGEREASON_I = 'NEW EMPLOYEE' THEN DATEADD(day, 14, dbo.LatestChanges.LatestDatePositionChanged)
WHEN DatesandReasons.CHANGEREASON_I = 'Update Employee Record' THEN DATEADD(day, 7, dbo.LatestChanges.LatestDatePositionChanged)
ELSE LatestChanges.LatestDatePositionChanged END AS [Grace]
FROM dbo.DatesandReasons INNER JOIN
LatestChanges ON dbo.LatestChanges.EMPLOYID = dbo.DatesandReasons.EMPLOYID AND
dbo.LatestChanges.LatestDatePositionChanged = dbo.DatesandReasons.CHANGEREASON_I


Also, Grace is the table that I am attempting to use in my report that gives the error : [Microsoft][ODBC Server Driver][SQL Server]Syntax error converting datetime from character String.


I have corrected the typo and I still I recieve the same error when i attempt to run the view [Grace]

 

[rac2]

...
dbo.LatestChanges.LatestDatePositionChanged = dbo.DatesandReasons.CHANGEREASON_I
...

Could it be that CHANGEREASON_I is not a DATETIME value, not even a string that represents a date?