I have a data set like the sample below. I need to query and summarize the number of transactions per shift. The problem is that the shift cycle does not align with the normal hours of a day. Eg. The shift for day 2020-01-01 will start at 06:00 AM on 2020-01-01 and end at 06:00 AM on 2020-01-02 Below are
Tag: sql-server
SQL unpivoting columns
How to unpivot table: to this: What I have so far is : but this is giving me count for all months. I need this with month number and count value for that month. Values 1-9 could be any numbers. Answer Got it working with
Remove same permutations from different multiple columns
Sample Data: Excepted ouput: The Logic : Remove the same val permutations and retain the min val1 data. e.g: Remove 1,1 because they’re same. Remove 2,1 because 1,2 exist…etc Data SQL Script : SQL Server 2012 | db<>fiddle Answer Here is one way to apply the two conditions:
I have multiple entry and exit status of a person. how do i get the last status of single person
I have a table in sql in following format Output required Answer In_Time should be the first instead of the last status. Then your result should be instead Userid IN_TIME OUT_TIME Code like below, live demo here Updated If you want to get the last status for In_Time, just adjust slightly condition from Min to…
SSRS giving error as HResult E_FAIL has been returned from a call to a COM Component
When I run the SSRS report with data having 150 000 characters it gives error HResult E_FAIL has been returned from a call to a COM Component I am using the single textbox to display this data. For any other data it works fine. I have searched on some other forums and they say that you can only print 32
Get IN and OUT date from booking table
For example I have this kind of table ———————————————————————- | ID | USER | READER | DATETIME | ——————–…
How to create a stored procedure to copy data from a query to a temporary table?
I have need of inserting data to a temporary table from an existing table/query. The following produces the error detailed below. Throws this error: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword ‘begin’. Answer Correct your syntax, use procedure instead of table : However, i…
Split SQL string with specific string instead of separator?
I have table that looks like: I want to split the string to this: My goal is to show ID and all the strings starting with 1 with just the next number after them. I filtered all rows without ‘%1,%’ and I don’t know how to continue. Answer If you use SQL Server 2016+, you may try to use a
Specify a specific year to be updated
I’m dealing with an old database that contains records of 365 days of the past 3 years. I’m trying to get the 2016 records ‘for example’ and change the year to 2020 and then add my new record to the table so it would contain now records of 4 years. this is what I did so far : I want
T-SQL sub select to retrieve rows with columns that have values instead of NULLs
This example is as close as I can demonstrate for the problem I’m trying to solve. For this case the sub-select has some mostly duplicated rows with the only difference being that some have a delvieryDate and some do NOT. If a row of data is available with the date, I want that. If not I’ll need t…