Monthly Report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Monthly Report

Author

Topic

maran_mail
Starting Member

5 Posts

Posted - 2008-04-15 : 01:04:35

Hi,

I m Maran. I am trying to write a SQL Query to retrieve the following report format. But I'm not sure how to go about it.

Input values:

Starting Date: 09/14/2007
End Date: 12/06/2007

Monthly Report :

Start Date - End Date - Number of companies
09/14/2007 - 09/30/2007 1
10/01/2007 - 10/31/2007 0
11/01/2007 - 11/30/2007 4
12/01/2007 - 12/06/2007 0

Please its very urgent, Plz do the needful help. Actually this same report format i was posted already and got some methodology, but its not satisfied my requirements :( :(

Used Table: CompanyHistorytrackTable

companyId changed_date
50198 2007-09-05 13:11:17.000
48942 2007-09-14 12:42:30.000
48945 2007-11-06 12:05:31.000
47876 2007-11-14 10:58:21.000
43278 2007-11-16 16:14:25.000
43273 2007-11-16 16:16:11.000
51695 2008-02-04 11:05:09.000
47876 2008-01-21 14:10:02.000
44604 2008-02-04 19:33:02.000
46648 2008-02-04 19:35:30.000

Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-04-15 : 03:29:57

hi
can u reply ur table structure and also wat u want with a simple example?

thanks

maran_mail
Starting Member

5 Posts

Posted - 2008-04-15 : 06:03:34

quote:
Originally posted by soorajtnpki

hi
can u reply ur table structure and also wat u want with a simple example?

thanks

Let us say that we want to generate lead generation report (monthly).

Input values are as follows:

Start Date: 09/14/2007
End Date : 12/06/2007

Monthly Report Format should display like this:

StartDate End Date No. of Leads
--------------------------------------------------------------
09/14/2007 09/30/2007 1
10/01/2007 10/31/2007 0
11/01/2007 11/30/2007 8
12/01/2007 12/06/2007 0

But I am retrieving the Report format, like the following:

StartDate End Date No. of Leads
--------------------------------------------------------------
09/14/2007 09/30/2007 1
11/01/2007 11/30/2007 8

I have used the Query for Retrieving the values from the above format:

SELECT
CASE
WHEN
MONTH(changed_date) = MONTH(CONVERT(VARCHAR,'09/14/2007',101)) AND YEAR(changed_date) = YEAR(CONVERT(VARCHAR,'09/14/2007',101))
THEN '09/14/2007'
ELSE
CONVERT (VARCHAR, MONTH(changed_date), 101) + '/' +
CONVERT (VARCHAR, MIN(DAY(CONVERT(VARCHAR, DateADD(mm,datediff(mm,0,changed_date),0), 101))), 101)
+ '/' + CONVERT (VARCHAR, YEAR(changed_date), 101)
END
AS StartDate,

CASE
WHEN
MONTH(changed_date) = MONTH(CONVERT(VARCHAR,'12/06/2007',101)) AND YEAR(changed_date) = YEAR(CONVERT(VARCHAR,'12/06/2007',101))
THEN '3/17/2008'
ELSE
CONVERT (VARCHAR, MONTH(changed_date), 101) + '/' +
CONVERT (VARCHAR, MAX(DAY(CONVERT(VARCHAR,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,changed_date)+1,0)),101))), 101)
+ '/' + CONVERT (VARCHAR, YEAR(changed_date), 101)
END
AS EndDate,

COUNT(company_id) AS NoOfLeads

FROM
companyhistorytrack
WHERE
CONVERT(VARCHAR(10), changed_date, 101) BETWEEN CONVERT(DATETIME,'09/14/2007', 101) AND CONVERT(DATETIME, '12/06/2007', 101) AND
( new_value = 'Lead' OR old_value = 'Lead' )
GROUP BY
MONTH(changed_date), YEAR(changed_date), DATEADD(MM, MONTH(changed_date),1)
ORDER BY
YEAR(changed_date)

Struck up with this Query:

CompanyHistoryTrack table is not having the 10th month and 12th month of 2007 datas. If this table not having any values for that corresponding month,
our report will show the starting month of the date and ending month of the date with the number of leads as 0.

Example:

10/01/2007 10/31/2007 0

12/01/2007 12/06/2007 0

Reference Table: CompanyHistoryTrack

SELECT
company_id, changed_date, old_value, new_value
FROM
companyhistorytrack
WHERE
CONVERT(VARCHAR(10), changed_date, 101) BETWEEN CONVERT(DATETIME,'09/14/2007', 101) AND CONVERT(DATETIME, '12/06/2007', 101) AND
( new_value = 'Lead' OR old_value = 'Lead' )
company_id changed_date new_value old_value
4894 2007-09-14 12:42:30.000 Ex-Customer Lead
4894 2007-11-06 12:05:31.000 Lead Most Prospective
47876 2007-11-14 10:58:21.000 Lead Most Prospective
43273 2007-11-16 16:14:25.000 Lead Most Prospective
43273 2007-11-16 16:16:11.000 Most Prospective Lead
44 2007-11-16 00:00:00.000 Accepted Data Lead
41063 2007-11-05 11:21:26.000 Lead Prospective
56491 2007-11-13 11:04:54.000 Lead Most Prospective
47897 2007-11-14 11:28:37.000 Lead Prospective

Please have a look on this. If you have any doubts regarding, please let me know. Thank you.

Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028

Artoo
Starting Member

16 Posts

Posted - 2008-04-15 : 11:24:34

Hi There,

OK you need a new lookup table, populated, and a stored procedure to run the report.

Firstly the new table, which looks like this:

CREATE TABLE MonthData (
	MonthID int NOT NULL,
	YearID int NOT NULL,
	StartDate datetime NOT NULL,
	EndDate datetime NOT NULL,
	CONSTRAINT pk_MonthData PRIMARY KEY CLUSTERED 
	(
		MonthID,
		YearID
	))

Then you need to populate this table. I have included what you need for 2007 and 2008. Moving forward you will need to add 12 rows per new year, and don't forget leap years.


INSERT INTO MonthData VALUES (1 , 2007, '01 Jan 2007', '31 Jan 2007')
INSERT INTO MonthData VALUES (2 , 2007, '01 Feb 2007', '28 Feb 2007')
INSERT INTO MonthData VALUES (3 , 2007, '01 Mar 2007', '31 Mar 2007')
INSERT INTO MonthData VALUES (4 , 2007, '01 Apr 2007', '30 Apr 2007')
INSERT INTO MonthData VALUES (5 , 2007, '01 May 2007', '31 May 2007')
INSERT INTO MonthData VALUES (6 , 2007, '01 Jun 2007', '30 Jun 2007')
INSERT INTO MonthData VALUES (7 , 2007, '01 Jul 2007', '31 Jul 2007')
INSERT INTO MonthData VALUES (8 , 2007, '01 Aug 2007', '31 Aug 2007')
INSERT INTO MonthData VALUES (9 , 2007, '01 Sep 2007', '30 Sep 2007')
INSERT INTO MonthData VALUES (10, 2007, '01 Oct 2007', '31 Oct 2007')
INSERT INTO MonthData VALUES (11, 2007, '01 Nov 2007', '30 Nov 2007')
INSERT INTO MonthData VALUES (12, 2007, '01 Dec 2007', '31 Dec 2007')
INSERT INTO MonthData VALUES (1 , 2008, '01 Jan 2008', '31 Jan 2008')
INSERT INTO MonthData VALUES (2 , 2008, '01 Feb 2008', '29 Feb 2008')
INSERT INTO MonthData VALUES (3 , 2008, '01 Mar 2008', '31 Mar 2008')
INSERT INTO MonthData VALUES (4 , 2008, '01 Apr 2008', '30 Apr 2008')
INSERT INTO MonthData VALUES (5 , 2008, '01 May 2008', '31 May 2008')
INSERT INTO MonthData VALUES (6 , 2008, '01 Jun 2008', '30 Jun 2008')
INSERT INTO MonthData VALUES (7 , 2008, '01 Jul 2008', '31 Jul 2008')
INSERT INTO MonthData VALUES (8 , 2008, '01 Aug 2008', '31 Aug 2008')
INSERT INTO MonthData VALUES (9 , 2008, '01 Sep 2008', '30 Sep 2008')
INSERT INTO MonthData VALUES (10, 2008, '01 Oct 2008', '31 Oct 2008')
INSERT INTO MonthData VALUES (11, 2008, '01 Nov 2008', '30 Nov 2008')
INSERT INTO MonthData VALUES (12, 2008, '01 Dec 2008', '31 Dec 2008')

Lastly here is the proc to do your monthly report


CREATE PROCEDURE MonthlyReport @Start datetime, @End datetime
AS
SELECT
	m.StartDate,
	m.EndDate,
	COUNT(c.CompanyID) AS NumberOfCompanies
FROM
	MonthData m
	LEFT JOIN Companies c
	ON DATEPART(yy, c.ChangedDate) = m.YearID 
	AND DATEPART(mm, c.ChangedDate) = m.MonthID
WHERE
	(
		(   m.YearID = DATEPART(yy, @Start) AND m.MonthID >= DATEPART(mm, @Start)    )
		OR (	m.YearID > DATEPART(yy, @Start)		) 
	)
	AND 
	(
		(	m.YearID = DATEPART(yy, @End) AND m.MonthID <= DATEPART(mm, @End)	)
		OR (	m.YearID < DATEPART(yy, @End)	)
	)
GROUP BY
	m.StartDate,
	m.EndDate
ORDER BY
	m.StartDate
GO

I hope this helps you. Good luck.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources