Viewing 15 posts - 181 through 195 (of 445 total)
Luis Cazares (4/22/2015)
Your function is missing the ORDER BY at the end. It might seem to return the correct results but it might fail at any time.
Thank you, Luis. Then...
April 22, 2015 at 3:18 pm
What do you mean, 'relationships'? Are there only FK or some triggers do exist?
If DB has only FK dependencies between tables then sure we can retrieve them from sys.foreign_keys....
April 22, 2015 at 3:00 pm
I've found two different queries which return each code and associated description by ID in separate rows. Perhaps the next step is to aggregate from this?
Not a DBA, rather...
April 22, 2015 at 1:38 pm
Try
DECLARE @sampleData TABLE(
ID VARCHAR(5) NOT NULL
, Territory VARCHAR(12)
, Total_Used int
, [Date] DATE
);
INSERT INTO @sampleData(ID,Territory,Total_Used,[Date]) VALUES
('ACASC','CAL071',287,'2014-06-01')
, ('ACASC','CAL071',287,'2014-08-01')
, ('ACASC','CAL071',288,'2014-09-01')
, ('ACASC','CAL071',190,'2014-11-01')-- which is 'latest'?
, ('ACASC','CAL071',288,'2014-11-01')--
, ('ACASC','CAL071',NULL,'2014-12-01')
,...
April 22, 2015 at 1:15 pm
Yes, ASCII _is_ collation dependent.
select ASCII(s collate Chinese_Simplified_Pinyin_100_BIN)
,ASCII(s collate Greek_100_BIN)
,ASCII(s collate Latin1_General_100_BIN)
from (values
(NCHAR(0x0020)),(NCHAR(0x0120)),(NCHAR(0x0220)),(NCHAR(0x0320))
,(NCHAR(0x2000)),(NCHAR(0x2001)),(NCHAR(0x2002)),(NCHAR(0x2003))
) t(s)
I should check it first of...
April 22, 2015 at 7:49 am
NCHAR(0x0020) which is binary dumped as 0x2000 is the only symbol with ASCII =32 and it's trimmed OK. All the rest have other ASCII.
with tt as (
select top(256)...
April 22, 2015 at 7:28 am
Sql 2008
select s, ASCII(s),UNICODE(s), dump =cast(s as varbinary(10))
from (values
(NCHAR(0x0020)),(NCHAR(0x0120)),(NCHAR(0x0220)),(NCHAR(0x0320))
,(NCHAR(0x2000)),(NCHAR(0x2001)),(NCHAR(0x2002)),(NCHAR(0x2003))
) t(s)
s(No column name)(No column name)dump
32320x2000
G712880x2001
?635440x2002
?638000x2003
6381920x0020
6381930x0120
6381940x0220
6381950x0320
April 22, 2015 at 6:57 am
The weird think is that OP's untrimmable symbol has ASCII =32.
All untrimmable spaces demonstrated so far have other ASCII codes reported by SQL.
April 22, 2015 at 6:12 am
I second Cadavre's suggestion please show column dump.
The script below reproduces what looks like untrimmable blank, but reported ASCII is not 32.
declare @1 varbinary(100) = cast(N'A' as varbinary(100))
declare @2 varchar(100)...
April 22, 2015 at 5:21 am
dwain.c (4/21/2015)
Alternate approach:
Nice solution. And easily extendable to milliseconds or years.
May i suggest a little tweak
CREATE TABLE #table_Data(
[idx] [int] IDENTITY(1,1) NOT NULL,
[crtDte] [datetime] NOT NULL
) ON [PRIMARY];
DECLARE @StartDT DATETIME =...
April 22, 2015 at 3:22 am
dwain.c (4/21/2015)
Recursion is not needed here if this is really being done in SQL 2012.
If i got OP's task right, recursion is inevitable. As far as i understand having...
April 22, 2015 at 1:31 am
Yes, certainly.
-- based on http://www.sqlservercentral.com/articles/Tally+Table/72993/
DROP FUNCTION [dbo].[DelimitedSplit8KFixedNbr]
go
CREATE FUNCTION [dbo].[DelimitedSplit8KFixedNbr]
(@pString VARCHAR(8000), @pDelimiter CHAR(1)
, @cnt int -- number of items >=1
)
RETURNS TABLE WITH SCHEMABINDING...
April 21, 2015 at 2:50 pm
May be something like
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsample]') AND type in (N'U'))
DROP TABLE [dbo].[tsample]
GO
create table tsample (
id int identity(1,1)
,val varchar(8000));
insert tsample values
('B_080623719__...
April 21, 2015 at 9:47 am
I'm just trying to figure out what OP needs to get as a result. My take, he needs
- get a row set by splitting an initial row,
- delete...
April 21, 2015 at 8:32 am
Split + take first given number of values + add nulls if splitted values set is shorter then requied length + PIVOT ?
April 21, 2015 at 7:36 am
Viewing 15 posts - 181 through 195 (of 445 total)