I have a table ‘Amounts’ where I have the monthly payments of customers. Every customer has a row for each payment he has made. I want to aggregate their payments yearly starting from the month they paid first. For example in the table given below, for userID 132, I want to aggregate his payments …
Tag: sql-server
SQL – combine rows conditionally on the value of previous row
My issue I believe is best explained by using an example: Sample Data SO Customer Product Description Quantity Extension Country 01 xxx 123 Product A 01 20.00 USA 02 yyy 456 Product B 02 25.00 China 02 yyy 456 Product B 01 12.50 China 02 yyy 789 Product C 01 50.00 Mexico 03 zzz 789 Product C 01 50.00 Mexico
How can I get the percentage of time per user in sql?
I am making an app where people can select a song and listen to it. Whenever someone plays a song it should display the percentage of the how much youve listened in an overview. I have the following …
Determine time duration based on events without using loops
I have a table with timestamps of 5 different types of events (start, stopped, restart, aborted, and completed). The given table looks like this: Time EventID Event 7:38:20 1 start 7:40:20 2 stopped 7:48:20 3 restart 7:50:20 4 aborted 8:00:20 1 start 8:40:20 5 completed 8:58:20 1 start 9:00:15 4 aborted I wou…
Need a SQL Server query to eliminate the Highlighted Rows ( Returning Routes in Flight)
I have a requirement where I need to eliminate all the rows which have returning flight routes. I have highlighted the routes in the orange color which depicts this behavior. For example, row 1 has one route BKI – MYY and row 4 has MYY – BKI. I need a flag (Boolean 1/0) that checks the entire row …
sql – union tables with same prefix
I have a set of tables with same prefix and same structure. All I need is to “combine” them as one. Basically I retrieve tables with certain pattern from information_schema.tables, then get rid of the last union all. Above method works for 20-30 tables, as the @result won’t exceed the limit …
If a column is not null then convert into a row for multiple columns
I have a table like below where I looking to transpose col4 col5 and col6 into rows but in a specific pattern that I listed below col1 col2 col3 col4 col5 col6 a b c 500 200 w x y 1000 300 z g h 200 600 I want to convert it to the following col1 col2 col3 col4 col5
MS SQL identify duplicate rows based on log time
I have a fairly simple database table that logs every time a tray passes over an RFID reader. What sometimes happens is the data is being sent twice, so I have been asked if I can find out how often this happens. Rather than spending the next few days going through every record in the log table, I presume the…
Variable length substring between two characters
Data looks like this: I want it to look like this: It’s pretty simple to get rid of one or the other. This: Gives me this: Initiative: Credible Sources Initiative: Just in Time Initiative: Database Normalization And this: Gives me this: Having a hard time figuring out how to combine the two. Answer How …
How to get count of particular column value from total number of records and display difference in two different columns in SQL Server
I am trying to get difference between total records and a column (Is_Registered) to get Month wise matrics of how many registered in particular month and how many are pending Actual Data Expected Output -As shown in actual data, out of 8 users(records) 2 are registered in Jan and 6 are not ,in February total …