I am very new to SQL and was hoping for some help. I have a database that is hosted remotely and I want to take a snapshot of all the views and archive them in a local 2019 database daily with an ‘archived’ date field in each of the local tables. So far I have managed to set up an
Tag: sql-server
Difference between data of two different row belonging to two different columns in SQL
Sample Table: Waiter Customer_In_Time Customer_Out_Time Date Pallavi 10:00 12:00 7/5/2020 Pallavi 13:15 13:50 7/5/2020 …
How to search in a SQL database table
I have a table with the following columns: user_id user_name user_unit user_last_name user_first_name user_email I want to write a query that the user declares a string that contains a word/part of a word/user_name/user_id/full name/ext. and the query returns all rows the contains the string, sorted by most r…
Yes/No Format for query column
I have the following query, simplified for this question: SELECT convert(bit, Substring(Max(convert(CHAR(8), tt.transaction_dt, 112) + convert(CHAR(1), tt.trans_live)), 9, 1)) AS is_live FROM …
SQL transforming one row to columns with unknown number of distinct values
I have a following database And the result I want to achieve would look like so, with values for system_code columns being sum of “value” My problem is that there is over 1000 distinct values for system_code so I can’t type them by hand. Database has nearly billion entries so anything that w…
Split 1 row into 2 rows with % of total
create table #testdata ( ID int, Total money ) insert into #testdata ( ID, Total ) select 1, 100 union all select 2, 105 union all select 3, 110 union all …
MySQL to Microsoft SQL Server derived table syntax
I am trying to select data from a derived table created in the FROM statement. I have the following code in MySQL: I’m trying to do the same in Microsoft SQL Server, but this doesn’t work, with an error ‘incorrect syntax’. I’ve tried a few different combinations, and anything I c…
Convert String to Date/Time in Report Builder query in SQL
I have a column ENTRY_MONTH with dates in it as a string like 11/2017. I’m trying to convert the column to datetime, preferably the last day of each month, so in the example above would be 11-30-2017. I’ve tried to no avail. Any advice? Answer You can try something like: This uses a European forma…
How to substring non constant strings?
I have the following query: With this query I try to associate the value of substring to my variable NUMERO_ORDINE when I find the “NR.” string in the Text column. Sometimes I don’t have the “NR.” string, so this query doesn’t work and I get wrong values from substring func…
How to define self-defined log in SQL Server
I am now dealing with a huge table that contains XML each row. My job is to write sp to parse the XML and insert its data to the other corresponding tables row by row. But there is a problem that when the sp interrupts, partial data is inserted into the new table while the others are not. So, I