Saturday, June 29, 2013

[T-SQL] Query Help

[T-SQL] Query Help

Query Help

Posted: 28 Jun 2013 09:11 PM PDT

Hi,I need help in getting this type of output, I tired with MIN and MAX function but didnt get required output:Create table T1(Id int identity primary key,VoucherNo varchar(10),TransNo varchar(10))Insert into values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1')Resultant output:TransNo FirsvoucherNo LastVoucherNo Quantitytrns1 V100 V104 5trns1 V106 V106 1

Group by - Retaining all serial numbers

Posted: 28 Jun 2013 11:12 PM PDT

Hi,I am trying to group a dataset by the variable Sale_Type and retain the serial numbers along:Here is the data:Table1:Sno Sale_Type Amount1 Drug 102 Fruit 203 Groceries 304 Drug 105 Fruit 206 Groceries 307 Drug 108 Fruit 209 Groceries 3010 Drug 10Here is the result, I am trying to achieve:Sale_Type Amount Sno_RetainedDrug 40 1, 4, 7, 10Fruit 60 2, 5, 8Groceries 90 3, 6, 9Here is my code so far:[code="sql"]select Saletype, sum(Amount) from table1 group by sale_type[/code]Any help would be truly appreciable.Thanks & Regards,Akber Khan.

Insert Multiple rows using Transacrtions

Posted: 28 Jun 2013 02:41 PM PDT

Hi.. I have a table and need insert multiple data into it. First i have delete all data into the table and then insert multiple rows in table using sql transactions so that if any error comes all data will not be deleted. Thanks and RegardsSankar Cochin

disable Job

Posted: 28 Jun 2013 03:47 AM PDT

How to disable a job by using t-sql query???Please help...

sql query questio

Posted: 28 Jun 2013 10:56 AM PDT

I have 2 tablesCaseCaseID Name-------------------12341 XYZ23451 ZZZ90892 XXQCaseCodeCaseID CaseCode TypeFlag--------------------------------12341 001 P12341 003 S90892 111 S90892 222 S90982 999 PHere in TypeFlag 'P' stands for PrimaryCode and 'S' for secondary codeI want an output like thisCaseID Name PrimaryCode SecondaryCode-----------------------------------------------------------------12341 XYZ 001 00323451 ZZZ NULL NULL90982 XXQ 999 111,222I tried joining the two tables using CASE WHEN, but it generates multiple rows for a case which has more than 1 code.Also i need comma seperated codes when there are multiple Type Flag for a caseID as shown in the last record.Please helpthankskk

Dynamic Vertical Rows to Horizontal

Posted: 28 Jun 2013 09:38 AM PDT

I read through the following topic already, and it has moved me MUCH closer to the solution that I'm seeking than I was getting otherwise, but I can't seem to find the answer to my questionStarting Point: [url=http://www.sqlservercentral.com/Forums/Topic1274906-392-1.aspx]Vertical to Horizontal[/url]My situation is somewhat similar in that I have a unique identifier than can have, at a max, 60 rows tied to it - however, there are often quite a few less, but knowing the upper boundary is useful, I think.I'm trying to figure out an easier way than taking the following block of code and having to copy-paste 60 times in order to account for the maximum number of rows I'm trying to "pivot" out:[code="sql"]SELECT MK, HK, CK, MAX(CASE WHEN N=1 THEN MD ELSE NULL END) AS MD1, MAX(CASE WHEN N=1 THEN BN ELSE NULL END) AS BN1, MAX(CASE WHEN N=2 THEN MD ELSE NULL END) AS MD2, MAX(CASE WHEN N=2 THEN BN ELSE NULL END) AS BN2, MAX(CASE WHEN N=3 THEN MD ELSE NULL END) AS MD3, MAX(CASE WHEN N=3 THEN BN ELSE NULL END) AS BN3FROM TMP_URECROSS APPLY (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) X(N)WHERE RN=NGROUP BY MK, HK, CKORDER BY MK, HK, CK[/code]And perhaps copy-pasting 60 times really is the best way, but it would seem that I should be able establish a variable that has a range of 1 - 60, and then loop the above statement for every value between 1 and 60, but I'm not sure, and more to the point, I'm not sure how to do so.Am I just barking up the wrong tree?

Need some help in solving the below logic of averages

Posted: 28 Jun 2013 04:27 AM PDT

I have a requirement to work with the below 2 tables and get the last table as my output. Any help would be great.Table 1 : [code="sql"]Type Code Current BPO ForecastNULL NULL NULL NULL NULL NULL NULL NULL NULLNULL NULL NULL NULL NULL NULL NULLA NULL NULL NULL NULLA NULL NULL NULLA A001 55 32 56.322A A002 NULL NULL NULLA A003 66.22 44.21 81.13A A004 NULL NULL NULLA B001 NULL NULL NULLA B002 NULL NULL NULLR A001 28.423 53.245 82.444R A003 100 100 100R A004 NULL NULL NULLR A023 NULL NULL NULLR C001 89.3444 52.432 100S A001 22.22 18.322 65S C001 67 34 62S NULL NULL NULLZ A003 12 22.56 38.43Z B001 56 43.233 81.69Z A023 NULL NULL NULL [/code] Table 2 : [code="sql"]Index Type CodeBact A A001Bact R A001Bact Z B001Bact S C001Dis1 A A001Dis1 A A002Dis1 A A003Dis1 R C001Dis1 Z A023MEX A A004MEX A B001MEX R A001MEX S A001MEX A B002TED R A003TED R A004TED S A001TED Z A003ZES R A023ZES Z A003ZES Z A023ZES Z B001 [/code]I need the below columns as my output :[code="sql"] Index Current BPO Forecast Bact (Avg of A A001,A B001,A C001,R A001,R B001,R C001,S A001,S B001,S C001,Z A001,Z B001,Z C001) Dis1 MEX TED ZES[/code]Here Current , BPO and Forecast should be average of the combination of Type and Code for a particular Index.We are also eliminating all the rows which do not match with the Table 2. I am also putting Null values in Table 1 combination as these were the actual values in my tableAlso, Please note that my Table1 is not a table but an output of some other query formed using some logic.Also below are my scripts : Table 1 Create and Insert Scripts :-[code="sql"] CREATE TABLE [dbo].[Table1]( [Type] [char](1) NULL, [Code] [varchar](5) NULL, [Current] [decimal](18, 8) NULL, [BPO] [decimal](18, 8) NULL, [Forecast] [decimal](18, 8) NULL ) ON [PRIMARY] INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES(NULL,NULL,NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('',NULL,NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES(NULL,'',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('','',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A',NULL,NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',55.00000000,32.00000000,56.32200000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',NULL,NULL,NULL)[/code]Table 2 Create and Insert Scripts :- [code="sql"] INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Bact','A','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Bact','R','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Bact','Z','B001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Bact','S','C001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','A','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','A','A002') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','A','A003') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','R','C001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','Z','A023') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','A','A004') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','A','B001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','R','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','S','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','A','B002') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('TED','R','A003') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('TED','R','A004') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('TED','S','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('TED','Z','A003') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('ZES','R','A023') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('ZES','Z','A003') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('ZES','Z','A023') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('ZES','Z','B001')[/code]

How to read all content of sp or fn?

Posted: 28 Jun 2013 02:33 AM PDT

Currently I am using the following code to read all content of sp or fn and it works in most cases, however, it does return more than one rows if the content's size is too large, is there better to get this? Thank you.[code="sql"]SELECT textFROM syscommentsWHERE id = (SELECT id FROM sysobjects WHERE name = 'spname')ORDER BY colid [/code]

query syntax problem

Posted: 28 Jun 2013 01:19 AM PDT

I have a query that is working successfully, just wanting to add a third table to it and trying several things but not getting the results needed. Here is the starting query and a description for the third table. Any ideas? Thanks!!SELECT k.AttributeKeyID, a.AttributeKeyID, k.AttributeKeyDsc, a.WorkItemID, a.KeyValueTxtFROM WorkItemAttributeKey AS k INNER JOINWorkItemAttribute AS a ON k.AttributeKeyID = a.AttributeKeyID WHERE (a.WorkItemID = 107893)SELECT c.WorkItemID, c.CommentTxtFROM WorkItemComment As cWHERE (c.WorkItemID = 107893)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog