Hi Given that I have a table with 2 columns. Table Booking Column Amount-TransactionDate Get me total Amount between Last 2 transactionDate. How do you do that ?How do you get the last transaction but 01 Any suggestions? ...
SELECT u.Id FROM Users u WHERE FREETEXT((FirstName,Lastname,MiddleName),'') UNION SELECT c.AId FROM Certification c WHERE FREETEXT(*,'') UNION SELECT ad.AId FROM ApplicantDetails ad WHERE FREETEXT(*,'') UNION SELECT eb.AId FROM EducationalBackground eb WHERE FREETEXT(*,'') UNION SELECT ed.AId FROM EmploymentDetails ed WHERE F...
this stored procedure does not return salary with decimal format 00.00 ALTER PROCEDURE taxable_varsalary @emp_code bigint, @co_id bigint AS declare @t_varsalary decimal(8,2) set @t_varsalary = (select sum(tran_value) from emp_ded_ben_trans where emp_code=@emp_code and co_id=@co_id and period_flg=2 and tax_flg=0) RETURN @t_var...
I have at my test database datatable Tags. ID int autoincrement Name nvarchar(255) I want to transfer this table to my production database. How to do it if I want to have the same ids foreach element The problem is that my start index at testdatable is 15, and some ids don't exist because I removed theirs. So: Situation at Test da...
I have a fairly conventional set of order entry tables divided by: Orders OrdersRows OrdersRowsOptions The record in OrderRowOptions is not created unless needed. When I create a set of joins like select * from orders o inner join OrdersRows r on r.idOrder = o.idOrder inner join ordersrowsoptions ro on ro.idOrderRow = r.idOrder...
Dear Colleagues, I want to alter a view as follows: ALTER VIEW [dbo].[ViewOne] as SELECT columnOne, -- not null columnTwo, --not null (convert(decimal(2,0), columnOne)) as columnThree -- I want this not to be NULL FROM DBOne.TableOne Since columnOne is "not null" I want to force columnThree to be "not null" also. Is...
Hello, I have a Microsoft SQL database, where i am trying to insert some data. I have a unique key on 4 columns and i want to insert data from multiple tables into this table while checking the data to make sure it will not violate the uniqueness of the key. If i was doing this on a single column, i would do a NOT IN, like so.. INSER...
I try to create standardized SQL scripts for use in my SSRS reports. If I wish to have a statement such as: Select * from mytable and use a SQL Variable (SSRS parameter) in the where clause, is there any speed advantage to using this: Where field = @MyField VS. Where field IN (@MyField) I know the second option supports both mu...
Hello, I had always used something similar to the following to achieve it: INSERT INTO TheTable SELECT @primaryKey, @value1, @value2 WHERE NOT EXISTS (SELECT NULL FROM TheTable WHERE PrimaryKey = @primaryKey) ...but once under load, a primary key violation occurred. This is the onl...
Hi I am having a problem when trying to update a table using an IN clause, I have a big list of clients that should be updated 4500+. Update table set columnA = 'value' where ID in ( biglistofids ) //biglistofids > 4500 ids I am getting this error "String or binary data would be truncated." I tried the same script with fewer ids le...
Hi All, I have two separate tables TVs and Receivers that I am using the FOR XML PATH commands to build XML off of. My issue is that I want to combine the output of my TV XML Build with my Receiver XML Build to create one XML output. So I would have something like this(Which allows me to keep the TVs and Receivers Tags Separate within...
Is it possible to construct a dynamic query to for a linked server (and if so how)? For example: @linkedServer varchar(50) @var1 varchar(10) @var2 varchar(10) select * from openquery(@linkedServer, 'select c1,c2 from t1 where p1 = @var1 and p2= @var2...
Hi Based on the 2 databases below: Database_A on Server_1 Database_B on Server_2 I have created a linked server to Database_B on Server_1 instance by name 'LS_B'. I have a huge script file which basically creates required tables, functions, views, and stored procs on Database_A. These functions, views and stored procs in turn ref...
I want to make a composite key (well that's the idea but I'm open to other suggestions) on a Documents table. It would consist of two columns, year (2010,...) and an ID, which would be autoincrementing but it should restart itself every year. So keys like these 2010-1, 2010-2, ..., 2011-1, 2011-2, ... and, preferrably those keys should ...
A slightly tricky SQL question (we are running SQL server 2000). I have the following table, StoreCount - WeekEndDate StoreCount 2010-07-25 359 2010-07-18 359 2010-07-11 358 2010-07-04 358 2010-06-27 358 2010-06-20 358 2010-06-13 358 2010-06-06 359 2010-05-30 360 2010-05-23 360 2010-05-16 ...
Hi I am writing a long program in TSQL that pulls in data from an OLD (and very dirty data set) scrubs the data and reformats the output including column headers to match a new data set There are 130 columns in both the new and old tables. For the purpose of testing I am bringing in 100k rows from each. To validate that the table struct...
I need to collapse multiple ranges of sequential numbers (1 or more) to sets of their minimum and maximum values. I have unique integers (no duplicates) stored in a table column. The obvious way (to me) to solve this problem is to use a cursor (see my algorithm below) and iterate through every integer. However, it seems inefficient to ...
I got this error when I supplied null value to @keyword variable.And I found a solution to resolve by setting the @keyword value to '""'. The problem is when the value is set to '""' and when you run the query there is no results displayed, it must display all the records. How can I do this? declare @keyword nvarchar(50) set @keyword='"...
ALTER PROCEDURE dbo.StoredProcedure8 @emp_code bigint, @co_id bigint, @p decimal(8,2) output AS SELECT @p = (select sum(tran_value) from emp_ded_ben_trans where emp_code=@emp_code and co_id=@co_id and period_flg=2 and tax_flg=0) RETURN ...
My data looks like below: <products> <product ProductID="1" Price="79.99" Weight="30.00" Quantity="1"> <addon ProductAddonID="0" ControlTypeID="9" Price="25.00" Weight="0.00" Quantity="1" Name="yyy" Data="ASD" /> <addon ProductAddonID="89" ControlTypeID="0" Price="15.00" Weight="4.00" Quantity="1" Name="xxx" Data...