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…
Tag: tsql
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…
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…
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
Generate Scripts to move a database from one server to another
I want to move a database from our testing server to my local server. I don’t have the option of back up and restore since I don’t have permission. So I decided to use the generate script option to create all the scripts for the DB and then move over the database to my local server. The problem I&…
SQL query GROUP BY groups
I have something like this: id name totalAmount 1 name1 10 2 name1 20 3 name1 25 4 name2 5 5 name2 12 And need to looks like this: id’s name totalAmount 1,2 name1 30 2,3 name1 45 1,3 name1 35 1,2,3 name1 55 4,5 name2 17 I’m using the STRING_AGG but don’t know how to separated in the first
Raw SELECT (without FROM) of most recent 7 days to current
I want to get the query result (e.g. to populate table) of last 7 dates (without times). I know that we can select some scalars without FROM statement. So I ended up with following solution: Please point me to better (and more elegant) solution if there is one. Answer The VALUES, table value constructor, is a…
FOR XML PATH produces only first item
I have two tables: Reservations and ReservationNights (every reservation has many nights). In a stored procedure, I have a variable that looks like this: @roomTypeList = ‘2;3;4;5;’ -> its a list of RoomUseIds. I need to display only the reservations that have a reservation nights with one of th…
How to select unique records from a 5 column table where the values can be the same but in different orders
I used 1 table to do a cross join 4 times giving me 5 columns of data. I used the same column in all 4 cross joins to get every combination. The only problem is I get the same data it is in a different order. I am trying to only get records that are unique. I can only have