adding a double transaction row to group.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

adding a double transaction row to group.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-28 : 05:25:10

Hi. i have a transaction 29801 that will have 2 rows, L and E.
L= type of transaction and E will only be the extra line if the transaction is canceled ,
So if a transaction is NOT cancelled it will have only one row (L) but if the transaction is cancelled it will have 2 rows(L and E).
I am trying to create a query that will sum transactions and will also display the E (negative number of the transaction) in another column.
So If i do
select * from tblTrans_Cash where transC_lgnnumber = 29801
the i will get 2 lines L and E , since this is a cancelled transaction.
I would like to incorporate that into a sum query i have:


declare @datefrom datetime,@dateto Datetime,@Cinema AS VARCHAR(2),@CinemaDescription VARCHAR(50),@CardStrType varchar(10)
set @datefrom ='20141101'
set @dateto = '20141126'
set @Cinema = 03
set @CinemaDescription = NULL
set @CardStrType = 'L'

SELECT	
--transc_curvalue,
--transc_strsummarisedflag,
@DateFrom,
		@DateTo,
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))	AS DateRecord,
		Count(*) AS TotalTransactions,
		SUM(CASE WHEN Transc_curValue>=0 THEN 1 ELSE 0 END) AS TotalPayments ,
		SUM(CASE WHEN Transc_curValue>=0 THEN Transc_curValue ELSE 0 END) AS PaymentValue ,
		SUM(CASE WHEN Transc_curValue<0 THEN 1 ELSE 0 END) AS TotalRefunds ,
		SUM(CASE WHEN Transc_curValue<0 THEN Transc_curValue ELSE 0 END) AS RefundValue,
		ISNULL(@Cinema,C.Cinema_strCode) AS Cinema  ,
		ISNULL(@CinemaDescription,C.Cinema_strName)
FROM	tblTrans_Cash 
--inner join tbltrans_ticket
--ON tblTrans_Cash.TransC_lgnNumber = tblTrans_Ticket.TransT_lgnNumber
		CROSS JOIN tblCinema C
WHERE	--TransC_strType= @CardStrType and 
TransC_strType= @CardStrType
and 
(TransC_strType= 'E' or TransC_strType= @CardStrType)
		AND TransC_dtmRealTransTime BETWEEN @DateFrom AND @DateTo
--and transC_lgnnumber = 2980
GROUP BY
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101)),
		ISNULL(@Cinema,C.Cinema_strCode),
		ISNULL(@CinemaDescription,C.Cinema_strName)

--,transc_curvalue
--,transc_strsummarisedflag
ORDER BY convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))

my problem here is that if i do
where TransC_strType= @CardStrType or TransC_strType= 'E'
It will display transactions that may or may not have 'L' so i will get extra sums.
If i do TransC_strType= @CardStrType and TransC_strType= 'E' it will only display transactions that have both 'L' and 'E'
What i would like to do is see if a transaction is 'L' and if so then if it has 'E' then add it to the sum.
So any ideas? A Case maybe in the where clause?
Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-28 : 05:34:44

Hi. I forgot that a transaction transC_lgnnumber is the same on both column that will come up if it's L and E.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-29 : 11:26:50

You'll need a self-join. Basically you want a join where the trans numbers are the same, and the second set has (or doesn't have) the other type.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-12-01 : 03:43:46

Any help?
I'm trying with temp tables to do your suggestion, probably something much simpler but...
:


declare @datefrom datetime,@dateto Datetime,@Cinema AS VARCHAR(2),@CinemaDescription VARCHAR(50),@CardStrType varchar(10)
set @datefrom ='20141101'
set @dateto = '20141126'
set @Cinema = 03
set @CinemaDescription = NULL
set @CardStrType = 'L'

SELECT	
--transc_curvalue,
--transc_strsummarisedflag,
Transc_LgnNumber,TransC_strType,
@DateFrom as Datefrom,
		@DateTo as Dateto,
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))	AS DateRecord,
		Count(*) AS TotalTransactions,
		SUM(CASE WHEN Transc_curValue>=0 THEN 1 ELSE 0 END) AS TotalPayments ,
		SUM(CASE WHEN Transc_curValue>=0 THEN Transc_curValue ELSE 0 END) AS PaymentValue ,
		SUM(CASE WHEN Transc_curValue<0 THEN 1 ELSE 0 END) AS TotalRefunds ,
		SUM(CASE WHEN Transc_curValue<0 THEN Transc_curValue ELSE 0 END) AS RefundValue,
		ISNULL(@Cinema,C.Cinema_strCode) AS Cinema  ,
		ISNULL(@CinemaDescription,C.Cinema_strName) as cinemadescription
INTO #TmpTableL
FROM	Vista.dbo.tblTrans_Cash 
--inner join tbltrans_ticket
--ON tblTrans_Cash.TransC_lgnNumber = tblTrans_Ticket.TransT_lgnNumber
		CROSS JOIN tblCinema C
WHERE	TransC_strType= @CardStrType 
--and 
--(TransC_strType= @CardStrType or TransC_strType= 'E')
--and 
--(TransC_strType= 'E' or TransC_strType= @CardStrType)
		AND TransC_dtmRealTransTime BETWEEN @DateFrom AND @DateTo
--and transC_lgnnumber = 2980135
--AND 1 =
--CASE WHEN (TransC_strType= 'L')
--  THEN if or TransC_strType= @CardStrType)1
 --ELSE 0 END


GROUP BY
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101)),
		ISNULL(@Cinema,C.Cinema_strCode),
		ISNULL(@CinemaDescription,C.Cinema_strName)
,Transc_LgnNumber,TransC_strType


SELECT	
--transc_curvalue,
--transc_strsummarisedflag,
Transc_LgnNumber,TransC_strType,
@DateFrom as Datefrom,
		@DateTo as Dateto,
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))	AS DateRecord,
		Count(*) AS TotalTransactions,
		SUM(CASE WHEN Transc_curValue>=0 THEN 1 ELSE 0 END) AS TotalPayments ,
		SUM(CASE WHEN Transc_curValue>=0 THEN Transc_curValue ELSE 0 END) AS PaymentValue ,
		SUM(CASE WHEN Transc_curValue<0 THEN 1 ELSE 0 END) AS TotalRefunds ,
		SUM(CASE WHEN Transc_curValue<0 THEN Transc_curValue ELSE 0 END) AS RefundValue,
		ISNULL(@Cinema,C.Cinema_strCode) AS Cinema  ,
		ISNULL(@CinemaDescription,C.Cinema_strName) as cinemadescription
INTO #TmpTableE
FROM	Vista.dbo.tblTrans_Cash 
--inner join tbltrans_ticket
--ON tblTrans_Cash.TransC_lgnNumber = tblTrans_Ticket.TransT_lgnNumber
		CROSS JOIN tblCinema C
WHERE	TransC_strType= 'E' 
--and 
--(TransC_strType= @CardStrType or TransC_strType= 'E')
--and 
--(TransC_strType= 'E' or TransC_strType= @CardStrType)
		AND TransC_dtmRealTransTime BETWEEN @DateFrom AND @DateTo        
--and transC_lgnnumber = 2980135
--AND 1 =
--CASE WHEN (TransC_strType= 'L')
--  THEN if or TransC_strType= @CardStrType)1
 --ELSE 0 END


GROUP BY
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101)),
		ISNULL(@Cinema,C.Cinema_strCode),
		ISNULL(@CinemaDescription,C.Cinema_strName)
,Transc_LgnNumber,TransC_strType




SELECT	
--transc_curvalue,
--transc_strsummarisedflag,
@DateFrom as Datefrom,
		@DateTo as Dateto,
		convert(datetime,convert(char(10),TL.TransC_dtmRealTransTime,101))	AS DateRecord,
		Count(*) AS TotalTransactions,
		SUM(CASE WHEN TL.Transc_curValue>=0 THEN 1 ELSE 0 END) AS TotalPayments ,
		SUM(CASE WHEN TL.Transc_curValue>=0 THEN Transc_curValue ELSE 0 END) AS PaymentValue ,
		SUM(CASE WHEN TL.Transc_curValue<0 THEN 1 ELSE 0 END) AS TotalRefunds ,
		SUM(CASE WHEN TL.Transc_curValue<0 THEN Transc_curValue ELSE 0 END) AS RefundValue,
		ISNULL(@Cinema,TL.Cinema_strCode) AS Cinema  ,
		ISNULL(@CinemaDescription,TL.Cinema_strName) as cinemadescription
from #TmpTableL TL inner join #TmpTableE TE on TL.Transc_LgnNumber = TE.Transc_LgnNumber

drop table #TmpTableL
drop table #TmpTableE

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-12-01 : 04:17:45

or like this but again i will either give me the L or E (inner join or left join)

 declare @datefrom datetime,@dateto Datetime,@Cinema AS VARCHAR(2),@CinemaDescription VARCHAR(50),@CardStrType varchar(10)
set @datefrom ='20141101'
set @dateto = '20141126'
set @Cinema = 03
set @CinemaDescription = NULL
set @CardStrType = 'L'

SELECT	
--transc_curvalue,
--transc_strsummarisedflag,
Vista.dbo.tblTrans_Cash .Transc_LgnNumber,Vista.dbo.tblTrans_Cash .TransC_strType,
@DateFrom,
		@DateTo,
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))	AS DateRecord,
		Count(*) AS TotalTransactions,
		SUM(CASE WHEN Transc_curValue>=0 THEN 1 ELSE 0 END) AS TotalPayments ,
		SUM(CASE WHEN Transc_curValue>=0 THEN Transc_curValue ELSE 0 END) AS PaymentValue ,
		SUM(CASE WHEN Transc_curValue<0 THEN 1 ELSE 0 END) AS TotalRefunds ,
		SUM(CASE WHEN Transc_curValue<0 THEN Transc_curValue ELSE 0 END) AS RefundValue,
		ISNULL(@Cinema,Cinema_strCode) AS Cinema  ,
		ISNULL(@CinemaDescription,Cinema_strName)
FROM	Vista.dbo.tblTrans_Cash inner join (

					SELECT Transc_LgnNumber
					FROM	Vista.dbo.tblTrans_Cash
WHERE	--TransC_strType= @CardStrType and 
TransC_strType= 'E'
				GROUP BY
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))
		
,Transc_LgnNumber
					) A
on Vista.dbo.tblTrans_Cash.Transc_LgnNumber = A.Transc_LgnNumber

--2980135
--inner join tbltrans_ticket
--ON tblTrans_Cash.TransC_lgnNumber = tblTrans_Ticket.TransT_lgnNumber
		CROSS JOIN tblCinema C
WHERE	--TransC_strType= @CardStrType and 
(TransC_strType= @CardStrType)
--and 
--(TransC_strType= 'E' or TransC_strType= @CardStrType)
		--AND TransC_dtmRealTransTime BETWEEN @DateFrom AND @DateTo
--and transC_lgnnumber = 2980135
--AND 1 =
--CASE WHEN (TransC_strType= 'L')
--  THEN if or TransC_strType= @CardStrType)1
 --ELSE 0 END


GROUP BY
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101)),
		ISNULL(@Cinema,C.Cinema_strCode),
		ISNULL(@CinemaDescription,C.Cinema_strName)
,Vista.dbo.tblTrans_Cash .Transc_LgnNumber,Vista.dbo.tblTrans_Cash .TransC_strType

--,transc_curvalue
--,transc_strsummarisedflag
ORDER BY TransC_LgnNumber,convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-12-01 : 04:53:43

Ok, so the closer i can get is the above.
This is correct in the way that it will give me the E transactions in the first table (i reversed the payment and refund so it will appear in the refund section on the complete select) but the problem i get is that when i apply the union the refunds will appear in an extra row and not in the row with the 'L' with the same transc_Lgnnumber on the refund section.
this is a correct behavior as i do a union but i would want the refunds to appear on the same row as their 'L' equivalent , on the refund section.
Here is what i have right now.How would i just sum the refunds on the 'L' row and not create a new row?
Thanks.


declare @datefrom datetime,@dateto Datetime,@Cinema AS VARCHAR(2),@CinemaDescription VARCHAR(50),@CardStrType varchar(10)
set @datefrom ='20141101'
set @dateto = '20141126'
set @Cinema = 03
set @CinemaDescription = NULL
set @CardStrType = 'L'

SELECT	

--Vista.dbo.tblTrans_Cash .Transc_LgnNumber,Vista.dbo.tblTrans_Cash .TransC_strType,
@DateFrom,
		@DateTo,
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))	AS DateRecord,
		Count(*) AS TotalTransactions,
		SUM(CASE WHEN Transc_curValue>=0 THEN 1 ELSE 0 END) AS TotalPayments ,
		SUM(CASE WHEN Transc_curValue<0 THEN Transc_curValue ELSE 0 END) AS PaymentValue,
		SUM(CASE WHEN Transc_curValue<0 THEN 1 ELSE 0 END) AS TotalRefunds ,		
SUM(CASE WHEN Transc_curValue>=0 THEN Transc_curValue ELSE 0 END) AS RefundValue  ,
		ISNULL(@Cinema,Cinema_strCode) AS Cinema  ,
		ISNULL(@CinemaDescription,Cinema_strName)
FROM	Vista.dbo.tblTrans_Cash inner join (

					SELECT Transc_LgnNumber
					FROM	Vista.dbo.tblTrans_Cash
WHERE	--TransC_strType= @CardStrType and 
TransC_strType= 'E'
	--AND TransC_dtmRealTransTime BETWEEN @DateFrom AND @DateTo
				GROUP BY
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))
		
,Transc_LgnNumber
					) A
on Vista.dbo.tblTrans_Cash.Transc_LgnNumber = A.Transc_LgnNumber

		CROSS JOIN tblCinema C
WHERE	--TransC_strType= @CardStrType and 
(TransC_strType= @CardStrType)

		AND TransC_dtmRealTransTime BETWEEN @DateFrom AND @DateTo



GROUP BY
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101)),
		ISNULL(@Cinema,C.Cinema_strCode),
		ISNULL(@CinemaDescription,C.Cinema_strName)

--,Vista.dbo.tblTrans_Cash .Transc_LgnNumber,Vista.dbo.tblTrans_Cash .TransC_strType


--- This is where i have the rows i need to add to the refund section but the union will just add them in the big set as extra rows.
union 



SELECT	
--Vista.dbo.tblTrans_Cash .Transc_LgnNumber,Vista.dbo.tblTrans_Cash .TransC_strType,
@DateFrom,
		@DateTo,
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))	AS DateRecord,
		Count(*) AS TotalTransactions,
		SUM(CASE WHEN Transc_curValue>=0 THEN 1 ELSE 0 END) AS TotalPayments ,
		SUM(CASE WHEN Transc_curValue>=0 THEN Transc_curValue ELSE 0 END) AS PaymentValue ,
		SUM(CASE WHEN Transc_curValue<0 THEN 1 ELSE 0 END) AS TotalRefunds ,
		SUM(CASE WHEN Transc_curValue<0 THEN Transc_curValue ELSE 0 END) AS RefundValue,
		ISNULL(@Cinema,C.Cinema_strCode) AS Cinema  ,
		ISNULL(@CinemaDescription,C.Cinema_strName)
FROM	Vista.dbo.tblTrans_Cash 

		CROSS JOIN tblCinema C
WHERE	--TransC_strType= @CardStrType and 
(TransC_strType= @CardStrType)

GROUP BY
		convert(datetime,convert(char(10),TransC_dtmRealTransTime,101)),
		ISNULL(@Cinema,C.Cinema_strCode),
		ISNULL(@CinemaDescription,C.Cinema_strName)

order by 	convert(datetime,convert(char(10),TransC_dtmRealTransTime,101))

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-12-01 : 08:04:46

I think this will do the trick?
Select
@DateFrom,
@DateTo,
convert(datetime,convert(char(10),T1.TransC_dtmRealTransTime,101)) AS DateRecord,
Count(*) AS TotalTransactions,
SUM(CASE WHEN T1.Transc_curValue>=0 THEN 1 ELSE 0 END) AS TotalPayments ,
SUM(CASE WHEN T1.Transc_curValue>=0 THEN T1.Transc_curValue ELSE 0 END) AS PaymentValue ,
SUM(CASE WHEN T2.Transc_curValue<0 THEN 1 ELSE 0 END) AS TotalRefunds ,
SUM(CASE WHEN T2.Transc_curValue<0 THEN T2.Transc_curValue ELSE 0 END) AS RefundValue,
ISNULL(@Cinema,C.Cinema_strCode) AS Cinema ,
ISNULL(@CinemaDescription,C.Cinema_strName)

From tblTrans_Cash T1
left Join tblTrans_Cash T2 on T2.transC_lgnnumber=T1.transC_lgnnumber
and T2.TransC_strType='E'
CROSS JOIN tblCinema C
Where T1.TransC_strType=@CardStrType

AND T1.TransC_dtmRealTransTime BETWEEN @DateFrom AND @DateTo
group by
convert(datetime,convert(char(10),T1.TransC_dtmRealTransTime,101)),
ISNULL(@Cinema,C.Cinema_strCode),
ISNULL(@CinemaDescription,C.Cinema_strName)
order by convert(datetime,convert(char(10),T1.TransC_dtmRealTransTime,101))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources