Thursday, August 8, 2013

[T-SQL] Pivot columns

[T-SQL] Pivot columns

Pivot columns

Posted: 07 Aug 2013 08:46 AM PDT

I have a table structure as ID Name1 A2 B3 C4 B5 E6 F7 G8 H9 I10 J11 K12 L13 M14 N15 O16 P17 Q18 R19 S20 T and so on...Can anyone help me to get the results either for adding Static columsn around 6 to 7 or Dynamic columns which can accomdate the number of records in the table by count 6 as mentioned below.ID COL1 COL2 COL3 COL4 COL5 COL61 A G M S Y AE2 B H N T Z AF3 C I O U AA AG4 D J P V AB AG5 E K Q W AC AI6 F L R X AD AJThanks in AdvanceSrikanth Reddy

Updating target table with source data

Posted: 07 Aug 2013 12:09 PM PDT

It's almost 2AM here so obviously I'm not thinking very well at the moment.I have a source (orange border) and target table (green border)[img]http://i.imgur.com/VEbT4jZ.jpg[/img]What I need to do is add the records from the source table into the target table but the to_date in the target table needs to be updated with the from_date - 1 from the source table. I just can't figure out how to do this.If anyone can offer suggestions I would be most grateful.Sample data below:[code="sql"]create table source_table (Uarn bigint, Asst_ref bigint, VO_Ref bigint, total_value int, from_date datetime, to_Date datetime)insert into source_table (Uarn, Asst_ref, VO_Ref, total_value, from_date, to_Date)select 6705290000, 14673893000, 22898762212, 3475, '2013-07-22', NULL UNION ALLselect 6705290000, 14673284000, 22573281212, 2893, '2013-07-19', '2013-07-21' union allselect 100381173, 14664965000, 22815438212, 17607, '2013-07-18', NULLcreate table target_table (Uarn bigint, Asst_ref bigint, VO_Ref bigint, total_value int, from_date datetime, to_Date datetime)insert into target_table (Uarn, Asst_ref, VO_Ref, total_value, from_date, to_Date)select 6705290000, 11045507000, 15243111182, 3319, '2010-04-01', NULL union allselect 100381173, 14528777000, 22500073212, 16727, '2013-04-05', NULL union allselect 100381173, 9245349000, 11945621182, 17607, '2010-04-01', '2013-04-04'[/code]

Query Help - To delete old files

Posted: 07 Aug 2013 11:27 PM PDT

Hi All,Looking for assistance to develop T-SQL codeRequirement is to delete old files from directory & its sub-directories(say F:\Temp\test\), which were placed in directory before 1 week and not modified.The files can be of any type :txt,bak,zip,doc,bak,mdf,ldfThanks in advance for any help.

Query help - summing between dates ranges and calculations...

Posted: 07 Aug 2013 08:58 PM PDT

Hi all,Looking for assistance in a SQL query to use in a report.Finance people need to show "price variance" to our group owners and are currently spending a lot of time in Excel. I'm thinking/hoping I can get the data via a [b]single SQL query[/b].I can get the data out of only three tables -Customers, Invoices and Invoiceitems - but it's the grouping and summing I'm not sure how to do.The output would be:Customer Number. From "Customers" Customer Name. From "Customers" and will join to "Invoices"Part Number. From "Invoiceditems" - which can join to "Invoices" - as is all of the data below…"Last year's Price." The sum of the qty * price within a date range"This year's price" The sum of the qty * price within a different date rangeQty shipped last year. Total qty of the part shipped grouped by customer within a date range Qty shipped this year. Total qty of the part shipped grouped by customer within a date range "A value for last year." Last year's price / by total qty within a date range "A Value for this year." This year's price / by total qty within a date rangeExample Output.Last year customer "A" bought part "ABC" 100 times on ten different orders and paid two different prices.This year customer "A" bought part "ABC" 75 times on 20 different orders and paid four different prices.I need it for ALL customers though. A nine column output for maybe a thousand customers and around 500 separate part numbers.Now, as I think about this and I have already discussed with colleagues, I see that the questions(s) are, er, not the ones necessarily I would want to ask. However, he who pays the piper, etc.Apologies in advance if I've put this in the wrong place and/or gone into too much detail.

Find number in String and Return INT

Posted: 07 Aug 2013 05:33 PM PDT

Hi All,I have a series of strings in a variable @buyer3, RRRRR4, SSSSS5, DDDDDDDDDDD6, AAAA7, PPPPPP8, UUUUUU9, MMMMMM10, LLLLLL11, IIIII12, EEEEEand I only want to sub string the number then convert it to an INT, so i can use it in a where clause.[code="sql"]select *from companywhere company_id = CAST(SUBSTRING(@buyer,1,LEN(@buyer) - CHARINDEX(',',@buyer,0)) AS INT)order by company_id[/code]substring (@buyer, start at position 1,find length of @buyer minus the index position of the comma in @buyer) then cast int. but it doesn't work. say conversion failed when converting value 3,RRRRR.I think it should be pretty simple, but I can't see it.Thanks for any help.

Query exchange 2013 calendars from SQL 2008 R2

Posted: 07 Aug 2013 10:32 AM PDT

Hi,I have been asked to report on exchange 2013 calendars from within SQL server.The exchange 2013 server is a different server to the SQL server, but in the same domain.I've had a look at some older technology using ExOLEDB, but that appears to require that the exchange and SQL instances reside on the same server.Is there anyone who knows what I need to do in order to be able to set up maybe a linked server (or equivalent) that will enable me to query exchange calendars from SQL?Many thanks,Craig

Help with the query and index.

Posted: 07 Aug 2013 02:03 AM PDT

I've a table which has about 15 columns, I'll just put 3 columns here in the script that we would be using in the script.CREATE TABLE "schema"."tblTest"( "TestId" uniqueidentifier NOT NULL , "StartDateTime" datetime2 NOT NULL , "EndDateTime" datetime2 NULL )TestId is PK with a clustered index. Also, there is 1 nonclustered index on StartDateTime. No index is present on Enddatetime.The query I need to rewrite\optimise is like this:SELECT TOP (500) test.[TestId]FROM tblTest as testWHERE COALESCE(test.EndDateTime, test.StartDateTime) < @Date;The table will have about 1 million records, and about 1/16th (about 6-7%) of that would be part of the output at any given time. Right now this query does clustered index scan. I would like it to do seeks instead.Solutions I tried:1) Add a computed column with definition: (case when EndDateTime is not null then EndDateTime Else StartDateTime End) and have an index on this. This works perfect but I cannot use this because we use microsoft sync framework that does not like computed columns.2) Indexed view with computed column. Again, we cannot use this as we need to support SQLExpress.3) Completely re-write the query by splitting it into 2 queries and union the 2 results. Create 2 separate indexes on enddatetime and startdatetime. This seems little overkill maybe. ;with cte as (SELECT test.[TestId] FROM schema.tblTest as test WHERE test.EndDateTime < @Date union all SELECT test.[TestId] FROM schema.tblTest as test WHERE test.EndDateTime is null and test.StartDateTime < @Date) select top 500 testid from cteAny help on finding a better solution would be appreciated.Thanks in advance.

Logic Behind Try . . . Catch

Posted: 07 Aug 2013 06:18 AM PDT

Hello All,I have a question behind the logic of Try and Catch. I'm using Try Catch in a complex statement with a While Loop. If I put the Try . . . Catch outside of the while loop and say the loop fails the 3rd time around, will the entire statement be caught and no transaction committed? By this I mean will the previous two loops be negated? I'm assuming that this is the case.Thanks again!

Parse data Stored in a varchar Column as XML

Posted: 07 Aug 2013 05:50 AM PDT

