Nice one, please read and give your comments.

Question

Post reply

Nice one, please read and give your comments.

Hariprasad.M

SSC Veteran

Points: 236
More actions
April 6, 2010 at 1:05 am

#220043

Hi friends,
I came to know this from a book, want to share this with you all.
Below is the Table structure and i need output as mentioned below
Date |Amount |Type
----------- --------- ------
1/1/2010 |10 |Debit
1/1/2010 |20 |Credit
1/2/2010 |30 |Debit
Output
Date |Debit |Credit
--------- -------- --------
1/1/2010 |20 |10
1/2/2010 |0 |30
we can do this with out using Pivot and Case statements.
below is the query for that.
select Date, Sum(instr(Type, 'Debit) * Amount) AS Debit,
Sum(Instr(Type, 'Credit') * Amount) AS Credit
From <Table Name>
Group by Date
Good ah 🙂
Regards
Hariprasad

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Mate, are u sure we have instr function in SQL Server ? :blink: :blink:

Hariprasad.M SSC Veteran Points: 236 More actions · Answer 2

Sorry Instead of Instr we can use CHARINDEX.

Regards

Hariprasad

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

IMO

select Date,

Sum(Case when Type ='Debit' then Amount else 0 end) AS Debit,

Sum(Case when Type ='Credit' then Amount else 0 end) AS Credit

From <Table Name>

Group by Date

Is Easier to read

Clear Sky SQL
My Blog[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 4

Dave Ballantyne (4/6/2010)
IMO
select Date,
Sum(Case when Type ='Debit' then Amount else 0 end) AS Debit,
Sum(Case when Type ='Credit' then Amount else 0 end) AS Credit
From <Table Name>
Group by Date
Is Easier to read

And perhaps it doesn't transpose your credit/debit totals? =) (Output above is backwards)

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Could you post up some working code that uses CHARINDEX?

I'm not sure I understand how that works or what it will do for you.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Hariprasad.M SSC Veteran Points: 236 More actions · Answer 6

Here is the updated script with careindex

select DDate, Sum(Charindex('Debit',Type) * Amount) AS Debit,

Sum(Charindex('Credit', Type) * Amount) AS Credit

From #Demo

group by Ddate

It will transpose rows into column (like pivot).

this can be a replacement for pivot & case

Regards

Hariprasad

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Got it. That is the first time I've seen this trick, and it's a clever variation. Thanks for sharing it. 🙂

I just tested the both the CHARINDEX and CASE variations 100,000 rows. The code is posted below. The CASE crosstab seemed to only take about 80% of the CPU cycles, but both are pretty quick. It's not surprising since they are both cross-tab solutions. Even in the absence of a CASE statement, doing conditional sums with a GROUP BY was the old school way of of consolidating rows into columns before PIVOT was created.

It is true that this CHARINDEX technique eliminates the need to do a CASE statement, but I am also in the camp that says the CASE statements are easier to read. Maybe that's just because I'm used to seeing it.

The real problem is that the CHARINDEX variation is limited in scope. Because it depends on multiplying a value by 1 or zero, it only works with numbers. Using CASE, you can also take a MAX() value of columns containing character data. I can't really see mixing the CHARINDEX and CASE techniques.

declare @demo table(ddate date, amount int, tType varchar(10))

insert into @demo

select top 100000

'1/'+left(cast(N as varchar),1)+'/10',

10 * cast(right(N,1) as int),

case when cast(right(N,1) as int) in (1,3,4,5,6,7) then 'Credit' else 'Debit' end

from tally

set statistics time on;

select DDate, Sum(Charindex('Debit',tType) * Amount) AS Debit,

Sum(Charindex('Credit', tType) * Amount) AS Credit

From @demo

group by Ddate

set statistics time off;

print '-------------------------------------------------------------------------'

set statistics time on;

select DDate,

sum(case when tType = 'Debit' then Amount else 0 end) AS Debit,

sum(case when tType = 'Credit' then Amount else 0 end) AS Credit

From @demo

group by Ddate

set statistics time off;

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills