October 25, 2012 at 10:41 am
Good afternooon all,
IM having problems with what seems to be a simple query but is proving to be a pain....
First off my datatable:
Job RefBooked Date
A10/10/2012
A11/10/2012
B10/10/2012
C12/10/2012
C13/10/2012
C14/10/2012
So my query i have written
Select [Job Ref], MAX([Booked Date]),MIN([Booked Date])
what i want to return is:
Job RefMax DateMin Date
A10/10/201211/10/2012
B10/10/201210/10/2012
C12/10/201214/10/2012
But am at a loss as to why i am not getting the max and min but ony the min value....
any ideas how i could solve this
Thanks
October 25, 2012 at 10:51 am
Are you grouping by job ref?
October 25, 2012 at 10:53 am
I only found a problem with your query but it should throw an error and not incorrect results. The problem is the query is not complete.
Could you post DDL?
As a suggestion, don't use spaces for names of objects or columns in your database.
October 25, 2012 at 10:53 am
Are you getting any error messages? If all you are running is Select [Job Ref], MAX([Booked Date]),MIN([Booked Date])
you should be getting an error. If so id like to point you to http://www.w3schools.com/sql/sql_select.asp your Query syntax is off.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 25, 2012 at 11:03 am
I don't know if I'm fully understanding, but are you looking for something like this? (Forgive me - I'm not totally sure on your date format so I switched it around).
CREATE TABLE YourTable
(
JobRefVARCHAR(25) NOT NULL,
BookedDate Date NOT NULL
)
;
INSERT INTO YourTable
(
JobRef,
BookedDate
)
Values
('A', '2012-10-10'),
('A', '2012-10-11'),
('B', '2012-10-10'),
('C', '2012-10-12'),
('C', '2012-10-13'),
('C', '2012-10-14')
SELECT JobRef, MAX(BookedDate) AS Max, MIN(BookedDate) AS Min
FROM YourTable
GROUP BY JobRef
;
--clean up
DROP TABLE YourTable
October 25, 2012 at 11:43 pm
it seems you have DD/MM/YYYY date formate
whats you column data type.
Question:but why you want maximum date in Min(Booked Date)
Like
Job Ref Max Date Min Date
C 12/10/2012 14/10/2012
now 14/10/2012 is maximum then 12/10/2012
can you give more focus on this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply