Skip to content
Advertisement

Tag: tsql

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 db<>fiddle Or if you

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 other mumber). If Refund=1 the PaidMonths

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 one vs the

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 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 little more concise. Returns:

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 those Ids (In this example, it should display only reservation 14105, because

Advertisement