Trying to extract data from a single table

Skanless

I attempting to extract data from a single table based on the two different criteria then presenting the information as one table. In the first instance I am trying to extact the date and count() of all messages received with a given time period(the query is based on createdDate). In the second intsatnce I am attempting to extrtact data based on it current status(i.e resolved, resaerched, open) I have created two temp table that provides me with the data I need now I need to group them by date so I can display the data by months for example: Beginning Period Received Messages Resolved Issues 1/1/2007 15 6 2/1/07 8 19 3/1/07 25 10 And so forth. I have inclused the script any assistance will be great appreciated. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_MS_IssuesReport](@PeriodName varchar(50), @BeginningDate datetime, @EndDate datetime)AS BEGIN DECLARE @Period datetime, @MessagesReceived bigint, @MessagesClosed bigint SET NOCOUNT ON; CREATE TABLE #MS_MessagesReceived( DateCreated datetime, IssuesReceived int, ) CREATE TABLE #MS_MessagesStatus( DateCreated datetime, IssuesClosed int ) CREATE TABLE #MS_MessagesReceivedAndStatuses( DateCreated datetime, IssuesReceived int, IssuesClosed int ) Insert Into #MS_MessagesReceived(DateCreated,IssuesReceived) Select dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS 'Period', count(CurrentStatusID) from Ms_threads where CreateDate BETWEEN @BeginningDate and @EndDate Group by CreateDate,CurrentStatusID Order by CreateDate Insert Into #MS_MessagesStatus(DateCreated, IssuesClosed) Select dbo.RPT_GetPeriod(@PeriodName, StatusDate) AS 'Period',count(CurrentStatusID)from Ms_threads where dbo.RPT_IsInquiryCompleted(CurrentStatusID) = 1 and StatusDate BETWEEN @BeginningDate and @EndDate Group by StatusDate, CurrentStatusID Order by StatusDate, CurrentStatusID SELECT @MessagesReceived = count(IssuesReceived) from #MS_MessagesReceived SELECT @MessagesClosed = count (IssuesClosed) from #MS_MessagesStatus SELECT @Period =dbo.RPT_GetPeriod(@PeriodName, DateCreated) from #MS_MessagesReceived INSERT INTO #MS_MessagesReceivedAndStatuses(DateCreated, IssuesReceived, IssuesClosed) VALUES(@Period, @MessagesReceived, @MessagesClosed) Select DateCreated 'Time Period Beginning' , IssuesReceived 'Issue Received', Issu

andyharman

How about:

SELECT dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS Period,
    COUNT(*) AS IssuesReceived,
    SUM(dbo.RPT_IsInquiryCompleted(CurrentStatusID)) AS IssuesClosed
FROM Ms_threads
WHERE CreateDate BETWEEN @BeginningDate and @EndDate
GROUP BY CreateDate
ORDER BY CreateDate

The "SUM" expression assumes that your function returns either 0 or 1.

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Skanless

Thanks for the your help. It sort does what I want however because a message that was created 1/5/2005 may not be resolved until sometime in March its current Status will not be 'Resolved' thus will not fall within this status period. Looking at the code that messages status may be counted during a January monthly report the status is not resolved only becuase the message was created in January. I guess I did not provide information but I am trying to extract and count ALL messages created during the period and all messages Resolved during the period. Agian, a messages created during that period may not be resolved until a future date hence we do not need to count their status on their status is 'Resolved' thus keying off the statusDate. Thanks for you response. I like the way you clean up the code.

Skan If you knew it would not compile why didn't you tell me?!?!?!

Skanless

A simple 'OR' Statement should work. SELECT dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS Period, COUNT(*) AS IssuesReceived, SUM(dbo.RPT_IsInquiryCompleted(CurrentStatusID)=1) AS IssuesClosed FROM Ms_threadsWHERE CreateDate BETWEEN @BeginningDate and @EndDateGROUP or StatusDate BETWEEN @BeginningDate and @EndDate(This would have to be union with CreateDate for it to work.) GROUP BY CreateDate ORDER BY CreateDate unfortunately the OR statement as scripted is not working either.

Skan If you knew it would not compile why didn't you tell me?!?!?!

andyharman

Hi Skan How about:

SELECT dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) AS Period,
    SUM(A.IssueReceived) AS IssuesReceived,
    SUM(A.IssueClosed) AS IssuesClosed
FROM (
  SELECT CreateDate AS PeriodDate,
      1 AS IssueReceived,
      0 AS IssueClosed
    FROM Ms_threads
    WHERE CreateDate BETWEEN @BeginningDate and @EndDate
  UNION ALL
  SELECT StatusDate AS PeriodDate,
      0 AS IssueReceived,
      dbo.RPT_IsInquiryCompleted(CurrentStatusID) AS IssueClosed
    FROM Ms_threads
    WHERE StatusDate BETWEEN @BeginningDate and @EndDate
) A
GROUP BY A.PeriodDate
ORDER BY A.PeriodDate

The select statements within the from-clause count the issues openned and closed during the selected period. The outside select statement then groups everything together. The technique is fairly common. If you have problems understanding what it is doing then run each of the select statements individually. Regards Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Skanless

Dude you are a Genius. I modified the script a but and it worked like a baby. Thnks dude.. I owe u a beer. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_MS_IssuesReport_Num2](@PeriodName varchar(50), @BeginningDate datetime, @EndDate datetime)AS BEGIN SELECT dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) AS Period, SUM(A.IssueReceived) AS IssuesReceived, SUM(A.IssueClosed) AS IssuesClosed FROM( SELECT CreateDate AS PeriodDate, 1 AS IssueReceived, 0 AS IssueClosed FROM Ms_threads WHERE CreateDate BETWEEN @BeginningDate and @EndDate UNION ALL SELECT StatusDate AS PeriodDate, 0 AS IssueReceived, dbo.RPT_IsInquiryCompleted(CurrentStatusID) AS IssueClosed FROM Ms_threads WHERE StatusDate BETWEEN @BeginningDate and @EndDate ) A GROUP BY dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) -- Added the date floor function. ORDER BY dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) end Thank Code Project....This deserves a 10 not a 5...hehehehe!!

Skan If you knew it would not compile why didn't you tell me?!?!?!