|
reignheart05
Starting Member
3 Posts |
 Posted - 12/06/2012 : 05:23:24
|
DROP TABLE SalesData;
-- use database
-- create table
CREATE TABLE SalesData
(
--P_Id int,
[Store No_] varchar(255),
);
DECLARE @vFrom Datetime;
DECLARE @vTo Datetime;
DECLARE @vStr nvarchar(500);
DECLARE @Query nvarchar(500);
--set your dates from here
SET @vFrom = '20121127';
SET @vTo = '20121205';
WHILE @vFrom < @vTo
BEGIN
SET @vStr = Replace(convert(varchar,@vFrom,102),'.','')
PRINT @vStr;
SET @Query = 'ALTER TABLE dbo.SalesData ADD "' + @vStr + '" decimal(16,2) DEFAULT 0.00';
EXEC(@Query);
Set @vFrom = DATEADD(day,1,@vFrom)
END;
--insert of dates columns first
INSERT INTO SalesData ([Store No_]) (Select [No_] FROM [db].[dbo].[sTORE] Group By [No_]);
--set your dates here same as the top
SET @vFrom = '20121127';
SET @vTo = '20121205';
WHILE @vFrom < @vTo
BEGIN
SET @vStr = Replace(convert(varchar,@vFrom,102),'.','')
PRINT @vStr;
SET @QUERY = 'UPDATE SalesData
SET ['+@vStr+'] =
(Select SUM([Net Amount])
FROM [db].[dbo].[Transaction Header]
WHERE [Date] =
''' +@vstr+ '''
Group By [Store No_])
WHERE [Store No_] =
(Select [Store No_]
FROM [db].[dbo].[Transaction Header]
WHERE [Date] = '''+@vstr+''' Group By [Store No_])';
--print @query;
EXEC(@QUERY);
Set @vFrom = DATEADD(day,1,@vFrom);
END;
Select * from SalesData ORDER BY [Store No_];
in sqlserver 2008 this query works but at 2005 i encountered this problem.. and i dont know how to solve it. can anyone help me out? thanks
the error begins at set update field 1 = subquery
where = subquery |
Edited by - reignheart05 on 12/06/2012 05:28:19
|
|
problem with update set subquery
