Suppose I have string like
DECLARE @strname varchar(50) = 'i_ncm_ai04';
DECLARE @SAMPLE_DATA TABLE
(
SD_ID INT NOT NULL
,SD_TEXT VARCHAR(10) NOT NULL
[Code] ......
how can I search a ID's which are exist in my string.
result should be,
3 i_ncm
8 i_ncm_a
Suppose I have string like
@strname varchar= = '3 April 15 abcd Oh rrrrrrrAAAAdd HJHJG'
and table contains two columns having rows like,
ID text
1 abcd ER
2 abcd AS
3 abcd Oh
4 xyz TR
5 azs WS
6 abcd O
7 OP trx
how can I search a ID's which are exist in my string.
result should be,
3 abcd Oh
6 abcd O
Consider the following: I have a table, say ORDERS, with these entries -
CustID
ProductID
1 CAN
2 2
3 1,2
4 4
5 1,2,3,4,5,CAN
6 10
7 CAN
8 1,CAN
I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.
Hi,
I have a table of row IDs, order numbers, and status. Status is a value of 1 or 2. Some rows only exist as a 1, some only as a 2, and some exist twice -- once as 1 and once as 2. I want to select orders that are 2 as well as rows that are 1 but do not also exist in the table as 2. Ultimately I will only see each order number once. Is there a better/faster way than doing multiple selects and comparing them to each other?
Example data:
RowID OrderNum Status
1 101 1
2 102 1
3 102 2
4 103 2
Desired results when selecting ID and ordernum from that table:
1 101
3 102
4 103
Thanks.
I have string as below:
InvoiceTemplateId= SOURCE.InvoiceTemplateId
,Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy
I need a string Like below:
Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy
So I need every thing except the First value before first comma .
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[code].....
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I have a set of data where a column contains titles which have been formatted as follows:
"FirstWordSecondWordThirdWord...." etc.
That is, all the words have been concatenated but can be visually separated by their capital first letters.
For reporting purposes, I need to break this column into the separate words so that it looks like:
"First Word Second Word Third Word...." etc.
how this can be achieved?
I have a table ("MyData") with string columns that have nvarchar data that looks like this:
ColA
--------
42/90
78/109
I plan to do a mathematical grouping on the numerator (eg: 0-10,11-20,21-30, etc... ), So I need to grab the numerator and turn it into an int. For reasons I can't get into, I have to do this in pure T-SQL.
So my question: How would I write a select statement that regex pattern matches "^([0-9]+)/" on ColA and returns integers instead of text?
E.g. THis Query:
SELECT [magic t-sql syntax] as Converted_ColA from MyData
should return this set of int values:
42
78
I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:
UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance
I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same
Movement Multiple CountRecords
UUUDUUUDUUUD1230123012301175
------------0000000000001163
UDUUUDUUUDUU3012301230121082
The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.
Movement Multiple CountRecords
UUDUUUDUUUUU-----------07
UDUUUDUUUUUU------------7
good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.
I have the following string and am trying to select only the characters before the last "</>". How can I do this?
declare @String varchar(500)
set @String = '<p>Assessed By: Michael Jordan Yagnesh</p>
<p>Reviewed By: Fred Smith</p>
<p>Home Address</p>'
select REVERSE(substring(REVERSE(@String),5,charindex(':',REVERSE(@String))-5))Here is what I tried so far:
[Code] ...
Select top 100 * from tab1 order by col1 ---gives me the top 100 order by col1
Select top 200 * from tab1 order by col1 ---gives me the top 200 order by col1
How do I select the top 100 to 200
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
I got two columns DEATHDATE and STATUS
I have to update STATUS Column as INVALID when data in DEATHDATE column as follows
Example of INAVLID DEATH DATE : 1221, 12xt88,12#10, 1, ABC, #@ , etc
Do nothing when the DEATHDATE is VALID like
Examples of Valid/accepatable DEATHDATE: 09122012, 091212 ,******,********, 000000,00000000
How can i achieve this.
I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.
How I can use this variable to email fellow users. For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.
I'm on SQL Server 2005.
I have 2 tables called Customer & CustomerArchive. Both Tables have the same Column names and Datatypes etc.
CustomerID Address Postcode
1 10 Alcove Street 4000
50 21 Spring Street 4032
33 5 Terrace Place 4010
What I need to do is compare each column except for the CustomerID to see if it is the same!
In other words, I need to compare Address in the Customer Table with Address in the CustomerArchive Table.
I have to do this with all columns (12) of them between the 2 Tables.
Is there a efficient & smart way to do this aprt from the obvious way of joing the 2 Tables via the CustomerID and performing a Customer.Address <> CustomerArchive.Address
Customer.PostCode <> CustomerArchive.PostCode
etc etc etc!
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies View RelatedCREATE TABLE #MyTable
(
Teams VARCHAR(10),
StartDate DATETIME,
Count INT
)
INSERT INTO #MyTable (Teams,StartDate,Count)
SELECT 'Team A', '01/01/2014',10
[Code] ....
'Team A' Has No records for '01/02/2014' and '01/03/2014' so I need to insert values of '01/01/2014' StartDate for Team A for the missing dates
So 'Team A' will now have 3 records
Team A2014-01-01 00:00:00.00010
Team A2014-01-02 00:00:00.00010
Team A2014-01-03 00:00:00.00010
'Team B' Has No records for '01/03/2014' so I need to insert values of '01/02/2014' StartDate for Team B for the missing date
So 'Team B' will now have 3 records
Team B2014-01-01 00:00:00.00030
Team B2014-01-02 00:00:00.00040
Team B2014-01-03 00:00:00.00040
As for 'Team C' we have values for all 3 dates, no inserts needed.
I am trying to produce a query result that will be using a Case statement to determine values based on scores in a table for each row. The result needs to be exported to be used to upload to a state reporting website. My problem is that the state requires in the CSV file that is uploaded a lot of fields that we do not actually have in the database table we are creating the result set from. After I receive my result set using the Case statement, is there a way to add additional columns that don't actually exist in a table so I can export directly from SQL?
View 6 Replies View RelatedAn application filters records based on names found in them. Forexample, looking through property buyer names, looking for banks andrelocation companies.I have a table of names and patterns:CREATE TABLE #Filters (Pattern varchar(100), IfWildCard int, Categoryint)INSERT #Filters SELECT 'Bank Of America', 0, 1INSERT #Filters SELECT '% Bank %', 1, 2INSERT #Filters SELECT 'Bank %', 1, 2INSERT #Filters SELECT '% Bank', 1, 2INSERT #Filters SELECT 'Credant Reloc%', 1, 3INSERT #Filters SELECT '%Relocation%, 1, 3The filtering matches the table of candidate names against the filters,and returns Category, where the where clause or join is(Candidate = Pattern AND IfWildCard = 0)OR (Candidate LIKE Pattern AND IfWildCard = 1)"Bank of America" matches an exact pattern, and also a wildcardpattern, and the two different matches give different values forCategory. Is there a way to control which match takes precedence, oris necessary to do it multiple times in the desired order, removingthose that hae already matched from consideration?Thanks,Jim Geissman
View 5 Replies View RelatedI know how to check for a sinle vlaue but how do I chekc to see if multiple values exist. I need to check for certain email addresses from a list that I have.
Let us say I ahve 3 email addresses, I want to check for all of them in a table and for eevery email address that is present I want to print something like "You email address is XXX" and if one of those 3 is not found my results should look like
"You email address is XXX"
YYYYY not found
"You email address is ZZZZ"
I'm attaching some TSQL that I tried on [AdventureWorks2012].[Person].[EmailAddress]
/****** Select ALL if where an email address is present in the list ******/
SELECT EmailAddressID,EmailAddress
FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN
(
'ken0@adventure-works.com', --1
'terri0@adventure-works.com', --2
[Code] ....
-- Test to see if a single email address is present
IF EXISTS
(
SELECT EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN ('25rob0@adventure-works.com')
)
BEGIN
SELECT 'Email address is presnt'
[Code] ....
When I check multiples using EXISTS it works as per its design and says YES even if a single item is present.
IS there a way to combine all matching rows in a table so that itoutputs as one row, for example:tblMyStuffUniqueID int IDENTITYParentID intSomeSuch nvarchar(50)SomeSuch2 nvarchar(50)Table data:UniqueID ParentID SomeSuch SomeSuch21 1 Dog Bark2 1 Cat Meow3 3 Cow Moo4 3 Horse Whinnie5 5 Pig OinkDesired query result from Query:SELECT ??? as myText from tblMyStuff WHERE ParentID = 3myText = Cow Moo, Horse WhinnieHelp is appreciated,lq
View 2 Replies View RelatedGiven an ID (column B), I need to find which IDs have identical data.That is, given '200', I want the desired result to be:100The idea is that the system sees that id=200 has 5 records with theindicated data in cols C and D.It should then find any other ids with the exact same data for thosecolumns.Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,40:5) so they match. 300 and 400 should NOT be returned.Any bright ideas out there? Thanks!DECLARE @a TABLE(A int, B int, C int, D int)DECLARE @b TABLE(A int, B int, C int, D int)INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)SELECT * FROM @a
View 7 Replies View RelatedWhy does sp_helprotect throw an error if there are no special security permissions for a specified @username? Why not simply return a blank resultset? This makes the procedure a hassle to use when trying to capture results into a table as part of another stored procedure (use it for auditing).
[Edited]The same problem occurs when I execute xp_logininfo to get the list of members for a group that has no members. Instead of just passing back some sort of status, the procedure blows up and keeps me from scripting the procedure.
Am I just missing something?
Brandon
Hi
I am very new to SSIS and currently was reading through various manuals but couldnt find a way to do a simple delete.
STAGE_ORDERS looks something like
order_id order_value
100 50000
300 70000
.....
Orders table looks like
order_id order_value
100 9000
200 1000
300 8000
now i want to use SSIS to delete the orders 100,300 which are present in staging table from the orders table .
How do i accomplish this task since i dont see a delete transformation.
How about updates if i want to update the order values from the staging table to the orders table what transformation can accomplish this
regards
Hrishy
I have three tables X,Y,Z. Table 'Y' is having foreign key constraints on tables 'X' and 'Z' (which happen to be primary key tables).
I would like to run a query in which I can retrieve rows from Table 'X' only if the matching rows in Table 'Y' have "ALL" their matching rows available in a simple query being run on Table "Z".
The "All" part is very important.
For more clarification, let me give you an example. Table "X" is equivalent to a mathematical "Equation" table which consists of an equation made up of several "Fields". These fields are stored in Table "Z". Table "Y" contains the primary keys from Tables "X" and "Z". i.e. Table "Y" determines what fields are required for an equation to be complete.
I am having a query "Q" on Table "Z" (Fields table) which returns me a bunch of Fields. Now, on the basis of these fields, I want to retrieve only those Equations (Table "X") which have "ALL" their required Fields present in the bunch retrieved by the Query "Q".
I hope I am clear enough.
Does anyone have any solutions???
Hello All,
I have one table that contains information about invoices.
However I need to find out if invoice numbers have been used more than once.
I've tried to join the table to itself and find matching numbers, however I can't seem to get it to work properly.
Any help will be appreciated.
Thanks,
We were asked to fix a query to get rows from a prior year history table that did not match to rows in the current year to show a variance from one year to the next. Rows must match on [corpnbr],[plincd],[pgrpcd] and [pitmcd]. If the combination has rows in the current and prior year ([hstyr]) then everything is fine. However, if they have rows in the prior year (e.g. [hstyr]='2014') but not in the current year (e.g. [hstyr]='2015') then they do not show in the result. Below is how they designed the table and below that is the stored procedure to pull the records.
CREATE TABLE [dbo].[BillingHistory](
[BillingHistoryID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[plincd] [varchar](3) NULL,
[pgrpcd] [varchar](4) NULL,
[pitmcd] [varchar](4) NULL,
[newplincd] [varchar](2) NULL,
[Code] ....
Hello I am new to SSIS and learning as I go. Any guidance to my questions would be appreciated.
I wrote a script that takes the current date and subtracts a number of days/months from this date. I then attempted to use an SQL Task as a select with a parameter using the calculated date from the script. I was not successful in doing this. While performing searches on the WEB with the hopes of finding a solution I came upon the following text in the Microsoft forum under EXECUTE SQL TASK.
When you use an OLE DB Connection Manager, you cannot use parameterized subqueries because the Execute SQL task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.
Having come upon this statement I moved on to putting together an OLE DB SOURCE with a Flat File Destination. The SQL that I wrote is:
SELECT BP_ID, INVC_NBR, INVC_DT, BUS_ADD_DT
FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)
The flat file destination was mainly used to confirm the select.
Having confirmed my select, I changed the select in the OLE DB SOURCE as follows:
DELETE FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)
I also removed the Flat File Destination. Needless to say when I tried to run the package I did not get very far as a package validation error was encountered since there were no output columns.
Can you share how I should go about peforming the delete as described from the table based on a calculated date? And am I not understanding the comment regarding the SQL Statement and the use of parameters?
Thank you and God Bless.
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table
[Code]....
Our division has approximately 300 employees. We have an annual shift bid where seniority is calculated using Date of Hire. If 2 or more employees share the same Date of Hire then we fall back to Date of Application. Currently the SSRS report does a very simple query and shows all the employees in order of their Date of Hire. If they match then it sorts the matching Date of Hire entries alphabetically by the employee name. It then becomes the task of the scheduler to locatethe entries with the same Date of Hire and manually look up the employees' dates of applications then sort them accordingly and re-write the report.
Goal:
Convert the manual process into an automatic process by modifying the current SSRS report.
Data:
The dataset is "DivDir" which contains the following fields: "EmpName", "DofHire", & "AppDate".
EmpName DofHire AppDate
Adam ...... 12/2/1996 11/15/1996
Bob ..... .... 1/16/1997 12/27/1996
Charlie ....... 1/16/1997 12/12/1996
Dan ...... ... 4/11/2001 3/22/2001
In the above example I want the SSRS report to list the employees in this order: Adam, Charlie, Bob, Dan.How do I do this programmatically using SSRS?
I have the following script below which checks my employee table for duplicate employees based on surname, forename, dateofbirth and NINumber and inserts the duplicates into a temp table i.e. @temptable. I now want to change how NINumber is matched. For example i want to now say:
match on the 1st 8 characters of NI Number where the NI Number does not start with TN.
For NI Numbers that start with TN match on all 9 chars.
Can anyone help with the syntax?
code is as follows:
DECLARE @temptable table(f_id int primary key identity(1,1), NhssNumber int, Surname varchar(18), Forename varchar(11), DateOfBirth datetime, NINumber varchar(9), MaxNhssNumber int)
INSERT INTO @temptable
SELECT
a.NhssNumber,
CAST(a.Surname as varchar(18)) as Surname,
CAST(a.Forename as varchar(11)) as Forename,
CONVERT(char(8),a.DateOfBirth,112) as DateOfBirth,
CAST(a.NINumber as varchar(9)) as NINumber, NULL
FROM dbo.t_Employee a
JOIN
(SELECT Surname, Forename,DateOfBirth,NINumber
FROM dbo.t_Employee
GROUP BY Surname, Forename,DateOfBirth,NiNumber
HAVING COUNT(*) > 1) b
ON a.Surname = b.Surname
AND a.Forename = b.Forename
AND a.DateOfBirth = b.DateOfBirth
AND a.NiNumber = b.NiNumber
ORDER BY a.Surname, a.Forename,a.DateOfBirth,a.NiNumber
select * from @temptable