I have a problem creating a SQL Server query. In summary, the query should get columns that are sum and count, grouped by customerID, and another column that is a case when by a column that is not used as a grouper column. My problem is to group only part of the syntax, while the case when column does not
Tag: sql-server
Map fiscal years using a date column from one table and taking start and end date from another table
I have two tables in SQL Server. The first table contains a Date column. The second table contains start date and end date for various fiscal years and another column denoting fiscal years (that goes like fy21, fy22, and so on). I want to map the Date in table 1 to the FYs in the second table by checking if
SQL : String extraction from Path
Could you please help on how can I extract String1 , String 2 and String 3 to use these in my SQL queries .. Answer Please try the following solution. It is based on tokenization via XML/XQuery. After that it is very easy to pick up any token based on its position. SQL Output
Fill NULL and get distinct record
I am ingesting data from two different source (source A and source B). Although data may be coming from two different sources, there is a possibility that an ID for a record may exist in both sources. The problem is, there are cases when one source is more complete than the other. In the example below, Source A has a
MIN() vs LEN() Behaviors as Shortest String
I’m trying to solve a question in HackerRank called “Your Weather Observation Station 5”, the question asks to return the shortest and longest city names within the provided table. Demonstration to clarify my confusion: SELECT MIN(City) FROM STATION; Output: A name of city with 4 characters. SELECT MIN(LEN(City)) FROM STATION; Output: 3. Why the MIN() function output isn’t a city
Assign NULL to repeated values in different columns in SQL
I have a table like this: id mail_1 mail_2 mail_3 1 john john_v2 NULL 2 clarisse clarisse clarisse_company 3 NULL julie NULL 4 mark markus_91 mark 5 alfred alfred alfred And I would like to put NULLs where the mail is repeated, for example in the row 2 mail_1 and mail_2 have the same value; clarisse, and I would like
Create a JSON object to be sent as parameter to Stored Procedure
I have a stored proc say sp_proc_1 which takes in a few parameters say @val1, @val2, and @val3. I would like to make a JSON object using the values @val1 and @val2 so that I can pass that JSON object as one of the parameters to a second stored procedure sp_proc2 which I will be invoking from sp_proc1. Please guide.
T-SQL : Alter Database statement inside IF is failing
We have a scenario where we have to change database compatibility based on SQL Server version and we have written below condition for the same. In one of our customer’s server this code is failing with below error and the server has SQL Server 2014 Msg 15048, Level 16, State 1, Line 4 Valid values of the database compatibility level
How can force SQL DateDiff function to stop rounding up?
I am working on a query that tries to count from an exact date to an exact date. Today is 2/16/2022. Between 6/30/2020 and 2/16/2022 19 months have gone by. My DateDiff query returns 20? How can I force DateDiff function to return 19? Answer Here is the solution that worked for me. link to fiddle
Count of two columns while keeping the remaining columns
I need to figure out how to count the combinations of two columns while keeping the rest of the columns visible. This is what I have The code above produces Columns 1 – Column 4 however I need a count of the combination of column 2 and column 4(which is the result of a case statement) as the example below.