Tuesday, September 17, 2013

[T-SQL] UTC Conversion problem

[T-SQL] UTC Conversion problem

UTC Conversion problem

Posted: 16 Sep 2013 06:58 AM PDT

Have this query that if you run it past the 16 hour it throughs this error....I"m stumped?? If you run it as is runs fine, but if you try to designate a specific hour bombs out. What am I missing??ALTER PROCEDURE **** ( @Hour INT = NULL , @Application VARCHAR(30) = 'Shamoo' )AS DECLARE @UTCDiff INT , @Start DATETIME , @End DATETIME IF @Hour IS NULL SELECT @Hour = DATEPART(hh, GETDATE()) - 1--SET @Hour = '11' -- (INT Value. Ex: "11" for 11 o'clock of current day hour)--SET @Application = '' -- CHOOSE FROM :-- COCO-- MMS-- PAS-- SHAMOO-- TOOLS-- UBERMAIL SELECT @UTCDiff = DATEDIFF(HOUR, SYSUTCDATETIME (), GETDATE()) * -1 SELECT @Start = CONVERT(NVARCHAR(50), DATEPART(MONTH, SYSUTCDATETIME ())) + '/' + CONVERT(NVARCHAR(50), DATEPART(DAY, SYSUTCDATETIME ())) + '/' + CONVERT(NVARCHAR(50), DATEPART(YEAR, SYSUTCDATETIME ())) + ' ' + CONVERT(NVARCHAR(50), @Hour + @UTCDiff) + ':00:00' SELECT @End = CONVERT(NVARCHAR(50), DATEPART(MONTH, SYSUTCDATETIME ())) + '/' + CONVERT(NVARCHAR(50), DATEPART(DAY, SYSUTCDATETIME ())) + '/' + CONVERT(NVARCHAR(50), DATEPART(YEAR, SYSUTCDATETIME ())) + ' ' + CONVERT(NVARCHAR(50), @Hour + @UTCDiff) + ':59:59'-- SiteCatalyst Errors IF @Application = '' BEGIN SELECT [MESSAGE] , url , Cnt = COUNT(*) , MinTimeUTC = MIN(TimeUtc) , MaxTimeUTC = MAX(TimeUtc) FROM weblogging.dbo.elmah_error WITH ( NOLOCK ) WHERE APPLICATION = 'SHAMOO' AND timeutc BETWEEN @Start AND @End AND MESSAGE != 'Invalid viewstate.' -- Excluded in SiteCatalyst alerts GROUP BY [message] , url ORDER BY cnt DESC , MESSAGE END ELSE BEGIN SELECT [MESSAGE] , url , Cnt = COUNT(*) , MinTimeUTC = MIN(TimeUtc) , MaxTimeUTC = MAX(TimeUtc) FROM weblogging.dbo.elmah_error WITH ( NOLOCK ) WHERE APPLICATION = @Application AND timeutc BETWEEN @Start AND @End AND MESSAGE != 'Invalid viewstate.' -- Excluded in SiteCatalyst alerts GROUP BY [message] , url ORDER BY cnt DESC , MESSAGE END

cursor question

Posted: 16 Sep 2013 06:14 AM PDT

Hi guys,Can i load a cursor from a procedure?Like DECLARE cursor_importedPatients CURSOR FOR EXEC procedureThanks!

How do I remove the Nulls in my output?

Posted: 16 Sep 2013 01:32 PM PDT

CREATE TABLE #Total_Count (NUM_DEATHS_1870 numeric (8,0), NUM_DEATHS_1880 numeric(8,0)) INSERT INTO #Total_Count (NUM_DEATHS_1870)(SELECT COUNT (*) COD FROM [dbo].[1870_1880_DAT]WHERE YR_Died = '1870')INSERT INTO #Total_Count (NUM_DEATHS_1880)(SELECT COUNT (*) COD FROM [dbo].[1870_1880_DAT] WHERE YR_Died = 1880)MY OUTPUTNUM_DEATHS_1870 NUM_DEATHS_1880612 NULLNULL 720

Advice on complex logic with embedded functions.

Posted: 16 Sep 2013 09:22 AM PDT

Hi,I have a scenario were under certain circumstances I need to use the earliest start date and then the latest finish date then work out days between these dates for records that need to be grouped together by a typeLatest finish date - earliest start date.In other circumstances, but using data from the same table I need to just use the start and finish date working out the days between without grouping by type.Finish date - start date.This determines the number of days another calculation needs to be greater than in order to qualify for my query. If the days between falls between certain ranges this gets me the days to be greater than called a qualifying period. The other calculation takes the start date, same as number 2 above and measures the days between.Actual finish - start dateI then check to see if this number of days is > the qualifying days.To determine if I should use just the start / finish date or the latest start / finish date I have a function that analysis about 10 parameters to work out what the rows type are first and then I can work out which method to use.All the information for parameters comes from three tables. All joined with inner joins.At the moment I feel I have functions embedded with functions and its not clean but messy.Just woundering how other people tacke complex scenarios like these. One option is I can pass in the rows unique id, have a select statement in a function get everything it needs and perform the calculation, but this feels I would be selecting from the same tables twice when I can use things like Max(date) over (Id) type logic.I could have a view with all the complex logic and self join using the Id mentioned above but this feels it would perfom not as well.Because the data is from 3rd party database, we cannot add indexes to improve performance or add application logic. I can select data only.Thank youEliza
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog