June 15, 2009 at 2:36 pm
Hiya... ran into a snag and I'm stuck on trying to figure out how to work around it... I have this chunk of code I've been working on to add up all the estimated setup times and I've managed to get it to this stage:
SELECT "track"."start_date",
CASE
WHEN "track"."order_id"=(SELECT "look"."order_id" FROM "flute_data"."dbo"."track" "look" WHERE "look"."process_id"=1 AND "look"."start_date"=
(SELECT MAX("datefind"."start_date") FROM "flute_data"."dbo"."track" "datefind" WHERE "datefind"."start_date"<"track"."start_date" AND "datefind"."process_id"=1))
THEN 0
ELSE "order_routing"."setup_time"
END AS EstSetup
FROM "flute_data"."dbo"."order_routing" "order_routing" INNER JOIN "flute_data"."dbo"."track" "track" ON (("order_routing"."order_id"="track"."order_id") AND ("order_routing"."order_line_nbr"="track"."order_line_nbr")) AND ("order_routing"."process_id"="track"."process_id")
WHERE "track"."start_date">={ts '2009-05-02 00:00:00'} AND "order_routing"."process_id"=1
ORDER BY "track"."start_date"
This gives me this snippet for a result:
start_dateEstSetup
05/04/2009 7:05:16 AM22
05/04/2009 8:20:08 AM15
05/04/2009 9:15:09 AM15
05/04/2009 9:49:54 AM0
05/04/2009 10:32:49 AM15
05/04/2009 11:47:06 AM15
05/05/2009 7:04:01 AM35
05/05/2009 8:43:35 AM0
05/05/2009 10:15:06 AM20
05/05/2009 12:10:37 PM15
Which works exactly as it ought to. So the last step for me at this stage is to add the EstSetup value together by date, which I thought should be easy by just dropping the look-back query inside a SUM statement, when results in this for the query:
SELECT distinct convert(varchar,"track"."start_date",111) AS DateRun,
SUM(CASE
WHEN "track"."order_id"=(SELECT "look"."order_id" FROM "flute_data"."dbo"."track" "look" WHERE "look"."process_id"=1 AND "look"."start_date"=
(SELECT MAX("datefind"."start_date") FROM "flute_data"."dbo"."track" "datefind" WHERE "datefind"."start_date"<"track"."start_date" AND "datefind"."process_id"=1))
THEN 0
ELSE "order_routing"."setup_time"
END) AS EstSetup
FROM "flute_data"."dbo"."order_routing" "order_routing" INNER JOIN "flute_data"."dbo"."track" "track" ON (("order_routing"."order_id"="track"."order_id") AND ("order_routing"."order_line_nbr"="track"."order_line_nbr")) AND ("order_routing"."process_id"="track"."process_id")
WHERE "track"."start_date">={ts '2009-05-02 00:00:00'} AND "order_routing"."process_id"=1
GROUP BY convert(varchar,"track"."start_date",111)
Except when I run it, I get an error message - "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." - and I'm at a complete loss on how to achieve the result I want given that error.
Any advice from more experienced SQL coders on how I can achieve this? The exact version of SQL Server being used is 2000.
June 15, 2009 at 3:03 pm
Try something like this:
select dateadd(day, datediff(day, 0, "start_date"), 0) as DateRun,
sum(EstSetup) as TotalEstSetup
from
(SELECT "track"."start_date",
CASE
WHEN "track"."order_id"=
(SELECT "look"."order_id"
FROM "flute_data"."dbo"."track" "look"
WHERE "look"."process_id"=1
AND "look"."start_date"=
(SELECT MAX("datefind"."start_date")
FROM "flute_data"."dbo"."track" "datefind"
WHERE "datefind"."start_date"={ts '2009-05-02 00:00:00'}
AND "order_routing"."process_id"=1) Sub
group by dateadd(day, datediff(day, 0, "start_date"), 0)
ORDER BY dateadd(day, datediff(day, 0, "start_date"), 0);
Note, I don't have your table definitions, so I can't test this. If it's buggy, provide the table definitions (create scripts), and I'll see about fixing it up.
Also, there's probably a better way to get what you're looking for. With table definitions and some sample data (insert statements), I can probably make something that will run faster and easier.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2009 at 7:35 am
Just gave it a try and works like a charm.
Thanks again!
June 16, 2009 at 1:58 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply