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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Add Fields to a table

Author  Topic 

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-03 : 14:03:53
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

52326 Posts

Posted - 2013-04-03 : 14:20:31
[code]
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
[/code]

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

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-03 : 16:03:07
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

352 Posts

Posted - 2013-04-03 : 20:37:30
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-04 : 01:12:33
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
   

- Advertisement -