|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
using getdate()up to the time it is run. The buttonpushes SP needs two values a begin date and an end date. It works fine when I put in the date for the week but when i try to use the variables so I will not have to change the dates each week it will not run. All it returns is the column heading LotID. I looks like the data is not getting passed into the Crosstab SP. If anybody has a suggestion please let me know. The code is attached: Alter Procedure GetLotReportV2 As --declare variables and set them for last week Declare @Start DateTime Declare @End DateTime Select @Start = (Getdate()-1), @End = (Getdate()-8) --Create temp table inorder to crosstab Create table PushTemp (LotID Char(15), Total dec(10,3), FaultCause Char(15)) --Fill table with data Insert into PushTemp exec spcsql.dbo.sp_Buttonpushes @Start, @End --Crosstab the table exec sp_JRMCrossTab 'PushTemp', 'LotID', 'FaultCause', 'Total', NULL, 'SUM' --Drop table in order for the procedure to run again Drop Table PushTemp Thanks AB A.B. wrote:
Show quote > I have created a SP that will generate information for the week With your current design, you are creating a permanent table each time, > leading up to the time it is run. The buttonpushes SP needs two > values a begin date and an end date. It works fine when I put in the > date for the week but when i try to use the variables so I will not > have to change the dates each week it will not run. All it returns is > the column heading LotID. I looks like the data is not getting passed > into the Crosstab SP. If anybody has a suggestion please let me know. > The code is attached: > > > Alter Procedure GetLotReportV2 > As > --declare variables and set them for last week > Declare @Start DateTime > Declare @End DateTime > > Select @Start = (Getdate()-1), > @End = (Getdate()-8) > > --Create temp table inorder to crosstab > Create table PushTemp (LotID Char(15), Total dec(10,3), FaultCause > Char(15)) > > --Fill table with data > Insert into PushTemp exec spcsql.dbo.sp_Buttonpushes @Start, @End > > --Crosstab the table > exec sp_JRMCrossTab > 'PushTemp', > 'LotID', > 'FaultCause', > 'Total', > NULL, > 'SUM' > --Drop table in order for the procedure to run again > Drop Table PushTemp > > > Thanks > AB not a temporary table as your in-line comments say. This is not a good idea. You are better off using a temp table. How are you passing the dates to the stored procedure? Can you post the exec code.
Show quote
"David Gugick" wrote: exec sp_buttonpushes '8/8/05', '8/14/05'> A.B. wrote: > > I have created a SP that will generate information for the week > > leading up to the time it is run. The buttonpushes SP needs two > > values a begin date and an end date. It works fine when I put in the > > date for the week but when i try to use the variables so I will not > > have to change the dates each week it will not run. All it returns is > > the column heading LotID. I looks like the data is not getting passed > > into the Crosstab SP. If anybody has a suggestion please let me know. > > The code is attached: > > > > > > Alter Procedure GetLotReportV2 > > As > > --declare variables and set them for last week > > Declare @Start DateTime > > Declare @End DateTime > > > > Select @Start = (Getdate()-1), > > @End = (Getdate()-8) > > > > --Create temp table inorder to crosstab > > Create table PushTemp (LotID Char(15), Total dec(10,3), FaultCause > > Char(15)) > > > > --Fill table with data > > Insert into PushTemp exec spcsql.dbo.sp_Buttonpushes @Start, @End > > > > --Crosstab the table > > exec sp_JRMCrossTab > > 'PushTemp', > > 'LotID', > > 'FaultCause', > > 'Total', > > NULL, > > 'SUM' > > --Drop table in order for the procedure to run again > > Drop Table PushTemp > > > > > > Thanks > > AB > > With your current design, you are creating a permanent table each time, > not a temporary table as your in-line comments say. This is not a good > idea. You are better off using a temp table. > > How are you passing the dates to the stored procedure? Can you post the > exec code. > > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com > > This is how I'm passing when I'm not trying to use the variables: A.B. wrote:
Show quote > "David Gugick" wrote: When working with dates, you should always use a portable date format. > >> A.B. wrote: >>> I have created a SP that will generate information for the week >>> leading up to the time it is run. The buttonpushes SP needs two >>> values a begin date and an end date. It works fine when I put in the >>> date for the week but when i try to use the variables so I will not >>> have to change the dates each week it will not run. All it returns >>> is the column heading LotID. I looks like the data is not getting >>> passed into the Crosstab SP. If anybody has a suggestion please let >>> me know. The code is attached: >>> >>> >>> Alter Procedure GetLotReportV2 >>> As >>> --declare variables and set them for last week >>> Declare @Start DateTime >>> Declare @End DateTime >>> >>> Select @Start = (Getdate()-1), >>> @End = (Getdate()-8) >>> >>> --Create temp table inorder to crosstab >>> Create table PushTemp (LotID Char(15), Total dec(10,3), FaultCause >>> Char(15)) >>> >>> --Fill table with data >>> Insert into PushTemp exec spcsql.dbo.sp_Buttonpushes @Start, @End >>> >>> --Crosstab the table >>> exec sp_JRMCrossTab >>> 'PushTemp', >>> 'LotID', >>> 'FaultCause', >>> 'Total', >>> NULL, >>> 'SUM' >>> --Drop table in order for the procedure to run again >>> Drop Table PushTemp >>> >>> >>> Thanks >>> AB >> >> With your current design, you are creating a permanent table each >> time, not a temporary table as your in-line comments say. This is >> not a good idea. You are better off using a temp table. >> >> How are you passing the dates to the stored procedure? Can you post >> the exec code. >> >> >> -- >> David Gugick >> Quest Software >> www.imceda.com >> www.quest.com >> >> This is how I'm passing when I'm not trying to use the variables: > exec sp_buttonpushes '8/8/05', '8/14/05' The format you are using is not portable and depending on the client could get interpreted as August 14, 2005 or an invalid date. I'm guessing based on those example dates though, that this is not the problem. If the date ha In any case, when working with date-only parameters (no time), use: YYYYMMDD Also remember that SQL Server DATETIME and SMALLDATETIME data types have time date attached to them as well making comparisons difficult unless all the values use the same 12am time. For example: "20050130" < "20050130T00:35:00.000"  |
|||||||||||||||||||||||
Other interesting topics