Suppose I have the following table where there are two sets of observation dates (2015-01-01, 2016-01-01) in the first column. For each observation date, there are associated item_date and item_value. observation_date item_date item_value 2015-01-01 2012-12-31 0 2015-01-01 2013-03-31 1 2015-01-01 2013-06-30 2…
Tag: sql
Return false if no rows for select
I have next query: It works fine, if we have some records in new_date table, but if it is empty for some user, query just returns empty string. How I can return false, if there are no records also? Answer You could just selects an EXISTS expression here: This would always return just a single record, containi…
what are the alternative approaches to optimize this sql query?
I am just a beginner in sql and i am trying to optimize sql query but couldn’t get any idea yet, so i am sharing the query with you guys if anybody help me out through this will be very appreciated. Tables are already indexed but if you have any composite indexing approach or anything you can share it. …
Is there a SQL command to return a count of an item with a WHERE and then the overall total?
I have the following statement: This returns me: How can I also return the total count of this part? My goal is to create a table like this: Thanks Answer A SUM case statement will do the trick:
Find the remainder of the number in a new column in SELECT statement
I am building a program in which the user enters a value, either negative or positive, and this value is added to the table, and through the SELECT statement, those values are stored and the remaining values are found in a new column, and at each entry, the data of the DataGridView is updated. Example: Date N…
Oracle SQL: ORA-01858 error on date fields
I have many tables with a date in their name. For example MY_TABLE_2021_06_01, MY_TABLE_2021_06_02, etc. I’m trying to extract the date from the table name and see if any tables are more than an year old. This is my code: The above code works fine if I don’t include the where clause where table_da…
SQL create new columns based on product classification and client buying the product
I have a table with clients and a table of products each client is buying To retrive each client interest in any product I wrote the following SQL statement This will return me a full list of client and product they are buying as per below Client ID Product ID 100017 7000 100018 7001 100019 7000 100021 7002 1…
The difference between OUT and OUTPUT
In an application that uses SQL Server I came across a code that uses both OUT and OUTPUT parameters. What is the difference between OUT and OUTPUT parameters. Answer As noted in the comments, the 2 are synonyms. From the documentation the syntax for the clause is listed as below: So this denotes that either …
Populating scd type 2 kind of dates for active inactive input data
I saw this question in one of the interviewing practice platforms and initially assumed its a gaps and islands problem, but I couldn’t go ahead with that logic. In ETL tool called Abinitio, there is a function called key_change using which we can solve these kind of problems. But, I am unable to do it i…
How to subtract from certain cell in Bigquery?
I have data date and a in this table my table How I can get b where the value is a subtract by a from previous date? Answer You may use the LAG analytic function here: