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.
Author |
Topic |
cusoxty
Constraint Violating Yak Guru
271 Posts |
Posted - 2004-06-23 : 11:43:56
|
I am trying to compare the data from one table (shipments) with the data from a view. The point of the function is to match the correct territory with the account depending on the data. Basically, I this deals with accounts that are transfering from one territory to another. The transfers take effect on the first day of the month, retroactive, therefore we need to allocate the sales data to the correct territory in the shipments table. Here is my function. Can someone tell me how I can get this to work and insert a territory ID for the account that has transfered into the shipments table?CREATE FUNCTION fnShipments ()RETURNS @Shipments TABLE (AccountID CHAR(10), DateInvoice DateTime, DollarShipments Money, TerritoryID CHAR(10)) AS BEGIN INSERT @Shipments (AccountID, DateInvoice, DollarShipments, TerritoryID)SELECT Shipments.AccountID, Shipments.DateInvoice, DollarShipments, ISNULL((SELECT TerritoryID FROM vwAccountTransfersWHERE Shipments.AccountID = vwAccountTransfers.AccountIDAND vwAccountTransfers.EffectiveMonth =(SELECT MIN(EffectiveMonth)FROM vwAccountTransfersWHERE Shipments.AccountID = vwAccountTransfers.AccountIDAND DatePart(m,Shipments.DateInvoice) < vwAccountTransfers.EffectiveMonth)),(SELECT TerritoryID FROM vwAccountTransfersWHERE Shipments.AccountID = vwAccountTransfers.AccountIDAND vwAccountTransfers.EffectiveMonth Is Null )) AS TerritoryFROM ShipmentsORDER BY Shipments.AccountID, Shipments.DateInvoice;RETURNEND |
|
|
|
|
|
|