T-SQL (SS2K8) :: How To Make A Column Comply To A Specific Format

Dec 4, 2014

I need to insert and update a table. One column of the table needs to conform to a format as: XXXXXXX.XXXXX

If any data is not complying to the format an error needs to be thrown.

How to implement this constraint!

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Concatenate Specific Values From A Column

May 20, 2014

Give this data set;

declare @BO table (Col1 varchar(50), Col2 varchar(50))
insert into @BO values
('01','009920140516102116'),
('071710811019600001000005',''),
('070534524264000001000005',''),
('001806505517000001000005',''),

[Code] ....

select * from @BO

The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered. The desired result set would look like:

declare @BOD table (Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @BOD values
('01','009920140516102116',''),
('071710811019600001000005','','01071710811019600001000005'),
('070534524264000001000005','','01070534524264000001000005'),

[Code] ....

View 7 Replies View Related

T-SQL (SS2K8) :: Specific Column Matching With Nulls

Apr 9, 2015

I'm working on a join between two tables where I only want one row returned... I'm matching on two columns between two tables. One of those columns in the target table could be null. I only want one record returned.

create table #vehicle(id int, vehiclemake varchar(10), vehiclemodel varchar(10), classtype varchar(1))
create table #class(id int, classtype varchar(1), value int)

insert into #vehicle values(1, 'AUDI', 'R8', 'A')
insert into #vehicle values(2, 'AUDI', null, 'B')

insert into #class values(1, 'A', 100)
insert into #class values(2, 'B', 1)

[Code] ....

Using the above example, if VehicleModel is anything other than 'R8' is specified then I want it to return the other class type record.

This is going to be used as a join within a bigger statement, so I'm not sure ordering and returning top 1 is going to work.

View 9 Replies View Related

T-SQL (SS2K8) :: Find All Rows From All Tables In A DB For Specific Column?

Mar 28, 2014

I have a DB with some tables and, on certain tables, i've a column named "ID_COMPUTER".

I need to find all the rows where id_computer=<specific_value> from all the tables of my database, when column "ID_COMPUTER" exists.

Pseudo-code would be: select * from db.* where id_computer=<specific_value>

how to write this ?

View 2 Replies View Related

Conditional Number Format On Specific Matrix Column Group

Feb 6, 2008

I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.

For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125

By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
Revenue Cost Total
Customer1 -100 60 -40
Customer2 -200 125 -75

But the users need the report to look like this, with all positives (why, oh why?! ):
Revenue Cost Total
Customer1 100 60 40
Customer2 200 125 75


I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:

=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")

But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:

=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")

The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?

How else can I do this? I must be missing something here. Thanks.

View 6 Replies View Related

T-SQL (SS2K8) :: Export Column To TXT Format

May 21, 2014

I have table1 with col1 varchar,col2 int , col3 xml , col4 bit ...best way to fetch the col3 into file (.txt or .sql) into seprate file for each col3 record . I want to export this in different files for each record if possible with date and time stamp .

View 5 Replies View Related

Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)

May 23, 2015

I need to select specific values from all rows where the value of a specific column is "Active"

This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client

But I want to add: WHERE StatusType = (Active) and how to do this.

View 4 Replies View Related

Whqt Is The TSQL Statement To Make A Backup Of A Specific Databese??

May 10, 2008

i have a database names "students" in SQL server 2000. is there any TSQL statement to make a backup of the student databse in to a sspecific location ??????????

pls help

View 4 Replies View Related

T-SQL (SS2K8) :: IF 0.00 Then Make It .00

Jan 28, 2015

I thought this would be somewhat easy but I'm having trouble with this one. I have a statement that if 'ACTLABCOST' or 'ACTMATCOST' has a value of 0.00 then I need to make it .00.

Here's the statement:

Select
CONVERT(VARCHAR(10), xn_approveddate, 101) + ' ' + convert(VARCHAR(8), xn_approveddate, 108)as "Approved Date",
WORKTYPE,

[Code]....

View 5 Replies View Related

What Tools Do You Use To Comply With Sarbanes-Oxley?

Jan 11, 2008

Thanks for your input in advance.

View 4 Replies View Related

T-SQL (SS2K8) :: How To Make Sum Of Values For The Month

May 19, 2014

I've the table like

