Group Everything With The Same First Two Letters

Aug 17, 2007



I'm working on a stored procedure that works fine. I just want to make it possible for the user to be able to have a drop down list in reporting services to display the "question codes" grouped by whatever the first two digits are. for example.

VT01
VT02
VT03
VN01
VN02
VN03
ST01
ST02
ST03

instead of listing everything, i want the viewers to see this
VT
VN
ST
or an alias for each of these like this:

Vet Tasks
Vet National
Survey Tasks
Survey National

any ideas, here's my current code, which is pullin up anything with the added substring part




Code Snippet
ALTER PROCEDURE [dbo].[Testing_Questions]
(@Region_Key int=null,@QuestionCode char(5))
AS
BEGIN

SELECT dbo.Qry_Questions.Territory,
dbo.Qry_Questions.SalesResponsible,
dbo.Qry_Questions.Customer,
dbo.Qry_Questions.Date,
dbo.Qry_Questions.StoreName,
dbo.Qry_Questions.PostCode,
dbo.Qry_Questions.Address2,
dbo.Qry_Questions.[Question Code],
dbo.Qry_Questions.Question,
dbo.Qry_Questions.[Response Type],
dbo.Qry_Questions.response,
dbo.Qry_Questions.sales_person_code,
dbo.Qry_Sales_Group.Region_Key,
dbo.Qry_Sales_Group.Region
FROM dbo.Qry_Questions
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
WHERE REGION_KEY=@Region_Key
AND SUBSTRING(dbo.Qry_Questions.[Question Code],0,3)=@QuestionCode

END
SET NOCOUNT OFF

View 1 Replies


ADVERTISEMENT

CAPITAL LETTERS

Sep 6, 2007

Any option on sql server to have the reserved words converted into CAPITAL when I type it in. Like "SELECT"

=============================
http://www.sqlserverstudy.com

View 8 Replies View Related

WHERE Includes All Letters

Apr 18, 2006

Hi

I would like to perform a procedure
which takes a string of numbers and searches for strings in a table which include all the
given numbers in any particular order. problem is i can only return strings
which include 'any' of the letters in the search string and not
'all'.

this is how i perform it so far:

@IncludeNumbers
VARCHAR(50)

AS
BEGIN

SELECT Phone AS [Telephone
Number]

FROM tbl_Person AS p

WHERE (p.Phone LIKE '%[' +
@IncludeNumbers + ']%')

END

(ive removed some unecessary bits but
this shows the basics)

As you can see currently it will return any phone numbers which contain any numbers given in the includeNumbers string, I would only like it to return phone numbers which contain all the numbers given in the includeNumbers string. is there any way to make it search for strings
which include 'all' numbers given?

thanks

Robert

View 7 Replies View Related

Query Column With Letters

Sep 22, 2004

I have a database with a zip code column. I want to flag all rows that have letters in that column (a-z). How could I construct the where clause?

Is there a better alternative to:
WHERE ZipCode LIKE '%a%' OR ZipCode LIKE '%b%' ...

View 5 Replies View Related

Drive Letters And MDF/LDF Files...

Apr 18, 2007

All,

I'm writing to ask if anyone knows whether or not MS SQL server stores in any system tables the association between a database and the drive letter/directory path where its corresponding MDF/LDF files are located.

Thanks,

Isaac

View 4 Replies View Related

Changing Drive Letters

Oct 9, 2007

All,

I have a test server that needs the drive letters changed for the data, log and backup drives. Is there a way to make this change without reinstalling SQL Server?


Future guru in the making.

View 13 Replies View Related

Dynamic Form Letters

Aug 6, 2006

I would like to design a 'mail merge' type of letter, whereby some of the paragraphs will be conditional on parameters. Can anyone direct me to example of mail merger letters using reporting services?

thanks

ken

View 1 Replies View Related

Generating Letters In SSRS

Aug 21, 2007

Hi. I'm looking for tutorials on how to use SSRS 2005 to generate letters in Word or PDF format from a database. We need to send letters with our company logo and some boilerplate text to a set of companies based on a SQL Server query. So the letter will look something like this, with the red areas repeating from the dataset,


[logo goes here]

123 Any St.
AnyCity, CA 90210

[today's date]

Dear [name from database],

Some boilerplate text goes here. Your Account [account number from database] is past due.


Sincerely,

[scanned signature goes here]

Somebody

View 2 Replies View Related

How To Apply Capital Letters But Only The First One?

Nov 20, 2007

Hi everyone,

As source on a Sql25k table I've got this (often):

XXXXX ZZZZZZZ YYYYYYYY


And, as a result I'd need this (plain file):

Xxxxx Zzzzzz Yyyyy

Ok guys, with VbScript inside a DTS it's very easy, building a loop and then playing with Ucase, Left functions stuff and so on...

How could I do this using a DTSX package?? Any specialized task?

Thanks indeed for your help,

View 29 Replies View Related

Searching For Strings That Contain Characters Other Than Letters?

Aug 4, 2004

Hi,

I have a table in an Access db that stores information about speech files. One of the fields in this table is called "Text" and it contains the phrase spoken in that particular speechfile.

These phrases often have characters such as the "#" sign at the end to indicate what tone of voice is used.

I am trying to create a Search where users can enter the phrase they are looking for, and will be returned the file (or combination of files) that contain this phrase.

My problem is, when I try to search for a string of text that includes the "#" I get 0 results everytime.

An example of what I am doing is this:

SELECT Speechfiles.Name FROM Speechfiles
WHERE Speechfiles.Text LIKE 'aero#'


It works fine for 'aero' or '*aero*' but whenever I try to add a character that is not a letter, it won't work.

If anyone has any ideas, I would REALLY appreciate it!!! I am completely at a loss.

Thanks so much....

View 1 Replies View Related

Help On Extracting Only With Three Capital Letters From The Field

Feb 12, 2008

Hi Everybody

I have this situation where I need to extract values from a field that satisfies this criteria...

ONLY THREE LETTERS AND
ALL IN CAPITAL LETTERS

thanks

View 14 Replies View Related

First Letters Of A String To Upper Case

Apr 23, 2002

Ho can I convert first letters of a string to Upper Case (i.e. UNITED KINGDOM - Untited Kingdom). I have country names table which has all entries in uper case. This makes a select box very larg and unproportional.
Thanks in advance for the help.


Php95saj

View 14 Replies View Related

Restoring The Master To Different Drive Letters

Jul 20, 2005

Can you restore the master database, running on windows server 2000, sqlserver 2000 from a backup that was taken from c: to another serverrestoring to d:?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

.NET Framework :: Generating PDF Letters From Server

Jul 9, 2015

Generating different types of letter (PDF formats) from SQL server?

View 3 Replies View Related

Sorting A Table With Numbers And Letters

Jan 25, 2007

I have a field (varchar) in a list that contains numbers and letters. I want to sort this table but I have only two functions that will work to convert the values:

Val
The Val function sorts the numbers in the string, but the letters are not sorted

CStr
The CStr function sorts the letters, but the numbers are not sorted

How can I sort the numbers and letters?

View 4 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName'  values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC)  must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName                 Clm ABC-X

X

Page2:

GroupName                 Clm ABC-Y

Y

Page3:

GroupName                 Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

Query Help, Get Count Of Names Having Distinct Letters

May 8, 2007

here is a query


Code:

select count(d) from (select distinct left(name,9) as d from bscs where region like 'n1' and name like 'Alc_%')



what i am trying to do is to get count of the names, having distinct letters in left 9 positions and some conditions too. How to do it?

View 5 Replies View Related

SELECT Only The Newest, But Based On Revised Letters

Feb 11, 2004

Ok here's a big one.

First I'm a big NEWBIE, so it'd be great if you can provide a little explanation as to how this should be done...

Here's what I want to do, but have no idea how to approach it.

I have a table with Quotes (table Quote) in them (for a Sale's Team). Each has a quote number (qtQN) and this number is sequencial but sometimes revised where a letter is added at the end. Like so:

qtQN__________Date
---------------------------
111q0001--------02/01/04
111q0002--------02/02/04
111q0002A------02/03/04
111q0002B -----02/04/04
222q0005--------01/15/04
etc... ------------- etc...

The first 3 digit are company codes and pretty unimportant to this problem. As you can see, 111q0002 has three versions. A, B, and no letter. The most up to date is B. So in this list I want to list only the most up to date quotes. So the resulting list would be:

111q0001
111q0002B
222q0005

Get it? Good, cuz I have no idea how to query that... any help at all is appreciated!

I'm using MS SQL Server 2k and scripting with ASP 3.0

View 6 Replies View Related

SQL Server 2012 :: Parse A String Every 6 Letters

Jan 28, 2015

How to parse a string to equal length substrings in SQL

I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..

CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)

INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp

After parsing I have to use these values in a where clause like this

IN('000001','0T0001','0L0001','000T01','0C0001')

Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow.. So the parsing should be dynamic.

View 2 Replies View Related

SQL 2012 :: Swapping Drive Letters In Cluster

Jul 3, 2015

As part of a migration of data to a new SAN I have hit a bit of a snag in the migration. In summary what will happen is user database data files will be moved from one LUN (say drive F:) to a new LUN (say drive G:). Once all the data is migrated, plan is to remove dependency of that drive from SQL server and remove the drive and delete the LUN. So far, so good.

However one of the LUNs (drive D:) destined to be deleted also hosts the instance default directories, i.e. everything under MSSQL11.MSSQLSERVER (Data, Backups, FTData, JOBS, etc). BOL has articles on how to migrate system databases, including tempdb. But there is no guidance that I could find on how to relocate other folders. There are forums where users have listed registry changes, etc that can achieve this but these are steps I am unwilling to take on a production server.

So my plan is:
1) Add new drive to cluster (drive E:), sufficiently large enough to host instance default folders
2) Shutdown SQL server
3) Copy all default folders to new drive
4) Swap drive letters so that new drive is now D:
5) Start SQL server and if everything works, delete the original drive (which is now drive E:).

