sassy168
asked on
HELP with provider ran out of memory
Hi all i have a very big query that I am working on and itwas working fine past couple of days and today i ran and i got this error:
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetNextRows returned 0x8007000e: The provider ran out of memory.].
Msg 7399, Level 16, State 1, Line 6
what does this mean?
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetNextRows returned 0x8007000e: The provider ran out of memory.].
Msg 7399, Level 16, State 1, Line 6
what does this mean?
Anthony Perkins
It means it "ran out of memory". It is time to refactor your query.
it looks the Server resources is over-usage you should update your provider.
>>you should update your provider.<<
Huh? Do you think the provider is suffering from Alzheimer?
Huh? Do you think the provider is suffering from Alzheimer?
ASKER
so tthat means my query is too intensive?
No. It means it needs to be optimized.
ASKER
what is the definition of "optimize" ..maybe less joins, and databases to get to the data? right now the query its getting data from 2 different databases, and its very long, perhaps that has something to do with it.
>>what is the definition of "optimize" ..maybe less joins, and databases to get to the data?<<
It has many meanings from:
Retructure your tables/data.
To:
Add additional indexes.
To:
Update statistics and or defrag indexes.
To:
Rewrite your query to ensure the SQL Server engine uses the best plan available.
Without seeing your query it is impossible to be more precise.
It has many meanings from:
Retructure your tables/data.
To:
Add additional indexes.
To:
Update statistics and or defrag indexes.
To:
Rewrite your query to ensure the SQL Server engine uses the best plan available.
Without seeing your query it is impossible to be more precise.
ASKER
here is a sample file for ONE workcenter, i create a stored procedure for 4 or 10 workcenters together. I can see how it would take a lot of resources but thats the way i have to do it right now. How can i solve the "memory" problem? add more ram, or increase the limit somewhere in the PC? thanks
4120.sql
4120.sql
You need to refactor your query. Start breaking up the query and inserting the data into temp tables (especially ones involving OPENROWSET. You can then query the temp tables directly.
ASKER
acperkin:
theopenrowset is a query from another database. If i use the temp tables, at what point do i use it? begining in the query? sorry not too familiar with temp tables. I assume you're suggesting putting everything that is outside the current database in the temp tables which will improve performance?
theopenrowset is a query from another database. If i use the temp tables, at what point do i use it? begining in the query? sorry not too familiar with temp tables. I assume you're suggesting putting everything that is outside the current database in the temp tables which will improve performance?
Exactly.
Instead of:
LEFT OUTER JOIN (
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')
) AS d ON n.Load_Date = d.End_Date
You would do something like this:
CREATE TABLE #Temp1 (
End_Date datetime,
Qty integer
)
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)
INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')
Then your main select becomes:
SELECT ...
FROM ...
LEFT JOIN #Temp1 AS d ON n.Load_Date = d.End_Date
...
ASKER
okay that sounds great, but i have different queries bunched up together, for example i might run 6-10 repetition of the above query (the query i want to create is a summary file of 10 different workcenters, and this query is only for ONE work center) so can I replicate this code and repeat it for 10 times and run it all together?
would i run into problems if the temp table is the same name? I would have to probably create the temp table once, and then insert the rest of the instances? and when does the temp table get deleted? if i run this query hourly would it error out saying, the table has been already created? please advice, thanks!!
would i run into problems if the temp table is the same name? I would have to probably create the temp table once, and then insert the rest of the instances? and when does the temp table get deleted? if i run this query hourly would it error out saying, the table has been already created? please advice, thanks!!
>>would i run into problems if the temp table is the same name?<<
No.
>>I would have to probably create the temp table once, and then insert the rest of the instances?<<
Yes you can.
>>and when does the temp table get deleted? <<
Either when you do a DROP TABLE #TempTable goes here or it goes out of scope at the end of the Stored Procedure.
>>if i run this query hourly would it error out saying, the table has been already created?<<
No.
No.
>>I would have to probably create the temp table once, and then insert the rest of the instances?<<
Yes you can.
>>and when does the temp table get deleted? <<
Either when you do a DROP TABLE #TempTable goes here or it goes out of scope at the end of the Stored Procedure.
>>if i run this query hourly would it error out saying, the table has been already created?<<
No.
ASKER
ok help me out here getting errors with this:
LEFT OUTER JOIN (
DROP TABLE #Temp1
CREATE TABLE #Temp1 ( End_Date datetime, Qty integer )
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)
INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date , CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo. t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output. Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND ((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')
select End_Date, Qty from #Temp1
) AS d ON n.Load_Date = d.End_Date
First step insert into the temp table:
CREATE TABLE #Temp1 (
End_Date datetime,
Qty integer
)
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)
INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Op
dimdbmc.dbo.t_Mfg_Order_Ou
((dimdbmc.dbo.t_Mfg_Order_
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Op
Second execute your query with the temp table.
SELECT ...
FROM ...
LEFT JOIN #Temp1 AS d ON n.Load_Date = d.End_Date
...
CREATE TABLE #Temp1 (
End_Date datetime,
Qty integer
)
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)
INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Op
dimdbmc.dbo.t_Mfg_Order_Ou
((dimdbmc.dbo.t_Mfg_Order_
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Op
Second execute your query with the temp table.
SELECT ...
FROM ...
LEFT JOIN #Temp1 AS d ON n.Load_Date = d.End_Date
...
ASKER
your example works by itself but i need it to work with the left outer join. I get an error when I leave it on
SELECT n.Load_Date ,
ISNULL(d.Qty, 0) AS Qty,
b.Factory_Name ,
b.Operation_Name ,
b.Total_Target
FROM (
SELECT CONVERT(VARCHAR(8), dt, 112) AS Load_Date FROM RTC.dbo.Corp_Calendar
WHERE ( dt >= DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0) )
AND ( dt < DATEADD(M, DATEDIFF(M, 0, GETDATE()) + 1, 0) )
) AS n
LEFT OUTER JOIN (
CREATE TABLE #Temp1 (
End_Date datetime, Qty integer
)
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)
INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')
SELECT End_Date, Qty
FROM #Temp1
) AS d ON n.Load_Date = d.End_Date
That is not what I stated, so let me try one more time:
First create the temp table and populate it:
CREATE TABLE #Temp1 (
End_Date datetime,
Qty integer
)
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)
INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')
Next use it in your query:
SELECT n.Load_Date,
ISNULL(d.Qty, 0) AS Qty,
b.Factory_Name,
b.Operation_Name,
b.Total_Target
FROM (SELECT CONVERT(VARCHAR(8), dt, 112) AS Load_Date
FROM RTC.dbo.Corp_Calendar
WHERE (dt >= DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0))
AND (dt < DATEADD(M, DATEDIFF(M, 0, GETDATE()) + 1, 0))
) AS n
LEFT OUTER JOIN #Temp1 AS d ON n.Load_Date = d.End_Date
...
If that is not clear, I suggest you hire a reputable DBA in your area to help you.
ASKER
that works now, but it wasn't clear in the beginning because you didn't say clearly to do the create temp table outside of the query.
ASKER
now should i put all my querys in the temp tables and avoid the openrowset altogether?
>>but it wasn't clear in the beginning because you didn't say clearly to do the create temp table outside of the query. <<
How much clearer could I have made it (from http:#a33758977) Can you not see those are two separate steps?
How much clearer could I have made it (from http:#a33758977) Can you not see those are two separate steps?
You would do something like this:
CREATE TABLE #Temp1 (
End_Date datetime,
Qty integer
)
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)
INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')
Then your main select becomes:
SELECT ...
FROM ...
LEFT JOIN #Temp1 AS d ON n.Load_Date = d.End_Date
...
ASKER
because you put :
Instead of:
LEFT OUTER JOIN (
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
etc
I assume you wanted me to replace that whole left outer join section so if you see what i did to follow your instructions is i put CREATE TEMP Table inside the left outer join. If you say Instead of you should put what is going to replace. but what you should probably have said is to put the CREATE TEMP table instructions on the top of the query, and then use the temp table as a regular table as usual. NOt completely my fault!!
Instead of:
LEFT OUTER JOIN (
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
etc
I assume you wanted me to replace that whole left outer join section so if you see what i did to follow your instructions is i put CREATE TEMP Table inside the left outer join. If you say Instead of you should put what is going to replace. but what you should probably have said is to put the CREATE TEMP table instructions on the top of the query, and then use the temp table as a regular table as usual. NOt completely my fault!!
>>now should i put all my querys in the temp tables and avoid the openrowset altogether?<<
No I did not say that and you can see that I am not avoiding them. I just think you should refactor your derived tables and instead use temp tables instead.
No I did not say that and you can see that I am not avoiding them. I just think you should refactor your derived tables and instead use temp tables instead.
>>NOt completely my fault!!<<
I agree. I assume that members who post here are conversant with the T-SQL language and obviously that is not the case here.
I agree. I assume that members who post here are conversant with the T-SQL language and obviously that is not the case here.
ASKER
if we were good at it we wouldn't be posting questions here.
I did not say good. I just said "conversant". That is someone that understands the basic T-SQL syntax for a SELECT or at the very least is prepared to look up the syntax in BOL.
ASKER
well i know basic sql. I am just not good at doing temp tables, so i thought maybe its possible to create the table within a query or something. So i guess NOT .are we gonna continue arguing about how little I know? if you're unhappy with my level of SQL knowledge you can probably skip my question and not answer.
Fair enough.
Good luck.
Good luck.
ASKER
one last question and i am going to close this question. THis relates to the original question about my query taking too much memory. If i follow your suggestion to restructure the query to put some of them into temp tables but I put 20 temp tables in one single stored procedure, would it still run into memory issues? Here is my revised query for ONE workcenter, but I need to put 10-20 of this instance in one Stored procedure.....just wondering if this would still put me in the same predicament after I make all the changes.
CREATE TABLE #5003_DAILY (
End_Date datetime,
Qty integer
)
CREATE INDEX #IX_5003_DAILY ON #5003_DAILY(End_Date)
CREATE TABLE #5003_WIP (
WIPQty integer
)
CREATE TABLE #5003_HOURLY (
Load_Hour datetime,
End_Date datetime,
Qty integer
)
CREATE INDEX #IX_HOURLY ON #5003_HOURLY(End_Date)
INSERT #5003_DAILY(End_Date, Qty)
SELECT End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''5003''))
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')
INSERT #5003_WIP(WIPQty)
SELECT WIPQty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'
SELECT IsNull( CAST(SUM(Order_Operation_Qty) as INT),0) AS WIPQty
FROM dimdbmc.dbo.t_Mfg_Order_Operation
WHERE (Mfg_Order_Oper_Status_Code = ''ACTIVE'')
AND (Work_Center_Code = ''5003'')')
INSERT #5003_HOURLY(Load_Hour,End_Date, Qty)
SELECT Load_Hour, End_Date, Qty
FROM OPENROWSET('SQLOLEDB',
'IDDB02'; 'opra'; 'oprapw',
'SELECT CONVERT(VARCHAR(2), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 108) AS Load_Hour,
CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date,
dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code,
CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty)as int) AS Qty
FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN
dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code
WHERE (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND
(CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= CONVERT(varchar(8), GETDATE(), 112)
AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= CONVERT(varchar(8), GETDATE(), 112)))
AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''5003''))
GROUP BY CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112),
CONVERT(VARCHAR(2), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 108),
dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code Order by CONVERT(VARCHAR(2), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 108) ')
--//The following code belongs to SIM PASTE Operation//
SELECT a.Factory_Name collate SQL_Latin1_General_CP1_CI_AS as'Factory',
a.Operation_Name collate SQL_Latin1_General_CP1_CI_AS as 'Operation',
'' as 'IC-Code',
'DAILY' as 'Daily/Hourly',
a.Load_Date as 'Date',
'' as 'Hour',
a.Qty as 'Qty-out',
a.GoalQty as 'Goal Qty',
CONVERT(CHAR(9), GETDATE(), 1) + 'at ' + CONVERT(CHAR(5), GETDATE(), 108) AS 'RunDate/Time',
a.Total_Target 'Total Monthly Goal',
'' 'IC-Code Desc',
a.WIPQty 'WIP-Qty'
FROM
(
SELECT
e.Factory_Name,
e.Operation_Name,
e.Load_Date,
e.Qty,
CASE WHEN ( ( c.isWeekday = 1 )
AND ( c.isHoliday = 0 )
) THEN e.Total_Target / w.Working_Days
ELSE 0
END AS GoalQty ,
e.Total_Target,
g.WIPQty
FROM
(
SELECT n.Load_Date ,
ISNULL(d.Qty, 0) AS Qty,
b.Factory_Name ,
b.Operation_Name ,
b.Total_Target
FROM (
SELECT CONVERT(VARCHAR(8), dt, 112) AS Load_Date FROM RTC.dbo.Corp_Calendar
WHERE ( dt >= DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0) )
AND ( dt < DATEADD(M, DATEDIFF(M, 0, GETDATE()) + 1, 0) )
) AS n
LEFT OUTER JOIN (
SELECT End_Date, Qty
FROM #5003_DAILY
) AS d ON n.Load_Date = d.End_Date
CROSS JOIN ( SELECT Factory_Name, Operation_Name, SUM(Monthly_Target) AS Total_Target
FROM dbo.WC_Target WHERE (WC_Code = 5003) and Factory_Name='CASSI' GROUP BY Factory_Name, Operation_Name
------End Query 3
) b
)e
LEFT OUTER JOIN (
------Begin Query 4: Check if date is weekday or holiday
Select dt, isWeekday, isHoliday from RTC.dbo.Corp_Calendar where dt >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)and dt < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)
------End Query 4
) AS c ON CONVERT(VARCHAR(8), c.dt, 112) = e.Load_Date
LEFT OUTER JOIN (
------Begin Query 5: Check how many working days in a month
SELECT COUNT(*) as Working_Days FROM dbo.Corp_Calendar WHERE isWeekday = 1 AND isHoliday = 0
AND dt >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)and dt < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)
------End Query 5
) AS w ON CONVERT(VARCHAR(8), c.dt, 112) = e.Load_Date
CROSS JOIN (
SELECT IsNull(WIPQty,0) as WIPQty
FROM #5003_WIP
------End Query 6
) g
) a
UNION ALL
SELECT b.Factory_Name as 'Factory',
b.Operation_Name as 'Operation',
'' as 'IC-Code',
'HOURLY' as 'Daily/Hourly',
b.Load_Date as 'Date',
b.Load_Hour as 'Hour',
b.Qty as 'Qty-out',
b.GoalQty as 'Goal Qty',
CONVERT(CHAR(9), GETDATE(), 1) + 'at ' + CONVERT(CHAR(5), GETDATE(), 108) AS 'RunDate/Time',
b.Total_Target as 'Total Monthly Goal',
'' as 'IC-Code Desc',
b.WIPQty as 'WIP-Qty'
FROM
(
SELECT
e.Factory_Name,
e.Operation_Name,
e.Load_Date,
e.Load_Hour,
e.Qty,
CASE WHEN ( ( c.isWeekday = 1 )
AND ( c.isHoliday = 0 )
) THEN ((e.Total_Target / w.Working_Days)/24)
ELSE 0
END AS GoalQty,
e.Total_Target,
g.WIPQty
FROM(
SELECT n.Load_Hour,
ISNULL(d.End_Date,
CONVERT(varchar(8), GETDATE(), 112)) AS Load_Date,
ISNULL(d.Qty, 0) AS Qty,
b.Factory_Name, b.Operation_Name,
b.Total_Target
FROM
(
------Query 1: This uses a system table to grab numbers from 0-23 (denoting 24 hours in a day)
SELECT RIGHT('0' + CAST(number AS varchar(2)), 2) AS Load_Hour
FROM master.dbo.spt_values
WHERE (type = 'p') AND (number BETWEEN 0 AND 23)
------End Query 1
) AS n
LEFT OUTER JOIN
(
------Query 2: This calculates the produced quantity from IMES for current day only
SELECT Load_Hour, End_Date, Qty
FROM #5003_HOURLY )
------End Query 2
AS d ON n.Load_Hour = d.Load_Hour
CROSS JOIN
------Query 3: This imports Target from excel to a table in IDDB01/RTC It needs to be automatically scheduled/imported
(SELECT Factory_Name, Operation_Name, SUM(Monthly_Target) AS Total_Target
FROM dbo.WC_Target WHERE (WC_Code = 5003) and Factory_Name='CASSI' GROUP BY Factory_Name, Operation_Name
)
AS b) AS e
LEFT OUTER JOIN (
------Query 4: This checks the calendar table in IDDB01/RTC if current day is weekday or holiday
Select dt, isWeekday, isHoliday from dbo.Corp_Calendar where CONVERT(varchar(8), dt, 112) = CONVERT(varchar(8), GETDATE(), 112)
) AS c ON CONVERT(VARCHAR(8), c.dt, 112) = e.Load_Date
------End Query 4
LEFT OUTER JOIN (
------Begin Query 5: This links to IDDB01/ RTC. THis will count the number of working days in the current month
SELECT COUNT(*) as Working_Days FROM dbo.Corp_Calendar WHERE isWeekday = 1 AND isHoliday = 0
AND dt >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)and dt < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)
) AS w ON CONVERT(VARCHAR(8), c.dt, 112) = e.Load_Date
------End Query 5
CROSS JOIN (
SELECT IsNull(WIPQty,0) as WIPQty
FROM #5003_WIP
------End Query 6
) g
) b
Order By Hour
DROP TABLE #5003_DAILY
DROP TABLE #5003_HOURLY
DROP TABLE #5003_WIP
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your help