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, SiteZipFROM dbo.TransARViewGROUP 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 MemoFROM dbo.TransARViewGROUP BY [From-cust], [Cust-no], [Misc-Code], ChargeName, ChargeAdd, ChargeCity, ChargeSt, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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 NULLGoIF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Memo')ALTER TABLE dbo.TransARView ADD Memo nvarchar(50) NULLGoINSERT INTO TransARView([Date], [Memo])SELECT '2/28/2013 00:00:00', 'Previous Balance'GOSELECT 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], MemoFROM dbo.TransARViewGROUP BY [From-cust], [Cust-no], [Misc-Code], ChargeName, ChargeAdd, ChargeCity, ChargeSt, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip, Date, Memo |
|
|
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 NULLGoIF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Memo')ALTER TABLE dbo.TransARView ADD Memo nvarchar(50) NULLGoINSERT INTO TransARView([Date], [Memo])SELECT '2/28/2013 00:00:00', 'Previous Balance'GOSELECT 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], MemoFROM dbo.TransARViewGROUP BY [From-cust], [Cust-no], [Misc-Code], ChargeName, ChargeAdd, ChargeCity, ChargeSt, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip, Date, Memo
it should be an updateIF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Date')ALTER TABLE dbo.TransARView ADD Date DateTime NULLGoIF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TransARView' AND COLUMN_NAME='Memo')ALTER TABLE dbo.TransARView ADD Memo nvarchar(50) NULLGoUPDATE TransARViewSET [Date]= '20130228', [Memo])='Previous Balance' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|