I tried the following, but that doesn’t work. Answer In this example, I’m composing a select statement based on list of columns from a table (passed to function as a parameter) whose name satisfies certain condition. As it is Scott’s emp table and its columns don’t have any suffix, I&#…
Tag: sql
Create dynamic list of tables based on list with wildcards
So I have a list of tables that I want (with wildcards) That gives me Now what I need is a list of all tables mentioned in the table config.datalist So I want a list of: id 1) all tables in database TEST of which the schemaname starts with audit id 7) all tables in database TEST in the schema
Wrong data output on basic select condition
i’m trying to get the values which Dates bigger then 2021-07-17 and Cities starting with ‘G’. I have id in the PERSON table, id (references from PERSON(id)) and date in VACCDATA table, id (references from PERSON(id)) and city in ADDRESS table. While i only want the Row 2 (because it’s …
How to achieve generic Audit.NET json data processing?
I am using Audit.Net library to log EntityFramework actions into a database (currently everything into one AuditEventLogs table, where the JsonData column stores the data in the following Json format: Me and my team has a main aspect to achieve: Being able to create a search page where administrators are able…
Snowflake SQL Filter by transactions in the last rolling 30 days
I have a data table something similar to having a customer ID and an item purchase date as shown below. As a filter, I want to return customer ID IFF a given Customer ID has at least 1 purchase in the last 30 rolling days. Is this something that can be done with a simple WHERE clause ? For my
Distinct few Columns and check condition on the column not included in distinct columns
Table “Module” has these columns How to select distinct columns of ModuleGroupDisplayAs and ModuleGroup And After that, Add column on checking these distinct columns has particular UserID and CompanyID. That means I want to check condition that distinct columns has this particular userID and Compa…
PostgreSql Right Join From 3 Tables [closed]
Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers. This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers. Closed las…
How to count and order correctly result from table
I have like this task currently: (sql fiddle: http://sqlfiddle.com/#!9/431723/49/0) It is necessary to form a SQL query to the files table which displays the number of files based on the success of the conversion and the presence of a good rating from registered / unregistered users. Output format: user_type …
Calculate distinct values per day that resets each month (Big Query)
If I have table like this that contains unique id of a store and the date when they make an order store_id order_date A 01-Jun-21 A 02-Jun-21 B 02-Jun-21 C 02-Jun-21 A 03-Jun-21 A 04-Jun-21 D 04-Jun-21 A 01-Jul-21 B 01-Jul-21 I need to aggregate it by day, but each day should only consist of store_id that nev…
Converting STUFF + FOR XML from SQL to Snowflake
I can’t quite figure out how to convert this to snowflake as I am still trying to learn it, is there a STUFF equivalent? Thanks Answer It is LISTAGG: STUFF + FOR XML is an old workaround used for version of SQL Server that does not support STRING_AGG natively. The Snwoflake equivalent is LISTAGG functio…