I understand window function but don’t understand why they are called window functions. When we say the “window” is it both the partition and the order by clauses? Answer Window functions operate on a range of rows, defined by the OVER clause. It is like you are looking through the window, y…
Tag: tsql
Can I Select DISTINCT on 2 columns and Sum grouped by 1 column in one query?
Is it possible to write one query, where I would group by 2 columns in a table to get the count of total members plus get a sum of one column in that same table, but grouped by one column? For example, the data looks like this I want to get a count on distinct combinations of columns “OHID” and
tsql to find members not contacted in past 18 months
I have a table that has ContactNumber (Bigint), MemberNumber(bigInt) and ContactDate(date). The table has millions of records (sample data image attached). I want to get all the records where member has not been contacted for last 18 months. How do I do that using tsql? Your help is highly appreciated. Thanks…
What is the syntax of a string literal in T-SQL?
What is the full syntax of a string literal in T-SQL? I could not find any explicit definition of string syntax in the official documentation, and any blog post I could find only mentioned certain aspects (like the N prefix). Additionally, any posts on Stack Overflow or other sites that asked how to escape a …
Build change history from table with historical row values
I have table that is storing historical values for data in other table: Now I need to generate from such table “change log” which will show what was old value, new value and when update occur: Unfortunately, I can’t change structure of the existing table and can’t put old value there, …
Sql Grouping insight Xml
I need to get different row data to 1 row based with id and need to group by its status. I tried in several ways and finally found a solution using xml, but when I use xml I don’t get the output I want. This is a sample code I used to try the logic. returned output: expected output: Answer
Update duplicate data with same unique-identifier
I have the following table in my database: So I want to generate Unique-identifier and Update the UniqueID column as shown below: If values of UniqueID are the same, then they must have the same Uniqueidentifier. I have tried to write a CTE as below: But am not getting what am looking for. Very knew to the wo…
Not able to display Chinese character in application
I have a column in which Chinese characters are stored and the column contains a customer name in Chinese language. When I select the column in SSMS I can see all the names in Chinese language but when the same is done from application the application displays ‘??????’ in all the fields of that co…
Group rows with a value depending on condition of a different column SQL
I have a list of rows that I need to group by a key and for each group the value of two columns depends on a condition from a different column. For example should become That means, for the value of …
Find date range for every row. LEAD/LAG but with different types/actions
I have a table with following data: There are two actions possible. ‘Remove’ and ‘Install’. How can I find Place for Code A for given date? For example for 2015 it will be Place 1. For 2020 it will be Place 2. For 2025 it will be Place 3. I think the first and the most difficult step i…