use MeineReparaturDatenbank
GO
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SCALARE FUNKTION, die den Zähler zurückgibt
Übergabeparameter ist wop.closedate
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
USE MeineReparaturdatenbank
GO
IF OBJECT_ID (N'schema.udf_Zaehler_ermitteln') IS NOT NULL
DROP FUNCTION schema.udf_Zaehler_ermitteln
GO
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE FUNCTION schema.udf_Zaehler_ermitteln
(
@udf_wop_closedate date
)
RETURNS int
WITH EXECUTE AS ????????????????
AS
BEGIN
RETURN (SELECT COUNT(*)
FROM WOP
INNER JOIN WO ON WOP.WONUM = WO.WONUM
WHERE WOP.CLOSEDATE > 1900-01-01 AND WO.WONUM = WOP.WONUM)
END
GO
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- VIEW erstellen
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IF EXISTS(SELECT name FROM sys.views WHERE name = N'vTOP10WONUM')
DROP VIEW vTOP10WONUM
GO
CREATE VIEW schema.vTOP10WONUM
AS
SELECT TOP 10 T.WONUM as 'WonumReferenz'
,COUNT(*) as 'MyTop10Count'
FROM WOP AS T
WHERE T.WONUM = WOP.WONUM
GROUP BY T.WONUM
ORDER BY MyCount DESC )
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- jetzt Dein SELECT
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT WOP.CLOSEDATE AS Arbeitsdatum
,WOP.EQNUM AS Maschine
,WOEQLIST.SERIALNUM AS MaNr
,WOMETER.METERREADING AS BetrStd
,WOP.ITEMNUM AS Sachnmummer
,WOP.DESCRIPTION AS Bezeichnung
,(WOP.TOTALUNITCOST / WOP.QTYUSED) AS Einzelpreis
,WOP.QTYUSED AS Ausg
,WO.TASKDESC AS Bemerkung
,WOE.EMPCODE AS ArbnNr
,WO.WONUM AS Referenz
,schema.udf_Zaehler_ermitteln(WOP.CloseDate) AS Zaehler
FROM WO
INNER JOIN WOP ON WO.WONUM = WOP.WONUM
LEFT JOIN WOEQLIST ON WO.WONUM = WOEQLIST.WONUM
LEFT JOIN WOMETER ON WO.WONUM = WOMETER.WONUM
LEFT JOIN WOE ON WO.WONUM = WOE.WONUM
WHERE WOP.WONUM IN (SELECT mytop10Count
FROM vTOP10WONUM
WHERE vTOP10WONUM.WonumReferenz = WOP.WONUM
ORDER BY MyCount DESC )
AND WOP.CLOSEDATE > 1900-01-01
ORDER BY Zaehler
, WOP.CLOSEDATE DESC