SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 script help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

113 Posts

Posted - 04/25/2013 :  11:02:46  Show Profile  Reply with Quote
I have all these scripts I need to run to create and group all the data. I was wondering if I can take all of them and create a bat file that a double click on to run it for me. Here is the code below.




1st Script InsertTables

INSERT INTO HBStatements ([Cust-no],[From-cust],PreBalance,Credit,Debit,budgetpayment,[Original-amt],Baldue,[Seq-no],[Cust-po],date,[Trans-code],Reference,Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,MainID,BudgetAmount,Memo,[Order-Code])

select
[Cust-no],[From-cust],Null AS PreBalance,Credit,Debit,budgetpayment,[Original-amt],Baldue,[Seq-no],[Cust-po],date,[Trans-code],Reference,
Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,MainID,Null AS BalanceAmount,Null AS Memo,[Order-Code]
from TransBalAfter

UNION ALL

select
[Cust-no],[From-cust],PreBalance,Credit,Debit,Null AS budgetpayment,NULL AS [Original-amt],NULL AS Baldue,NULL AS [Seq-no],NULL AS [Cust-po],date,NULL AS [Trans-code],NULL AS Reference,Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,MainID,Null AS BalanceAmount,Memo, Null AS [Order-Code]
FROM TransPrebal

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2nd Script FromNEWCustUpdateSite, This updates the inserted data so the SiteName, SiteAddress, SiteCity, SiteState, and SiteZip are Tied to the From-cust not equal to Cust-no.

SELECT msd.[From-cust], msd.[Cust-no], msd.PreBalance, msd.[From-cust], msd.Reference, msd.budgetpayment, msd.[Original-amt], msd.Baldue, msd.[Seq-no], msd.Memo, msd.[Cust-po], msd.date, msd.[Trans-code], msd.Credit, msd.Debit,
msd.[Misc-Code], msd.ChargeName, msd.ChargeAdd, msd.ChargeCity, msd.ChargeSt, msd.ChargeZip, msd.SiteName, msd.SiteAdd, msd.SiteCity, msd.SiteState, msd.SiteZip
FROM Service.dbo.HBStatements msd
WHERE (msd.[From-cust] <> msd.[Cust-no])

update msd
set
msd.SiteName = c.name,
msd.SiteAdd = c.address,
msd.SiteCity = c.City,
msd.SiteState = c.St,
msd.SiteZip = c.[Zip-Code]

From
Service.dbo.HBStatements msd
inner join Service.dbo.customer c on c.[Cust-no] = msd.[From-cust]
WHERE (msd.[From-cust] <> msd.[Cust-no])

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3rd Script FromNEWCustUpdateCust, This updates the inserted data so the ChargeName, ChargeAddress, ChargeCity, ChargeState, and ChargeZip are Tied to the Cust-no not equal to From-cust.



SELECT msd.[From-cust], msd.[Cust-no], msd.PreBalance, msd.[From-cust], msd.Reference, msd.budgetpayment, msd.[Original-amt], msd.Baldue, msd.[Seq-no], msd.Memo, msd.[Cust-po], msd.date, msd.[Trans-code], msd.Credit, msd.Debit,
msd.[Misc-Code], msd.ChargeName, msd.ChargeAdd, msd.ChargeCity, msd.ChargeSt, msd.ChargeZip, msd.SiteName, msd.SiteAdd, msd.SiteCity, msd.SiteState, msd.SiteZip
FROM Service.dbo.HBStatements msd
WHERE (msd.[From-cust] <> msd.[Cust-no])

update msd
set msd.ChargeName = c.name,
msd.ChargeAdd = c.address,
msd.ChargeCity = c.City,
msd.ChargeSt = c.St,
msd.ChargeZip = c.[Zip-code]
From
Service.dbo.HBStatements msd
inner join Service.dbo.customer c on c.[Cust-no] = msd.[Cust-no]
WHERE (msd.[From-cust] <> msd.[Cust-no])


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

4th Script FromEQCustUpdate, This updates all Site and Charge fields that are equal From-cust = Cust-no


SELECT msd.[From-cust], msd.[Cust-no], msd.PreBalance, msd.[From-cust], msd.Reference, msd.budgetpayment, msd.[Original-amt], msd.Baldue, msd.[Seq-no], msd.Memo, msd.[Cust-po], msd.date, msd.[Trans-code], msd.Credit, msd.Debit,
msd.[Misc-Code], msd.ChargeName, msd.ChargeAdd, msd.ChargeCity, msd.ChargeSt, msd.ChargeZip, msd.SiteName, msd.SiteAdd, msd.SiteCity, msd.SiteState, msd.SiteZip
FROM Service.dbo.HBStatements msd
WHERE (msd.[From-cust] = msd.[Cust-no])

update msd
set msd.ChargeName = c.name,
msd.ChargeAdd = c.address,
msd.ChargeCity = c.City,
msd.ChargeSt = c.St,
msd.ChargeZip = c.[Zip-code],
msd.SiteName = c.name,
msd.SiteAdd = c.address,
msd.SiteCity = c.City,
msd.SiteState = c.St,
msd.SiteZip = c.[Zip-Code]

From
Service.dbo.HBStatements msd
inner join Service.dbo.customer c on c.[Cust-no] = msd.[From-cust]
WHERE (msd.[From-cust] = msd.[Cust-no])


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


5th Script Delete Nulls, this is where we are taking the Prebalance and debit and credits and removing the ones that are null/0 in all 3 columns.

DELETE
FROM HBStatements
WHERE PreBalance = '0' AND Credit IS Null AND Debit IS Null


DELETE
FROM HBStatements
WHERE Baldue = '0' AND Credit IS Null AND Debit IS Null

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6th Script Delete Misc-code, This Script removes all of the unwanted Misc-codes Rows we do not want to report on in our statement.

DELETE
FROM HBStatements
WHERE [Misc-code]= 'COM' OR [Misc-code] = 'CLCT' OR [Misc-code] = 'IND' OR [Misc-code] = 'UNCL'


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7th Script Update Budget Account This lets us pull from the Sabudget the prd and the amount they owe as a budget customer.

UPDATE
Service.dbo.HBStatements
SET
Service.dbo.HBStatements.BudgetAmount = Service.dbo.SaBudget.Amount
FROM HBStatements INNER JOIN
SaBudget ON HBStatements.[Cust-no] = SaBudget.[Charge-cust]
WHERE (SaBudget.[sa-prd] = '05')
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8th Script Import Memo Message

update msd
set
msd.Message = c.Message

From
Service.dbo.HBStatements msd
inner join Service.dbo.Comments c on c.[Misc-Code] = msd.[Misc-Code]
---------------------------------------------------------------------------
9th Script Update Memo field for Transaction Type P%
update msd
set
msd.Memo = c.Memo

From
Service.dbo.HBStatements msd
inner join Service.dbo.hbdescription c on c.[Trans-code] = msd.[Trans-Code]
WHERE msd.[Trans-code] LIKE 'P%'
---------------------------------------------------------------------------
10th Script update Memo Transaction Types C's, D, F, and I's

update msd
set
msd.Memo = c.Memo

From
Service.dbo.HBStatements msd
inner join Service.dbo.hbdescription c on c.[Trans-code] = msd.[Trans-Code]
WHERE (msd.[Trans-code] = 'C')OR(msd.[Trans-code] = 'CA') OR
(msd.[Trans-code] = 'CJ') OR
(msd.[Trans-code] = 'CS') OR
(msd.[Trans-code] = 'DP') OR
(msd.[Trans-code] = 'F') OR
(msd.[Trans-code] = 'I') OR
(msd.[Trans-code] = 'IA') OR
(msd.[Trans-code] = 'IJ')

---------------------------------------------------------------------------
10th Script Update Memo for transactions in IS that have a Order Code
update msd
set
msd.Memo = c.Memo

From
Service.dbo.HBStatements msd
inner join Service.dbo.hbdescription c on c.[Order-code] = msd.[Order-Code]
WHERE (msd.[Trans-code] = 'IS')

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/25/2013 :  21:38:43  Show Profile  Reply with Quote
You can use sqlcmd if you have it on your machine http://msdn.microsoft.com/en-us/library/ms170572.aspx Another option would be to use Powershell and use Invoke-SqlCmd http://technet.microsoft.com/en-us/library/cc281720.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000