Matrix like report in ASP or SQL

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

SQL Server Development (2000)

Matrix like report in ASP or SQL

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-09 : 06:34:02

hi,

i have two tables clients and fileinfo. client contains clientid and file info contain data uploaded by client with respect to date.

my result should be like that.

Client last month jan feb mar apr ..... Dec
11 01-jun-2004 y
14 01-mar-2004 y
15 01-dec-2003 N
16 01-jan-2004 y

client id and his last added record date and keep show result per month from jan to dec. if client uploaded data on that month then it mark other wise dont.

how i can achieve this either through asp or sql.

regards

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-09 : 08:45:22

Here are some possible solutions:

quote:

Declare @clientLoads table (ClientId int, loadDate datetime)
Insert Into @clientLoads
Select ClientId = 132, loadDate = getdate()
Union all Select ClientId = 132, loadDate = getdate()-17
Union all Select ClientId = 132, loadDate = getdate()-17-28
Union all Select ClientId = 132, loadDate = getdate()-17-28-35
Union all Select ClientId = 132, loadDate = getdate()-17-28-35-55
Union all Select ClientId = 132, loadDate = getdate()-17-28-35-55-57
Union all Select ClientId = 132, loadDate = getdate()-17-28-35-55-57-30

Select * from @ClientLoads

--If you always want a rolling year:

Declare @seed table (seed int)
Insert Into @seed Select Seed = 1 Union All Select Seed = 2 Union All Select Seed = 3 Union All Select Seed = 4 Union All Select Seed = 5 Union All Select Seed = 6 Union All
Select Seed = 7 Union All Select Seed = 8 Union All Select Seed = 9 Union All Select Seed = 10 Union All Select Seed = 11 Union All Select Seed = 12

Select
A.ClientId,
B.CurMonth,
LoadedInfo = max(Case when C.LoadDate is not null then 1 else 0 end),
LastLoad = max(C.LoadDate)
From (Select Distinct ClientId From @ClientLoads) as A, (Select curMonth = dateadd(mm,-seed,convert(datetime,convert(nvarchar,dateadd(dy,-day(getdate())+1,getdate()),101))) From @seed) as B
Left Join @clientLoads as C
On month(B.curMonth) = month(C.LoadDate)
and year(B.curMonth) = year(C.LoadDate)
Group By A.ClientId, B.CurMonth
Order By B.curMonth

--You finish with a while loop in asp

--If you always want jan - dec then:
Select
ClientId,
LoadYear,
LastUpload = (Select max(loadDate) From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear),
Jan = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=1) then 1 else 0 end,
Feb = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=2) then 1 else 0 end,
Mar = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=3) then 1 else 0 end,
Apr = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=4) then 1 else 0 end,
May = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=5) then 1 else 0 end,
Jun = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=6) then 1 else 0 end,
Jul = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=7) then 1 else 0 end,
Aug = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=8) then 1 else 0 end,
Sep = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=9) then 1 else 0 end,
Oct = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=10) then 1 else 0 end,
Nov = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=11) then 1 else 0 end,
Dec = case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=12) then 1 else 0 end
From (Select Distinct ClientId, LoadYear = year(loadDate) From @ClientLoads) as A

Corey

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-10 : 03:26:04

how to count no of months has 1
like this

clientid year total jan feb mar .... dec
11 2004 2 1 1 0 0 0 ... 0

thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-10 : 07:19:13

for the jan to dec example:

total = (Select count(*) From (Select Distinct ClientId, lMonth = month(LoadDate), lYear = Year(LoadDate) From @ClientLoads) as blah Where ClientId = A.ClientId and lYear = A.LoadYear)

Corey

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-16 : 02:24:32

hi, little bit change in the query i have to just show month which are marked, means which have data available.

like

client id total months
547 2 jan -feb
2477 3 feb mar apr

how i do that

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 08:21:09

--If you always want jan - dec then:
Select
ClientId,
LoadYear,
LastUpload = (Select max(loadDate) From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear),
Months =
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=1) then 'Jan ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=2) then 'Feb ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=3) then 'Mar ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=4) then 'Apr ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=5) then 'May ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=6) then 'Jun ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=7) then 'Jul ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=8) then 'Aug ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=9) then 'Sep ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=10) then 'Oct ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=11) then 'Nov ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=12) then 'Dec ' else '' end
From (Select Distinct ClientId, LoadYear = year(loadDate) From @ClientLoads) as A

Corey

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-17 : 01:57:42

thx, one more thing how to give serial no in the result. i have change the table like

Declare @clientLoads table (S_No INT IDENTITY(1,1),ClientId int, loadDate datetime)
Insert Into @clientLoads

select caclientsloginid, creationdate
FROM backpdfinfo
WHERE (upload_date BETWEEN CONVERT(DATETIME, '2004-06-01') AND
CONVERT(DATETIME, '2004-06-30')) and year(creationdate) = '2004'
order by caclientsloginid

select * from @clientloads

it shows the serial no, when i try to add

Select
S_No 'error on this line
ClientId,
LoadYear,
.....

it give me error.
kindly tell me how to add serial no i the result.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 08:14:51

what error does it give you?? is it because you don't have a comma after S_No?

Select
S_No,
ClientId,
LoadYear,
...

Also if you are using a group by you may need to add S_No to the group by clause.

Corey

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-18 : 00:26:00

i have done it by using IDENTITY in temp table. thanks for ur reply.

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-26 : 03:31:00

hi, some new changes.
in client table one more field is added which is noofmonths.
if user uploaded in may-2004 then user can select no of months data in that file. now i need to calculate and how the no of months uploaded. qry result like this.

noofmonths
1 547 2 3 2004 Apr May
2 604 1 2 2004 Apr
3 966 1 12 2003 Dec
4 966 1 1 2004 Apr
5 1010 1 5 2003 Dec

noofmonths filed is in clienttable.

thx

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-26 : 19:08:41

use your current result as a subquery and count the length of
the months field...

Select *,
noOfMonths = len(Months+' ')/4
From
(
<current query>
) A

Corey

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-28 : 00:25:42

hi, this query in not giving me desire result.

Declare @clientLoads table (S_No INT IDENTITY(1,1),ClientId int, loadDate datetime,noofmonths int)
Insert Into @clientLoads

select caclientsloginid, creationdate,noofmonths
FROM backpdfinfo
WHERE (upload_date BETWEEN CONVERT(DATETIME, @s_date) AND
CONVERT(DATETIME, @e_date))
order by caclientsloginid

select * from @clientloads
select sum(noofmonths),clientid from @clientloads group by clientid

i want this query result in my final result query how i can achieve this result with my final query.

thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-28 : 08:37:16

Use this with the sample data set i provided earlier...

Select
* ,
NoOfMonths = (Len(Months)+1)/4
From
(
Select
ClientId,
LoadYear,
LastUpload = (Select max(loadDate) From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear),
Months =
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=1) then 'Jan ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=2) then 'Feb ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=3) then 'Mar ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=4) then 'Apr ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=5) then 'May ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=6) then 'Jun ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=7) then 'Jul ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=8) then 'Aug ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=9) then 'Sep ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=10) then 'Oct ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=11) then 'Nov ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=12) then 'Dec ' else '' end
From (Select Distinct ClientId, LoadYear = year(loadDate) From @ClientLoads) as A
) as Z

Corey

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-29 : 00:23:40

hi, thx but i have done it in other way. Now the complete query is this.

Declare @s_date as datetime
Declare @e_date as datetime

set @s_date ='2004-06-01'
set @e_date= '2004-06-30'

Declare @clientLoads table (S_No INT IDENTITY(1,1),ClientId int, loadDate datetime,noofmonths int)
Insert Into @clientLoads

select caclientsloginid, creationdate,noofmonths
FROM backpdfinfo
WHERE (upload_date BETWEEN CONVERT(DATETIME, @s_date) AND
CONVERT(DATETIME, @e_date))
order by caclientsloginid

Declare @seed table (seed int)
Insert Into @seed Select Seed = 1 Union All Select Seed = 2 Union All Select Seed = 3 Union All Select Seed = 4 Union All Select Seed = 5 Union All Select Seed = 6 Union All
Select Seed = 7 Union All Select Seed = 8 Union All Select Seed = 9 Union All Select Seed = 10 Union All Select Seed = 11 Union All Select Seed = 12

Declare @results table (S_No INT IDENTITY(1,1),ClientId int, total int,noofmonths int ,Year varchar(5), Months varchar(25))
Insert Into @results
Select
ClientId,
total = (Select count(*) From (Select Distinct ClientId, lMonth = month(LoadDate), lYear = Year(LoadDate) From @ClientLoads) as blah Where ClientId = A.ClientId and lYear = A.LoadYear),
noofmonths =( select sum(noofmonths) from @clientloads where clientid = A.clientid and Year(LoadDate) = A.LoadYear group by A.clientid ),
Loadyear,
Months =
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=1) then 'Jan ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=2) then 'Feb ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=3) then 'Mar ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=4) then 'Apr ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=5) then 'May ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=6) then 'Jun ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=7) then 'Jul ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=8) then 'Aug ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=9) then 'Sep ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=10) then 'Oct ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=11) then 'Nov ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=12) then 'Dec ' else '' end
From (Select Distinct ClientId,noofmonths,LoadYear = year(loadDate) From @ClientLoads) as A
select * from @results

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-30 : 06:23:37

some problem following qry just showing me one duplicate record other are fine.

Declare @s_date as datetime
Declare @e_date as datetime
set @s_date = '2004-06-01'
set @e_date = '2004-06-30'

Declare @clientLoads table (S_No INT IDENTITY(1,1),ClientId int, loadDate datetime,noofmonths int)
Insert Into @clientLoads

select caclientsloginid, creationdate,noofmonths
FROM backpdfinfo
WHERE (upload_date BETWEEN CONVERT(DATETIME, @s_date) AND
CONVERT(DATETIME, @e_date))
order by caclientsloginid

--select * from @clientLoads

Declare @seed table (seed int)
Insert Into @seed Select Seed = 1 Union All Select Seed = 2 Union All Select Seed = 3 Union All Select Seed = 4 Union All Select Seed = 5 Union All Select Seed = 6 Union All
Select Seed = 7 Union All Select Seed = 8 Union All Select Seed = 9 Union All Select Seed = 10 Union All Select Seed = 11 Union All Select Seed = 12

Declare @results table (S_No INT IDENTITY(1,1),ClientId int, total int,noofmonths int ,Year varchar(5), Months varchar(25))
Insert Into @results
Select
ClientId,
total = (Select count(*) From (Select Distinct ClientId, lMonth = month(LoadDate), lYear = Year(LoadDate) From @ClientLoads) as blah Where ClientId = A.ClientId and lYear = A.LoadYear),
noofmonths =( select sum(noofmonths) from @clientloads where clientid = A.clientid and Year(LoadDate) = A.LoadYear group by A.clientid ),
Loadyear,
Months =
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=1) then 'Jan ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=2) then 'Feb ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=3) then 'Mar ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=4) then 'Apr ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=5) then 'May ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=6) then 'Jun ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=7) then 'Jul ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=8) then 'Aug ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=9) then 'Sep ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=10) then 'Oct ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=11) then 'Nov ' else '' end +
case when exists(Select * From @ClientLoads Where ClientId = A.ClientId and Year(LoadDate) = A.LoadYear and month(LoadDate)=12) then 'Dec ' else '' end
From (Select distinct ClientId,noofmonths,LoadYear = year(loadDate) From @ClientLoads) as A
select * from @results

kindly chk it and tell me where is the problem.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-30 : 13:05:24

What is being duplicated... show the query results.

or the entire script (including creating and filling table variable)

Corey

imughal
Posting Yak Master

192 Posts

Posted - 2004-07-01 : 00:11:32

hi,

only one record was duplicating when i try to rewrite your query from the scratch then it's start working fine without any duplication.
i don't know wht was wrong but atlast it's working fine. thx for u.

iffi

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-01 : 08:20:43

no problem

Glad its workin'

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources