April 18, 2006 at 9:52 am
Hello. I have a question that I hope someone can help me with. Using the stored procedure below, is it possible to format the field, VolDate so that I can show it in either a month format such as April, or a year format such as 2006? If so, how would you go about doing it?
CREATE procedure qryMTD_Volume
@StartDate datetime,
@EndDate datetime
AS
SELECT VolDate, ClientName, ClientBoxNumber, tblClient.ClientCode, ClientORG, ClientDDA, ClientGroup,
SUM(fldChecksFull) AS ChecksFull, SUM(fldStubsFull) AS StubsFull, SUM(fldChecksPartial) AS ChecksPartial,
SUM(fldStubsPartial) AS StubsPartial, SUM(fldChecksMulti) AS ChecksMulti, SUM(fldStubsMulti) AS StubsMulti,
SUM(fldChecksOnly) AS ChecksOnly, SUM(fldChecksOnlySuspense) AS Suspense,
SUM(fldCheckAndList) AS CheckAndList, SUM(fldCheckAndListChecks) AS CheckAndListChecks,
SUM(fldOCRScanLineRejects) AS OCRScanlineRejects, SUM(fldMICRScanlineRejects) AS MICRScanLineRejects,
SUM(fldExpressMail) AS ExpressMail, SUM(fldLSARCChecksAttempted) AS LSARCChecksAttempted,
SUM(fldHSARCChecksAttempted) AS HSARCChecksAttempted, SUM(fldLSARCChecksConverted) AS LSARCChecksConverted,
SUM(fldHSARCChecksConverted) AS HSARCChecksConverted, SUM(fldLookupStubs) AS LookupStubs,
SUM(fldStubsOnly) AS StubsOnly, SUM(fldTotalDollarsDeposited) AS TotalDollarsDeposited, SUM(Attempts) AS RAttempts,
SUM(ExtractOnly) AS Extract, SUM(Correspondence) AS Corr, SUM(Unbankables) AS Unbank,SUM(Lookups) AS LookupItems,
SUM(Returns) AS ReturnItems, SUM(ReturnsNF) AS ReturnsNotFound, SUM(ReturnEnv) AS ReturnedEnvelope, SUM(BackDate) AS BackDated,
SUM(AddressChange) AS AddressChanges, SUM(PostMark) AS PostMarked, SUM(BatchCount) AS Batches, SUM(CreditCard) AS CCards,
SUM(PostalCards) AS PostalReturns, SUM(Cash) AS RCash , Sum(CDs) AS CDBurned,
SUM(fldStubsFull + fldStubsPartial + fldStubsMulti + fldChecksOnly + fldCheckAndList) AS Image,
SUM(fldStubsFull + fldStubsPartial + fldStubsMulti + fldChecksOnly + fldCheckAndList) AS Sales,
SUM(fldChecksFull + fldChecksPartial + fldChecksMulti + fldChecksOnly + fldCheckAndListChecks) AS GoodCheck
FROM tblClient INNER JOIN
tblImportedVolume ON tblClient.ClientCode = tblImportedVolume.ClientCode AND fldStubsFull + fldStubsPartial + fldStubsMulti + fldChecksOnly + ExtractOnly + Attempts + Correspondence + Unbankables + Lookups + PostalCards + Returns > 0
AND VolDate >= @StartDate
AND VolDate <= @EndDate
GROUP BY ClientName, ClientBoxNumber, tblClient.ClientCode, ClientORG, ClientDDA, ClientGroup
ORDER BY ClientName
GO
April 18, 2006 at 10:30 am
Look at DATEPART and DATENAME in Books Online for hints on formatting.
April 18, 2006 at 1:45 pm
splly. DATENAME..
SELECT DATENAME(mm, getdate()) AS Month, DATENAME(yyyy, getdate()) AS Year
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply