wrong system date

I hope it's not too late, but i just started to check this newsgroup I highly recommend that u test this statement first on testing database, then run it on the live databse if things go fine

--**********BE CAREFULE WHILE USING THIS QUERY*************

--************IT MAY DESTROY YOUR SALES DATA***************

--***********MAKE SURE TO USE THE RIGHT DATES**************

declare @RightDate datetime declare @WrongDate datetime set @rightdate='2006-08-25 00:00:00' set @Wrongdate='2006-09-25 00:00:00'

--Transaction table update [transaction] set time = convert(char(10),@rightdate,120) + ' ' + convert(char(8),time,8) where convert(char(10),@wrongdate,120)=convert(char(10),time,120)

--and [transaction].transactionnumber between x and y -- to dbl check

--DailySales

--By Supplier update DailySales set DailySales.Total=MyTab.MyTot from (select SupplierID,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by SupplierID) as MyTab where MyTab.SupplierIDÚilySales.TypeID and DailySales.Type=1 and date= @rightdate

--By Department update DailySales set DailySales.Total=MyTab.MyTot from (select departmentid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by departmentid) as MyTab where MyTab.DepartmentIDÚilySales.TypeID and DailySales.Type=3 and date= @rightdate

--By Category update DailySales set DailySales.Total=MyTab.MyTot from (select Categoryid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by Categoryid) as MyTab where MyTab.CategoryIDÚilySales.TypeID and DailySales.Type=2 and date= @rightdate

--By Register update DailySales set DailySales.Total=MyTab.MyTot from (select Registerid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item,batch where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and batch.batchnumber=[Transaction].batchnumber and time >= @rightdate and time < dateadd(day,1,@rightdate) group by Registerid) as MyTab where MyTab.RegisterIDÚilySales.TypeID and DailySales.Type=4 and date= @rightdate

--By Cashier update DailySales set DailySales.Total=MyTab.MyTot from (select Cashierid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by Cashierid) as MyTab where MyTab.CashierIDÚilySales.TypeID and DailySales.Type=5 and date= @rightdate

--By Tax update DailySales set DailySales.Total=MyTab.MyTot from (select Taxid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by Taxid) as MyTab where MyTab.TaxIDÚilySales.TypeID and DailySales.Type=7 and date= @rightdate

--By Sales Rep. update DailySales set DailySales.Total=MyTab.MyTot from (select sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate)) as MyTab where DailySales.Type=6 and date= @rightdate

--Remove wrong data from DailySales DELETE FROM DailySales WHERE Date=@WrongDate

"tom" wrote:

I hope it's not too late, but i just started to check this newsgroup I highly recommend that u test this statement first on testing database, then run it on the live databse if things go fine

--**********BE CAREFULE WHILE USING THIS QUERY*************

--************IT MAY DESTROY YOUR SALES DATA***************

--***********MAKE SURE TO USE THE RIGHT DATES**************

declare @RightDate datetime declare @WrongDate datetime set @rightdate='2006-08-25 00:00:00' set @Wrongdate='2006-09-25 00:00:00'

--Transaction table update [transaction] set time = convert(char(10),@rightdate,120) + ' ' + convert(char(8),time,8) where convert(char(10),@wrongdate,120)=convert(char(10),time,120)

--and [transaction].transactionnumber between x and y -- to dbl check

--DailySales

--By Supplier update DailySales set DailySales.Total=MyTab.MyTot from (select SupplierID,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by SupplierID) as MyTab where MyTab.SupplierIDÚilySales.TypeID and DailySales.Type=1 and date= @rightdate

--By Department update DailySales set DailySales.Total=MyTab.MyTot from (select departmentid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by departmentid) as MyTab where MyTab.DepartmentIDÚilySales.TypeID and DailySales.Type=3 and date= @rightdate

--By Category update DailySales set DailySales.Total=MyTab.MyTot from (select Categoryid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by Categoryid) as MyTab where MyTab.CategoryIDÚilySales.TypeID and DailySales.Type=2 and date= @rightdate

--By Register update DailySales set DailySales.Total=MyTab.MyTot from (select Registerid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item,batch where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and batch.batchnumber=[Transaction].batchnumber and time >= @rightdate and time < dateadd(day,1,@rightdate) group by Registerid) as MyTab where MyTab.RegisterIDÚilySales.TypeID and DailySales.Type=4 and date= @rightdate

--By Cashier update DailySales set DailySales.Total=MyTab.MyTot from (select Cashierid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by Cashierid) as MyTab where MyTab.CashierIDÚilySales.TypeID and DailySales.Type=5 and date= @rightdate

--By Tax update DailySales set DailySales.Total=MyTab.MyTot from (select Taxid,sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate) group by Taxid) as MyTab where MyTab.TaxIDÚilySales.TypeID and DailySales.Type=7 and date= @rightdate

--By Sales Rep. update DailySales set DailySales.Total=MyTab.MyTot from (select sum(transactionentry.quantity*transactionentry.price) as MyTot from [transaction],transactionentry,item where [transaction].transactionnumber=transactionentry.transactionnumber and item.id=itemid and time >= @rightdate and time < dateadd(day,1,@rightdate)) as MyTab where DailySales.Type=6 and date= @rightdate

--Remove wrong data from DailySales DELETE FROM DailySales WHERE Date=@WrongDate

"tom" wrote:

Jeff,

Please make sure that the wrong date is in the future coz this is what i suppose in my script. If the wrong date is in the past, you must include the transaction number in the transaction table statement, remove the last DailySales statement & replace it with similar DailySales statement to recalculate the totals of the wrong date.

i just realised that this script will not work correctly if you have sales with the wr> Nashat,

for a customer.

Jeff,

Thank u for the tip, can u plz tell me about them? Yes, i'm new to this newsgroup and i know nothing about the reseller newsgroup, i dunno even if i can access them or not. I've just worked as consultant for a reseller 2 months ago! maybe this is the reason i started exploring areas that i never looked at before while working as Application (RMS) Administrator in a supermarket for more than 6 years.

Nashat

"Jeff" wrote:

work. Customer went to a tradeshow last weekend, ran in offline mode and his date was set to 01/01/80.

have you looked at the 2 reseller newsgroups?

too for a customer.

convert(char(8),time,8)

Cashierid,sum(transactionentry.quantity*transactionentry.price)

Jeff,

Thank u so much for your help, I'm located > Nashat,

the web interface, ugh!!. Instructions on how to setup Outlook Express or other newsgroup readers are in the .doc file on the right. After your click on whichever newgroup, look for Retail Management System on the left.

it work. Customer went to a tradeshow last weekend, ran in offline mode and his date was set to 01/01/80.

reseller, have you looked at the 2 reseller newsgroups?

this too for a customer.

convert(char(8),time,8)

Cashierid,sum(transactionentry.quantity*transactionentry.price)

Taxid,sum(transactionentry.quantity*transactionentry.price) as

Cashierid,sum(transactionentry.quantity*transactionentry.price)