I have a table that contains a line entry for each item that I need to group into an output with all the values of multiple rows that have the same uniqueID into one column result. The ItemType will be different but the UniqueID and OtherData shouldn’t change as it’s tied directly to the UniqueID, but I’m not 100% sure
Tag: sql-server
Getting peak hourly KW for each month and showing the year, month, day, and hour it occurred for past 10 years?
I have electric meter usage data for 22,000 meters that goes back 10 years (2012 to present) and it’s stored as hour intervals. So, each individual meter will have (10 years * 365 days * 24 hours) 87,600 records – one for each hour going back 10 years and the KW (usage) for that hour is recorded. And for each
Parse text and special characters from SQL Server
I have an issue with parsing text with special characters from XML in SQL Server. Let’s say I have a XML file Sample.xml which has the following data: I prepared the following SQL script which can parse everything except two sentences in the <TextType> attribute: First sentence to retrieve Second sentence to retrieve Could you please help me to add
join equal or max
I want to join two tables by ‘EmployeeId’ and ‘CalendarMonthId’, but problem is that I want to connect record with the bigest CalednarMonthId if not exist equal. I use Azure Synapse SQL pool. Example data and code Base on this data I have problem with connect table: emp_contr, row: EmployeeId:1, CalendarMonthId:202205 with row from table contr from the same user
get max value based on rank fucntion
I have sample data like below. I need to show the total sum from amount column irrespective of account column and need to get account which has maximum value in the amount column. I used the below script for the same. I am able to get the account which has maximum value in the amount column but total sum is
Combine two large multi-JOIN and GROUP BY queries
So I have one query that is performing one GROUP BY that requires a lot of joining to get it to perform aggregation of a row count basically Then there is a similarly structured query but performing a different aggregation on a row count. How would you nest the second query into the first query so that the final columns
Print within the SQL Server Select query based on the condition
I have this query from the link here which works fine, only that I am adding include FK constraints ‘ON Delete/ Update cascade’ if delete/update_referential_action is 1. I am unable to achieve the desired result. Any help appreciated. Answer Ms Sql 2016 dose not support if statement in middle of query. You must use case statement instead of it. you
SQL: Capturing first row in WHILE loop
I’m thinking my issue is based on how I’ve written the loop, but how it is written is how I’ve come to understand loops. So I’m really wondering if there is a way to consolidate this? As it sits, I have 2 sections: the first which captures the very first row, and it exists solely because my second section will
How to persist SQL Server system table records?
I am running a query that returns the last execution time for a stored procedure: I am getting the correct results, but if I run the query again in around 30 seconds, I don’t get any results. Is there a setting or command I need to set or add to persist the results? My goal is to find out what
SQL Server => How to use “case when” on multiple column to show a formula result based on a condition in the same line
I’m trying to provide an overall result of a formula ([CALCULATION]) and then in two extra columns ([NO_TENURE] & [TENURE]) the same calculation but using “CASE WHEN” to filter the information based on another column called [TENURE], everything in one single line like this: Right now is being shown this way: This is the code I’m using: I really appreciate