SQL server 2008 month and year logic

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 2008 Forums

Transact-SQL (2008)

SQL server 2008 month and year logic

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-11 : 06:13:27

Dear all

i have to create 1 report based on date and year basis report

my table structure

empno date1
10 2012-Apr-10
11 2012-Apr-20
12 2012-Apr-25
13 2012-May-02
14 2012-May-04
15 2012-May-06
16 2012-May-08
17 2012-May-10
18 2012-May-10

here i want to calculate the March month employees to dec employees if any incremental or decremental should calculate to evrymonth

expected out put is
jan :200
feb :234
mar :298
apr :310
may :267
june :222
july :430
................

anybody know the way pls explain the logic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:46:09

is 234 total count of employees for Feb? Or is it number of new employees who joined compared to Jan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-12 : 08:41:02

I hope this is what you are looking for:


--Creating Table

Create Table Ex
(empno int,
 date1 Varchar(30) )


--Inserting Sample Data
 
Insert into Ex
Select 10, '2012-Apr-10'
Union ALL
Select 11, '2012-Apr-20'
Union ALL
Select 12, '2012-Apr-25'
Union ALL
Select 13, '2012-May-02'
Union ALL
Select 14, '2012-May-04'
Union ALL
Select 15, '2012-May-06'
Union ALL
Select 16, '2012-May-08'
Union ALL
Select 17, '2012-May-10'
Union ALL
Select 18, '2012-May-10'


--Query For Your requirement

Select Distinct SUBSTRING(date1, CHARINDEX('-', Date1) + 1, 3) +':'+ Cast(Count(empno) Over (Partition By DatePart(MM,date1) ) As Varchar(30) )
As EmployeeCount From Ex

A few things:

1. Date should be stored As Date and not a String.
2. If this is not what you are looking for then please elaborate on what the requirement is.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources