For example I have this table: I want to get the row where the PersonID occurs for the second time. So the desired output needs to be (for one of the person ID) : What would be the query for this scenario? Answer Use ROW_NUMBER() Function For this scenario Please Refer this link for how to use this function d…
Why my after update trigger is executing on insert although I have separate trigger for insert as well
Answer First of all – for the INSERT – I would use a DATETIME2(3) column with a default constraint – then you do not need a trigger: Now, each time you insert a row and you do not specify the dtEnter column in your list of columns to insert values into – it will automatically be set by…
Get max date based on another column in SQL
I have a table variable which consists of columns id, date and status as shown below And follows is the sample data Also declared a variable as shown below. From this table I need to get the row which containing the maximum value of date( MAX(dates) ) where status is ‘ABC’, from the above sample v…
counting consecutive months in sas
From the sample data below, I’m trying to count how many consecutive months a member has. In the event if an ID has a gap month in the middle, the output should show the most recent count of consecutive months this member had, see example below. dataset=one ID Month 72 01SEP2020 72 01OCT2020 72 01NOV202…
Zero lead digit for a number removed when concatenating in SQL
I have written a couple of PL SQL Functions to return a currency and amount from a compressed field. These work perfectly individually. e.g. returns GBP returns 0.1 Concatenating the 2… returns GBP.1 I want it to be GBP0.1, any idea what is going wrong? The amount function returns a NUMBER the currency …
MySQL merge two tables with the same IDs
In table_A I have data from this week, the IDs started from 1 up to 5000. In table_B I have data from last month, whereas the first let’s say 3000 IDs conflict with the ones in table_A, and it’s not the same data for all of the IDs (ID 1 in table_A is not the same as ID 1 in
SQL SELECT “pair” records
There are cities, suppliers, and customers tables. Each supplier, (as well as customer), is located in one and only one city. How could I select pairs of supplier and customer within the city, where each of them is mentioned only once? P.S. if there is no “pair” NULL should be instead. E.g.: cityI…
is it possible to do a conditional concat?
I have a query that pulls different data but for one field it concats its since the URL is partially filled in my db but the problem is if logourl is blank then I just get https://my_url.com instead of the full URL. This is having down stream impact. Is there a way to only concat if logurl is not null?
SQL Query between a date range and a time range in MariaDB
I’m having trouble writing this query to give me any results. I’m using MariaDB as well. The DATE_ADD clause works just fine and gives results within that interval, but as soon as I add in the TIME function nothing is returned in the results. The CallDate format is 2021-09-21 HH:MM:SS I have tried…
How to create new count column based on adjacent combinations in existing table
I have a simple table I’ve already built in BigQuery and all I want to do is what feels like a simple count of the number of times the combination of the person_id and the specific activity in the activity column has appeared in that table and create as a new column with a value/count of the adjacent co…