how i can fix this problem . it happened due change in computer clock
What is the problem exactly? The date is missing?
Show your Report Template.
>Day|Ticket Sales|Discount|Discount %|Service|Tax|Total|Cash|Credit|Officer|C/L|Comp
{REPORT TICKET DETAILS:W.Range,([T.TotalAmount]-[CA.Discount]-[T.Tax]-[CA.Service 12%]-[CA.Discount %]).sum,CA.Discount.sum,CA.Discount %.sum,CA.Service 12%.sum,T.Tax.sum,T.TotalAmount.sum,PA.Cash.sum,PA.Credit Card.sum,PA.Officer.sum,PA.C/L.sum,PA.Complimentary.sum:(TS.Status=Paid)}
>Total |{REPORT TICKET DETAILS:([T.TotalAmount]-[CA.Discount]-[T.Tax]-[CA.Service 12%]-[CA.Discount %]).sum,CA.Discount.sum,CA.Discount %.sum,CA.Service 12%.sum,T.Tax.sum,T.TotalAmount.sum,PA.Cash.sum,PA.Credit Card.sum,PA.Officer.sum,PA.C/L.sum,PA.Complimentary.sum:(TS.Status=Paid)}
the time was’t correct due windows auto time update , tho those orders orders before work period.
the work period started at 11 pm during that windows changed time to 9 pm then i switched back 11pm.
that is why it dosen’t show the date
Why would Windows change your time? Maybe you should turn that off or ensure you configured it for your area. Windows doesn’t just change your time to a false time.
You will need to find the records for those Tickets in the DB and manually correct the Date/Time.
It might be as simple as making updates to the [Tickets] table, but may also involve updating Transaction tables.
This SELECT statement should find all the records in the related Tables:
SELECT
t.[Id] as [TicketID]
,[TicketNumber]
,t.[Date] as [TicketDate]
,[LastUpdateTime] as [TicketUpdateDate]
,[LastOrderDate] as [TicketLastOrderDate]
,[LastPaymentDate] as [TicketPaymentDate]
,[TransactionDocument_Id] as [DocID]
,d.[Date] as [DocDate]
,tx.[Id] as [TxID]
,tv.[Date] as [TxvalDate]
,o.[CreatedDateTime] as [OrderCreateDate]
,o.[LastUpdateDateTime] as [OrderUpdateDate]
FROM [Tickets] t
JOIN [AccountTransactionDocuments] d on d.[Id] = t.[TransactionDocument_Id]
JOIN [AccountTransactions] tx on tx.AccountTransactionDocumentId = d.[Id]
JOIN [AccountTransactionValues] tv on tv.[AccountTransactionId] = tx.[Id] and tv.[AccountTransactionDocumentId] = d.[Id]
JOIN [Orders] o on o.[TicketId] = t.[Id]
WHERE 1=1
AND t.[Date] > '2016-03-04 23:59'
AND t.[Date] < '2016-03-06 12:00'
1 Like
The reason I asked about the time is because you need to fix that if its what is causing this or it will happen again.
Windows is usually configured to automatically update Time based on your region, mostly for setting the clock when switching between Standard and Daylight Savings. I expect this is what happened.
1 Like
Yeah to fix it he should ensure his region settings are correct and turn on or off daylight savings feature.
i found the records , how i can change em ? change the time to the correct time
I want to help you to help yourself.
Post the results (screenshot from SSMS) of the query showing the 5 Tickets that are out of range.
Then I can give you a set of UPDATE queries to fix those records.
2 Likes
Yes, give me some time to construct the SQL UPDATE statements.
1 Like
Ok, this should do the trick…
BACKUP THE DATABASE before you run this.
There is no way to reverse this, short of restoring from a backup.
The very first DECLARE statement should be set to a Date/Time that is within the period you are trying to adjust. Change this date/time if necessary, but I think the value provided should be ok.
Unfortunately, this is going to clump all of these Tickets into a single date/time so any “hourly sales reports” or similar will be slightly skewed, but so be it.
-- set this date/time to a value that falls within the "bad" period
DECLARE @newDate datetime = '2016-03-05 23:59'
UPDATE [Tickets] SET [Date] = @newDate WHERE [Id] between 13693 and 13697
UPDATE [Tickets] SET [LastUpdateTime] = @newDate WHERE [Id] between 13693 and 13697
UPDATE [Tickets] SET [LastOrderDate] = @newDate WHERE [Id] between 13693 and 13697
UPDATE [Tickets] SET [LastPaymentDate] = @newDate WHERE [Id] between 13693 and 13697
UPDATE [Orders] SET [CreatedDateTime] = @newDate WHERE [TicketId] between 13693 and 13697
UPDATE [Orders] SET [LastUpdateDateTime] = @newDate WHERE [TicketId] between 13693 and 13697
UPDATE [AccountTransactionValues] SET [Date] = @newDate WHERE [AccountTransactionDocumentId] between 13693 and 13697
UPDATE [AccountTransactionDocuments] SET [Date] = @newDate WHERE [Id] between 13693 and 13697
There may be relation restrictions on the DB that could cause errors to appear when running this. If you see any errors, please post them here. They will appear in the Messages tab in the Results pane in SSMS.
Even if there are no errors generated, please post the contents of the Messages tab so we can see how many records were affected/updated. They look like this:
(5 row(s) affected) -- Tickets
(5 row(s) affected) -- Tickets
(5 row(s) affected) -- Tickets
(5 row(s) affected) -- Tickets
(0 row(s) affected) -- Orders (depends on the number of Orders in the Tickets)
(0 row(s) affected) -- Orders (depends on the number of Orders in the Tickets)
(0 row(s) affected) -- AccountTransactionValues (depends on Transaction Types applied to the Tickets)
(5 row(s) affected) -- AccountTransactionDocuments (depends on Document Types applied to the Tickets)
1 Like
