Forum Widgets
Latest Discussions
How to create a view with unique record based on date
I have an EMPLOYEE table that looks like this: EmployeeID,Name,PositionID,HireDate,PositionStatus E001,Chelsey,P123,2013-11-23,Terminated E001,Chelsey,P234,2019-03-13,Active E002,Mike,P345,2014-03-23,Terminated E002,Mike,P345,2021-10-13,Terminated E003,James,P543,2015-01-12,Active E004,Samantha,P895,2018-12-13,Terminated E004,Samantha,P430,2020-05-21,Terminated E004,Samantha,P945,2022-06-16,Active E005,Kayla,P459,2023-03-13,Terminated As you can see, the EmployeeID can have duplicate. The reason for this is, there's a secondary ID called PositionID, which essentially describes the employee's role. For example, whenever an employee gets promoted, or moves to a different department, a new record will be created for them with the same EmployeeID but a different PositionID. When that happens, the Position Status of the older role gets set to Terminated, and the new one gets set to Active. The HireDate represents the date teh employee started working in that role. I would like to create a VIEW to filter this to have only the unique employees, based on the HireDate. If there are multiple records for the same employee, I want the view to show only the record with the most recent HireDate. The resulting table should look like this: What's the best way to go about this?
Rounding issue - expected or bug?
Hello, we use SQL Server 2019 and we got some unexpected rounding issues. See example: It is only example with numbers, in our situation there were sub selects with data types: 0.4399 = decimal(20,4) 33.00 = decimal(20,2) 1 = smallint. My question is, why in first and fifth selects is result only with 2 decimal and not 4 based on first number with data type decimal (20,4)? I have tested it with latest cumulative update. Thank you for your answer. Pavol
SQL Query
Hi All, I have 2 tables ConfigurationTable and Data table. I require combine value like below output Source tables: Target Output: Sql Scripts: CREATE TABLE CONFIGTABLE(Productcode VARCHAR(10), Linkedvalue VARCHAR(10)) INSERT INTO CONFIGTABLE VALUES ('A', 'PEN') ,('C', 'PENCIL') ,('B', 'BOOK') ,('M', 'MOUSE') CREATE TABLE DATATABLE (FIELDVALUE VARCHAR(50), ORDERID INT,NAME VARCHAR(20)) INSERT INTO DATATABLE VALUES ('321', 9, 'COMPUTER') ,('THIS PEN IS', 1, 'A') ,('Country', 5, 'BOOK') ,('Great village in a', 4, 'B') ,('MINE', 2, 'PEN')
