I have a table and data like below: Employee: LeaveRequest: LeaveUpdateLogs: Now here I want to calculate statistics as below for a particular department: Total number of requests sent and received for DepartmentId 100. But I am confused here for getting data for “Sent” and “Received” …
Tag: sql-server
Modify query so as to add new row with sum of values in some column
I have table in SQL Server like below using below code: How can I modify my query in SQL Server so as to add new row with sum of values in col2? So I need something like below: Answer You could use ROLLUP for this. The documentation explains how this works. https://docs.microsoft.com/en-us/sql/t-sql/queries/s…
Sort column values in a particular way
I have 2 tables: I want to get average of points for each student and each course and sort the result by Student ID and Course in order (Mathematics,Physics and Chemistry). The output should be the following: For each Student the course order should be (Math,Phys, Chem) and also it should be ordered by Studen…
Could not find control ‘DropCity’ in ControlParameter ‘City’ ERROR
Context: I am trying to nest two dropdownlist(ddl) inside a gridview, the first ddl is called “Ciudad” and the second one is called “Comuna”, I want that according to the “Ciudad” I choose the “Comunas” change to that corresponding “ciudad”. but I ge…
How to order columns results in another column (SQL Server)
I have this table The key columns in my table are RowCnt , Lvl , a_TargetID , b_TargetID I want to put the name of the column as the order of their value in the a_1st a_2nd a_3rd a_4th so the final results will be like this I can do this but it seems very wrong I wonder if someone
Trying to delete duplicate rows in SQL Server where the difference is the date or batch number
I have this query: Which is returning the following results ID_NUMBER INCEPTION_DATE OCCURRENCE TRANSACTION_DATE FILE_LOAD_DATE BATCH_NUM 112897732 2008-09-15 4 2008-07-03 2008-07-07 17:57:19 06341 112897732 2008-09-15 4 2008-07-13 2008-07-18 03:35:55 06753 828194721 2008-11-11 1 2008-09-06 2008-09-17 02:50:4…
SQL – get rid of the nested aggregate select
There is a table Payment, which for example tracks the amount of money user puts into account, simplified as What is need is to receive a table with balance before payment moment, eg: Currently the select statement looks something like How can I rewrite this select to get rid of the nested aggregate selection…
SQL ROLLUP with COALESCE
The data: The query: The output I get is like this: It gives null every individual brand item not grouped, and I want the last null to be ‘Grand Total’ not ‘Monthly Total’. I tried putting PurchaseDate or Brand column in and out the ROLLUP but it doesn’t seem to work for the Bran…
How to use Left Join in subquery SQL Server?
I am trying to use left join in subquery in SQL Server. My query looks fine to me but it gives syntax error. This is my query: This is the error I’m getting: This is my table structure: Answer As @Chris mentioned, the query is bit incomplete. I guess you are trying to do something like this: This query …
LEFT Join on a Subquery with specific criteria
I have two tables that I am trying to JOIN I am attempting to Select from table1 a and LEFT JOIN table2 b on a.id = b.id – however, I want to only select on the id in table2 where MAX(EndDt) Is something like that doable? Answer There are a few ways you can do this. I make some assumptions