March 16, 2015 at 2:39 pm
Hi,
I have the following table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Transactions](
[ID] [char](5) NOT NULL,
[Amount] [float] NULL,
[InjectedDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
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')
I then want a new field that will show the value for that ID for Yesterday. So the end table will look like the attached "SnapShot_FinalTable.PNG"
Thanks
March 16, 2015 at 6:08 pm
There are various ways to do this. The concept is similar to Creating a Date Range from Multiple Rows Based on a Single Date [/url] except you want to pull Amount off the prior record instead of a date.
I didn't provide a specific solution for your case for two reasons:
1. If you happened to be working in SQL 2012, LAG would be great for this (very simple code). Oftentimes people post to the wrong forums.
2. You do not indicate how you get the first four values for yesterday shown in your PNG. I suspect you're simply doing an ISNULL, but not sure.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 16, 2015 at 11:22 pm
As Dwain mentioned there are several ways of doing this, here are few examples
😎
USE tempdb;
GO
SET NOCOUNT ON;
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 2012 and later
LAG function
*************************************************/
/* Add the third parameter for a default value
i.e. LAG(TR.Amount,1,0) or LAG(TR.Amount,1,TR.Amount)
to replace the NULLs where previous values are missing
*/
SELECT
TR.ID
,TR.Amount
,LAG(TR.Amount,1) OVER
(
PARTITION BY TR.ID
ORDER BY TR.InjectedDate
) AS Yesterday_Amount
,TR.InjectedDate
FROM dbo.Transactions TR;
/************************************************
SQL Server 2005 and later
Self-join method 1
*************************************************/
/* ISNULL(BYD.Amount,[Column/value]) to replace
the NULLs
*/
;WITH 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 dbo.Transactions TR
)
SELECT
BD.ID
,BD.Amount
,BYD.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;
/************************************************
SQL Server 2005 and later
Self-join method 2
*************************************************/
/* ISNULL(BYD.Amount,[Column/value]) to replace
the NULLs
*/
;WITH 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 dbo.Transactions TR
)
SELECT
BD.ID
,BD.Amount
,BYD.Amount
,BD.InjectedDate
FROM BASE_DATA BD
OUTER APPLY (
SELECT B.Amount
FROM BASE_DATA B
WHERE BD.ID = B.ID
AND BD.TR_RID = B.TR_RID + 1
) AS BYD(Amount);
/************************************************
SQL Server 2005 and later
Aggregation row-shift method
*************************************************/
;WITH 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 dbo.Transactions TR
)
,GROUPED_DATA AS
(
SELECT
BD.ID
,BD.Amount
,BD.TR_RID
,MAX(CASE WHEN BD.TR_RID % 2 = 0 THEN BD.Amount END) OVER (PARTITION BY BD.ID,(BD.TR_RID + ((BD.TR_RID + 1) % 2))) AS ODD_COL_VAL
,MAX(CASE WHEN BD.TR_RID % 2 = 1 THEN BD.Amount END) OVER (PARTITION BY BD.ID,(BD.TR_RID + (BD.TR_RID % 2))) AS EVEN_COL_VAL
,BD.InjectedDate
FROM BASE_DATA BD
)
SELECT
GD.ID
,GD.Amount
,CASE
WHEN GD.TR_RID % 2 = 1 THEN GD.ODD_COL_VAL
ELSE GD.EVEN_COL_VAL
END AS Yesterday_Amount
,GD.InjectedDate
FROM GROUPED_DATA GD;
Results (same for all queries)
ID Amount Yesterday_Amount InjectedDate
----- ---------------------- ---------------------- -----------------------
A0001 50 NULL 2015-03-01 23:19:01.727
A0001 50 50 2015-03-02 23:19:01.727
A0001 20 50 2015-03-03 23:19:01.727
A0001 50 20 2015-03-04 23:19:01.727
A0002 100.23 NULL 2015-03-01 23:19:01.727
A0002 90.23 100.23 2015-03-02 23:19:01.727
A0002 50 90.23 2015-03-03 23:19:01.727
A0002 50 50 2015-03-04 23:19:01.727
A0002 30 50 2015-03-05 23:19:01.727
A0003 80 NULL 2015-03-01 23:19:01.727
A0003 80 80 2015-03-02 23:19:01.727
A0003 80 80 2015-03-03 23:19:01.727
A0003 80 80 2015-03-04 23:19:01.727
A0003 80 80 2015-03-05 23:19:01.727
A0004 20 NULL 2015-03-01 23:19:01.727
A0004 10 20 2015-03-02 23:19:01.727
A0004 10 10 2015-03-03 23:19:01.727
A0004 10 10 2015-03-04 23:19:01.727
A0004 10 10 2015-03-05 23:19:01.727
A0005 100 NULL 2015-03-04 23:19:01.727
A0005 90 100 2015-03-05 23:19:01.727
A0006 10 NULL 2015-03-05 23:19:01.727
March 17, 2015 at 11:18 am
SQL_Kills (3/16/2015)
Hi,I have the following table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Transactions](
[ID] [char](5) NOT NULL,
[Amount] [float] NULL,
[InjectedDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
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')
[font="Arial Black"]I then want a new field that will show the value for that ID for Yesterday. [/font]So the end table will look like the attached "SnapShot_FinalTable.PNG"
Thanks
You're PNG shows the "amount" from yesterday, not the ID. Which is it that you actually need?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2015 at 2:18 pm
Jeff, The Yesterday_Amount should show the Amount the Amount the ID was for yesterday. Thanks
March 17, 2015 at 2:28 pm
SQL_Kills (3/17/2015)
Jeff, The Yesterday_Amount should show the Amount the Amount the ID was for yesterday. Thanks
Could you elaborate on Dwain's questions please?
😎
March 17, 2015 at 2:50 pm
Hi All,
Thanks for your help so far, your queries are closed but I need a entry for A0001 for the InjectedDate of "05/03/2015 23:19:02". I know there is no Entry for that injectedDate so really for the whole of March 2015 I want there to be a InjectedDate for each Day like below
07/03/2015 00:00:00
08/03/2015 00:00:00
09/03/2015 00:00:00
10/03/2015 00:00:00
So the time on InjectedDate field does not matter.
Thanks
March 17, 2015 at 3:08 pm
1. If you happened to be working in SQL 2012, LAG would be great for this (very simple code). Oftentimes people post to the wrong forums.
We do not have SQL Server 2012 only 2008 R2
2. You do not indicate how you get the first four values for yesterday shown in your PNG. I suspect you're simply doing an ISNULL, but not sure.
Yes I think it will need a isnull as the first day of the new month will need to show the Amount and Yesterday_Amount the same
March 17, 2015 at 4:03 pm
select T.ID, T.Amount, Y.Amount as Yesterday_Amount, T.InjectedDate
from Transactions as T
left join Transactions as Y
on cast(Y.InjectedDate as date) = dateadd(day,-1,cast(T.InjectedDate as date))
order by T.InjectedDate, T.ID;
If join performance is an issue, then it may help if you have an index on InjectedDate. Perhaps even better if you add a persisted computed column CInjectedDate = cast(InjectedDate as Date), index that, and join on that instead of InjectedDate.
select T.ID, T.Amount, Y.Amount as Yesterday_Amount, T.InjectedDate
from Transactions as T
left join Transactions as Y
on Y.CInjectedDate = dateadd(day,-1,T.CInjectedDate)
order by T.CInjectedDate, T.ID;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 17, 2015 at 4:48 pm
Hi Eric,
This is not giving me the result I require.
Thanks
March 18, 2015 at 7:33 am
SQL_Kills (3/17/2015)
Hi Eric,This is not giving me the result I require.
Thanks
Sorry about that; I forgot to add Y.ID = T.ID in the join, which is why it was previously cross joining and doubling up on resultset. Also, I've added expression to replace Yesterday_Amount with current Amount, if previous day's Amount is NULL.
Here is the corrected code which should match exactly your target resultset:
select
T.ID
, T.Amount
, isnull(Y.Amount,T.Amount) as Yesterday_Amount
, T.InjectedDate
from Transactions as T
left join Transactions as Y
on cast(Y.InjectedDate as date) = dateadd(day,-1,cast(T.InjectedDate as date))
and Y.ID = T.ID
order by T.InjectedDate, T.ID;
IDAmountYesterday_AmountInjectedDate
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
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
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
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
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 29, 2015 at 2:52 pm
A0001 still needs to appear as 50 as a Difference for the InjectedDate 2015-03-05 23:19:01.727
March 30, 2015 at 2:00 pm
Anyone know how to do this?
March 30, 2015 at 10:33 pm
SQL_Kills (3/29/2015)
A0001 still needs to appear as 50 as a Difference for the InjectedDate 2015-03-05 23:19:01.727
Quick question, do you mean that you want to add missing entries as zero value, ie. A0001 2015-03-05 (0)?
😎
March 31, 2015 at 7:19 am
SQL_Kills (3/29/2015)
A0001 still needs to appear as 50 as a Difference for the InjectedDate 2015-03-05 23:19:01.727
To pad a resultset with missing rows required to form a complete set, you can cross join with a subset of all possible codes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply