updating statement with incorrect dates.

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

General SQL Server Forums

New to SQL Server Programming

updating statement with incorrect dates.

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-06-03 : 17:29:23

Hello there. I have the following update statement

with cte_Trans

as (

select transactionid, purchaseID, MIN(convert(date,TransactionPurchaseDate)) as 'TransactionPurchaseDate'
from dbo.Ticket_History
where TransactionMOP <> 0
------------and BookingID = 542066
group by transactionid, purchaseID )

update t
set t.TransactionSoldDate = convert(date,Trans.TransactionPurchaseDate)
from dbo.Ticket_Facts t inner join cte_trans trans on
t.transactionid = trans.TransactionID

when I select * tickets from our ticket table with
where bookingid = 542066

then I get the following

transactionpurchasedate TransactionSoldDate
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-04-30
2013-05-01 2013-05-01
2013-05-01 2013-05-01
2013-05-01 2013-05-01

see the date that is 2013-04-30 when it should be 2013-05-01

what sort of things would cause this,

BUT if I run the update statement with a criteria of

where bookingid = 542066 then I don't get any problem.

very strange,

Regards

Rob

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-03 : 17:42:18

Perhaps you have more than one booking id for a TransactionID and purchaseID combination? It's a wild guess, but you can check if that is so using this query. If it returns any rows at all, then you do:

with cte_Trans

as (

select transactionid, purchaseID, bookingID,
MIN(convert(date,TransactionPurchaseDate)) as 'TransactionPurchaseDate'
from dbo.Ticket_History
where TransactionMOP <> 0
------------and BookingID = 542066
group by transactionid, purchaseID , bookingID) 
SELECT transactionid, purchaseID, COUNT(*)
FROM cte_Trans GROUP BY transactionid, purchaseID 
HAVING COUNT(*) > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources