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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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))
Go to Top of Page

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))
Go to Top of Page

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))
Go to Top of Page
   

- Advertisement -