I am trying to create a SQL report that shows hourly sales. This is from my POS system. There’s a built in table that has basically all the data I need, I’m just struggling to calculate the sum of a column if they are in the same hour. Table vRPTHourlySalesPerformance (stripped down to only show r…
Tag: sql-server-2016
SQL – split numeric into 2 columns?
I am trying to split some numeric keys in my table into separate columns (to help save space in SSAS, lower cardinality) My data looks like the below.. I want to split these into 2 columns… with 4 digits in each column. (where applicable, as anything 1>9999 won’t have anything populated in the …
How to improve performance of multi-database queries in SQL Server where one database is synchronized and the other is not
I have two databases. One I’ll call a is a read-only synchronized database that is part of an availability group and the other is a plain ol database I’ll call b on the same server as the synchronized database. I need to write views in b that read from a, but they perform very poorly in this envir…
How to find ‘MAIN’ master record SQL
I’m running a query: Which returns the following results: I just so happen to know these IDs and am using them for this example. How can I return results like this across the board grouping together linked companies, until I get to the ‘MAIN’ company. There are going to be several other comp…
SQL UPDATE column with self referenced id from another column
I want to update the ParentId of each row that is not null With the Id from the row that has a TemplateId Matching ParentId Id ParentId TemplateID 1001 NULL 86 1002 86 41 1003 43 44 1004 NULL 43 1005 44 73 Desired results: Id ParentId TemplateID 1001 NULL 86 1002 1001 41 1003 1004 44 1004 NULL 43
how to convert a table to another in SQL (similar to pivot, but not exactly)
I have a database table looks like this in the below, in SQL Server 2016: (the max rows for the same ProjectKey is 3) I want to write a query to be able to convert the above table to the following: If it can be achieved by writing a SQL query that would be great. Anyone can help? Thank you
Select specific columns from the stored procedure
I have a stored procedure in other database which is maintained by other team. Assume that it is currently returning 3 columns, and my system only needs those 3 columns but the other team can add few more columns for their own use which is causing my system to fail. Other database SP And below is my query, wh…
Using HAVING COUNT(column name) NOT working as expected
I have the SQL statement, working in SQL Server 2016, below where I’m getting records from an INNER SELECT UNION ALL statement and in the OUTER SELECT I’m trying to extract those records that meet the HAVING COUNT code however, my code is bringing back no records. Here are the records from my INNE…
How to check the current row with next row and update the current row not using while loop and cursor
Is there any way other than using the cursor or while loop to check the current row with the next row and update the current row and do this through all rows in the data set? For example, this is the table we have: This while loop go through each row and update @new_value based on some conditions and then
SQL Server 2016: how to get a single row view
I have been trying to word this correctly, but here’s my dbfiddle Table: Expected Output: CustomerKey processdate Product_Mix Product_Mix_Expanded 101 2021-05-01 12,3,5 CCcount, CHKCount, SACount 102 2021-02-03 1,3,1 CCcount, CHKCount, SACount 103 2019-04-12 4,0,2 CCount, SACount As you can see, I used …