April 23, 2013 at 5:23 am
I have a stored procedure and I am selecting Renewal Date and the Last Renewal date (second last greater date)
ALTER PROCEDURE [rept].[spRenewalDates]
@startDate DATETIME,
@endDate DATETIME
AS
BEGIN
SET NOCOUNT ON;
--DECLARE @system INT
SELECT
tblrenewals.SystemNum,
(case when Year([Date]) = 1900 or Year([Date]) = 2999 then '' else
ISNULL(CONVERT(VARCHAR, DATE, 103) + ' ' + left(CONVERT(VARCHAR, DATE, 108), 5),'')
end ) AS [Renewal Date]
,(SELECT MAX( date )
FROM tblRenewals
WHERE date != ( SELECT MAX( date )
FROM tblRenewals where SystemNum = tblSystem.SystemNum ) and SystemNum = tblSystem.SystemNum) AS [Last Renewal Date]
,ClientName, Name AS [Ot's]
FROM tblRenewals
INNER JOIN dbo.tblSystem ON dbo.tblRenewals.SystemNum = dbo.tblSystem.SystemNum
INNER JOIN dbo.tblClient ON dbo.tblSystem.ClientNumber = dbo.tblClient.ClientNumber
INNER JOIN dbo.tblReps ON dbo.tblClient.RepType1 = dbo.tblReps.Id
WHERE (DATE BETWEEN @startDate and @endDate)
ORDER BY Date DESC
END
To get the correct format for the renewal date I am using
ISNULL(CONVERT(VARCHAR, DATE, 103) + ' ' + left(CONVERT(VARCHAR, DATE, 108), 5),'')
Can someone please edit the above stored procedure so I get the Last Renewal date in the same format as the Renewal Date.
Thank you
April 23, 2013 at 8:13 am
You really should leave formatting to the front end. All formatting does on the sql side of things is slow it down and make it more difficult to work with. Also you really should avoid using reserved words as column names (DATE, NAME etc...).
Honestly I think your query needs a complete rewrite but this should get you close.
SELECT CONVERT(VARCHAR, MAX(DATE), 103) + ' ' + left(CONVERT(VARCHAR, MAX(DATE), 108), 5)
FROM tblRenewals
WHERE DATE != (
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 8:21 am
Thank you sean that fixed my problem and this is just a report I am making for a client which will be viewed in a word document so I had to do the formatting.
April 23, 2013 at 8:24 am
maxlezious (4/23/2013)
Thank you sean that fixed my problem and this is just a report I am making for a client which will be viewed in a word document so I had to do the formatting.
Glad that worked for you. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply