USE [OblakLive3]
GO
/****** Object:  StoredProcedure [vm].[VirtualMachineDetailsGetByInstanceID]    Script Date: 11/21/2019 6:37:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [vm].[VirtualMachineDetailsGetByInstanceID]
	@InstanceID UNIQUEIDENTIFIER
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @DateLastModified DATETIME
	DECLARE @DateFirstCreated DATETIME
	SELECT
		@DateLastModified = MAX(CreateDate),
		@DateFirstCreated = MIN(CreateDate)
	FROM [vm].[VirtualMachineDetails] vmd
	WHERE vmd.InstanceID = @InstanceID AND vmd.CreateDate IS NOT NULL

	DECLARE @DateFirstRun DATETIME
	DECLARE @DateLastRun DATETIME
	SELECT
		@DateFirstRun = MIN(vmdi.StartDate),
		@DateLastRun = MAX(vmdi.StartDate)
	FROM [vm].[VirtualMachineDetails] vmd
	INNER JOIN [vm].[VirtualMachineDetailInfo] vmdi on vmdi.VMDetailsID = vmd.VMDetailsID
	WHERE vmd.InstanceID = @InstanceID

	SELECT
		 vmd.[VMDetailsID]
		,vmd.[InstanceID]
		,vmd.[CreateDate]
		,vmd.[DeleteDate]
		,vmd.[ChargedItemID]
		,vmd.[Quantity]
		,vmd.[IsUserDefined]
		,vmd.[VMMID]
		,ci.[ChargeTypeID]
		,ci.[Label] AS [ChargeTypeLabel]
		,@DateFirstCreated AS [DateFirstCreated]
		,@DateLastModified AS [DateLastModified]
		,@DateFirstRun AS [DateFirstRun]
		,@DateLastRun AS [DateLastRun]
		,cit.ParentTypeID AS ChargeItemTypeLevel2ParentID
		,cit2.ParentTypeID AS ChargeItemTypeLevel3ParentID
	FROM [vm].[VirtualMachineDetails] vmd
	INNER JOIN [lookup].[ChargeItem] ci ON ci.ItemID = vmd.ChargedItemID
	INNER JOIN [lookup].[ChargeItemType] cit ON ci.ChargeTypeID = cit.ChargeTypeID
	LEFT JOIN [lookup].[ChargeItemType] cit2 ON cit.ParentTypeID = cit2.ChargeTypeID
	WHERE vmd.[InstanceID] = @InstanceID AND vmd.[DeleteDate] IS NULL

END