July 15, 2012 at 7:29 am
Hi All,
OK let me see if I can explain the problem. I have some SQL in an SSRS report that I've been asked to modify, essentialy it's looking at all the payments coming in and out, It lists various fields such as Currency, who's on the other side of the deal, Amounts etc.
The DB has two tables that are used. Table A contains all the transactions that are yet to clear and Table B contains the cleared transactions. There is a big Union query in the report and it gets out all the data in a similar format to this...
Euro's
Our SideTheir Side Rate AmountStatus
Our CorpTheir Company 1.26 100000 Cleared
Our CorpTheir Company 1.26 34976 Pending
...there are a lot more fields than that but essentially the difference between each row is what the status is and obv the amount).
What they want to do is have all the data on the same row (they're exporting out to CSV so this is important for them) and instead have this kind of arrangement...
Euro's
Our SideTheir Side Rate Amount ClearedAmount pending
Our CorpTheir Company 1.26 100000 34976
So I'll need to create a new field and have the logic behind it either in the Sql query or SSRS to only display a single row .
It's a pretty huge Union query with all the different fields so the less changes I have to do the better and if you could explain it like you're talking to a 5 year old that would be great as I'm new to this 😀
I feel this should be really easy but I just can't seem to find out how to do it and time is running out.
All help greatly Appreciated.
July 15, 2012 at 10:09 am
as a start... lets assume you have something like this...at the moment we have nothing to work with.
is this anywhere close to being representative of your data?
pls amend accordingly and post back...we can then maybe help you a little faster and more accurate.
We will also need the results you expect based on the sample data.
Any probs pls post back
CREATE TABLE [dbo].[Cleared](
[CompanyID] [int] NULL,
[Rate] [decimal](10, 4) NULL,
[Amount] [int] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Cleared]([CompanyID], [Rate], [Amount])
SELECT 1, 1.2600, 100000 UNION ALL
SELECT 2, 1.2400, 50000 UNION ALL
SELECT 1, 1.2800, 200000 UNION ALL
SELECT 3, 1.2300, 400000
CREATE TABLE [dbo].[Pending](
[CompanyID] [int] NULL,
[Rate] [decimal](10, 4) NULL,
[Amount] [int] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Pending]([CompanyID], [Rate], [Amount])
SELECT 1, 1.1800, 50000 UNION ALL
SELECT 2, 1.3200, 250000 UNION ALL
SELECT 1, 1.3100, 400000 UNION ALL
SELECT 3, 1.2900, 300000
SELECT CompanyID, Rate, Amount, 'Cleared' AS Status
FROM Cleared
UNION ALL
SELECT CompanyID, Rate, Amount, 'Pending' AS Status
FROM Pending
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 18, 2012 at 9:29 pm
Hi There,
Thanks for the replys and apologies for not following the board etiquette, lets see if I can remedy that with this question.
OK so I've looked at these suggestions and others online and come up with something that works. Horray 😀 the other thing I need to do is convert positives to negatives depending on direction and then sum this up (all table values are positive but the direction of the transaction dictates whether I should display as a negative or not.
So my code looks like this....
Select CCy,
Max(Case Status WHEN 'enRoute' THEN Sum ELSE '' END) as AmountenRoute,
Max(Case Status WHEN 'Arrived' THEN Sum Else '' END) as AmountArrived
from (
Select Currency as CCy, Status,Sum(
CASE WHEN Table1.ReceivingParty = 'Them' THEN table1.Amount * (-1) ELSE table1.Amount END
) as sum
from Table1
Group By Currency, Status, ReceivingParty
UNION
Select CurrencyId as Ccy, 'arrived' as Status, Sum(
CASE WHEN table2.direction = 'sent' THEN table2.amount * (-1) ELSE table2.amount END) AS Sum
from table2
Group By CurrencyID, Status, direction
)Mytable
Group By CCy
So when I first did this I just had Sum(amount) and it works fine with the expected result set once I add the case statement in I find zero values where I previously had values. If I remove just the * (-1) part I get the values back (but obviously a sum of all positive values and so sometimes incorrect).
I've tried all kinds of variations with brackets and ABS() but I just get zero's back when I try this logic. I'm sure it's something obvious but I can't see the solution.
Any ideas?
July 19, 2012 at 12:01 am
declare @testtable table (name varchar(25), name1 varchar(max),rate decimal(10,2),Amount int,status varchar(25))
declare @testtable1 table (id int, date1 varchar(max))
declare @date1 varchar(max)
declare @i int =1
insert into @testtable values('Our Corp','Their Company',1.26,100000,'Cleared')
insert into @testtable values('Our Corp','Their Company',1.26,34976,'Pending')
insert into @testtable values('Our Corp1','Their Company1',2.30,100,'Cleared')
insert into @testtable values('Our Corp1','Their Company1',2.30,34,'Pending')
insert into @testtable values('Our Corp1','Their Company1',2.30,345,'Completed')
Select * from @testtable
Select name,name1,rate,Cleared,Pending,Completed FROM
(Select name,name1,rate,amount,status
FROM @testtable ) P
PIVOT
(SUM(Amount) FOR status in (Cleared,Pending,Completed))Q
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply