Need help on grouping - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Need help on grouping

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-04-22 : 07:18:45

I've following table,

PayerTrnx
TrnxID | RefNo | RefGroupNo | CtrMonth | CtrYear |PayerNme | DrAmount | PaymentDte | PayerBank
---------------------------------------------------------------------------------------------------------
1 | 1009 | 1001 | 4 | 2008 | Julie | 54.0000 | 4/19/2008 | KUB
2 | 1010 | 1001 | 4 | 2008 | Mick | 30.0000 | 4/19/2008 | KUB
3 | 1011 | 1001 | 4 | 2008 | Karl | 21.0000 | 4/19/2008 | KUB
4 | 1012 | 1002 | 5 | 2008 | Bon | 29.0000 | 5/28/2008 | LOP
5 | 1013 | 1002 | 5 | 2008 | Julie | 44.0000 | 5/28/2008 | LOP
6 | 1014 | 1002 | 5 | 2008 | Marsden | 74.0000 | 5/28/2008 | LOP
7 | 1016 | 1002 | 5 | 2008 | Myers | 50.0000 | 5/28/2008 | LOP
8 | 1017 | 1003 | 6 | 2008 | Hanna | 30.0000 | 6/29/2008 | HYU
9 | 1018 | 1003 | 6 | 2008 | Suzie | 22.0000 | 6/29/2008 | HYU

I want to group by RefGroupNo. How to query and get the result as follow,

Payment Date | Payer Bank | CtrButionFor |No Of Transaction | Total Amount
--------------------------------------------------------------------------------
4/19/2009 | KUB | Apr 2008 | 3 | 105
5/28/2009 | LOP | May 2008 | 4 | 197
6/29/2009 | HYU | Jun 2008 | 2 | 52

If possible, Please help ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-22 : 07:30:22

Try this

select PaymentDate ,PayerBank ,dateadd(month,ctrmonth-1,dateadd(year,ctryear-1900,0)) as CtrButionFor , count(*) as [No Of Transaction],sum(DrAmount) as [Total Amount] from yourtable
group by PaymentDate ,PayerBank ,dateadd(month,ctrmonth-1,dateadd(year,ctryear-1900,0))

Madhivanan

Failing to plan is Planning to fail

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-04-22 : 10:35:58

Hi Mr. Madhivanan,

After follow your SQL, i got almost what i want. But why

CtrButionFor
----------------
4/1/2009
5/1/2009

How to make it this
CtrButionFor
Apr 2009
May 2009

???

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-04-22 : 10:42:50

Ok. I know. I can adjust my front-end. It can be display Apr 2009 and soon. TQ

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-22 : 10:42:59

quote:
Originally posted by Delinda

Hi Mr. Madhivanan,

After follow your SQL, i got almost what i want. But why

CtrButionFor
----------------
4/1/2009
5/1/2009

How to make it this
CtrButionFor
Apr 2009
May 2009

???

If you want to show them in formt end application, do formation there

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-22 : 10:44:09

quote:
Originally posted by Delinda

Ok. I know. I can adjust my front-end. It can be display Apr 2009 and soon. TQ

You understood faster than I typed the same

Madhivanan

Failing to plan is Planning to fail

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-04-22 : 12:54:15

Regarding the date as follow,

CrtDte
--------------
4/22/2009 11:33:43 PM

i perform,
select CONVERT(CHAR(10),CrtDte,121), it will return
2009-04-23

how to adjust my query above, to make it CrtDte return as
CrtDte
---------------
2009-04-23 00:00:00.000

???

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-22 : 13:55:40

Try something like this:

DECLARE @Date DATETIME

SET @Date = '4/22/2009 11:33:43 PM'

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0)

--Result
-----------------------
2009-04-22 00:00:00.000

dsindo
Starting Member

45 Posts

Posted - 2009-04-22 : 16:20:05

select case when datepart(mm,crtdte) =1 then 'Jan ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =2 then 'Feb ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =3 then 'Mar ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =4 then 'Apr ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =5 then 'May ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =6 then 'Jun ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =7 then 'Jul ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =8 then 'Aug ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =9 then 'Sep ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =10 then 'Oct ' + cast(datepart(yyyy,crtdte) as varchar(12))
when datepart(mm,crtdte) =11 then 'Nov ' + cast(datepart(yyyy,crtdte) as varchar(12))
else 'Dec' end as CtrButionFor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources