So this post remains unanswered and not useful Finding missing month from my table This link Get Missing Month from table requires a lookup table… which is not my first choice. I have a table with Financial Periods, and a reference number. Each reference numbers has a series of financial periods which may start anywhere,… Read More Find missing months in SQL

I have a message error "Invalid int argument data type for substring function argument 1." I don’t understand why ! UPDATE DBO.NUMBER SET NUMBER = 22675442455 WHERE SUBSTRING(ID,1,2) NOT IN (’67’,’68’) Thanks for your help ! >Solution : SUBSTRING required first argument as STRING, so simply convert it to VARCHAR. UPDATE DBO.NUMBER SET NUMBER =… Read More SQL SERVER INVALID TYPE

I have this query that works excellently and gives me the results I want, however, does anybody know how I can remove any rows that have 0 orders? I am sure it is something simple, I just can’t get my head around it. In other words, should it only show the top 2 rows? SELECT… Read More SQL Selecting & Counting From Another Table

How do I query a json document with field names like "$id"? SELECT [ID] FROM OPENJSON( ‘[{"$id":42},{"$id":43}]’, ‘$’ ) WITH ([ID] NVARCHAR(25) ‘$.$id’) WHERE ID = 42 >Solution : You need to use quotes (‘$."$id"’), if the key name in the path declaration starts with a dollar sign or contains special characters: SELECT * FROM… Read More How to escape fields with "$" in the name

ALTER FUNCTION[dbo].[DateValidation] ( @P_Input NVARCHAR(55) ) RETURNS NVARCHAR(55) AS BEGIN DECLARE @V_Results DATE DECLARE @V_Result INT DECLARE @V_ERROR NVARCHAR(MAX) set @V_Result=’SELECT ISDATE(@P_Input)’ IF @V_Result=1 BEGIN SET @V_Results=’SELECT CONVERT(P_Input,getDate(),2)’ RETURN @V_Results END SET @V_ERROR =’NOT IN DATE FORMAT’ RETURN @V_ERROR END GO >Solution : First of all you are setting variable @V_Result with string (Command). If… Read More Conversion failed when converting the varchar value 'SELECT ISDATE(@P_Input)' to data type int

I’m aware of using various functions like CHARINDEX and PATINDEX, but only for a single "spot" in a string. What I’m needing to do is parse out the characters of a string that exist between a < and >. For example: <ul><li>Good afternoon,<br /><br />Please see the NDC number listed on the attached delivery ticket.<br… Read More SQL: How to parse a string only where certain character patterns exist

I need to find 2 values and related columns where a given number either sits between the column values or is either of the 2 values. Really confusing but here is a table: I have a car_id 2 which is going at speed 12 and I want to get a fuel value. I need to… Read More Finding the 2 rows where a given value sits between 2 values in a table

I have been looking for solution for selecting parent-child relation from table [Group] which contains a xml column. [Group] table has the following structure: ID – int Content – xml There is xml data – parent-child relation in column Content <root> <person name="John"> <device name="notebook" /> <device name="xbox" /> </person> <person name="Jane"> <device name="TV" />… Read More Select from parent-children xml content

I have a table name deployment in which show the id of the deployed personal base on their task as columns the problem is 1 person can be deployed in multiple task and when I select the table is show as followed: date | task1 | task2 | task3 | task4 —–+——-+——-+——-+—— fri + 5046… Read More Is there any way to delete duplicates value in one row?

I have to select from a table with JSON array column Id Name ListCol ——————- 1 test1 [1,2,3] 2 test2 [4] 3 test3 [1,4] 4 test4 [2] If I query: SELECT [ListCol] FROM [dbo].[SomeTable] it returns [1,2,3] [4] [1,4] [2] What I expect is 1 2 3 4 >Solution : Simply use distinct with openjson… Read More Flatten a JSON array column to distinct result