Pivot query result (column name will change)
Hi there, Working on a huge db task. A small part of it is pivoting a query result with dynamic column names. Example Query: Select top 1 * from information_schema.columns Output should be ColumnName | Value Thanks
Pivot query result (column name will change)
Hi there, Have been working on a huge database task and one part of it is the below. How do i pivot this 1 row with dynamic columns (column names will change) Query: select top 1 * from INFORMATION_SCHEMA.columns Output should be ColumnName |…
What is the best choice for this scenario - CTE or View
Hello Team, I have a scenario where in the source data is in an Azure SQL DB. The data in the source is stored in the form of snapshots. For Ex:- Day1 - 500K Records, Day2 - 510K Records - Day3 - 520K Records (Total - 1.53M records). All the tables the…
How to get a date of day in current week
Hi, I have table that stores configurations like In my sql code, I need to take currentDateTime and get current week date for Execution Day which is Wednesday. So, for this week, it will be 10-02-2024. Get following week wednesday date. Here it…
Max value of each column in a table
Hi there, I have 500+ Azure SQL tables with total of 25000+ columns. Most of the tables have varchar (max), or nvarchar(max) but to contain a value which could only be 3 , 5 or 20 characters long. --for characters select max(**len(**columnname)) from…
better query for select results based on date.
What is the best way to write this code to get the date results? I have the time throughout the day in the datetime field. SELECT * FROM Reconciliation.Documents D Inner Join Reconciliation.DocumentsTransaction T ON D.DocumentPKey = T.DocumentPKey WHERE…
How to swap two rows in sql server
say ex: Id name 1 a 2 b 3 c 4 d 5 e 6 f i want output like: Id name 1 b 2 a 3 d 4 c 5 f 6 e Please help me how to write a SQL query
How to get a CSV file from a T-SQL in SQL Agent
I am using SQL agent to schedule a task. This task is the execution of a script that performs a query to several databases and concatenates the output. I have managed to obtain a file but not a CSV file, that is, I cannot obtain a file separating the…
Remove last 3 charecters in column
Hi, I need to remove three characters(.00) in below table result: 111111111 without (last three characters i.e .00) chr1111111111.0011111111.0011111111.001111111111.001111111.00111111.0011111111.001111.001111111.00
Update statement
How to convert query into update statement... Unable to do it select rt.outstandingamount,rt.outstandingamount * TT_USD.RateMultiplier,ENTITY,FromCurrencyKeyFK,ConversionCurrencyKey,RateMultiplier from deltalake.FactARReceiptDetail RT LEFT outer JOIN…
Like to review records incorrectly entered in the database
We have a database where we enter a record with a number with an alphanumeric suffix. But the software entered it incorrectly due to an algorithm issue which we are trying to fix now but past transactions need to be fixed in the database. If I know the…
date function in Sql
I have a timestamp where I want to query only based on the date and exclude time.
DB CM_log.ldf with large size
Hi, I have sccm in the Always On availability group, the CM database size = 5GB but the log file CM_log.ldf = 50GB, how can i reduce the size? thanks.
Processing monthly Partition from SQL Job using Stored Procedure
Hello Expert, I have a Tabular model which is having a fact table partitioned monthly on date field. e,g. Partition_July2024, Partition_Aug_2024, Partiton_Sept2024 etc.. I want to create a single SQL job to process these monthly partitions which should…
Convert Julian dates in Normal date
How to convert Julian date column in normal date like colum contains 12490,12492 Julian dates
Finding initial size of database datafiles
Hi everybody, Is there a way to determine the initial size of the data files of databases in MS SQL Server? I mean when the data files size is defined with "create database" statement or a backup was restored from another server, or the size…
SQL to Count of Rows Read Inserted Updated Deleted
Hi there, Im looking to log count of row read, inserted, updated, delete while processing data within a SP. Is there any best practice or sp template to catch these counts? You could see inorder to catch the count, the data (millions of rows) is moved…
Way to optimize query where no union needed
I had to use union on two very similar queries where one is just a copy of the other but the having clause is equal with an additional condition in the where clause. Is there a way to clean up the code? DECLARE @PO_DATE_FROM date = '2024-09-08',…
Queries in the same window for multiple databases, servers, and tables
I was wondering in SSMS in the query window could I write multiple queries that use different servers, databases, and tables? A compounded question and answer I am expecting for this.
Is there any background round operation for floats?
select ceiling(1285.0992 / 1.94712) result for the query above is 660 but the query below gives 661 as a result. declare @var1 float = 1285.0992 declare @var2 float = 1.94712 select ceiling(@var1 / @var2) can anyone tell me how that is?