I have a Visual studio based stored procedure that generates a report for a monthly audit process. In the database being queried, all data for each month lives in its own individual table (Contacts_month_1, Contacts_month_2, etc.)
我有一个基于Visual Studio的存储过程,它为每月审计过程生成一个报告。在要查询的数据库中,每个月的所有数据都存在于其各自的表中(Contacts_month_1,Contacts_month_2等)
The SQL used in this report generation has some minor logic included, to allow it to work dynamically, rather than use hard coded dates. The problem arose at the start of January 2017, when I started receiving not just the results for the prior month, but additionally the prior year as well. To be specific, the audit report for December 2016 included data for both 12/2016 and 12/2015. Initially I thought it was a fluke of some kind based on the turn of the year, and we have not had this automated process during the turn as of yet. Unfortunately when I came in to the office today, inside the output file for January 2017, I also received the results for January 2016.
此报告生成中使用的SQL包含一些次要逻辑,以允许它动态工作,而不是使用硬编码日期。这个问题出现在2017年1月初,当时我开始收到的不仅是上个月的结果,还有上一年的结果。具体而言,2016年12月的审计报告包括12/2016和2015年12月的数据。最初我认为这是一种基于今年年初的某种侥幸,而且我们还没有在转弯时使用这种自动化过程。不幸的是,当我今天进入办公室时,在2017年1月的输出文件中,我也收到了2016年1月的结果。
I attempted to include a year check to the process, however I am still getting the same result output. Any ideas would be appreciated.
我试图在过程中包含一年检查,但是我仍然得到相同的结果输出。任何想法,将不胜感激。
Declare @GetMonth TinyInt
,@SessionTable varchar(50)
,@ContactTable varchar(50)
,@TableVersion varchar(2)
Declare @GetYear SmallInt
,@SessionTable_year varchar(50)
,@ContactTable_year varchar(50)
,@TableVersion_year varchar(4)
Set @GetMonth=MONTH(cast(GetDate() as Datetime))-1
Set @GetYear=YEAR(cast(GetDate() as Datetime))
If (@getmonth=0) Set @GetMonth=12 + (@GetYear-1)
Set @TableVersion=CAST(@getMonth as varchar(2))
Set @SessionTable='[CentralDWH].[dbo].[Sessions_month_' +@tableversion +']'
Set @ContactTable ='[CentralDWH].[dbo].[Contacts_month_' +@tableversion +']'
-- Select @GetMonth,@GetYear (DEBUGGING STATEMENT)
-- Select @SessionTable,@ContactTable (DEBUGGING STATEMENT)
Exec('SELECT [PBX_id] as AgentID
,[p22_value] as Skill
,''Athens'' as Location
,Convert(varchar(20),c.[local_start_time],120) as local_start_time
,convert(varchar(20),c.[local_end_time],120) as local_end_time
,U.[USER_NAME]
,call_id
FROM '+@SessionTable +' S
Inner join dbo.Users U on S.user_key=U.user_key
inner Join '+ @ContactTable+' C on S.contact_key=C.contact_key
Where is_screen > 0
And Unit_Num between 398003 and 398005
and P22_value is not null
and c.[local_start_time] > ' + @GetYear
+ ' order by local_start_time')
2 个解决方案
#1
0
As I understand, the @GetMonth variable is used for returning the previous month
据我了解,@ GetMonth变量用于返回上个月
Set @GetMonth = MONTH(CAST(GetDate() AS Datetime)) - 1
After a quick look after you procedure my first issue was this line of code:
经过快速查看程序后,我的第一个问题是这行代码:
IF (@getmonth = 0)
SET @GetMonth = 12 + (@GetYear - 1)
I don't understand why are you setting the @GetMonth variable to 12 + current year -1 and I assume this is the cause to the problem.
我不明白你为什么要将@GetMonth变量设置为12 +当前年-1,我认为这是导致问题的原因。
Did you want to get the 12th month of the previous year when the current month is 1 (January)? If yes then you can easily change the If block to
当前月份为1(1月)时,您想获得上一年的第12个月吗?如果是,那么您可以轻松地将If块更改为
If @GetMonth = 0
Begin
Set @GetMonth = 12
Set @GetYear = @GetYear - 1
End
Other issues:
-
It's recommended to keep the consistency of the names of the variables
@GetMonth,@getmonth, this will cause an error if the database collation is case sensitive.建议保持变量名称@GetMonth,@ getmonth的一致性,如果数据库排序规则区分大小写,这将导致错误。
-
@GetMonthis declared asTinyIntand this will cause an arithmetic overflow if you try to store the year@GetMonth被声明为TinyInt,如果你试图存储年份,这将导致算术溢出
I recommend testing the dynamic SQL statement that you are composing here with some hard coded values to check the results returned, you can use January and 2016 to check if the actual issue in your procedure or it's in your query.
我建议您使用一些硬编码值测试您在此处编写的动态SQL语句以检查返回的结果,您可以使用1月和2016年检查过程中的实际问题或查询中的实际问题。
Hope it helps
希望能帮助到你
#2
0
Thanks for your help, I figured out the root of the problem, and it was because i was not casting the GetYear as a varchar when trying to run the T-SQL statement. This in turn caused the variable to be completely ignored. I also cleaned up the query a little bit after realizing i was goofing up pretty hard.
感谢您的帮助,我找出了问题的根源,这是因为我在尝试运行T-SQL语句时没有将GetYear强制转换为varchar。这反过来导致变量被完全忽略。在意识到我非常努力地搞砸之后,我还清理了一下查询。
Below is the cleaned up functional query, so that it may help someone in the future:
下面是已清理的功能查询,以便将来可以帮助某人:
Declare @GetMonth SmallInt,
@SessionTable varchar(50),
@ContactTable varchar(50),
@TableVersion varchar(2),
@GetYear SmallInt,
@YearCheck varchar(4)
Set @GetMonth=MONTH(cast(GetDate() as Datetime))-1
Set @GetYear=YEAR(cast(GetDate() as Datetime))-1
If (@GetMonth=0)
Begin
Set @GetMonth =12
Set @GetYear =@GetYear - 1
End
Set @TableVersion=CAST(@GetMonth as varchar(2))
Set @SessionTable='[CentralDWH].[dbo].[Sessions_month_' +@tableversion +']'
Set @ContactTable ='[CentralDWH].[dbo].[Contacts_month_' +@tableversion +']'
Set @YearCheck=CAST(@GetYear as varchar(4))
--Select @GetMonth,@GetYear,@YearCheck (DEBUGGING STATEMENT)
-- Select @SessionTable,@ContactTable (DEBUGGING STATEMENT)
Exec('SELECT
[PBX_id] as AgentID,
[p22_value] as Skill,
''Athens'' as Location,
Convert(varchar(20),c.[local_start_time],120) as local_start_time,
convert(varchar(20),c.[local_end_time],120) as local_end_time,
U.[USER_NAME],
call_id
FROM '+@SessionTable +' S
Inner join dbo.Users U on S.user_key=U.user_key
inner Join '+ @ContactTable+' C on S.contact_key=C.contact_key
Where is_screen>0
And Unit_Num between 398003 and 398005
And P22_value is not null
And year(c.[local_start_time]) > '+@YearCheck+'
order by local_start_time')
Once I cleaned all this up and remembered to cast the year properly, everything fell into place.
一旦我清理了所有这些,并记得正确地投下一年,一切都落到了位置。
#1
0
As I understand, the @GetMonth variable is used for returning the previous month
据我了解,@ GetMonth变量用于返回上个月
Set @GetMonth = MONTH(CAST(GetDate() AS Datetime)) - 1
After a quick look after you procedure my first issue was this line of code:
经过快速查看程序后,我的第一个问题是这行代码:
IF (@getmonth = 0)
SET @GetMonth = 12 + (@GetYear - 1)
I don't understand why are you setting the @GetMonth variable to 12 + current year -1 and I assume this is the cause to the problem.
我不明白你为什么要将@GetMonth变量设置为12 +当前年-1,我认为这是导致问题的原因。
Did you want to get the 12th month of the previous year when the current month is 1 (January)? If yes then you can easily change the If block to
当前月份为1(1月)时,您想获得上一年的第12个月吗?如果是,那么您可以轻松地将If块更改为
If @GetMonth = 0
Begin
Set @GetMonth = 12
Set @GetYear = @GetYear - 1
End
Other issues:
-
It's recommended to keep the consistency of the names of the variables
@GetMonth,@getmonth, this will cause an error if the database collation is case sensitive.建议保持变量名称@GetMonth,@ getmonth的一致性,如果数据库排序规则区分大小写,这将导致错误。
-
@GetMonthis declared asTinyIntand this will cause an arithmetic overflow if you try to store the year@GetMonth被声明为TinyInt,如果你试图存储年份,这将导致算术溢出
I recommend testing the dynamic SQL statement that you are composing here with some hard coded values to check the results returned, you can use January and 2016 to check if the actual issue in your procedure or it's in your query.
我建议您使用一些硬编码值测试您在此处编写的动态SQL语句以检查返回的结果,您可以使用1月和2016年检查过程中的实际问题或查询中的实际问题。
Hope it helps
希望能帮助到你
#2
0
Thanks for your help, I figured out the root of the problem, and it was because i was not casting the GetYear as a varchar when trying to run the T-SQL statement. This in turn caused the variable to be completely ignored. I also cleaned up the query a little bit after realizing i was goofing up pretty hard.
感谢您的帮助,我找出了问题的根源,这是因为我在尝试运行T-SQL语句时没有将GetYear强制转换为varchar。这反过来导致变量被完全忽略。在意识到我非常努力地搞砸之后,我还清理了一下查询。
Below is the cleaned up functional query, so that it may help someone in the future:
下面是已清理的功能查询,以便将来可以帮助某人:
Declare @GetMonth SmallInt,
@SessionTable varchar(50),
@ContactTable varchar(50),
@TableVersion varchar(2),
@GetYear SmallInt,
@YearCheck varchar(4)
Set @GetMonth=MONTH(cast(GetDate() as Datetime))-1
Set @GetYear=YEAR(cast(GetDate() as Datetime))-1
If (@GetMonth=0)
Begin
Set @GetMonth =12
Set @GetYear =@GetYear - 1
End
Set @TableVersion=CAST(@GetMonth as varchar(2))
Set @SessionTable='[CentralDWH].[dbo].[Sessions_month_' +@tableversion +']'
Set @ContactTable ='[CentralDWH].[dbo].[Contacts_month_' +@tableversion +']'
Set @YearCheck=CAST(@GetYear as varchar(4))
--Select @GetMonth,@GetYear,@YearCheck (DEBUGGING STATEMENT)
-- Select @SessionTable,@ContactTable (DEBUGGING STATEMENT)
Exec('SELECT
[PBX_id] as AgentID,
[p22_value] as Skill,
''Athens'' as Location,
Convert(varchar(20),c.[local_start_time],120) as local_start_time,
convert(varchar(20),c.[local_end_time],120) as local_end_time,
U.[USER_NAME],
call_id
FROM '+@SessionTable +' S
Inner join dbo.Users U on S.user_key=U.user_key
inner Join '+ @ContactTable+' C on S.contact_key=C.contact_key
Where is_screen>0
And Unit_Num between 398003 and 398005
And P22_value is not null
And year(c.[local_start_time]) > '+@YearCheck+'
order by local_start_time')
Once I cleaned all this up and remembered to cast the year properly, everything fell into place.
一旦我清理了所有这些,并记得正确地投下一年,一切都落到了位置。