How can I optimize this script?
-
I am trying to create a reporting system which will give me the "Customer ID", "Number of Messages sent", "Is Message a part of a thread or Distinct", Message Status (i.e. New, Under Review, Cancelled or Resolved) within a given period. I honestly do not see no need for a curso in here but I believe one can be used but I do not understand the benefit of using one. The search is working fine except for the last "Select" prior to dropping the table is not providing Distinct User's. Users with multiple messages/Threads are being pulled up as many time as the amount of records found for them. It is the result set from the Last select that is displayed in the GUI. Any assistance given will be appreciated. SET NOCOUNT ON /* Don't return row counting to caller */ CREATE TABLE #MS_MessagesFromCustomer( NumOfMessages BIGINT, CustomerID VARCHAR(100), Period datetime, ThreadID int, MessageStatus int ) CREATE TABLE #MS_InquiriesByCustomer( ThreadID int, CustomerID VARCHAR(100), TotalMessages int, New int, UnderReview int, Cancelled int, Resolved int, TotalThreads int ) Insert into #MS_MessagesFromCustomer(CustomerID, NumOfMessages, Period, ThreadID, MessageStatus) Select MSM.SenderID, count(MSM.MessageID)Messages, Convert(varchar, MSM.SentDate, 101) [Sent Date],MSM.ThreadID,MST.CurrentStatusID [Current Status] from dbo.MS_Messages MSM INNER JOIN dbo.MS_Threads MST ON MSM.ThreadID = MST.ThreadID where MSM.SentDate >'3/1/2005' and MSM.SentDate < '3/31/2005' group by MSM.SenderID, MSM.SentDate, MSM.ThreadID, MST.CurrentStatusID order by SenderID --Select * from #MS_MessagesFromCustomer INSERT INTO #MS_InquiriesByCustomer(ThreadID,CustomerID, TotalMessages, New, UnderReview, Cancelled,Resolved, TotalThreads) Select Distinct(ThreadID)'ThreadID', CustomerID'Customer' , count(NumOfMessages) 'Total Messages Recevied',COUNT(CASE MessageStatus when 19 THEN MessageStatus end) AS 'New', COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) AS 'UnderReview', COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) AS 'Cancelled', COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end) AS 'Resolved', (COUNT(CASE MessageStatus WHEN 19 THEN MessageStatus end) + COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) + COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) + COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end)) AS 'Total Threads' from #MS_MessagesFromCustomer group by customerID, ThreadID o
-
I am trying to create a reporting system which will give me the "Customer ID", "Number of Messages sent", "Is Message a part of a thread or Distinct", Message Status (i.e. New, Under Review, Cancelled or Resolved) within a given period. I honestly do not see no need for a curso in here but I believe one can be used but I do not understand the benefit of using one. The search is working fine except for the last "Select" prior to dropping the table is not providing Distinct User's. Users with multiple messages/Threads are being pulled up as many time as the amount of records found for them. It is the result set from the Last select that is displayed in the GUI. Any assistance given will be appreciated. SET NOCOUNT ON /* Don't return row counting to caller */ CREATE TABLE #MS_MessagesFromCustomer( NumOfMessages BIGINT, CustomerID VARCHAR(100), Period datetime, ThreadID int, MessageStatus int ) CREATE TABLE #MS_InquiriesByCustomer( ThreadID int, CustomerID VARCHAR(100), TotalMessages int, New int, UnderReview int, Cancelled int, Resolved int, TotalThreads int ) Insert into #MS_MessagesFromCustomer(CustomerID, NumOfMessages, Period, ThreadID, MessageStatus) Select MSM.SenderID, count(MSM.MessageID)Messages, Convert(varchar, MSM.SentDate, 101) [Sent Date],MSM.ThreadID,MST.CurrentStatusID [Current Status] from dbo.MS_Messages MSM INNER JOIN dbo.MS_Threads MST ON MSM.ThreadID = MST.ThreadID where MSM.SentDate >'3/1/2005' and MSM.SentDate < '3/31/2005' group by MSM.SenderID, MSM.SentDate, MSM.ThreadID, MST.CurrentStatusID order by SenderID --Select * from #MS_MessagesFromCustomer INSERT INTO #MS_InquiriesByCustomer(ThreadID,CustomerID, TotalMessages, New, UnderReview, Cancelled,Resolved, TotalThreads) Select Distinct(ThreadID)'ThreadID', CustomerID'Customer' , count(NumOfMessages) 'Total Messages Recevied',COUNT(CASE MessageStatus when 19 THEN MessageStatus end) AS 'New', COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) AS 'UnderReview', COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) AS 'Cancelled', COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end) AS 'Resolved', (COUNT(CASE MessageStatus WHEN 19 THEN MessageStatus end) + COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) + COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) + COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end)) AS 'Total Threads' from #MS_MessagesFromCustomer group by customerID, ThreadID o
DISTINCTused as an argument of aSELECTclause does not eliminate rows due to a single duplicate column. It eliminates rows when all columns are equivalent. The whole row would have to be the same for it to begin its removal process.DISTINCTcan be used as an argument in an aggregate likeAVG(DISTINCT price). Here it will eliminate duplicate prices before calculating the average. I think you need to re-examine how you have coded your store procedure. It seems a little over done. Not sure what your desired end result is but, I would think a much simplerLEFT JOINwould handle most of the work.