Skip to content

Tag: sql

SQL apply where clause to an arbitrary query results

I’m working on a system where the user introduces an SQL server/db connection and a valid SQL query, which I save on my system. I’m using python+sqlalchemy+pandas to accomplish this. That query will return a table like this one, in which the only rule is that the query result must have a timestamp…

SQL case when combined with group by

sql

lets say I have a table with 2 columns: car_id, and selling_code if a car got sold at 2020, no matter how many times, it will have a single line in the table with the code 1. if a car got sold at …

Sum rows with same Id based on type and exlude where SUM = 0

I have this table MOVEMENTS: I’m trying to get all the FatherId with the SUM of IN – OUT Movments > 0. So the result would be: FatherId = B not showing because SUM(MovementType = IN) – SUM (MovementType = OUT) = 0 I tried with That gives me the result grouped by FatherId, but I’m no…

Combining a recursive CTE with another query

I have a table of locations each of which can have a parent location I managed to create a recursive CTE which gives me parent location id (plus the original location id) for any given location id e.g. where LocationId = 3 would return: In another table I have a query which will return LocationId as one of th…

How to perform LEFT JOIN with condition

I want to perform something like this Answer If I understand correctly, you want to conditionally join to a table. I would recommend two left joins as in: Because it is the same table, you could also use or in the on clause: However, or in an on clause usually kills performance.

How to calculate employees working hours in sql

My company business hour is 7:30 to 18 and 13:00 to 15:00 is lunch time. Important part is about lunch time that should not calculate as working time of employee at all. So imagine employee start working at 8:30 and exit at 15:00 so the time of 4:30 hours should be calculate for him. actually I want to remove…