May 19, 2014 at 12:24 am
Hi Friends
i ve the table like
create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)
insert into expense values('01-04-2014',1200,250,0,0)
('02-04-2014','0',0,500,600)
('0-04-2014','800',300,0,0)
like i ve 30th onwards.....
expecting o/p:
month Travel Fixed Food Lodge
apr-14 200 550 500 600
these cum column values how to make a code ?????
May 19, 2014 at 12:46 am
Is this what you are looking for
select MONTH(bill_date), YEAR(bill_date),SUM(travel),SUM(fixed),SUM(food),SUM(lodge)
from dbo.expense
group by MONTH(bill_date), YEAR(bill_date)
order by MONTH(bill_date), YEAR(bill_date)
Difficult to know if that's what you want as your sample data don't seem to relate to the expected result.
May 19, 2014 at 12:53 am
Try something like this:
SELECT
CONVERT(nvarchar(30), GETDATE(), 6) AS Month ;
GO
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 19, 2014 at 1:18 am
Also you can use following to trim result:
SELECT
SUBSTRING (CONVERT(nvarchar(15), GETDATE(), 6), 4,6) AS Month;
GO
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 19, 2014 at 4:22 am
Hi friend,
Here one problem is there
my table contains the info authorized date also
i.e
create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)
insert into expense values('01-04-2014 00:00:00',1200,250,0,0)
('02-04-2014 00:00:00','0',0,500,600)
('0-04-2014 00:00:00','800',300,0,0)
like i ve 30th onwards.....
when accounts people authorisethe travel claim the current info total values of expenses ll stored in that table
('15-05-2014 14:22:17',200, 550, 500, 600)
when i tried to give the date range '2014-03-01' to '2014-05-31'
these authorized date give confudion...how to neglect that???
May 19, 2014 at 8:04 am
I don't see an authorized date column in your table, but if you want to exclude anything that hasn't been authorized, simply use a where clause to exclude the unauthorized rows. For example:
AND AuthorizedDate IS NOT NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply