Tuesday, April 9, 2013

[T-SQL] SQL /Procedure - for Transpose and Calculation

[T-SQL] SQL /Procedure - for Transpose and Calculation

SQL /Procedure - for Transpose and Calculation

Posted: 08 Apr 2013 01:10 PM PDT

Hi Team,I have a Complex requirement.Source:-------- NAME PAYMENT_TYPE PAYMENTSUDHIR SAL 30.3SUDHIR ADV 10.3SUDHIR ALL_1 10SUDHIR ALL_2 10SUDHIR ALL_3 10MADHAV SAL 34MADHAV ALL_1 24MADHAV BONUS 10Formula:--------TGT_PAYMENT_TYPE PAYMENT_FORMULASAL SAL-ADVCOMM ALL_1+ALL_2-ALL_3BONUS SAL * 1.1Target Table:------------NAME SAL BONUS COMMSUDHIR 20 0 10MADHAV 34 10 24Please need help to code SQL Query / Package which will take the Formula on from the table Formula and apply on Source before pushing it to target.Thanks & Regards,Sudhir Nune.

Create table Using the Structure and Data from the Dynamic SQL

Posted: 09 Apr 2013 12:32 AM PDT

Hi All,I need to create a table which will be having teh structure of the Result of the PIVOTED Query.I am sure we can create a view for this when we are using Pivot.I am creating the table so that I can Update the Data of a Column from Multiple Columns which we have Data needed.Sudhir Nune

pull out all values within an XML column

Posted: 07 Aug 2012 12:47 AM PDT

i'm wondering if something like this is possible in some way:[code="sql"]select OriginalSchema.value('Form[1]/Codes[1]/code[' + convert(varchar(10),y.myNum) + ']','varchar(max)'),--OriginalSchema.value('Form[1]/Codes[1]/code[sql:variable("y.myNum")]','varchar(max)'),* from (select id,ROW_NUMBER() over(partition by id order by id) myNum,OriginalSchema from myXMLTable xcross join TallyTable t wheret.N <= 400and x.ID = 1)y[/code]currently, it doesn't work stating:[code="other"]The argument 1 of the XML data type method "value" must be a string literal[/code]my goal is to pull out all the 'code' in one set-based manner. There are 400 'code'.e.g[code="other"]<Codes> <code id="1">A</code> <code id="2">B</code> <code id="400">Z</code></Codes>[/code]is this possible? I know that if i hard code it to be '1' or '2' etc, instead of [code="other"]convert(varchar(10),y.myNum)[/code] it will work, but obviously it will only be bringing back the item at that address.cheers

Need help with Dates

Posted: 08 Apr 2013 12:42 PM PDT

Dear friends,I'm new to TSQL and need help please -I have to Calculate a field value as Position Duration = (Work Order End Date - Work Order Start Date)/30. Round up to the next whole number.in the DB the 2 dates are of the format-Work Order End Date =2013-05-31 00:00:00.000andWork Order Start Date = 2012-12-03 00:00:00.000ThanksDhananjay

how to convert the below subquery into join..single update statment with joins

Posted: 08 Apr 2013 06:18 PM PDT

here is the complete query..the ulitmate aim is the update the claim table...but it should be only one statement without any subquery only joins are allowed becuase as i said am going to run this in an appliance which wont support subqueryDECLARE @DecWdrwn as TABLE(CtryId smallint, CmId int, DecWdrwnDt int);WITH s AS (SELECT Ctryid,CmId,Dt,ISNULL((SELECT max(CmHistDtTmId) FROM ClaimHistory l WHERE St = 3 AND l.Ctryid = c.Ctryid AND l.CmId = c.CmId), 0) MaxDec, ISNULL((SELECT max(CmHistDtTmId) FROM ClaimHistory l WHERE St = 7 AND l.Ctryid = c.Ctryid AND l.CmId = c.CmId), 0) MaxSetFROM ClaimHistory cWHERE St =3)INSERT INTO @DecWdrwnSELECT CtryId, CmId, Max(Dt) DecDt FROM sWHERE MaxSet > MaxDecGROUP BY CtryId,CmIdyour response is much appreciated...UPDATE Claims SET CmDclnWdwnDt = (SELECT DecWdrwnDt FROM @DecWdrwn d WHERE d.CmId = Claims.CmId AND d.CtryId = Claims.CtryId)WHERE EXISTS (SELECT * FROM @DecWdrwn d WHERE d.CmId = Claims.CmId AND d.CtryId = Claims.CtryId)

Checking to see if a time is between two times

Posted: 21 Feb 2012 10:05 PM PST

Hi all,I'm trying to break down events by hour of day as 00-23.So if an event has a duration of 5 hours, from 22-03 then (this is the question) then I want to count the hours/events asHour EventCount21 022 123 100 101 102 103 0I created 24 case statements to check if an event was active - if the case hour was between the event start and end times. But, of course this fails utterly as the day wraps around its 24 hour clock. As an example, checking at 02 hours, the event started at 22 and ended at 03, so I'd like to count this as a 1. But, 02 is not between 22 and 03. Err, any ideas on how to do this chaps ?Regards, Greg

Dead Lock Question

Posted: 08 Apr 2013 03:39 PM PDT

Hi Guys, I have two questions. 1) My first question, how i can find out on which table of my database has dead lock problem? 2) If i restart my server all dead lock problem solve mean go away or not? or still i have to kill each process manually?Thank You.

How to call a batch file to execute from an SP

Posted: 20 Mar 2013 04:15 AM PDT

Hi All,Need your assistance please, I am not very good with scripting.I have created a draft of SP, and I need syntax to make a call to a batch file(.bat) from within the SP. Once I have that I can incorporate it in the code and begin testing.Can someone please provide sample script.Thanks,SueTons.

Rounding up

Posted: 08 Apr 2013 08:10 AM PDT

Hello Everyone!I have this issue where i use a store procedure to create a report:ALTER Procedure [dbo].[A_RepChequesxCheque]@FechaInicio T_DATETIME,@FechaTermino T_DATETIMEASBeginselect ant.CheckId, Cheque, Fecha, Departamento, ISNULL (Cajero,0), SL2.Importe, SL2.Propina, ISNULL (SL2.FormaDePago, 'Cancelado') as FormaDePago, ISNULL(descuento.DescImp, 0) As DescImp, 1.16 * ISNULL(descuento.DescImp, 0) As DescImpIVA, SL2.Importe + 1.16 * ISNULL(descuento.DescImp, 0) + SL2.Propina As TotalBruto, ISNULL ((SL2.Importe + SL2.Propina) / 1.16,0) As MontoNeto, ISNULL ((SL2.Importe - SL2.Propina - SL2.ScAmt) -((SL2.Importe - SL2.Propina - SL2.ScAmt)/ 1.16),0) As ImpIVA, (SL2.Importe + SL2.Propina) As MontoTotal, Empleado, NoMesa, Personas from (Select XCH.CheckId, XCH.CheckNo As Cheque, XCH.TransactionDate As Fecha, KD.Name As Departamento, (KE.FirstName + KE.LastName ) As Cajero, KEB.Name As Empleado, XCT.Name As NoMesa, XCH.Covers As Personas From K_Employee KE, X_CheckHeader XCH, K_Department KD, K_EmpBadge KEB, X_CheckTable XCT Where XCH.IsCurrent = 1 and XCH.Active = 0 And XCH.ServerId = KEB.ServerId And KE.EmpId = XCH.SettledId And XCH.SetDept = KD.DeptNo And XCH.TransactionDate Between @FechaInicio And @FechaTermino And XCT.CheckId = XCH.CheckId Group by XCH.CheckId, XCH.CheckNo, XCH.TransactionDate, KD.Name, KE.FirstName, KE.LastName,KEB.Name,XCT.Name,XCH.Covers) antLEFT OUTER JOIN (Select CheckId, Sum(PromoAmt) DescImp from X_CheckItem, X_CheckPromo where X_CheckItem.ItemId = X_CheckPromo.ItemId group by CheckId) descuentoON ant.CheckId = descuento.CheckIdLEFT OUTER JOIN (Select XCH.CheckId, ISNULL (Sum(XCP.PayAmt),0) As Importe, (ISNULL (Sum(XCP.TipAmt),0)*-1) As Propina, ISNULL (Sum(Servicio.ScAmt),0) as ScAmt, ISNULL (KP.Name,'Cancelado') As FormaDePago From X_CheckHeader XCH left join (select XCH.CheckId, Sum(isnull(XCSVC.ScAmt,0)) As ScAmt from X_CheckHeader XCH, X_CheckItem XCI, X_CheckSrvcChrg XCSVC where XCH.IsCurrent = 1 And XCH.Active = 0 And (XCH.TransactionDate Between @FechaInicio And @FechaTermino) And XCI.CheckId = XCH.CheckId And XCSVC.ItemId = XCI.ItemId GROUP BY XCH.CheckId )Servicio on XCH.CheckId = Servicio.CheckId, X_CheckPay XCP, K_Payment KP Where XCH.IsCurrent = 1 and XCH.Active = 0 And XCH.CheckId = XCP.CheckId And XCH.TransactionDate Between @FechaInicio And @FechaTermino And KP.PaymentID = XCP.PaymentID Group by XCH.CheckId,KP.Name, ScAmt) SL2ON ant.CheckId = SL2.CheckId Order by Cheque AscEndGOIt returns almost every field correct except for the IMPIVA where it returns 2 decimals rounded.ISNULL ((SL2.Importe - SL2.Propina - SL2.ScAmt) -((SL2.Importe - SL2.Propina - SL2.ScAmt)/ 1.16),0) As ImpIVA,If the math comes 45.856 Returns 45.86 is there a way to prevent this?

split single row into multiple lines

Posted: 08 Apr 2013 09:07 AM PDT

Hi, Can any one help me please How to split single row into multiple lines (Not rows) ThanksGrace
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog