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