The query below gives me average of case when QuoteStatusID = 6 but it I am having issues with associating the average by Street column. QuoteTable QuoteID QuoteStateID ProjectManager_userID Shipping_AddressID 1 6 12 56 2 6 12 56 3 26 12 56 4 6 12 18 5 26 12 18 Shipping_AddressID 56: 338 Elizabeth St 18: 83 E…
Tag: tsql
Displaying the difference between rows in the same table
I have a table named Employee_audit with following schema, emp_audit_id eid name salary 1 1 Daniel 1000 2 1 Dani 1000 3 1 Danny 3000 My goal is to write a SQL query which will return in following format, considering the first row also as changed value from null. columnName oldValue newValue name null Daniel s…
Select data using closest date
I have two tables like below: table1 table2 I have to calculate TotalPrice (price x Quantity) from each ID for the date ‘2017-07-28’ but condition is that: if no price is available for a given date, the price closest to but before the date should be used. I have tried the below query but its not g…
Joining on the same table to get 2 different amount values
I want to get the total amount of a funding source (dim_6) against 2 different account codes (1301 AND 1300). Below is the query that I designed. The above query doesn’t return the correct sum of amount and it doesn’t match the value of amount if I try to run a simple SQL query against 1 account c…
Combination of dynamic pivot and static pivot in SQL Server
Dynamic pivot combined combined with static aggregates I have a table that looks something like this: I basically need to: Group by a number of fields (Place, State, Category in the example) Count per such group Sum MCount, Cost (and others, not in example) per group, these columns are static Pivot over Categ…
How do select only swipe-in users from the table which has all swipe records
I have a SQL Server table task_swipe_rec which has only 6 columns. I want to know how many people swipe in right now for the assigned task and the list of minutes they spent on those tasks till now. I am expecting to get the list of tasks that are not swiped out (not completed) and number of minutes spent
List No of minutes worked by an employee using self join
I have a SQL Server table emp_swipe_rec which has only 6 columns. I want to know how much time (in minutes) both employees A and B worked for today (last 24 hours). Employee A already swiped out for the day. Employee B is still working. We don’t store two different columns for a swipe in/swipe out. We u…
Conditional Aggregation query not giving correct output
I am trying to find average using conditional aggregation. I have 2 tables in SQL as below RentalExtension Table: RentalItemsID ExtensionDate ExtensionBy_UserID 7 2020-07-27 10 1 2020-07-28 7 The user table contains attributes as listed in select query When I run the query above I get Extension Rate for UserI…
Updating field value based on data across three separate tables
I have been trying to UPDATE the values of a field (IsException) from 0 to 1 in a table (g) based on corresponding data from a separate table (a) in two fields (name, date) when the combination of both appear in that separate smaller table. The problem I am running up against is that the ‘name’ fi…
How can I use Except all in SQL Server?
I’m trying to use this query statement in order to except daysoff and a duration, let’s called holiday from a calendar of a specific month . This what I have tried : and this is the function of Get_Calendar_Date To except the daysoff ( weekends ) , I have used except , but what I got is something …