October 28, 2008 at 4:42 pm
I HAVE A QUERY THIS WAY ..
select convert(varchar(12),datepart(m,date)) + '/' + convert(varchar(4),datepart(yyyy,date)) as date,
count(*) as total
from TBL1
where status = 'OUT'
group by convert(varchar(12),datepart(m,date)) + '/' + convert(varchar(4),datepart(yyyy,date))
order by 1
WHEN I AM DOING THIS I AM GETTING OUTPUT AS
10/2008
8/2008
9/2008
7/2008
BUT I NEED TO ORDER THIS IN DESC OR ASC BUT I COPUDNT DO THAT EVEN I ADDED THE SAME THING IN ORDER BY ALSO WHICH IS IN GROUP BY ...STILL I COULDNT FIGURE IT OUT ....PLZ HELP ME OUT HOW TO ORDER THIS AS
10/2008
9/2008
8/2008
7/2008
PLZ DO LET ME KNOW A BIT URGENT ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
October 28, 2008 at 4:51 pm
Read the article identified below, then post the DDL for the table, some sample data, and the expected results based on the sample data. This will help us help you better.
October 28, 2008 at 5:08 pm
Try this:
Select convert(char(7), date, 120) As DatePeriod
,count(*) As Total
From TBL1
Where Status = 'OUT'
Group By
convert(char(7), date, 120)
Order By
DatePeriod;
If that doesn't work, use format 112 and convert to char(6) which will return YYYYMM.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 28, 2008 at 5:21 pm
jeffrey...
if i consider 112 and take char(6) then io shall get 200810 i mean yyyymm
but i need to order by date....will i get that ...
create table #abc
(
id int,
date datetime,
status
)
insert into #abc values (1,'10/02/2008','out')
insert into #abc values (2,'7/02/2008','out')
insert into #abc values (3,'7/02/2008','in')
insert into #abc values (4,'08/02/2008','in')
insert into #abc values (5,'10/02/2008','out')
insert into #abc values (6,'9/02/2008','out')
insert into #abc values (7,'10/02/2008','out')
insert into #abc values (8,'10/02/2008','out')
insert into #abc values (9,'7/02/2008','out')
select datepart(mm,date) + '/' + datepart(yyyy,date) ,count(*) from #abc
where status ='out'
group by datepart(mm,date) + '/' + datepart(yyyy,date)
order by 1
----present output order ---i am getting as
10/2008----4
7/2008----2
9/2008----1
required order----i need to get the data as in this order but i am not getting it ...
date------count
7/2008----2
9/2008----1
10/2008----4
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
October 28, 2008 at 5:57 pm
Seems to me you should be doing something like this.
I don't have SQL Server at home, so I couldn't test this code.
select datepart(mm,date) + '/' + datepart(yyyy,date) AS MonthYear
,count(*) from #abc
where status ='out'
group by datepart(mm,date) + '/' + datepart(yyyy,date)
order by MonthYear
October 28, 2008 at 5:58 pm
Thanks Jeff first sol works well......
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
October 28, 2008 at 5:59 pm
Grabber i tried that way... it did not work for me ....anyway i got that resolved by jeff post...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
October 28, 2008 at 10:39 pm
I am happy that worked for you - I think either YYYYMM or YYYY-MM would work the same way and ordered the way you want it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 28, 2008 at 10:42 pm
jeff i have another issue could u help me out with that ....
http://www.sqlservercentral.com/Forums/Topic593360-338-1.aspx
Thanks is advance
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
October 29, 2008 at 9:28 am
Not sure how I can help on that other issue - looks like you have a couple of people who already have given you an answer that should work. If not, post back on that thread and I will pick up on it if needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 29, 2008 at 9:30 am
yeah got it...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
October 30, 2008 at 11:55 am
Part of your problem is that you are trying to get polished results from your query. Let SQL do what it does best, retrieve data, and let your application do what it does best, pretty the data up to look nice (or more meaningful). If you do that, your query becomes simple.
declare @abc table(
id int,
date datetime,
status varchar(10)
);
insert @abc
(id, date, status)
select 1, '10/02/2008','out' union all
select 2, '07/02/2008','out' union all
select 3, '07/02/2008','in' union all
select 4, '08/02/2008','in' union all
select 5, '10/02/2008','out' union all
select 6, '09/02/2008','out' union all
select 7, '10/02/2008','out' union all
select 8, '10/02/2008','out' union all
select 9, '07/02/2008','out';
select DateAdd( mm, DateDiff( mm, 0, Date ), 0 ) as YearMonth, Count(*) as Total
from @abc
where status ='out'
group by DateAdd( mm, DateDiff( mm, 0, Date ), 0 )
order by DateAdd( mm, DateDiff( mm, 0, Date ), 0 );
However, if you really need polished output from SQL, take the query above as a derived table and manipulate the results all you want.
select Convert( varchar, DatePart( mm, YearMonth )) + '\' +
Convert( varchar, DatePart( yy, YearMonth )) as MonthAndYear,
Total
from(
select DateAdd( mm, DateDiff( mm, 0, Date ), 0 ) as YearMonth, Count(*) as Total
from @abc
where status ='out'
group by DateAdd( mm, DateDiff( mm, 0, Date ), 0 )
) x
order by YearMonth;
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply