My current code is duplicating results per item_id. I am summing the previous three months usage per item in two locations. I do have more locations but I limited it in the WHERE clause. I think the …
Tag: sql-server
Compute a Decreasing Cumulative Sum in SQL Server
What I’m trying to achieve is a cumulative sum on a non-negative column where it decreases by 1 on every row, however the result must also be non-negative. For example, for the following table, summing over the “VALUE” column ordered by the “ID” column: I expect: Answer Your ques…
Import JSON File into SQL Server Table with nested Arrays
I’m trying to import the Census Block GeoJSON file and unable to get “Coordinates” for each block along with it’s properties. I’m trying to get the ID, BlockGrp, Block.. and it’s associated coordinates. Below is my code, but I’m unable to get the coordinates since it&…
Terse syntax to return an empty result set from SQL server
Is there a shorter way to write the following? The “original” question follows. This was was modified may times, explicitly in hopes of stopping Y responses as a result of showing a specific use-case; and [rightly] claimed to be a confusing mess. The use-case is a TSQL query that returns an empty …
Put value on table inside variable?
I need to put a filename that is already listed into a table. I need to put the filename to address to access the file. I already tried this: I expect @filepath will contain something like this D:romliLockAmount_out123456.txt, how I can store the filename in a table to store in a variable? Answer Seeing that …
Could not able to retrieve distinct rows from a Column based on the condition of another column in SQL server
I am new to SQL and I have a big table my_table having thousands of rows. The following is a sample: What I want in the output is following: Basically, I want the unique Ids which follow PricingRule as Rule1 from the JsonObject column. I tried the following code: Answer Try the following, here is the demo. Ou…
How to implement the like and share feature for a social networking website? [closed]
Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 2 years ago. Improve this question I want to understand how the like button is actually implemented in a Web …
switch case from subquery sql
Assume that I have 6 tables: Users, Customers, Providers (each user must be or customer or provider), Requests, Offers, Booking. Each customer can publish new request, and each provider can make an offer for any request, if the customer want he can make booking for one of the offer (each request may have at m…
Calculate overlap time in seconds for groups in SQL
I have a bunch of timestamps grouped by ID and type in the sample data shown below. I would like to find overlapped time between start_time and end_time columns in seconds for each group of ID and between each lead and follower combinations. I would like to show the overlap time only for the first record of e…
Top-N By Decade for successive decades (in SQL Server)
I’m trying to get a ranked list of Top 5 (i.e. most common) document titles, grouped by decade, for each of the 6 most recent decades. The document titles are non-unique. There could be dozens or even hundreds of documents with the same title in any given calendar year. The following query is as far as …