SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Msg 4104
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 4

mana
Yak Posting Veteran

88 Posts

Posted - 08/08/2014 :  03:23:35  Show Profile  Reply with Quote










Hello

I have a problem, I have a table in sql server and i think i changed it and now it is empty. I want to restore the database ro a time but when i try to do it i have the below message:

can you help me please?
ITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'CATSVA01SQCP01V'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4266.0+((KJ_SP2_QFE-CU).120926-1658+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:


System.Data.SqlClient.SqlError: RESTORE can not process because it is used by this session the reporting database. It is recommended to use the master database for this operation. (Microsoft.SqlServer.Smo)

please help me.
thank you









quote:
Originally posted by MichaelJSQL

I would first try it as a select and make sure the result is appropriate for the precision and scale. ALso check what you have set for precision and scale in the sql server table

Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 08/09/2014 :  19:00:35  Show Profile  Reply with Quote

run the following

use master
Select 'KILL ' + Cast(spid as varchar(10)) from Master.dbo.sysprocesses where dbid = DB_ID('YourDatabaseName')

The results of this query - you will need to copy and place in a sql window and run. It will kill active connections to your database. you cannot restore a database that has connections.
Go to Top of Page

mana
Yak Posting Veteran

88 Posts

Posted - 08/13/2014 :  03:21:42  Show Profile  Reply with Quote





Hello,
thank you
I have the following codes and when i write them i a job i have erro excecution but as a normal query it works
what should i do?
thank you for your help


--calculation of number of devices
update f
set
numberofdevices = Orders_DailyVolume
from AMPPU_Fehlerdaten_CalcVals a
join AMPPU_Alle_Fehlteile f
on f.Offline_date = a.Offline_date
where f.Offline_date is not null;
---------------------------------------------------
--calculation of repairtime per day
UPDATE AMPPU_Alle_Fehlteile SET
summe_Reparaturzeit_Daily = B.SUpdateColSource
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;
--------------------------------------------------------
--calculation of repairtime per day (exclNA)
UPDATE AMPPU_Alle_Fehlteile SET
[summe_Reparaturzeit_Daily(excl)] = E.SUpdateColSource
FROM AMPPU_Alle_Fehlteile C
INNER JOIN (select On_Line_Repair,Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' group by Offline_date,On_Line_Repair
) E ON C.Offline_date=E.Offline_date
where C.On_Line_Repair='NO';
--------------------------------------------------------
--calculation of ARTPU per day
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU_daily = CASE WHEN numberofdevices = 0 THEN 0 ELSE (summe_Reparaturzeit_Daily/numberofdevices) END;
-----------------------------------------------------------
--calculation of ARTPU per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[ARTPU_daily(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([summe_Reparaturzeit_Daily(excl)]/numberofdevices) END;
----------------------------------------------------------
--calculation of repairtime for per Bereich per day
UPDATE AMPPU_Alle_Fehlteile SET
summe_Reparaturzeit_B_Daily = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,bereich,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile group by Offline_date,Bereich) B
ON A.Offline_date = B.Offline_date and
ISNULL(A.Bereich,'Null') = ISNULL(B.bereich,'Null');
------------------------------------------------------------
--calculation of repairtime for per Bereich per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[summe_Reparaturzeit_B_Daily(excl)] = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select On_Line_Repair,Offline_date,bereich,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' group by Offline_date,Bereich,On_Line_Repair) B
ON A.Offline_date = B.Offline_date and
ISNULL(A.Bereich,'Null') = ISNULL(B.bereich,'Null')
where A.On_Line_Repair='NO';
-----------------------------------------------------------
--calculation of ARTPU for Bereich
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU_daily_Bereich = CASE WHEN numberofdevices = 0 THEN 0 ELSE (summe_Reparaturzeit_B_Daily/numberofdevices) END;
------------------------------------------------------------
--calculation of ARTPU for Bereich(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[ARTPU_daily_Bereich(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([summe_Reparaturzeit_B_Daily(excl)]/numberofdevices) END;
-----------------------------------------------------------
--calculation of average from ARTPU(Bereich) per month
UPDATE AMPPU_Alle_Fehlteile
SET summe_Reparaturzeit_B_Monat =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG(ARTPU_daily_Bereich) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
---------------------------------------------------------------
--calculation of average from ARTPU(Bereich) per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [summe_Reparaturzeit_B_Monat(excl)] =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG([ARTPU_daily_Bereich(excl)]) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
---------------------------------------------------------------
--sum of summe_Reparaturzeit_B_Monat per month
UPDATE AMPPU_Alle_Fehlteile
set summe_Total_Monat =
(SELECT ISNULL(SUM(summe_Reparaturzeit_B_Monat),0)
FROM (SELECT DISTINCT summe_Reparaturzeit_B_Monat
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)) a);
--------------------------------------------------------------------------------------
--sum of summe_Reparaturzeit_B_Monat per month(excl)
UPDATE AMPPU_Alle_Fehlteile
set [summe_Total_Monat(excl)] =
(SELECT ISNULL(SUM([summe_Reparaturzeit_B_Monat(excl)]),0)
FROM (SELECT DISTINCT [summe_Reparaturzeit_B_Monat(excl)]
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)) a);
--------------------------------------------------------------------------------------
--calculation of repairtime for per Teamleader per day
UPDATE AMPPU_Alle_Fehlteile SET
Reparaturzeit_Teamleader = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Bereich,Offline_date,Teamleader,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where Bereich = 'Intern' group by Offline_date,Bereich,Teamleader) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Teamleader=B.Teamleader;
----------------------------------------------------------------------------------------
--calculation of repairtime for per Teamleader per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[Reparaturzeit_Teamleader(excl)] = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select On_Line_Repair,Offline_date,bereich,Teamleader,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' and Bereich = 'Intern' group by Offline_date,Bereich,On_Line_Repair,Teamleader) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Teamleader=B.Teamleader
where A.On_Line_Repair='NO';
---------------------------------------------------------------------------------------------
--calculation of ARTPU for Teamleader
UPDATE AMPPU_Alle_Fehlteile SET
calcval_Teamleader = CASE WHEN numberofdevices = 0 THEN 0 ELSE (Reparaturzeit_Teamleader/numberofdevices) END;
----------------------------------------------------------------------------------------------
--calculation of ARTPU for Teamleader(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[calcval_Teamleader(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([Reparaturzeit_Teamleader(excl)]/numberofdevices) END;
-----------------------------------------------------------------------------------------------
--calculation of average from ARTPU(Teamleader) per month
UPDATE AMPPU_Alle_Fehlteile
SET calcval_T_Monat =
(SELECT AVG(avg_Teamleader)
FROM (SELECT Offline_date, AVG(calcval_Teamleader) AS avg_Teamleader
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Teamleader,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
------------------------------------------------------------------------------------------------------
--calculation of average from ARTPUTeamleader) per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [calcval_T_Monat(excl)] =
(SELECT AVG(avg_Teamleader)
FROM (SELECT Offline_date, AVG([calcval_Teamleader(excl)]) AS avg_Teamleader
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Teamleader,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
---------------------------------------------------------------------------------------------------------------
--calculation of repairtime for per Disponent per day
UPDATE AMPPU_Alle_Fehlteile SET
Reparaturzeit_Disponent = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Bereich,Offline_date,Disponent,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where Bereich = 'Extern' group by Offline_date,Bereich,Disponent) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Disponent=B.Disponent;
------------------------------------------------------------------------------------------------------------------------
--calculation of repairtime for per Disponent per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[Reparaturzeit_Disponent(excl)]= B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select On_Line_Repair,Offline_date,bereich,Disponent,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' and Bereich = 'Extern' group by Offline_date,Bereich,On_Line_Repair,Disponent) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Disponent=B.Disponent
where A.On_Line_Repair='NO';
---------------------------------------------------------------------------------------------------------------------------
--calculation of ARTPU for Disponent
UPDATE AMPPU_Alle_Fehlteile SET
calcval_Disponent = CASE WHEN numberofdevices = 0 THEN 0 ELSE (Reparaturzeit_Disponent/numberofdevices) END;
----------------------------------------------------------------------------------------------
--calculation of ARTPU for Disponent(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[calcval_Disponent(excl)]= CASE WHEN numberofdevices = 0 THEN 0 ELSE ([Reparaturzeit_Disponent(excl)]/numberofdevices) END;
-----------------------------------------------------------------------------------------------
--calculation of average from ARTPU(Disponent) per month
UPDATE AMPPU_Alle_Fehlteile
SET calcval_D_Monat =
(SELECT AVG(avg_Disponent)
FROM (SELECT Offline_date, AVG(calcval_Disponent ) AS avg_Disponent
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Disponent,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
------------------------------------------------------------------------------------------------------
--calculation of average from ARTPUDisponent per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [calcval_D_Monat(excl)]=
(SELECT AVG(avg_Disponent)
FROM (SELECT Offline_date, AVG([calcval_Disponent(excl)]) AS avg_Disponent
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Disponent,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);























quote:
Originally posted by MichaelJSQL


run the following

use master
Select 'KILL ' + Cast(spid as varchar(10)) from Master.dbo.sysprocesses where dbid = DB_ID('YourDatabaseName')

The results of this query - you will need to copy and place in a sql window and run. It will kill active connections to your database. you cannot restore a database that has connections.


Go to Top of Page

mana
Yak Posting Veteran

88 Posts

Posted - 08/13/2014 :  03:47:16  Show Profile  Reply with Quote
Hello,

In the following queries i have this warning. can it cause that the job doesn't work?

thank you for your help

Warning: A NULL value is cleared by an aggregate or other SET operation.

the queries are:
--calculation of repairtime per day
UPDATE AMPPU_Alle_Fehlteile SET
summe_Reparaturzeit_Daily = B.SUpdateColSource
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;



--calculation of ARTPU per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[ARTPU_daily(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([summe_Reparaturzeit_Daily(excl)]/numberofdevices) END;
----------------------------------------------------------
--calculation of repairtime for per Bereich per day
UPDATE AMPPU_Alle_Fehlteile SET
summe_Reparaturzeit_B_Daily = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,bereich,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile group by Offline_date,Bereich) B
ON A.Offline_date = B.Offline_date and
ISNULL(A.Bereich,'Null') = ISNULL(B.bereich,'Null');


--calculation of average from ARTPU(Bereich) per month
UPDATE AMPPU_Alle_Fehlteile
SET summe_Reparaturzeit_B_Monat =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG(ARTPU_daily_Bereich) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);



--calculation of repairtime for per Disponent per day
UPDATE AMPPU_Alle_Fehlteile SET
Reparaturzeit_Disponent = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Bereich,Offline_date,Disponent,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where Bereich = 'Extern' group by Offline_date,Bereich,Disponent) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Disponent=B.Disponent;



--calculation of average from ARTPU(Teamleader) per month
UPDATE AMPPU_Alle_Fehlteile
SET calcval_T_Monat =
(SELECT AVG(avg_Teamleader)
FROM (SELECT Offline_date, AVG(calcval_Teamleader) AS avg_Teamleader
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Teamleader,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);



--calculation of ARTPU for Teamleader(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[calcval_Teamleader(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([Reparaturzeit_Teamleader(excl)]/numberofdevices) END;


--calculation of average from ARTPU(Bereich) per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [summe_Reparaturzeit_B_Monat(excl)] =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG([ARTPU_daily_Bereich(excl)]) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);




can you helo me please?
thank you
quote:
Originally posted by mana






Hello,
thank you
I have the following codes and when i write them i a job i have erro excecution but as a normal query it works
what should i do?
thank you for your help


--calculation of number of devices
update f
set
numberofdevices = Orders_DailyVolume
from AMPPU_Fehlerdaten_CalcVals a
join AMPPU_Alle_Fehlteile f
on f.Offline_date = a.Offline_date
where f.Offline_date is not null;
---------------------------------------------------
--calculation of repairtime per day
UPDATE AMPPU_Alle_Fehlteile SET
summe_Reparaturzeit_Daily = B.SUpdateColSource
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;
--------------------------------------------------------
--calculation of repairtime per day (exclNA)
UPDATE AMPPU_Alle_Fehlteile SET
[summe_Reparaturzeit_Daily(excl)] = E.SUpdateColSource
FROM AMPPU_Alle_Fehlteile C
INNER JOIN (select On_Line_Repair,Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' group by Offline_date,On_Line_Repair
) E ON C.Offline_date=E.Offline_date
where C.On_Line_Repair='NO';
--------------------------------------------------------
--calculation of ARTPU per day
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU_daily = CASE WHEN numberofdevices = 0 THEN 0 ELSE (summe_Reparaturzeit_Daily/numberofdevices) END;
-----------------------------------------------------------
--calculation of ARTPU per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[ARTPU_daily(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([summe_Reparaturzeit_Daily(excl)]/numberofdevices) END;
----------------------------------------------------------
--calculation of repairtime for per Bereich per day
UPDATE AMPPU_Alle_Fehlteile SET
summe_Reparaturzeit_B_Daily = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,bereich,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile group by Offline_date,Bereich) B
ON A.Offline_date = B.Offline_date and
ISNULL(A.Bereich,'Null') = ISNULL(B.bereich,'Null');
------------------------------------------------------------
--calculation of repairtime for per Bereich per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[summe_Reparaturzeit_B_Daily(excl)] = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select On_Line_Repair,Offline_date,bereich,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' group by Offline_date,Bereich,On_Line_Repair) B
ON A.Offline_date = B.Offline_date and
ISNULL(A.Bereich,'Null') = ISNULL(B.bereich,'Null')
where A.On_Line_Repair='NO';
-----------------------------------------------------------
--calculation of ARTPU for Bereich
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU_daily_Bereich = CASE WHEN numberofdevices = 0 THEN 0 ELSE (summe_Reparaturzeit_B_Daily/numberofdevices) END;
------------------------------------------------------------
--calculation of ARTPU for Bereich(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[ARTPU_daily_Bereich(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([summe_Reparaturzeit_B_Daily(excl)]/numberofdevices) END;
-----------------------------------------------------------
--calculation of average from ARTPU(Bereich) per month
UPDATE AMPPU_Alle_Fehlteile
SET summe_Reparaturzeit_B_Monat =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG(ARTPU_daily_Bereich) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
---------------------------------------------------------------
--calculation of average from ARTPU(Bereich) per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [summe_Reparaturzeit_B_Monat(excl)] =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG([ARTPU_daily_Bereich(excl)]) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
---------------------------------------------------------------
--sum of summe_Reparaturzeit_B_Monat per month
UPDATE AMPPU_Alle_Fehlteile
set summe_Total_Monat =
(SELECT ISNULL(SUM(summe_Reparaturzeit_B_Monat),0)
FROM (SELECT DISTINCT summe_Reparaturzeit_B_Monat
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)) a);
--------------------------------------------------------------------------------------
--sum of summe_Reparaturzeit_B_Monat per month(excl)
UPDATE AMPPU_Alle_Fehlteile
set [summe_Total_Monat(excl)] =
(SELECT ISNULL(SUM([summe_Reparaturzeit_B_Monat(excl)]),0)
FROM (SELECT DISTINCT [summe_Reparaturzeit_B_Monat(excl)]
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)) a);
--------------------------------------------------------------------------------------
--calculation of repairtime for per Teamleader per day
UPDATE AMPPU_Alle_Fehlteile SET
Reparaturzeit_Teamleader = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Bereich,Offline_date,Teamleader,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where Bereich = 'Intern' group by Offline_date,Bereich,Teamleader) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Teamleader=B.Teamleader;
----------------------------------------------------------------------------------------
--calculation of repairtime for per Teamleader per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[Reparaturzeit_Teamleader(excl)] = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select On_Line_Repair,Offline_date,bereich,Teamleader,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' and Bereich = 'Intern' group by Offline_date,Bereich,On_Line_Repair,Teamleader) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Teamleader=B.Teamleader
where A.On_Line_Repair='NO';
---------------------------------------------------------------------------------------------
--calculation of ARTPU for Teamleader
UPDATE AMPPU_Alle_Fehlteile SET
calcval_Teamleader = CASE WHEN numberofdevices = 0 THEN 0 ELSE (Reparaturzeit_Teamleader/numberofdevices) END;
----------------------------------------------------------------------------------------------
--calculation of ARTPU for Teamleader(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[calcval_Teamleader(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([Reparaturzeit_Teamleader(excl)]/numberofdevices) END;
-----------------------------------------------------------------------------------------------
--calculation of average from ARTPU(Teamleader) per month
UPDATE AMPPU_Alle_Fehlteile
SET calcval_T_Monat =
(SELECT AVG(avg_Teamleader)
FROM (SELECT Offline_date, AVG(calcval_Teamleader) AS avg_Teamleader
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Teamleader,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
------------------------------------------------------------------------------------------------------
--calculation of average from ARTPUTeamleader) per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [calcval_T_Monat(excl)] =
(SELECT AVG(avg_Teamleader)
FROM (SELECT Offline_date, AVG([calcval_Teamleader(excl)]) AS avg_Teamleader
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Teamleader,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
---------------------------------------------------------------------------------------------------------------
--calculation of repairtime for per Disponent per day
UPDATE AMPPU_Alle_Fehlteile SET
Reparaturzeit_Disponent = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Bereich,Offline_date,Disponent,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where Bereich = 'Extern' group by Offline_date,Bereich,Disponent) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Disponent=B.Disponent;
------------------------------------------------------------------------------------------------------------------------
--calculation of repairtime for per Disponent per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[Reparaturzeit_Disponent(excl)]= B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select On_Line_Repair,Offline_date,bereich,Disponent,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' and Bereich = 'Extern' group by Offline_date,Bereich,On_Line_Repair,Disponent) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Disponent=B.Disponent
where A.On_Line_Repair='NO';
---------------------------------------------------------------------------------------------------------------------------
--calculation of ARTPU for Disponent
UPDATE AMPPU_Alle_Fehlteile SET
calcval_Disponent = CASE WHEN numberofdevices = 0 THEN 0 ELSE (Reparaturzeit_Disponent/numberofdevices) END;
----------------------------------------------------------------------------------------------
--calculation of ARTPU for Disponent(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[calcval_Disponent(excl)]= CASE WHEN numberofdevices = 0 THEN 0 ELSE ([Reparaturzeit_Disponent(excl)]/numberofdevices) END;
-----------------------------------------------------------------------------------------------
--calculation of average from ARTPU(Disponent) per month
UPDATE AMPPU_Alle_Fehlteile
SET calcval_D_Monat =
(SELECT AVG(avg_Disponent)
FROM (SELECT Offline_date, AVG(calcval_Disponent ) AS avg_Disponent
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Disponent,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
------------------------------------------------------------------------------------------------------
--calculation of average from ARTPUDisponent per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [calcval_D_Monat(excl)]=
(SELECT AVG(avg_Disponent)
FROM (SELECT Offline_date, AVG([calcval_Disponent(excl)]) AS avg_Disponent
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Disponent,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);























quote:
Originally posted by MichaelJSQL


run the following

use master
Select 'KILL ' + Cast(spid as varchar(10)) from Master.dbo.sysprocesses where dbid = DB_ID('YourDatabaseName')

The results of this query - you will need to copy and place in a sql window and run. It will kill active connections to your database. you cannot restore a database that has connections.




Go to Top of Page

mana
Yak Posting Veteran

88 Posts

Posted - 08/13/2014 :  07:46:18  Show Profile  Reply with Quote

hello
i have the following data and i want the summ of daily volume in one month for example in 2014.07 in each of bereich. for example in bereich=intern or etc. can you hlp me pleae? I wrote the follow code but the result is incorrect

select YEAR(Offline_date),
MONTH(Offline_date),SUM(numberofdevices),Bereich FROM
AMPPU_Alle_Fehlteile group by YEAR(Offline_date),
MONTH(Offline_date),Bereich;





Offline Datum Daily Volume bereich
2014-07-25 36 938,4
2014-07-25 36 938,4 Extern
2014-07-25 36 938,4 Intern
2014-07-25 36 938,4 unknown pn
2014-07-24 51 2217 Extern
2014-07-24 51 2217 Intern
2014-07-24 51 2217 unknown pn
2014-07-23 51 1863,6 Extern
2014-07-23 51 1863,6 Intern
2014-07-23 51 1863,6 unknown pn
2014-07-22 50 1016,4 Extern
2014-07-22 50 1016,4 Intern
2014-07-22 50 1016,4 unknown pn
2014-07-21 51 1624,8 Extern
2014-07-21 51 1624,8 Intern
2014-07-21 51 1624,8 unknown pn
2014-07-18 33 1264,2 Intern
2014-07-18 33 1264,2 unknown pn
2014-07-17 40 2580 Extern
2014-07-17 40 2580 Intern
2014-07-17 40 2580 unknown pn
2014-07-16 51 2076 Extern
2014-07-16 51 2076 Intern
2014-07-16 51 2076 unknown pn
2014-07-15 51 1503 Extern
2014-07-15 51 1503 Intern
2014-07-15 51 1503 unknown pn
2014-07-14 51 2026,8
2014-07-14 51 2026,8 Extern
2014-07-14 51 2026,8 Intern
2014-07-14 51 2026,8 unknown pn
2014-07-11 34 1517,4 Extern
2014-07-11 34 1517,4 Intern
2014-07-11 34 1517,4 unknown pn
2014-07-10 51 1081,8 Extern
2014-07-10 51 1081,8 Intern
2014-07-10 51 1081,8 unknown pn
2014-07-09 52 1506,6 Extern
2014-07-09 52 1506,6 Intern
2014-07-09 52 1506,6 unknown pn
2014-07-08 51 963,6 Extern
2014-07-08 51 963,6 Intern
2014-07-08 51 963,6 unknown pn
2014-07-07 51 1452 Extern
2014-07-07 51 1452 Intern
2014-07-07 51 1452 unknown pn
2014-07-04 34 812,4 Extern
2014-07-04 34 812,4 Intern
2014-07-03 51 2435,4 Extern
2014-07-03 51 2435,4 Intern
2014-07-03 51 2435,4 unknown pn
2014-07-02 51 2113,2 Extern
2014-07-02 51 2113,2 Intern
2014-07-02 51 2113,2 unknown pn
2014-07-01 51 1114,2 Extern
2014-07-01 51 1114,2 Intern
2014-07-01 51 1114,2 unknown pn
2014-06-30 51 1222,2 Extern
2014-06-30 51 1222,2 Intern
2014-06-30 51 1222,2 unknown pn
2014-06-27 34 811,8 Extern
2014-06-27 34 811,8 Intern
2014-06-27 34 811,8 unknown pn
2014-06-26 51 2605,2
2014-06-26 51 2605,2 Extern
2014-06-26 51 2605,2 Intern
2014-06-26 51 2605,2 unknown pn
2014-06-25 51 787,2 Extern
2014-06-25 51 787,2 Intern
2014-06-25 51 787,2 unknown pn
2014-06-24 51 714,6 Intern
2014-06-24 51 714,6 unknown pn
2014-06-23 51 1390,2 Extern
2014-06-23 51 1390,2 Intern
2014-06-23 51 1390,2 unknown pn
2014-06-18 51 779,4 Extern
2014-06-18 51 779,4 Intern
2014-06-18 51 779,4 unknown pn
2014-06-17 51 598,2 Extern
2014-06-17 51 598,2 Intern
2014-06-17 51 598,2 unknown pn
2014-06-16 51 691,2 Extern
2014-06-16 51 691,2 Intern
2014-06-16 51 691,2 unknown pn
2014-06-13 34 865,8 Extern
2014-06-13 34 865,8 Intern
2014-06-13 34 865,8 unknown pn
2014-06-12 51 1402,2 Extern
2014-06-12 51 1402,2 Intern
2014-06-12 51 1402,2 unknown pn
2014-06-11 51 607,2 Extern
2014-06-11 51 607,2 Intern
2014-06-11 51 607,2 unknown pn
2014-06-10 51 1349,4 Extern
2014-06-10 51 1349,4 Intern
2014-06-10 51 1349,4 unknown pn
2014-06-06 34 608,4 Extern
2014-06-06 34 608,4 Intern
2014-06-06 34 608,4 unknown pn
2014-06-05 51 849,6 Extern
2014-06-05 51 849,6 Intern
2014-06-05 51 849,6 unknown pn
2014-06-04 52 361,2 Extern
2014-06-04 52 361,2 Intern
2014-06-04 52 361,2 unknown pn
2014-06-03 51 877,8 Extern
2014-06-03 51 877,8 Intern
2014-06-03 51 877,8 unknown pn
2014-06-02 51 1523,4 Extern
2014-06-02 51 1523,4 Intern
2014-06-02 51 1523,4 unknown pn
2014-05-28 48 613,2 Extern
2014-05-28 48 613,2 Intern
2014-05-28 48 613,2 unknown pn
2014-05-27 48 529,2 Extern
2014-05-27 48 529,2 Intern
2014-05-27 48 529,2 unknown pn
2014-05-26 48 591 Extern
2014-05-26 48 591 Intern
2014-05-26 48 591 unknown pn
2014-05-23 34 660,6 Intern
2014-05-23 34 660,6 unknown pn
2014-05-22 48 709,8 Extern
2014-05-22 48 709,8 Intern
2014-05-22 48 709,8 unknown pn
2014-05-21 48 692,4 Extern
2014-05-21 48 692,4 Intern
2014-05-21 48 692,4 unknown pn
2014-05-20 48 868,2 Extern
2014-05-20 48 868,2 Intern
2014-05-20 48 868,2 unknown pn
2014-05-19 48 1210,2 Extern
2014-05-19 48 1210,2 Intern
2014-05-19 48 1210,2 unknown pn
2014-05-16 34 506,4 Extern
2014-05-16 34 506,4 Intern
2014-05-16 34 506,4 unknown pn
2014-05-15 48 1330,2 Extern
2014-05-15 48 1330,2 Intern
2014-05-15 48 1330,2 unknown pn
2014-05-14 48 3465,6 Extern
2014-05-14 48 3465,6 Intern
2014-05-14 48 3465,6 unknown pn
2014-05-13 48 1541,4 Extern
2014-05-13 48 1541,4 Intern
2014-05-13 48 1541,4 unknown pn
2014-05-12 48 1125 Extern
2014-05-12 48 1125 Intern
2014-05-12 48 1125 unknown pn
2014-05-09 34 767,4 Extern
2014-05-09 34 767,4 Intern
2014-05-09 34 767,4 unknown pn
2014-05-08 48 1923 Extern
2014-05-08 48 1923 Intern
2014-05-08 48 1923 unknown pn
2014-05-07 46 2412 Extern
2014-05-07 46 2412 Intern
2014-05-07 46 2412 unknown pn
2014-05-06 48 1956,6 Extern
2014-05-06 48 1956,6 Intern
2014-05-06 48 1956,6 unknown pn
2014-05-05 48 2585,4
2014-05-05 48 2585,4 Extern
2014-05-05 48 2585,4 Intern
2014-05-05 48 2585,4 unknown pn
2014-04-30 48 1367,4 Extern
2014-04-30 48 1367,4 Intern
2014-04-30 48 1367,4 unknown pn
2014-04-29 51 1134
2014-04-29 51 1134 Extern
2014-04-29 51 1134 Intern
2014-04-29 51 1134 unknown pn
2014-04-28 51 448,2 Extern
2014-04-28 51 448,2 Intern
2014-04-28 51 448,2 unknown pn
2014-04-25 37 1273,2 Extern
2014-04-25 37 1273,2 Intern
2014-04-25 37 1273,2 unknown pn
2014-04-24 48 1471,8 Extern
2014-04-24 48 1471,8 Intern
2014-04-24 48 1471,8 unknown pn
2014-04-23 48 878,4 Extern
2014-04-23 48 878,4 Intern
2014-04-23 48 878,4 unknown pn
2014-04-22 48 595,2 Intern
2014-04-22 48 595,2 unknown pn
2014-04-17 48 1116 Extern
2014-04-17 48 1116 Intern
2014-04-17 48 1116 unknown pn
2014-04-16 48 957 Extern
2014-04-16 48 957 Intern
2014-04-16 48 957 unknown pn
2014-04-15 38 724,8 Extern
2014-04-15 38 724,8 Intern
2014-04-15 38 724,8 unknown pn
2014-04-14 48 903 Extern
2014-04-14 48 903 Intern
2014-04-14 48 903 unknown pn
2014-04-11 34 1170,6 Extern
2014-04-11 34 1170,6 Intern
2014-04-11 34 1170,6 unknown pn
2014-04-10 48 672
2014-04-10 48 672 Extern
2014-04-10 48 672 Intern
2014-04-10 48 672 unknown pn
2014-04-09 48 382,8 Extern
2014-04-09 48 382,8 Intern
2014-04-09 48 382,8 unknown pn
2014-04-08 48 2227,2 Extern
2014-04-08 48 2227,2 Intern
2014-04-08 48 2227,2 unknown pn
2014-04-07 48 1443,6 Extern
2014-04-07 48 1443,6 Intern
2014-04-07 48 1443,6 unknown pn
2014-04-04 34 291,6 Extern
2014-04-04 34 291,6 Intern
2014-04-03 48 658,8 Extern
2014-04-03 48 658,8 Intern
2014-04-03 48 658,8 unknown pn
2014-04-02 48 558 Extern
2014-04-02 48 558 Intern
2014-04-02 48 558 unknown pn
2014-04-01 45 1955,4 Intern
2014-04-01 45 1955,4 unknown pn
2014-03-31 34 246 Extern
2014-03-31 34 246 Intern
2014-03-31 34 246 unknown pn
2014-03-27 42 333 Intern
2014-03-27 42 333 unknown pn
2014-03-26 37 712,8 Extern
2014-03-26 37 712,8 Intern
2014-03-26 37 712,8 unknown pn
2014-03-25 42 589,2 Extern
2014-03-25 42 589,2 Intern
2014-03-25 42 589,2 unknown pn
2014-03-24 42 205,8 Extern
2014-03-24 42 205,8 Intern
2014-03-21 29 379,8 Extern
2014-03-21 29 379,8 Intern
2014-03-21 29 379,8 unknown pn
2014-03-20 42 744 Intern
2014-03-20 42 744 unknown pn
2014-03-19 42 969,6 Intern
2014-03-19 42 969,6 unknown pn
2014-03-18 42 617,4 Extern
2014-03-18 42 617,4 Intern
2014-03-18 42 617,4 unknown pn
2014-03-17 42 636,6
2014-03-17 42 636,6 Extern
2014-03-17 42 636,6 Intern
2014-03-17 42 636,6 unknown pn
2014-03-14 29 234 Extern
2014-03-14 29 234 Intern
2014-03-14 29 234 unknown pn
2014-03-13 42 271,8 Intern
2014-03-13 42 271,8 unknown pn
2014-03-12 42 676,2 Extern
2014-03-12 42 676,2 Intern
2014-03-12 42 676,2 unknown pn
2014-03-11 42 410,4
2014-03-11 42 410,4 Intern
2014-03-11 42 410,4 unknown pn
2014-03-10 42 361,8 intern
2014-03-10 42 361,8 unknown pn
2014-03-07 29 610,8 Extern
2014-03-07 29 610,8 Intern
2014-03-07 29 610,8 unknown pn
2014-03-06 42 1733,4 Extern
2014-03-06 42 1733,4 Intern
2014-03-06 42 1733,4 unknown pn
2014-03-05 42 349,8 Extern
2014-03-05 42 349,8 Intern
2014-03-05 42 349,8 unknown pn
2014-03-04 42 244,2 Intern
2014-03-04 42 244,2 unknown pn
2014-03-03 42 319,2 Extern
2014-03-03 42 319,2 Intern
2014-02-28 29 809,4 Extern
2014-02-28 29 809,4 Intern
2014-02-28 29 809,4 unknown pn
2014-02-27 42 325,2
2014-02-27 42 325,2 Extern
2014-02-27 42 325,2 Intern
2014-02-27 42 325,2 unknown pn
2014-02-26 42 479,4 Intern
2014-02-26 42 479,4 unknown pn
2014-02-25 42 616,2 Extern
2014-02-25 42 616,2 Intern
2014-02-25 42 616,2 unknown pn
2014-02-24 42 667,8 Extern
2014-02-24 42 667,8 Intern
2014-02-24 42 667,8 unknown pn
2014-02-21 27 489 Extern
2014-02-21 27 489 Intern
2014-02-21 27 489 unknown pn
2014-02-20 42 735
2014-02-20 42 735 Extern
2014-02-20 42 735 Intern
2014-02-20 42 735 unknown pn
2014-02-19 42 875,4 Extern
2014-02-19 42 875,4 Intern
2014-02-19 42 875,4 unknown pn
2014-02-18 42 854,4
2014-02-18 42 854,4 Intern
2014-02-18 42 854,4 unknown pn
2014-02-17 42 627 Intern
2014-02-17 42 627 unknown pn
2014-02-14 28 463,2
2014-02-14 28 463,2 Extern
2014-02-14 28 463,2 Intern
2014-02-14 28 463,2 unknown pn
2014-02-13 42 903
2014-02-13 42 903 Extern
2014-02-13 42 903 Intern
2014-02-13 42 903 unknown pn
2014-02-12 42 531,6 Extern
2014-02-12 42 531,6 Intern
2014-02-12 42 531,6 unknown pn
2014-02-11 42 658,2 Intern
2014-02-11 42 658,2 unknown pn
2014-02-10 42 844,2 Extern
2014-02-10 42 844,2 Intern
2014-02-10 42 844,2 unknown pn
2014-02-07 29 331,2 Intern
2014-02-07 29 331,2 unknown pn
2014-02-06 42 963 Extern
2014-02-06 42 963 Intern
2014-02-06 42 963 unknown pn
2014-02-05 42 916,2 Extern
2014-02-05 42 916,2 Intern
2014-02-05 42 916,2 unknown pn
2014-02-04 42 838,8 Extern
2014-02-04 42 838,8 Intern
2014-02-04 42 838,8 unknown pn
2014-02-03 42 1194 Extern
2014-02-03 42 1194 Intern
2014-02-03 42 1194 unknown pn
2014-01-31 29 501,6 Extern
2014-01-31 29 501,6 Intern
2014-01-31 29 501,6 unknown pn
2014-01-30 42 577,8 Extern
2014-01-30 42 577,8 Intern
2014-01-30 42 577,8 unknown pn
2014-01-29 42 1769,4 Extern
2014-01-29 42 1769,4 Intern
2014-01-29 42 1769,4 unknown pn
2014-01-28 42 1172,4 Extern
2014-01-28 42 1172,4 Intern
2014-01-28 42 1172,4 unknown pn
2014-01-27 42 1075,2 Intern
2014-01-27 42 1075,2 unknown pn
2014-01-24 29 694,8 Intern
2014-01-24 29 694,8 unknown pn
2014-01-23 42 673,8 Intern
2014-01-23 42 673,8 unknown pn
2014-01-22 42 893,4 Intern
2014-01-22 42 893,4 unknown pn
2014-01-21 42 598,8 Extern
2014-01-21 42 598,8 Intern
2014-01-21 42 598,8 unknown pn
2014-01-20 41 1135,2 Extern
2014-01-20 41 1135,2 Intern
2014-01-20 41 1135,2 unknown pn
2014-01-17 29 964,2 Intern
2014-01-17 29 964,2 unknown pn
2014-01-16 42 571,8 Extern
2014-01-16 42 571,8 Intern
2014-01-16 42 571,8 unknown pn
2014-01-15 42 1500,6 Extern
2014-01-15 42 1500,6 Intern
2014-01-15 42 1500,6 unknown pn
2014-01-14 42 1689,6 Extern
2014-01-14 42 1689,6 Intern
2014-01-14 42 1689,6 unknown pn
2014-01-13 42 960 Extern
2014-01-13 42 960 Intern
2014-01-13 42 960 unknown pn
2014-01-10 29 661,2 Intern
2014-01-10 29 661,2 unknown pn
2014-01-09 42 1084,8 Extern
2014-01-09 42 1084,8 Intern
2014-01-09 42 1084,8 unknown pn
2014-01-08 42 1057,8 Extern
2014-01-08 42 1057,8 Intern
2014-01-08 42 1057,8 unknown pn
2014-01-07 36 720 Intern
2014-01-07 36 720 unknown pn
2013-12-19 33 368,4 Intern
2013-12-19 33 368,4 unknown pn
2013-12-18 42 844,8 Extern
2013-12-18 42 844,8 Intern
2013-12-18 42 844,8 unknown pn
2013-12-17 45 705,6 Extern
2013-12-17 45 705,6 Intern
2013-12-17 45 705,6 unknown pn
2013-12-16 45 673,8 Extern
2013-12-16 45 673,8 Intern
2013-12-16 45 673,8 unknown pn
2013-12-13 29 346,8 Extern
2013-12-13 29 346,8 Intern
2013-12-13 29 346,8 unknown pn
2013-12-12 45 1015,2 Extern
2013-12-12 45 1015,2 Intern
2013-12-12 45 1015,2 unknown pn
2013-12-11 46 1291,2 Extern
2013-12-11 46 1291,2 Intern
2013-12-11 46 1291,2 unknown pn
2013-12-10 41 1636,2 Extern
2013-12-10 41 1636,2 Intern
2013-12-10 41 1636,2 unknown pn
2013-12-09 42 785,4 Extern
2013-12-09 42 785,4 Intern
2013-12-09 42 785,4 unknown pn
2013-12-06 29 644,4 Extern
2013-12-06 29 644,4 Intern
2013-12-06 29 644,4 unknown pn
2013-12-05 42 532,2 Extern
2013-12-05 42 532,2 Intern
2013-12-05 42 532,2 unknown pn
2013-12-04 42 802,2 Extern
2013-12-04 42 802,2 Intern
2013-12-04 42 802,2 unknown pn
2013-12-03 42 1353 Extern
2013-12-03 42 1353 Intern
2013-12-03 42 1353 unknown pn
2013-12-02 42 756,6 Extern
2013-12-02 42 756,6 Intern
2013-12-02 42 756,6 unknown pn
2013-11-29 29 1099,2 Extern
2013-11-29 29 1099,2 Intern
2013-11-29 29 1099,2 unknown pn
2013-11-28 42 640,8 Extern
2013-11-28 42 640,8 Intern
2013-11-28 42 640,8 unknown pn
2013-11-27 42 874,2 Extern
2013-11-27 42 874,2 Intern
2013-11-27 42 874,2 unknown pn
2013-11-26 42 1083,6 Extern
2013-11-26 42 1083,6 Intern
2013-11-26 42 1083,6 unknown pn
2013-11-25 42 973,2 Extern
2013-11-25 42 973,2 Intern
2013-11-25 42 973,2 unknown pn
2013-11-22 29 702 Intern
2013-11-22 29 702 unknown pn
2013-11-21 42 1289,4 Intern
2013-11-21 42 1289,4 unknown pn
2013-11-20 42 330 Intern
2013-11-20 42 330 unknown pn
2013-11-19 42 612 Intern
2013-11-19 42 612 unknown pn
2013-11-18 42 940,8 Extern
2013-11-18 42 940,8 Intern
2013-11-18 42 940,8 unknown pn
2013-11-15 29 801 Extern
2013-11-15 29 801 Intern
2013-11-15 29 801 unknown pn
2013-11-14 42 1344 Extern
2013-11-14 42 1344 Intern
2013-11-14 42 1344 unknown pn
2013-11-13 42 456 Intern
2013-11-13 42 456 unknown pn
2013-11-12 42 603,6 Extern
2013-11-12 42 603,6 Intern
2013-11-12 42 603,6 unknown pn
2013-11-11 42 730,8 Intern
2013-11-11 42 730,8 unknown pn
2013-11-08 29 1162,2 Extern
2013-11-08 29 1162,2 Intern
2013-11-08 29 1162,2 unknown pn
2013-11-07 42 834,6 Intern
2013-11-07 42 834,6 unknown pn
2013-11-06 42 3031,2 Extern
2013-11-06 42 3031,2 Intern
2013-11-06 42 3031,2 unknown pn
2013-11-05 42 1333,2 Extern
2013-11-05 42 1333,2 Intern
2013-11-05 42 1333,2 unknown pn
2013-10-30 42 497,4 Intern
2013-10-30 42 497,4 unknown pn
2013-10-29 42 1042,2 Extern
2013-10-29 42 1042,2 Intern
2013-10-29 42 1042,2 unknown pn
2013-10-28 42 618 Extern
2013-10-28 42 618 Intern
2013-10-28 42 618 unknown pn
2013-10-25 29 690,6 Extern
2013-10-25 29 690,6 Intern
2013-10-25 29 690,6 unknown pn
2013-10-24 42 1282,2 Extern
2013-10-24 42 1282,2 Intern
2013-10-24 42 1282,2 unknown pn
2013-10-23 42 2209,8 Extern
2013-10-23 42 2209,8 Intern
2013-10-23 42 2209,8 unknown pn
2013-10-22 42 1017,6 Extern
2013-10-22 42 1017,6 Intern
2013-10-22 42 1017,6 unknown pn
2013-10-21 42 1193,4 Extern
2013-10-21 42 1193,4 Intern
2013-10-21 42 1193,4 unknown pn
2013-10-18 29 1539 Intern
2013-10-18 29 1539 unknown pn
2013-10-17 42 1750,2 Extern
2013-10-17 42 1750,2 Intern
2013-10-17 42 1750,2 unknown pn
2013-10-16 42 3306,6 Extern
2013-10-16 42 3306,6 Intern
2013-10-16 42 3306,6 unknown pn
2013-10-15 42 1857,6 Extern
2013-10-15 42 1857,6 Intern
2013-10-15 42 1857,6 unknown pn
2013-10-14 42 1291,2 Extern
2013-10-14 42 1291,2 Intern
2013-10-14 42 1291,2 unknown pn
2013-10-11 29 1013,4 Extern
2013-10-11 29 1013,4 Intern
2013-10-11 29 1013,4 unknown pn
2013-10-10 42 2165,4 Extern
2013-10-10 42 2165,4 Intern
2013-10-10 42 2165,4 unknown pn
2013-10-09 42 1711,2 Extern
2013-10-09 42 1711,2 Intern
2013-10-09 42 1711,2 unknown pn
2013-10-08 42 1536,6 Extern
2013-10-08 42 1536,6 Intern
2013-10-08 42 1536,6 unknown pn
2013-10-07 42 2202 Extern
2013-10-07 42 2202 Intern
2013-10-07 42 2202 unknown pn
2013-10-04 33 1257 Extern
2013-10-04 33 1257 Intern
2013-10-04 33 1257 unknown pn
2013-10-03 50 3415,8 Extern
2013-10-03 50 3415,8 Intern
2013-10-03 50 3415,8 unknown pn
2013-10-02 50 3526,2 Extern
2013-10-02 50 3526,2 Intern
2013-10-02 50 3526,2 unknown pn
2013-10-01 50 3558 Extern
2013-10-01 50 3558 Intern
2013-10-01 50 3558 unknown pn
2013-09-30 50 2158,8 Extern
2013-09-30 50 2158,8 Intern
2013-09-30 50 2158,8 unknown pn
2013-09-27 33 870,6 Extern
2013-09-27 33 870,6 Intern
2013-09-27 33 870,6 unknown pn
2013-09-26 50 2206,8 Extern
2013-09-26 50 2206,8 Intern
2013-09-26 50 2206,8 unknown pn
2013-09-25 50 3124,2 Extern
2013-09-25 50 3124,2 Intern
2013-09-25 50 3124,2 unknown pn
2013-09-24 44 1855,8 Extern
2013-09-24 44 1855,8 Intern
2013-09-24 44 1855,8 unknown pn
2013-09-23 50 1764,6 Extern
2013-09-23 50 1764,6 Intern
2013-09-23 50 1764,6 unknown pn
2013-09-20 33 1522,2 Extern
2013-09-20 33 1522,2 Intern
2013-09-20 33 1522,2 unknown pn
2013-09-19 50 2550,6 Extern
2013-09-19 50 2550,6 Intern
2013-09-19 50 2550,6 unknown pn
2013-09-18 50 1215 Extern
2013-09-18 50 1215 Intern
2013-09-18 50 1215 unknown pn
2013-09-17 50 2974,2 Extern
2013-09-17 50 2974,2 Intern
2013-09-17 50 2974,2 unknown pn
2013-09-16 50 3019,8 Extern
2013-09-16 50 3019,8 intern
2013-09-16 50 3019,8 unknown pn
2013-09-13 32 2996,4 extern
2013-09-13 32 2996,4 intern
2013-09-13 32 2996,4 unknown pn
2013-09-12 51 3344,4 extern
2013-09-12 51 3344,4 intern
2013-09-12 51 3344,4 unknown pn
2013-09-11 50 2751 extern
2013-09-11 50 2751 intern
2013-09-11 50 2751 unknown pn
2013-09-10 50 3383,4 extern
2013-09-10 50 3383,4 intern
2013-09-10 50 3383,4 unknown pn
2013-09-09 50 5530,8 extern
2013-09-09 50 5530,8 intern
2013-09-09 50 5530,8 unknown pn
2013-09-06 33 4634,4 extern
2013-09-06 33 4634,4 intern
2013-09-06 33 4634,4 unknown pn
2013-09-05 50 3040,2 extern
2013-09-05 50 3040,2 intern
2013-09-05 50 3040,2 unknown pn
2013-09-04 50 2301 extern
2013-09-04 50 2301 intern
2013-09-04 50 2301 unknown pn
2013-09-03 50 2691 extern
2013-09-03 50 2691 intern
2013-09-03 50 2691 unknown pn
2013-09-02 50 2992,8 extern
2013-09-02 50 2992,8 intern
2013-09-02 50 2992,8 unknown pn
2013-08-30 33 1344,6 extern
2013-08-30 33 1344,6 intern
2013-08-30 33 1344,6 unknown pn
2013-08-29 50 2766 extern
2013-08-29 50 2766 intern
2013-08-29 50 2766 unknown pn
2013-08-28 50 4505,4 extern
2013-08-28 50 4505,4 intern
2013-08-28 50 4505,4 unknown pn
2013-08-27 49 5052,6 extern
2013-08-27 49 5052,6 intern
2013-08-27 49 5052,6 unknown pn
2013-08-26 50 3137,4 extern
2013-08-26 50 3137,4 intern
2013-08-26 50 3137,4 unknown pn
2013-08-23 33 1322,4 extern
2013-08-23 33 1322,4 intern
2013-08-23 33 1322,4 unknown pn
2013-08-22 50 2606,4 extern
2013-08-22 50 2606,4 intern
2013-08-22 50 2606,4 unknown pn
2013-08-21 50 1516,2 extern
2013-08-21 50 1516,2 intern
2013-08-21 50 1516,2 unknown pn
2013-08-20 50 2954,4 extern
2013-08-20 50 2954,4 intern
2013-08-20 50 2954,4 unknown pn
2013-08-19 48 2820,6 extern
2013-08-19 48 2820,6 intern
2013-08-19 48 2820,6 N/A
2013-08-19 48 2820,6 unknown pn
2013-07-26 34 6309 extern
2013-07-26 34 6309 intern
2013-07-26 34 6309 N/A
2013-07-26 34 6309 unknown pn
2013-07-25 49 4768,2 extern
2013-07-25 49 4768,2 intern
2013-07-25 49 4768,2 unknown pn
2013-07-24 50 6151,2 extern
2013-07-24 50 6151,2 intern
2013-07-24 50 6151,2 N/A
2013-07-24 50 6151,2 unknown pn
2013-07-23 50 5644,2 extern
2013-07-23 50 5644,2 intern
2013-07-23 50 5644,2 unknown pn
2013-07-22 41 2578,2 extern
2013-07-22 41 2578,2 intern
2013-07-22 41 2578,2 N/A
2013-07-22 41 2578,2 unknown pn
2013-07-19 50 4068,6 extern
2013-07-19 50 4068,6 intern
2013-07-19 50 4068,6 N/A
2013-07-19 50 4068,6 unknown pn
2013-07-18 50 3175,2 extern
2013-07-18 50 3175,2 intern
2013-07-18 50 3175,2 N/A
2013-07-18 50 3175,2 unknown pn
2013-07-17 50 3415,2 extern
2013-07-17 50 3415,2 intern
2013-07-17 50 3415,2 unknown pn
2013-07-16 49 2549,4 extern
2013-07-16 49 2549,4 intern
2013-07-16 49 2549,4 N/A
2013-07-16 49 2549,4 unknown pn
2013-07-15 48 1646,4 extern
2013-07-15 48 1646,4 intern
2013-07-15 48 1646,4 unknown pn
2013-07-12 35 3958,8 extern
2013-07-12 35 3958,8 intern
2013-07-12 35 3958,8 unknown pn
2013-07-11 50 4128,6 extern
2013-07-11 50 4128,6 intern
2013-07-11 50 4128,6 unknown pn
2013-07-10 50 2857,2 extern
2013-07-10 50 2857,2 intern
2013-07-10 50 2857,2 N/A
2013-07-10 50 2857,2 unknown pn
2013-07-09 50 3052,2 extern
2013-07-09 50 3052,2 intern
2013-07-09 50 3052,2 unknown pn
2013-07-08 50 4407 extern
2013-07-08 50 4407 intern
2013-07-08 50 4407 N/A
2013-07-08 50 4407 unknown pn
2013-07-05 50 4899 extern
2013-07-05 50 4899 intern
2013-07-05 50 4899 unknown pn
2013-07-04 50 7616,4 extern
2013-07-04 50 7616,4 intern
2013-07-04 50 7616,4 N/A
2013-07-04 50 7616,4 unknown pn
2013-07-03 50 3933 extern
2013-07-03 50 3933 intern
2013-07-03 50 3933 N/A
2013-07-03 50 3933 unknown pn
2013-07-02 50 2403,6 extern
2013-07-02 50 2403,6 intern
2013-07-02 50 2403,6 unknown pn
2013-07-01 53 5177,4 extern
2013-07-01 53 5177,4 intern
2013-07-01 53 5177,4 unknown pn
2013-06-28 35 1912,8 extern
2013-06-28 35 1912,8 intern
2013-06-28 35 1912,8 unknown pn
2013-06-27 53 3837,6 extern
2013-06-27 53 3837,6 intern
2013-06-27 53 3837,6 N/A
2013-06-27 53 3837,6 unknown pn
2013-06-26 53 4854,6 extern
2013-06-26 53 4854,6 intern
2013-06-26 53 4854,6 unknown pn
2013-06-25 53 5073 extern
2013-06-25 53 5073 intern
2013-06-25 53 5073 unknown pn
2013-06-22 35 2986,8 extern
2013-06-22 35 2986,8 intern
2013-06-22 35 2986,8 unknown pn
2013-06-21 54 4350 extern
2013-06-21 54 4350 intern
2013-06-21 54 4350 unknown pn
2013-06-20 52 5392,2 extern
2013-06-20 52 5392,2 intern
2013-06-20 52 5392,2 unknown pn
2013-06-19 50 3231 extern
2013-06-19 50 3231 intern
2013-06-19 50 3231 unknown pn
2013-06-18 53 5475,6 extern
2013-06-18 53 5475,6 intern
2013-06-18 53 5475,6 unknown pn
2013-06-17 53 6033,6 extern
2013-06-17 53 6033,6 intern
2013-06-17 53 6033,6 unknown pn
2013-06-14 41 4268,4 extern
2013-06-14 41 4268,4 intern
2013-06-14 41 4268,4 unknown pn
2013-06-13 53 3223,8 extern
2013-06-13 53 3223,8 intern
2013-06-13 53 3223,8 N/A
2013-06-13 53 3223,8 unknown pn
2013-06-12 53 3259,8 extern
2013-06-12 53 3259,8 intern
2013-06-12 53 3259,8 N/A
2013-06-12 53 3259,8 unknown pn
2013-06-11 53 2354,4 extern
2013-06-11 53 2354,4 intern
2013-06-11 53 2354,4 N/A
2013-06-11 53 2354,4 unknown pn
2013-06-10 53 2130,6 extern
2013-06-10 53 2130,6 intern
2013-06-10 53 2130,6 unknown pn
2013-06-07 47 2565 extern
2013-06-07 47 2565 intern
2013-06-07 47 2565 N/A
2013-06-07 47 2565 unknown pn
2013-06-06 53 2122,8 extern
2013-06-06 53 2122,8 intern
2013-06-06 53 2122,8 N/A
2013-06-06 53 2122,8 unknown pn
2013-06-05 53 3357,6 extern
2013-06-05 53 3357,6 intern
2013-06-05 53 3357,6 unknown pn
2013-06-04 53 4665 extern
2013-06-04 53 4665 intern
2013-06-04 53 4665 unknown pn
2013-06-03 53 1936,8 extern
2013-06-03 53 1936,8 intern
2013-06-03 53 1936,8 unknown pn
2013-05-29 45 2630,4 extern
2013-05-29 45 2630,4 intern
2013-05-29 45 2630,4 unknown pn
2013-05-28 50 4081,8 extern
2013-05-28 50 4081,8 intern
2013-05-28 50 4081,8 N/A
2013-05-28 50 4081,8 unknown pn
2013-05-27 51 3283,8 extern
2013-05-27 51 3283,8 intern
2013-05-27 51 3283,8 unknown pn
2013-05-24 49 2399,4 extern
2013-05-24 49 2399,4 intern
2013-05-24 49 2399,4 unknown pn
2013-05-23 50 3859,8 extern
2013-05-23 50 3859,8 intern
2013-05-23 50 3859,8 N/A
2013-05-23 50 3859,8 unknown pn
2013-05-22 50 2175 extern
2013-05-22 50 2175 intern
2013-05-22 50 2175 unknown pn
2013-05-21 50 2655,6 extern
2013-05-21 50 2655,6 intern
2013-05-21 50 2655,6 unknown pn
2013-05-17 34 1116,6 extern
2013-05-17 34 1116,6 intern
2013-05-17 34 1116,6 unknown pn
2013-05-16 46 2395,2 extern
2013-05-16 46 2395,2 intern
2013-05-16 46 2395,2 unknown pn
2013-05-15 47 2166,6 extern
2013-05-15 47 2166,6 intern
2013-05-15 47 2166,6 unknown pn
2013-05-14 47 2747,4 extern
2013-05-14 47 2747,4 intern
2013-05-14 47 2747,4 unknown pn
2013-05-13 47 4593 extern
2013-05-13 47 4593 intern
2013-05-13 47 4593 N/A
2013-05-13 47 4593 unknown pn
2013-05-08 47 2911,8 extern
2013-05-08 47 2911,8 intern
2013-05-08 47 2911,8 N/A
2013-05-08 47 2911,8 unknown pn
2013-05-07 50 3012,6 extern
2013-05-07 50 3012,6 intern
2013-05-07 50 3012,6 N/A
2013-05-07 50 3012,6 unknown pn
2013-05-06 50 3925,2 extern
2013-05-06 50 3925,2 intern
2013-05-06 50 3925,2 N/A
2013-05-06 50 3925,2 unknown pn
2013-05-03 33 2566,2 extern
2013-05-03 33 2566,2 intern
2013-05-03 33 2566,2 unknown pn
2013-05-02 49 712,8 extern
2013-05-02 49 712,8 intern
2013-05-02 49 712,8 N/A
2013-05-02 49 712,8 unknown pn
2013-04-30 33 786 extern
2013-04-30 33 786 intern
2013-04-30 33 786 N/A
2013-04-30 33 786 unknown pn
2013-04-29 48 1141,2 extern
2013-04-29 48 1141,2 intern
2013-04-29 48 1141,2 unknown pn
2013-04-25 45 973,2 intern
2013-04-25 45 973,2 unknown pn
2013-04-24 45 961,2 intern
2013-04-24 45 961,2 N/A
2013-04-24 45 961,2 unknown pn
2013-04-23 45 409,8 intern
2013-04-23 45 409,8 unknown pn
2013-04-22 45 536,4 extern
2013-04-22 45 536,4 intern
2013-04-22 45 536,4 unknown pn
2013-04-19 29 514,2 intern
2013-04-19 29 514,2 unknown pn
2013-04-18 42 340,8 intern
2013-04-18 42 340,8 unknown pn
2013-04-17 42 540 intern
2013-04-17 42 540 unknown pn
2013-04-16 42 350,4 intern
2013-04-16 42 350,4 unknown pn
2013-04-15 42 835,8 extern
2013-04-15 42 835,8 intern
2013-04-15 42 835,8 unknown pn
2013-04-12 29 432 extern
2013-04-12 29 432 intern
2013-04-12 29 432 unknown pn
2013-04-11 42 1473,6 extern
2013-04-11 42 1473,6 intern
2013-04-11 42 1473,6 unknown pn
2013-04-10 42 850,8 extern
2013-04-10 42 850,8 intern
2013-04-10 42 850,8 unknown pn
2013-04-09 42 1750,8 extern
2013-04-09 42 1750,8 intern
2013-04-09 42 1750,8 N/A
2013-04-09 42 1750,8 unknown pn
2013-04-08 42 1569 extern
2013-04-08 42 1569 intern
2013-04-08 42 1569 unknown pn
2013-04-05 29 195 extern
2013-04-05 29 195 intern
2013-04-05 29 195 unknown pn
2013-04-04 41 567,6 intern
2013-04-04 41 567,6 unknown pn
2013-04-03 42 1248,6 extern
2013-04-03 42 1248,6 intern
2013-04-03 42 1248,6 unknown pn
2013-03-28 42 664,8 extern
2013-03-28 42 664,8 intern
2013-03-28 42 664,8 unknown pn
2013-03-27 42 2118,6 extern
2013-03-27 42 2118,6 intern
2013-03-27 42 2118,6 unknown pn
2013-03-26 43 1007,4 extern
2013-03-26 43 1007,4 intern
2013-03-26 43 1007,4 unknown pn
2013-03-25 41 1278,6 extern
2013-03-25 41 1278,6 intern
2013-03-25 41 1278,6 unknown pn
2013-03-22 29 704,4 extern
2013-03-22 29 704,4 intern
2013-03-21 42 1209,6 extern
2013-03-21 42 1209,6 intern
2013-03-21 42 1209,6 unknown pn
2013-03-20 42 741 extern
2013-03-20 42 741 intern
2013-03-20 42 741 unknown pn
2013-03-19 42 526,2 extern
2013-03-19 42 526,2 intern
2013-03-19 42 526,2 unknown pn
2013-03-18 42 613,2 extern
2013-03-18 42 613,2 intern
2013-03-14 42 1050,6 extern
2013-03-14 42 1050,6 intern
2013-03-14 42 1050,6 unknown pn
2013-03-13 42 1861,8 extern
2013-03-13 42 1861,8 intern
2013-03-13 42 1861,8 unknown pn
2013-03-12 41 454,8 extern
2013-03-12 41 454,8 intern
2013-03-12 41 454,8 unknown pn
2013-03-11 42 846 extern
2013-03-11 42 846 intern
2013-03-11 42 846 unknown pn
2013-03-08 29 1942,2 extern
2013-03-08 29 1942,2 intern
2013-03-08 29 1942,2 unknown pn
2013-03-07 42 1126,8 extern
2013-03-07 42 1126,8 intern
2013-03-07 42 1126,8 unknown pn
2013-03-06 42 927,6 extern
2013-03-06 42 927,6 intern
2013-03-06 42 927,6 N/A
2013-03-06 42 927,6 unknown pn
2013-03-05 42 361,8 extern
2013-03-05 42 361,8 intern
2013-03-05 42 361,8 unknown pn
2013-03-04 42 369,6 intern
2013-03-04 42 369,6 unknown pn
2013-03-01 29 186,6 intern
2013-03-01 29 186,6 unknown pn
2013-02-28 42 1078,2 extern
2013-02-28 42 1078,2 intern
2013-02-28 42 1078,2 unknown pn
2013-02-27 42 482,4 extern
2013-02-27 42 482,4 intern
2013-02-27 42 482,4 unknown pn
2013-02-26 42 267 extern
2013-02-26 42 267 intern
2013-02-26 42 267 unknown pn
2013-02-25 42 700,8 extern
2013-02-25 42 700,8 intern
2013-02-25 42 700,8 unknown pn
2013-02-22 29 614,4 extern
2013-02-22 29 614,4 intern
2013-02-22 29 614,4 unknown pn
2013-02-21 42 1393,2 extern
2013-02-21 42 1393,2 intern
2013-02-21 42 1393,2 unknown pn
2013-02-20 42 963,6 extern
2013-02-20 42 963,6 intern
2013-02-20 42 963,6 unknown pn
2013-02-19 42 564 extern
2013-02-19 42 564 intern
2013-02-19 42 564 unknown pn
2013-02-18 42 922,2 extern
2013-02-18 42 922,2 intern
2013-02-18 42 922,2 unknown pn
2013-02-15 29 471 intern
2013-02-15 29 471 unknown pn
2013-02-14 42 1020 extern
2013-02-14 42 1020 intern
2013-02-14 42 1020 unknown pn
2013-02-13 42 1151,4 extern
2013-02-13 42 1151,4 intern
2013-02-13 42 1151,4 unknown pn
2013-02-12 42 1131,6 extern
2013-02-12 42 1131,6 intern
2013-02-12 42 1131,6 unknown pn
2013-02-11 43 1301,4 intern
2013-02-11 43 1301,4 unknown pn
2013-02-07 46 1038,6 intern
2013-02-07 46 1038,6 unknown pn
2013-02-06 47 694,2 extern
2013-02-06 47 694,2 intern
2013-02-06 47 694,2 unknown pn
2013-02-05 47 998,4 intern
2013-02-05 47 998,4 unknown pn
2013-02-04 47 562,2 extern
2013-02-04 47 562,2 intern
2013-02-04 47 562,2 unknown pn
2013-02-01 32 79,8 intern
2013-01-31 47 499,8 extern
2013-01-31 47 499,8 intern
2013-01-31 47 499,8 unknown pn
2013-01-30 47 786 extern
2013-01-30 47 786 intern
2013-01-30 47 786 unknown pn
2013-01-29 47 470,4 extern
2013-01-29 47 470,4 intern
2013-01-29 47 470,4 unknown pn
2013-01-28 47 1290,6 extern
2013-01-28 47 1290,6 intern
2013-01-28 47 1290,6 unknown pn
2013-01-24 47 1690,8 extern
2013-01-24 47 1690,8 intern
2013-01-24 47 1690,8 unknown pn
2013-01-23 47 1519,8 extern
2013-01-23 47 1519,8 intern
2013-01-23 47 1519,8 unknown pn
2013-01-22 47 682,2 extern
2013-01-22 47 682,2 intern
2013-01-22 47 682,2 unknown pn
2013-01-21 45 672,6 extern
2013-01-21 45 672,6 intern
2013-01-21 45 672,6 unknown pn
2013-01-18 33 1031,4 extern
2013-01-18 33 1031,4 intern
2013-01-18 33 1031,4 N/A
2013-01-18 33 1031,4 unknown pn
2013-01-17 47 681 extern
2013-01-17 47 681 intern
2013-01-17 47 681 unknown pn
2013-01-16 47 647,4 extern
2013-01-16 47 647,4 intern
2013-01-16 47 647,4 unknown pn
2013-01-15 47 1181,4 extern
2013-01-15 47 1181,4 intern
2013-01-15 47 1181,4 unknown pn
2013-01-14 47 1908 extern
2013-01-14 47 1908 intern
2013-01-14 47 1908 unknown pn
2013-01-11 29 870,6 extern
2013-01-11 29 870,6 intern
2013-01-11 29 870,6 unknown pn
2013-01-10 47 1992,6 extern
2013-01-10 47 1992,6 intern
2013-01-10 47 1992,6 unknown pn
2013-01-09 47 3123 extern
2013-01-09 47 3123 intern
2013-01-09 47 3123 unknown pn
2013-01-08 47 2632,8 extern
2013-01-08 47 2632,8 intern
2013-01-08 47 2632,8 unknown pn
2013-01-07 45 759,6 extern
2013-01-07 45 759,6 intern
2013-01-07 45 759,6 unknown pn
2012-12-21 33 909 extern
2012-12-21 33 909 intern
2012-12-21 33 909 unknown pn
2012-12-20 47 880,8 extern
2012-12-20 47 880,8 intern
2012-12-20 47 880,8 unknown pn
2012-12-19 47 1241,4 extern
2012-12-19 47 1241,4 intern
2012-12-19 47 1241,4 N/A
2012-12-19 47 1241,4 unknown pn
2012-12-18 47 792,6 extern
2012-12-18 47 792,6 intern
2012-12-18 47 792,6 unknown pn
2012-12-17 47 2310,6 intern
2012-12-17 47 2310,6 unknown pn
2012-12-14 35 3495,6 extern
2012-12-14 35 3495,6 intern
2012-12-14 35 3495,6 N/A
2012-12-14 35 3495,6 unknown pn
2012-12-13 50 2869,2 extern
2012-12-13 50 2869,2 intern
2012-12-13 50 2869,2 N/A
2012-12-13 50 2869,2 unknown pn
2012-12-12 50 829,8 extern
2012-12-12 50 829,8 intern
2012-12-12 50 829,8 unknown pn
2012-12-11 50 577,2 extern
2012-12-11 50 577,2 intern
2012-12-11 50 577,2 unknown pn
2012-12-10 50 844,8 extern
2012-12-10 50 844,8 intern
2012-12-10 50 844,8 unknown pn
2012-12-07 35 1489,2 extern
2012-12-07 35 1489,2 intern
2012-12-07 35 1489,2 unknown pn
2012-12-06 50 508,8 extern
2012-12-06 50 508,8 intern
2012-12-06 50 508,8 unknown pn
2012-12-05 50 691,2 extern
2012-12-05 50 691,2 intern
2012-12-05 50 691,2 unknown pn
2012-12-04 50 484,2 extern
2012-12-04 50 484,2 intern
2012-12-04 50 484,2 N/A
2012-12-04 50 484,2 unknown pn
2012-12-03 50 1733,4 extern
2012-12-03 50 1733,4 intern
2012-12-03 50 1733,4 unknown pn
2012-11-30 35 604,2 extern
2012-11-30 35 604,2 intern
2012-11-30 35 604,2 unknown pn
2012-11-29 50 2028,6 extern
2012-11-29 50 2028,6 intern
2012-11-29 50 2028,6 unknown pn
2012-11-28 50 731,4 intern
2012-11-28 50 731,4 unknown pn
2012-11-27 50 436,2 intern
2012-11-27 50 436,2 unknown pn
2012-11-26 50 180 extern
2012-11-26 50 180 intern
2012-11-26 50 180 N/A
2012-11-26 50 180 unknown pn
2012-11-23 32 240,6 extern
2012-11-23 32 240,6 intern
2012-11-23 32 240,6 unknown pn
2012-11-22 50 237,6 extern
2012-11-22 50 237,6 intern
2012-11-22 50 237,6 unknown pn
2012-11-21 50 179,4 extern
2012-11-21 50 179,4 intern
2012-11-21 50 179,4 unknown pn
2012-11-20 50 115,8 extern
2012-11-20 50 115,8 intern
2012-11-20 50 115,8 unknown pn
2012-11-19 53 621,6 extern
2012-11-19 53 621,6 intern
2012-11-19 53 621,6 N/A
2012-11-19 53 621,6 unknown pn
2012-11-16 35 105 N/A
2012-11-16 35 105 unknown pn
2012-11-15 49 60 extern
2012-11-15 49 60 intern
2012-11-15 49 60 N/A
2012-11-14 50 739,8 extern
2012-11-14 50 739,8 intern
2012-11-14 50 739,8 N/A
2012-11-14 50 739,8 unknown pn
2012-11-13 50 1923,6 extern
2012-11-13 50 1923,6 intern
2012-11-13 50 1923,6 N/A
2012-11-13 50 1923,6 unknown pn
2012-11-12 50 430,2 extern
2012-11-12 50 430,2 intern
2012-11-12 50 430,2 N/A
2012-11-12 50 430,2 unknown pn
2012-11-09 34 576,6 extern
2012-11-09 34 576,6 intern
2012-11-09 34 576,6 unknown pn
2012-11-08 50 378 extern
2012-11-08 50 378 intern
2012-11-08 50 378 unknown pn
2012-11-07 50 1224,6 extern
2012-11-07 50 1224,6 intern
2012-11-07 50 1224,6 unknown pn
2012-11-06 50 2261,4 extern
2012-11-06 50 2261,4 intern
2012-11-06 50 2261,4 N/A
2012-11-06 50 2261,4 unknown pn
2012-11-05 50 650,4 extern
2012-11-05 50 650,4 intern
2012-11-05 50 650,4 N/A
2012-11-05 50 650,4 unknown pn
2012-10-31 38 570 extern
2012-10-31 38 570 intern
2012-10-31 38 570 unknown pn
2012-10-30 50 2105,4 extern
2012-10-30 50 2105,4 intern
2012-10-30 50 2105,4 N/A
2012-10-30 50 2105,4 unknown pn
2012-10-29 50 887,4 extern
2012-10-29 50 887,4 intern
2012-10-29 50 887,4 N/A
2012-10-29 50 887,4 unknown pn
2012-10-25 50 1814,4 extern
2012-10-25 50 1814,4 intern
2012-10-25 50 1814,4 N/A
2012-10-25 50 1814,4 unknown pn
2012-10-24 50 1027,2 extern
2012-10-24 50 1027,2 intern
2012-10-24 50 1027,2 N/A
2012-10-24 50 1027,2 unknown pn
2012-10-23 49 1161,6 extern
2012-10-23 49 1161,6 intern
2012-10-23 49 1161,6 unknown pn
2012-10-22 50 583,2 extern
2012-10-22 50 583,2 intern
2012-10-22 50 583,2 unknown pn
2012-10-19 35 848,4 intern
2012-10-19 35 848,4 unknown pn
2012-10-18 50 703,2 extern
2012-10-18 50 703,2 intern
2012-10-18 50 703,2 unknown pn
2012-10-17 50 753 extern
2012-10-17 50 753 intern
2012-10-17 50 753 N/A
2012-10-17 50 753 unknown pn
2012-10-16 50 1092,6 extern
2012-10-16 50 1092,6 intern
2012-10-16 50 1092,6 N/A
2012-10-16 50 1092,6 unknown pn
2012-10-15 52 2019,6 extern
2012-10-15 52 2019,6 intern
2012-10-15 52 2019,6 unknown pn
2012-10-12 33 1320 extern
2012-10-12 33 1320 intern
2012-10-12 33 1320 unknown pn
2012-10-11 50 864 extern
2012-10-11 50 864 intern
2012-10-11 50 864 N/A
2012-10-11 50 864 unknown pn
2012-10-10 50 1058,4 extern
2012-10-10 50 1058,4 intern
2012-10-10 50 1058,4 N/A
2012-10-10 50 1058,4 unknown pn
2012-10-09 51 2323,2 extern
2012-10-09 51 2323,2 intern
2012-10-09 51 2323,2 N/A
2012-10-09 51 2323,2 unknown pn
2012-10-08 49 1911,6 extern
2012-10-08 49 1911,6 intern
2012-10-08 49 1911,6 N/A
2012-10-08 49 1911,6 unknown pn
2012-10-05 37 834,6 extern
2012-10-05 37 834,6 intern
2012-10-05 37 834,6 unknown pn
2012-10-04 53 1624,2 extern
2012-10-04 53 1624,2 intern
2012-10-04 53 1624,2 unknown pn
2012-10-03 53 1509,6 extern
2012-10-03 53 1509,6 intern
2012-10-03 53 1509,6 unknown pn
2012-10-02 53 3184,8 extern
2012-10-02 53 3184,8 intern
2012-10-02 53 3184,8 unknown pn
2012-10-01 53 1491,6 extern
2012-10-01 53 1491,6 intern
2012-10-01 53 1491,6 unknown pn
2012-09-28 37 672 extern
2012-09-28 37 672 unknown pn
2012-09-27 53 1127,4 extern
2012-09-27 53 1127,4 intern
2012-09-27 53 1127,4 unknown pn
2012-09-26 53 2457,6 extern
2012-09-26 53 2457,6 intern
2012-09-26 53 2457,6 unknown pn
2012-09-25 53 1609,8 extern
2012-09-25 53 1609,8 intern
2012-09-25 53 1609,8 unknown pn
2012-09-24 53 1423,2 extern
2012-09-24 53 1423,2 intern
2012-09-24 53 1423,2 N/A
2012-09-24 53 1423,2 unknown pn
2012-09-21 37 1333,8 extern
2012-09-21 37 1333,8 intern
2012-09-21 37 1333,8 N/A
2012-09-21 37 1333,8 unknown pn
2012-09-20 53 1695,6 extern
2012-09-20 53 1695,6 intern
2012-09-20 53 1695,6 N/A
2012-09-20 53 1695,6 unknown pn
2012-09-19 51 1351,8 extern
2012-09-19 51 1351,8 intern
2012-09-19 51 1351,8 N/A
2012-09-19 51 1351,8 unknown pn
2012-09-18 53 1036,2 extern
2012-09-18 53 1036,2 intern
2012-09-18 53 1036,2 unknown pn
2012-09-17 53 3462 extern
2012-09-17 53 3462 intern
2012-09-17 53 3462 unknown pn
2012-09-14 0 150 extern
2012-09-14 0 150 intern
2012-09-13 53 2056,2 extern
2012-09-13 53 2056,2 intern
2012-09-13 53 2056,2 unknown pn
2012-09-12 53 3120,6 extern
2012-09-12 53 3120,6 intern
2012-09-12 53 3120,6 N/A
2012-09-12 53 3120,6 unknown pn
2012-09-11 53 2215,2 extern
2012-09-11 53 2215,2 intern
2012-09-11 53 2215,2 N/A
2012-09-11 53 2215,2 unknown pn
2012-09-10 53 2991 extern
2012-09-10 53 2991 intern
2012-09-10 53 2991 N/A
2012-09-10 53 2991 unknown pn
2012-09-07 37 334,8 extern
2012-09-07 37 334,8 intern
2012-09-07 37 334,8 unknown pn
2012-09-06 53 1166,4 extern
2012-09-06 53 1166,4 intern
2012-09-06 53 1166,4 unknown pn
2012-09-05 53 1542,6 extern
2012-09-05 53 1542,6 intern
2012-09-05 53 1542,6 N/A
2012-09-05 53 1542,6 unknown pn
2012-09-04 53 1141,8 extern
2012-09-04 53 1141,8 intern
2012-09-04 53 1141,8 N/A
2012-09-04 53 1141,8 unknown pn
2012-09-03 53 1567,8 extern
2012-09-03 53 1567,8 intern
2012-09-03 53 1567,8 N/A
2012-09-03 53 1567,8 unknown pn
2012-08-31 37 1626,6 extern
2012-08-31 37 1626,6 intern
2012-08-31 37 1626,6 unknown pn
2012-08-30 56 2278,8 unknown pn
2012-08-29 56 5316 extern
2012-08-29 56 5316 intern
2012-08-29 56 5316 N/A
2012-08-29 56 5316 unknown pn
2012-08-28 56 3256,8 extern
2012-08-28 56 3256,8 intern
2012-08-28 56 3256,8 N/A
2012-08-28 56 3256,8 unknown pn
2012-08-27 56 4049,4 N/A
2012-08-27 56 4049,4 unknown pn
2012-08-24 56 2441,4 N/A
2012-08-24 56 2441,4 unknown pn
2012-08-23 56 2959,8 N/A
2012-08-23 56 2959,8 unknown pn
2012-08-22 56 2585,4 Extern
2012-08-22 56 2585,4 N/A
2012-08-22 56 2585,4 unknown pn
2012-08-21 56 3414,6 N/A
2012-08-21 56 3414,6 unknown pn
2012-08-20 13 468 N/A
2012-08-20 13 468 unknown pn
Extern
Intern
56 Extern
56 Intern
56 N/A
56 unknown pn












hello,

I have the following problem can you help me please?thank you for your help.

I have the folloing data and i want the sum of daily volume in a


--calculation of ARTPU per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[ARTPU_daily(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([summe_Reparaturzeit_Daily(excl)]/numberofdevices) END;
----------------------------------------------------------
--calculation of repairtime for per Bereich per day
UPDATE AMPPU_Alle_Fehlteile SET
summe_Reparaturzeit_B_Daily = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,bereich,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile group by Offline_date,Bereich) B
ON A.Offline_date = B.Offline_date and
ISNULL(A.Bereich,'Null') = ISNULL(B.bereich,'Null');


--calculation of average from ARTPU(Bereich) per month
UPDATE AMPPU_Alle_Fehlteile
SET summe_Reparaturzeit_B_Monat =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG(ARTPU_daily_Bereich) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);



--calculation of repairtime for per Disponent per day
UPDATE AMPPU_Alle_Fehlteile SET
Reparaturzeit_Disponent = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Bereich,Offline_date,Disponent,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where Bereich = 'Extern' group by Offline_date,Bereich,Disponent) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Disponent=B.Disponent;



--calculation of average from ARTPU(Teamleader) per month
UPDATE AMPPU_Alle_Fehlteile
SET calcval_T_Monat =
(SELECT AVG(avg_Teamleader)
FROM (SELECT Offline_date, AVG(calcval_Teamleader) AS avg_Teamleader
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Teamleader,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);



--calculation of ARTPU for Teamleader(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[calcval_Teamleader(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([Reparaturzeit_Teamleader(excl)]/numberofdevices) END;


--calculation of average from ARTPU(Bereich) per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [summe_Reparaturzeit_B_Monat(excl)] =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG([ARTPU_daily_Bereich(excl)]) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);




can you helo me please?
thank you
quote:
Originally posted by mana






Hello,
thank you
I have the following codes and when i write them i a job i have erro excecution but as a normal query it works
what should i do?
thank you for your help


--calculation of number of devices
update f
set
numberofdevices = Orders_DailyVolume
from AMPPU_Fehlerdaten_CalcVals a
join AMPPU_Alle_Fehlteile f
on f.Offline_date = a.Offline_date
where f.Offline_date is not null;
---------------------------------------------------
--calculation of repairtime per day
UPDATE AMPPU_Alle_Fehlteile SET
summe_Reparaturzeit_Daily = B.SUpdateColSource
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;
--------------------------------------------------------
--calculation of repairtime per day (exclNA)
UPDATE AMPPU_Alle_Fehlteile SET
[summe_Reparaturzeit_Daily(excl)] = E.SUpdateColSource
FROM AMPPU_Alle_Fehlteile C
INNER JOIN (select On_Line_Repair,Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' group by Offline_date,On_Line_Repair
) E ON C.Offline_date=E.Offline_date
where C.On_Line_Repair='NO';
--------------------------------------------------------
--calculation of ARTPU per day
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU_daily = CASE WHEN numberofdevices = 0 THEN 0 ELSE (summe_Reparaturzeit_Daily/numberofdevices) END;
-----------------------------------------------------------
--calculation of ARTPU per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[ARTPU_daily(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([summe_Reparaturzeit_Daily(excl)]/numberofdevices) END;
----------------------------------------------------------
--calculation of repairtime for per Bereich per day
UPDATE AMPPU_Alle_Fehlteile SET
summe_Reparaturzeit_B_Daily = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,bereich,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile group by Offline_date,Bereich) B
ON A.Offline_date = B.Offline_date and
ISNULL(A.Bereich,'Null') = ISNULL(B.bereich,'Null');
------------------------------------------------------------
--calculation of repairtime for per Bereich per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[summe_Reparaturzeit_B_Daily(excl)] = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select On_Line_Repair,Offline_date,bereich,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' group by Offline_date,Bereich,On_Line_Repair) B
ON A.Offline_date = B.Offline_date and
ISNULL(A.Bereich,'Null') = ISNULL(B.bereich,'Null')
where A.On_Line_Repair='NO';
-----------------------------------------------------------
--calculation of ARTPU for Bereich
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU_daily_Bereich = CASE WHEN numberofdevices = 0 THEN 0 ELSE (summe_Reparaturzeit_B_Daily/numberofdevices) END;
------------------------------------------------------------
--calculation of ARTPU for Bereich(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[ARTPU_daily_Bereich(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([summe_Reparaturzeit_B_Daily(excl)]/numberofdevices) END;
-----------------------------------------------------------
--calculation of average from ARTPU(Bereich) per month
UPDATE AMPPU_Alle_Fehlteile
SET summe_Reparaturzeit_B_Monat =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG(ARTPU_daily_Bereich) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
---------------------------------------------------------------
--calculation of average from ARTPU(Bereich) per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [summe_Reparaturzeit_B_Monat(excl)] =
(SELECT AVG(avg_bereich)
FROM (SELECT Offline_date, AVG([ARTPU_daily_Bereich(excl)]) AS avg_bereich
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
---------------------------------------------------------------
--sum of summe_Reparaturzeit_B_Monat per month
UPDATE AMPPU_Alle_Fehlteile
set summe_Total_Monat =
(SELECT ISNULL(SUM(summe_Reparaturzeit_B_Monat),0)
FROM (SELECT DISTINCT summe_Reparaturzeit_B_Monat
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)) a);
--------------------------------------------------------------------------------------
--sum of summe_Reparaturzeit_B_Monat per month(excl)
UPDATE AMPPU_Alle_Fehlteile
set [summe_Total_Monat(excl)] =
(SELECT ISNULL(SUM([summe_Reparaturzeit_B_Monat(excl)]),0)
FROM (SELECT DISTINCT [summe_Reparaturzeit_B_Monat(excl)]
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)) a);
--------------------------------------------------------------------------------------
--calculation of repairtime for per Teamleader per day
UPDATE AMPPU_Alle_Fehlteile SET
Reparaturzeit_Teamleader = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Bereich,Offline_date,Teamleader,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where Bereich = 'Intern' group by Offline_date,Bereich,Teamleader) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Teamleader=B.Teamleader;
----------------------------------------------------------------------------------------
--calculation of repairtime for per Teamleader per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[Reparaturzeit_Teamleader(excl)] = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select On_Line_Repair,Offline_date,bereich,Teamleader,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' and Bereich = 'Intern' group by Offline_date,Bereich,On_Line_Repair,Teamleader) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Teamleader=B.Teamleader
where A.On_Line_Repair='NO';
---------------------------------------------------------------------------------------------
--calculation of ARTPU for Teamleader
UPDATE AMPPU_Alle_Fehlteile SET
calcval_Teamleader = CASE WHEN numberofdevices = 0 THEN 0 ELSE (Reparaturzeit_Teamleader/numberofdevices) END;
----------------------------------------------------------------------------------------------
--calculation of ARTPU for Teamleader(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[calcval_Teamleader(excl)] = CASE WHEN numberofdevices = 0 THEN 0 ELSE ([Reparaturzeit_Teamleader(excl)]/numberofdevices) END;
-----------------------------------------------------------------------------------------------
--calculation of average from ARTPU(Teamleader) per month
UPDATE AMPPU_Alle_Fehlteile
SET calcval_T_Monat =
(SELECT AVG(avg_Teamleader)
FROM (SELECT Offline_date, AVG(calcval_Teamleader) AS avg_Teamleader
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Teamleader,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
------------------------------------------------------------------------------------------------------
--calculation of average from ARTPUTeamleader) per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [calcval_T_Monat(excl)] =
(SELECT AVG(avg_Teamleader)
FROM (SELECT Offline_date, AVG([calcval_Teamleader(excl)]) AS avg_Teamleader
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Teamleader,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
---------------------------------------------------------------------------------------------------------------
--calculation of repairtime for per Disponent per day
UPDATE AMPPU_Alle_Fehlteile SET
Reparaturzeit_Disponent = B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Bereich,Offline_date,Disponent,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where Bereich = 'Extern' group by Offline_date,Bereich,Disponent) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Disponent=B.Disponent;
------------------------------------------------------------------------------------------------------------------------
--calculation of repairtime for per Disponent per day(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[Reparaturzeit_Disponent(excl)]= B.SUpdateColSource1
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select On_Line_Repair,Offline_date,bereich,Disponent,SUM(Reparatur_hr)*60 SUpdateColSource1 FROM
AMPPU_Alle_Fehlteile where On_Line_Repair = 'No' and Bereich = 'Extern' group by Offline_date,Bereich,On_Line_Repair,Disponent) B
ON A.Offline_date = B.Offline_date and
A.Bereich = B.Bereich and
A.Disponent=B.Disponent
where A.On_Line_Repair='NO';
---------------------------------------------------------------------------------------------------------------------------
--calculation of ARTPU for Disponent
UPDATE AMPPU_Alle_Fehlteile SET
calcval_Disponent = CASE WHEN numberofdevices = 0 THEN 0 ELSE (Reparaturzeit_Disponent/numberofdevices) END;
----------------------------------------------------------------------------------------------
--calculation of ARTPU for Disponent(excl)
UPDATE AMPPU_Alle_Fehlteile SET
[calcval_Disponent(excl)]= CASE WHEN numberofdevices = 0 THEN 0 ELSE ([Reparaturzeit_Disponent(excl)]/numberofdevices) END;
-----------------------------------------------------------------------------------------------
--calculation of average from ARTPU(Disponent) per month
UPDATE AMPPU_Alle_Fehlteile
SET calcval_D_Monat =
(SELECT AVG(avg_Disponent)
FROM (SELECT Offline_date, AVG(calcval_Disponent ) AS avg_Disponent
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Disponent,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);
------------------------------------------------------------------------------------------------------
--calculation of average from ARTPUDisponent per month(excl)
UPDATE AMPPU_Alle_Fehlteile
SET [calcval_D_Monat(excl)]=
(SELECT AVG(avg_Disponent)
FROM (SELECT Offline_date, AVG([calcval_Disponent(excl)]) AS avg_Disponent
FROM AMPPU_Alle_Fehlteile b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Alle_Fehlteile.Disponent,'Null')
GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a);























quote:
Originally posted by MichaelJSQL


run the following

use master
Select 'KILL ' + Cast(spid as varchar(10)) from Master.dbo.sysprocesses where dbid = DB_ID('YourDatabaseName')

The results of this query - you will need to copy and place in a sql window and run. It will kill active connections to your database. you cannot restore a database that has connections.





[/quote]
Go to Top of Page

mana
Yak Posting Veteran

88 Posts

Posted - 08/13/2014 :  08:18:29  Show Profile  Reply with Quote

















Hello i wrote this code but it has still error:


UPDATE AMPPU_Alle_Fehlteile
SET sum_devices_B_Monthly =
(SELECT sum(numberofdevices)
FROM (select Offline_date ,numberofdevices ,bereich from AMPPU_Alle_Fehlteile
group by Offline_date,Bereich,numberofdevices b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date,numberofdevices,bereich ) a);


can you help me please?
i WANT THE sum of number of devices for each bereich in each month.







quote:
Originally posted by mana











Hello

I have a problem, I have a table in sql server and i think i changed it and now it is empty. I want to restore the database ro a time but when i try to do it i have the below message:

can you help me please?
ITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'CATSVA01SQCP01V'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4266.0+((KJ_SP2_QFE-CU).120926-1658+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:


System.Data.SqlClient.SqlError: RESTORE can not process because it is used by this session the reporting database. It is recommended to use the master database for this operation. (Microsoft.SqlServer.Smo)

please help me.
thank you









quote:
Originally posted by MichaelJSQL

I would first try it as a select and make sure the result is appropriate for the precision and scale. ALso check what you have set for precision and scale in the sql server table



Go to Top of Page

mana
Yak Posting Veteran

88 Posts

Posted - 08/13/2014 :  09:46:05  Show Profile  Reply with Quote



hello
i have the following query and i want to add the condition
online-repaire=no

but i don't know where to add it. thank you for your help

UPDATE AMPPU_Alle_Fehlteile SET
[sum_repairtime_B_Monthly(excl)] =
( select sum(SUpdateColSource1)
FROM (select On_Line_Repair,summe_Reparaturzeit_B_Daily as SUpdateColSource1,Bereich from AMPPU_Alle_Fehlteile
B
where MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date) and
YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date) and
ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
group by Offline_date,Bereich,summe_Reparaturzeit_B_Daily ) a );











quote:
Originally posted by mana


















Hello i wrote this code but it has still error:


UPDATE AMPPU_Alle_Fehlteile
SET sum_devices_B_Monthly =
(SELECT sum(numberofdevices)
FROM (select Offline_date ,numberofdevices ,bereich from AMPPU_Alle_Fehlteile
group by Offline_date,Bereich,numberofdevices b
WHERE MONTH(Offline_date) = MONTH(AMPPU_Alle_Fehlteile.Offline_date)
AND YEAR(Offline_date) = YEAR(AMPPU_Alle_Fehlteile.Offline_date)
AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Alle_Fehlteile.bereich,'Null')
GROUP BY Offline_date,numberofdevices,bereich ) a);


can you help me please?
i WANT THE sum of number of devices for each bereich in each month.







quote:
Originally posted by mana











Hello

I have a problem, I have a table in sql server and i think i changed it and now it is empty. I want to restore the database ro a time but when i try to do it i have the below message:

can you help me please?
ITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'CATSVA01SQCP01V'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4266.0+((KJ_SP2_QFE-CU).120926-1658+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:


System.Data.SqlClient.SqlError: RESTORE can not process because it is used by this session the reporting database. It is recommended to use the master database for this operation. (Microsoft.SqlServer.Smo)

please help me.
thank you









quote:
Originally posted by MichaelJSQL

I would first try it as a select and make sure the result is appropriate for the precision and scale. ALso check what you have set for precision and scale in the sql server table





Go to Top of Page

mana
Yak Posting Veteran

88 Posts

Posted - 08/18/2014 :  08:34:28  Show Profile  Reply with Quote



Hello

i Hva ethis error message in access when i want to sort a field. how can i solve this problem?

a column was mehrmls specified in the order by list.
columns in the order by list must be unique


I have a grid in access and when i want to sort a field as A to Z or Zto A i have this problem.Thank you for your help









quote:
Originally posted by mana











Hello

I have a problem, I have a table in sql server and i think i changed it and now it is empty. I want to restore the database ro a time but when i try to do it i have the below message:

can you help me please?
ITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'CATSVA01SQCP01V'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4266.0+((KJ_SP2_QFE-CU).120926-1658+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:


System.Data.SqlClient.SqlError: RESTORE can not process because it is used by this session the reporting database. It is recommended to use the master database for this operation. (Microsoft.SqlServer.Smo)

please help me.
thank you









quote:
Originally posted by MichaelJSQL

I would first try it as a select and make sure the result is appropriate for the precision and scale. ALso check what you have set for precision and scale in the sql server table



Go to Top of Page

mana
Yak Posting Veteran

88 Posts

Posted - 09/14/2014 :  04:22:00  Show Profile  Reply with Quote
hello

i have the following code but it has no result
i have for each teil nummer in dbo.bewegungen several Von addresse in dbo.v_ME_Montage_Adressen but i want that just the first von address that will be found will be chosen for each teil nummer. can you help me please? am i right or shoud i write it in another way?
thank you

update dbo.Bewegungen_Inventur_Korrekturen
set Von_Addresse = F.Von_Adresse
FROM dbo.Bewegungen_Inventur_Korrekturen M
inner join (select top 1 Von_Adresse ,Teil_Nummer
from dbo.v_ME_Montage_Adressen order by Teil_Nummer ) F
ON
F.Teil_Nummer = M.Teil_Nummer;




quote:
Originally posted by mana











Hello

I have a problem, I have a table in sql server and i think i changed it and now it is empty. I want to restore the database ro a time but when i try to do it i have the below message:

can you help me please?
ITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'CATSVA01SQCP01V'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4266.0+((KJ_SP2_QFE-CU).120926-1658+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:


System.Data.SqlClient.SqlError: RESTORE can not process because it is used by this session the reporting database. It is recommended to use the master database for this operation. (Microsoft.SqlServer.Smo)

please help me.
thank you









quote:
Originally posted by MichaelJSQL

I would first try it as a select and make sure the result is appropriate for the precision and scale. ALso check what you have set for precision and scale in the sql server table



Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.23 seconds. Powered By: Snitz Forums 2000