When I attempt to convert UNIX timestamp to a timestamp recognized by SQL I get multiple UNIX timestamps. For example the image shows that the following code will provide two different date/times i.e 05/09/2019 00:00:00.00 and 19/03/2019 00:00:00.000 for the same UNIX time of 1567641600 Answer Ref: How can I …
Tag: sql-server
add not null column take values from other column
I have an existing table say, Items with Id | CreateDate (as DateTime) columns; want to add Year int not null, take the default year from the CreateDate column. The Year value will be futer updated …
Find missing rows between three related tables
I have three tables: Person Dates Absence I need to find all of the person_id’s in the Person table and the date’s from the Dates table who have not booked any absence matching the following criteria: Dates.date_type = ‘PUBLIC_HOLIDAY’ Absence.absence_type = ‘HOLIDAY’ Basic…
Dynamic bit-based flattening of multiple rows by pivoting into additional columns
I have data that looks like this: And I would like it to look like this: I think a dynamic conditional aggregation is required. Any help would be appreciated. Answer Here is a sample implementation of dynamic conditional aggregation: this is the result: Now you can add a value (for example id = 4 and value = …
Using BOTH scalar values and JSON objects as JSON values
I have a local table variable that I’m trying to populate with JSON key-value pairs. Sometimes the values are themselves JSON strings An example of what this ends up looking like: After populating it, I attempt to build it all up into a single JSON string, like so: The problem with this is that it turns…
UPDATE command in a JSON
I’m trying to update a column in my table, but that column stores a JSON Object, but for that I need to do a where with a JSON as well. This is how the data is stored in my RG column: { “Id”:”…
Stored Procedure error – incorrect syntax near else
I’m working on my first stored procedure, and it’s showing an error at the else (where I have a comment at the else below). If anyone knows how I can fix the error, I’d appreciate it. I’ve tried looking online, and I think it may have to do with my begin/end and if/else positioning. I&…
TSQLT – Error while trying to fake a table
Hi I am pretty new for TSQLT Unit tests I am getting error while trying to create a fake table, any help is appreciated. I have my dev project in one database and TSQLT in another database EXEC …
SQL Recursive Count
I have two tables I am joining with the following structure: A ContentDivider can have n ContentDividers as Children and can have m CustomPages as children as well. I want a View that counts the Display the current CustomDivider and the COunt for all the CustomPages as Children of the current ContentDivider. …
SELECT to return duplicate distinct column values
Tying to have query that can duplicate some column values based off values in other columns. ID1 column will always have a value and ID2 and ID3 may or may not. Data in table looks like this. Wanting to to query this table to show resultset like this Been stabbing at this trying case selects, and inserting in…