Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

120 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

3873 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  
 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.03 seconds. Powered By: Snitz Forums 2000