Please help me with this query
-
Hi All,
I have a table that has more than 3 Million rows. I preferably do not want to do a RBAR 😀
Let me first put the Scripts in and then explain what I need.
CREATE TABLE [dbo].[TranTable](
[TranID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[DateEntered] [datetime] NOT NULL,
[Amount] [float] NOT NULL,
[CreditDebit] [varchar](1) NOT NULL,
[Type] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
--Here is the sample insert script
insert into dbo.trantable
(TranID,CustomerID,Dateentered,Amount,CreditDebit,[Type])
Select 1, 1023,'01-Jan-2009 08:25:25',50.5,'C','Dep'
UNION ALL
Select 2, 1023,'02-Jan-2009 01:15:45',50.8,'D','With'
UNION ALL
Select 3, 1023,'05-Jan-2009 07:15:45',65.0,'C','Dep'
UNION ALL
Select 4, 1023,'05-Jan-2009 09:25:45',67.25,'D','With'
UNION ALL
Select 6, 1524,'12-Feb-2009 14:00:25',125.0,'C','Dep'
UNION ALL
Select 8, 1524,'13-Feb-2009 01:01:25',125.0,'D','With'
UNION ALL
Select 9, 1524,'16-Feb-2009 08:03:25',150.0,'C','Dep'
UNION ALL
Select 12, 1524,'16-Feb-2009 09:01:25',125.0,'D','With'
GO
If I do a select * from dbo.trantable This is what I get.
TranIDCustomerIDDateEnteredAmountCreditDebitType
110232009-01-01 08:25:25.00050.5CDep
210232009-01-02 01:15:45.00050.8DWith
310232009-01-05 07:15:45.00065CDep
410232009-01-05 09:25:45.00067.25DWith
615242009-02-12 14:00:25.000125CDep
815242009-02-13 01:01:25.000125DWith
915242009-02-16 08:03:25.000150CDep
1215242009-02-16 09:01:25.000125DWith
If you look at the data, you will see thee are two instances where there was a deposit one day and WithDrawal the next day. (TranID 1,2, 6,8). The other four records are Deposit and withdrawal the same day. I want to get only the TranID 1,2,6,8. That is the Deposit one day and the WithDrawal is the next day. Is this possible?
Thanks
Roy
PS : Sorry about the formatting of the output of Select *
-Roy
-
It is hard to join because there is a very loose relationship between the two records (no transaction id to link them). However, this might give you a good starting point:
SELECT t1.TranID AS FirstTrans,t2.TranID AS SecondTran
FROM
(
SELECT *,ROW_NUMBER() OVER(ORDER BY TranID ASC) AS Row_Num
FROM TranTable
) t1,
(
SELECT *,ROW_NUMBER() OVER(ORDER BY TranID ASC) AS Row_Num
FROM TranTable
) t2
WHERE t1.Row_Num+1 = t2.Row_Num
AND t1.Row_Num % 2 = 1
AND t2.Row_Num % 2 = 0
AND t1.CreditDebit = 'C'
AND t2.CreditDebit = 'D'
AND DAY(t1.DateEntered) = DAY(DATEADD(d,-1,t2.DateEntered))
AND t1.CustomerID = t2.CustomerID;
The basic concept is that I'm setting up a join based on the row number (joining a current row to the next row). The assumption is that for a given row, the debit will ALWAYS follow the credit transaction.
Cheers,
Brian
-
Thanks for the input. Unfortunately the Credit will not always follow a Debit. Maybe another Customer made a deposit between the Credit and Debit. Also maybe there is only a Credit and no Matching Debit for that customer. The money was used for something else.
I am going to see if I can work with your Base query and try to do something.
-Roy
-
I don't know how this will perform on large number of records (As row_number is used, the above query requires sort operation.)
But it works even if you have multiple debits and multiple credits in the same day.
[/code]
SELECT t.* FROM trantable t
INNER JOIN
(
SELECT agg.tranid as aggtranid,agg1.tranid as agg1tranid
FROM
(SELECT *,
row_number() over (partition by customerID,CreditDebit order by customerID,dateentered) rownum
FROM trantable)agg
INNER JOIN
(SELECT *,
row_number() over (partition by customerID,CreditDebit order by customerID,dateentered) rownum
FROM trantable)agg1
ON agg.rownum = agg1.rownum
AND agg.customerID = agg1.customerID
WHERE datediff(dd,agg.dateentered,agg1.dateentered) = 1
)Z
ON Z.aggtranid = t.tranid
OR Z.agg1tranid = t.tranid
[/code]
-Vikas Bindra
-
Try this:
select t1.*, t2.TranID
from dbo.TranTable t1
inner join dbo.TranTable t2
on t1.CustomerID = t2.CustomerID
and t2.Type = 'With'
and t1.Type = 'Dep'
and t2.DateEntered between
dateadd(day, datediff(day, 0, t1.DateEntered)+1, 0)
and
dateadd(day, datediff(day, 0, t1.DateEntered)+2, 0);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thanks both of you for queries.
GSquared, I liked your method, It is simple. But here it runs into a small complication.
Let us say Customer 1023 Deposited one amount yesterday (TranID 1) and he also made a deposit today (tranID 3). He also did one withdrawal yesterday (tranID 2) and did another one today (tranID 4), then it will still link TranID 1 with tranID 4.
The TranID I am giving here is just an example and does not relate to the data inserted. I am sorry I missed this point when giving the sample data.
Vikas, Yours too has the same issue.
-Roy
-
Maybe this?
select t1.*, t2.TranIDfrom dbo.TranTable t1
inner join dbo.TranTable t2
on t1.CustomerID = t2.CustomerID
and t2.Type = 'With'
and t1.Type = 'Dep'
and t2.DateEntered between
dateadd(day, datediff(day, 0, t1.DateEntered)+1, 0)
and
dateadd(day, datediff(day, 0, t1.DateEntered)+2, 0)
where not exists(select * from dbo.TranTable t3
where t1.CustomerID = t3.CustomerID
and t3.Type = 'With'
and t3.DateEntered between
dateadd(day, datediff(day, 0, t1.DateEntered), 0)
and
dateadd(day, datediff(day, 0, t1.DateEntered)+1, 0));
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
select *
from #TranTable t1
inner join #TranTable t2
on t1.CustomerID = t2.CustomerID
and t2.Type = 'With'
and t1.Type = 'Dep'
and t2.DateEntered between
dateadd(day, datediff(day, 0, t1.DateEntered)+1, 0)
and
dateadd(day, datediff(day, 0, t1.DateEntered)+2, 0)
left outer join #TranTable t3
on t1.CustomerID = t3.CustomerID
and t1.Type = 'Dep'
and t3.Type = 'With'
and t3.DateEntered between
t1.DateEntered
and
dateadd(day, datediff(day, 0, t1.DateEntered)+1, 0)
where t3.tranid is null;
I tested it like this:
set nocount on;
go
if object_id(N'tempdb..#TranTable') is not null
drop table #TranTable;
CREATE TABLE #TranTable(
[TranID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[DateEntered] [datetime] NOT NULL,
[Amount] [float] NOT NULL,
[CreditDebit] [varchar](1) NOT NULL,
[Type] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
--Here is the sample insert script
insert into #trantable
(TranID,CustomerID,Dateentered,Amount,CreditDebit,[Type])
Select 1, 1023,'01-Jan-2009 08:25:25',50.5,'C','Dep'
UNION ALL
Select 2, 1023,'02-Jan-2009 01:15:45',50.8,'D','With'
UNION ALL
Select 3, 1023,'05-Jan-2009 07:15:45',65.0,'C','Dep'
UNION ALL
Select 4, 1023,'05-Jan-2009 09:25:45',67.25,'D','With'
UNION ALL
Select 6, 1524,'12-Feb-2009 14:00:25',125.0,'C','Dep'
UNION ALL
Select 8, 1524,'13-Feb-2009 01:01:25',125.0,'D','With'
UNION ALL
Select 9, 1524,'16-Feb-2009 08:03:25',150.0,'C','Dep'
UNION ALL
Select 12, 1524,'16-Feb-2009 09:01:25',125.0,'D','With'
UNION ALL
select 13, 1, '2/25/09 1 pm',1, 'C','Dep'
union all
select 14, 1, '2/25/09 1:30 pm',1,'D','With'
union all
select 15, 1, '2/26/09 1 am',1,'C','Dep'
union all
select 16, 1,'2/26/09 1:30 am',1,'D','With'
GO
select *
from #TranTable t1
inner join #TranTable t2
on t1.CustomerID = t2.CustomerID
and t2.Type = 'With'
and t1.Type = 'Dep'
and t2.DateEntered between
dateadd(day, datediff(day, 0, t1.DateEntered)+1, 0)
and
dateadd(day, datediff(day, 0, t1.DateEntered)+2, 0)
left outer join #TranTable t3
on t1.CustomerID = t3.CustomerID
and t1.Type = 'Dep'
and t3.Type = 'With'
and t3.DateEntered between
t1.DateEntered
and
dateadd(day, datediff(day, 0, t1.DateEntered)+1, 0)
where t3.tranid is null;
Transactions 13-16 fit the exception pattern you mentioned, and the query doesn't return them. Will that work?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
That worked fine.... Thank You all for helping me out. Appreciate it very much. 🙂
Roy
-Roy
-
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Roy Ernest (2/26/2009)
That worked fine.... Thank You all for helping me out. Appreciate it very much. 🙂Roy
How's the performance, Roy?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Roy Ernest (2/26/2009)
Thanks both of you for queries.But here it runs into a small complication.
Let us say Customer 1023 Deposited one amount yesterday (TranID 1) and he also made a deposit today (tranID 3). He also did one withdrawal yesterday (tranID 2) and did another one today (tranID 4), then it will still link TranID 1 with tranID 4.
Vikas, Yours too has the same issue.
My query doesn't relate transID1 with TransID4. Here is the sample code:
set nocount on;
go
if object_id(N'tempdb..#TranTable') is not null
drop table #TranTable;
CREATE TABLE #TranTable(
[TranID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[DateEntered] [datetime] NOT NULL,
[Amount] [float] NOT NULL,
[CreditDebit] [varchar](1) NOT NULL,
[Type] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
insert into #trantable
(TranID,CustomerID,Dateentered,Amount,CreditDebit,[Type])
Select 1, 1023,'01-Jan-2009 08:25:25',50.5,'C','Dep'
UNION ALL
Select 2, 1023,'01-Jan-2009 11:15:45',50.8,'D','With'
UNION ALL
Select 3, 1023,'02-Jan-2009 08:25:25',50.5,'C','Dep'
UNION ALL
Select 4, 1023,'02-Jan-2009 11:15:45',50.8,'D','With'
UNION ALL
Select 5, 1023,'05-Jan-2009 07:15:45',65.0,'C','Dep'
UNION ALL
Select 6, 1023,'06-Jan-2009 09:25:45',67.25,'D','With'
UNION ALL
Select 7, 1524,'12-Feb-2009 14:00:25',125.0,'C','Dep'
UNION ALL
Select 8, 1524,'13-Feb-2009 01:01:25',125.0,'D','With'
UNION ALL
Select 9, 1524,'16-Feb-2009 08:03:25',150.0,'C','Dep'
UNION ALL
Select 12, 1524,'16-Feb-2009 09:01:25',125.0,'D','With'
UNION ALL
select 13, 1, '2/25/09 1 pm',1, 'C','Dep'
union all
select 14, 1, '2/25/09 1:30 pm',1,'D','With'
union all
select 15, 1, '2/26/09 1 am',1,'C','Dep'
union all
select 16, 1,'2/26/09 1:30 am',1,'D','With'
GO
SELECT t.* FROM #TranTable t
INNER JOIN
(
SELECT agg.tranid as aggtranid,agg1.tranid as agg1tranid
FROM
(SELECT *,
row_number() over (partition by customerID,CreditDebit order by customerID,dateentered) rownum
FROM #TranTable)agg
INNER JOIN
(SELECT *,
row_number() over (partition by customerID,CreditDebit order by customerID,dateentered) rownum
FROM #TranTable)agg1
ON agg.rownum = agg1.rownum
AND agg.customerID = agg1.customerID
WHERE datediff(dd,agg.dateentered,agg1.dateentered) = 1
)Z
ON Z.aggtranid = t.tranid
OR Z.agg1tranid = t.tranid
-Vikas Bindra
-
Jeff Moden (2/26/2009)
Roy Ernest (2/26/2009)
That worked fine.... Thank You all for helping me out. Appreciate it very much. 🙂Roy
How's the performance, Roy?
Actually performance is not so bad. This is not going to be run every day. This was a one time query that I had to run to find out who all were abusing the fluctuating Currency Rate. So I had to run for the past 5 months data. I thought it would be worse. But since it was a one time shot, I did not mind and was much better than I expected.
Vikas, I have not checked yours yet. I will check it out today I will let you know if it was performing better.
-Roy
-
Cool... thanks for the feedback, Roy.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Roy,
I may be bit late for your query, but hope even this code gives you the result you are expecting :).
Have a look on this and let me know whether it solves your problem...
SELECT * FROM TRANTABLE
WHERE TRANID NOT IN
(
SELECT CREDIT.TRANID FROM
(SELECT
TRANID, CUSTOMERID, CONVERT(VARCHAR(10), DATEENTERED, 110) DATEENTERED, CREDITDEBIT
FROM
TRANTABLE
WHERE
CREDITDEBIT = 'C') CREDIT,
(SELECT
TRANID, CUSTOMERID, CONVERT(VARCHAR(10), DATEENTERED, 110) DATEENTERED, CREDITDEBIT
FROM
TRANTABLE
WHERE
CREDITDEBIT = 'D') DEBIT
WHERE
CREDIT.DATEENTERED = DEBIT.DATEENTERED
AND CREDIT.CUSTOMERID = DEBIT.CUSTOMERID
)
AND TRANID NOT IN
(
SELECT DEBIT.TRANID FROM
(SELECT
TRANID, CUSTOMERID, CONVERT(VARCHAR(10), DATEENTERED, 110) DATEENTERED, CREDITDEBIT
FROM
TRANTABLE
WHERE
CREDITDEBIT = 'C') CREDIT,
(SELECT
TRANID, CUSTOMERID, CONVERT(VARCHAR(10), DATEENTERED, 110) DATEENTERED, CREDITDEBIT
FROM
TRANTABLE
WHERE
CREDITDEBIT = 'D') DEBIT
WHERE
CREDIT.DATEENTERED = DEBIT.DATEENTERED
AND CREDIT.CUSTOMERID = DEBIT.CUSTOMERID
)
This will give you the TRANID that has different DATEENTERED.
If you want to get the records that fall in same date, then you can use this ...
SELECT * FROM
(SELECT
TRANID, CUSTOMERID, CONVERT(VARCHAR(10), DATEENTERED, 110) DATEENTERED, CREDITDEBIT
FROM
TRANTABLE
WHERE
CREDITDEBIT = 'C') CREDIT,
(SELECT
TRANID, CUSTOMERID, CONVERT(VARCHAR(10), DATEENTERED, 110) DATEENTERED, CREDITDEBIT
FROM
TRANTABLE
WHERE
CREDITDEBIT = 'D') DEBIT
WHERE
CREDIT.DATEENTERED = DEBIT.DATEENTERED
AND CREDIT.CUSTOMERID = DEBIT.CUSTOMERID
I'm still trying to figure out some other logic 🙂 for your query. Hope I can get few more logic.
Regards,
Ashok S
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply