I’m currently getting a result-set back as follows: What I’m trying to do is get the results to appear as follows: I’ve put together the following query, and I was curious as to whether someone had a better way of doing it: Here’s the sample data: Answer You can use conditional aggregation. That is, aggregate functions wrapped around CASE expressions.
Tag: sql-server-2017
Split records into buckets based on counts in reference table
This is a simplified version, stripped down to my core problem. I have a ContactData table with millions of rows of data, with each contact record broken up into categories with a ReferenceID. I now have to assign a new UpdatedValue to each contact record, based on counts from the separate NewValues table also by ReferenceID. It doesn’t matter which
Merge not inserting new values
I’m trying to use MERGE to insert new values to a table only if they don’t already exists in the same table. This is the query I am using: MERGE [dbo].[TARGET_TABLE] AS Target USING (SELECT [NAME] …
SQL Remove all leading zeros
My join returns null because I am not able to remove the leading zeros. Basically, I am trying to replace the pol.Policy_Number with src.Policy_Number for a large number of policies. Is there a way I can remove the leading 0s in the ON statement for src.Policy_Number? I think Padding is needed, but I am new to SQL so any help
Break periods at the end of the month
SQL Server 2017 CREATE TABLE [TABLE_1] ( PLAN_NR decimal(28,6) NULL, START_DATE datetime NULL, ); INSERT INTO TABLE_1 (PLAN_NR, START_DATE) VALUES (1,’2020-05-01′), (2,’2020-08-01′); …
Query not returning expected data
I’m looking for an explanation for why 1 of the following 3 queries aren’t returning what I am expecting. Assume the following: Anmodning with ANNo=1, ANCpr=1111112222 And the Person table doesn’t have a row with PSCpr=111111-2222 Queries are executed in Management Studio against a SQL Server 2017. Queries 2 and 3 returns the Anmodning row as expected but query 1
How to calculate employees working hours in sql
My company business hour is 7:30 to 18 and 13:00 to 15:00 is lunch time. Important part is about lunch time that should not calculate as working time of employee at all. So imagine employee start working at 8:30 and exit at 15:00 so the time of 4:30 hours should be calculate for him. actually I want to remove lunch
How to split a sentence and get only left 15 or less (Not greater than 15) characters on one column but at the end it could be a complete word
How to split a sentence and get only left 15 or less (Not greater than 15) characters on one column but it could be a complete word. Example: Ali, House Number ABC/123, Sattelite Town, Lahore. Need support here. Answer You can try something like this:
SQL Server – Ordering Combined Number Strings Prior To Column Insert
I have 2 string columns (thousands of rows) with ordered numbers in each string (there can be zero to ten numbers in each string). Example: The end result is to combine these 2 columns, sort the numbers in ascending order and then put each number into individual columns (smallest, 2nd smallest etc). e.g. Colstring1 is 1;3;5;12; and ColString2 is 4;6;
SQL Calculate Consecutive hours [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 2 years ago. Improve this question Need to calculate consecutive hours. Here is the data This the client has a limit of the amount of consecutive hours allowed. Here is the