I have a table (named profile) in my Postgres database, which has 3 columns: ID, Day, Ftfm_profile of type jsonb, I tried to extract the row where the profile name (ftfm_profile->’name’) begins with ‘LFBB’ ( sql: LFBB%) using the wildcard as following: the expected result: I canR…
Tag: sql
Oracle – Count based on previous and next column
I’ve got a rather unusual question about some database query with oracle. I got asked if it’s possible to get the number of cases where the patient got a resumption on the same station they were discharged from within 48 / 72 hours. Consider the following example: Case Station From To 1 Stat_1 202…
inserting a variable into openquery
I’m trying to insert a variable into the openquery expression, but unfortunately I get an error, does anyone know where I got this error? Arithmetic overflow error when converting expression data to int data type. Answer OPENQUERY requires a literal string, you can’t put variables into it. As such…
Count seen users per week
My input table looks like: id user_id at 1 1 2020-12-07 13:07:35 2 794 2020-12-07 13:12:17 3 794 2020-12-07 13:12:26 4 794 2020-12-07 13:12:27 5 263 2020-12-07 13:12:50 6 263 2020-12-07 13:12:50 7 263 2020-12-07 13:13:10 8 1029 2020-12-07 13:18:07 9 1029 2020-12-07 13:18:07 10 378 2020-12-07 13:19:10 11 378 2…
Oracle SQL: Transfer certain records from one table to another filtering rows based on condition
Need to transfer certain records of some columns from Table1 to Table2 but filtering rows based on condition. Lets say Table1 looks like as shown below, has many columns in it. In Table2, need to insert rows from Table1 based on following: First, select A, B, C, D, E, F from Table1 where D=’xyz’ a…
AWK – Parsing SQL output
I have a SQL output something like below from the output of a custom tool. Would appreciate any help in finding what I am doing incorrectly. I am trying to pipe this output the columns I need in my case column1, column2, and column7. I have tried piping out like this but it just prints column1 tool check | aw…
SQL how to select only single row using joining?
I have the tables: tblClients: tblKeys: tblEntries: lClientID and lKeyID (optional) refer to above tables. I need a SQL query which gives me the list of the users from the tblEntries only with maximum datDate field. Now i used the follwing SQL query: The problem i encountered is the datLastAccess can contains…
How to query previous 8 quarters for quarterly data report using sql server?
I want to query the previous 8 quarters from today’s date. Example: last quarter from today’s date = ‘2020-09-30’ and last 8 quarter from today’s date is ‘2018-10-01’. I want the last 8 quarter previous (‘2018-10-01’) mark as Q1 in my query result instead …
How to list last 7 days records in postgresql?
I want to display current date and previous 7 days. the query doesnt work for me. Answer I doubt you have future entry dates. So don’t use between. Instead: Note: If you want to count the current date as a day, then you want interval ‘6 days’.
Table, Row valued UDFs in SQL Server
SQL Server (and some other databases) support a Table-value function. Is there a such thing as a Row-valued function — where based on a set of input values, a specific row would be returned, or is that type of function type not supported. If not, why isn’t it supported? The example SQL Server give…