October 24, 2008 at 4:04 am
This is the query I am using that i would like to change to get the last date for each day:-
SELECT dbo.JobActual.TitleID,
dbo.JobActual.LocationID, dbo.JobActual.Pagination,
dbo.JobPlan.IssueDate,
dbo.JobActualComment.Comments,
dbo.JobPlanEdition.PressStart,
dbo.JobPlanEdition.PressLift,
dbo.JobPlan.TargetWastePercentage, dbo.Title.Title, dbo.JobPlan.JobPlanName,
dbo.JobPlan.TargetStartupWaste,
dbo.JobActual.OverrunWaste,
dbo.Location.Location,
dbo.GetJobActualTotalCopies(dbo.JobActual.JobActualID) - dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID) AS PrintWaste,
dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID) as PrintOrder,
(dbo.GetJobActualTotalCopies(dbo.JobActual.JobActualID) - dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID)) as PrintedWaste,
dbo.GetJobActualWebBreakDowntime(dbo.JobActual.JobActualID) as WebBreaks,
dbo.GetJobActualPlateWaiting(dbo.JobActual.JobActualID) as PlateWaiting,
dbo.GetJobActualTotalFullDowntime(dbo.JobActual.JobActualID) as ActualDowntime,
dbo.GetJobActualTotalDowntime(dbo.JobActual.JobActualID) as UnschedDowntime,
CAST((convert(decimal(15, 2),(dbo.GetJobActualTotalCopies(dbo.JobActual.JobActualID) -
dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID)))
/ nullif(dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID),0) )as decimal(9,3)) as PrintedWastePercent
FROM dbo.JobActual
--left JOINselect max(PressStart) from dbo.JobPlanEdition
INNER JOINdbo.JobPlanEdition ON dbo.JobPlanEdition.JobPlanID = dbo.JobActual.JobPlanID
LEFT JOINdbo.JobActualComment ON dbo.JobActualComment.JobActualID = dbo.JobActual.JobActualID
INNER JOINdbo.JobPlan ON dbo.JobActual.JobPlanID = dbo.JobPlan.JobPlanID
INNER JOINdbo.Title ON dbo.JobPlan.TitleID = dbo.Title.TitleID
INNER JOINdbo.Location ON dbo.JobActual.LocationID = dbo.Location.LocationID AND dbo.JobPlan.LocationID = dbo.Location.LocationID
where
dbo.JobActual.LocationID =6
and
dbo.JobActual.TitleID=1489
and (dbo.JobPlan.[IssueDate] >= '10 October 2008'
and dbo.JobPlan.[IssueDate] <= '13 October 2008')
order by
dbo.JobPlan.[IssueDate] ,dbo.JobActual.LocationID
But the field PressStart is a date filed and this is bringing back all the jobs of the date but ideally I would only like to return three rows one for each date 10th,11th and 12th of Oct '08. And the lax date for each particular date.
I am sorry if this is a bit vague but I can probably show you more on a private email if anyone is really keen and kind to help.
Thanks in advance.
October 24, 2008 at 7:46 am
Best Practice for forum code
SELECT dbo.JobActual.TitleID,
dbo.JobActual.LocationID, dbo.JobActual.Pagination,
dbo.JobPlan.IssueDate,
dbo.JobActualComment.Comments,
dbo.JobPlanEdition.PressStart,
dbo.JobPlanEdition.PressLift,
dbo.JobPlan.TargetWastePercentage, dbo.Title.Title, dbo.JobPlan.JobPlanName,
dbo.JobPlan.TargetStartupWaste,
dbo.JobActual.OverrunWaste,
dbo.Location.Location,
dbo.GetJobActualTotalCopies(dbo.JobActual.JobActualID) - dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID) AS PrintWaste,
dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID) AS PrintOrder,
(dbo.GetJobActualTotalCopies(dbo.JobActual.JobActualID) - dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID)) AS PrintedWaste,
dbo.GetJobActualWebBreakDowntime(dbo.JobActual.JobActualID) AS WebBreaks,
dbo.GetJobActualPlateWaiting(dbo.JobActual.JobActualID) AS PlateWaiting,
dbo.GetJobActualTotalFullDowntime(dbo.JobActual.JobActualID) AS ActualDowntime,
dbo.GetJobActualTotalDowntime(dbo.JobActual.JobActualID) AS UnschedDowntime,
CAST((CONVERT(DECIMAL(15, 2),(dbo.GetJobActualTotalCopies(dbo.JobActual.JobActualID) -
dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID)))
/ NULLIF(dbo.GetJobActualPrintOrder(dbo.JobActual.JobActualID),0) )AS DECIMAL(9,3)) AS PrintedWastePercent
FROM dbo.JobActual
--left JOIN select max(PressStart) from dbo.JobPlanEdition
INNER JOIN dbo.JobPlanEdition ON dbo.JobPlanEdition.JobPlanID = dbo.JobActual.JobPlanID
LEFT JOIN dbo.JobActualComment ON dbo.JobActualComment.JobActualID = dbo.JobActual.JobActualID
INNER JOIN dbo.JobPlan ON dbo.JobActual.JobPlanID = dbo.JobPlan.JobPlanID
INNER JOIN dbo.Title ON dbo.JobPlan.TitleID = dbo.Title.TitleID
INNER JOIN dbo.Location ON dbo.JobActual.LocationID = dbo.Location.LocationID AND dbo.JobPlan.LocationID = dbo.Location.LocationID
WHERE
dbo.JobActual.LocationID =6
AND
dbo.JobActual.TitleID=1489
AND (dbo.JobPlan.[IssueDate] >= '10 October 2008'
AND dbo.JobPlan.[IssueDate] <= '13 October 2008')
ORDER BY
dbo.JobPlan.[IssueDate] ,dbo.JobActual.LocationID
I didn't look in detail, but if you want the "newest/latest/last" date, usually you do
1. either group by, then
add in the where clause
WHERE [date] = (SELECT MAX([date] FROM ....)
2. use ROW_NUMBER order by [date] desc in the subquery, then select row = 1
October 24, 2008 at 9:29 am
Select max date option only gives me one line. I am looking for three rows one for each date between the 10th and 13th or whatever dates will be input.
I couldn't find a good example of the row_number to apply to my sql.
Each record has between 10 and 20 different date/times and I am trying to cpature just the latest date.
October 24, 2008 at 11:14 pm
A data sample ( real or fictional) would help to understand the problem and therefore solve the problem
October 25, 2008 at 1:06 am
See the link in my signature for an example of how to post sample data / table structure on the forum.
October 28, 2008 at 3:54 am
Hi Guys,
Thanks for your help. I guess my initial explanation didn't really explain it so here goes with some date example:-
col=ID===Pagin=====IssueDate=============PressStart===
1=1489==56==2008-10-10 00:00:00.00======2008-10-10 10:06:00.00
2=1489==56==2008-10-10 00:00:00.00======2008-10-10 10:20:00.00
3=1489==56==2008-10-10 00:00:00.00======2008-10-10 11:40:00.00
4=1489==70==2008-11-11 00:00:00.00======2008-10-11 12:05:00.00
5=1489==70==2008-11-11 00:00:00.00======2008-10-11 13:10:00.00
6=1489==70==2008-11-11 00:00:00.00======2008-10-11 16:30:00.00
I have two days data in the table based on Issue date. I may have more then two days in normal cases but just for example here is two days. I would like to in this case capture the two latest rows based on PressStart column. So my query should capture columns(col) 3 and column 6 for the two IssueDates. The col column is just for clarity in explanation. Thanks in advance for any help offered.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply