Friday, October 4, 2013

[T-SQL] Format Cast INT as Text

[T-SQL] Format Cast INT as Text

Format Cast INT as Text

Posted: 03 Oct 2013 09:15 AM PDT

I'm using CAST to conver integers to Varchar or Char, but when the value is less than 10 I want it to start with a 0, as in "07". But a 12 is still 12.What is the best way to do that?

Get previous value

Posted: 03 Oct 2013 09:43 PM PDT

Hi,I need get from a history table an specific previous value, any help will be welcome and it's appreciate beforehand, thanksIt treats about get those records with zonestate_id 'A' and his previous zonestate_id 'ARM' Samplesystem_no /event_date/ seqno/ event_id/ eventrpt_id/ zone_id/ alarminc_no/ zonestate_id100035370 2013-10-03 16:44:35.640 273769808 7441 O E441 NULL ARM 100035370 2013-10-03 16:44:35.663 273769809 C O O/C NULL ARM 100035370 2013-10-03 16:44:53.840 273769868 7130 R 11 143069012 A 100035370 2013-10-03 16:44:58.193 273769880 7130 R 11 143069012 A 100035370 2013-10-03 16:44:58.570 273769883 C O O/C 143069012 ARM 100035370 2013-10-03 16:45:05.137 273769907 7441 O E441 143069012 ARM 100035370 2013-10-03 16:45:05.183 273769908 C O O/C 143069012 ARM 100035370 2013-10-03 16:45:10.880 273769931 C O O/C 143069012 ARM 100035370 2013-10-03 16:45:12.920 273769935 7130 R 11 143069012 A 100035370 2013-10-03 16:45:12.970 273769936 7130 R 11 143069012 A 100035370 2013-10-03 16:45:19.430 273769944 7130 R 11 143069012 A 100035370 2013-10-03 16:45:30.990 273769973 7130 R 11 143069012 A 100035370 2013-10-03 16:45:31.320 273769978 7130 R 11 143069012 A 100035370 2013-10-03 16:45:33.843 273769989 7130 R 11 143069012 A 100035370 2013-10-03 16:45:50.100 273770058 7130 R 11 143069012 A 100035370 2013-10-03 16:46:09.147 273770111 7130 R 11 143069012 A 100035370 2013-10-03 16:46:09.643 273770118 7130 R 11 143069012 A300010596 2013-10-03 16:16:16.240 273763678 CLMAIL O O/C NULL ARM 300010596 2013-10-03 16:16:18.363 273763681 7131 R 11 143067032 A 300010596 2013-10-03 16:16:20.700 273763700 7131 R 11 143067032 A 300010596 2013-10-03 16:16:20.760 273763701 7131 R 11 143067032 A 300010596 2013-10-03 16:16:25.560 273763721 7131 R 11 143067032 A So for the system_no 100035370 we should get100035370 2013-10-03 16:44:35.663 273769809 C O O/C NULL ARM 100035370 2013-10-03 16:44:53.840 273769868 7130 R 11 143069012 A 100035370 2013-10-03 16:45:10.880 273769931 C O O/C 143069012 ARM 100035370 2013-10-03 16:45:12.920 273769935 7130 R 11 143069012 A For system_no 300010596 we should get300010596 2013-10-03 16:16:16.240 273763678 CLMAIL O O/C NULL ARM 300010596 2013-10-03 16:16:18.363 273763681 7131 R 11 143067032 A Sample table and valuesCREATE TABLE [event_history]( [system_no] [int] NOT NULL, [event_date] [datetime] NOT NULL, [seqno] [numeric](9, 0) IDENTITY(1,1) NOT NULL, [event_id] [char](6) NULL, [eventrpt_id] [char](2) NULL, [zone_id] [char](6) NULL, [alarminc_no] [decimal](18, 0) NULL, [zonestate_id] [char](4) NULL,)INSERT INTO [event_history]([system_no], [event_date], [seqno], [event_id], [eventrpt_id], [zone_id], [alarminc_no], [zonestate_id])Values('100035370', '2013-10-03 16:44:35.640', '273769808', '7441', 'O', 'E441',NULL, 'ARM') Values('100035370', '2013-10-03 16:44:35.663', '273769809', 'C', 'O', 'O/C',NULL, 'ARM') Values('100035370','2013-10-03 16:44:53.840', '273769868', '7130', 'R', '11','143069012', 'A') Values('100035370', '2013-10-03 16:44:58.193', '273769880', '7130', 'R', '11', '143069012', 'A') Values('100035370', '2013-10-03 16:44:58.570', '273769883', 'C', 'O', 'O/C', '143069012', 'ARM') Values ('100035370', '2013-10-03 16:45:05.137', '273769907', '7441', 'O', 'E441', '143069012', 'ARM') Values('100035370', '2013-10-03 16:45:05.183', '273769908', 'C', 'O', 'O/C', '143069012', 'ARM') Values('100035370', '2013-10-03 16:45:10.880', '273769931', 'C', 'O', ' O/C', '143069012', 'ARM') Values('100035370', '2013-10-03 16:45:12.920', '273769935', '7130', 'R', '11', '143069012', 'A') Values('100035370', '2013-10-03 16:45:12.970', '273769936', '7130', 'R', '11', '143069012', 'A') Values('100035370', '2013-10-03 16:45:19.430', '273769944', '7130', 'R', '11', '143069012', 'A') Values('100035370', '2013-10-03 16:45:30.990', '273769973', '7130', 'R', '11', '143069012', 'A') Values('100035370', '2013-10-03 16:45:31.320', '273769978', '7130', 'R', '11', '143069012', 'A') Values('100035370', '2013-10-03 16:45:33.843', '273769989', '7130', 'R', '11', '143069012', 'A') Values('100035370', '2013-10-03 16:45:50.100', '273770058', '7130', 'R', '11', '143069012', 'A') Values('100035370', '2013-10-03 16:46:09.147', '273770111', '7130', 'R', '11', '143069012', 'A') Values('100035370', '2013-10-03 16:46:09.643', '273770118', '7130', 'R', '11', '143069012', 'A')Values('300010596', '2013-10-03 16:16:16.240', '273763678', 'CLMAIL', 'O', 'O/C', NULL, 'ARM') Values('300010596', '2013-10-03 16:16:18.363', '273763681', '7131', 'R', '11', '143067032', 'A') Values('300010596', '2013-10-03 16:16:20.700', '273763700', '7131', 'R', '11', '143067032', 'A') Values('300010596', '2013-10-03 16:16:20.760', '273763701', '7131', 'R', '11', '143067032', 'A') Values('300010596', 2013-10-03 16:16:25.560', '273763721', ' 7131', ' R', '11', '143067032', 'A')

Help building query, please

Posted: 03 Oct 2013 02:04 PM PDT

I have a table that contains times of server backups. There are days where nothing happens so I join this table to a calendar table to create placeholders for those dates. This results in additional rows populated with a date that was missing in the serverbackup table, but having NULL values for the remaining columns. Since I have more than one server in the table, I need a date placeholder for each server that hasn't had a backup on any given day.I don't know how to do this. I've created DDL below. Please read on.--server backup tableCREATE TABLE [dbo].[CannedBackupJobs]( [jobid] [int] NULL, [SizeTB] [float] NULL, [StartTime] [datetime] NULL, [ServerName] [varchar](20) NULL) --server backup data-- As you can see, since neither Peaches nor Pears was backed up on 2013-08-05 through 2013-08-13. insert into [dbo].[CannedBackupJobs]values(83, 365.226943141887,'2013-08-04 03:20:30.777', 'Peaches'),(83, 408.830221699759, '2013-08-14 18:26:53.220', 'Peaches'),(83, 391.654500133873, '2013-08-15 15:44:34.977', 'Peaches'),(83, 397.063717616127, '2013-08-20 02:10:57.747', 'Peaches'),(83, 353.803773579467, '2013-08-24 05:56:26.090', 'Peaches'),(100, 533.226943141887,'2013-08-07 03:20:30.777', 'Pears'),(100, 788.830221699759, '2013-08-09 18:26:53.220', 'Pears'),(100, 351.654500133873, '2013-09-07 15:44:34.977', 'Pears'),(100, 347.063717616127, '2013-09-09 02:10:57.747', 'Pears'),(100, 663.803773579467, '2013-10-09 05:56:26.090', 'Pears');--calendar look up tableCREATE TABLE _Dates ( d DATE, PRIMARY KEY (d))DECLARE @dIncr DATE = '2000-01-01'DECLARE @dEnd DATE = '2100-01-01'WHILE ( @dIncr < @dEnd )BEGIN INSERT INTO _Dates (d) VALUES( @dIncr ) SELECT @dIncr = DATEADD(DAY, 1, @dIncr )END Here's join. It only produces one placeholder for missing dates.SELECT d, StartTime, ServerName, SizeTBFROM dbo._dates dINNER JOIN ( SELECT DATEADD(DAY, DATEDIFF(DAY, 0, MIN(starttime)), 0) AS start_date, DATEADD(DAY, DATEDIFF(DAY, 0, MAX(starttime)) + 1, 0) AS end_date FROM dbo.cannedbackupjobs) AS date_range ON d.d >= date_range.start_date AND d.d < date_range.end_dateLEFT OUTER JOIN cannedbackupjobs dbj ON dbj.starttime >= d.d AND dbj.starttime < DATEADD(DAY, 1, d.d)Desired output to look like this. Notice on 2013-08-09, there is only one placeholder date for Peaches, none for Pears because Pears had a backup.. But elsewhere two placeholder rows for same date, for times when neither backed up.: SELECT '2013-08-04','2013-08-04 03:20:30.777', 'Peaches', 365.226943141887 UNION ALL SELECT '2013-08-05', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-05', NULL, 'Pears',NULL UNION ALL SELECT '2013-08-06', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-06', NULL, 'Pears',NULL UNION ALL SELECT '2013-08-07', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-07', NULL, 'Pears',NULL UNION ALL SELECT '2013-08-08', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-08', NULL, 'Pears',NULL UNION ALL SELECT '2013-08-09', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-09','2013-08-09 18:26:53.220', 'Pears', 788.830221699759 UNION ALL SELECT '2013-08-10', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-10', NULL, 'Pears',NULL UNION ALL SELECT '2013-08-11', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-11', NULL, 'Pears',NULL UNION ALL SELECT '2013-08-12', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-12', NULL, 'Pears',NULL UNION ALL SELECT '2013-08-13', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-13', NULL, 'Pears',NULL UNION ALL SELECT '2013-08-14', NULL, 'Peaches',NULL UNION ALL SELECT '2013-08-14', NULL, 'Pears',NULL Do I do this with a bunch of seperate select statements (with WHERE ServerName = Peaches and with WHERE ServerName = Pears) run against the _Dates table with a UNION ALL to recombine them? Or is there a better way?

Displaying column names

Posted: 03 Oct 2013 11:04 PM PDT

HiI have a requirement where I need to show a column name in a SSRS report in the following format:List of <Hazards.hazardCode> separated by ", "<MaterialsItems.otherHazards>Here, Hazards and MaterialsItems are table names and hazardCode and otherHazards are column names.How to display this? Pls help me on this.

openrowset question

Posted: 03 Oct 2013 04:46 AM PDT

I would like to know why I can't run openrowset the way that system stored procedures do? For example, in sys.xp_logininfo, they use it. When I try to copy the part of the code from that SP for use in my code, it gives me the error:Msg 156, Level 15, State 17, Line 16Incorrect syntax near the keyword 'OPENROWSET'.I just don't understand how it can work in the system SP, but I can't run the part of the code I want to use... please help.

Building a Comma Separated on a new line

Posted: 03 Oct 2013 01:53 PM PDT

Hi All,I need some help! firstly to tweak my results to work as I expect it to & secondly provide an alternate method if my logic is not best practice.I'm returning a list of PDF's (very minimal results < 20) into a temporary table and then running the following query to build these into a string. (E.G. 1.pdf,2.pdf,3.pdf ... 20.pdf)[code="sql"]DECLARE @PDFFileName VARCHAR(8000) = ( SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf' FROM @PDFResult ORDER BY 1 ASC FOR XML PATH('')),1,1,'') ) [/code]My aim is to split these up 1 per line instead of a massive line (as below)1.pdf,2.pdf...20.pdfAny ideas?

Inserting auto increment values through Identity

Posted: 03 Oct 2013 05:25 PM PDT

Hi I created a table CREATE TABLE IdentityProducts(productid int IDENTITY(1,1) NOT NULL,productname nvarchar(40) NOT NULL,categoryid int NOT NULL,unitprice money NOT NULL) I want to insert values into it with the belkow statementinsert into IdentityProducts(productname,categoryid,unitprice)values('shad',1,100) when i insert the "productid" should get inserted automatically as it is defined with identity propertbut its not happeningeven though i have madeSET IDENTITY_INSERT IdentityProducts ON I am getting the following error============Msg 545, Level 16, State 1, Line 1Explicit value must be specified for identity column in table 'IdentityProducts' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.==============:crying:

Paramater Concatenation

Posted: 03 Oct 2013 07:43 AM PDT

Hi Everyone,DECLARE @CN VARCHAR (100)SET @CN = 'abc pvt Ltd'select * from [@CN'+'$Production]My output should be select * from [abc pvt Ltd$Production].I DO NOT want to use a string for my Select QueryCould anyone of you help on me this.

Advice on Agent job step

Posted: 03 Oct 2013 03:44 AM PDT

He everyone.I have a requirement to move a .xlsx file from a local location to a sharepoint site.I looked around I saw advice that suggested mappping Sharepoint site as a mapped drive location, which I have done.The line below works fine when exectuted from cmd prompt. It moves the file to Sharepoint location.It fails when I try to run it as a SQL agent job when Job type is Operating System (CmdExec)XCopy "C:\SQL\PowerShell\Failedjobs.xlsx" J:\ /yWould anyone be able to advise me what I have done wrong in the script in order for it to be able to be run as a SQL agent job ?Do I need the full Sharepoint URL, have tried many combinations but with no luck.Error I get is : Invalid drive specification 0 File(s) copied. Process Exit Code 4. The step failed.So I have incorrectly named the location it needs to go to, but do not know what to do to correct it ?Thanks for any advice.

Best method to append columns based on similar ID

Posted: 03 Oct 2013 02:52 AM PDT

Hi, sorry about long thread but here we go, so i have been trying to figure this piece out as well as search forms and i find similar questions but not the exact one. The issue is that i have a raw data table which extracts from a legacy application, the raw data comes as followscar_id: 1car_name: vwcustomer_comment: oil changedealer_comment: completeresponse_line: 1car_id:1car_name: vwcustomer_comment: brakes changedealer_comment: NULLresponse_line: 2note that this response_line field is breaking up a "text area" by line number (yes that legacy application can't just extract the text area it as a whole). i'm tying to append the dealer_comment into 1 field so i can update a staging table and clean things up...the perfect output would becar_id:1car_name: vwcustomer_comment: oil change brakes changedealer_comment: complete here is my code which works (in two different ways) but what happens is the code doesn't include any records that exactly match the number response_lines (there can be a max of 3), example if one record has 3 response_lines (3 lines filled in the text area) this works and another record has 2 response_line (only 2 line filled in the text area) this code would not include the 2 response_line record;with temp (num1,t1)as(select car_id, dealer_commentfrom cars where Response_Line ='1' and cars.query='dealer_comment'),temp2 (num2,t2)as(select car_id, dealer_commentfrom carswhere Response_Line ='2' and cars.query='dealer_comment'),temp3 (num3,t3)as(select car_id,dealer_commentfrom carswhere Response_Line ='3' and cars.query='dealer_comment')select t1+' '+t2+' '+t3 from temp inner join temp2on temp.num1=temp2.num2*shows all records with only 3 response lines* i was hoping to show all recordsor update staging.cars set dealer_comments= (select dealer_comment from cars where Response_Line = '1' and cars.query='dealer_comment' and staging.cars.car_id = cars.car_id ) + ' ' + (select Response_Text from cars where Response_Line = '2' and cars.query='dealer_comment' and staging.cars.car_id = cars.car_id ) + ' ' + (select Response_Text from cars where Response_Line = '3' and cars.query='dealer_comment' and staging.cars.car_id = cars.car_id )*updates records with only 3 response linesmy plan would be to update based on either method, either CTE tables or a update and i would certainly understand i need to incorporate a case when else but i can't' seem to figure that piece out. i hope this all makes sense and any advice would be appreciated
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog