I have a table that contains many IDs as random numbers and I would like to create another table that contains all the IDs with another column having dates from first day of the year until a specific date. For example: My actual table looks like: And I would like to have a table if my specific end date is
Tag: tsql
Updating current database with values from restored database and matching IDs
I have a table for my game called monsterClass. It’s a pretty simple table with these columns: And here’s sample data: etc Well, foolish me was quickly trying to update a certain set of monsters, a mix of monsters with various names, with a new monsterMapYear (7000). I did set the needed set of mo…
Find how many times the department of an employee has changed
I have the following table Employee storing any updates made on an employee: EmployeeId DepartmentId Status From To 44 30 Recruited 01/01/2017 06/03/2017 44 …
Convert simple JSON to SQL SELECT Statement
I have the following JSON: I’m trying to convert to SQL as follows: All i get is an empty resultset (0 rows when it should be 2 rows), what am i doing wrong? Answer Your JSON has square brackets round the whole thing, meaning it is an array (it only has one element) so your OPENJSON starting point will …
Retrieve a row of data with the most recent date when another row ‘X’ in T-SQL
I have a database of customers who have an effective date and end date of their membership, both separate columns. The data is a bit dirty, however, and a customer can have multiple rows of data, only one of which is their most recent membership record. A member is considered “active” if they have…
Filter a condition after aggregate function in SQL Server?
I’ve got database of flights and I want it to return average of delay, but I want it to filter ‘origin = ‘SFO” after counting average of arr_delay_new. If I use WHERE or HAVING it counts the average with condition origin = ‘SFO’. How to make it count avg first and filter la…
SQL All Categorized Possible Combinations
I recently had a rather interesting problem to solve where I needed to construct a list consisting of a combined selection of one choice from each of an arbitrarily defined set of categories, and an aggregated total of a value associated with each choice. The choices within each category are also arbitrarily …
SQL View Select Optional
I have a view that the current output something like this The rules are as follows Each group can only have 2 code and always have a default code vs not a default code. IF default code is not null, then always show the row. Regardless if Non default code has value or not If default code is null, then
How to get TOP larger tables in SQL Server 2000 and their sizes
Is there a way via T-SQL to get the larger tables and their respective size in a SQL Server 2000 engine? Answer I think this script works with SQL Server 2000 even if sp_msforeachtable was not documented
add not null column take values from other column
I have an existing table say, Items with Id | CreateDate (as DateTime) columns; want to add Year int not null, take the default year from the CreateDate column. The Year value will be futer updated …