grouped by month

Dear All,

I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)

So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.

Not sure what would be the right approach here.

I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?

Will this work?group by month(Date)

More over
group by year(date), month(date)

GROUP BY DateTime

Dear All,
Did a research, seems like the only solution in SQL Server is:
SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
CONVERT(CHAR(8), RecTime , 112);
The RecTime is a DateTime field, and I wish to sort by the Date
Component.
Any simpler solution?<ckkwan@.my-deja.com> wrote in message
news:58c5c9b0-1fd1-43f5-aac3-360c83d7b2a7@.w1g2000prd.googlegroups.com...
> Dear All,
> Did a research, seems like the only solution in SQL Server is:
> SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
> CONVERT(CHAR(8), RecTime , 112);
> The RecTime is a DateTime field, and I wish to sort by the Date
> Component.
> Any simpler solution?
In SQL2000/2005, not really. But really, it's not that complex.
There's variations on this, but they're all about the same idea.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||ckkwan@.my-deja.com wrote:
> Dear All,
> Did a research, seems like the only solution in SQL Server is:
> SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
> CONVERT(CHAR(8), RecTime , 112);
> The RecTime is a DateTime field, and I wish to sort by the Date
> Component.
> Any simpler solution?
Definitely not the only solution!
Are there any simpler solutions? No. But there is a faster solution.
AFAIK this is the best performing solution:
SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0)
FROM RecTbl
GROUP BY DATEDIFF(day, 0, us_Hitdate)
--
Gert-Jan
SQL Server MVP|||Gert
You meant
GROUP BY DATEADD(day, DATEDIFF(day,0,EstimateDate) ,0)
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:480657AB.F6B90AE9@.toomuchspamalready.nl...
> ckkwan@.my-deja.com wrote:
>> Dear All,
>> Did a research, seems like the only solution in SQL Server is:
>> SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
>> CONVERT(CHAR(8), RecTime , 112);
>> The RecTime is a DateTime field, and I wish to sort by the Date
>> Component.
>> Any simpler solution?
> Definitely not the only solution!
> Are there any simpler solutions? No. But there is a faster solution.
> AFAIK this is the best performing solution:
> SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0)
> FROM RecTbl
> GROUP BY DATEDIFF(day, 0, us_Hitdate)
> --
> Gert-Jan
> SQL Server MVP|||Gert is correct, no need to have the DATEADD in the GROUP BY as it does not
change the grouping. Try this:
SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0), COUNT(*)
FROM (SELECT CURRENT_TIMESTAMP
UNION ALL
SELECT DATEADD(hh, 1, CURRENT_TIMESTAMP)) AS T(us_HitDate)
GROUP BY DATEDIFF(day, 0, us_Hitdate);
Plamen Ratchev
http://www.SQLStudio.com|||Nope :-)
--
Gert-Jan
Uri Dimant wrote:
> Gert
> You meant
> GROUP BY DATEADD(day, DATEDIFF(day,0,EstimateDate) ,0)
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:480657AB.F6B90AE9@.toomuchspamalready.nl...
> > ckkwan@.my-deja.com wrote:
> >>
> >> Dear All,
> >>
> >> Did a research, seems like the only solution in SQL Server is:
> >>
> >> SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
> >> CONVERT(CHAR(8), RecTime , 112);
> >>
> >> The RecTime is a DateTime field, and I wish to sort by the Date
> >> Component.
> >>
> >> Any simpler solution?
> >
> > Definitely not the only solution!
> >
> > Are there any simpler solutions? No. But there is a faster solution.
> > AFAIK this is the best performing solution:
> >
> > SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0)
> > FROM RecTbl
> > GROUP BY DATEDIFF(day, 0, us_Hitdate)
> >
> > --
> > Gert-Jan
> > SQL Server MVP|||Plamen
Have you run your script on SS2000?
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:9814BC3D-0B4E-401C-B18D-868581495690@.microsoft.com...
> Gert is correct, no need to have the DATEADD in the GROUP BY as it does
> not change the grouping. Try this:
> SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0), COUNT(*)
> FROM (SELECT CURRENT_TIMESTAMP
> UNION ALL
> SELECT DATEADD(hh, 1, CURRENT_TIMESTAMP)) AS T(us_HitDate)
> GROUP BY DATEDIFF(day, 0, us_Hitdate);
> Plamen Ratchev
> http://www.SQLStudio.com|||Yes, that was a limitation in SQL 2000... But I would assume by today's
standards SQL 2005 is the base to measure. :)
Plamen Ratchev
http://www.SQLStudio.com|||I'd prefer ask people what version they are using. There are still lots of
businesses use SQL Server 2000/
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:6D6390C6-57B3-470F-80FC-8E4DA9F4A3C5@.microsoft.com...
> Yes, that was a limitation in SQL 2000... But I would assume by today's
> standards SQL 2005 is the base to measure. :)
> Plamen Ratchev
> http://www.SQLStudio.com|||Yes, SQL Server 2000 or earlier will not accept the solution I posted.
In that case, it is easier to keep the Selection List items in sync with
the GROUP BY items.
However, if you really wanted to, you could achieve the same thing on
these version by writing:
SELECT DATEADD(day,MAX( DATEDIFF(day,0,my_date_column) ),0)
FROM ...
GROUP BY DATEDIFF(day, 0, my_date_column)
--
Gert-Jan
Uri Dimant wrote:
> I'd prefer ask people what version they are using. There are still lots of
> businesses use SQL Server 2000/
> "Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
> news:6D6390C6-57B3-470F-80FC-8E4DA9F4A3C5@.microsoft.com...
> > Yes, that was a limitation in SQL 2000... But I would assume by today's
> > standards SQL 2005 is the base to measure. :)
> >
> > Plamen Ratchev
> > http://www.SQLStudio.com

