Will this script check the existing values in a column or am i missing something?
if EXISTS(select 1
FROM inserted
INNER JOIN TESTTABLE M ON
inserted.ID= M.ID
WHERE inserted.RECORD_Id >= inserted.RECORD_Id)
Everything seems hundreds, i now just need it to check the the existing values in the table
I'm trying to checking my production table table_a against a working table table_b (which i'm downlading data to)Here are the collumns i have in table_a and table_bDescription | FundID (this is not my PK) | Money I'm running an update if there is already vaule in the money collumn. I check to see if table_a matches table_b...if not i update table a with table b's value where FundID match up.What i'm having trouble on is if there is no record in table_a but there is a record in table_b. How can I insert that record into table_a? I would like to do all of this (the update and insert statement in one stored proc. if possible. )If anyone has this answer please let me know.Thanks,RB
View 3 Replies View RelatedWe have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
Hi All,
Is it possible in SQL to Restrict value in one table checking a value on anather tables.
Scenerio-1.
I have two table let say,
Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value.
Scenerio -2.[/B]
I have table called [B]SIC.
This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns.
Please Advise.
Vijay
Hi there!I have a problem I can't get rid of... I hope anyone can help me with it!For a room-booking application I need to select available rooms for a givenperiod. First I do the availability check which is no problem, than I needto find out if there is a tariff available for every night of the givenperiod. If not, I will not show the room as 'available' in the systembecause I can't make a tariff calculation.All tariffs are entered in a table which can hold different night-tariffsfor different periods. The table looks like this:========CREATE TABLE [dbo].[TARIEVEN] ([TARIEF_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[FK_OBJECT_ID] [numeric](18, 0) NOT NULL ,[STARTDATUM] [datetime] NULL ,[EINDDATUM] [datetime] NULL ,[TARIEF_PRIJS] [decimal](10, 2) NULL ,) ON [PRIMARY]GOFK_OBJECT_ID is the Room IDSTARTDATUM is the startdate of a tariff periodEINDDATUM is the enddate of a tariff periodTARIEF_PRIJS is the tariff per night in the tariff period========What I do at the moment is:--------SELECT fk_object_id FROM TARIEVENwhere ('15-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)AND ('16-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)AND ('17-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)--------This will work if all nights are in the same tariff period, but it will notproduce the right results if the tariffs are in different periods because nosingle row contains a startdate and enddate in between which all dates are.I can't use an OR statement because I absolutely need a tariff per night.So, what my question is in short:How can I for example select OBJECT 1 from the following table consideringthat I want to return all rooms which have a tariff available for a periodfrom 15th of october to 17th of october?-----------------FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJSFK_OBJECT_ID STARTDATE ENDDATE TARIFF1 10-oct-2004 16-oct-20041001 17-oct-2004 18-oct-2004125In words: object 1 costs 100 per night in the period from 10 to 16 octoberand 125 in the period from 17 to 18 october------------------I hope anyone can help me. Thanks a lot in advance!!Robert
View 8 Replies View RelatedI have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.Â
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.Â
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values-------------------------------------------PoId Podate Approved
2 2008-07-07 No 4 2007-05-05 No 5 2005-08-06 Yes 6 2006-07-07 Yes
Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
Pls provide me with full stored procedure code.
It is very urgent.
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?
Hi,I have a table which I want to alter to add the data from anothertable.Now both these tables have a few columns which are same. Now,I want the system to be such that only the unique columns can beadded to the first table.How can I accomplish this in SQL?
View 1 Replies View Related
Hi,
I have a table as follows
Table
{
Category1,
Category2,
Category3
}
I wanted to write a query to check whether the column with the name 'Category3' exists in the table.
Can anyone please let me know how to do this
Best Regards and Thanks
~Mohan Babu
Is there a command I can use to tell me all the tables in my schema that have a column name "Id"?
View 5 Replies View Relatedhi
i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail.
if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks
Ihave 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up).
image 1 is what is currently set up
imgur: the simple image sharer
top part is what is stored in tables..bottom is more of the result
it basically runs this code to get the bottom
DECLARE @id INT;
DECLARE @invest nvarchar(50);
SET @id = '7633';
SET @invest = '';
SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat
FROM orderheader, orderaudits, orderfindings
WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%'
GROUP BY orderfindings.risk_rating
If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it.
image 2 - the table at the bottom is more what i'm looking for..it combines them both into 1
imgur: the simple image sharer
if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..
I've been working with it...did this [SQL] compare2 - Pastebin.com ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
Bitmask fields! I am capturing row changes manually via a high frequency ETL task. Â It works effectively however i am capturing the movement of multiple fields. Â A simple example, for Order lines, i have a price, a discount and a date. Â I am capturing a 001, 010, 100 respectively for each change. Â
I would like my users to be able to select from a dimension which has the 3 members in it and they can select one, multiples, or all values (i.e. only want to see rows that have had the date and price changed).Â
Obviously if i only had 3 columns i would use bit's and be done with it, i have many different values (currently around 24 and growing).
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedI have a table with 2 columns and my source data looks like this..
PolicyNum  InsCode   Â
1ABC12Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum  InsCode1  InsCode2   Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â 1006Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC20Â Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005Â Â Â
          InsCode value 1002 should always mapped to 1006
           InsCode value 1003 should always mapped to 1004Â
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')Â Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')Â Â Â Â Â Â
[Code] ....
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies View Related
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!
View 7 Replies View RelatedGood afternoon,
I am trying to figure out a way to use a columns default value when using a stored procedure to insert a new row into a table. I know you are thinking "that is what the default value is for", but bare with me on this.
Take the following table and subsequent stored procedure. In the table below, I have four columns, one of which is NOT NULL and has a default value set for that column.
CREATE TABLE [dbo].[TestTable](
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[SSN] [nvarchar](15) NULL,
[IsGeek] [bit] NOT NULL CONSTRAINT [DF_TestTable_IsGeek] DEFAULT ((1))
) ON [PRIMARY]
I then created the following stored procedure:
CREATE PROCEDURE TestTable_Insert
@FirstName nvarchar(50),
@LastName nvarchar(50),
@SSN nvarchar(15),
@geek bit = NULL
AS
BEGIN
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, @geek)
END
GO
and executed it as follows (without passing the @geek parameter value)
EXEC TestTable_Insert 'scott', 'klein', '555-55-5555'
The error I got back (and somewhat expected) is the following:
Cannot insert the value NULL into column 'IsGeek', table 'ScottTest.dbo.TestTable'; column does not allow nulls. INSERT fails.
What I would like to happen is for the table to use the columns default value and not the NULL value if I don't pass a parameter for @geek. OR, it would be really cool to be able to do something like this:
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, ISNULL(@geek, DEFAULT))
Does this make sense?
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)
PartsNum
MiniPartsCL
NewMiniPartsCL
1
K
DK
1
K
K
1
Q
Q
0
L
L
0
L
LC
0
D
G
0
S
S
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.
The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,
Beli
Hi,
I have got a table where i want to display sum of count(Column1), count(Column2) in another column.How can this be done?
for example
SELECT SUM(Count(pxInsName)+Count(pxFlowName)) AS "pySummaryCount(1)" , Count(pxInsName) AS "pySummaryCount(2)" , Count(pxFlowName) AS "pySummaryCount(3)" , pxAssignedOrg AS "pxAssignedOrg" , pxAssignedOrgDiv AS "pxAssignedOrgDiv" , pxAssignedOrgUnit AS "pxAssignedOrgUnit" FROM pc_assign_worklist WHERE pxObjClass = ? GROUP BY pxAssignedOrg , pxAssignedOrgDiv , pxAssignedOrgUnit ORDER BY 'pySummaryCount(1)' DESC
But sum function can not be used on aggregate function.
Is there any other way.
Hi Folks, Im new to SQL, and I am trying to do the following:
I have a table Documents with DocID, Path and FileName.
A second table Keywords has KwdID, KeywordString
A third table DocumentKeywords links the two with DocID,KwdID. Multiple keywords are linked to one document.
I want to create a SELECT query that makes a result table that contains Path, FileName and Keywords columns where the Keywords column contains entries like "Keyword1,Keyword2,Keyword3" ie. a comma delimited list of keyword strings which have been built from the keywords that associate with a specific document.
I found a nice sample here
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
which shows how to return just the comma delimited string itself:
DECLARE @List varchar(100)
SELECT @List = COALESCE(@List + ', ', '') + Keywords.KeywordString
FROM DocumentKeywords
WHERE KwdID = 1
SELECT @List
I cannot seem to integrate this into the query so that it calculates the string for each row on the fly. My suspicion is that the capability is there. Can somebody point me in the right direction?
Thanks
Say my dataset has a column with numbers 1-15. Those numbers are attached to other information.
CUSTID Name
1 Jim
2 Pat
3 Mary
4 Sally
5 Jane
How can I pull only even customer IDs?
Will something like this work?
SELECT * FROM TABLENAME
WHERE CustomerID/2 = 'Even/Int'
Hi,
I want to have the sum of the values of a column .. however the range is not definate.. a.. i.e we need to check till the last rec.. and need to get the sum
pls help
Hi,
I have a status column in Excel file which is getting data from checkboxes.
When I exported my file to Excel this status column is containing 2 or more values.
The possible values for status are:
1 active
2 Pending
3 Inactive
So for each case it might be possible to get values like
case 1: status: Active Pending
Case 2 : Status: Inactive pending
So in my Excel file is containing these values in one status column.
I am thinking of storing statuses in a separate table like this:
Case Active Pending inactive
1 1 1 0
2 0 1 1
In the SSIS transformation how to achive this? how to split the status columns values and where to store?
Thanks
We have a column that contains values only between 0 and 1; most of them are 0 or nearly 0. We perform a basic clustering on that single column with the data mining add-in for excel. The results show three clusters: two of them centered in values between 0 and 1 (0 and 0,47), the other one looks strange and has a very high negative value which is not present in our data. I haven€™t tried running the same clustering directly on Visual Studio. This hapend with other algortim. Do you know the problem We have screen dump if it needs Best regards Capgemini - Oslo
View 4 Replies View RelatedI have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies View Related