Hi
Vongrut, I so agree with you on the torture on this one.
The m/d is every day of the year with no date attached. the data in the table has what the equipment did for that day and the table which has the dreded m/d field has what the targets were for that day.
this is the stored proc I am starting with.
CREATE PROCEDURE dbo.sp_Equipday
@Equip varchar(10)
, @PeriodType varchar(10)
, @PeriodStart smalldatetime
, @PeriodEnd smalldatetime
AS
SET DATEFIRST 1
-- if the @PeriodStart or @PeriodEnd is null, then change these values to the default values
-- if the @PeriodType is null, then change this value to the default value (Month)
-- if the @Equip is null, change this value to All
--SET @EndDate = @Month +'-01-'+ @Year
IF @Equip IS NULL
BEGIN SET @Equip = 'ALL' END
IF @PeriodType IS NULL
BEGIN SET @PeriodType = 'MONTH' END
IF @PeriodEnd IS NULL
BEGIN
IF UPPER(@PeriodType) = 'MONTH'
BEGIN
SELECT @PeriodEnd = CAST(MONTH(GETDATE())AS varchar(2))+'/01/'+CAST(YEAR(GETDATE()) AS varchar(4))
SELECT @PeriodEnd = DATEADD(Day,-1,@PeriodEnd)
END
IF UPPER(@PeriodType) = 'YEAR'
BEGIN
SELECT @PeriodEnd = '01/01/'+CAST(YEAR(GETDATE()) AS varchar(4))
SELECT @PeriodEnd = DATEADD(Day,-1,@PeriodEnd)
END
IF UPPER(@PeriodType) IN ('WEEK','DAY')
BEGIN
SELECT @PeriodEnd = GETDATE()
SELECT @PeriodEnd = DATEADD(Day,-1,@PeriodEnd)
END
END
IF @PeriodStart IS NULL OR @PeriodStart >= @PeriodEnd
BEGIN
IF UPPER(@PeriodType) = 'MONTH'
BEGIN
SELECT @PeriodStart = DATEADD(Month,-6,@PeriodEnd)
SELECT @PeriodStart = DATEADD(Day,-1,@PeriodStart)
END
IF UPPER(@PeriodType) = 'YEAR'
BEGIN
SELECT @PeriodStart = '01/01/'+CAST(YEAR(GETDATE()) AS varchar(4))
SELECT @PeriodStart = DATEADD(year,-5,@PeriodStart)
END
IF UPPER(@PeriodType) = 'WEEK'
BEGIN
SELECT @PeriodStart = DATEADD(Day,-34,@PeriodEnd)
END
IF UPPER(@PeriodType) = 'DAY'
BEGIN
SELECT @PeriodStart = DATEADD(Day,-6,@PeriodEnd)
END
END
--SELECT @Equip, @PeriodType, @PeriodStart, @PeriodEnd
SELECT
A.tblEquipRealName AS Equip
, HD.str_StartTime AS StartTime
, HD.str_EndTime AS EndTime
, HD.num_RunHrs AS RunHours
, HD.date_EquipDay + ISNULL(CAST(I.str_IncidentTime AS smalldatetime),'1900-01-01') AS IncidentDateTime
, CASE WHEN UPPER(@PeriodType) = 'YEAR' THEN DATEPART(year,HD.date_EquipDay )
WHEN UPPER(@PeriodType) = 'MONTH' THEN DATEPART(month,HD.date_EquipDay )
WHEN UPPER(@PeriodType) = 'WEEK' THEN DATEPART(week,HD.date_EquipDay )
WHEN UPPER(@PeriodType) = 'DAY' THEN DATEPART(day,HD.date_EquipDay )
ELSE 0 END AS Period
, CASE WHEN UPPER(@PeriodType) = 'YEAR' THEN DATENAME(year,HD.date_EquipDay )
WHEN UPPER(@PeriodType) = 'MONTH' THEN SUBSTRING(DATENAME(m,HD.date_EquipDay ),1,3)
WHEN UPPER(@PeriodType) = 'WEEK'
THEN CONVERT(varchar(10),DATEADD(DAY,-DATEDIFF(DAY,'Jan 1, 2001',HD.date_EquipDay )%7,HD.date_EquipDay ),6)
WHEN UPPER(@PeriodType) = 'DAY' THEN CONVERT(varchar(10),HD.date_EquipDay,6)
ELSE DATENAME(m,HD.date_EquipDay) END AS PeriodName
, I.int_IncidentDuration AS IncidentDuration
, IT.str_EquipIncidentTypeDescrip AS IncidentType
, IC.ID_EquipIncidentCode AS IncidentCode
, IC.str_EquipIncidentDescrip AS IncidentDescription
, I.str_IncidentComments AS IncidentComments
FROM
tblEquipment AS A
JOIN tblEquipDay AS HD
ON A.tblEquipRealName = HD.ID_EquipID
JOIN tblEquipIncidents AS I
ON HD.ID_EquipID = I.ID_EquipID
AND HD.ID_EquipDay = I.ID_EquipDay
JOIN tblEquipIncidentCode AS IC
ON I.ID_IncidentCode = IC.ID_EquipIncidentCodeID
JOIN tblEquipIncidentType AS IT
ON IT.ID_EquipIncidentType = IC.ID_EquipIncidentType
WHERE
HD.date_EquipDay + ISNULL(CAST(I.str_IncidentTime AS smalldatetime),'1900-01-01') BETWEEN @PeriodStart-1 AND @PeriodEnd
AND (CASE WHEN UPPER(@Equip) = 'ALL' THEN A.tblEquipRealName ELSE UPPER(@Equip) END) = UPPER(A.tblEquipRealName)
ORDER BY
HD.date_EquipDay + ISNULL(CAST(I.str_IncidentTime AS smalldatetime),'1900-01-01')
GO
*************************************
and this other table Targetable with fields
targetdate - nvarchar 5 ie 1/2 for jan 2
targethours - float 8
now i need to match up HD.date_EquipDay which is a full datetime field with targetdate
as you can see that we prompt for which equipment, group of day, month or week. and we give the date start and ends.
I need to pull the targetdate for only that period.
if anyone has any guidance they could provide would be awesome. As I said, I am a bit newer at this depth.
thanks again all!!
Eileen McEvoy
Authorized Crystal Engineer
Crystal Reports Consultant and Trainer
emcevoy@crystalconsulting.ca