Thanks for helping. I have my table CONVERSATIONS structured in columns like this : [ ID , JSON_CONTENT ] In the column ID i have a simple id in Varchar In the column JSON_CONTENT i something like this : I would like to query and get the id and the last element of exchanges : [ ID , LAST_ELT_IN_EXCHANGE_IN_JS…
Tag: sql-server
How can I select data from last 13 months?
I have two tables with exactly same structure Now I need to ‘union all’ these two tables and I want only last 13 month data every time I run below query I have to run this every month and need only last 13 month data. How can I apply the filter? I have a date column ‘date’ in both the
Invalid Datatype for user defined table type
I have declared the following user defined table type Now when i pass it as parameter to an SP I keep getting the following error Parameter or variable has invalid datatype Answer You must pass this argument as :
Dynamic TSQL Pivot without aggregate function
I have a table like this (‘ExternalPersonRelationTable’) PersonId SubjectCode 4187 3 4187 278 4429 3 4429 4 4463 99 4464 174 4464 175 I want to rotate the data so that every person in the table gets a column and a TRUE/FALSE value for each subject code, i.e. a table like this: Code 4187 4429 4463 …
SQL: Use LEAD() and PARTITION BY to access to the next row following the current row
I have a mobile app browsing history dataset as shown below. DeviceDateTime: Date and Time the User views the page in the mobile app. UserID: each UserID represents a visitor who login the mobile app. PageName: There are different pages in the Mobile App. All visitors would first land on the Home page, and th…
Formatting the surrogate key based on other columns
I have a table in SQL Server I want my registerNumber to be of type batch+branch+id eg a row in student id:1, batch:17, branch:BIT then the registerNumber should be 17BIT1 I tried to do this by using a default value in create table but it does not allow me to reference the columns of the same table edit thank…
Partition By Logic
I have a dataset that has roughly 1 million rows. Without hard coding any claims, what would be the way to get the resulting output? From my research I determined something like DENSE_RANK or ROW_NUMBER() with a partition expression should do the trick.Is there a way to use DENSE_RANK to say “go down th…
Splitting up events that occur over the day boundary
I have a table of events with a start time and an end time, with some events that have a start time before midnight and an end time after midnight. I’d like to produce output that splits up these events at the midnight barrier so they can be counted toward their respective date. The above table could be…
How do I restrict my SQL aggregation Rollups to a specific grouping?
The following SQL returns 5 grouped columns and two aggregated columns: I do want the rollup to the level of TenancyName, but it don’t need all the other intermediate rollup lines. In fact, you can see that rolling up from the Doctor’s (Employee’s) row up to the EmployeeId produces the exact…
how to select third, fourth, fifth date in sql using window function
The table rental of movies has this data: how to generate pairs of : first rental_ts, second rental date, second rental, third rental date, …. second LAST rental date, last rental date, last rental date, null? using window functions- this is not working: i expect: basically i need more than 1 level back…