I need to convert the following logic to python and SQL (SQL query is more imp): I have a table with ID and Date columns. I need to add a column called “Week_Num” such that: Every time it sees a new ID, Week_Num becomes 1 7 dates correspond to 1 week so if the first week begins on 29th Oct
Tag: sql-server
Need help in framing SQL Query
TABLE-1 OrderDate CustomerNo ProductId Quantity 2021-03-01 1 1 10 2021-03-01 1 3 20 2021-03-02 1 2 15 2021-03-02 1 3 10 2021-03-03 1 1 10 2021-03-03 1 5 25 This table also contains data for other customers also Customers 2,3,4 … TABLE-2 ProductId ProductName 1 P1 2 P2 3 P3 4 P4 5 P5 Products are not Fix…
Is is possible to create a view based on a table in a different server?
My client have an azure service app MyServiceApp, its database called MyCloudServer (its data is taken from a legacy MyPremiseServer). The MyPremiseServer is a server located on my client’s site, because the cloud’s service app MyServiceApp can’t access it, They created the MyCloudServer wit…
SQL procedure group by parameter value
I want to use group by functionality with dynamic value. Ex: When I code like this I get the error == > Each GROUP BY expression must contain at least one column that is not an outer reference. Any workaround for this? Answer You need to be very careful with this type of code as it is prone to SQL
Query to select the manager and total salary of employees under the manager
I need to write a query to select the manager and total salary of employees under the manager. The salary of employees should roll up to Manager based on hierarchy. Mgrid references to empid in the same table. Table : Employee Output should be ManagerName and Salary In the above example : M2 has M3, M4, M6 di…
Merge several rows into one if they have a gap less than a 5 seconds
I’m trying to find a SQL query that lets me to merge some rows into one from a table that have a gap less than 5 seconds. For example, I have a table like the following: So, I want to have a result like this: For John there are two rows that have a gap less than 5 seconds, so
Merge not inserting new values
I’m trying to use MERGE to insert new values to a table only if they don’t already exists in the same table. This is the query I am using: MERGE [dbo].[TARGET_TABLE] AS Target USING (SELECT [NAME] …
T-SQL: How to return a result set that excludes values based on an Exclusion table
I’m using SQL Server 2016 and I want to return a result set which has been filtered based on an “Exclusion” table. The rows in exclusion table could contain a [Make] code, a [Color] code or a combination of [Make] & [Color] codes. The data table has approx 450K rows. NULL values in the a…
Unable to Query XML Document with SQL/OPENXML
I would like to query the following XML-File using SQL: Using this code I would like to get the value for “mRID”: However, the result is: mRID NULL How to get the correct value for mRID (‘2f6f8b82348440b1b121bca06311945d’) instead of NULL? Answer Microsoft proprietary OPENXML and its c…
Performing subtraction in SQL/finding the balance
SQL table here: http://sqlfiddle.com/#!9/abe1da/9 Current Table: Year Month Type Accounts Amount 2021 1 Actual abc 20 2021 1 Actual def 30 2021 1 Actual ghi 40 2021 1 Actual X 7 2021 1 Actual Y 3 2021 1 Actual final 105 Expected Year Month Type Accounts Amount 2021 1 Actual abc 20 2021 1 Actual def 30 2021 1 …