I have following tables Table 1 i have rates Table 2 i have Dates Table 3 i have data which show price of rates which are applicable between start and end i want to create table 4 with SQL query in which i have every rate (from table 1) + every date (from table 2) + if that date exist
Tag: sql-server
How do I merge OUTER JOIN and UNION results?
I have two tables, and I want ALL the data from both. If the tables have a matching AssetID, then join them on one row. If not, then on separate rows. A full outer join sounds like the right approach but I have a problem in how to select the keys depending on which table it comes from. Goal: Produces
SQL switch latitude and longitude positions in the polygon string
I have a text that contains polygon information Example data: it there any way that could swap “lat” and “lng” in this string in SQL Server? Expect outcome: Any help appreciate! Answer What you have here is JSON. You can break out the info using OPENJSON, swap it round, and rebuild it …
How to count rows by date for each of two values?
I have a large dataset in SQL Server that contains two date fields and a foreign key field (among others): I need a query that can count the number of rows for each day, but to count them for each distinct value in the type_id column. The closest I can wrap my brain around right now is returning the total
T-SQL stored procedure: update local variable inside IF/ELSE statement
I have an item table that has a foreign key constraint customer_id that references a customer table. The customer_id column is of type uniqueidentifier which needs to be generated on insert (there is no value we could use for this provided by the client). I am created a T-SQL (using SQL Server Express) stored…
SQL, casting a string to date so I can use GETDATE()
I am using SQL Server Management Studio 18 against SQL Server 2016. I have some material that are in batches and those batches have expiration dates that are held as strings, but are basically in the format of ‘yearmonthday’, e.g. ‘20210312’ for March 3rd, 2021. My goal is to only see …
query to get data between date ranges and the padded data around
suppose this is my data in a table in the database and this is my query to fetch data I would also like the query to return one date before and after this range. so like the dates padded. Now how can i do this efficiently. One way could be to get the dates in the ranges and then get
SQL Server: Counts and Percentage by Group
I’ve tried the suggestions I’ve found on SO and cannot get the results I’m looking for which is this: I am able to get the first two columns with this code: — What I’ve tried: Trying to use a calcuation in a subquery to represent the total number of rows in the table is beyond me…
SQL Row Data to Column Data in Join
Trying to wrap my tiny brain around how to write this query and I am at a loss: Current: How I would like it to look: If someone can point me to a function that could help me with this, it would be much appreciated. I already tried using: max(case when ….) and pivot() which I found in other articles,
How can I insert data in SQL server to two different tables while having a ORDER# primary key in SQL
I want to insert data on to two different tables in SQL server from VB.net. I have Identity increment set up in SQL that is where the order# comes from. The rest of the data comes from vb.net. Another issue is that every time I capture an order, everything from the Order table copies to the Order Details tabl…