I have cumulative counts for two groups over time in this format: Date Group Cumulative Count 1/1/2020 A 1 1/2/2020 A 3 1/2/2020 B 1 1/3/2020 B 2 And I’d like to reshape this data into this format: Date Group Cumulative Count 1/1/2020 A 1 1/1/2020 B 0 1/2/2020 A 3 1/2/2020 B 1 1/3/2020 A 3 1/3/2020 B 2
Tag: sql
How to transform a postgresql select with join to a json object?
I want to transform the result from a select with joins into a json object. I mean this query: should output this: (there are more fields than these. I’m just making the example simple) I found a solution this way: But I think there should be a much better way to do this. Imagine that chat_messages had …
PL/SQL CREATE PROCEDURE – Salary increase based on tenure
I have worked on this for a while but the code did not work and I could not figure out the correct solution. Did I miss something from the code? Thank you. — Question – The company wants to calculate the employees’ annual salary: –The first year of employment, the amount of salary is the base sala…
Trigger for INSERT to validate input data in SQL Server?
My problem: table dbo.student has StudentID like SV001. How can I create a trigger to check data inserted into dbo.student has a StudentID that begins with SV and the numbers in the range 000 to 100? Example: SV099 is valid id to insert, while SV101 is not valid Answer Use SQL constraints: Example : Demo in d…
how to update and append values to array in google spanner
I have a column with data type Array(String) in spanner. How can I append value to an array for update queries I’m able to update the values using this command But the problem with this update is it overrides the previous values. I want to append these values to the ones that are already present. Is the…
SQL Count depending on certain conditions
I have two tables. One have userid and email (users table). The other have payments information (payments table) from the userid in users. I want to count the PaidMonths taking in consideration the following rules: If ValuePaid < 10 PaidMonths should be = 0.23 (even if in the column the value seen is any o…
Multiple tables joined to a table via single column
I am trying to create query, on below scenario. with my skills I am able to join Table A,A1,B and A,A1,C and A,A1,D individually and union them. Is there any better way to achieve same. I am using Oracle as Database. Answer It all depends on what they mean and if you need to know the columns the values are
SSIS Script Task throws Object reference not set to an instance of an object
I am trying to query the database and send the content in the body of the email. When I try to run the package it throws deadlock error. Can anyone please suggest what is that I am missing Script is like below Below is the And the package looks like below.. For every user I need to get all the
LEFT JOIN ON NULL Key Values Combined with GROUP BY
I’m using Teradata SQL and I wrote the following query (pay attention at the LEFT JOIN) Some entries for t1.key2 und t1.key3 (of the left sided table) are NULL. When that’s the case, the rows are not showing in the result, why? Is that Teradata specific, I would expect a LEFT JOIN to show rows wit…
Having and Where in combination with Group By
I have learned to use HAVING when I use GROUP BY instead of the WHERE clause and never encountered any problems with it. Today I saw this on a w3schools.com a SQL Learning Page: Why this should work? When I should use this? Answer The difference between WHERE and HAVING is central to when you should employ on…