finding previous and current month total

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

General SQL Server Forums

New to SQL Server Programming

finding previous and current month total

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-08-06 : 08:28:54

Hi Friends,

I need the query for the below sample data.Here in LP_Payment table for the expertid 114 with reference as
'Expert' ,i need the total price for the current month ,previous month and total Price.

LP_Usermanagement

UserId
UserName

LP_Payment

PaymentId
UserId
ExpertId
Price
Reference
CreationDateTime

CREATE TABLE LP_UserManagement(UserId bigint NOT NULL,NickName varchar(128) NOT NULL)

insert into LP_UserManagement(UserId,NickName)values(1,'admin')
insert into LP_UserManagement(UserId,NickName)values(2,'sam')
insert into LP_UserManagement(UserId,NickName)values(3,'bin')
insert into LP_UserManagement(UserId,NickName)values(4,'sean')
insert into LP_UserManagement(UserId,NickName)values(9,'A')
insert into LP_UserManagement(UserId,NickName)values(70,'B')
insert into LP_UserManagement(UserId,NickName)values(114,'C')

CREATE TABLE LP_Payment(PaymentId bigint NOT NULL,UserId bigint NOT NULL,ExpertId bigint NOT NULL,Price float NOT NULL,Reference varchar(128) NULL,CreationDateTim Datetime NULL)

insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(1,4,6,4,'Expert','2009-04-30 02:26:15.710')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(2,9,14,4,'Expert','2009-04-30 02:27:22.410')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(3,2,1,10,'Admin','2009-06-08 11:27:49.503')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(4,2,1,10,'Admin','2009-06-08 11:29:06.537')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(5,2,1,10,'Admin','2009-06-08 11:32:14.817')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(6,2,1,10,'Admin','2009-06-08 11:39:21.350')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(7,2,1,250,'Admin','2009-06-08 11:47:39.870')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(8,70,1,21,'Advertisement','2009-07-18 07:02:51.237')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(9,114,1,63,'Membership','2009-07-31 00:55:52.380')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(10,2,114,4,'Escrow','2009-08-04 08:51:49.613')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(11,2,114,10,'Expert','2009-08-04 10:30:02.623')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(12,2,114,16,'Escrow','2009-08-04 13:15:58.860')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(13,2,114,16,'Escrow','2009-08-04 13:18:48.483')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(14,2,114,345,'Expert','2009-08-05 02:29:22.577')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(15,2,114,2,'Escrow','2009-08-05 02:39:06.377')
insert into LP_Payment(PaymentId,UserId,ExpertId,Price,Reference,CreationDateTime)values(16,2,114,25,'Expert','2009-07-05 03:01:39.670')

Expected Output
--------------------------

Prevmonth currentMonth Total
25 355 380

Thanks an Advance

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-06 : 08:43:39

[code]
select
sum(case when month(CreationDateTime) = month(dateadd(mm,-1,getdate())) then Price else 0 end) as Prevmonth,
sum(case when month(CreationDateTime) = month(getdate()) then Price else 0 end) as currentMonth
from LP_Payment
where ExpertId = 114
and year(CreationDateTime) = year(getdate())
[/code]

This will give you this months and last months. I have made this simple and not attempted to bother with the calculatuion for a split of year (Jan calculation) as you can work this out from the above.

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-08-06 : 11:08:55

Hi RickD,

Thanks for your response,

your query is working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources