Could you any one suggest me, what is the alternate approach to write the below stored procedure in a simple way
你有没有人建议我,以简单的方式编写下面的存储过程的替代方法是什么
If you observ in all insert statements WHERE condition is changing
Please see below where conditions
如果你在所有插入语句中观察WHERE条件正在改变请参见下面的条件
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,1,@lCurrentDateTime )
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,2,@lCurrentDateTime )
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,3,@lCurrentDateTime )
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,4,@lCurrentDateTime )
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,5,@lCurrentDateTime )
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,6,@lCurrentDateTime )
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,7,@lCurrentDateTime )
Please find the below Stored procedure which i have to make simple
请找到以下存储过程,我必须简单
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------------------------------------------------
END MAINTENANCE WRAPPER.
===============================================================================*/
CREATE PROCEDURE [dbo].[FamilyDates]
(
@pErrorMessage VARCHAR(500) = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
DECLARE @lCurrentDateTime DATETIME
SET @lCurrentDateTime = CONVERT(VARCHAR(10), GETDATE(), 101)
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextTwoDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextTwoDays
SELECT ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextTwoDays'
INTO #tempSlotsAvailabityForNextTwoDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,1,@lCurrentDateTime )
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextThreeDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextThreeDays
SELECT ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextThreeDays'
INTO #tempSlotsAvailabityForNextThreeDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,2,@lCurrentDateTime )
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextFourDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextFourDays
SELECT ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextFourDays'
INTO #tempSlotsAvailabityForNextFourDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,3,@lCurrentDateTime )
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextFiveDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextFiveDays
SELECT ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextFiveDays'
INTO #tempSlotsAvailabityForNextFiveDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,4,@lCurrentDateTime )
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextSixDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextSixDays
SELECT ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextSixDays'
INTO #tempSlotsAvailabityForNextSixDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,5,@lCurrentDateTime )
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextSevenDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextSevenDays
SELECT ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextSevenDays'
INTO #tempSlotsAvailabityForNextSevenDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,6,@lCurrentDateTime )
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForEightDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForEightDays
SELECT ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextEightDays'
INTO #tempSlotsAvailabityForEightDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,7,@lCurrentDateTime )
SELECT SATwoWeeks.EquipmentCode, SATwoWeeks.TestTypeCode,
ISNULL(SAThreeDays.OpenSlotsForNextThreeDays, '0') 'OpenSlots ForNextTwoDays',
ISNULL(SAFourDays.OpenSlotsForNextFourDays, '0') 'OpenSlots ForNextThreeDays',
ISNULL(SAFiveDays.OpenSlotsForNextFiveDays,'0') 'OpenSlots ForNextFourDays',
ISNULL(SASixDays.OpenSlotsForNextSixDays,'0') 'OpenSlots ForNextFiveDays',
ISNULL(SASevenDays.OpenSlotsForNextSevenDays, '0') 'OpenSlots ForNextSixDays',
ISNULL(SAEightDays.OpenSlotsForNextEightDays,'0') 'OpenSlots ForNextSevenDays',
ISNULL(SANineDays.OpenSlotsForNextEightDays,'0') 'OpenSlots ForNextEightDays'
FROM #tempSlotsAvailabityForNextTwoDays SATwoWeeks
LEFT JOIN #tempSlotsAvailabityForNextThreeDays SAThreeDays ON (SAThreeDays.ClientId = SATwoWeeks.ClientId )
LEFT JOIN #tempSlotsAvailabityForNextFourDays SAFourDays ON (SAFourDays.ClientId = SATwoWeeks.ClientId )
LEFT JOIN #tempSlotsAvailabityForNextFiveDays SAFiveDays ON (SAFiveDays.ClientId = SATwoWeeks.ClientId )
LEFT JOIN #tempSlotsAvailabityForNextSixDays SASixDays ON (SASixDays.ClientId = SATwoWeeks.ClientId )
LEFT JOIN #tempSlotsAvailabityForNextSevenDays SASevenDays ON (SASevenDays.ClientId = SATwoWeeks.ClientId )
LEFT JOIN #tempSlotsAvailabityForEightDays SAEightDays ON (SAEightDays.ClientId = SATwoWeeks.ClientId )
END TRY
BEGIN CATCH
SET @pErrorMessage = CONVERT(VARCHAR(10),ERROR_NUMBER()) + ': ' + ERROR_MESSAGE()
END CATCH
END
GO
2 个解决方案
#1
2
You can try something like this:
你可以尝试这样的事情:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------------------------------------------------
END MAINTENANCE WRAPPER.
===============================================================================*/
CREATE PROCEDURE [dbo].[FamilyDates]
(
@pErrorMessage VARCHAR(500) = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
DECLARE @lCurrentDateTime DATETIME
SET @lCurrentDateTime = CONVERT(VARCHAR(10), GETDATE(), 101)
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextTwoDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextDays
SELECT ClientId,ClientName, OpenSlotsForNextDays,
DATEDIFF(day, @lCurrentDateTime, ChamberAvailaBilityDate) AS DayNumber
INTO #tempSlotsAvailabityForNextTwoDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate BETWEEN
DATEADD(DAY,1,@lCurrentDateTime )
AND DATEADD(DAY,8,@lCurrentDateTime )
SELECT DayNumber,
[1], [2], [3], [4], [5], [6], [7]
FROM (
SELECT DayNumber, OpenSlotsForNextDays
FROM #tempSlotsAvailabityForNextDays SANextDays) AS SourceTable
PIVOT
(
MIN(OpenSlotsForNextDays)
FOR DayNumber IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable
END TRY
BEGIN CATCH
SET @pErrorMessage = CONVERT(VARCHAR(10),ERROR_NUMBER()) + ': ' + ERROR_MESSAGE()
END CATCH
END
GO
(Note: I can't test it, but the general solution is a PIVOT and only one temp table)
(注意:我无法测试它,但一般的解决方案是一个PIVOT,只有一个临时表)
#2
1
Where to start....
从哪儿开始....
First to remove the time from a date use date functions, rather than varchar conversion, although in this case it is not that detrimental it is good practise:
首先从日期使用日期函数中删除时间,而不是varchar转换,尽管在这种情况下它不是有害的,这是好的做法:
DECLARE @lCurrentDateTime DATE = CAST(GETDATE() AS DATE)
Next you select SATwoWeeks.EquipmentCode
AND SATwoWeeks.TestTypeCode
, from #tempSlotsAvailabityForNextTwoDays SATwoWeeks
, yet when you create #tempSlotsAvailabityForNextTwoDays
neither of these columns are defined:
接下来,从#tempSlotsAvailabityForNextTwoDays SATwoWeeks中选择SATwoWeeks.EquipmentCode AND SATwoWeeks.TestTypeCode,但是当您创建#tempSlotsAvailabityForNextTwoDays时,这些列都没有定义:
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextTwoDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextTwoDays
SELECT ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextTwoDays'
INTO #tempSlotsAvailabityForNextTwoDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,1,@lCurrentDateTime )
It is difficult to infer what you are trying to do given that I have no idea where EquipmentCode
and TestTypeCode
come from, however I'd suggest scrapping using temp tables completely, as you are not reusing your data, if you are using them just to make the SQL more legible consider using Common Table Expressions. e.g.
鉴于我不知道EquipmentCode和TestTypeCode来自哪里,很难推断出你要做什么,但我建议完全使用临时表进行报废,因为你没有重复使用你的数据,如果你只是使用它们考虑使用公用表表达式,使SQL更易读。例如
;WITH NextTwoDays AS
( SELECT ClientId,ClientName, OpenSlotsForNextTwoDays AS OpenSlots
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY, 1, @lCurrentDateTime)
), NextThreeDays AS
( SELECT ClientId,ClientName, OpenSlotsForNextTwoDays AS OpenSlots
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY, 2, @lCurrentDateTime)
), NextFourDays AS
( ...
)
SELECT NextTwoDays.ClientID,
NextTwoDays.OpenSlots AS NextTwoDays,
COALESCE(NextThreeDays.OpenSlots, 0) AS NextThreeDays,
COALESCE(NextFourDays.OpenSlots, 0) AS NextFourDays
FROM NextTwoDays
LEFT JOIN NextThreeDays
ON NextTwoDays.ClientID = NextThreeDays.ClientID
LEFT JOIN NextFourDays
ON NextTwoDays.ClientID = NextFourDays.ClientID
Alternatively, if Table1
is large enough to warrant extracting the data before joining, consider using a single temporary table:
或者,如果Table1足够大以保证在加入之前提取数据,请考虑使用单个临时表:
CREATE TABLE #Temp
( DayNumber INT NOT NULL,
ClientID INT NOT NULL,
ClientName VARCHAR(255) NOT NULL,
OpenSlots INT NOT NULL
)
INSERT INTO #Temp
SELECT DATEDIFF(DAY, @lCurrentDateTime, TCS.ChamberAvailaBilityDate),
ClientID,
ClientName,
OpenSlotsForNextTwoDays
FROM Table1
WHERE TCS.ChamberAvailaBilityDate BETWEEN DATEADD(DAY, 1, @lCurrentDateTime) AND DATEADD(DAY, 7, @lCurrentDateTime)
SELECT two.ClientID,
two.OpenSlots AS NextTwoDays,
COALESCE(three.OpenSlots, 0) AS NextThreeDays,
COALESCE(four.OpenSlots, 0) AS NextFourDays
FROM #Temp two
LEFT JOIN #Temp three
ON two.ClientID = three.ClientID
AND three.DayNumber = 2
LEFT JOIN #Temp four
ON two.ClientID = four.ClientID
AND four.DayNumber = 2
WHERE two.dayNumber = 1
Or if you are looking to aggregate your data by day you could use 'PIVOT'
或者,如果您希望白天汇总数据,可以使用'PIVOT'
;WITH Data AS
( SELECT DATEDIFF(DAY, @lCurrentDateTime, TCS.ChamberAvailaBilityDate) AS DayNum,
ClientID,
ClientName,
OpenSlotsForNextTwoDays
FROM Table1
WHERE TCS.ChamberAvailaBilityDate BETWEEN DATEADD(DAY, 1, @lCurrentDateTime) AND DATEADD(DAY, 7, @lCurrentDateTime)
)
SELECT ClientID,
[1] AS NextTwoDays,
[2] AS NextThreeDays,
[3] AS NextFourDays,
[4] AS NextFiveDays,
[5] AS NextSixDays,
[6] AS NextSevenDays,
[7] AS NextEightDays
FROM Data
PIVOT
( SUM(OpenSlots)
FOR DayNum IN ([1], [2], [3], [4], [5], [6], [7])
) pvt
Finally, have you considered records that may be in the NextThreeDays
but not in the Nexttwodays
, these will never appear since NextTwoDays
is the table you are selecting from. You may need to create another table that contains records for all days, or use FULL JOIN
s
最后,您是否考虑过可能在NextThreeDays中而不是在Nexttwodays中的记录,这些记录将永远不会出现,因为NextTwoDays是您选择的表。您可能需要创建另一个包含所有日期记录的表,或使用FULL JOIN
Ultimately I think more information is required to fully answer your question.
最后,我认为需要更多信息才能完全回答您的问题。
#1
2
You can try something like this:
你可以尝试这样的事情:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------------------------------------------------
END MAINTENANCE WRAPPER.
===============================================================================*/
CREATE PROCEDURE [dbo].[FamilyDates]
(
@pErrorMessage VARCHAR(500) = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
DECLARE @lCurrentDateTime DATETIME
SET @lCurrentDateTime = CONVERT(VARCHAR(10), GETDATE(), 101)
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextTwoDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextDays
SELECT ClientId,ClientName, OpenSlotsForNextDays,
DATEDIFF(day, @lCurrentDateTime, ChamberAvailaBilityDate) AS DayNumber
INTO #tempSlotsAvailabityForNextTwoDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate BETWEEN
DATEADD(DAY,1,@lCurrentDateTime )
AND DATEADD(DAY,8,@lCurrentDateTime )
SELECT DayNumber,
[1], [2], [3], [4], [5], [6], [7]
FROM (
SELECT DayNumber, OpenSlotsForNextDays
FROM #tempSlotsAvailabityForNextDays SANextDays) AS SourceTable
PIVOT
(
MIN(OpenSlotsForNextDays)
FOR DayNumber IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable
END TRY
BEGIN CATCH
SET @pErrorMessage = CONVERT(VARCHAR(10),ERROR_NUMBER()) + ': ' + ERROR_MESSAGE()
END CATCH
END
GO
(Note: I can't test it, but the general solution is a PIVOT and only one temp table)
(注意:我无法测试它,但一般的解决方案是一个PIVOT,只有一个临时表)
#2
1
Where to start....
从哪儿开始....
First to remove the time from a date use date functions, rather than varchar conversion, although in this case it is not that detrimental it is good practise:
首先从日期使用日期函数中删除时间,而不是varchar转换,尽管在这种情况下它不是有害的,这是好的做法:
DECLARE @lCurrentDateTime DATE = CAST(GETDATE() AS DATE)
Next you select SATwoWeeks.EquipmentCode
AND SATwoWeeks.TestTypeCode
, from #tempSlotsAvailabityForNextTwoDays SATwoWeeks
, yet when you create #tempSlotsAvailabityForNextTwoDays
neither of these columns are defined:
接下来,从#tempSlotsAvailabityForNextTwoDays SATwoWeeks中选择SATwoWeeks.EquipmentCode AND SATwoWeeks.TestTypeCode,但是当您创建#tempSlotsAvailabityForNextTwoDays时,这些列都没有定义:
IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextTwoDays') IS NOT NULL
DROP TABLE #tempSlotsAvailabityForNextTwoDays
SELECT ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextTwoDays'
INTO #tempSlotsAvailabityForNextTwoDays
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY,1,@lCurrentDateTime )
It is difficult to infer what you are trying to do given that I have no idea where EquipmentCode
and TestTypeCode
come from, however I'd suggest scrapping using temp tables completely, as you are not reusing your data, if you are using them just to make the SQL more legible consider using Common Table Expressions. e.g.
鉴于我不知道EquipmentCode和TestTypeCode来自哪里,很难推断出你要做什么,但我建议完全使用临时表进行报废,因为你没有重复使用你的数据,如果你只是使用它们考虑使用公用表表达式,使SQL更易读。例如
;WITH NextTwoDays AS
( SELECT ClientId,ClientName, OpenSlotsForNextTwoDays AS OpenSlots
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY, 1, @lCurrentDateTime)
), NextThreeDays AS
( SELECT ClientId,ClientName, OpenSlotsForNextTwoDays AS OpenSlots
FROM Table1 TCS
WHERE TCS.ChamberAvailaBilityDate = DATEADD(DAY, 2, @lCurrentDateTime)
), NextFourDays AS
( ...
)
SELECT NextTwoDays.ClientID,
NextTwoDays.OpenSlots AS NextTwoDays,
COALESCE(NextThreeDays.OpenSlots, 0) AS NextThreeDays,
COALESCE(NextFourDays.OpenSlots, 0) AS NextFourDays
FROM NextTwoDays
LEFT JOIN NextThreeDays
ON NextTwoDays.ClientID = NextThreeDays.ClientID
LEFT JOIN NextFourDays
ON NextTwoDays.ClientID = NextFourDays.ClientID
Alternatively, if Table1
is large enough to warrant extracting the data before joining, consider using a single temporary table:
或者,如果Table1足够大以保证在加入之前提取数据,请考虑使用单个临时表:
CREATE TABLE #Temp
( DayNumber INT NOT NULL,
ClientID INT NOT NULL,
ClientName VARCHAR(255) NOT NULL,
OpenSlots INT NOT NULL
)
INSERT INTO #Temp
SELECT DATEDIFF(DAY, @lCurrentDateTime, TCS.ChamberAvailaBilityDate),
ClientID,
ClientName,
OpenSlotsForNextTwoDays
FROM Table1
WHERE TCS.ChamberAvailaBilityDate BETWEEN DATEADD(DAY, 1, @lCurrentDateTime) AND DATEADD(DAY, 7, @lCurrentDateTime)
SELECT two.ClientID,
two.OpenSlots AS NextTwoDays,
COALESCE(three.OpenSlots, 0) AS NextThreeDays,
COALESCE(four.OpenSlots, 0) AS NextFourDays
FROM #Temp two
LEFT JOIN #Temp three
ON two.ClientID = three.ClientID
AND three.DayNumber = 2
LEFT JOIN #Temp four
ON two.ClientID = four.ClientID
AND four.DayNumber = 2
WHERE two.dayNumber = 1
Or if you are looking to aggregate your data by day you could use 'PIVOT'
或者,如果您希望白天汇总数据,可以使用'PIVOT'
;WITH Data AS
( SELECT DATEDIFF(DAY, @lCurrentDateTime, TCS.ChamberAvailaBilityDate) AS DayNum,
ClientID,
ClientName,
OpenSlotsForNextTwoDays
FROM Table1
WHERE TCS.ChamberAvailaBilityDate BETWEEN DATEADD(DAY, 1, @lCurrentDateTime) AND DATEADD(DAY, 7, @lCurrentDateTime)
)
SELECT ClientID,
[1] AS NextTwoDays,
[2] AS NextThreeDays,
[3] AS NextFourDays,
[4] AS NextFiveDays,
[5] AS NextSixDays,
[6] AS NextSevenDays,
[7] AS NextEightDays
FROM Data
PIVOT
( SUM(OpenSlots)
FOR DayNum IN ([1], [2], [3], [4], [5], [6], [7])
) pvt
Finally, have you considered records that may be in the NextThreeDays
but not in the Nexttwodays
, these will never appear since NextTwoDays
is the table you are selecting from. You may need to create another table that contains records for all days, or use FULL JOIN
s
最后,您是否考虑过可能在NextThreeDays中而不是在Nexttwodays中的记录,这些记录将永远不会出现,因为NextTwoDays是您选择的表。您可能需要创建另一个包含所有日期记录的表,或使用FULL JOIN
Ultimately I think more information is required to fully answer your question.
最后,我认为需要更多信息才能完全回答您的问题。