Forum Widgets
Latest Discussions
Capturing Latest Effective Date of Current Instance of Position
I am having issues trying to formulate a JOIN statement within my query that will provide the values that I am needing for all employees. Specifically, situations where an employee held the position multiple times. Here my current statement segment: JOIN (SELECT R_POSITION, WORK_ASSIGNMNT, EMPLOYEE, MIN(EFFECT_DATE) AS EFFECT_DATE FROM HR_EMP_POSITIONS GROUP BY R_POSITION, WORK_ASSIGNMNT, EMPLOYEE) AS EP ON EP.R_POSITION = W.POSITION AND EP.EMPLOYEE = W.EMPLOYEE AND EP.WORK_ASSIGNMNT = W.WORK_ASSIGNMNT For my statement, I need to retrieve the latest EFFECT_DATE of the last time the employee held a specific position that matches their current position. Please find my sample document with the EP and W tables. My current statement works for employee A; however, for employee B it is providing the 10/16/2023 date when I need it to populate the 8/26/2024 date. Any ideas on what I can do to get the data that I need? I don't have any other fields that I can use to help refine the criteria.
Import from Excel changes Header Names
I have a wide table in Excel with column names like Q1.1, Q1.2, etc. However, when I import this table using SSMS, the column names are renamed to Q1#1#, Q1#2#, and so on. This renaming is visible when you look at Column Mapping before actually importing. Is there a way to prevent this renaming? If not, can I write some code to rename the columns afterward? I have 96 columns in this dataset, which I need to upload monthly. This issue is becoming a nuisance because I want SQL to clean up this dataset before pivoting it and appending it to my final table. Currently, I manually clean up the data in Excel, pivot it, and then upload a tall file with only 4 columns. This method avoids the renaming issue because all the question numbers become values in a column rather than column names.
SQL Server Collation
Hi Experts, I am doing cross database migration. source database characterset is ISO_1 (ISO 8859-1) and binary sorting. While checking equivalent collation in MS SQL, I found Latin1_General_BIN. Later, I checked that all 4 collations are equivalent to the source database characterset and sorting. Latin1_General_BIN Latin1_General_BIN2 Latin1_General_100_BIN2 Latin1_General_100_BIN2_UTF8 Could you share your expert advice on the difference between the above collations and which one is best?SQL Server Collation
Hi Experts, I am doing cross database migration. source database characterset is ISO_1 (ISO 8859-1) and binary sorting. While checking equivalent collation in MS SQL, I found Latin1_General_BIN. Later, I checked that all 4 collations are equivalent to the source database characterset and sorting. Latin1_General_BIN Latin1_General_BIN2 Latin1_General_100_BIN2 Latin1_General_100_BIN2_UTF8 Could you share your expert advice on the difference between the above collations and which one is best?
What is the best practice to create documentation for our SQL Server
I know that we have to documents all our risk assessments and backup and recovery strategy but for Database Administrator to handover the task to other Database Admin, is there any best practice what to documents? Also I am aware of the 3rd party tool to help the documentation, but based on your experience what are the important things to document? Thank youDelete Statement slowness
Hello, We are running a delete statement on database every weekend where it deletes rows based on the 16 where conditions. It has to pass 16 where conditions before delete more than millions row. Since delete statement itself is resource consuming activity and takes time to delete more than a millions of data. Is it something these many where conditions causing this issue? our DB environment is like :- SQL Server 2022 Ent Version with latest patch TempDB files added considering the number of vCPUS on VM, also TempDB is on separate disk NDF Files of secondary filegroups on separate disk for better throughput MaxDOP is set to default Data and log files are on separate disks. Weekly DB maintenance plan is in place. regards, NitinCDC- change data capture
Hi, Trying to run ALTER TABLE on cdc getting below error. i have tried disabling cdc and renabling. still nothing works. Can anyone assist? Cannot alter the Error: 8277, Severity: -1, State: 0. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped. 'Table ' because it is being used for Change Data Capture. RegardsWhat are the best practices for managing and optimizing a SQL table that contains 10 million records
I have a table that currently holds 10 million records, and it continues to grow daily. As the dataset expands, I'm encountering significant challenges in retrieving data efficiently and generating reports. The increasing volume of data is causing performance bottlenecks, leading to slow query execution times and delays in report generation. To address these issues, I need to implement strategies for optimizing data retrieval and managing the growing dataset effectively and to ensure that the system remains responsive and capable of handling the increasing data load.
sql query to check status change of an item
I have a table dbo.tblPresentationStatus (sql script at the bottom ) I have to select rows where the status change is wrong. Flow should be as this: Review --> approve --> presentation --> close From 'Review' the status for a specific presentation ID can change to either 'Approve' or 'Presentation' or 'Close' From 'Approve' the status for a specific presentation ID can change to either 'Presentation' or 'Close' From 'Presentation' the status for a specific presentation ID can change to only 'Close' I want to write query to return Presentations with wrong status flow. So expected output as per records given in sql script at the bottom should be : PrID | Status1 | Status2 | Status3 | Status 4 103 | REVIEW | PRESENTATION | APPROVE |CLOSE 101 | APPROVE | REVIEW | NULL | NULL -----------------------------------sql script------------------------------------------- DROP TABLE IF EXISTS#tblPresentation CREATE TABLE#tblPresentation ( PrID int NOT NULL, PrName nvarchar(100) NULL ) INSERT INTO#tblPresentationVALUES (100,'PrA'), (101,'PrB'), (102,'PrC'), (103,'PrD') DROP TABLE IF EXISTS#tblPresentationStatus CREATE TABLE#tblPresentationStatus ( StatusID int NOT NULL, PrID int NOT NULL, PrStatus nvarchar(100) NOT NULL, StatusDate datetime NOT NULL ) INSERT INTO#tblPresentationStatusVALUES --PRESENTATION ID 100 (1,100,'REVIEW','2024-01-01 00:00:00.00'), (2,100,'APPROVE','2024-01-02 00:00:00.00'), (3,100,'PRESENTATION','2024-01-03 07:00:00.00'), (4,100,'CLOSE','2024-01-03 10:00:00.00'), --PRESENTATION ID 101 (5,101,'APPROVE','2024-01-01 00:00:00.00'), (6,101,'REVIEW','2024-01-03 10:00:00.00'), --wrong status change from 'APPROVE' to back ward status of ' REVIEW ' is not allowed --PRESENTATION ID 102 (7,102,'REVIEW','2024-01-01 00:00:00.00'), (8,102,'PRESENTATION','2024-01-02 00:00:00.00'), (9,102,'CLOSE','2024-01-03 10:00:00.00'), --PRESENTATION ID 103 (10,103,'REVIEW','2024-01-01 00:00:00.00'), (11,103,'PRESENTATION','2024-01-02 00:00:00.00'), (12,103,'APPROVE','2024-01-03 00:00:00.00'), --wrong status change from 'PRESENTATION' to back ward status of ' APPROVE' is not allowed (13,103,'CLOSE','2024-01-04 00:00:00.00')
