I have a table with the following structure: Unfortunately, the sale_date is stored as INT and I had to find a way to convert it in quarter (202001 will be Q1). The conversion worked well, but I need to include also some calculated columns based on this conversion. Basically, I need to calculate for Q1 the to…
Tag: sql-server
How to extract Year together with Month from this query
I have a query to show total orders by month based on a date column. May I know how to append year to the Month column? Answer I would just aggregate directly by the year and month, as a single text field: If you really want the year and three letter abbreviation for the month, you may try:
SQL how to get the max date when already using max
I want to get the maximum f.post date, and then the maximum a.process date. When I try to use MAX(a.processdate), it won’t work because of the group by and the other MAX already used. This is my result so far: I want it to just return this: Note: a.processdate is an INT (I did not create the database th…
Remove items 30 minutes after their duration
In a table in my database, I have a date and time column (datetime) that stores the time when the event will occur. How can I delete that event 30 minutes after it happened?
Sum data based on columns
Table 1 has several columns with distinct values. What I want is this information combined and condensed to the different column levels, so that the sale and value amounts are sum’d based on the product and type in the year and months instead of each distinct line being displayed as a separate value. For exam…
Create table from a table in SQL Server
Below is the query I tried to execute in SQL Server: I get this error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘(‘ I did some research on google to understand and isolate the error, but, failed to do so. Could anyone please help me understand the error. Thank you Answer SQL-Server…
SQL Server Concatenate three different columns into a Comma-Separated without repeated values
The next table is a simplification of my problem in SQL Server: I want to get a group with the columns concatenated by comma without repeated values. I tried to use STRING_AGG() but it returns: This is the query I have done: I would like the next result: Thank you! Answer Without using window functions. The u…
Find date range for every row. LEAD/LAG but with different types/actions
I have a table with following data: There are two actions possible. ‘Remove’ and ‘Install’. How can I find Place for Code A for given date? For example for 2015 it will be Place 1. For 2020 it will be Place 2. For 2025 it will be Place 3. I think the first and the most difficult step i…
MSSQL Check if a SELECT statement will have rows, if yes, then execute the same SELECT statement
I would like to execute a SELECT statement in a stored procedure, but before executing, I have to know if it will have rows. If not, I have to jump to a specified Label, and set an output. My current method works just fine, but I will need more of these in several procedures, and I was wondering if I
Query using group by with steps/range over large data
I have a table that stores a sensor temperature readings every few seconds Sample data looks like this I want to have the average temperature per device so I ran the following query Where I can pass the time range I want. My issue is that this gives me total average but I want steps or range I want to