asked on

SQL Query Help

Below is the table structure:

Number	ClientID	ClientName	PltVersion	OptCt	DateTimeStarted	DateTimeFZ	RunStatus
10819259	1450	abc	x1	0	6/1/2014 0:03:35	6/1/2014 0:56:06	Success
10819259	1450	abc	x1	0	6/1/2014 0:03:35	6/1/2014 0:56:06	Success
10819260	1450	xyz	x1	1	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	3000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	1	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	0	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819261	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:34:07	Success
10819261	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:34:07	Success

Open in new window

This below query works but was just wondering if there is a better way of writing this without having to put ClientID, ClientName and other fields that I want to retrieve in the order by clause because they are varchar fields and there may be slight variation in those fields especially in the ClientName field.

SELECT Distinct Number, ClientID, ClientName, PltVer, MIN(DateTimeStarted), MAX(DateTimeFZ), SUM(Images) ImageCt
  FROM [table_name]
  WHERE 
	(DateTimeStarted BETWEEN '2014-09-29' AND '2014-10-05') AND 
	RunStatus IN ('xxx', ''yyy, 'etc')
  GROUP BY RunNumber, ClientID, ClientName, PltVersion
  ORDER BY RunNumber

Open in new window

chaau

The ORDER BY clause contains the RunNumber only. I guess you were asking about the group by clause. Looks like you want the MIN and MAX partitioned by the RunNumber only but at the same time you want to see all other columns as well. It is easy to achieve with a help of the window functions, like this:

SELECT Distinct RunNumber, ClientID, ClientName, PltVer, 
MIN(DateTimeStarted) OVER (PARTITION BY RunNumber), 
MAX(DateTimeFZ) OVER (PARTITION BY RunNumber), 
SUM(Images) OVER (PARTITION BY RunNumber) ImageCt
  FROM [table_name]
  WHERE 
	(DateTimeStarted BETWEEN '2014-09-29' AND '2014-10-05') AND 
	RunStatus IN ('xxx', ''yyy, 'etc')
  ORDER BY RunNumber

Open in new window

As you can see I have completely removed the GROUP BY clause as it is not required here.
Alternatively you can use the aggregate functions on other columns, like this:

SELECT Distinct RunNumber, Max(ClientID), Max(ClientName), Max(PltVer), MIN(DateTimeStarted), MAX(DateTimeFZ), SUM(Images) ImageCt
  FROM [table_name]
  WHERE 
	(DateTimeStarted BETWEEN '2014-09-29' AND '2014-10-05') AND 
	RunStatus IN ('xxx', ''yyy, 'etc')
  GROUP BY RunNumber
  ORDER BY RunNumber

Open in new window

itsmehar

ASKER

Is the OVER PARTITION clause supported in SQL2008?

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

itsmehar

ASKER

doesnt work for me

chaau

Instead of accepting my answer that doesn't work for you I suggest you keep the question open by allowing other experts look into it