The Setup What I am about to describe is run on the following hardware: Disk: 6x 2TB HDD in RAID5 (w/ 1 redundant drive) CPU: Intel Xeon E5-2640 @ 2.4 GHz, 6 Cores RAM: 64 GB SQL Server Version: SQL Server 2016 Developer Both SQL Server Management Studio (SSMS) and the sql server instance are running on this …
Tag: sql-server
Trying to convert oracle sql to Sql Server date for a migration activity
Below is the oracle SQL query currently used in the existing system, I am trying to convert this SQL into a SQL Server query for the new system. I have been trying different format, but I am not successful and also since I am experienced in Oracle than SQL Server. Kindly help me in this regard. Answer Based M…
Query optimization for multiple inner joins and sub-query
I need help regarding query optimization of the below query. Since there are duplicate joins in the main query and sub query, is there any way to remove those joins in the subquery? Answer Since, as you clarified, your sub-query is almost identical to your main query you might be able to use the window functi…
How to get output parameter results and query result from stored procedure?
I have this stored procedure which I call from C# using Entity Framework Core using a DbContext When I call the SP from code, the output parameters are empty. How to get the output parameter values in code? When I call the SP directly from SQL the output parameters are populated correctly. Answer You’re…
SQL Joining One Table to a Selection of Rows from Second Table that Contains a Max Value per Group
I have a table of Cases with info like the following – ID CaseName Date Occupation 11 John 2020-01-01 Joiner 12 Mark 2019-10-10 Mechanic And a table of Financial information like the following – ID CaseID Date Value 1 11 2020-01-01 1,000 2 11 2020-02-03 2,000 3 12 2019-10-10 3,000 4 12 2019-12-25 …
Ranking in SQL Server with a column change
Below is the original table that I want to rank according to the column changes by Install_Status and Dates for each product. Query: This SQL query returns the following results: However, I am not getting appropriate ranking for product A by date. My expected result is as below: Answer You seem to want to cou…
Grouping the result set based on conditions
I am calculating Age of a user based on his date of birth. This gives me the UserId and his age. Now I want to group this result. How many users are in 30’s, How many users in 40’s and how many users in their 50’s.. need the count of users with their age groups If the user is >
SQL – Count rows based on matching columns and value range
Please see below query using T-SQL with SSMS. There are three tables: B, G and L. B has a column Bname G has 2 columns Gname, Gross L has 2 columns Bname, Gname Gross column is an INT ranging …
How to show the closest date to the selected one
I’m trying to extract the stock in an specific date. To do so, I’m doing a cumulative of stock movements by date, product and warehouse. select m.codart AS REF, m.descart AS ‘…
Combine two queries to get the data in two columns
This returns Using this statement Which returns I would like to combine these 2 queries in 1 to get the below result. Tried union / union all but no success 🙁 Any help will be very much helpful. Answer You can simply use the sub-query as follows: