March 31, 2015 at 7:32 am
Hi,
I tried to do a cross join but it wasn't working, do you have a example?
Thanks
March 31, 2015 at 8:08 am
SQL_Kills (3/31/2015)
Hi,I tried to do a cross join but it wasn't working, do you have a example?
Thanks
What I was referring to is something like the following. Here we have a subquery (X) which contains a CROSS JOIN of ID and InjectedDate, meaning is is a result containing all distinct values of ID paired with all distinct values of InjectedDate. We then use this (X) as the base for the SELECT and ORDER BY. We also LEFT OUTER JOIN (T) to get Amount and LEFT OUTER JOIN (Y) to get Yesterday_Amount.
select
X.ID
, coalesce( T.Amount, 0 ) as Amount
, coalesce( Y.Amount, T.Amount, 0 ) as Yesterday_Amount
, X.InjectedDate
from
(
select ID, InjectedDate from
(select distinct ID from Transactions) as X1
cross join
(select distinct InjectedDate from Transactions) as X2
) as X
left outer join Transactions T on T.ID = X.ID and T.InjectedDate = X.InjectedDate
left outer join Transactions as Y
on cast(Y.InjectedDate as date) = dateadd(day,-1,cast(X.InjectedDate as date))
and Y.ID = X.ID
order by X.InjectedDate, X.ID;
A000150502015-03-01 23:19:01.727
A0002100.23100.232015-03-01 23:19:01.727
A000380802015-03-01 23:19:01.727
A000420202015-03-01 23:19:01.727
A0005002015-03-01 23:19:01.727
A0006002015-03-01 23:19:01.727
A000150502015-03-02 23:19:01.727
A000290.23100.232015-03-02 23:19:01.727
A000380802015-03-02 23:19:01.727
A000410202015-03-02 23:19:01.727
A0005002015-03-02 23:19:01.727
A0006002015-03-02 23:19:01.727
A000120502015-03-03 23:19:01.727
A00025090.232015-03-03 23:19:01.727
A000380802015-03-03 23:19:01.727
A000410102015-03-03 23:19:01.727
A0005002015-03-03 23:19:01.727
A0006002015-03-03 23:19:01.727
A000150202015-03-04 23:19:01.727
A000250502015-03-04 23:19:01.727
A000380802015-03-04 23:19:01.727
A000410102015-03-04 23:19:01.727
A00051001002015-03-04 23:19:01.727
A0006002015-03-04 23:19:01.727
A00010502015-03-05 23:19:01.727
A000230502015-03-05 23:19:01.727
A000380802015-03-05 23:19:01.727
A000410102015-03-05 23:19:01.727
A0005901002015-03-05 23:19:01.727
A000610102015-03-05 23:19:01.727
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 31, 2015 at 10:52 am
Thanks Eric, that seems to have done the trick!
March 31, 2015 at 1:39 pm
Try this.
create function dbo.[F_GetPreviousDayAndAmount] (@ID char(5),@InjectedDate datetime)
RETURNS [float]
as
BEGIN
declare @YesterdayAmount float ,@PrevDate datetime
select@PrevDate = max(InjectedDate)
from[dbo].[Transactions]
where[ID] = @ID and
[InjectedDate] < @InjectedDate
if @PrevDate is NULL
select@YesterdayAmount = NULL
else
begin
select@YesterdayAmount = [Amount]
from[dbo].[Transactions]
where[ID] = @ID and
[InjectedDate] = @PrevDate
end
RETURN @YesterdayAmount
END
GO
selecta.ID,a.[InjectedDate],a.[Amount],dbo.[F_GetPreviousDayAndAmount](ID,InjectedDate) 'Yesterday_Amount'
from[dbo].[Transactions] a
order by a.ID,a.InjectedDate
March 31, 2015 at 8:43 pm
Eric M Russell (3/31/2015)
What I was referring to is something like the following. Here we have a subquery (X) which contains a CROSS JOIN of ID and InjectedDate, meaning is is a result containing all distinct values of ID paired with all distinct values of InjectedDate. We then use this (X) as the base for the SELECT and ORDER BY. We also LEFT OUTER JOIN (T) to get Amount and LEFT OUTER JOIN (Y) to get Yesterday_Amount.
Quick thought, this works as long as there is not a day without a transaction. If a day doesn't have any transaction, it will be missing from the result set.
😎
Here is an example of a simple calendar CTE to fill the gaps, added to the code I posted earlier. Note that there are no transactions on 2015-03-03.
USE tempdb;
GO
SET NOCOUNT ON;
--DROP TABLE dbo.Transactions;
IF OBJECT_ID(N'dbo.Transactions') IS NULL
BEGIN
CREATE TABLE [dbo].[Transactions](
[ID] [char](5) NOT NULL,
[Amount] [float] NULL,
[InjectedDate] [datetime] NULL
) ON [PRIMARY]
insert into Transactions values ('A0001',50,'2015-03-01 23:19:01.727')
insert into Transactions values ('A0002',100.23,'2015-03-01 23:19:01.727')
insert into Transactions values ('A0003',80,'2015-03-01 23:19:01.727')
insert into Transactions values ('A0004',20,'2015-03-01 23:19:01.727')
insert into Transactions values ('A0001',50,'2015-03-02 23:19:01.727')
insert into Transactions values ('A0002',90.23,'2015-03-02 23:19:01.727')
insert into Transactions values ('A0003',80,'2015-03-02 23:19:01.727')
insert into Transactions values ('A0004',10,'2015-03-02 23:19:01.727')
--insert into Transactions values ('A0001',20,'2015-03-03 23:19:01.727')
--insert into Transactions values ('A0002',50,'2015-03-03 23:19:01.727')
--insert into Transactions values ('A0003',80,'2015-03-03 23:19:01.727')
--insert into Transactions values ('A0004',10,'2015-03-03 23:19:01.727')
insert into Transactions values ('A0001',50,'2015-03-04 23:19:01.727')
insert into Transactions values ('A0002',50,'2015-03-04 23:19:01.727')
insert into Transactions values ('A0003',80,'2015-03-04 23:19:01.727')
insert into Transactions values ('A0004',10,'2015-03-04 23:19:01.727')
insert into Transactions values ('A0005',100,'2015-03-04 23:19:01.727')
insert into Transactions values ('A0002',30,'2015-03-05 23:19:01.727')
insert into Transactions values ('A0003',80,'2015-03-05 23:19:01.727')
insert into Transactions values ('A0004',10,'2015-03-05 23:19:01.727')
insert into Transactions values ('A0005',90,'2015-03-05 23:19:01.727')
insert into Transactions values ('A0006',10,'2015-03-05 23:19:01.727');
END
/************************************************
SQL Server 2005 and later
Self-join method 1
*************************************************/
/* ISNULL(BYD.Amount,[Column/value]) to replace
the NULLs
*/
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,DAYS_COVERED(NUM_DAYS,START_DAY) AS
(
SELECT
DATEDIFF(DAY,MIN(InjectedDate),MAX(InjectedDate)) + 1 AS NUM_DAYS
,MIN(InjectedDate) AS START_DAY
FROM dbo.Transactions
)
,CALENDAR AS
(
SELECT
TOP (SELECT NUM_DAYS FROM DAYS_COVERED) CONVERT(DATE,DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1,DC.START_DAY),0) AS CAL_DAY
FROM T T1,T T2,T T3,T T4
CROSS APPLY DAYS_COVERED DC
)
,ID_ON_ALL_DAYS AS
(
SELECT DISTINCT
TR.ID
,CAL.CAL_DAY
FROM CALENDAR CAL
OUTER APPLY dbo.Transactions TR
)
,TRANSACTION_ID_DAYS AS
(
SELECT
IOAD.ID
,IOAD.CAL_DAY AS InjectedDate
,ISNULL(TR.Amount,0.0) AS Amount
FROM ID_ON_ALL_DAYS IOAD
LEFT OUTER JOIN dbo.Transactions TR
ON IOAD.ID = TR.ID
AND IOAD.CAL_DAY = CONVERT(DATE,TR.InjectedDate,0)
)
,BASE_DATA AS
(
SELECT
TR.ID
,ROW_NUMBER() OVER
(
PARTITION BY TR.ID
ORDER BY TR.InjectedDate
) AS TR_RID
,TR.Amount
,TR.InjectedDate
FROM TRANSACTION_ID_DAYS TR
)
SELECT
BD.ID
,BD.Amount
,ISNULL(BYD.Amount,BD.Amount) AS Yesterday_Amount
,BD.InjectedDate
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA BYD
ON BD.ID = BYD.ID
AND BD.TR_RID = BYD.TR_RID + 1;
Results
ID Amount Yesterday_Amount InjectedDate
----- ---------------------- ---------------------- ------------
A0001 50 50 2015-03-01
A0001 50 50 2015-03-02
A0001 0 50 2015-03-03
A0001 50 0 2015-03-04
A0001 0 50 2015-03-05
A0002 100.23 100.23 2015-03-01
A0002 90.23 100.23 2015-03-02
A0002 0 90.23 2015-03-03
A0002 50 0 2015-03-04
A0002 30 50 2015-03-05
A0003 80 80 2015-03-01
A0003 80 80 2015-03-02
A0003 0 80 2015-03-03
A0003 80 0 2015-03-04
A0003 80 80 2015-03-05
A0004 20 20 2015-03-01
A0004 10 20 2015-03-02
A0004 0 10 2015-03-03
A0004 10 0 2015-03-04
A0004 10 10 2015-03-05
A0005 0 0 2015-03-01
A0005 0 0 2015-03-02
A0005 0 0 2015-03-03
A0005 100 0 2015-03-04
A0005 90 100 2015-03-05
A0006 0 0 2015-03-01
A0006 0 0 2015-03-02
A0006 0 0 2015-03-03
A0006 0 0 2015-03-04
A0006 10 0 2015-03-05
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply