I'm still a bit new to MS SQL and was wondering if there was a better way to do what I want.
If the user supplies a location the LocID with be > 0 and I want to select the data where emp.LOCATION_ID = @LocID. If the LocID = 0 then I don't want to include that in the where clause.
I wrote something that works but seems like there should be an easier way to do this.
Any comments or suggestions are welcome.
Thanks!
If the user supplies a location the LocID with be > 0 and I want to select the data where emp.LOCATION_ID = @LocID. If the LocID = 0 then I don't want to include that in the where clause.
I wrote something that works but seems like there should be an easier way to do this.
Any comments or suggestions are welcome.
Thanks!
Code:
CREATE PROCEDURE GET_REPORT
-- Add the parameters for the stored procedure here
@startDate datetime,
@endDate datetime,
@LocID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF ( @LocID = 0)
BEGIN
SELECT
MAX(distinct qad.QA_DATA_ID),
CONVERT(VARCHAR,qad.DATA_DATE, 101) DATA_DATE,
SUM(qad.NEW_USERS) NEW_USERS,
SUM(qad.TRANSACTIONS),
SUM(qad.CORP_TRANSACTIONS),
SUM(qad.CALLS_QUEUE) CALLS,
SUM(qad.CORP_CALLS) CORP_CALLS,
SUM(qad.CALLS_DN) DN_CALLS,
SUM(qad.DN_TRANSFERS) DN_TRANS,
SUM(qad.CALLS_SHORT) SHORT_CALLS,
dbo.GET_CONVERSION (SUM(qad.TRANSACTIONS),
SUM(qad.CORP_TRANSACTIONS),
(SELECT COUNT(TRANS_NUMBER) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4)AND
(dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))),
SUM(qad.CALLS_QUEUE),
SUM(qad.CALLS_DN),
SUM(qad.CORP_CALLS),
SUM(qad.CALLS_SHORT),
SUM(qad.DN_TRANSFERS)) ttlCon,
CONVERT(VARCHAR, SUM(qad.SALES_COBRAND), 1) SALES_COBRAND,
CONVERT(VARCHAR, SUM(qad.SALES_GOLD), 1) SALES_GOLD,
CONVERT(VARCHAR, SUM(qad.SALES_CORP), 1) SALES_CORP,
CONVERT(VARCHAR, (SELECT SUM(DOLLAR_AMOUNT) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4)AND
(dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))), 1) AS SALES_LS,
(SELECT COUNT(TRANS_NUMBER) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4)AND
(dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))) AS LS_TRANSACTIONS,
CONVERT(VARCHAR, dbo.TOTAL_TS(
SUM(qad.SALES_COBRAND),
SUM(qad.SALES_GOLD),
SUM(qad.SALES_CORP),
(SELECT SUM(DOLLAR_AMOUNT) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 )AND
(dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE)))), 1) TTS
FROM QA_DATA qad JOIN EMPLOYEE emp
ON emp.EMPLOYEE_ID = qad.EMPLOYEE_ID
WHERE qad.DATA_DATE >= @startDate AND
qad.DATA_DATE < @endDate AND
emp.REPORTABLE = 1 AND
emp.ENABLED > 0 AND
(dbo.IS_IN_DEPT(emp.DEPARTMENT_ID, 'inside sales') = 'true')
GROUP BY qad.DATA_DATE
END
ELSE
BEGIN
SELECT
MAX(distinct qad.QA_DATA_ID),
CONVERT(VARCHAR,qad.DATA_DATE, 101) DATA_DATE,
SUM(qad.NEW_USERS) NEW_USERS,
SUM(qad.TRANSACTIONS),
SUM(qad.CORP_TRANSACTIONS),
SUM(qad.CALLS_QUEUE) CALLS,
SUM(qad.CORP_CALLS) CORP_CALLS,
SUM(qad.CALLS_DN) DN_CALLS,
SUM(qad.DN_TRANSFERS) DN_TRANS,
SUM(qad.CALLS_SHORT) SHORT_CALLS,
dbo.GET_CONVERSION (SUM(qad.TRANSACTIONS),
SUM(qad.CORP_TRANSACTIONS),
(SELECT COUNT(TRANS_NUMBER) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 )AND
(dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))),
SUM(qad.CALLS_QUEUE),
SUM(qad.CALLS_DN),
SUM(qad.CORP_CALLS),
SUM(qad.CALLS_SHORT),
SUM(qad.DN_TRANSFERS)) ttlCon,
CONVERT(VARCHAR, SUM(qad.SALES_COBRAND), 1) SALES_COBRAND,
CONVERT(VARCHAR, SUM(qad.SALES_GOLD), 1) SALES_GOLD,
CONVERT(VARCHAR, SUM(qad.SALES_CORP), 1) SALES_CORP,
CONVERT(VARCHAR, (SELECT SUM(DOLLAR_AMOUNT) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 OR tl.TRANSACTION_ID IS NULL)AND
(dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE)OR tl.TRANSACTION_ID IS NULL)), 1) AS SALES_LS,
(SELECT COUNT(TRANS_NUMBER) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 )AND
(dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))) AS LS_TRANSACTIONS,
CONVERT(VARCHAR, dbo.TOTAL_TS(
SUM(qad.SALES_COBRAND),
SUM(qad.SALES_GOLD),
SUM(qad.SALES_CORP),
(SELECT SUM(DOLLAR_AMOUNT) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 )AND
(dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE)))), 1) TTS
FROM QA_DATA qad JOIN EMPLOYEE emp
ON emp.EMPLOYEE_ID = qad.EMPLOYEE_ID
WHERE qad.DATA_DATE >= @startDate AND
qad.DATA_DATE < @endDate AND
emp.REPORTABLE = 1 AND
emp.ENABLED > 0 AND
(dbo.IS_IN_DEPT(emp.DEPARTMENT_ID, 'inside sales') = 'true') AND
emp.LOCATION_ID = @LocID
GROUP BY qad.DATA_DATE
END
END