Reg: Weekly - 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)

Reg: Weekly - Monthly Report

Author

Topic

maran_mail
Starting Member

5 Posts

Posted - 2008-04-05 : 05:11:53

Hi All,

I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.

Input values:

Start Date: 2/17/2008
End Date : 5/8/2008

Output Format:

2/17/08 - 2/29/08 (Partial Month) 12
3/1/08 - 3/31/08 (Full month) 0
4/1/08 - 4/30/08 (Full month) 22
5/1/08 - 5/8/08 (Full month) 10

I want the above format of the monthly report. I really could use some help on this. thanks.

~ Maran

Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 05:42:59

Use Recursive CTE. SOmething like:-

;
With Date_CTE (Seq,DateValue) AS
(SELECT 0,@StartDate,NULL

UNION ALL

SELECT Seq+1,DATEADD(d,(-1) * DATEADD(d,1,DATEPART(dd,DATEADD(m,1,DateValue))),DATEADD(m,1,DateValue))
FROM Date_CTE
WHERE DateValue < @EndDate
)

SELECT CONVERT(varchar(11),t1.DateValue,101) + '-' + CONVERT(varchar(11),DATEADD(d,-1,t2.DateValue),101) FROM Date_CTE t1
INNER JOIN Date_CTE t2 
ON t1.DateValue <> t2.DateValue
AND t1.Seq=t2.Seq-1

maran_mail
Starting Member

5 Posts

Posted - 2008-04-05 : 07:16:32

Hi.... This is the following report format.
Kindly give the needful help. Thank you.

Currently I am using the Microsoft SQL Server 2005 version..

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

CompanyHistorytrackTable

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

Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 14:21:35

I'm sure somebody will come with better approach. This method calaculates count correctly but code to get reqd. display format is a bit clumsy.


SELECT CASE 
WHEN MONTH(changed_date)=MONTH(@StartDate) AND YEAR(changed_date)=YEAR(@StartDate) THEN CAST(DATEPART(dd,@StartDate) AS varchar(2)) + '/'
ELSE'01/' END+ 
CASE 
WHEN MONTH(changed_date) < 10 THEN '0'+ CAST(MONTH(changed_date) AS varchar(1)) 
ELSE CAST(MONTH(changed_date) AS varchar(2))
END + YEAR(changed_date) + ' - '
+ CASE WHEN DATEADD(d,-1,DATEADD(mm,1,CAST((01/' + 
CASE 
WHEN MONTH(changed_date) < 10 THEN '0'+ CAST(MONTH(changed_date) AS varchar(1)) 
ELSE CAST(MONTH(changed_date) AS varchar(2))
END + YEAR(changed_date)) AS datetime))) > @EndDate
THEN CAST(@EndDate AS varchar(10))
ELSE CAST(DATEADD(d,-1,DATEADD(mm,1,CAST((01/' + 
CASE 
WHEN MONTH(changed_date) < 10 THEN '0'+ CAST(MONTH(changed_date) AS varchar(1)) 
ELSE CAST(MONTH(changed_date) AS varchar(2))
END + YEAR(changed_date)) AS datetime)))AS varchar(10)),
COUNT(companyId)
FROM CompanyHistorytrackTable
WHERE changed_date>=@StartDate
AND changed_date<=@EndDate
GROUP BY YEAR(changed_date),MONTH(changed_date)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources