I have a table that where each record has start and end dates. I need to return a record for each day between start and end date fields (including the start and end dates). Using MS SQL Server. Example: Current data Data required: Looking for recommendations. Thanks. Answer You can use recursive cte :
Tag: sql-server
Trigger for INSERT to validate input data in SQL Server?
My problem: table dbo.student has StudentID like SV001. How can I create a trigger to check data inserted into dbo.student has a StudentID that begins with SV and the numbers in the range 000 to 100? Example: SV099 is valid id to insert, while SV101 is not valid Answer Use SQL constraints: Example : Demo in d…
SQL Count depending on certain conditions
I have two tables. One have userid and email (users table). The other have payments information (payments table) from the userid in users. I want to count the PaidMonths taking in consideration the following rules: If ValuePaid < 10 PaidMonths should be = 0.23 (even if in the column the value seen is any o…
SSIS Script Task throws Object reference not set to an instance of an object
I am trying to query the database and send the content in the body of the email. When I try to run the package it throws deadlock error. Can anyone please suggest what is that I am missing Script is like below Below is the And the package looks like below.. For every user I need to get all the
Having and Where in combination with Group By
I have learned to use HAVING when I use GROUP BY instead of the WHERE clause and never encountered any problems with it. Today I saw this on a w3schools.com a SQL Learning Page: Why this should work? When I should use this? Answer The difference between WHERE and HAVING is central to when you should employ on…
Generate all combinations of values with set list of values for each character in SQL
I have a dataset that looks like this Position Value 1 1 1 2 1 3 2 8 3 5 3 6 And I’d like to generate all combinations of strings with the value at each position. For this example, the output would look like Output 185 285 385 186 286 386 The order doesn’t particularly matter. Note: There can
How to display multiple rows based row difference data In SQL?
I have a table as follows: I want to split this data based on above data. If FromYm(Means yearmonth) and ToYM(Means yearmonth) difference is two then result as two rows: Example result : Tried Code Answer You can do it using recursive cte: You can test on this db<>fiddle
SQL – select based on value existence in another column
I want to create a SQL query to identify wells which do not have A3 events. I am using SQL server. I have tried multiple ways like checking count, comparing with event A3 but still unable to get what I want. From below example desired result would be W3 and W4 Site Event W1 A1 W1 A2 W1 A3 W2
SQL Merge Into with multiple statements
I’m trying to keep a historic table of another one. When updating the original I would like to insert rows into the historic one. I’m using Sql Merge: Can I make multiple statements in the same “when” condition?, as such: Answer Normally you can INSERT only into one table. The syntax d…
How to calculate sum of a value with only “day of week” using a date-range?
I have 2 tables namely Item table with details of item_id, store_id, offer_start_Date and offer_end_date Store table has store_id, day_of_week, store_hours The structure of both tables are follows – 1) Item_Table : Store ID Item ID offer_start_Date offer_end_date NY0001 FMC0001 2021-10-30 2021-11-04 NY0…