I am developing a data validation framework where I have this requirement of checking that the table fields should have at least one non-null value i.e they shouldn’t be completely empty having all values as null. For a particular column, I can easily check using If it’s greater than 0 I can tell …
Tag: tsql
SQL -Listing between Two dates for each record
My data source looks like below. And I want output like below. Basically, it should list each Employee multiple times for each date between those periods. For quick and easy, below is the Temp Data code. Answer Build a calendar table and join that.
How to get specific part string from column
I have two tables one is SifraArtikla The second table ArtikalUsluge extend the SifraArtikla ID I need to get from PuniNaziv column ‘PW60’ if ArtikalID is 1 else if ID is 2 ‘PW120’ else if ID is 3 ‘pw250’ I tried with if else statment but it didn’t work. Can anyone gi…
SQL Server: Convert YYYYMMDD-HHMM and YYYYMMDD to mm/dd/yyyy hh:mm
I am trying to convert the time format from YYYYMMDD-HHMM and YYYYMMDD to mm/dd/yyyy hh:mm. My issue is that the data is not consistent. Below is some exemple: I tried to use: but the issue is that some data does not have time. Answer You can do it like this:
Combine like rows based on substring of one column?
I have a table of parts that looks like this: Part Part Num Thing1 Thing2 Thing3 Thing4 Door 10105322 abc abc Door 10105323 abc abc Door 10105324 abc abc Door 84625111 abc abc abc Door 84625118 abc abc abc Door 84625185 abc abc abc Door 56897101 abc abc The part number is always 8 characters. For many parts, …
Determine if an event happened every hour for 24 straight hours
Edit – I am still digesting the great responses and learning how they work, awesome knowledge! Using SQL Server 2016, I am trying to figure out if any users have done something at least once every hour, for 24 consecutive hours. I have a table similar to this, in the example below, the only user who qua…
Convert table data and reformat
I have table named Table id Level1 Level2 Level3 1 US CA 13000 2 FR PA 30000 3 US CA 24000 4 US LA 10000 5 UK LN 500 6 UK LN 600 7 FR PA 888 8 FR DF 1000 and I would like to convert it to the (First Conversion) below format which will sum Level2 and column
Display records that are not present in a table and a given date range
Let’s say I have two tables – Student and Student Attendance tables. The Student table displays basic information about the student: Student# FirstName LastName 201710 John Smith 201711 John Doe 201712 Anna Sy 201713 Chris Dy While the Student Attendance table displays the TimeIn and TimeOut of th…
How to extract numbers from a string SQL
Ts there a way to extract numbers from a string? In my database there is a column called Reference and it contains multiple numbers CM|319|14163|||109|405000 Is there a way to get the first number like this? select CM|%s|… as ParentId from table So the output should be 319 And maybe even multiple like s…
Trigger to create or update records in another table
Edit: This question is mostly solved. What I am still trying to do might require a different approach. I am adding a trigger to a legacy database table, in order to automatically create new records for a related table. This is not homework. Here is my DB Fiddle on SQL Server 2017. https://dbfiddle.uk/?rdbms=s…