I’m trying to number rows in which column a and column b is a match and when they occur again regardless of the date. The dataset looks like this and I want the row_number column: rider driver date row_number jim joe 2020-10-20 1 jim joe 2020-10-28 2 jim jack 2020-10-29 1 jim joe 2020-10-31 3 john jane …
Tag: sql
Joining on the same table to get 2 different amount values
I want to get the total amount of a funding source (dim_6) against 2 different account codes (1301 AND 1300). Below is the query that I designed. The above query doesn’t return the correct sum of amount and it doesn’t match the value of amount if I try to run a simple SQL query against 1 account c…
SPROC with Outer-Apply returns too many rows
I have a complicated enough SPROC, which I modified to return a few more columns (from additional tables). What I wanted is to have this SPROC still to return the same amount of rows and the same data, but to fill in new columns (if data exists) as well, for those rows. My modification does fill new columns, …
Cannot use a CTE in a scalar subquery in Db2
I’m trying to use a CTE in a DB2 LUW v11.5.4.0 scalar subquery, but this doesn’t work: I’m getting this error: SQL Error [42601]: An unexpected token “AS” was found following “1 IN ( WITH t (x)”. Expected tokens may include: “JOIN”.. SQLCODE=-104, SQLSTATE…
Combination of dynamic pivot and static pivot in SQL Server
Dynamic pivot combined combined with static aggregates I have a table that looks something like this: I basically need to: Group by a number of fields (Place, State, Category in the example) Count per such group Sum MCount, Cost (and others, not in example) per group, these columns are static Pivot over Categ…
Is there anyway to test the function in SQL?
I have following problem. I’ve made an function which checks dates, warehouse if it’s full, etc.. Here is the code and I’ve added comments so it is more clear and understanble: So basically if I run only the function I don’t get any error. But when I run it with the test date I get fol…
Columns in SQLPlus report display longer than database column size
I have a number of sql reports that used to work fine. Recently however they have started to be outputted with the column widths being longer than they should. According to Oracle’s documentation if a column in an sql report has no explicit “COLUMN” definition at the start of the report, eg …
UPDATE VALUES OF COLUMN IN POSTGRESQL
I have 4 tables: what i want is to update table1 with the salary values from matchsal table with conditions in the others tables, i tried this query: and i got this error: ERROR: more than one row returned by a subquery used as an expression SQL state: 21000 Answer I found the answer ^^” It is simple th…
I tried solving this problem by using MAX function but get the result . Is there a way to solve it by using any other function?
I need to get “second updated_time” for each order and sorting them by “update_time”. I wrote the following query, which is not giving any output. Suppose, we have to calculate get the time difference between second updated time and first updated time against each order_id ,if we sort …
MySQL: Extract Select in “Module”
Assuming the following SQL statement: The there a way to modularize the statement, for instance, extract the second select statement in a file and call this file instead of writing the whole query? Answer I found a way to reuse SQL. Its possible via SQL-Functions and SQL-Procedures