Group By months for a range of dates

singh_ginni · September 8th, 2003, 08:27 AM

Dear All, My problem though easy but couldnt find way out, hence your help

The sample database is as follows (Table name. Let us call it " Complaint" for the problem) :

ComplainDates Complaint Id
Jan 1,2001 1
Jan 2,2001 2
Jan 23,2001 1
Jan 24,2001 4

Feb 1,2001 5
Feb 2,2001 5

Mar 1,2001 6
Mar 2,2001 7
Mar.......

p.s : (ComplaintId is not a primary key here)

2) The Problem :

The asp query form looks like follows:

Suppose the dates are the filled dates
-----------------------------------
Start date : _Jan23,2001_
End Date : _Mar02,2001_

SUBMIT Button

-----------------------------------

I need to create a table or result only through SQL queries or stored Procedure in the following manner.

Dates No of unique complaints
Jan23 - Jan 31 2 (Note : pls note the start/end date)
Feb01 - Feb 28 1 (-do-)
Mar01 - Mar 02 2 (Note : Pls note the end date)

P.S: The data here matches the above database. This is exactly what i want and later on the other complex queries will go on.

----------------------------------------------
Just some coding which i was trying to do is as follows :

CREATE PROCEDURE [dbo].[statustime] (@startdate as varchar(50),@enddate as varchar(50)) AS
declare @intmonth INT
declare @loopmonth datetime
declare @strdate
begin
--@intmonth=month(enddate)-month(startdate)+1
--@loopmonth=month(@startdate)

while month(@startdate)<=month(@enddate)
begin
if month(@startdate)<month(@enddate)
select count(*) from orderlist where startdate between @startdate and year(@startdate)+(@loopmonth+1)+'01'
else
select count(*) from orderlist where startdate between @startdate and @enddate
--@startdate='2003/02/02'
--@startdate=dateadd(month,1,convert(datetime,@start date))
end
end

-----------------------------------------------------------
I know there are some errors in the format which i rectified but couldnt really run it. May be i am into "paralysis by analysis" , OS please help. The problem is up there you can also create the same database to test your solution.

Regards,
Ginni

owain · September 8th, 2003, 09:17 AM

This is not in the exact format you require, however it can easily be formatted at the client:

Code:

  SELECT Month(ComplainDates) AS [Month],
         Year(ComplainDates) AS [Year],
         COUNT(ComplainDates) AS NoComplaints
    FROM Complaint
   WHERE ComplainDates BETWEEN 2001-01-23 AND 2001-03-02
GROUP BY Month(ComplainDates), Year(ComplainDates)

Here I am selecting the Month and Year part of the complain date and placing them in their own columns and then adding a COUNT of the complaints. Then I group by the month and year. This would provide the following result:

Code:

Month    Year    NoComplaints
1        2001    2
2        2001    2
3        2001    2

At the client you could convert the month column into a string (e.g. 1 = Jan) and concatenate the year (e.g. Jan-2001.)

Regards
Owain Williams

singh_ginni · September 8th, 2003, 09:28 PM

Thanks Owain,
Yes, this solution works well. I tried this out.There is also one more solution which i got, and would like to share with all of you. The solution is as follows.

SELECT CONVERT(CHAR(6), CASE WHEN @startDate > yrMth THEN @startDate ELSE yrMth END, 107) + ' - ' +
     CONVERT(CHAR(6), CASE WHEN @endDate < DATEADD(DAY, -1, DATEADD(MONTH, 1, yrMth))
         THEN @endDate ELSE DATEADD(DAY, -1, DATEADD(MONTH, 1, yrMth)) END, 107) AS [Dates],
    [count] AS [No of unique complaints]
FROM (
    SELECT CAST(CONVERT(CHAR(7), startDate, 120) + '-01' AS SMALLDATETIME) AS yrMth,
          COUNT(DISTINCT complaintId) AS [count]
    FROM orderlist
    WHERE startDate BETWEEN @startDate AND @endDate
    GROUP BY CAST(CONVERT(CHAR(7), startDate, 120) + '-01' AS SMALLDATETIME)
) AS derived

Once thanks to you all who tried to solve this problem.

Regards,
Ginni