GROUP BY DateTime

Dear All,
Did a research, seems like the only solution in SQL Server is:
SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
CONVERT(CHAR(8), RecTime , 112);
The RecTime is a DateTime field, and I wish to sort by the Date
Component.
Any simpler solution?
<ckkwan@.my-deja.com> wrote in message
news:58c5c9b0-1fd1-43f5-aac3-360c83d7b2a7@.w1g2000prd.googlegroups.com...
> Dear All,
> Did a research, seems like the only solution in SQL Server is:
> SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
> CONVERT(CHAR(8), RecTime , 112);
> The RecTime is a DateTime field, and I wish to sort by the Date
> Component.
> Any simpler solution?
In SQL2000/2005, not really. But really, it's not that complex.
There's variations on this, but they're all about the same idea.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||ckkwan@.my-deja.com wrote:
> Dear All,
> Did a research, seems like the only solution in SQL Server is:
> SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
> CONVERT(CHAR(8), RecTime , 112);
> The RecTime is a DateTime field, and I wish to sort by the Date
> Component.
> Any simpler solution?
Definitely not the only solution!
Are there any simpler solutions? No. But there is a faster solution.
AFAIK this is the best performing solution:
SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0)
FROM RecTbl
GROUP BY DATEDIFF(day, 0, us_Hitdate)
Gert-Jan
SQL Server MVP
|||Gert
You meant
GROUP BY DATEADD(day, DATEDIFF(day,0,EstimateDate) ,0)
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:480657AB.F6B90AE9@.toomuchspamalready.nl...
> ckkwan@.my-deja.com wrote:
> Definitely not the only solution!
> Are there any simpler solutions? No. But there is a faster solution.
> AFAIK this is the best performing solution:
> SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0)
> FROM RecTbl
> GROUP BY DATEDIFF(day, 0, us_Hitdate)
> --
> Gert-Jan
> SQL Server MVP
|||Gert is correct, no need to have the DATEADD in the GROUP BY as it does not
change the grouping. Try this:
SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0), COUNT(*)
FROM (SELECT CURRENT_TIMESTAMP
UNION ALL
SELECT DATEADD(hh, 1, CURRENT_TIMESTAMP)) AS T(us_HitDate)
GROUP BY DATEDIFF(day, 0, us_Hitdate);
Plamen Ratchev
http://www.SQLStudio.com
|||Nope :-)
Gert-Jan
Uri Dimant wrote:[vbcol=seagreen]
> Gert
> You meant
> GROUP BY DATEADD(day, DATEDIFF(day,0,EstimateDate) ,0)
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:480657AB.F6B90AE9@.toomuchspamalready.nl...

Group By Count

Dear all

How do i group the count by Top ?

For example, We want to show Top 5 high salary people in my company , but may top 3 people's salary is same. So I want to sperate 5 groups, the output data look like this :
[Top 1 high salary] - Philip , Alex , David
[Top 2 high salary] - Emily
[Top 3 high salary] - Tom, May, Ada
[Top 4 high salary] - Queenie
[Top 5 high salary] - Ellie, Candy, Hazel, Stella, Tanya, Jacky

Hence, the total count of name should be over 5, so how do i output this record ? because it should just show 5 peoples (Philip, Alex, David, Emily and Tom) if i just use "Top 5" .

Thanks you for your helpin SQL server, you could declare table variable, and insert those names into the table variable, then return from the table variable|||This is effectively a pivot on top of aggregation. That means that it is best done on the client.

With that said, it can be done in SQL. Something like:CREATE TABLE dbo.salary (
name VARCHAR(20) NOT NULL
, salary MONEY NOT NULL
)

INSERT dbo.salary (salary, [name]) VALUES (50, 'Phillip')
INSERT dbo.salary (salary, [name]) VALUES (50, 'Alex')
INSERT dbo.salary (salary, [name]) VALUES (50, 'David')
INSERT dbo.salary (salary, [name]) VALUES (40, 'Emily')
INSERT dbo.salary (salary, [name]) VALUES (30, 'Tom')
INSERT dbo.salary (salary, [name]) VALUES (30, 'May')
INSERT dbo.salary (salary, [name]) VALUES (30, 'Ada')
INSERT dbo.salary (salary, [name]) VALUES (20, 'Queenie')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Ellie')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Candy')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Hazel')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Stella')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Tanya')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Jacky')

INSERT dbo.salary (salary, [name]) VALUES ( 5, 'Sam')
INSERT dbo.salary (salary, [name]) VALUES ( 5, 'Sammy')

INSERT dbo.salary (salary, [name]) VALUES ( 3, 'Clyde')

SELECT TOP 5 r.salary, Min(s1.[name]) + Coalesce( ', ' + Min(s2.[name]), '')
+ Coalesce( ', ' + Min(s3.[name]), '') + Coalesce( ', ' + Min(s4.[name]), '')
+ Coalesce( ', ' + Min(s5.[name]), '')
FROM (SELECT TOP 5 salary FROM dbo.salary GROUP BY salary ORDER BY salary DESC) AS r
JOIN (SELECT salary, [name] FROM dbo.salary) AS s1
ON (s1.salary = r.salary)
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s2
ON (s2.salary = r.salary
AND s2.[name] > s1.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s3
ON (s3.salary = r.salary
AND s3.[name] > s2.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s4
ON (s4.salary = r.salary
AND s4.[name] > s3.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s5
ON (s5.salary = r.salary
AND s5.[name] > s4.[name])
GROUP BY r.salary
ORDER BY r.salary DESC-PatP

group by and having cluase problem

dear sir/madam
I have given a table
name, dept, sal, doj
n1, d1, 1000, 1/2/2001
n2, d2, 2000, 2/3/2005
n3, d3, 3000, 3/2/2002
n4, d4, 4000, 4/7/2003
n5, d5, 5000,12/5/2006
n6, d2, 6000,3/2/2005
I have to select dept wise record doj should not be in group by but
all record should be doj > 1/2/200. My main problem is that if I dont
take doj in group by then how can I select record on condition of doj.
Please Help me
Thanks in Advance
If you need to filter all rows by the condition doj > 1/2/2001 you can add
this filter to the WHERE clause:
WHERE doj > '20010102'
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Hi
Can you explain what are you trying to achive better?
create table #t (name char(2),dept char(2),sal int,doj datetime)
go
insert into #t values ('n1','d1',1000,'20010201')
insert into #t values ('n2','d2',2000,'20050302')
insert into #t values ('n3','d3',3000,'20020203')
insert into #t values ('n4','d4',4000,'20030704')
insert into #t values ('n5','d5',5000,'20060512')
insert into #t values ('n6','d6',6000,'20050203')
select * from #t where doj=....
Please post desired result
<vinodkus@.gmail.com> wrote in message
news:25c5cfc8-e9d9-4c05-9538-235ac482adf2@.e23g2000prf.googlegroups.com...
> dear sir/madam
> I have given a table
> name, dept, sal, doj
> n1, d1, 1000, 1/2/2001
> n2, d2, 2000, 2/3/2005
> n3, d3, 3000, 3/2/2002
> n4, d4, 4000, 4/7/2003
> n5, d5, 5000,12/5/2006
> n6, d2, 6000,3/2/2005
> I have to select dept wise record doj should not be in group by but
> all record should be doj > 1/2/200. My main problem is that if I dont
> take doj in group by then how can I select record on condition of doj.
> Please Help me
> Thanks in Advance

group by and having cluase problem

dear sir/madam
I have given a table
name, dept, sal, doj
n1, d1, 1000, 1/2/2001
n2, d2, 2000, 2/3/2005
n3, d3, 3000, 3/2/2002
n4, d4, 4000, 4/7/2003
n5, d5, 5000,12/5/2006
n6, d2, 6000,3/2/2005
I have to select dept wise record doj should not be in group by but
all record should be doj > 1/2/200. My main problem is that if I dont
take doj in group by then how can I select record on condition of doj.
Please Help me
Thanks in AdvanceIf you need to filter all rows by the condition doj > 1/2/2001 you can add
this filter to the WHERE clause:
WHERE doj > '20010102'
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hi
Can you explain what are you trying to achive better?
create table #t (name char(2),dept char(2),sal int,doj datetime)
go
insert into #t values ('n1','d1',1000,'20010201')
insert into #t values ('n2','d2',2000,'20050302')
insert into #t values ('n3','d3',3000,'20020203')
insert into #t values ('n4','d4',4000,'20030704')
insert into #t values ('n5','d5',5000,'20060512')
insert into #t values ('n6','d6',6000,'20050203')
select * from #t where doj=....
Please post desired result
<vinodkus@.gmail.com> wrote in message
news:25c5cfc8-e9d9-4c05-9538-235ac482adf2@.e23g2000prf.googlegroups.com...
> dear sir/madam
> I have given a table
> name, dept, sal, doj
> n1, d1, 1000, 1/2/2001
> n2, d2, 2000, 2/3/2005
> n3, d3, 3000, 3/2/2002
> n4, d4, 4000, 4/7/2003
> n5, d5, 5000,12/5/2006
> n6, d2, 6000,3/2/2005
> I have to select dept wise record doj should not be in group by but
> all record should be doj > 1/2/200. My main problem is that if I dont
> take doj in group by then how can I select record on condition of doj.
> Please Help me
> Thanks in Advance