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.
| 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
|
|
|
|
|
|
|
|
|
Need help on grouping