| 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 |
| 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 |
| 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? |
