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)
 Add Fields to a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

109 Posts

Posted - 04/03/2013 :  14:03:53  Show Profile  Reply with Quote
I have a table that I used a select statement with but I need to add fields one is date and the other is Memo. The Date field is a repeatable static field 2/28/2013 and the Memo Field says Previous Balance. Below is the code I have for the select part.

SELECT
CASE WHEN SUM(dbo.TransARView.Baldue) < 0 THEN SUM(dbo.TransARView.Baldue) END AS Credit,
CASE WHEN SUM(dbo.TransARView.Baldue)> 0 THEN SUM(dbo.TransARView.Baldue) END AS Debit,
[From-cust], [Cust-no], [Misc-Code],
ChargeName, ChargeAdd, ChargeCity,
ChargeSt, ChargeZip, SiteName,
SiteAdd, SiteCity, SiteState, SiteZip


FROM dbo.TransARView


GROUP BY
[From-cust], [Cust-no], [Misc-Code], ChargeName, ChargeAdd, ChargeCity, ChargeSt, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/03/2013 :  14:20:31  Show Profile  Reply with Quote

SELECT 
CASE WHEN SUM(dbo.TransARView.Baldue) < 0 THEN SUM(dbo.TransARView.Baldue) END AS Credit, 
CASE WHEN SUM(dbo.TransARView.Baldue)> 0 THEN SUM(dbo.TransARView.Baldue) END AS Debit, 
[From-cust], [Cust-no], [Misc-Code], 
ChargeName, ChargeAdd, ChargeCity, 
ChargeSt, ChargeZip, SiteName, 
SiteAdd, SiteCity, SiteState, SiteZip,
'20130228' AS [Date],
'Previous Balance' AS Memo

FROM dbo.TransARView


GROUP BY 
[From-cust], [Cust-no], [Misc-Code], ChargeName, ChargeAdd, ChargeCity, ChargeSt, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

hbadministrator
Posting Yak Master

109 Posts

Posted - 04/03/2013 :  16:03:07  Show Profile  Reply with Quote
I want to permanently insert that into the table for these records the query pulls. and date I wanted as 02/28/2013. Sorry for the confusion.
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
350 Posts

Posted - 04/03/2013 :  20:37:30  Show Profile  Reply with Quote
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Date')
ALTER TABLE dbo.TransARView ADD Date DateTime NULL
Go

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Memo')
ALTER TABLE dbo.TransARView ADD Memo nvarchar(50) NULL
Go

INSERT INTO TransARView
([Date], [Memo])
SELECT '2/28/2013 00:00:00', 'Previous Balance'
GO


SELECT
CASE WHEN SUM(dbo.TransARView.Baldue) < 0 THEN SUM(dbo.TransARView.Baldue) END AS Credit,
CASE WHEN SUM(dbo.TransARView.Baldue)> 0 THEN SUM(dbo.TransARView.Baldue) END AS Debit,
[From-cust], [Cust-no], [Misc-Code],
ChargeName, ChargeAdd, ChargeCity,
ChargeSt, ChargeZip, SiteName,
SiteAdd, SiteCity, SiteState, SiteZip, [Date], Memo

FROM dbo.TransARView


GROUP BY
[From-cust], [Cust-no], [Misc-Code], ChargeName, ChargeAdd, ChargeCity, ChargeSt, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip, Date, Memo

Edited by - harlingtonthewizard on 04/03/2013 20:39:26
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/04/2013 :  01:12:33  Show Profile  Reply with Quote
quote:
Originally posted by harlingtonthewizard

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Date')
ALTER TABLE dbo.TransARView ADD Date DateTime NULL
Go

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Memo')
ALTER TABLE dbo.TransARView ADD Memo nvarchar(50) NULL
Go

INSERT INTO TransARView
([Date], [Memo])
SELECT '2/28/2013 00:00:00', 'Previous Balance'
GO


SELECT
CASE WHEN SUM(dbo.TransARView.Baldue) < 0 THEN SUM(dbo.TransARView.Baldue) END AS Credit,
CASE WHEN SUM(dbo.TransARView.Baldue)> 0 THEN SUM(dbo.TransARView.Baldue) END AS Debit,
[From-cust], [Cust-no], [Misc-Code],
ChargeName, ChargeAdd, ChargeCity,
ChargeSt, ChargeZip, SiteName,
SiteAdd, SiteCity, SiteState, SiteZip, [Date], Memo

FROM dbo.TransARView


GROUP BY
[From-cust], [Cust-no], [Misc-Code], ChargeName, ChargeAdd, ChargeCity, ChargeSt, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip, Date, Memo




it should be an update


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Date')
ALTER TABLE dbo.TransARView ADD Date DateTime NULL
Go

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Memo')
ALTER TABLE dbo.TransARView ADD Memo nvarchar(50) NULL
Go


UPDATE TransARView
SET [Date]= '20130228', 
[Memo])='Previous Balance'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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