In this answer it is shown how a literal table can be created. However, I want to perform a full outer join or inner join on two literal tables, but I cannot figure out the syntax of the literal table after the join. I have tried several variations on this: SELECT * FROM (VALUES (1),… Read More How to join two literal tables together in T-SQL
Can you help me to do this view correctly? code: SELECT CustomerName, C_CUR, SUM(BILL_AMT) AS B_AMT, C_CODE FROM dbo.BILLS WHERE (BILL_TYPE = 2) GROUP BY C_CODE, C_CUR, CustomerName UNION ALL SELECT C_CODE, SUM(BILL_AMT) AS RT_AMT, C_CUR, CustomerName FROM dbo.RTN_BILLS WHERE (BILL_TYPE = 2) GROUP BY C_CODE, C_CUR, CustomerName error: Msg 8114, Level 16, State 5,… Read More Msg 8114, Level 16, State 5, Line 2 Error converting data type
Table example: unique ID process date Amount 123 1/1/2023 10 123 1/1/2023 15 123 1/1/2023 20 123 1/1/2023 16 123 1/1/2023 90 124 1/1/2023 11 124 1/1/2023 10 124 1/1/2023 15 124 1/1/2023 20 124 1/1/2023 16 124 1/1/2023 90 124 1/1/2023 11 I would like to count the amounts per unique ID per process… Read More SQL count occurrences of unique id on a specific date
I have a string like this that I want to convert to a DATETIME value. DECLARE @eh NVARCHAR(MAX) = ‘2021-07-19 14:00:39.0000000’ So this is what I tried: DECLARE @eh NVARCHAR(MAX) = ‘2021-07-19 14:00:39.0000000’ SELECT CONVERT(DATETIME, @eh) But it always returns this error: Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date… Read More CONVERT function cannot convert string to DATETIME
I’m trying to retrieve the status for a multi-step process which is stored in a sql server db. Each step is a record in a table. When a client wants to know the status of that process, I would like to retrieve the statusses for all steps and have my query return a status of… Read More Is there a way in SQL Server to get a string from a group of strings based on some predicate?
I have a table like this: DepartmentName | SubDivisionName | Importance Security | Cyber | 1 Security | Airlines | 2 Security | Banks | 3 Health | Children | 4 Health | Elderly | 5 Housing | Housing | 6 Misc | | 7 I want to create a new table ( a temp… Read More Rolling up rows and creating a new row per roll up
Initially in a regular table, there are two columns Key and ParentKey. The Key gets its value as an identity column – automatically. ParentKey – by the expression WITH latest as ( SELECT ProductID, Date, [Key], ROW_NUMBER() OVER ( PARTITION BY ProductID ORDER BY [Date] DESC — order by latest Date ) rn FROM [MyTable]… Read More Two virtually calculated columns in view
I had an interview where they asked me: "Find the most popular class among students using their first enrollment date", where the assumption was that a student would pick their favorite class first. For simplicity, no two EnrollmentDT could be exactly the same and there are no data issues (e.g. a student can’t be enrolled… Read More Converting subquery into window function
I have the following SQL Table 1: id name gender age country ambition 1 Peter Male 20 Italy Doctor 2 Angeli Female 30 Australia Lawyer I want to insert into another table like this method. Output : SQL Table 2 id name details json 1 Peter {"gender":"Male","age":"20","country":"Italy","ambition":"Doctor"} 2 Angeli {"gender":"Female","age":"30","country":"Australia","ambition":"Lawyer"} Any suggestions on how to… Read More Insert JSON into SQL table
converting datetime to float in sql Hello, I have a question, how do I convert the difference of the following dates, for example: (2023-01-02 18:00:00)-(2023-01-01 17:00:00) into a decimal number? I know how the conversion to an integer happens(cast(cast(test as date) – cast(testtwo as date) as int), but here I am confused. >Solution : You… Read More converting datetime to float in sql