First I need to create a sproc that will give me just a list of years from this year back to 5 years before this year. I was thinking of somehow using a table variable? After that I need to use that value as a parameter in another sproc. How would I then say show all Dates that contain the year that is picked? I know how to write the pa...
i have a table called table1 and it has following columns. suppose there are records like localamount is 20,000, 30000,50000, 100000 then as per my condition i have to delete records from this table according to the group by set of site id, till id, transid,shift id where localamount exceeds 10,000... the rest of the records can be avai...
I have a column created as `date_start` datetime NOT NULL DEFAULT '1970-01-01' However when I upload data from a CSV file with the LOAD DATA command with a blank entry for date_start the value saved is 0000-00-00 00:00:00 ? ...
I have the following UDF. CREATE FUNCTION [dbo].[udf_GenerateVarcharTableFromStringList] (@list varchar(MAX), @delimiter char(1) = N',') RETURNS @tbl TABLE ([Value] varchar(200)) WITH SCHEMABINDING AS BEGIN DECLARE @chrind INT DECLARE @Piece nvarchar(4000) SELECT @chrind...
I have a number of read only databases alter database [ReferenceData] set READ_ONLY that is used by our application and was wondering what type of things I can tweak for maximum performance? So far, I came up with the following goals: Force padding in indexes to 100% fill factor index fragementation be less than 10% If space isn't a p...
i have a column with dates, but it is a varchar: 8/31/2010 9:48 8/31/2010 9:49 8/31/2010 9:51 8/31/2010 9:52 8/31/2010 9:55 8/31/2010 9:59 8/31/2010 10:11 8/31/2010 10:13 8/31/2010 10:16 8/31/2010 10:37 8/31/2010 10:42 i made sure that none of these will be a BAD date: SELECT * FROM qcvalues.dbo.batchinfo WHERE ISDATE(reporttime) <> ...
I have a query select * from table where name in ('52 T&M', '60 T&M'); The "&" is causing the query to expect a parameter. How do I qualify the "&" in the query to sting so that the query can find string with the "&" character in them? ...
Say I have these three tables: Table: Baskets id | name 1 Sale 2 Premium 3 Standard 4 Expired Table: Fruit id | name | basketid 1 Apples 1 2 Oranges 2 3 Grapes 3 4 Apples 2 5 Apples 4 Table: Veggies id | name | basketid 1 Carrots 1 2 Peas 2 3 ...
We are capturing attendance data in a certain table primarily capturing the values (userId, startDate, endDate). If a person applies leave on a certain date then only startDate is filled and if he applies for a set of dates then the start/end dates are captured. What query will help me find if a person is taking leave on a certain date (...
Hello I am using EntityFramework 4 with POCO classes, but I like to divide the database up into separate schemas. While I can do this designing the database first and then generating the model and everything works fine, if I update the model and select to generate database from model it ignores all my schemas and generates all tables un...
I have data in following format Table1 e_id e_name e_type ------------------------- 1 CBC 2 2 ABC 3 3 N2 1 4 CBC1 3 5 ABC1 3 6 N1 1 table2 N_ID N_Name --------------- 3 N2 6 N1 Table3 N_ID E_ID ------------ 3 1 3 2 3 3 6 4 6 5 6 6 And...
Hi, Is this the most efficient way to delete from DeletedProducts table where there are not references found in the ProductFileInfo table? Sample: DELETE FROM DeletedProducts WHERE ProductId NOT IN SELECT DISTINCT ProductID FROM ProductFileInfo Or is NOT EXIST a better way to perform this. Note: ProductFileInfo has over 20 Million...
Hi, I have a software that LIMITs queries based on user input. I'm aware of SQL injections and DOS possibilities here so I'm not interested in hearing about those. What I wonder is that how high limit should I put for common requests like getting the latest X items? Currently I allow the client to specify the value, and I make sure the ...
I got this function from someone on here: create FUNCTION [dbo].[fnSplitString] (@s varchar(512),@sep char(1)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop...
I was asked to create a desktop tool to compare an older access DB with a newer "updated" access DB and create an excel file of all new or altered records. the DB tables I need to compare are structured Primary key | Description. I created a simple Windows form that allows the user to select the old and new databases and I figured ou...
Hello, I Have a table with the following columns: patient_id, visit_id, and visit_date. How can I write a query to get the max(visit_id) on the most recent visit date for each patient? (several visit_id could occur on the same date for the same patient) basically, I want to end up with NO duplicate patient ID's. Thanks. ...
In SSRS I have a parameter that is populated from a multi-value list which allows a blank value. When I select just the blank value it returns unit 123 When I select just "New" it returns unit 987 When I select both owner "New" and the blank value it only returns 987. Any ideas? Does SSRS not allow you to pass the blank value and a n...
Hello all, I have a very large db that I am working with, and I need to know how to select a large set of id's which doesn't have any real pattern to them. This is segment of code I have so far: longIdList = [1, 3, 5 ,8 ....................................] for id in longIdList sql = "select * from Table where id = %s" %id resu...
Hello, I have few inline sql statements with some arrays and if loops. I want to change them all into one stored procedure in SQL server 2005. As i am learning SQL, i have got no idea about how it should be done with arrays and if loops. Is that possible to send arrays into stored procs or is there any other solution. Refer the code wh...
I have a table with data as following: id col1 col2 1 c1 1 c2 2 b1 3 a3 4 e1 4 e2 Now I need to move this data in another table in such a way that the record that has same id goes as a single record in this new table. so the new table data would look like this: id col1 col2 1 c1 ...