Wednesday, July 3, 2013

[T-SQL] Get Employee Managers Manger Query

[T-SQL] Get Employee Managers Manger Query

Get Employee Managers Manger Query

Posted: 03 Jul 2013 12:36 AM PDT

Hi I want to take employee, Manager and the managers' manger from employee table. I've tried the below query its working if i have the three values (emp, manager1, manager2).But f i have only two levels (emp and manger only) then its not working.[code="sql"]CREATE TABLE [#Employee]( [EmpID] [int] NOT NULL, [EmpName] [varchar](50) NOT NULL, [SupID] [int] NULL)GOINSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)--INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)select e1.empname employee,m1.empname Manager1, m2.empname Manager2 from[#Employee] e1, [#Employee] m1, [#Employee] m2where m1.EmpID = e1.SupID and m2.EmpID = m1.SupID[/code]Can any one help me here?

Delta of Disk IO

Posted: 02 Jul 2013 11:55 PM PDT

Hi all - I am trying to work out some deltas from information I collect every 5mins from the sys.dm_io_virtual_file_stats DMV. I am currently struggling to find a way to minus one value from the other for each row in the table.I am trying to do the following to get this to work however I have syntax problems in the CASE statement and also issues when trying to + 1 to the ROW total.All help appreciated, apologies if this is not clear.[code="sql"]WITH IOPS ([IO_STALL] ,[IO_STALL_READ_MS] ,[IO_STALL_WRITE_MS] ,[NUM_OF_READS] ,[NUM_OF_WRITES] ,[SIZE_ON_DISK_MB] ,[DBNAME] ,[NAME] ,[FILE_ID] ,[DB_FILE_TYPE] ,[FILE_LOCATION] ,[TIMESTAMP] ,[ROW])AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]FROM dbo.DISKIOPS)SELECT CASE WHEN ROW = 1 THEN 0 ELSE ((SELECT IO_STALL FROM IOPS WHERE ROW = (ROW+1)) - IO_STALL) END AS IO_STALL, FILE_LOCATIONFROM IOPS[/code]

SQL statement that will perform a daily sum aggregation my last chance

Posted: 02 Jul 2013 07:05 PM PDT

a SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from division 1. Below is the example table that you will be retrieving data from:Costs• Receipt• Date• Item• Reason• Division• CostI have tried this :use STEVENTESTgoCREATE FUNCTION Daily_Cost1( @item varchar , @Division nchar )RETURNS money ASBEGIN DECLARE @daily_cost money DECLARE @Cost_date date set @Cost_date = GETDATE () if @item = 'Z001' OR @item ='Z002'SELECT @daily_cost = SUM(cost) from Costwhere @Cost_date =@Cost_date RETURN @daily_cost ENDGO

sql sales

Posted: 02 Jul 2013 07:08 PM PDT

Write a SQL statement that will return all the Sales Orders for the Salespersons' with the name starting with 'John'. [b]i have tried this :[/b] CREATE VIEW [dbo].[SalesOrder]ASSELECT dbo.Trnasction.Salesorder, dbo.SalesPerson.SalesPersonNameFROM dbo.Trnasction CROSS JOIN dbo.SalesPersonWHERE (dbo.SalesPerson.SalesPersonName = N'John')

Information displaying incorrectly.....

Posted: 02 Jul 2013 06:11 PM PDT

Hi,I have 2 tables:[code="sql"]CREATE TABLE [dbo].[GV_InwardAtStore]( [InwardAtStoreID] [int] IDENTITY(1,1) NOT NULL, [StoreCode] [int] NULL, [STNNo] [varchar](20) NULL, [GRNNo] [varchar](10) NULL, [VoucherBookletNo] [varchar](10) NULL, [ReceivedDate] [datetime] NULL, [StoreManagerID] [varchar](50) NULL,PRIMARY KEY CLUSTERED ( [InwardAtStoreID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/code][code="sql"]CREATE TABLE [dbo].[GV_StoreAllocation]( [StoreTransactionId] [int] IDENTITY(1,1) NOT NULL, [StoreId] [int] NULL, [STNNo] [varchar](20) NULL, [VoucherBookletNo] [varchar](10) NULL, [Quantity] [int] NULL, [AllocatedDate] [datetime] NULL, [AllocatedBy] [varchar](40) NULL, CONSTRAINT [PK_GV_Allocation] PRIMARY KEY CLUSTERED ( [StoreTransactionId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]ALTER TABLE [dbo].[GV_StoreAllocation] ADD DEFAULT (getdate()) FOR [AllocatedDate][/code]Let me explained the scenario now:Firstly I allocate some vouchers to a store, after allocating follwing information gets saved in table GV_StoreAllocation like this:StoreTransactionId StoreId STNNo VoucherBookletNo Quantity AllocatedDate AllocatedBy1 1006 1004000066 B06349 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d93272 1006 1004000066 B06350 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d9327After this I have inwarded those vouchers in my store, when I inward some voucher it will get stored into GV_InwardAtStore like this:lets suppose I have inward 2 voucher with information as below:InwardAtStoreID StoreCode STNNo GRNNo VoucherBookletNo ReceivedDate StoreManagerID1 1006 1004000066 000000001 B06349 2013-07-03 11:43:35.033 012a967b-50b5-4af6-825f-f064af4d93272 1006 1004000066 000000001 B06350 2013-07-03 11:43:35.033 012a967b-50b5-4af6-825f-f064af4d9327Now, I am not able to sell voucher B06349 so I want to allocate it to another store 5001, as I allocate it to store 5001 table GV_StoreAllocation gets following rows:StoreTransactionId StoreId STNNo VoucherBookletNo Quantity AllocatedDate AllocatedBy1 1006 1004000066 B06349 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d93272 1006 1004000066 B06350 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d93273 5001 1006000006 B06349 1 2013-07-03 11:46:14.927 012a967b-50b5-4af6-825f-f064af4d9327Now, store 5001 have to inward this voucher untill that it should not get displayed on the screen, but its displaying. These are the stored procedures that I have created to display vouchers inforamtion for both scrrens Inward at Store and Allocation To Store,[code="sql"]ALTER PROCEDURE [dbo].[BS_InwardAtStore_ShowVouchers]@UserID varchar(40) = '012A967B-50B5-4AF6-825F-F064AF4D9327'ASBEGIN SET NOCOUNT ON;DECLARE @IsAdmin INT = 0SELECT @IsAdmin = a.IsAdmin FROM GV_StoreUserDetails a WHERE a.UserID = @UserID SELECT gias.StoreCode ToStore, --gias.STNNo, gias.grnno GRNNo, MIN(gias.VoucherBookletNo)FirstVoucherNo, MAX(gias.VoucherBookletNo)LastVoucherNo, gv.Denomination Denomination, gvt.VoucherType VoucherType, CAST(MAX(RIGHT(gias.VoucherBookletNo,7))AS int) - CAST(MIN(RIGHT(gias.VoucherBookletNo,7)) AS int) +1 Quantity, CASE WHEN LEFT(MIN(gias.VoucherBookletNo),1)='V' THEN 'VOUCHER' ELSE 'BOOKLET' END AS [Type]INTO #Voucher FROM GV_InwardAtStore gias JOIN GV_Voucher gv ON gv.VoucherNo = gias.VoucherBookletNo JOIN GV_VoucherType gvt ON gvt.VoucherTypeID = gv.VoucherTypeId JOIN GV_StoreAllocation gsa ON gsa.VoucherBookletNo = gias.VoucherBookletNo JOIN GV_StoreUserDetails sud ON sud.StoreCode = gias.StoreCode--WHERE sud.UserID = @UserIDGROUP BY gias.StoreCode, gias.grnno, gv.Denomination, gsa.Quantity, gvt.VoucherType --UNION ALLSELECT gias.StoreCode ToStore, --gias.STNNo, gias.grnno GRNNo, MIN(gias.VoucherBookletNo) FirstBookletNo, MAX(gias.VoucherBookletNo) LastBookletNo, gv.Denomination Denomination, gvt.VoucherType VoucherTYpe, CAST(MAX(RIGHT(gias.VoucherBookletNo,5))AS int) - CAST(MIN(RIGHT(gias.VoucherBookletNo,5)) AS int) +1 Quantity, CASE WHEN LEFT(MIN(gias.VoucherBookletNo),1) = 'B' THEN 'BOOKLET' ELSE 'VOUCHER' END AS [Type]INTO #Booklet FROM GV_InwardAtStore gias JOIN GV_Booklet gv ON gv.BookletID = gias.VoucherBookletNo JOIN GV_VoucherType gvt ON gvt.VoucherTypeID = gv.VoucherTypeId JOIN GV_StoreAllocation gsa ON gsa.VoucherBookletNo = gias.VoucherBookletNo JOIN GV_StoreUserDetails sud ON sud.StoreCode = gias.StoreCode--WHERE sud.UserID = @UserIDGROUP BY gias.StoreCode, gias.grnno, gv.Denomination, gsa.Quantity, gvt.VoucherTypeIF @IsAdmin = 1 SELECT v.ToStore, v.GRNNo, v.FirstVoucherNo, v.LastVoucherNo, v.Denomination, v.VoucherType, v.Quantity, v.[Type] FROM #Voucher v UNION ALL SELECT b.ToStore, b.GRNNo, b.FirstBookletNo, b.LastBookletNo, b.Denomination, b.VoucherTYpe, b.Quantity, b.[type] FROM #Booklet bELSE SELECT v.ToStore, v.GRNNo, v.FirstVoucherNo, v.LastVoucherNo, v.Denomination, v.VoucherType, v.Quantity, v.[Type] FROM #Voucher v WHERE v.ToStore = (SELECT a.StoreCode FROM GV_StoreUserDetails a WHERE a.UserID = @UserID) UNION ALL SELECT b.ToStore, b.GRNNo, b.FirstBookletNo, b.LastBookletNo, b.Denomination, b.VoucherTYpe, b.Quantity, b.[type] FROM #Booklet b WHERE b.ToStore = (SELECT a.StoreCode FROM GV_StoreUserDetails a WHERE a.UserID = @UserID)ORDER BY GRNNo DESCEND[/code][code="sql"]ALTER PROCEDURE [dbo].[BS_StoreAllocation_ShowVouchers]ASBEGINSET NOCOUNT ON;WITH Voucher AS( SELECT gsa.StoreId StoreCode, gsa.STNNo STNNo, MIN(gsa.VoucherBookletNo) FirstID, MAX(gsa.VoucherBookletNo) LastID, gv.Denomination, CAST(MAX(RIGHT(gsa.VoucherBookletNo,7))AS int) - CAST(MIN(RIGHT(gsa.VoucherBookletNo,7)) AS int) +1 Quantity, gv.VoucherTypeId, CASE WHEN LEFT(MIN(gsa.VoucherBookletNo),1) = 'B' THEN 'BOOKLET' ELSE 'VOUCHER' END AS Booklet, ISNULL(gvstn.AWBNo, '') AS AWBNo FROM GV_StoreAllocation gsa JOIN GV_Voucher gv ON gv.VoucherNo = gsa.VoucherBookletNo JOIN GV_VoucherType gvt ON gv.VoucherTypeId = gvt.VoucherTypeID JOIN GV_STNDetails gvstn ON gsa.STNNo = gvstn.STNNO AND gvstn.TO_StoreCode = gsa.StoreId --AND gvstn.AWBNo IS NULL GROUP BY gsa.StoreId, gsa.STNNo, --gsa.Quantity, gv.Denomination, gv.VoucherTypeId, AWBNo --gsa.VoucherBookletNo),Booklet AS( SELECT gsa.StoreId StoreCode, gsa.STNNo STNNo, MIN(gsa.VoucherBookletNo) FirstID, MAX(gsa.VoucherBookletNo) LastID, gv.Denomination, CAST(MAX(RIGHT(gsa.VoucherBookletNo,5))AS int) - CAST(MIN(RIGHT(gsa.VoucherBookletNo,5)) AS int) +1 Quantity, gv.VoucherTypeId, CASE WHEN LEFT(MIN(gsa.VoucherBookletNo),1) = 'B' THEN 'BOOKLET' ELSE 'VOUCHER' END AS Booklet, ISNULL(gvstn.AWBNo, '') AS AWBNo FROM GV_StoreAllocation gsa JOIN GV_Booklet gv ON gv.BookletID = gsa.VoucherBookletNo JOIN GV_VoucherType gvt ON gv.VoucherTypeId = gvt.VoucherTypeID JOIN GV_STNDetails gvstn ON gsa.STNNo = gvstn.STNNO AND gvstn.TO_StoreCode = gsa.StoreId --AND gvstn.AWBNo IS NULL GROUP BY gsa.StoreId, gsa.STNNo, --gsa.Quantity, gv.Denomination, gv.VoucherTypeId, AWBNo)SELECT * FROM VoucherUNION ALLSELECT * FROM BookletORDER BY STNNo DESCEND[/code]Please help....

the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011 Then confirm the Financial Year, Quarter, Month, Week and Weekday,please help

Posted: 02 Jul 2013 08:59 PM PDT

i am not sure about this statements please help :CREATE PROCEDURE FinYear2012 @Cost_date date = '2011-02-15 00:00:00.0000000' ASBEGIN DECLARE @Fin_year int DECLARE @Fin_quarter int DECLARE @fin_Month int DECLARE @Fin_Week int DECLARE @Weekday Varchar (25) SELECT Mydate , CASE WHEN (Mydate) BETWEEN '2010-02-28 ' AND '2010-05-28 ' THEN 'First Quarter' WHEN (Mydate) BETWEEN '2010-05-28 ' AND '2010-08-28 ' THEN 'Second Quarter' WHEN (Mydate) BETWEEN '2010-08-28 ' AND '2010-12-28 ' THEN 'Third Quarter' WHEN (Mydate) BETWEEN '2010-12-28 ' AND '2011-02-28 ' THEN 'Fourth Quarter' END AS FiscalYearFROM Transactions set @Fin_year = DATEDIFF(year, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_quarter = DATEDIFF(quarter, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @fin_Month = DATEDIFF(MONTH, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_Week = DATEDIFF(WEEK, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Weekday = DATEDIFF(WEEKDAY, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); SELECT @Fin_year as FinancialYr ,@Fin_quarter as FinancialQtr , @fin_Month as FinancialMnth , @Weekday as WekkedayENDGO:-D

sql quantity sold per day

Posted: 02 Jul 2013 07:10 PM PDT

I have tried this but my head of it department still telling me tht i have failed this is my last chanceWrite a SQL statement from the above example tables that will return the Product Name and Quantity with the maximum quantity sold per day.SELECT e.BuProductName,s.sum(OrderQuantity) as Sold per dayFROM dbo.Product AS e INNER JOIN dbo.Transact AS s ON e. productID= s. productID

Problem using FOR XML in SQL Query

Posted: 02 Jul 2013 08:04 PM PDT

Hi to all, I have one format problem that i cannot get it right. I hope you can help me.So here it is.[code="sql"]declare @test table(id int identity(1,1), custname varchar(10), custcode varchar(10) )insert @testvalues ('name1','code1'),('name2','code2')SELECT (SELECT custname,custcode FOR XML PATH(''),TYPE) AS ItemFROM @testFOR XML PATH('Header'),ROOT('ns1')[/code]The above code it will output like this.[code="xml"]<ns1> <Header> <Item> <custname>name1</custname> <custcode>code1</custcode> </Item> </Header> <Header> <Item> <custname>name2</custname> <custcode>code2</custcode> </Item> </Header></ns1>[/code]What i want is something like this...[code="xml"]<ns1> <Header> <Item> <custname>name1</custname> <custcode>code1</custcode> </Item> <Item> <custname>name2</custname> <custcode>code2</custcode> </Item> </Header></ns1>[/code]All of your answers is very much appreciated. thanks

financial year sql statement please help

Posted: 02 Jul 2013 07:06 PM PDT

You are only given the transaction date in your transactions table and your client requires you to be able to show them their data by Financial Year, Quarter, Month, Week and Week Day[b]Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011Then confirm the Financial Year, Quarter, Month, Week and Weekday.[/b]CREATE PROCEDURE Fin_Year1 @Cost_date date = '2011-02-15 00:00:00.0000000' ASBEGIN DECLARE @Fin_year int DECLARE @Fin_quarter int DECLARE @fin_Month int DECLARE @Fin_Week int DECLARE @Weekday Varchar (25) set @Fin_year = DATEDIFF(year, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_quarter = DATEDIFF(quarter, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @fin_Month = DATEDIFF(MONTH, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_Week = DATEDIFF(WEEK, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Weekday = DATEDIFF(WEEKDAY, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); SELECT @Fin_year as FinancialYr ,@Fin_quarter as FinancialQtr , @fin_Month as FinancialMnth , @Weekday as WekkedayENDGO

Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011

Posted: 02 Jul 2013 08:26 PM PDT

5.You are only given the transaction date in your transactions table and your client requires you to be able to show them their data by Financial Year, Quarter, Month, Week and Week DayWrite the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011Then confirm the Financial Year, Quarter, Month, Week and Weekday.R/CREATE PROCEDURE FinYear2012 @Cost_date date = '2011-02-15 00:00:00.0000000' ASBEGIN DECLARE @Fin_year int DECLARE @Fin_quarter int DECLARE @fin_Month int DECLARE @Fin_Week int DECLARE @Weekday Varchar (25) SELECT Mydate , CASE WHEN (Mydate) BETWEEN '2010-02-28 ' AND '2010-05-28 ' THEN 'First Quarter' WHEN (Mydate) BETWEEN '2010-05-28 ' AND '2010-08-28 ' THEN 'Second Quarter' WHEN (Mydate) BETWEEN '2010-08-28 ' AND '2010-12-28 ' THEN 'Third Quarter' WHEN (Mydate) BETWEEN '2010-12-28 ' AND '2011-02-28 ' THEN 'Fourth Quarter' END AS FiscalYearFROM Transactions set @Fin_year = DATEDIFF(year, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_quarter = DATEDIFF(quarter, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @fin_Month = DATEDIFF(MONTH, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_Week = DATEDIFF(WEEK, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Weekday = DATEDIFF(WEEKDAY, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); SELECT @Fin_year as FinancialYr ,@Fin_quarter as FinancialQtr , @fin_Month as FinancialMnth , @Weekday as WekkedayENDGO

Function to replace string 'NULL' with null value

Posted: 02 Jul 2013 07:26 PM PDT

HiI am selecting values to one table and populating them in another table, and I'm using a case statement to check if the word is the string 'NULL' then I replace it with value null.e.g CASE WHEN [Gross_Amount] = 'NULL' THEN NULL ELSE CAST([Gross_Amount] AS DECIMAL(18,2)) END[Gross_Amount]So I'm thinking I can write a function that can check to see the value if it is a string NULL, if so replace it with null or return Gross_Amount if not. and rewrite that whole CASE statement, to objective here is to minimise the lines of code.Can anyone help please.

Max of 2 dates

Posted: 24 Jun 2013 07:40 PM PDT

I search for Date functions for finding the max of the 2 dates. But such function does not exits.Is there any easy way to do it.Thanks in advance.

CHARINDEX problem

Posted: 02 Jul 2013 09:50 AM PDT

Hello comunityI have a table name field FT.DESCAR Varchar(60) with the following content:5695 - 0 | 7050-127 MONTEMOR O NOVO with this Select :Select RIGHT(FT.DESCAR,CHARINDEX('|',FT.DESCAR)-1) from FTwhere ftstamp = 'jm13070255373,181882981'the result is:OR O NOVOto remove blank spaces i change my select to :select RIGHT( Ltrim(Rtrim(FT.DESCAR)),CHARINDEX('|', Ltrim(Rtrim(FT.DESCAR)))-1) from ft where ftstamp = 'jm13070255373,181882981'the result is the same:OR O NOVOI don´t understand why??someone could give me some help.Many thanksLuis Santos

Urgent help with Date comparison in TSQL

Posted: 02 Jul 2013 02:23 AM PDT

Dear friends,I have requirement - to retrieve order from database that were created before 12:00 AM GMT on June 3, 2013.I'm using datediff but it is giving me couple records that were created on June 3rd 2.59 AM also,below is my code of line-datediff(second,[WorkForce_JobPosting].[Job Posting Create Date_JP] ,'2013-06-03 12:00:00.000')> 0 )note the date format for thhis create date in DB is 'date time'thanksDhananjay

Put first line as column name

Posted: 02 Jul 2013 04:03 AM PDT

Hi people,I am creating a function to be used in a specific application that a user can insert any kind of information into a table. This code below:[code="sql"]IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_HEADER')CREATE TABLE TB_HEADER(ID_HEADER INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, CODIGO VARCHAR(50) UNIQUE, COL1_H VARCHAR(100), COL2_H VARCHAR(100), COL3_H VARCHAR(100), COL4_H VARCHAR(100), COL5_H VARCHAR(100), COL6_H VARCHAR(100), COL7_H VARCHAR(100), COL8_H VARCHAR(100), COL9_H VARCHAR(100), COL10_H VARCHAR(100))GO IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_DADOS')CREATE TABLE TB_DADOS(ID_DADOS INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, ID_HEADER INT, COL1_D VARCHAR(100), COL2_D VARCHAR(100), COL3_D VARCHAR(100), COL4_D VARCHAR(100), COL5_D VARCHAR(100), COL6_D VARCHAR(100), COL7_D VARCHAR(100), COL8_D VARCHAR(100), COL9_D VARCHAR(100), COL10_D VARCHAR(100))GOINSERT INTO TB_HEADERSELECT CODIGO = 'CARRO_01', COL1_H = 'MODELO_CARRO', COL2_H = 'ANO', COL3_H = 'DATA_COMPRA', COL4_H = 'COR', COL5_H = 'PLACA', COL6_H = 'COL6', COL7_H = 'COL7', COL8_H = 'COL8', COL9_H = 'COL9', COL10_H = 'COL10'GO INSERT INTO TB_HEADERSELECT CODIGO = 'IMOVEL_01', COL1_H = 'TIPO_DE_IMOVEL', COL2_H = 'LOCALIZAÇÃO', COL3_H = 'VALOR_ENTRADA', COL4_H = 'QTD_PARCELA', COL5_H = 'VALOR_PARCELA', COL6_H = 'MOEDA', COL7_H = 'COL7', COL8_H = 'COL8', COL9_H = 'COL9', COL10_H = 'COL10'GO INSERT INTO TB_DADOSSELECT ID_HEADER = ID_HEADER, COL1_D = 'PALIO', COL2_D = '2012', COL3_D = '05/02/2013', COL4_D = 'VERMELHO', COL5_D = 'XPT 1515', COL6_D = 'COL6', COL7_D = 'COL7', COL8_D = 'COL8', COL9_D = 'COL9', COL10_D = 'COL10'FROM TB_HEADER WHERE CODIGO = 'CARRO_01'GO INSERT INTO TB_DADOSSELECT ID_HEADER = ID_HEADER, COL1_D = 'CRV', COL2_D = '2011', COL3_D = '23/06/2012', COL4_D = 'PRATA', COL5_D = 'ABC 2020', COL6_D = '', COL7_D = '', COL8_D = '', COL9_D = '', COL10_D = ''FROM TB_HEADER WHERE CODIGO = 'CARRO_01'GO INSERT INTO TB_DADOSSELECT ID_HEADER = ID_HEADER, COL1_D = 'APARTAMENTO', COL2_D = 'BARRA DA TIJUCA', COL3_D = '200.000,00', COL4_D = '48', COL5_D = '8.000,00', COL6_D = 'R$', COL7_D = '', COL8_D = '', COL9_D = '', COL10_D = ''FROM TB_HEADER WHERE CODIGO = 'IMOVEL_01'GO CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))RETURNS TABLEASRETURNSELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H FROM TB_HEADER WHERE CODIGO = @CODIGOUNION ALLSELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_DFROM TB_DADOS INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER WHERE CODIGO = @CODIGOGO[/code]Although big query, that is a query function creation. The problem is: How can I return first lines from "tb_Header" to be column name?, I thought in "sp_rename", but I afraid it can be slow in a big table. I'd like to emphasize that columns are always dynamic. Part of code below[code="sql"]CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))RETURNS TABLEASRETURNSELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H FROM TB_HEADER WHERE CODIGO = @CODIGOUNION ALLSELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_DFROM TB_DADOS INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER WHERE CODIGO = @CODIGOGO[/code]grateful for your attention

Need to derive avg execuion time for 3 tables

Posted: 02 Jul 2013 04:23 AM PDT

[font="Courier New"]Need to derive avg execution time per row in table for each tablename. Sample table layoutTableName BeginTime EndTime========= ========= ======= T1 2013-06-02 00:30.10.073 2013-06-02 00:30.17.463 T1 2013-05-14 00:40.09.283 2013-05-14 00:42.10.640T1 2013-06-22 00:52.02.073 2013-06-22 01:03.10.727T1 2013-07-01 00:03.07.487 2013-07-01 00:04.03.120T2 2013-03-06 00:16.10.763 2013-03-06 02:21.10.133T2 2013-07-02 00:22.10.880 2013-07-02 01:12:04.282 T2 2013-05-12 00:01.10.113 2013-05-12 00:44.15.797T3 2013-01-18 00:14.10.577 2013-01-18 02:02.19.120T3 2013-07-02 00:23.10.560 2013-07-02 01:42.03.480So I need avg time for T1, T2, and T3... just these 3 values.thanks for any suggestions[/font]

Test

Posted: 02 Jul 2013 05:07 AM PDT

Testing
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog