SQL MIN AND MAX FUNCTION

I have a query that uses the Min and Max function but when it runs Max value and Min value are the same.
I know for a fact that there are different value for each item so I should always have a Min and Max value.

What am I missing?

Thanks

****
CREATE procedure [dbo].[ReportSHIFT]
@DA nvarchar (10),
@A nvarchar (10),
@Shift CHAR(1)

SELECT    
Report.DateT,
Report.Shift,
Report.DieCode,
Report.OrderNo,
MAX(Parameter.Holes),
MIN(Report.Time1) AS StartTime,
MAX(Report.Time1) AS EndTime,
MAX(DieChangeCode.ChgDescr) AS Description

FROM  Report INNER JOIN
Parameter ON Report.ID_Die = Parameter.ID_Die INNER JOIN
Orders ON Report.ID_Order = Orders.ID_Order INNER JOIN
DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode

GROUP BY Report.DateT, Report.Shift, Report.DieCode, Report.OrderNo  

HAVING(Report.DateT BETWEEN @DA AND @A) AND (Report.Shift = @Shift)

ORDER BY Report.DateT, StartTime asc
****

All values are correct except MIN(Report.Time1) and MAX(Report.Time1)
The query returns MAX in both columns.
DONADIEAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

santhimurthydCommented:
In your Querry your grouping the regords based on below condition

Report.DateT, Report.Shift, Report.DieCode, Report.OrderNo  and correct me in my understanding is wrong. Int he group by section "Report.OrderNo" will be an Unique record and which will retun only one record for the specific "OrderNo" in that case your MIN and MAX have to be same and wont be any change. Please check with your business case and provide more infor as what you need to get.

Will help to provide the SQL which meets your business requirment.

0
DONADIEAuthor Commented:
I attached a spreadsheet with the details of my main table and my query result.

Hope this help.

Thank you.
MySQLQuery.xlsx
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The only grouping column not shown in your XLSX is Report.Shift, so I suspect it is set only on the max value. A simple check is done by adding a count(*) to the SELECT list, but details are only revealed if you remove the grouping and aggregates.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

santhimurthydCommented:
I have tried with the below sample of SQL syntax it's workign fine from my side

create table maintable
(
      DateT datetime,
      TimeT time,
      diecode varchar(20),
      orderno int
)

insert into maintable(DateT ,TimeT , DieCode , OrderNo)values(convert(datetime,'19/12/2011',103),convert(time,'8:58:37',108),'e 9872/02',10103645)
insert into maintable(DateT ,TimeT , DieCode , OrderNo)values(convert(datetime,'19/12/2011',103),convert(time,'9:01:33',108),'e 9872/02',10103645)
insert into maintable(DateT ,TimeT , DieCode , OrderNo)values(convert(datetime,'19/12/2011',103),convert(time,'9:04:54',108),'e 9872/02',10103645)
insert into maintable(DateT ,TimeT , DieCode , OrderNo)values(convert(datetime,'19/12/2011',103),convert(time,'9:07:48',108),'e 9872/02',10103645)
insert into maintable(DateT ,TimeT , DieCode , OrderNo)values(convert(datetime,'19/12/2011',103),convert(time,'9:10:44',108),'e 9872/02',10103645)
insert into maintable(DateT ,TimeT , DieCode , OrderNo)values(convert(datetime,'19/12/2011',103),convert(time,'9:35:42',108),'E 9872/01',10103648)
insert into maintable(DateT ,TimeT , DieCode , OrderNo)values(convert(datetime,'19/12/2011',103),convert(time,'9:38:20',108),'E 9872/01',10103648)
insert into maintable(DateT ,TimeT , DieCode , OrderNo)values(convert(datetime,'19/12/2011',103),convert(time,'9:40:58',108),'E 9872/01',10103648)
insert into maintable(DateT ,TimeT , DieCode , OrderNo)values(convert(datetime,'19/12/2011',103),convert(time,'9:43:25',108),'E 9872/01',10103648)

select orderno , DateT , diecode , MIN(Timet) as StartTime , MAX(Timet) as StartTime  from maintable
Group by orderno , DateT , diecode

Please compare with your SQL and let me know as where it's failing.

One suggestion from my side is, try to execute the SQL wiht out GROUP / Having and see whether your getting the records first, then you can try to narrow down

SELECT    
Report.DateT,
Report.Shift,
Report.DieCode,
Report.OrderNo,
Parameter.Holes,
Report.Time1 AS StartTime,
Report.Time1 AS EndTime,
DieChangeCode.ChgDescr AS Description

FROM  Report
INNER JOIN Parameter ON Report.ID_Die = Parameter.ID_Die
INNER JOIN Orders ON Report.ID_Order = Orders.ID_Order
INNER JOIN DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode
ORDER BY Report.DateT, StartTime asc
0
deightonprogCommented:
what you want to do is amend your query so that you can actually see the values of Report.Time1 and Report.Time1, order by your grouping instead, and see if you can visually inspect where the values come from

0
DONADIEAuthor Commented:
In my previous excel file I missed to add the values from the other table. In fact my queru is using values from 4 different table.
Sorry about that.


MySQLQuery1.xlsx
0
CrashmanCommented:
in your file doesn't exist all columns and fields, you need to use "OVER PARTITION BY" and if you wish "RANK"

with the example of santhimurthyd

select  orderno,
        DateT,
        diecode,
        MIN(Timet) OVER ( PARTITION BY orderno, datet, diecode ) as MinStartTime,
        MAX(Timet) OVER ( PARTITION BY orderno, datet, diecode ) as MaxStartTime
from    maintable

Open in new window

you can create one query into another query, if you post the exact structure of you data, i can create the query complete
0
santhimurthydCommented:
with the below SQL it's workign perfectly for me

select r.orderno , r.DateT ,  MIN(r.Timet) as StartTime , MAX(r.Timet) as StartTime  ,
max(p.Holes) as ParameterHoles , r.diecode , d.Description
from Report r
inner join Parameter p on p.DieCode = r.diecode
INNER JOIN Orders O ON O.ID_Order = r.ID_Order
INNER JOIN DieChangeCode d ON d.ChgDieCode = r.ReasChg
Group by r.orderno , r.DateT , r.diecode , d.Description
ORDER BY r.DateT, 3 asc

Could oyu check
I haven't included the Having clause as don't have the value to check the same
0
CrashmanCommented:
in my experience, i can't say all time the aggregates, max or min works well, in the sql normal sintax, is better make sure, I use OVER PARTITION BY
0
DONADIEAuthor Commented:
ID:37315263
I tried your suggestion but unfortunately nothing change.


ID:37314987
Sounds good.
I'm sending a small ACCESS 2010 DB with exact structure of my data.
Anyway I have to use the query in SQL 2005 DB.
Thank you very much in advance.
DB-For-ExpertExchange.accdb
0
santhimurthydCommented:
Could oyu check what's the condition (Report.Shift = @Shift), I feel except everything is fine in your SQL and check what's the value your passing and in the Excel sheet you have shared doesn't have any column with the name "shift". Revalute the data and this specific condition and it be the main cause fro returning single row instead of multiple row which in turn gives Max and Min value are same as it returns only one row.
0
santhimurthydCommented:
Check with this SQL, it's working perfectly for me with your data

 
CREATE procedure [dbo].[ReportSHIFT]
@DA nvarchar (10),
@A nvarchar (10),
@Shift CHAR(1)
as
begin
declare @DA1 datetime
declare @A1 datetime
set @DA1 = convert(datetime,@DA,103)
set @A1 = convert(datetime , @A , 103)

SELECT     
Report.DateT,
Report.Shift, 
Report.DieCode,
Report.OrderNo, 
MAX(Parameter.Holes),
MIN(Report.Time1) AS StartTime, 
MAX(Report.Time1) AS EndTime, 
MAX(DieChangeCode.ChgDescr) AS Description 

FROM  Report INNER JOIN
Parameter ON Report.ID_Die = Parameter.ID_Die INNER JOIN
Orders ON Report.ID_Order = Orders.ID_Order INNER JOIN
DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode 

GROUP BY Report.OrderNo , Report.DateT, Report.Shift, Report.DieCode
HAVING(Report.DateT BETWEEN @DA1 AND @A1) AND (Report.Shift = @Shift)

ORDER BY Report.DateT, StartTime asc 

end

Open in new window

0
CrashmanCommented:
Ok, the problem is this:
You have this information:

2011-12-19       B      e 9872/02            10103645             2      09:10:44        09:10:44        
2011-12-19       B      E 9872/01            10103648             1      09:52:19        09:52:19        

what you need?, you need the Start Time and the End Time, but with conditionals are?
you need make differences in the times for every OrderNo? or for All information?, in the query is correct, because in the group by you have this

GROUP BY Report.DateT, Report.Shift, Report.DieCode, Report.OrderNo  

and you are saying, i need the start date and end date for every combination of Shift, DieCode and OrderNo
if we see the information, only for the first line, you have the same time, and is correct, for the combinations of the informations of the rows of the columns in the group.
if you need the max and the min for all row, you need think the group.
Put special attention to the columns in the partition by. in the second query i have the 4 columns and for that show the same info as the begining, and the last i use only one column to make the group and for that the difference will be only for the orderid...
sorry for my english is very poor
it's a little complicate...

SELECT  Report.DateT,
        Report.Shift,
        Report.DieCode,
        Report.OrderNo,
        Parameter.Holes,
        Report.Time1 AS StartTime,
        Report.Time1 AS EndTime,
        DieChangeCode.ChgDescr AS D
FROM    Report
        INNER JOIN Parameter ON Report.ID_Die = Parameter.ID_Die
        INNER JOIN Orders ON Report.ID_Order = Orders.ID_Order
        INNER JOIN DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode
/*
 * max min 
 */
 
SELECT  Report.DateT,
        Report.Shift,
        Report.DieCode,
        Report.OrderNo,
        MAX(Parameter.Holes) OVER ( PARTITION BY Report.DateT, Report.Shift,
                                    Report.DieCode, Report.OrderNo ) AS Holes,
        MIN(Report.Time1) OVER ( PARTITION BY Report.DateT, Report.Shift,
                                 Report.DieCode, Report.OrderNo ) AS StartTime,
        MAX(Report.Time1) OVER ( PARTITION BY Report.DateT, Report.Shift,
                                 Report.DieCode, Report.OrderNo ) AS EndTime,
        MAX(DieChangeCode.ChgDescr) OVER ( PARTITION BY Report.DateT,
                                           Report.Shift, Report.DieCode,
                                           Report.OrderNo ) AS D
FROM    Report
        INNER JOIN Parameter ON Report.ID_Die = Parameter.ID_Die
        INNER JOIN Orders ON Report.ID_Order = Orders.ID_Order
        INNER JOIN DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode

/*
 * Groups
 */
 
SELECT  Report.DateT,
        Report.Shift,
        Report.DieCode,
        Report.OrderNo,
        MAX(Parameter.Holes) OVER ( PARTITION BY Report.DateT ) AS Holes,
        MIN(Report.Time1) OVER ( PARTITION BY Report.DateT ) AS StartTime,
        MAX(Report.Time1) OVER ( PARTITION BY Report.DateT ) AS EndTime,
        MAX(DieChangeCode.ChgDescr) OVER ( PARTITION BY Report.DateT ) AS D
FROM    Report
        INNER JOIN Parameter ON Report.ID_Die = Parameter.ID_Die
        INNER JOIN Orders ON Report.ID_Order = Orders.ID_Order
        INNER JOIN DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode

Open in new window


hope will be helpful
0
DONADIEAuthor Commented:
ID:37316220
I need make differences in the times for every OrderNo.

Referring to my example DB this is what I would like to have:

                                               OrderNo             StartTime        EndTime
2011-12-19    B    e 9872/02   10103645     2      08:58:37        09:10:44        
2011-12-19    B    E 9872/01   10103648     1      09:35:42        09:52:19

Sorry even my English is very poor.
Thank you.
       
0
CrashmanCommented:
only change the column in the partition by
but the two orders only have on row with information in the example, that's mean, you only have one max and min in the data

SELECT  Report.DateT,
        Report.Shift,
        Report.DieCode,
        Report.OrderNo,
        MAX(Parameter.Holes) OVER ( PARTITION BY Report.OrderNo) AS Holes,
        MIN(Report.Time1) OVER ( PARTITION BY Report.OrderNo) AS StartTime,
        MAX(Report.Time1) OVER ( PARTITION BY Report.OrderNo) AS EndTime,
        MAX(DieChangeCode.ChgDescr) OVER ( PARTITION BY Report.OrderNo) AS D
FROM    Report
        INNER JOIN Parameter ON Report.ID_Die = Parameter.ID_Die
        INNER JOIN Orders ON Report.ID_Order = Orders.ID_Order
        INNER JOIN DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode

spanish:
depende de que estes agrupando, las dos filas que tengo en el ejemplo, solo tienen dos ordenes y dos tiempos, entonces el maximo y minimo es el mismo para cada orden, has pruebas con mas información, por eso solo te aparecen los mismos datos cambia los valores de tiempo y veras cambios significativos
cuando dices agrupar, estas diciendo que se tome el maximo o el minimo por cada diferencia entre el grupo, entonces como la combinación no cambia es el mismo valor...eso quiere decir que la orden al ser unica y el mismo tiempo, es el maximo y el minimo igual....
0
DONADIEAuthor Commented:
Please see in the example ACCESS DB my Report table.
It contains 12 rows.
With MySQLQuery I would like to have:
                                                                        StartTime       EndTime
2011-12-19    B    e 9872/02   10103645     2      08:58:37        09:10:44        
2011-12-19    B    E 9872/01   10103648     1      09:35:42        09:52:19

instead of:
2011-12-19    B    e 9872/02   10103645     2      09:10:44        09:10:44        
2011-12-19    B    E 9872/01   10103648     1      09:52:19        09:52:19    
DB-For-ExpertExchange1.mdb
0
CrashmanCommented:
your really have problems in there.
you are showing information required format, and this information dont comes from the actual query

you need to change the INNER JOIN beause this join only take two rows
try this with the information of the example, create another database and import the msaccess database and you will see
try this first

SELECT  Report.DateT,
        Report.Shift,
        Report.DieCode,
        Report.OrderNo,
        Parameter.Holes,
        Report.Time1 AS StartTime,
        Report.Time1 AS EndTime,
        DieChangeCode.ChgDescr AS D
FROM    Report
        INNER JOIN Parameter ON Report.ID_Die = Parameter.ID_Die
        INNER JOIN Orders ON Report.ID_Order = Orders.ID_Order
        INNER  JOIN DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode

Open in new window


this give two rows only.

this other

SELECT  Der.DateT,
        Der.Shift,
        Der.DieCode,
        Der.OrderNo,
        Der.Holes,
        Der.StartTime,
        Der.EndTime,
        Der.D
FROM    ( SELECT    Report.DateT,
                    Report.Shift,
                    Report.DieCode,
                    Report.OrderNo,
                    MAX(Parameter.Holes) OVER ( PARTITION BY Report.OrderNo ) AS Holes,
                    MIN(Report.Time1) OVER ( PARTITION BY Report.OrderNo ) AS StartTime,
                    MAX(Report.Time1) OVER ( PARTITION BY Report.OrderNo ) AS EndTime,
                    MAX(DieChangeCode.ChgDescr) OVER ( PARTITION BY Report.OrderNo ) AS D,
                    RANK() OVER ( partition by report.orderno ORDER BY report.Time1 ) Rnk
          FROM      Report
                    LEFT JOIN Parameter ON Report.ID_Die = Parameter.ID_Die
                    LEFT JOIN Orders ON Report.ID_Order = Orders.ID_Order
                    LEFT JOIN DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode
        ) Der
WHERE   Der.Rnk = 1

Open in new window


show the information, but not with the values you show here...
please review your inner joins...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CrashmanCommented:
sorry i read bad, i think the second query has the correct values
0
CrashmanCommented:
but the joins still wrong in you first query....
0
santhimurthydCommented:
I have create the store procedure with the SQL you have provided to and when try to execute the same with below SQL [dbo].[ReportSHIFT] '19/12/2011' , '19/12/2011' , 'B' it throws the error as unable to convert varchar to Datetime.

I need an clarification as column Report.DateT si datetime field else varchar filed.

considering the DateT field as Datetime field, the PL/SQL has been changed as follow  
create procedure [dbo].[ReportSHIFT] 
@DA nvarchar (10),
@A nvarchar (10),
@Shift CHAR(1)
as
begin
SELECT     
Report.DateT,
Report.Shift, 
Report.DieCode,
Report.OrderNo, 
MAX(Parameter.Holes),
MIN(Report.Time1) AS StartTime, 
MAX(Report.Time1) AS EndTime, 
MAX(DieChangeCode.Description) AS Description 

FROM  Report 
INNER JOIN Parameter ON Report.ID_Die = Parameter.ID_Die 
INNER JOIN Orders ON Report.ID_Order = Orders.ID_Order 
INNER JOIN DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode 
GROUP BY Report.DateT, Report.Shift, Report.DieCode, Report.OrderNo  
HAVING(Report.DateT BETWEEN convert(datetime,@DA,103) AND convert(datetime,@A,103)) AND (Report.Shift = @Shift)
ORDER BY Report.DateT, StartTime asc 
end

Open in new window


with this change and the input above gives me the excat report as you have specified
Querry result
0
DONADIEAuthor Commented:
ID: 37317050

Here below my final query that is working perfectly!
Thank you very much.

create procedure [dbo].[ReportSHIFT_2]
@DA nvarchar (10),
@A nvarchar (10),
@Shift CHAR(1)
as

SELECT  Der.DateT,
        Der.Shift,
        Der.DieCode,
        Der.OrderNo,
        Der.Holes,
        Der.StartTime,
        Der.EndTime,
        Der.D
FROM    ( SELECT    Report.DateT,
                    Report.Shift,
                    Report.DieCode,
                    Report.OrderNo,
                    MAX(Parameter.Holes) OVER ( PARTITION BY Report.OrderNo ) AS Holes,
                    MIN(Report.Time1) OVER ( PARTITION BY Report.OrderNo ) AS StartTime,
                    MAX(Report.Time1) OVER ( PARTITION BY Report.OrderNo ) AS EndTime,
                    MAX(DieChangeCode.ChgDescr) OVER ( PARTITION BY Report.OrderNo ) AS D,
                    RANK() OVER ( partition by report.orderno ORDER BY report.Time1 ) Rnk
          FROM      Report
                    LEFT JOIN Parameter ON Report.ID_Die = Parameter.ID_Die
                    LEFT JOIN Orders ON Report.ID_Order = Orders.ID_Order
                    LEFT JOIN DieChangeCode ON Report.ReasChg = DieChangeCode.ChgDieCode
            where (report.DateT BETWEEN @DA AND @A) AND (report.Shift = @Shift)
        ) Der
WHERE   Der.Rnk = 1
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.