Saturday, July 6, 2013

[T-SQL] Barcode font 128b -SQL function

[T-SQL] Barcode font 128b -SQL function

Barcode font 128b -SQL function

Posted: 05 Jul 2013 11:06 PM PDT

Hi,I am looking for an SQL function that will generate the correct characters to make barcode font code128 work when used in Microsoft Word. The data is passed to word with the data tag using the barcode font.This is a web link to the rules and where the font can be downloaded.http://grandzebu.net/informatique/codbar-en/code128.htmA bit more info on the font herehttp://en.wikipedia.org/wiki/Code_128And I triedhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70901http://www.sqlservercentral.com/Forums/Topic1065554-149-1.aspxhttp://www.sqlservercentral.com/Forums/Topic1062489-1291-1.aspxBut none of these solutions appear to work when tested with a barcode reader. The bar code reader does work because we tested it against web sites generating the barcodes and it is code128b we are working withAnyone any ideas/SQL functions that do work.Many thanksEliza

blocking remove

Posted: 05 Jul 2013 06:53 AM PDT

hii am adding new column with default valuethis table has millions of rows, so while i am executing my query ,its causing blocking to other user.how to solve thisexamplealter table empadd column senti_v int default 0thanks

Sequential numeric

Posted: 05 Jul 2013 06:52 AM PDT

I have a need to update numeric values in a table column so that they are unique and sequencial, the order of which is determined by another column in the same table, and by data in a 2nd table. I am running into a problem when there is duplicate data, and I can't figure out how to get the numeric values to be unique.For the data below, I want the seq_nbr column in TABLE1 to be in order 1,2,3,4 based on the order the codes are listed horizontally in TABLE2. TABLE1seq_no seq_nbr dx_codeE3CD8342-1294-4CBA-9201-D51C07E9FB0C 1 366.16997312BA-8C90-4773-B0FC-1838C46A4728 3 370.035DC781A2-71BC-4148-9D56-DA95D3F8F081 4 362.52E65354B3-F404-430B-8153-EDD7D1921431 4 362.52TABLE2dx_code1 dx_code2 dx_code3 dx_code4366.16 362.52 370.03 362.52[code="sql"]CREATE TABLE Table1 (seq_no UNIQUEIDENTIFIER,seq_nbr INT,dx_code VARCHAR(6)) CREATE TABLE Table2 (dx_code1 VARCHAR(6),dx_code2 VARCHAR(6),dx_code3 VARCHAR(6),dx_code4 VARCHAR(6)) INSERT INTO Table1(seq_no,seq_nbr,dx_code) SELECT NEWID(),'1','366.16'GOINSERT INTO Table1 (seq_no,seq_nbr,dx_code)SELECT NEWID(),'3','370.03'GOINSERT INTO Table1 (seq_no,seq_nbr,dx_code) SELECT NEWID(),'4','362.52' GOINSERT INTO Table1 (seq_no,seq_nbr,dx_code) SELECT NEWID(),'4','362.52'GO INSERT INTO Table2 (dx_code1,dx_code2,dx_code3,dx_code4) VALUES('366.16','362.52','370.03','362.52') GO[/code]Pivot TABLE2 [code="sql"]CREATE TABLE #diag_codes(dx INT IDENTITY,sequence char(8),dx_code VARCHAR(6))INSERT INTO #diag_codes (sequence,dx_code)SELECT sequence,dx_codeFROM(SELECT dx_code1,dx_code2,dx_code3,dx_code4FROM Table2 ) dUNPIVOT(dx_code FOR sequence IN (dx_code1,dx_code2,dx_code3,dx_code4))AS unpvt_assessselect * from #diag_codesDROP TABLE #diag_codes[/code]//EditHit post too soon.I have the syntax above for creating the 2 tables and adding data for the test scenario.I tried some queries with the pivot of TABLE2 to get my numeric values updated, but was running into difficulty due to the duplicate dx_code values.

Running Total Query

Posted: 05 Jul 2013 01:28 AM PDT

Hi there people,I'm writting a T-SQL procedure to get a running total over a certain period, the data may have lapses in time between data rows, here is a example of the schema[code="sql"]CREATE TABLE #TranType1( value int, TranDate datetime)CREATE TABLE #TranType2( value int, TranDate datetime)CREATE TABLE #TranType3( value int, TranDate datetime)INSERT INTO #TranType1SELECT 10,'2013-06-14'UNIONSELECT 0,'2013-06-19'UNIONSELECT 0,'2013-06-24'INSERT INTO #TranType2SELECT 0,'2013-06-26'UNIONSELECT 5208,'2013-06-28'UNIONSELECT 10,'2013-06-29'UNIONSELECT 16005,'2013-07-01'INSERT INTO #TranType3SELECT 23135,'2013-07-02'UNIONSELECT 35070,'2013-07-03'UNIONSELECT 26509,'2013-07-04'UNIONSELECT 7000,'2013-07-05'[/code]And here is the procedure I wrote[code="sql"]DECLARE @DtBegin datetime,@DtEnd date,@dtDif intset @DtBegin = '5/1/2013'SET @DtEnd = GETDATE()set @dtDif = DATEDIFF(day,@DtBegin,@DtEnd);WITH ctePingInfo(sumValue,TranDate) AS ( SELECT SUM(SumValue),Trandate FROM( SELECT COALESCE(value,0),CAST(Trandate AS date) FROM #TranType1 UNION ALL SELECT COALESCE(value,0),CAST(Trandate AS date) FROM #TranType2 UNION ALL SELECT COALESCE(value,0),CAST(Trandate AS date) FROM #TranType3) ValInfo(SumValue,TranDate) WHERE Trandate BETWEEN @DtBegin AND @DtEnd GROUP BY TranDate ), E1(N) AS ( --=== 1x10^1 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --1x10^2 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --1x10^4 cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM E4), cteDateRange(dateRange) AS (SELECT TOP (@dtDif+1) dateadd(day,n,@DtBegin) FROM cteTally), cteResult(totValue,TranDate) AS ( SELECT COALESCE(cpi.Sumvalue,0),cdr.daterange FROM cteDateRange cdr LEFT JOIN ctePingInfo cpi ON cdr.daterange = cpi.TranDate )SELECT DISTINCT SUM(res2.totvalue) OVER (PARTITION BY res.TranDate),res.TranDate FROM cteResult res INNER JOIN cteResult res2 ON res.TranDate >= res2.TranDate ORDER BY res.TranDateDROP TABLE #TranType1DROP TABLE #TranType2DROP TABLE #TranType3[/code]It does return exatcly what I want, but given I believe this kind of request to be very common, I was wondering if there are any ways to speed it up, creating the physical tally table might not be an option, I believe there must be other topics on this very subject, but I'm not aware of what terms to look up for, any help is appreciated thanks !//EditPlease people nevermind, just found this link http://www.sqlservercentral.com/articles/T-SQL/68467/Don't know how that didn't come up on google, just had to make some minor changes

Fun task for the afternoon - grouping data

Posted: 05 Jul 2013 01:53 AM PDT

Hi,I am trying to create a table with a grouping based on a merged column. Best to explain with SQL[code="sql"]CREATE TABLE [dbo].[#Table_1]( [ID] [int] IDENTITY(1,1) NOT NULL, [Region] [varchar](50) NULL, [Prop1] [varchar](50) NULL, [Prop2] [varchar](50) NULL, [Prop3] [varchar](50) NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOinsert into #Table_1 values('ANZ', 'A', 'B', 'C'),('CAM', 'F', 'G', 'C'),('CAM', 'A', 'B', 'C'),('ME', 'H', 'V', 'N'),('SAM', 'A', 'B', 'C')select * from #Table_1--SOME QUERY HERE to turn the data into something like this:CREATE TABLE [dbo].[#Table_2]( [Region] [varchar](50) NULL, [Prop1] [varchar](50) NULL, [Prop2] [varchar](50) NULL, [Prop3] [varchar](50) NULL)insert into #Table_2 values('ANZ,CAM,SAM', 'A', 'B', 'C'),('CAM', 'F', 'G', 'C'),('ME', 'H', 'V', 'N')select * from #Table_2--drop Table #Table_1--drop Table #Table_2[/code]I am thinking that at somepoint I need to use the XML PATH trick to concatenate the fields but I am a bit lost as to how to do this with the above senario. Any help you can pass on would be very useful.Many Thanks,Oliver
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog