Trying to generate a report for the update date with the employeetype in (DA VENDOR, US VENDOR, BESE

[AshirvadhN]

Hi Team,

Does any of you worked on this?

Actually we have a requirement to generate a report for the user update date based on employeetype in (DA Vendor, US Vendor, BeSEE). we need a count the users how many have updated based on employeetype.

 

I tried this below query, But i don't how to link the employee type here please can someone help me out.

select b.USERKEY, b.username, a.UPDATEDATE as 'User Update Date' from usershistory a,users b where DATEDIFF(now(),a.UPDATEDATE) < 1 AND a.USERKEY = b.USERKEY AND b.STATUSKEY =1 AND b.EMPLOYEEID IS NOT NULL AND b.SYSTEMUSERNAME IS NOT NULL order by userkey;

[pruthvi_t]

Hi @AshirvadhN ,

Can you please elaborate on the business requirement. 

As per my understanding you're looking for list of users and their update dates for certain employee types. If my understanding is right then adding a filter on employee type in where clause in your query will give you the list. If you're looking for something else, please elaborate.

Thanks.

[Ishan]

@AshirvadhN  Employeetype is the column name in users table which you can add in the select query to display. You can try this:

select distinct b.employeetype, DATE(a.UPDATEDATE) as 'UserUpdateDate',count(b.username) as 'Count of Users' from usershistory a,users b where DATEDIFF(now(),a.UPDATEDATE) < 1 and a.USERKEY = b.USERKEY AND b.STATUSKEY =1 AND b.EMPLOYEEID IS NOT NULL AND b.SYSTEMUSERNAME IS NOT NULL group by b.employeetype,UserUpdateDate

[AshirvadhN]

Yes for the Query.

But How to exclude the create date with this update date.

[DixshantValecha · Accepted solution]

To exclude the create date from the query, you can add a condition in the WHERE clause to filter out records where the  a.UPDATEDATE equals the b.CREATEDATE. Here's the modified query:

SELECT DISTINCT
b.employeetype,
DATE(a.UPDATEDATE) as 'UserUpdateDate',
count(b.username) as 'Count of Users'
FROM
usershistory a,
users b
WHERE
DATEDIFF(now(), a.UPDATEDATE) < 1
AND a.USERKEY = b.USERKEY
AND b.STATUSKEY = 1
AND b.EMPLOYEEID IS NOT NULL
AND b.SYSTEMUSERNAME IS NOT NULL
AND a.UPDATEDATE != b.CREATEDATE -- exclude records where the update date equals the create date
GROUP BY
b.employeetype,
UserUpdateDate

This will filter out records where the update date equals the create date and will only return records where the a.UPDATEDATE  is within one day of the current date.