April 7, 2009 at 2:02 pm
For the time being, I would love to be able to take the results from this query, and create a STORED PROCEDURE that INSERTS the results into a table:
SELECT itl.user_id, au.notes, au.shift,
sum(update_qty * m.avg_cubes) as total_cubes,
(select sum(itl2.update_qty * m2.avg_cubes)
from
rp_sku s2,
rp_inventory_transaction itl2,
rp_application_user au2,
rp_minor_average m2
where itl2.dstamp between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and itl2.code in ('Putaway', 'Pick', 'Relocate')
and (itl2.from_loc_id <> 'SUSPENSE' or itl2.to_loc_id <> 'SUSPENSE')
and au2.user_id = itl2.user_id
and m2.mnr_cd = s2.v_userdef1
and au.shift = au2.shift
and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s2.sku_id = itl2.sku_id ) as shift_total_cubes,
(select sum(datediff(s,rls2.login_time, rls2.logout_time))
from rp_login_stats rls2
where rls2.login_time between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls2.shift = au.shift) as shift_login_time, ROW_NUMBER() OVER(ORDER BY sum(update_qty * m.avg_cubes) DESC) AS 'RowNumber',
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id ) as loginTime,
(select sum(datediff(s,rls.login_time,rls.logout_time))
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id ) as loginTimeSeconds,
(select convert(varchar, max(rls.logout_time),100)
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id ) as LogoutTime
-- (select sum(datediff(s,rls2.login_time,rls2.logout_time)) from rp_login_stats rls2 where rls2.login_time between convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 09:00:00' and convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 20:00:00' and au.shift = rls2.shift) as loginTimeSecondsShift
FROM
rp_minor_average m,
rp_sku s,
rp_application_user au,
rp_inventory_transaction itl
WHERE
itl.dstamp between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and itl.code in ('Putaway', 'Pick', 'Relocate')
-- and au.shift in ('1ST P', '2ND P', '3RD P')
and au.shift = '1ST P'
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
and from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s.sku_id = itl.sku_id
and m.mnr_cd = s.v_userdef1
and au.user_id = itl.user_id
and au.shift is not null
-- and au.user_id = 'AJJ'
group by itl.user_id, au.notes, au.shift
-- ORDER BY au.shift, RowNumber
union all
SELECT itl.user_id, au.notes, au.shift,
sum(update_qty * m.avg_cubes) as total_cubes,
(select sum(itl2.update_qty * m2.avg_cubes)
from
rp_sku s2,
rp_inventory_transaction itl2,
rp_application_user au2,
rp_minor_average m2
where itl2.dstamp between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and itl2.code in ('Putaway', 'Pick', 'Relocate')
and (itl2.from_loc_id <> 'SUSPENSE' or itl2.to_loc_id <> 'SUSPENSE')
and au2.user_id = itl2.user_id
and m2.mnr_cd = s2.v_userdef1
and au.shift = au2.shift
and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s2.sku_id = itl2.sku_id ) as shift_total_cubes,
(select sum(datediff(s,rls2.login_time, rls2.logout_time))
from rp_login_stats rls2
where rls2.login_time between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and rls2.shift = au.shift) as shift_login_time, ROW_NUMBER() OVER(ORDER BY sum(update_qty * m.avg_cubes) DESC) AS 'RowNumber',
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and rls.user_id = itl.user_id ) as loginTime,
(select sum(datediff(s,rls.login_time,rls.logout_time))
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and rls.user_id = itl.user_id ) as loginTimeSeconds,
(select convert(varchar, max(rls.logout_time),100)
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and rls.user_id = itl.user_id ) as LogoutTime
-- (select sum(datediff(s,rls2.login_time,rls2.logout_time)) from rp_login_stats rls2 where rls2.login_time between convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 09:00:00' and convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 20:00:00' and au.shift = rls2.shift) as loginTimeSecondsShift
FROM
rp_minor_average m,
rp_sku s,
rp_application_user au,
rp_inventory_transaction itl
WHERE
itl.dstamp between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and itl.code in ('Putaway', 'Pick', 'Relocate')
-- and au.shift in ('1ST P', '2ND P', '3RD P')
and au.shift = '2ND P'
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
and from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s.sku_id = itl.sku_id
and m.mnr_cd = s.v_userdef1
and au.user_id = itl.user_id
and au.shift is not null
-- and au.user_id = 'AJJ'
group by itl.user_id, au.notes, au.shift
-- ORDER BY au.shift, RowNumber
-----------------------------------------
--3RD SHIFT
-----------------------------------------
union all
SELECT itl.user_id, au.notes, au.shift,
sum(update_qty * m.avg_cubes) as total_cubes,
(select sum(itl2.update_qty * m2.avg_cubes)
from
rp_sku s2,
rp_inventory_transaction itl2,
rp_application_user au2,
rp_minor_average m2
where itl2.dstamp between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and itl2.code in ('Putaway', 'Pick', 'Relocate')
and (itl2.from_loc_id <> 'SUSPENSE' or itl2.to_loc_id <> 'SUSPENSE')
and au2.user_id = itl2.user_id
and m2.mnr_cd = s2.v_userdef1
and au.shift = au2.shift
and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s2.sku_id = itl2.sku_id ) as shift_total_cubes,
(select sum(datediff(s,rls2.login_time, rls2.logout_time))
from rp_login_stats rls2
where rls2.login_time between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and rls2.shift = au.shift) as shift_login_time, ROW_NUMBER() OVER(ORDER BY sum(update_qty * m.avg_cubes) DESC) AS 'RowNumber',
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and rls.user_id = itl.user_id ) as loginTime,
(select sum(datediff(s,rls.login_time,rls.logout_time))
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and rls.user_id = itl.user_id ) as loginTimeSeconds,
(select convert(varchar, max(rls.logout_time),100)
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and rls.user_id = itl.user_id ) as LogoutTime
-- (select sum(datediff(s,rls2.login_time,rls2.logout_time)) from rp_login_stats rls2 where rls2.login_time between convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 09:00:00' and convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 20:00:00' and au.shift = rls2.shift) as loginTimeSecondsShift
FROM
rp_minor_average m,
rp_sku s,
rp_application_user au,
rp_inventory_transaction itl
WHERE
itl.dstamp between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and itl.code in ('Putaway', 'Pick', 'Relocate')
-- and au.shift in ('1ST P', '2ND P', '3RD P')
and au.shift = '3RD P'
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
and from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s.sku_id = itl.sku_id
and m.mnr_cd = s.v_userdef1
and au.user_id = itl.user_id
and au.shift is not null
-- and au.user_id = 'AJJ'
group by itl.user_id, au.notes, au.shift
ORDER BY au.shift, RowNumber
April 7, 2009 at 2:04 pm
Chris Morris (4/7/2009)
Can you run this please, with and without the derived table which is currently commented out, and post back the run times:SELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00', 101),@Enddate = convert(datetime, '02-APR-09' + ' 20:00:00', 101)
--
SELECT itl.user_id, au.notes, au.shift
--
FROM rp_minor_average m
INNER JOIN rp_sku s ON m.mnr_cd = s.v_userdef1
INNER JOIN rp_inventory_transaction itl ON s.sku_id = itl.sku_id
INNER JOIN rp_application_user au ON au.user_id = itl.user_id
/*
INNER JOIN (SELECT [user_id],
LogoutTime = convert(varchar, max(logout_time),100),
loginTimeSeconds = sum(datediff(s, login_time, logout_time)),
loginTime = cast(sum(datediff(s, login_time, logout_time))as decimal(9,2)) / 25200
FROM rp_login_stats
WHERE login_time BETWEEN @Startdate AND @Enddate
GROUP BY [user_id]) d
ON d.user_id = itl.user_id
*/
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
and au.shift = '1ST P' and au.shift is not null
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
AND NOT EXISTS (SELECT 1 FROM rp_location where location_id = itl.from_loc_id AND loc_type in ('Stage', 'Marshalling'))
group by itl.user_id, au.notes, au.shift
Chris,
I get:
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@Startdate".
Msg 137, Level 15, State 2, Line 22
Must declare the scalar variable "@Startdate".
Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'group'.
April 7, 2009 at 2:16 pm
My mistake Donato, the top line was eaten:
DECLARE @Startdate DATETIME, @Enddate DATETIME
SELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00', 101),
@Enddate = convert(datetime, '02-APR-09' + ' 20:00:00', 101)
--
SELECT itl.user_id, au.notes, au.shift, COUNT(*)
--
FROM rp_minor_average m
INNER JOIN rp_sku s ON m.mnr_cd = s.v_userdef1
INNER JOIN rp_inventory_transaction itl ON s.sku_id = itl.sku_id
INNER JOIN rp_application_user au ON au.user_id = itl.user_id
/*
INNER JOIN (SELECT [user_id],
LogoutTime = convert(varchar, max(logout_time),100),
loginTimeSeconds = sum(datediff(s, login_time, logout_time)),
loginTime = cast(sum(datediff(s, login_time, logout_time))as decimal(9,2)) / 25200
FROM rp_login_stats
WHERE login_time BETWEEN @Startdate AND @Enddate
GROUP BY [user_id]) d
ON d.user_id = itl.user_id
*/
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
and au.shift = '1ST P' and au.shift is not null
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
AND NOT EXISTS (SELECT 1 FROM rp_location where location_id = itl.from_loc_id AND loc_type in ('Stage', 'Marshalling'))
group by itl.user_id, au.notes, au.shift
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 2:32 pm
Chris Morris (4/7/2009)
My mistake Donato, the top line was eaten:
DECLARE @Startdate DATETIME, @Enddate DATETIMESELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00', 101),
@Enddate = convert(datetime, '02-APR-09' + ' 20:00:00', 101)
--
SELECT itl.user_id, au.notes, au.shift, COUNT(*)
--
FROM rp_minor_average m
INNER JOIN rp_sku s ON m.mnr_cd = s.v_userdef1
INNER JOIN rp_inventory_transaction itl ON s.sku_id = itl.sku_id
INNER JOIN rp_application_user au ON au.user_id = itl.user_id
/*
INNER JOIN (SELECT [user_id],
LogoutTime = convert(varchar, max(logout_time),100),
loginTimeSeconds = sum(datediff(s, login_time, logout_time)),
loginTime = cast(sum(datediff(s, login_time, logout_time))as decimal(9,2)) / 25200
FROM rp_login_stats
WHERE login_time BETWEEN @Startdate AND @Enddate
GROUP BY [user_id]) d
ON d.user_id = itl.user_id
*/
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
and au.shift = '1ST P' and au.shift is not null
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
AND NOT EXISTS (SELECT 1 FROM rp_location where location_id = itl.from_loc_id AND loc_type in ('Stage', 'Marshalling'))
group by itl.user_id, au.notes, au.shift
A little over 11 minutes with the JOIN commented out
April 7, 2009 at 2:35 pm
Chris,
Could you help me with this?
I just want this to work now. Inserting the results into a table. Here is my code, however, I get an error:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[AvgCubesByShift]
AS
DELETE FROM AvgCubesByShift
INSERT AvgCubesByShift(user_id,notes,shift,total_cubes,shift_total_cubes)
SELECT itl.user_id, au.notes, au.shift,
sum(update_qty * m.avg_cubes) as total_cubes,
(select sum(itl2.update_qty * m2.avg_cubes) as shift_total_cubes
from
rp_sku s2,
rp_inventory_transaction itl2,
rp_application_user au2,
rp_minor_average m2
where itl2.dstamp between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and itl2.code in ('Putaway', 'Pick', 'Relocate')
and (itl2.from_loc_id <> 'SUSPENSE' or itl2.to_loc_id <> 'SUSPENSE')
and au2.user_id = itl2.user_id
and m2.mnr_cd = s2.v_userdef1
and au.shift = au2.shift
and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s2.sku_id = itl2.sku_id )
INSERT AvgCubesByShift(RowNumber, shift_login_time)
select sum(datediff(s,rls2.login_time, rls2.logout_time)) as RowNumber, 1 AS shift_login_time
from rp_login_stats rls2
where rls2.login_time between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls2.shift = au.shift
--ROW_NUMBER() OVER(ORDER BY sum(update_qty * m.avg_cubes) DESC)
INSERT AvgCubesByShift(loginTime)
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200 as loginTime
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id )
INSERT AvgCubesByShift(loginTimeSeconds)
(select sum(datediff(s,rls.login_time,rls.logout_time)) as loginTimeSeconds
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id )
INSERT AvgCubesByShift(LogoutTime)
(select convert(varchar, max(rls.logout_time),100) as LogoutTime
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id)
-- (select sum(datediff(s,rls2.login_time,rls2.logout_time)) from rp_login_stats rls2 where rls2.login_time between convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 09:00:00' and convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 20:00:00' and au.shift = rls2.shift) as loginTimeSecondsShift
--
-- Troubleshoot from here down04-07-09
--
INTO AvgCubesByShift
FROM
rp_minor_average m,
rp_sku s,
rp_application_user au,
rp_inventory_transaction itl
WHERE
itl.dstamp between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and itl.code in ('Putaway', 'Pick', 'Relocate')
-- and au.shift in ('1ST P', '2ND P', '3RD P')
and au.shift = '1ST P'
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
and from_loc_id not in select location_id from rp_location where loc_type in ('Stage', 'Marshalling')
and s.sku_id = itl.sku_id
and m.mnr_cd = s.v_userdef1
and au.user_id = itl.user_id
and au.shift is not null
-- and au.user_id = 'AJJ'
group by itl.user_id, au.notes, au.shift
And the error is:
Msg 156, Level 15, State 1, Procedure AvgCubesByShift, Line 102
Incorrect syntax near the keyword 'INTO'.
It doesn't make sense to me why this won't work.
April 7, 2009 at 2:44 pm
Chris Morris (4/7/2009)
My mistake Donato, the top line was eaten:
DECLARE @Startdate DATETIME, @Enddate DATETIMESELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00', 101),
@Enddate = convert(datetime, '02-APR-09' + ' 20:00:00', 101)
--
SELECT itl.user_id, au.notes, au.shift, COUNT(*)
--
FROM rp_minor_average m
INNER JOIN rp_sku s ON m.mnr_cd = s.v_userdef1
INNER JOIN rp_inventory_transaction itl ON s.sku_id = itl.sku_id
INNER JOIN rp_application_user au ON au.user_id = itl.user_id
/*
INNER JOIN (SELECT [user_id],
LogoutTime = convert(varchar, max(logout_time),100),
loginTimeSeconds = sum(datediff(s, login_time, logout_time)),
loginTime = cast(sum(datediff(s, login_time, logout_time))as decimal(9,2)) / 25200
FROM rp_login_stats
WHERE login_time BETWEEN @Startdate AND @Enddate
GROUP BY [user_id]) d
ON d.user_id = itl.user_id
*/
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
and au.shift = '1ST P' and au.shift is not null
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
AND NOT EXISTS (SELECT 1 FROM rp_location where location_id = itl.from_loc_id AND loc_type in ('Stage', 'Marshalling'))
group by itl.user_id, au.notes, au.shift
7:53 with the JOIN...
April 7, 2009 at 2:44 pm
I don't understand what you're trying to do with the "INTO" command. It's not a command all by itself, but that's how it appears in your proc.
Also, I think you'll get errors on some of your inserts, where you have things like this:
INSERT AvgCubesByShift(LogoutTime)
(select convert(varchar, max(rls.logout_time),100) as LogoutTime
...
I think you'll need to add "Select" before the "(select", so it has:
INSERT AvgCubesByShift(LogoutTime)
select (select convert(varchar, max(rls.logout_time),100) as LogoutTime
...
- 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
April 7, 2009 at 2:45 pm
Sure, briefly (it's nearly 10pm here).
Everything above this line [Troubleshoot from here down04-07-09] makes sense, nothing makes sense below the line. I'm guessing it should read something like
INSERT INTO tablename (column list)
SELECT...
FROM...
Incidentally, Grant Fritchey asked for some info in the fifth or so post in this thread - the information he has asked for is key to solving the performance problem.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 3:00 pm
Sorry guys... At this point, I just want to be able to insert the results into a table.
If anyone could help me with that, I would greatly appreciate it!
Thank you and here is what I have so far:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[AvgCubesByShift]
AS
DELETE FROM AvgCubesByShift
INSERT AvgCubesByShift(user_id,notes,shift,total_cubes,shift_total_cubes)
SELECT itl.user_id, au.notes, au.shift,
sum(update_qty * m.avg_cubes) as total_cubes,
(select sum(itl2.update_qty * m2.avg_cubes) as shift_total_cubes
from
rp_sku s2,
rp_inventory_transaction itl2,
rp_application_user au2,
rp_minor_average m2
where itl2.dstamp between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and itl2.code in ('Putaway', 'Pick', 'Relocate')
and (itl2.from_loc_id <> 'SUSPENSE' or itl2.to_loc_id <> 'SUSPENSE')
and au2.user_id = itl2.user_id
and m2.mnr_cd = s2.v_userdef1
and au.shift = au2.shift
and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s2.sku_id = itl2.sku_id )
INSERT AvgCubesByShift(RowNumber, shift_login_time)
select sum(datediff(s,rls2.login_time, rls2.logout_time)) as RowNumber, 1 AS shift_login_time
from rp_login_stats rls2
where rls2.login_time between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls2.shift = au.shift
--ROW_NUMBER() OVER(ORDER BY sum(update_qty * m.avg_cubes) DESC)
INSERT AvgCubesByShift(loginTime)
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200 as loginTime
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id )
INSERT AvgCubesByShift(loginTimeSeconds)
(select sum(datediff(s,rls.login_time,rls.logout_time)) as loginTimeSeconds
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id )
INSERT AvgCubesByShift(LogoutTime)
(select convert(varchar, max(rls.logout_time),100) as LogoutTime
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id)
-- (select sum(datediff(s,rls2.login_time,rls2.logout_time)) from rp_login_stats rls2 where rls2.login_time between convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 09:00:00' and convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 20:00:00' and au.shift = rls2.shift) as loginTimeSecondsShift
--
-- Troubleshoot from here down 04-07-09
--
INSERT INTO AvgCubesByShift(user_id,notes,shift,total_cubes,shift_total_cubes,RowNumber, shift_login_time,loginTime,loginTimeSeconds,LogoutTime)
FROM
rp_minor_average m,
rp_sku s,
rp_application_user au,
rp_inventory_transaction itl
WHERE
itl.dstamp between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and itl.code in ('Putaway', 'Pick', 'Relocate')
-- and au.shift in ('1ST P', '2ND P', '3RD P')
and au.shift = '1ST P'
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
and from_loc_id not in select location_id from rp_location where loc_type in ('Stage', 'Marshalling')
and s.sku_id = itl.sku_id
and m.mnr_cd = s.v_userdef1
and au.user_id = itl.user_id
and au.shift is not null
-- and au.user_id = 'AJJ'
group by itl.user_id, au.notes, au.shift
April 7, 2009 at 3:05 pm
GSquared is right - you need those SELECT (SELECT constructs in there as he describes.
Also, the final query is missing SELECT [columnlist].
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 3:12 pm
OK... could someone just please show me. I'm not very familiar with all of this.
What kills me is stuff like:
Msg 102, Level 15, State 1, Procedure AvgCubesByShift, Line 103
Incorrect syntax near ','.
I fricken comma that I don't see on that line. This is very frustrating now, because I hear that it is so easy to insert into a table, yet, so far, this has been very hard. All I want to do now is take my origianl query and insert the results into a table. If someone could please show me that, I would greatly appreciate it. I just need to see it once and then I can usually understand why, but right now, I just don't understand why. I'm sorry... but it is what it is.
I really appreciate all of your help. I really do. Thank you.
I just want to get this done. :-/
April 7, 2009 at 7:06 pm
Here's the basic way to do an insert into a table using a select. I'm using your code, so it'll be up to you to translate this:
INSERT INTO dbo.MyTable
(Column1
,Column2
,ColumnWhatever)
SELECT d.NewColumn1
,d.NewColumn2
,d.SomethingElse
FROM dbo.TheOtherTable d
WHERE...
That assumes there's already a table, dbo.MyTable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply