August 28, 2007 at 12:57 pm
I need to create a stored procedure that mimics a view I created using derived tables. It basically needs to return values using a date range, rather than a total count (like the view). With derived tables, do I have to re-declare the parameters in each time in each derived tables to pass the parameters?
Alternatively, is there a better way of doing this entirely?
Here is the view:
SELECT afreq.af_req AS [AF Requote],
exreq.ex_req AS [EX Requote],
afqq.af_qq AS [AF Quick Quote],
exqq.ex_qq AS [EX Quick Quote],
afbk.af_bk AS [AF Booked],
exbk.ex_bk AS [EX Booked],
afrg.af_rg AS [AF Reg Quote],
exrg.ex_rg AS [EX Reg Quote]
FROM
--derived tables:
--REQUOTES
(SELECT COUNT(d1.transactionid) AS AF_ReQ
FROM [CustomerWeb].[dbo].[QT_Data] d1
WHERE
d1.[QuoteTypeId]=1 AND
d1.[TransTypeId]=1) afreq,
(SELECT COUNT(d1.transactionid) AS Ex_ReQ
FROM [CustomerWeb].[dbo].[QT_Data] d1
WHERE
d1.[QuoteTypeId]=1 AND
d1.[TransTypeId]=2) exreq,
--***********************************************
--QUICKQUOTES
(SELECT COUNT(d1.transactionid) AS AF_QQ
FROM [CustomerWeb].[dbo].[QT_Data] d1
WHERE
d1.[QuoteTypeId]=2 AND
d1.[TransTypeId]=1) afqq,
(SELECT COUNT(d1.transactionid) AS EX_QQ
FROM [CustomerWeb].[dbo].[QT_Data] d1
WHERE
d1.[QuoteTypeId]=2 AND
d1.[TransTypeId]=2) exqq,
--**********************************************
--BOOKED
(SELECT COUNT(d1.transactionid) AS AF_BK
FROM [CustomerWeb].[dbo].[QT_Data] d1
WHERE
d1.[QuoteTypeId]=3 AND
d1.[TransTypeId]=1) afbk,
(SELECT COUNT(d1.transactionid) AS EX_BK
FROM [CustomerWeb].[dbo].[QT_Data] d1
WHERE
d1.[QuoteTypeId]=3 AND
d1.[TransTypeId]=2) exbk,
--**********************************************
--REG QUOTES
(SELECT COUNT(d1.transactionid) AS AF_RG
FROM [CustomerWeb].[dbo].[QT_Data] d1
WHERE
d1.[QuoteTypeId]=4 AND
d1.[TransTypeId]=1) afrg,
(SELECT COUNT(d1.transactionid) AS EX_RG
FROM [CustomerWeb].[dbo].[QT_Data] d1
WHERE
d1.[QuoteTypeId]=4 AND
d1.[TransTypeId]=2) exrg
August 28, 2007 at 1:08 pm
Without any sample data to work with, at a glance it certainly looks like you can get your numbers by simply grouping by QuoteTypeID and TransTypeID. I'm not sure where the data range comes into play, as I don't see it in your code.
August 28, 2007 at 1:22 pm
Sorry the transaction date is in the original table. I did get a solution, by just doing nested select statements, rather than derived tables, I only had to declare the variables once:
CREATE PROCEDURE [dbo].[sp_DateRangeCounts]
(
@StartDate DATETIME,
@EndDate DATETIME
)
AS
BEGIN
SELECT COUNT(*) AS TotalCount,
(SELECT COUNT(*)FROM QT_Data AS QT2
WHERE QT2.QuoteTypeId = 1 AND QT2.TransTypeId = 1
AND (QT2.EntryDate BETWEEN @StartDate and @EndDate)) AS AF_Requote,
(SELECT COUNT(*)FROM QT_Data AS QT2
WHERE QT2.QuoteTypeId = 2 AND QT2.TransTypeId = 1
AND (QT2.EntryDate BETWEEN @StartDate and @EndDate)) AS AF_QuickQuote,
(SELECT COUNT(*)FROM QT_Data AS QT2
WHERE QT2.QuoteTypeId = 3 AND QT2.TransTypeId = 1
AND (QT2.EntryDate BETWEEN @StartDate and @EndDate)) AS AF_Booked,
(SELECT COUNT(*)FROM QT_Data AS QT2
WHERE QT2.QuoteTypeId = 4 AND QT2.TransTypeId = 1
AND (QT2.EntryDate BETWEEN @StartDate and @EndDate)) AS AF_RegQuote,
(SELECT COUNT(*)FROM QT_Data AS QT2
WHERE QT2.QuoteTypeId = 1 AND QT2.TransTypeId = 2
AND (QT2.EntryDate BETWEEN @StartDate and @EndDate)) AS EX_Requote,
(SELECT COUNT(*)FROM QT_Data AS QT2
WHERE QT2.QuoteTypeId = 3 AND QT2.TransTypeId = 2
AND (QT2.EntryDate BETWEEN @StartDate and @EndDate)) AS EX_QuickQuote,
(SELECT COUNT(*)FROM QT_Data AS QT2
WHERE QT2.QuoteTypeId = 3 AND QT2.TransTypeId = 2
AND (QT2.EntryDate BETWEEN @StartDate and @EndDate)) AS EX_Booked,
(SELECT COUNT(*)FROM QT_Data AS QT2
WHERE QT2.QuoteTypeId = 4 AND QT2.TransTypeId = 2
AND (QT2.EntryDate BETWEEN @StartDate and @EndDate)) AS EX_RegQuote
FROM QT_Data
August 28, 2007 at 1:46 pm
Never ever name your stored procedures with sp_ !!!
CREATE
PROCEDURE dbo.uspDateRangeCounts
(
@StartDate DATETIME,
@EndDate DATETIME
)
AS
SET NOCOUNT ON
SELECT COUNT(*) AS TotalCount,
SUM(CASE WHEN QuoteTypeId = 1 AND TransTypeId = 1 THEN 1 ELSE 0 END) AS AF_Requote,
SUM(CASE WHEN QuoteTypeId = 2 AND TransTypeId = 1 THEN 1 ELSE 0 END) AS AF_QuickQuote,
SUM(CASE WHEN QuoteTypeId = 3 AND TransTypeId = 1 THEN 1 ELSE 0 END) AS AF_Booked,
SUM(CASE WHEN QuoteTypeId = 4 AND TransTypeId = 1 THEN 1 ELSE 0 END) AS AF_RegQuote,
SUM(CASE WHEN QuoteTypeId = 1 AND TransTypeId = 2 THEN 1 ELSE 0 END) AS EX_Requote,
SUM(CASE WHEN QuoteTypeId = 3 AND TransTypeId = 2 THEN 1 ELSE 0 END) AS EX_QuickQuote,
SUM(CASE WHEN QuoteTypeId = 3 AND TransTypeId = 2 THEN 1 ELSE 0 END) AS EX_Booked,
SUM(CASE WHEN QuoteTypeId = 4 AND TransTypeId = 2 THEN 1 ELSE 0 END) AS EX_RegQuote
FROM QT_Data
WHERE EntryDate BETWEEN @StartDate and @EndDate
N 56°04'39.16"
E 12°55'05.25"
August 28, 2007 at 2:04 pm
Thanks Peter, much cleaner.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply