Monday, September 2, 2013

[T-SQL] Use SUM() with GROUP BY?

[T-SQL] Use SUM() with GROUP BY?

Use SUM() with GROUP BY?

Posted: 02 Sep 2013 12:47 AM PDT

Is it possible to use SUM() in a SELECT query that uses a series of CASE statements to group data together? I have a procedure that generates an age-range report using case into different groups, I would like to combine that report with a report that SUM's 7 different fields by category. Right now, I take the output of the age -range report and through code, sum up all of the fields by separate calls. It's decently fast but I would like to know if I can move the SUM calculation into the case query if that's even possible.

Proplem Put Filter ("And" or "Or") in Query

Posted: 01 Sep 2013 07:32 PM PDT

Table With two Feild Name,Tools Example Table :1-Mike Walker2-Mike Cane3-Steve Walker4-Mitchell CaneI want someone who uses both(Walker and Cane ) to exhibit = MikeQuery : Select Name From TableTools WHERE (Tools = N'Walker') AND (Tools = N'Cane')Result=0With By Or Result=4How to write this queryPlz Help Me

Finding row number which caused the error in Table value parameter insertion

Posted: 01 Sep 2013 09:08 PM PDT

HiI am trying to write a Bulk import procedure, i am using table value parameter to insert thousands of rows in the table.The problem is if any error happens,i am not able to know which row in the table value parameter caused it.I am not using transaction in stored procedurehere is the code[code="sql"]--Table creation CREATE TABLE DataImport (id int identity(1,1),Name varchar(2))Insert INTO DataImportvalues('aa'),('bb'),('cc'),('dd')--SELECT * FROM DataImport--Table type creationCREATE TYPE udt_Table AS TABLE (ud_name varchar(3))--stored procedure CREATE PROC Usp_DataImport@TVP [dbo].[udt_Table] READONLYASBEGIN DECLARE @Insert Table (ActionType nvarchar(10),NewestName varchar(3),OldestName vaRCHAR(3)) MERGE DataImport AS [TARGET] USING @TVP AS [SOURCE] ON [TARGET].Name = [SOURCE].ud_name WHEN MATCHED THEN UPDATE SET Name = [SOURCE].ud_name WHEN NOT MATCHED THEN INSERT (Name) VALUES([SOURCE].ud_name) OUTPUT $ACTION, Inserted.Name,Deleted.Name INTO @Insert; SELECT * FROM @InsertEND--Executing Procedure--First proc Exection sucessfully doneDECLARE @dd AS dbo.udt_Table INSERT INTO @ddvalues('ee'),('aa'),('XX'),('GG'),('66')EXEC Usp_DataImport @TVP = @dd--Second proc Exection fail this will generate error due to varchar length (String validation can be done frontend to avoid this problem...i am using this to generate the error)Here i want to get at which row error happens...so that i will return to application to make changes and then insertDECLARE @dd AS dbo.udt_Table INSERT INTO @ddvalues('ee'),('aa'),('XX'),('GG'),('GGG')('66')EXEC Usp_DataImport @TVP = @dd[/code]Thanks

sql query

Posted: 01 Sep 2013 08:14 AM PDT

Hello friends,Please help me in finding the correct query!!Question:You need to find duplicate account's in an Account table. The Account table has 15 columns in it. What is the problem with this query?SELECT * FROM AccountWHERE CreateDate > '2/1/2013'GROUP BY AccountIDHAVING COUNT(AccountID) >1Thanks,RT.

Executed as user: MVS\sqlserver. Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. [SQLSTATE 42000] (Error 468). The step failed.

Posted: 01 Sep 2013 06:02 PM PDT

Hi Guys,I need help in sorting out this job its begin failing for a week now.The code is as follows:--==================================Get Box_no, MSISDN from Navision================================================== --Pulls MSISDN info from DynamicsNavdatabase SELECT DISTINCT CAST(REPLACE(si2.[Serial No_],'AC1','BX1') AS VARCHAR(15)) AS Box_no, si.[Serial No_] AS SIM, si.[MSISDN Serial No_] AS MSISDN INTO #NavisionUnitSerials -- drop table #NavisionUnitSerials FROM DynamicsNavdatabase.dbo.[MIX TELEMATICS$Serial No_ Information] si LEFT JOIN DynamicsNavdatabase.dbo.[MIX TELEMATICS$Serial No_ Information] si2 ON si2.[SIM Serial No_ (Embeded)] = si.[Serial No_] WHERE si.[Item No_] = '1000851' AND si.[MSISDN Serial No_] IS NOT NULL AND si.[MSISDN Serial No_] <> '' AND si2.[Serial No_] <> '' AND si2.[Serial No_] IS NOT NULL; CREATE NONCLUSTERED INDEX IDX_NAV_BOX ON #NavisionUnitSerials ( Box_no ); --=============================Get last passed test date of RSLead & MiXControl========================================= SELECT unitid AS [Box_No], createdate AS [CreateDate], 1 as tab INTO #LastPassedTestDate_Init -- drop table #LastPassedTestDate_Init FROM RSLead.dbo.unittesthead H WITH (NOLOCK) WHERE (testrefcode IS NOT NULL OR testrefcode <> '') --IF HAS REF THEN TEST PASSED AND H.unittestheadno IN (SELECT MAX(td2.unittestheadno) FROM RSLead.dbo.unittesthead td2 WITH (NOLOCK) WHERE h.unitid = td2.unitid COLLATE DATABASE_DEFAULT GROUP BY td2.unitid) UNION ALL SELECT [Box_No], [CreateDate], 2 FROM [MixControl].[dbo].[UnitTestResultHead] H WITH (NOLOCK) WHERE TestStatus = 3 AND TestReference IS NOT NULL AND H.[UnitTestResultHeadID] IN (SELECT MAX(td2.[UnitTestResultHeadID]) FROM [MixControl].[dbo].[UnitTestResultHead] td2 WITH (NOLOCK) WHERE h.[Box_No] = td2.[Box_No] COLLATE DATABASE_DEFAULT GROUP BY td2.[Box_No]); CREATE CLUSTERED INDEX IDX_LastPassedTestDate_Init ON #LastPassedTestDate_Init ( [Box_No] ); SELECT [Box_No], MAX([CreateDate]) AS [CreateDate] INTO #LastPassedTestDate -- drop table #LastPassedTestDate FROM #LastPassedTestDate_Init GROUP BY Box_No; CREATE CLUSTERED INDEX IDX_LastPassedTestDate ON #LastPassedTestDate ( [Box_No] ); --=============================Get Company RegNo on MiXControl========================================= Select ClientID,CompanyRegistrationNumber into #tempCompanyReg from dbo.ClientCompany where CompanyRegistrationNumber <> '' --==========================================Get Unit Info============================================================= DECLARE @debperiodno INT; SET @debperiodno = ( SELECT DISTINCT(periodno)FROM rsdebtors.dbo.period WHERE curflag = 1 ); INSERT INTO [AllUnitsReportDaily] SELECT Distinct UT.UnitTypeDesc AS [TYPE], C.ContractID, U.Box_No, A.RegistrationNumber, CL.AccountName, rscon.chargecode, --CASE -- WHEN (cch.escalchargeflag = 1) THEN rscon.chargeamt -- WHEN (rscon.incrperiod > @debperiodno + 1) THEN rscon.chargeamt -- ELSE cch.chargeamt -- END AS 'ChargeAmt', rscon.chargeamt AS 'ChargeAmt', cch.chargeamt as NormalChargeAmt, rscon.startperiod, C.CreateDate AS [ContractDate], rscon.accountno AS [AccountNo], C.salespersonid AS [SalesPersonID], b2.brokername as SalesPersonDesc, C.FitmentCentreID AS [FitmentCentreID], case when left(u.Box_No,2) = '04' then i.name when LEFT(u.box_no,2) = '35' then i.name else b1.brokername end as [FitmentCentreName], CASE WHEN (cch.escalchargeflag = 1) THEN 'RENTAL CONTRACT' ELSE 'CASH CONTRACT' END AS 'ContractType', ISNULL(CCT.contracttypedesc,CCT1.contracttypedesc) AS ContractTypeDesc, CAST(ISNULL(aic.PolicyNumber,'') AS CHAR(30)) AS 'PolicyNumber', ISNULL(C.UniqueNumber,'') AS 'UniqueNumber', ISNULL(l.leadno,ML.LEADID) AS LeadNo, ISNULL(l.brokerno,LP.BrokerID) AS BrokerNo, ISNULL(ISNULL(B.brokercode,LP.ExternalPartnerCode),'') AS ExternalPartnerCode, ISNULL(L.idno,LP.IDNumber) AS IDNumber, zl.MSISDN, LPT.CreateDate AS LastPassedTestDate, a.ChassisNumber, tcr.CompanyRegistrationNumber, ISNULL(l.createopr,ML.CreateOpr) AS LeadCreatedBy, c.editOpr as [ContractModifiedBy], ama.AssetMakeDesc, am.AssetModelDesc FROM MixControl.dbo.unit U WITH (NOLOCK) JOIN MixControl.dbo.unittype UT WITH (NOLOCK) ON U.UnitTypeID = UT.UnitTypeID JOIN MixControl.dbo.ContractAsset CA WITH (NOLOCK) ON CA.AssetID = U.AssetID JOIN MixControl.dbo.CONTRACT C WITH (NOLOCK) ON C.ContractID = CA.ContractID JOIN MixControl.dbo.Asset A WITH (NOLOCK) ON CA.AssetID = A.AssetID JOIN MixControl.dbo.assetmodel AM WITH (NOLOCK) ON a.assetmodelid = am.assetmodelid JOIN MixControl.dbo.assetmake AMA WITH (NOLOCK) ON am.assetmakeid = ama.assetmakeid JOIN MixControl.dbo.Client CL WITH (NOLOCK) ON CL.ClientID = C.ClientID JOIN RSDebtors.dbo.contractcharge rscon WITH (NOLOCK) ON rscon.contractno = C.ContractID JOIN RSDebtors.dbo.charge cch WITH (NOLOCK) ON rscon.chargecode = cch.chargecode LEFT JOIN RSlead.dbo.lead L WITH (NOLOCK) ON l.contractid = c.ContractID AND l.eventtypecode = 'PUSHTOFX' LEFT JOIN RSlead.dbo.broker B WITH (NOLOCK) ON L.externalpartnerno = B.brokerno LEFT JOIN MixControl.dbo.lead ML WITH (NOLOCK) ON ML.contractid = c.ContractID LEFT JOIN MixControl.dbo.LeadPerson LP WITH (NOLOCK) ON LP.LeadPersonID = ML.LeadPersonID LEFT JOIN MixControl.dbo.AssetInsuranceCompany AIC WITH (NOLOCK) ON CA.AssetID = AIC.AssetID LEFT JOIN RSLead.dbo.contracttype CCT WITH (NOLOCK) ON CCT.contracttypeno = L.contracttypeno LEFT JOIN RSLead.dbo.contracttype CCT1 WITH (NOLOCK) ON CCT1.contracttypeno = ML.contracttypeID LEFT JOIN #navisionUnitSerials zl ON U.Box_No = zl.box_no COLLATE DATABASE_DEFAULT LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No COLLATE DATABASE_DEFAULT left join #tempCompanyReg tcr with(nolock) on cl.ClientID = tcr.ClientID left join RSlead.dbo.broker b1 on c.FitmentCentreID = cast(b1.brokerno as varchar(10)) left join RSlead.dbo.broker b2 on c.SalespersonID = b2.brokercode left join Installer i on c.FitmentCentreID = cast(i.installerID as varchar(15)) WHERE rscon.endperiod > @debperiodno --and rscon.startperiod < = @debperiodno AND U.IsActive = 1 AND CA.IsActive = 1 AND C.IsActive = 1 order by U.Box_No desc ; --=========================================CA with service charges========================================================= INSERT INTO [AllUnitsReportDaily] SELECT Distinct UT.UnitTypeDesc AS [TYPE], C.ContractID, U.Box_No, A.RegistrationNumber, CL.AccountName, 'MixServices', SUM(conserv.serviceamt) AS [chargeamt], '0.00' as NormalChargeAmt, conserv.startperiod, C.CreateDate AS [ContractDate], conserv.accountno AS [AccountNo], C.salespersonid AS [SalesPersonID], b2.brokername as SalesPersonDesc, C.FitmentCentreID AS [FitmentCentreID], b1.brokername as [FitmentCentreName], CASE WHEN l.contracttypeno = 2 THEN 'RENTAL CONTRACT' ELSE 'CASH CONTRACT' END AS 'Contract Type', ISNULL(CCT.contracttypedesc,CCT1.contracttypedesc) AS ContractTypeDesc, CAST(ISNULL(aic.PolicyNumber,'') AS CHAR(30)) AS 'PolicyNumber', ISNULL(C.UniqueNumber,'') AS 'UniqueNumber', ISNULL(l.leadno,ML.LEADID) AS LeadNo, ISNULL(l.brokerno,LP.BrokerID) AS BrokerNo, ISNULL(ISNULL(B.brokercode,LP.ExternalPartnerCode),'') AS ExternalPartnerCode, ISNULL(L.idno,LP.IDNumber) AS IDNumber, zl.MSISDN, LPT.CreateDate AS LastPassedTestDate, a.ChassisNumber, tcr.CompanyRegistrationNumber, ISNULL(l.createopr,ML.CreateOpr) AS LeadCreatedBy, c.editOpr as [ContractModifiedBy], ama.AssetMakeDesc, am.AssetModelDesc FROM unit U WITH (NOLOCK) JOIN unittype UT WITH (NOLOCK) ON U.UnitTypeID = UT.UnitTypeID JOIN ContractAsset CA WITH (NOLOCK) ON CA.AssetID = U.AssetID JOIN CONTRACT C WITH (NOLOCK) ON C.ContractID = CA.ContractID JOIN Asset A WITH (NOLOCK) ON CA.AssetID = A.AssetID JOIN dbo.assetmodel AM WITH (NOLOCK) ON a.assetmodelid = am.assetmodelid JOIN dbo.assetmake AMA WITH (NOLOCK) ON am.assetmakeid = ama.assetmakeid JOIN Client CL WITH (NOLOCK) ON CL.ClientID = C.ClientID JOIN rsdebtors.dbo.contractservice conserv WITH (NOLOCK) ON conserv.contractno = ca.ContractID LEFT JOIN AssetInsuranceCompany AIC WITH (NOLOCK) ON CA.AssetID = AIC.AssetID LEFT JOIN rslead.dbo.lead l WITH (NOLOCK) ON CA.ContractID = l.contractid AND l.eventtypecode = 'PUSHTOFX' LEFT JOIN RSlead.dbo.broker B WITH (NOLOCK) ON L.externalpartnerno = B.brokerno LEFT JOIN MixControl.dbo.lead ML WITH (NOLOCK) ON ML.contractid = c.ContractID LEFT JOIN MixControl.dbo.LeadPerson LP WITH (NOLOCK) ON LP.LeadPersonID = ML.LeadPersonID LEFT JOIN RSLead.dbo.contracttype CCT WITH (NOLOCK) ON CCT.contracttypeno = L.contracttypeno LEFT JOIN RSLead.dbo.contracttype CCT1 WITH (NOLOCK) ON CCT1.contracttypeno = ML.contracttypeID LEFT JOIN #navisionUnitSerials zl ON U.Box_No = zl.box_no COLLATE DATABASE_DEFAULT LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No left join #tempCompanyReg tcr on cl.ClientID = tcr.ClientID left join RSlead.dbo.broker b1 on c.FitmentCentreID = cast(b1.brokerno as varchar(10)) left join RSlead.dbo.broker b2 on c.SalespersonID = b2.brokercode WHERE @debperiodno BETWEEN conserv.startperiod AND conserv.endperiod AND U.UnitTypeID = 7 --ca AND U.IsActive = 1 AND CA.IsActive = 1 AND c.IsActive = 1 GROUP BY C.ContractID, U.Box_No, A.RegistrationNumber, CL.AccountName, conserv.startperiod, C.CreateDate, conserv.accountno, C.salespersonid, b2.brokername, C.FitmentCentreID, b1.brokername, CASE WHEN l.contracttypeno = 2 THEN 'RENTAL CONTRACT' ELSE 'CASH CONTRACT' END, CCT.contracttypedesc, CCT1.contracttypedesc, aic.PolicyNumber, C.UniqueNumber, UT.UnitTypeDesc, ISNULL(l.leadno,ML.LEADID), ISNULL(l.brokerno,LP.BrokerID), ISNULL(B.brokercode,LP.ExternalPartnerCode), ISNULL(L.idno,LP.IDNumber), zl.MSISDN, LPT.CreateDate, a.ChassisNumber, tcr.CompanyRegistrationNumber, ISNULL(l.createopr,ML.CreateOpr), c.editOpr, ama.AssetMakeDesc, am.AssetModelDesc ; --============================================DISPLAY DATA============================================================= --RUNTIME AROUND 35S /* --compare to excel file SELECT [TYPE], COUNT(UnitId) AS Amount FROM AllUnitsReportDaily GROUP BY [TYPE] ORDER BY [TYPE]; SELECT * FROM AllUnitsReportDaily order by CONTRACTDATE DESC */ /* --Drop tables --=================================-- DROP TABLE #navisionUnitSerials; DROP TABLE #LastPassedTestDate; DROP TABLE #LastPassedTestDate_Init --=================================-- --SELECT * FROM AllUnitsReportDaily ORDER BY CONTRACTDATE DESC

Split Data

Posted: 01 Sep 2013 05:16 PM PDT

Hi All,I have written 3 queries and put them in 3 cte statements each is a different report. 1. FCR 2. FCP 3. DFPC. The code is putting each report after the other in one row, when I need each type under each other where the division and currency are the same.Current Data:Division,Code,FCR,jan,feb,mar,total,division,code,FCP,jan,feb,mar,total,division,code,DFPC,Jan,Feb,Mar,totalABCD SGD FCR -28901.4 71343.22 113375.79 155817.61 ABCD SGD FCP 0 50507.58 -2068.07 48439.51 ABCD SGD DFCP 0 0 0 0ACME HKD FCR 161.74 182.24 19963.11 20307.09 ACME HKD FCP 1875.94 2347.15 -4324.07 -100.98 ACME HKD DFCP 0 0 150.01 150.01ACME MYR FCR 0 0 150.01 150.01 ACME MYR FCP 195.85 -34239.96 -2754.33 -36798.44 ACME MYR DFCP 0 0 0 0[code="sql"];withcteFCR(DIVName1,CCYCODE,title,FCRJan,FCRFeb,FCRMar,total)AS (select d.DIVName1 ,c.CCYCode ,'Foreign Currency Receivables' as [Title] ,SUM(CASE WHEN r.ACRFSMID IN (128) THEN r.ACRBalanceBase ELSE 0 END) AS [January 13] ,SUM(CASE WHEN r.ACRFSMID IN (129) THEN r.ACRBalanceBase ELSE 0 END) AS [Feburary 13] ,SUM(CASE WHEN r.ACRFSMID IN (130) THEN r.ACRBalanceBase ELSE 0 END) AS [March 13] ,SUM(CASE WHEN r.ACRFSMID IN (128, 129, 130) THEN r.ACRBalanceBase ELSE 0 END) AS [Total]from vwAccountsReceivable rinner join vwDivision don r.ACRDIVID = d.DIVIDinner join vwCurrency con r.ACRTransactionCCYID = c.CCYIDgroup by d.DIVName1 ,c.CCYCODE),cteFCP(DIVName1,CCYCODE,title,FCPJan,FCPFeb,FCPMar,total)AS (select d.DIVName1 ,c.CCYCODE ,'Foreign Currency Payables' as [Title] ,SUM(CASE WHEN p.ACPFSMID IN (128) THEN p.ACPBalanceBase ELSE 0 END) AS [January 13] ,SUM(CASE WHEN p.ACPFSMID IN (129) THEN p.ACPBalanceBase ELSE 0 END) AS [Feburary 13] ,SUM(CASE WHEN p.ACPFSMID IN (130) THEN p.ACPBalanceBase ELSE 0 END) AS [March 13] ,SUM(CASE WHEN p.ACPFSMID IN (128, 129, 130) THEN '$' + (CONVERT(money,p.ACPBalanceBase,1))ELSE 0 END) AS [Total] from vwAccountsPayable pinner join vwDivision don p.ACPDIVID = d.DIVIDinner join vwCurrency con p.ACPTransactionCCYID = c.CCYIDgroup by d.DIVName1 ,c.CCYCODE),cteDFCP(DIVName1,CCYCODE,title,DFCPJan, DFCPFeb, DFCPMar,total)AS(Select ISNULL(pvt.[Division Buyer],0) as[DIVName1] ,pvt.currency as [CCYCode] ,'Draft Foreign Currency Payables' as [Title] ,ISNULL(pvt.[January],0) as [January] ,ISNULL(pvt.[Feburary],0) as [Feburary] ,ISNULL(pvt.[March],0) as [March] ,SUM(ISNULL(pvt.[January],0)+ ISNULL(pvt.[Feburary],0) + ISNULL(pvt.[March],0)) as [Total]from(SELECT h.optional_1 as [Division Buyer] ,currency ,DATENAME(Month,h.issuedate) as [Month] ,SUM(h.invoicetotal) as [Invoice Total] FROM Companies c JOIN Invoice_head h ON h.company_id = c.company_id JOIN Invoice_lines l ON l.invoice_id = h.invoice_id WHERE l.approvedate is null and h.company_id in (36,37,38,39,40,41,42,43,44,45,46,47,99,103,114) GROUP BY h.optional_1 ,currency ,h.issuedate)as screPIVOT (SUM([Invoice Total]) for [Month] IN( [January] ,[Feburary] ,[March] ))as pvtGROUP BY pvt.[January] ,pvt.[Feburary] ,pvt.[March] ,pvt.[Division Buyer] ,pvt.currency )select r.DIVName1 as [Division] ,r.CCYCODE as [Code] ,r.title as [Foreign Currency Receivables] ,r.FCRJan as [Jan] ,r.FCRFeb as [Feb] ,r.FCRMar as [Mar] ,r.total as [Total] ,p.DIVName1 as [Division] ,p.CCYCODE as [Code] ,p.title as [Foreign Currency Payables] ,p.FCPJan as [Jan] ,p.FCPFeb as[Feb] ,p.FCPMar as [Mar] ,p.total as [Total] ,d.DIVNAME1 as [Division] ,d.CCYCODE as [Code] ,d.title as [Draft Foreign Currency Payables] ,d.DFCPJan as [Jan] ,d.DFCPFeb as [Feb] ,d.DFCPMar as [Mar] ,d.total as [Total]from cteFCR rinner join cteFCP pon r.DIVName1 = p.DIVName1 and r.CCYCODE = p.CCYCODEinner join cteDFCP don r.DIVName1 = d.DIVName1 and r.CCYCODE = d.CCYCODEGROUP BY r.DIVName1 ,r.CCYCODE ,r.title ,r.FCRJan ,r.FCRFeb ,r.FCRMar ,r.total ,p.DIVName1 ,p.CCYCODE ,p.title ,p.FCPJan ,p.FCPFeb ,p.FCPMar ,p.total ,d.DIVNAME1 ,d.CCYCODE ,d.title ,d.DFCPJan ,d.DFCPFeb ,d.DFCPMar ,d.total[/code]Expected:Division, Currency, Report, Jan, Feb, Mar, TotalABCD SGD FCR -28901.4 71343.22 113375.79 155817.61ABCD SGD FCP 0 50507.58 -2068.07 48439.51ABCD SGD DFCP 0 0 0 0ACME HKD FCR 161.74 182.24 19963.11 20307.09ACME HKD FCP 1875.94 2347.15 -4324.07 -100.98ACME HKD DFCP 0 0 150.01 150.01ACME MYR FCR 0 0 150.01 150.01ACME MYR FCP 195.85 -34239.96 -2754.33 -36798.44ACME MYR DFCP 0 0 0 0Thanks for any help.

sql query

Posted: 01 Sep 2013 08:15 AM PDT

Hello friends,Please help me in writing a query!!Question:Write a query based off of the following data stored in the AccountingEntry table. The query needs to return running totals and should produce the exact results for each customer listed below.Deposits – amounts added to an accountCharge – amounts should be subtracted from a depositCancel/Refund – amount returned to a customer, should be subtracted from a depositAccountingEntryID CustomerID Date Type Amount 1 1 1/1/2013 Deposit 400 2 3 1/23/2013 Deposit 900 3 19 2/28/2013 Deposit 250 4 23 3/15/2013 Charge 175 5 1 2/1/2013 Charge 350 6 15 4/1/2013 Deposit 2000 7 3 2/23/2013 Charge 500 8 15 4/1/2013 Charge 100 9 1 2/23/2013 Deposit 100 10 23 3/15/2013 Charge 175 11 15 4/1/2013 Charge 750 12 1 2/15/2013 Charge 25 13 15 4/1/2013 Cancel/Refund 1150 14 3 1/25/2013 Deposit 100 15 15 4/1/2013 Deposit 750 16 19 3/28/2013 Charge 100 17 15 4/1/2013 Deposit 100 18 3 3/23/2013 Charge 500 19 23 3/15/2013 Deposit 400 20 19 4/28/2013 Charge 100 21 1 3/15/2013 Cancel/Refund 125 Results should look like AccountingEntryID CustomerID Date Type Amount Running Deposit RunningAmount Charged RunningAmt Remaining1 1 1/1/2013 Deposit 400 400 0 4005 1 2/1/2013 Charge 350 400 350 5012 1 2/15/2013 Charge 25 400 375 259 1 2/23/2013 Deposit 100 500 375 12521 1 3/15/2013 Cancel/Refund 125 500 375 0Thanks,RT

Query

Posted: 01 Sep 2013 01:17 PM PDT

I need a T sql query which should read and load text files data from the directory one file after another file and load the file data into a table.Thanks for your help in advance
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog