Forum Widgets
Latest Discussions
Sql to calculate quarterly/annual aggregation aside of monthly numbers
Hi, I am struggling to calculate amounts based on mtd amounts w/o using a cursor. Any idea? create table #raw(quarter int, name varchar(10), year int, month int, amount decimal(19,2)) insert #raw(quarter, name, year, month, amount) values(1, 'aa', 2025,1,2.0),(1, 'bb', 2025,1,4.0),(1, 'cc', 2025,1,1.0), (1, 'aa', 2025,2,6.0),(1, 'bb', 2025,2,8.0), (1, 'aa', 2025,3,10.0),(1, 'bb', 2025,3,2.0),(1, 'dd', 2025,3,4.0), (2, 'ee', 2025,4,9.0),(2, 'bb', 2025,4,3.0),(2, 'cc', 2025,4,3.0),(2, 'aa', 2025,4,1.0), (2, 'ee', 2025,5,15.0),(2, 'bb', 2025,5,1.0),(2, 'cc', 2025,5,2.0),(2, 'aa', 2025,5,7.0), (2, 'cc', 2025,6,8.0),(2, 'aa', 2025,6,9.0) Annual calc is easy, but not sure how to add quarterly number aside: select mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount, sum(ytd.amount) as ytd from #raw mtd join #raw ytd on mtd.name = ytd.name and mtd.year = ytd.year and mtd.month >= ytd.month and mtd.quarter >= ytd.quarter group by mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount order by 1,3,4,2 Goal is to get a report like this:
problem restoring a backup
I have a backup file (xxxx.bak) in my c:\trmp directory. When I launch SSMS signed on as "sa" and try to restore this database, i get this window: I am not able to enter anything for "Database" and the dropdown is empty. Then if I switch it to "Device" I get this window: Now, under "Destination" I am able to enter a database name, i.e. the name I want the database to be referred to after I restore it. However, note the message at the top of the window: No backupset selected to be restored. How do I resolve this issue? Murray
How to create a view with unique record based on date
I have an EMPLOYEE table that looks like this: EmployeeID,Name,PositionID,HireDate,PositionStatus E001,Chelsey,P123,2013-11-23,Terminated E001,Chelsey,P234,2019-03-13,Active E002,Mike,P345,2014-03-23,Terminated E002,Mike,P345,2021-10-13,Terminated E003,James,P543,2015-01-12,Active E004,Samantha,P895,2018-12-13,Terminated E004,Samantha,P430,2020-05-21,Terminated E004,Samantha,P945,2022-06-16,Active E005,Kayla,P459,2023-03-13,Terminated As you can see, the EmployeeID can have duplicate. The reason for this is, there's a secondary ID called PositionID, which essentially describes the employee's role. For example, whenever an employee gets promoted, or moves to a different department, a new record will be created for them with the same EmployeeID but a different PositionID. When that happens, the Position Status of the older role gets set to Terminated, and the new one gets set to Active. The HireDate represents the date teh employee started working in that role. I would like to create a VIEW to filter this to have only the unique employees, based on the HireDate. If there are multiple records for the same employee, I want the view to show only the record with the most recent HireDate. The resulting table should look like this: What's the best way to go about this?
