Tag: sql

How can force SQL DateDiff function to stop rounding up?

I am working on a query that tries to count from an exact date to an exact date. Today is 2/16/2022. Between 6/30/2020 and 2/16/2022 19 months have gone by. My DateDiff query returns 20? How can I force DateDiff function to return 19? Answer Here is the solution that worked for me. link to fiddle

In PostgreSQL, how can I convert some boolean columns into an array of those, that are true?

postgresql sql

Given this table How can I select it into this result? Answer You can convert the whole row into a JSON, then use a JSON Path query to get only those keys (=columns) that are true: to_jsonb(t) converts the whole row into a JSON value. The JSON Path expression splits that into key/value pairs and then aggregat…

Iterate over data set and insert sequence ids for rows

oracle oracle-sqldeveloper sql

I have a data set with more than 2 million rows in a Oracle SQL table and want to do some association analysis on this. To apply a sequence mining algorithm to this data, I need a column named ‘sequenceId’ and a column ‘eventId’. The table structure looks like this: time pId uId Now I …

Can’t overwrite javascript array

arrays javascript node.js slice sql

I want to track changes of “results”. If the results.length increases, the array will be overwritten and saved. If the length decreases, then the array will be overwritten, but the new value won’t save. Answer I found de wae!

autoincrement number function-postgres

auto-increment function postgresql sql

i have a table like this: id person 20 adams 20 george 40 jina 46 rico 80 naya 90 john 90 peter 90 richard i want to find a way to select a new_id starting from 1 and increazing +1 every time id is different. for example i want a select with a result like this: new_id id person 1

Oracle SQL : how to specify Time Zone Region

date oracle sql time zone

Underlined, as hours are not specified, that means that hour is ’00:00′ I would like to specify that this is for Europe/Paris time zone region. Can you help me set-up this ? Thanks Answer A DATE data type has the components: year, month, day, hour, minute and second. It ALWAYS has those components…

How to Count from sql as a value in Npsql

c# execute npgsql postgresql sql

I am using Npgsql to access postgresql database and I am running a query: The query will return value 4, but my a gets -1.what is the problem here and how can I solve it? Answer It looks like you are using Dapper. If so, you want QueryFirst not Execute, as Execute just returns the number of rows

Converting XML to a dataframe R and extracting value from within

r sql xml

I have the following XML which I am trying to convert into a data frame in R or a table within SQL: I’ve tried using XMLtoDataFrame (“xml” library) but this just creates a table without any values. Would anyone be able to help with this please? Open to a SQL solution. Edit: I’m looking…

Count of two columns while keeping the remaining columns

sql sql-server

I need to figure out how to count the combinations of two columns while keeping the rest of the columns visible. This is what I have The code above produces Columns 1 – Column 4 however I need a count of the combination of column 2 and column 4(which is the result of a case statement) as the example bel…