I am using pyodbc to fetch total quantity of a product based on a certain criteria called “Strength”. The “Strength” column also has string value for some records, so it is a varchar column. The user enters details like brand, product type, product line, date range, minimum quantity (i…
Tag: sql-server
SQL – Violation of UNIQUE KEY constraint
I am trying to find the solution for the below query: There are declared tables @Country and @WHO (World Health Organization). Insert all possibles countries from table @Country into table @WHO. …
MAX() OVER PARTITION BY not working as intended
I’m having some issues when i try to obtain the MAX value of a field withing a set of records and i hope some of you can help me finding what am i doing wrong. I’m trying to get the ID of the item of the most expensive line, within an order. Given this query: I’m getting this results: Result…
Get distinct column values to an array
Using below query I get the column values to an array. Now I want to get distinct column values from it. marks_details The @columns should be [1,2,3] Answer
Function to convert units based on conversion table
There is a very simple table in the database with metric unit conversions, that looks like: I was thinking about creating a function to convert units that calculates the conversion from the table based on inputs (Unit, FromUnit, ToUnit). For example Conv_Units(0.1, ‘m’, ‘cm’) returns 1…
Query to join DateTimestamp column with another Table with StartDate and EndDate
I have below table with 3 column. Lets say table1 with the following data Another table with 4 columns, lets say table2 with following data What I would like to achieve is check in Table2 for start_date and End_Date where typ_Id = 1 from Table1 where E_Date comes under in Table2 Start_Date and End_Date. for I…
How to increment column by one group wise
I have a table called productLocation, and its data structure as follows, SQLFiddle For each location have its own sorting order for products. But some of the products have 0 as the SortValue Now I need to write a query to update SortValue as, If I consider one location, FkLocationId = 1 In the above data tab…
Sum and Count by month, shown with last day of that month
I have a transaction table like this: I need to sum amount and count transactions by year and month. I tried this: It works like charm. However, I will use this data on PowerBI thus I cannot show these results in a “line graphic” due to the year and month info being in separate columns. I tried ch…
Last record per transaction
I am trying to select the last record per sales order. My query is simple in SQL Server management. The problem is that this database has tens of thousands of records, as it tracks all SO steps. I would to see the most recent record per the SO Answer Well I’m not sure how that table has two Name columns…
Loop through a table valued function
I have a table x which has columns y and z it has 10 rows of data. I then have a table-valued function getDetails(y,z) which takes values stored in table x in columns y and z to return a table tb with three columns. I need to write a tsql query that loops values from table x through function getDetails(y,z)to