View 3 Replies View Related

How To Convert Selected Text Into Capital Letters

Oct 24, 2013

i have string where i need to convert them into Capital text in required string only

i have written query to do this using Charindex it is finding only one how to go for 2nd one i am only finding the 2 nd word through index how to find other words also

select UPPER(LEFT(SUBSTRING('AHRQ utilization flag',CHARINDEX(' ','AHRQ utilization flag',0)+1,
len('AHRQ utilization flag')),1))

For Example :

have a great day

my desired out put :

Have A Great Day

how to do this one .......

View 2 Replies View Related

Extract Numbers Or Letters From Mixed String

Mar 16, 2004

Yeah, it's pretty simple. Maybe it'll help someone out.


-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers')
CREATE FUNCTION fn_extract_chars (@x varchar(128), @y char(7))
RETURNS varchar(128)
AS
BEGIN
DECLARE @chars varchar(128)
DECLARE @pos int
DECLARE @action varchar(32)
SET @pos = 0
SET @chars = ''

IF @y = 'numbers' SET @action = '[0-9]'
ELSE IF @y = 'letters' SET @action = '[a-zA-Z]'

WHILE @pos < (DATALENGTH(@x) + 1)
BEGIN
IF PATINDEX(@action,SUBSTRING(@x, @pos, 1)) > 0
BEGIN
SET @chars = @chars + (SELECT SUBSTRING(@x, @pos, 1))
END
SET @pos = @pos + 1
END
RETURN(@chars)
END

View 4 Replies View Related

Transact SQL :: How To Get Indexes Of Capital Letters In A String

Sep 15, 2015

Is it possible to pull the index(s) for capital words in a string.  For example, if I have a string that says, GetTableName, would it be possible to output, 0, 3, and 8?

View 5 Replies View Related

Adding New Volume And Swapping Drive Letters....

