I need to create a table of every second between two input dates eg between 01/01/2015 00:00:01 and 05/01/2016 00:00:00 So a 3m row expected output that looks like this: Does this need a CLR? …
Tag: tsql
How to use case to construct a conditional update
I have to write an update query. If the special_member account is not cancelled then in the where clause I have to use this condition by adding a grace period of 15 days to the expiry date and compare …
Return all rows from one table, and match rows from another table
I have the following two tables: ExchangeRate (T1): CurrencyCode Rate CHF 6.9660 EUR 7.4445 GBP 8.2569 NOK 0.6686 Customer (T2): CustomerNo Name …
After Insert trigger to modify particular value
I have some problems with my trigger. On my transactions table I can’t allow any rows to be inserted which are cancelled. Any transaction can be Scheduled, Done or Cancelled. If you insert a cancelled …
Performance issue using IsNull function in the Select statement
I have a financial application. I have ViewHistoricInstrumentValue which has rows like this My views are complicated but the db itself is small (4000 transactions). ViewHistoricInstrumentValue was executed in less than 1 second before I added the next CTE to the view. After that it takes 26s. ActualEvaluationPrice is the price for instrumentX at dateY. If this value is missing
Find repeating phone numbers between a 7 day range
I have a phone and a call date field. I need to find all phone and call dates where calls were made more than once (>1) within a 7 day period. What is the best approach to this? Example: output: Here’s an example query I tried but I assume I can do better (besides, its taking a very long time,
Many conditions depends on IF clause within WHERE clause
How can I implement a WHERE clause that depends on one @value condition, like that pseudocode below: Answer As you’ve seen you can’t use an if like that, but you can create the desired behavior using the and and or logical operators:
Get Latest ModifiedDate Joining Multiple Tables
I have 4 tables ProjectTable projectid: INT (pk) modifiedDate: DateTime2 … ProjectFinalizeTable id: INT (pk) projectid: INT (fk) modifiedDate: DateTime2 … ProjectAdditionalTimeTable id: INT (…
Full Join using a fuzzy join but with distinct matching
We have a table of expected payments and a table of payments made. We need to be able to match payments with a singular expected payment but we allow a +-3 day window for it to be made. On top of that, it should be a one-to-one match. So imagine I have a table of expected payments and payments The
How can I pull out the second highest product usage from a SQL Server table?
We have a product usage table for software. It has 4 fields, [product name], [usage month], [users] and [Country]. We must report the data by Country and Product Name for licensing purposes. Our rule …