Frequently I need to do something in SQL Server that I just don’t know how to do properly. Basically, if I have a table #sample that is like this: DEPARTMENT DATE ACCOUNTING A FINANCE A PAYROLL A ACCOUNTING B PAYROLL A FINANCE A PAYROLL B PAYROLL C I need to query that table to get an output that is thi…
Tag: sql-server
Join Hours and Minute column for showing in Decimal format SQL Query
I have two datetime columns in a SQL Server table: From which I have separated hours and minutes through SQL query The output is Now, for further grouping the data, I need to join Hours and Minutes columns in one column for getting output like this Answer You can try converting both hours and mins to varchar …
SQL Find/Count similar duplicates
I am trying to to do a count on all records that are similar. Eg. I have 2 records 1 is 1A Smith St and the other is 1 Smith St. So this data needs to be returned/counted as 1. Any assistance would be gratefully appreciated. Thanks Answer Counting those where the (street, number) combination isn’t uniqu…
Is there a more efficient way to append multiple columns from the same table?
I’d like to return multiple values from the same record, with different columns of the same type appended under one another. I could do multiple joins, but that seems very inefficient because of the multiple table scans. Returns: VisID Vis Home HomeID arenn001 Nolan Arenado Colin Moran morac001 badeh001…
How to compare two row in same table and return the data in response using stored procedure
I have to compare between two rows using two Id’s in the same table, and I want to get the columns and their values that are not matching in the stored procedure and I need to return it in JSON format. Output: I don’t have expertise in it however I tried the below SQL code but I need this in
Find duplicate data in last 1 hour
I am looking for a SQL script to find the data which has more than 2 entries in last 1 hour. I have a table having user_id & event_time. I want a way to find out if the user_id has more than 1 entries in last 1 hour. I have tried below till now: Create temp table to put all
Why is the SSIS variable not passing the milliseconds part into the query?
DB SETUP: SSIS: Execute SQL task: The task has an output parameter (say Param1) of data type DT_DBTIMESTAMP. The SSIS variable data type is DateTime. Then in a subsequent Data Flow Task (say TASK2), I have an OLE DB source SQL command text: The above variable is used as an input parameter. In the SQL profiler…
Is there any better option to apply pagination without applying OFFSET in SQL Server?
I want to apply pagination on a table with huge data. All I want to know a better option than using OFFSET in SQL Server. Here is my simple query: Answer You can use Keyset Pagination for this. It’s far more efficient than using Rowset Pagination (paging by row number). In Rowset Pagination, all previou…
ADO.NET: Send procedure parameter with null value
I need to send a parameter a null value to the procedure, but I get an error: System.Data.SqlClient.SqlException: ‘Procedure or function ‘sel_mizanyeni’ expects parameter ‘@subeno’, which was not supplied.’ When I run the same procedure with the same parameters in SQL Serve…
How to get data types of columns when a CREATE TABLE FROM SELECT is forbidden?
I’ve finished a big query in SSMS with a lot of columns. The result will be inserted in another server. Usually, I create a temporary table to quickly get the data types of each columns (CREATE TABLE from a SELECT). But now, I’m working on a server where it is forbidden to create a table (even a t…