AllI have a column that stores data in a varchar column. The data is easily converted to xml but it has a namespace embedded within the data . I used the value method but I keep geting null for the results for the column when I try to extract the account number. I've tried the namespace option as well.Data Example:<p322:ViewStatement xmlns:p322="http://proxy.ws.ejb.mhg.com"><p322:XMLRequest><?xml version="1.0" encoding="utf-8" ?><Request><AccountNumber>123456789</AccountNumber><InternetScreenName>mhg.com.Statements</InternetScreenName><StatementDate>05/20/2013</StatementDate></Request></p322:XMLRequest></p322:ViewStatement>SQL ExampleSELECT TOP 1000 [RequestID] ,cast([Request]as xml) as test ,cast( [Request] as xml).value('(Request/@AccountNumber)[1]', 'Varchar(9)') as AccountNumber FROM [log]Any Suggestions?

delete rows

Posted: 07 Aug 2013 06:24 AM PDT

hihere is my queryCREATE TABLE [dbo].[CustomCode]([ID] [int] NULL,[pcondition] [varchar](50) NULL,[Pinten] [int] NULL,[scondition] [varchar](50) NULL,[sinten] [int] NULL) ON [PRIMARY]insert into dbo.CustomCodeselect 1,'hf',1,NULL,NULLUNION ALLSELECT 1,'CAD',1,NULL,NULLUNION ALLSELECT 2,'CA',1,null,nullunion ALLselect 2,'ast',1,null,nullunion ALL SELECT 3,'HF',2 ,null,nullunion ALL SELECT 3,'HF',2,NULL,NULLunion ALL select 3,'HF',2,NULL,NULLUNION ALL select 3,'HF',2,NULL,NULLunion ALL SELECT 5 ,'hf',3 ,null,nullunion ALL SELECT 5, null ,null, 'CA',2UNION ALL SELECT 5, 'HF',2,NULL,NULLUNION ALL SELECT 5,NULL,NULL, 'CAD',2union ALL select 5,'HF',2,NULL,NULLunion all select 6,'AF',3,NULL,NULLunion all select 6,'AST',3,NULL,NULLUNION ALL select 6,'AST',3,NULL,NULLunion all select 7, 'AF',1,NULL,NULLunion all select 7,'AF',1,NULL,NULLunion all select 7, 'AST',1,NULL,NULLAfter executing store porc as belowcreate procedure [dbo].[customecode]asbegincreate table #SortSomething(pcondition varchar(3),SortOrder int)insert #SortSomething(SortOrder, pcondition)select 1, 'AF' union allselect 2, 'CA' union allselect 3, 'CAD' union allselect 4, 'AST' union allselect 5, 'hf';with duplicateprimarycondition as(select cc.*, s.SortOrder, ROW_NUMBER() over(partition by ID, cc.pcondition,cc.pinten order by s.SortOrder) as RowNumfrom CustomCode ccINNER join #SortSomething s on cc.pcondition = s.pcondition)select * from duplicateprimarycondition endresult will be ID pconditionPinten sconditionsinten SortOrderRowNum1 CAD 1NULL NULL3 11 hf 1NULL NULL5 12 ast 1NULL NULL4 12 CA 1NULL NULL2 13 HF 2NULL NULL5 13 HF 2NULL NULL5 23 HF 2NULL NULL5 33 HF 2NULL NULL5 45 HF 2NULL NULL5 15 HF 2NULL NULL5 25 hf 3NULL NULL5 16 AF 3NULL NULL1 16 AST 3NULL NULL4 16 AST 3NULL NULL4 27 AF 1NULL NULL1 17 AF 1NULL NULL1 27 AST 1NULL NULL4 1i need to modify my stored proc so that if for that particular ID, for that particular pcondition and pinten if sorder order comes 1 as two time,it should delete 1 row,example, here7 AF 1 NULL NULL 1 17 AF 1 NULL NULL 1 2it should delete 1 row as two times 1(sortorder) is coming , one row should remain in table out of twoI want delete statement after my statementplz help

How to made a stored procedure for already date exists ?

Posted: 07 Aug 2013 02:00 AM PDT

Hai friends ,i ve the table like travel_requestcreate table travel_request( request_id int identity, user_id varchar(20), travel_purpose varchar(20))insert into travel_request (user_id,travel_purpose) values ('012','Market Visit')journey create table journey (request_id int references travel_request(request_id),departuredate datetime,from varchar(20),to varchar(20))now my requirement is when user enter the date if it is table ll show already exists........how to make a code?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog