Database

CPallini wrote:

(Now I suppose you know what to do with "Fourth Normal Form").

/* create test table and populate */

 

create table UserArea(Country varchar(20))

insert into UserArea
select 'India'
union all
select 'USA'
union all
select 'India'
union all
select 'UK'


/* now the actual code */

DECLARE @SQL nvarchar(4000)

SET @SQL=''   

SELECT @SQL= @SQL +

'SUM(CASE WHEN Country=''' + a.Country + ''' THEN 1 ELSE 0 END) AS [' + a.Country + '],'

FROM (select distinct Country from UserArea) as a

select @SQL = left(@SQL,len(@SQL)-1)

SET @SQL='SELECT ' + @SQL + ' FROM UserArea'

EXEC(@SQL)

/* tidy up */

drop table  UserArea

select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Calls]<br />
from status join empmaster on empmaster.empid = status.empcode<br />
where empmaster.Permission <> '1' and logintime between '06/02/2006' and '06/03/2006' <br />
group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by empcode asc<br />
<br />
UNOIN<br />
<br />
select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Contacts]<br />
from status join empmaster on empmaster.empid = status.employeecode<br />
where empmaster.Permission <> '1' and logintime between '06/02/2008' and '06/03/2008' and<br />
statuscode not in (11)group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by emp asc