Query or SP question
-
I am trying to determine if a job transfers and sells again what the previous job was...I currently have it so:
Fname Lname Cust_ID LotID SaleDate CancelDate IsTransfer Betty Botox 12 1 3/23/04 4:40 PM 3/23/04 4:42 PM 0 Betty Botox 12 1 3/30/04 8:36 AM 3/30/04 8:44 AM 0 Betty Botox 12 6 3/30/04 9:14 AM 3/31/04 12:12 PM 1 Betty Botox 12 4 3/31/04 2:46 PM 3/31/04 3:36 PM 1 Betty Botox 12 9 3/31/04 3:38 PM NULL 0 bill smith 13 21 3/22/04 9:32 AM NULL 0 Boy George 33 33 3/23/04 4:11 PM NULL 0 bruce wayne 22 2112 3/15/04 8:57 AM 3/15/04 9:04 AM 0 bruce wayne 22 2112 3/15/04 9:57 AM 3/16/04 2:51 PM 0 Carol Douis 32 2323 3/31/04 8:53 AM NULL 0 John Schnieder 33 5435 3/30/04 8:42 AM NULL 0 Kris Lee 31 2343 3/19/04 9:55 AM NULL 0 Kristi Fioca 21 3323 3/30/04 8:42 AM NULL 0 Lyn Smith 333 3232 3/31/04 8:54 AM NULL 0 I need to have it such:
Fname Lname Cust_ID LotID SaleDate CancelDate IsTransfer TransferredFromLotID Betty Botox 12 1 3/23/04 4:40 PM 3/23/04 4:42 PM 0 Null Betty Botox 12 1 3/30/04 8:36 AM 3/30/04 8:44 AM 0 Null Betty Botox 12 6 3/30/04 9:14 AM 3/31/04 12:12 PM 1 Null Betty Botox 12 4 3/31/04 2:46 PM 3/31/04 3:36 PM 1 6 Betty Botox 12 9 3/31/04 3:38 PM NULL 0 4 bill smith 13 21 3/22/04 9:32 AM NULL 0 Null Boy George 33 33 3/23/04 4:11 PM NULL 0 Null bruce wayne 22 2112 3/15/04 8:57 AM 3/15/04 9:04 AM 0 Null bruce wayne 22 2112 3/15/04 9:57 AM 3/16/04 2:51 PM 0 Null Carol Douis 32 2323 3/31/04 8:53 AM NULL 0 Null John Schnieder 33 5435 3/30/04 8:42 AM NULL 0 Null Kris Lee 31 2343 3/19/04 9:55 AM NULL 0 Null Kristi Fioca 21 3323 3/30/04 8:42 AM NULL 0 Null Lyn Smith 333 3232 3/31/04 8:54 AM NULL 0 Null So basically if a sale cancels and it is a transfer, on the next sale I need to know from which lot
a customer transfered from.
Any help is appreciated!!!
-
If using SQL 200 then simple create a USer Defined Function to lookup the previous value like so.
CREATE FUNCTION dbo.PreviousLot (@Cust_ID int, @Cur_Lot int)
RETURNS int
AS
BEGIN
DECLARE @chkLot int,
@chkDate datetime
SELECT
TOP 1
@chkLot = S1.LotID,
@chkDate = S1.CancelDate
FROM
dbo.tblSales S1
INNER JOIN
dbo.TblSales S2
ON
S1.Cust_ID = S2.Cust_ID AND
IsNull(S1.CancelDate,'19000101') < S2.SaleDate AND
S1.LotID != S2.LotID AND
S2.LotID = @Cur_Lot
WHERE
S1.Cust_ID = @Cust_ID
ORDER BY
S1.SaleDate DESC
DECLARE @output int
SET @output = (CASE WHEN @chkDate IS NULL THEN @chkLot ELSE NULL END)
RETURN (@output)
END
Then in you select do this
SELECT (CASE WHEN IsTransfer = 1 THEN dbo.PreviousLot(Cust_ID, LotID) ELSE NULL END) AS TransferredFromLotID ....
See if this helps.
-
Antares, I think you're on the right track but it came up wrong...this is how it came up...
Fname Lname Cust_ID LotID SaleDate CancelDate IsTransfer TransferredFromLotID Betty Botox 12 1 3/23/04 4:40 PM 3/23/04 4:42 PM 0 Null Betty Botox 12 1 3/30/04 8:36 AM 3/30/04 8:44 AM 0 Null Betty Botox 12 6 3/30/04 9:14 AM 3/31/04 12:12 PM 1 9 Betty Botox 12 4 3/31/04 2:46 PM 3/31/04 3:36 PM 1 9 Betty Botox 12 9 3/31/04 3:38 PM NULL 0 Null bill smith 13 21 3/22/04 9:32 AM NULL 0 Null Boy George 33 33 3/23/04 4:11 PM NULL 0 Null bruce wayne 22 2112 3/15/04 8:57 AM 3/15/04 9:04 AM 0 Null bruce wayne 22 2112 3/15/04 9:57 AM 3/16/04 2:51 PM 0 Null Carol Douis 32 2323 3/31/04 8:53 AM NULL 0 Null John Schnieder 33 5435 3/30/04 8:42 AM NULL 0 Null Kris Lee 31 2343 3/19/04 9:55 AM NULL 0 Null Kristi Fioca 21 3323 3/30/04 8:42 AM NULL 0 Null Lyn Smith 333 3232 3/31/04 8:54 AM NULL 0 Null Thanks for trying, let me know if you find anything more on it!
-
In the previous code change this
IsNull(S1.CancelDate,'19000101') < S2.SaleDate
To This
S1.CancelDate < S2.SaleDate
And try again.
-
Thanks for the prompt reply! Came up as all nulls that time
Fname Lname Cust_ID LotID SaleDate CancelDate IsTransfer TransferredFromLotID Betty Botox 12 1 3/23/04 4:40 PM 3/23/04 4:42 PM 0 Null Betty Botox 12 1 3/30/04 8:36 AM 3/30/04 8:44 AM 0 Null Betty Botox 12 6 3/30/04 9:14 AM 3/31/04 12:12 PM 1 Null Betty Botox 12 4 3/31/04 2:46 PM 3/31/04 3:36 PM 1 Null Betty Botox 12 9 3/31/04 3:38 PM NULL 0 Null bill smith 13 21 3/22/04 9:32 AM NULL 0 Null Boy George 33 33 3/23/04 4:11 PM NULL 0 Null bruce wayne 22 2112 3/15/04 8:57 AM 3/15/04 9:04 AM 0 Null bruce wayne 22 2112 3/15/04 9:57 AM 3/16/04 2:51 PM 0 Null Carol Douis 32 2323 3/31/04 8:53 AM NULL 0 Null John Schnieder 33 5435 3/30/04 8:42 AM NULL 0 Null Kris Lee 31 2343 3/19/04 9:55 AM NULL 0 Null Kristi Fioca 21 3323 3/30/04 8:42 AM NULL 0 Null Lyn Smith 333 3232 3/31/04 8:54 AM NULL 0 Null -
Antares, I think I got it!
Create FUNCTION dbo.PreviousLot (@Cust_ID int, @Cur_Lot int, @SaleDate datetime)
RETURNS int
AS
BEGIN
DECLARE
@chkCust int,
@chkLot int,
@chkCancelDate datetime
select @chkCust=a.cust_id,@chkLot=a.lotid,@chkCancelDate=max(b.canceldate)
from tblSales b inner join tblsales a
on b.canceldate<@SaleDate
and b.istransfer=1
group by a.cust_id,a.lotid
DECLARE @output int
SET @output = (select lotid
from tblSales
where CancelDate=@chkCancelDate
and cust_id=@Cust_ID)
RETURN (@output)
END
select *,dbo.PreviousLot(cust_id,lotid,saledate)
from tblSales
Thanks for pointing me in the right direction, I don't think I ever would have thought to do it in a function!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply