Please see the table given below. The table contains the json string and need to create a json array with those json string. But When I use JSON_Query and For Json Path it adds additional header. (Alias name or the source column name). How to generate the json array without alias name or source column name. P…
Tag: sql-server
Select Distinct Top One Record by Date
I am writing a script to compare software between servers but have a many-to-many relational situation to execute this cleanly. I’m thinking a distinct-top by latest date type of query may help. So that I can go from this To this shortened and clean list I am not exactly sure how to approach this? Shoul…
How to loop through table using while loop and create another table with values needed
I have two tables: MainTable and MyTable. MyTable has unique ControlNo and ID. I need to add very first EffDate from MainTable to MyTablebased on ID and ControlNo. For that I need to look at …
Alternate approach to WITH CTE and large UNION query
I’d like to rework a script I’ve been given. The way it currently works is via a WITH CTE using a large number of UNIONs. Current setup We’re taking one record from a source table, inserting it into a destination table once with [Name] A then inserting it again with [Name] B. Essentially cre…
Group by one Column in Sql Server and Order by Another Column not contained in either an aggregate function or the GROUP BY clause
i have table “ApplicationEventsSearch” with two column Keyword and EventDate i want to have a query that return result as distinct keyword , but ordered asc by EventDate i have tried many combination , but none worked Data Desired Result and what i have tried so far Answer Aren’t you after j…
selecting max value from table with two variable colums (microsoft SQL)
i´m working with a table that looks like this: Start https://i.stack.imgur.com/uibc3.png My desired result would look like this: Result https://i.stack.imgur.com/v0sic.png So i´m triyng to select the max value from two “combined” colums. If the values are the same amount (Part C), the outcome does…
SQL How to avoid groupby
I have this query that it results is not correct and I assume it groupby issue Fiddle This is my dbfiddle Query Result Expected In reality only user1 has done 4 requests Answer Your result shows one registry with ‘Title1’. Per registry you can have many users (in table RegistryMember). And per reg…
Create daily logs based on records that contain multi day values
This is an extension to this question: Break down long multi day periods of time into several single day periods where the problem was solved using Tallies. The situation I am dealing now is let’s say I have a table like this: The goal is to create a daily outage report over a period of time (Let’…
How to capture first row in a grouping and subsequent rows that are each a minimum of 15 days apart?
Assume a given insurance will only pay for the same patient visiting the same doctor once in 15 days. If the patient comes once, twice, or twenty times within those 15 days to the doctor, the doctor …
SQL PIVOT-WINDOW FUNCTIONS
With the sql query I get table 1 table 1 Trying to pivot using window functions below on Server 2012 fails My desired output Answer Use conditional aggregation Also, you can try with pivot –