I have two tables. One is the Transaction table and the other is the PriceBook table. I want to select the price from the PriceBook table, matching with the ItemID, Assortment and UnitID with Transaction table. But the UnitID match is optional, so if there is no match with UnitID, I want to have the matching …
Tag: sql-server
Update column by removing text in braces
I need to update single column for all the rows in a table. The update should happen in such way that data needs be filtered out by removing all text where braces are appended for the string and swap the strings separated by ‘,’. Example: As per the above example, I need the final data to be somet…
How to query the number of records when there is the DISTINCT keyword in the SQL multi-table join query string?
— table1 has 1 record — table2 has 4 record — t_test01.value = t_test02.value, 3 record — DISTINCT t_test01.value = t_test02.value, 1 record How to query the number of records when there is the DISTINCT keyword in the SQL multi-table join query string? Answer A quick-and-dirty approach…
Getting the correct data to be stored into temp table by comparing timerange
Currently, I need to get the correct data to be inputted into temp table. There are 3 datetimes that I need to compare. However not all values are populated. Example-Assume that all dates are the same Data1 DateTime1 DateTime2 DateTime3 First 2020-08-24 14:00:00.000 2020-08-24 14:30:00.000 2020-08-24 15:30:00…
How to get in json a column that combine from two field in sql?
I have table with those columns in mssql: How to combine name and surname into info when I get json from the sql: What I get is: And what I need is: This is possible to do with mssql? if so, how can I do it in sql? Answer You need to use FOR JSON PATH to build the nested
Syntax Error when trying to use Dynamic Pivot Query
I’ve been playing around with the PIVOT function for a while. I have a table that looks like this Every ClientID is repeated once for each month. Every month new rows are added to the table with a new period associated to the row. It’s an incrementing table. What I need to do is to PIVOT the table…
Sql where Condition execution not working
I have the following query logic Show comments which is not deleted Show comments which is deleted after created, within a timespan of 30 days before today(Today – Createddate <= 30) Below is the logic which I wrote, but I don’t think it is working correct. Answer
How to sum the amount and merge the offers in a single row?
I have table with discount transactions data as below: TransactionID DiscountAmount DiscountOffer S011-T012 50 Jun-21 S011-T012 25 ManagerDisc S011-T025 15 Jul-21 I need to create a table in a way that for a single transaction, Discount amount is summed up and Discount offer is shown side-by-side (maybe with …
How add select in list without join
I don’t need to join in a whole table, but just want a value from the table returned in my select list (but other tables are joined for other items). It’s giving me an error…incorrect syntax near select I’m having trouble searching online for this. Answer Assuming the scalar subquery r…
how many banks are currently rated B+ or above and when was the last time (dateindex) that they had been below
I have these two tables and im trying to get the dateindex of the last time that the company was rated below a B+. dateindex=19941 which means 1994 quarter 1 This selects all the companies that have B+ or above in q2 2020 And it yields the following How can I add the dateindex the last time it was below