I have Attendance table in which date and attendance is stored and I am passing date range in this query to display attendance report. Now my question is how can I replace 0 (which I am getting as a output if the date passsed doesn’t match with the date inside the Attendance table) with N/A or -1 ? Answ…
Tag: sql-server
Connect to local SQL Server with Sequelize
I recently set up a Local SQL Server Db for development of my node app. I’m having trouble trying to connect to it from my node app. I was able to connect with sqlcmd using the command: sqlcmd -S &…
Renaming index gives error Explicit @objtype id not recognised
I am trying to rename an index and getting an error: Error: Explicit @objtype ‘idx_FinData20’ is unrecognized. I can see both the table and index exist. Then what is the problem Answer First – your syntax is wrong. You need only two arguments, first should be table_name.old_name and second o…
How to have a NULL instead of No Value from a query
I had a query like: Then I tried But when it doesn’t find the named table returns not output, Value or Null value or anything I can work on. This is going to be used as a crosscheck. Does anybody have any idea? Answer Thanks for those who payed attention to what I exactly asked and for their responses. …
Compare between JSON string using openjson in SQL Server 2016
I have two scenarios of inserting json string. I check the json string is already exists or not using the below code and then I insert into the table In the 2nd Scenario, my data is not getting inserted into table as the count is 1. But I want both the string in scenario 1 and 2 to be treated
Get fill rates in another table – SQL Server
I am trying to create a script to calculate the fill rates for each column in a table Data_table and insert it into a second table Metadata_table. The Data_table has 30 columns in it, and some columns have 100% data in them, and some have less than 100% (due to nulls). My code to calculate the fill rate looks…
Split a column in SELECT
I’m trying to get all records where I have a column concatenated with a :. The data stored in table as like this: I want to get below results by removing :extra from column in query: I’m not sure what should I do with my above query. Answer For SQL Server, you would do: You can find the columns us…
SELECT DISTINCT ordering rows by order of appearance of the field
I have this data Id Campo ———– ———- 1 id_recibo 2 col_2 3 col_65 4 UNDCAP 5 id_recibo 6 col_2 7 col_65 8 …
TSQL-ORDER BY clause in a CTE expression?
Can we use ORDER BY clause in a CTE expression? Error message: Msg 1033, Level 15, State 1, Line 14 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. Msg 102, Level 15, State 1, Line 25 Incorrec…
Heap big table with performance issues
I have a business case where I need to store all details about invoices from vehicle workshop (invoice number, dealer,vehicle number, peace, nature of service , …). I have more than 50 columns which are all selected in the same query. In my treatment I need to do some specific treatment in order to get …