create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)

insert into expense values('01-04-2014',1200,250,0,0)
('02-04-2014','0',0,500,600)
('0-04-2014','800',300,0,0)

Like I've 30th onwards.....

Expecting o/p:

month Travel Fixed Food Lodge
apr-14 200 550 500 600

These cum column values how to make a code ?????

View 5 Replies View Related

T-SQL (SS2K8) :: Track How Many Times A Specific SP Called Every Day

Apr 3, 2014

I need to know /observe for a period like 10 days in a row HOW MANY TIMES a specific Stored Proc is executed on the server. How can I do that? is there a way to precisely know a number of such calls via DMVs?

View 9 Replies View Related

T-SQL (SS2K8) :: Create Index For A Specific Query That Is Used A Lot?

Jul 28, 2014

I am trying to create an index for a specific query that is used a lot.

Unfortunately I am keep getting a clustered index scan. (4.6 mil rows)

Googling "AND" and "OR" is tricky, but did lead met to many articles about filtered indexes.

None of which gave me a clear answer.

How can I make an index for this query?

Here's the code setup

------------------------------------------------------------------------------------------
-- Setup
------------------------------------------------------------------------------------------
IF OBJECT_ID('FilterTbl') IS NOT NULL
DROP TABLE FilterTbl
CREATE TABLE FilterTbl
(
SurroIDInt IDENTITY PRIMARY KEY,
CompIDSmallInt,

[code]....

View 3 Replies View Related

T-SQL (SS2K8) :: Find Best Index For Specific Query

Dec 28, 2014

This is my table:

use tempdb
go
if object_id('Data', 'u') is not null drop table Data
go
with temp as (
select top 10000 row_number() over (order by c1.object_id) Id
from sys.columns c1 cross join sys.columns c2

[code]....

What index would be best for these three queries? With best I mean the execution time, I don't care about additional space.

This is the index I currently use:
create nonclustered index Ix_Data on Data (StateId, PalletId, BoxId, Id)
The execution plan is SELECT (0%) - Stream Aggregate (10%) - Index Scan (90%).

Can this be optimized (maybe to use Index Seek method)?

View 7 Replies View Related

T-SQL (SS2K8) :: Replace String After Specific Index

Mar 23, 2015

I have data like below

Potter, James J
Williams, Ted R
Allen, Gary G

I want to remove Middle Name from the output

Potter, James
Williams, Ted
Allen, Gary

My Query:

SELECT
CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN
REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '')
ELSE Supervisor END AS NewSupervisor from data d

However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"

Do we have any way to say sql to replace after certain index?

View 3 Replies View Related

T-SQL (SS2K8) :: Search Through Rows With Specific Values

Sep 16, 2015

I've a table that stores operationcode for each jobnumber. The jobnumber can have multiple operationcode. From the below DDL, I need to show all the jobs that have operation codes as 2001 and 2002. In the below DDL Jobnumber 80011 has both the operation codes 2001 and 2002 so this job will display on the report.

On the other hand Job 80021 only has operationcode 2001 and I do not want this job to show up on the report.

I need to show all the operationcodes for a job if it has operationcode 2001 and 2002.

USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, OperationCodeVARCHAR(10) NOT NULL
, EmployeeCode VARCHAR(10) NOT NULL

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: CTE Query To Find Specific Record?

Sep 18, 2015

I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.

I know that i can build this with a correlated subquery, but i´am curious about using CTE.

I post sample create Script:

create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)

insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
union
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
union
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'
union

[code]....

View 5 Replies View Related

T-SQL (SS2K8) :: How To Make Code Into Cursor Within Procedure

Feb 3, 2015

i wanna create a procedure for P& L cost sheet , i had done that procedure now include a cursor instead of replacing sql queries .

create procedure pl_test
@fmdate datetime,
@todate datetime,
@categ varchar(2000)
begin
create table #temp

[code]....

how to include cursor on if part and else part

View 2 Replies View Related

T-SQL (SS2K8) :: Different Levels To Make Table Flat

May 6, 2015

I am trying to fill my table in different levels (hierarchy). The different levels are not properly processed. All layers now have the same value. It is intended that the layers are beaten flat. This implies multiple joins in order to be able to get the different levels.

'Ledgertableinterval' could be used to determine the hierarchy, but I did not come all the way out ...

So my suggestion is to fill with 1 character level 1, level 2 with 2 character and so on. If a level is not available anymore than filling up with higher level. For this you can use the replace function.

The DIM that I use;

USE [NJ]
GO

/****** Object: Table [DIM].[FA] Script Date: 05/06/2015 09:57:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [DIM].[FA](

[Code] .....

View 1 Replies View Related

SQL 2012 :: Output In Specific Format

Dec 12, 2014

I have sample data like 'J.S.xxxx','J.xxxx.'

Want to separate the . in the middle of word with single space and display results in following way.

J. S. xxxx
J. xxxx

Need the code in optimized way.

View 3 Replies View Related

Exporting File Name In Specific Format

Jul 17, 2013

I tried to scheduled and save a Report Via SSRS. The Exporting file name need to be in this format -- 17July2013_NewSystem.csv

I have tried using @timestamp in file name, but the file was saved like this 2013_07_17_090529_NewSystem

How to specify the filename to get the desired format ie. the file name deposited in the folder be --

17July2013_NewSystem.csv
18July2013_NewSystem.csv (for tomorrow and so on)

View 1 Replies View Related

Get Recent Event In Specific Format

Dec 17, 2014

I have event table with below sample value :

table name : Events

EventId EventName EventDateTime StartTime
----------------------------------------------
1 Event1 2014-12-16 00:00:00.000 4:30 PM
2 Event2 2014-12-16 00:00:00.000 8:00 PM
4 Event4 2014-12-17 00:00:00.000 10:30 AM
3 Event3 2014-12-08 00:00:00.000 11:25 AM
1 Event1 2014-12-17 00:00:00.000 8:30 PM
2 Event2 2014-12-12 00:00:00.000 6:00 PM
3 Event3 2014-12-15 00:00:00.000 10:45 AM
5 Event5 2014-12-16 00:00:00.000 2:30 PM

I would like to get the next recent Event Based on the current date time, It should be like below format...Suppose today is 2014-12-17 and Current Time is 07:30 AM The expected result should be

NextEvent
------------
Upcomming Event : Event 4 - This Morning 10:45 AM

Specifically i am looking for a Format Like Below

If we have the event by TODAY, The result should be in below format
This Morning (12:00 AM to 12:00 PM)
This Afternoon (12:00 PM to 4:00 PM)
This Evening (4:00 PM to 7:00 PM)
Tonight (7:00 PM to 11:59 PM)

[code]....

how to get this expected result in SP/

View 1 Replies View Related

T-SQL (SS2K8) :: Adding Two Month - How To Make Year Change

Oct 15, 2014

This statement adds two additional months to which is fine :

DATENAME(MM,dd.date)+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,dd.date)

but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.

View 7 Replies View Related

T-SQL (SS2K8) :: How To Make Soundex Functions Include The First Letter

Oct 8, 2015

I need DIFFERENCE('Kolton','Colton') to equal 4 rather than 3.

But SOUNDEX keeps the first letter of the word:

SOUNDEX('Kolton') = K435
SOUNDEX('Colton') = C435

View 6 Replies View Related

Asp.net Want To Make SQL Time Format Of 00:00:00:000 To 14:42:51:153 When Inserting In Database

Nov 12, 2007

I have asp.net 1.1 web form and it inserts date and time in  SQL database, but it insert only date not time , It insert date time in following format
2002-01-22 00:00:00.000
some one tell me why it is not inserting time or why it is inserting time as 00:00:00:000.
I want to my time to look like 14:42:51:153. (format)
How can I change my time format
give me asp.net codes for time formating or what do I need to do to resolve the problem,
 
thank you
maxmax

View 1 Replies View Related

T-SQL (SS2K8) :: Call Stored Procedure And Obtain Specific Results

Apr 17, 2014

I have a few questions about the following t-sql 2008 r2 sql code listed below that is calling a stored procedure:

DECLARE@return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C'

EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules

The sql listed above does execute the stored procedure called [dbo].[sproom] successfully and returns all the data all the rows from the stored procedure multiple times. However can you tell me the following:

1. How can I have the stored procedure return distinct rows?
2. I want the stored procedure to return selected columns. I tried using the OUTPUT parameter for some of the columns, but I got the error message, "Procedure or function spHomeroom has too many arguments specified.".

when I change the sql above to:

DECLARE @return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C',
@CustName varchar(50)
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
,@CustName

That is when I get the error message.

A solution might be to change the stored procedure, but I would prefer not to since this is a generic stored procedure that I believe a lot of t-sqls and stored procedures will use.

View 3 Replies View Related

T-SQL (SS2K8) :: Run Block In Stored Procedure Only During Specific Time Frame

May 11, 2015

I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.

Currently, my block looks like this:
IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL
BEGIN

I can get the current minutes of the current time by using:

Select DATEPART(MINUTE,GetDate())

I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there.

View 7 Replies View Related

T-SQL (SS2K8) :: Create Periods Transaction Dates And Make Them Columns

Sep 4, 2014

I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.

I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!

select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
sum(gl_ledger.amount_n) as Net
from gl_account

[Code] ....

View 9 Replies View Related

Output In Specific Number Format With Leading Zeros

May 16, 2013

I am trying to output a number in a specific format. I am playing with CAST() and CONVERT() but have not been able to get what I need.

Current: 0.019891
Desired: 000199

It doesn't have to remain in a number format, as i will be output to a CSV in order to bulk load into another system.

View 4 Replies View Related

Transact SQL :: Flattening Hierarchical Data In Specific Format

Apr 22, 2015

I have the data in this format

ID        NAME         ParentID
CV1      CV1NAME      CV
CVX1    CVX1NAME    CV1
CVXX1  CVXX1NAME  CVX1
CV2      CV2NAME     CV
CVX2    CVX2NAME    CV2
CVXX2  CVXX2NAME  CVX2

How can i flatten this data into this format

CVID    CVNAME     CVXID   CVXNAME   CVXXID   CVXXNAME
cv1        cv1name  cvx1        cvx1name    cvxx1     cvxx1name
cv2        cv2name  cvx2        cvx2name    cvxx2     cvxx2name

View 4 Replies View Related

Extract Specific Words From A Free Format String

Feb 27, 2008

I am required to send an XML file of our clients to head office in Belgium for comparison against a database of known undesirables. The data is in a legacy system with a custom database so I have created an SSIS package that extracts the tables I need into SQL Server and have developed a program that reads from a text source and creates the XML then Secure FTPs it to Hong Kong who will handle it from there.

My problem lies in actually extracting enough data to avoid too many false positives. The scanning will check name, identity (passport number, etc.), town/city and country. We don't hold an identity number and the town/city and country are buried in free format fields. A quick analysis of the 419,000 records shows that the spelling is terribly unreliable, too. In most cases country has not been entered because the clients are local and even when they are overseas, sometimes only the city has been entered. That is often misspelt, too e.g. Kuala Lumpar or Melboure.

The addresses are held in 3 equal length fields called Address_1, Address_2 and Address_3. There's no guarantee that I will find the town/city or country in any particular one of these fields. In some cases, the street number and name are in Address_3 because the first two hold a company name and a C/O line.

So I'm not going to fret over the ones where the address information is nonsense or missing but I would like to try and extract valid country names and town/city names, where present and this is where I get stuck. I'm from a COBOL programming background and although I'm loving getting used to the power of SQL, I'm still a bit stumped when I come across a problem like this probably because I keep thinking of the solution in procedural terms.

I have a feeling that the solution will be to create two separate reference tables, one of towns/cities and the other of countries. I would then somehow search the 3 fields looking for those keywords and if found, entering them in the appropriate part of the output text file to represent town/city and/or country. I did also think about destringing to find the separate words but that doesn't help where the name consists of two words such as NEW ZEALAND.

I would love to hear from anyone who has dealt with a similar problem and has a neat solution to this using SQL.

View 4 Replies View Related

SQL Server 2012 :: Output Of Table Required In Specific Format

Apr 17, 2014

I need to display the output of a table in a specific format, I have attached the sample screenshot and code for reference.

I tried with pivot but does not seems to be working.

View 2 Replies View Related

Reporting Services :: Need To Display Current Time In Specific Format

Jun 1, 2015

Need to display current time in below format:

06/01/2015 at 7:00 a PST
(Date at time TimeZone)

View 2 Replies View Related







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