Apr 11, 2007

Hello - I have a SQL 2000 server which has a D: drive that contains all of my databases (system and user). I am running out of space on this volume and need to migrate the contents of this volume to a larger one. My initial plan was to introduce a new volume to the server (say a K: drive). Backup all databases (of course), and then stop all SQL services. Copy all data from D: to K:. Once data is copied, swap drive letter names (D: to I: and then K: to D. Then restart SQL services. SQL should not know any better since everything was on the D: drive when it went down, and everythiing is still on the D: drive when it came back up, correct?

The other option mentioned is to detatch the databases, copy the data and then reattach them in their new locations. I understand this method, but it seems more involved (and riskier) than just renaming the drives. Does anyone have an opinion regarding these two migration methods? Thanks for your help.

Chris

View 3 Replies View Related

SQL Server 2012 :: Removing Character (Letters Only) From A String

Dec 19, 2014

I need to build a query, or use a function to removing alfabetic Character from a string.

My string is
WI14000323-0003

The value i want to keep is : 14000323-0003.

I try using this SELECT:

SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable

but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.

View 2 Replies View Related

Transact SQL :: Adding Alphabet Letters To Column Values

Oct 14, 2015

I am trying to do the following; 

declare @table table
  (
ID int,
InvoiceNumber varchar(10)
  )
insert into @table

values (1, 20),
(2, 20),
(2, 20),
(3, 60),
(3, 60)

I am trying to add sequential alphabet letters to the InvoiceNumber value on each row. Example;

1 20a
2 20b
3 20c
4 60a
5 60b
and so on. 

View 4 Replies View Related

Reporting Services :: How To Display Amount In Letters Format In SSRS

Apr 25, 2015

I have doubt In ssrs while doing report like If We have Amount Column In a Table, then I want to display amount in letters  Format.

Ex: 5000 /- Now I want to display  Amount As Five Thousand Rupees Only.

View 3 Replies View Related

T-SQL (SS2K8) :: Find All Instances Of String That Contain Certain Letters - Escape Double Quote

Sep 14, 2015

I am trying to find all instances of a string that contain the letters FSC. While I am able to find most of them, I am unable to find the ones wrapped in double quotes.

Query example:

Select *
from myTable
Where item like '%FSC%'

This works great with the exception of when FSC is surrounded by double quotes.

ex: MySentencecontains"FSC"

I have tried Replace with no luck.

View 3 Replies View Related

Text Is Getting Cut After 1.4k Letters In Text Data Type

Oct 26, 2007

Hello all,
started working on a website in ASP that use mssql, i am trying to store large ammount of text, for now its just 10kk letters, the data type is set to text in the mssql db, but when i read it back from the db, i only get 7.7k letters, i know its not an ASP problem cause i print the var holding the content before storing it in the db.

my question is, is there a way to change the text length limit in mssql, or use another data type?

Thanks,
Gilad Darshan


EDIT: forgot its saving the html source with the style and not only the words. fixed to the right amount of letters

View 2 Replies View Related

What's Microsoft Doing About Providing Page Numbering Per Group And Total Pages Per Group?

Nov 27, 2007

Hi!

I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.


https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679

Provide Individual Page Numbering per Group and Total Pages per Group

Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.


I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.


Thanks!
Karen

View 1 Replies View Related

Reporting Services :: RDL XML To Unmerge Cells In Group BUT Keep Group Drill-In In Excel

Nov 6, 2015

I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this. 

View 4 Replies View Related

SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?

Jan 10, 2014

I'd like to ask how you would get the OUTPUT below from the TABLE below:

TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B

OUTPUT:

category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6

The code would go something like:

Select category, count(*), .... as id's
from TABLE
group by category

I just need to find that .... part.

View 3 Replies View Related

Error 15401: Windows NT Group Or Group Not Found

Sep 25, 2003

I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager

Now when I try to add Mikel, Im getting error 15401.

Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?

Can I go into the Master database and just change Mike login to Mikel ?

Thank you

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved