I am using SQL Server 2014 and I am faced with the following problem in the WHERE clause of my T-SQL query. My WHERE clause will list the names of towns. However some of the names contain characters like an apostrophe in them. Example: ST JULIEN D’HOTMAN My T-SQL looks as follows: The above does not wor…
Tag: sql-server
Combine data with same ID
I have an assignment where I need to use aggregate functions in the queries. I keep running into a problem where there are multiply entries for the same ID, and I would rather them be combined into one run (added together for the same ID). Output below (IDs to be combined in column 3, “intTeamandClubID&…
Truncate table Dataverse
I am trying to truncate a table in Dataverse, so I have installed SQL Server Management studio, selected my database, and ran the query TRUNCATE TABLE tablename However, I get the error: Is there something that I am missing? Answer The SQL Connection to Dataverse is read-only The SQL connection provides read-…
Need to get the value from a column whose column name is based on a value in another table
Table A has columns ID, COL1, COL2, COL3. Table B has columns AID, ColumnName. I need to get the [ColumnName] value in Table A based on the value of [ColumnName] in Table B. In the example below: For ID 1, I need to get the value of column COL1 (This is the value of [ColumnName] for AID 1 in Table
How can I avoid “stringly typed” code in T-SQL?
Consider some code like I believe that the technical term for such poor code is “stringly typed”. The key issue in the above code is that decisions are being made based on a string output that the developer needs to type and consistently get correct. If anything goes wrong, the language will be in…
mssql execution order guarantee when conversion in where clause
I have following scalar function Problem with that is, that o.aValue could not only have numeric values, so that the convertion can fail, if it is executet on other rows of idmv_value_basic, where attrName is not ‘OM’. For some unknown reason this morning, our MSSQL-Server changed the execution or…
Take oldest record in a set of rows
So, I have this problem, I have this set of records in a table TemperatureID CastingID TemperatureDateTime TemperatureValue 1421294 1073513 2021-01-07 11:53:00.000 1648 1421295 1073513 2021-01-07 11:54:00.000 1698 1421326 1073514 2021-01-07 22:00:00.000 1594 1421327 1073514 2021-01-07 22:11:00.000 1609 and th…
Extract the record for last hour for specific date
I am trying to extract the last hour (TKT_DT) record for number of tickets (TKT_DN) from sales table (PS_TKT_HIST) for specific date (BUS_DAT). I have the following code but it extracts the number of tickets (TKT_NO) for each hour. I want to filter the last hour only. Here is the code I used: I get the flowin…
How to select DISTINCT records based on multiple columns and without considering their order
I’m currently working on a SQL Server database and I would need a query that returns pairs of customers with the same city from a table that has this structure and this sample data I have tried defining a query that joins the Customer table itself but it gives me a table that looks like this with duplic…
How to select the best item in each group?
I have table reports: In human language: there are reports for each month. Each report could be in XML or CSV format. There could be 1-2 reports for each month in unique format. I want to select the reports for all months, picking only 1 file for each month. The XML format is more preferable. So, expected out…