I’m not an expert by any means but I write SQL queries fairly regularly. I am pulling data via Crystal Reports 2013 from SQL Server 2016 (not positive about the version). Just as an aside, CR permits parameters so that’s what {?DateStart} is. {?DateStart} and {?DateEnd} are regular dates, {?Servic…
Tag: sql-server
Assign new ID to each row dynamically
I have a table with over 9000 rows. Each row has some weird ID. I would like to re-set each ID as following 000001,000002,000003, etc… (dynamically). Is there an easy way of doing it?
Invalid column name using sp_MSForEachDB in SQL Server 2016
I am attempting to query multiple databases housed on the same SQL Server instance using sp_MSForEachDB. There are 8 databases that have the table man_days with a column named servicetype. I have …
Insert only selected field with multiple value into one value with delimiter
I have two tables, one is to record the queue, another one is record the data for each queue Queue Table Id Queue_Id 1 1 Data Table Id Queue_Id Item Field 1 1 A Vege 2 1 B Fruit 3 1 A Vege 4 1 B Fruit Now I have to create a stored procedure that will return a temp
How to calculate the degree of agreement by row comparisons in SQL Server?
For a minimal, reproducible example (reprex) let’s assume I have a database object (dbo) in a Microsoft SQL Server and I want to query things in T-SQL. My dbo looks like this: Animal-ID Marker-ID …
Need to modify a variable data to use as a “LIKE” in a COUNT query
I’m trying to use a numeric variable on a LIKE statement, but I do not know how to change the data to a numeric value. Before we start, I apologize because I can’t copy the table structure as I’m …
Invalid syntax error near ‘<' when using case when variable, works fine without variable
I am trying to resolve this SQL issue where my code throws an error Invalid syntax error near ‘<‘ when I use the variable @totalprice. Without using the variable, the CASE works and the query executes correctly but I want it as a variable Thanks Answer If you want to assign the result of the CA…
Extract strings till the second delim SQL
I wanted to extract all the details till the second /(forward slash)from my table in SQL Server. Any ideas? Answer Perhaps this will suit your needs:
Find groups that are missing values of one column
Hopefully someone can send some light on an issue I’m trying to resolve. Types Codes I would like to be able to determine those Code/ID pairs that are missing a particular type. The result of this query should yield. Answer You need conditional aggregation for this: Another option is to cross join the p…
Match column name with data from other table
I have dynamically created a temp table #temp1 that create column dynamically based on a json string. The table will look like as below #temp1 ID Field FRUIT VEGE 1 Field1 A B 2 Field2 C D I would like to fill in the value column only in result table based on the #temp1 table, the field and type_id is