March 24, 2010 at 3:59 am
Dear all, please help me. I have a select query that currently produces the following results:
Name | Team |Visit_Date
--------------------
dr. As A 5
dr. Sc A 4
dr. Gh B 6
dr. Nd C 31
dr. As A 7
Using the following query:
SELECT d.DoctorName, t.TeamName, ca.VisitDate FROM cActivity AS ca INNER JOIN doctor AS d ON ca.DoctorId = d.Id INNER JOIN team AS t ON ca.TeamId = t.Id WHERE ca.VisitDate BETWEEN '1/1/2010' AND '1/31/2010'
I want to produce the following in reporting services:
DoctorName Team 1 2 3 4 5 6 7 ... 31 Visited
dr. As A x x ... 2 times
dr. Sc A x ... 1 times
dr. Gh B x ... 1 times
dr. Nd C ... x 1 times
I also want the column of date are dynamic. if it is between 2/1/2010 and 2/28/2010 in reporting services will be :
DoctorName Team 1 2 3 4 5 6 7 ... 28 Visited
dr. As A x x ... 2 times
dr. Sc A x ... 1 times
dr. Gh B x ... 1 times
dr. Nd C ... x 1 times
Can anyone help me? I am newbie in sql and reporting service
March 24, 2010 at 6:36 am
You can use tally table and pivot to get the output mentioned. I don't have much time to give you a query as of now. I could probably post a query tomorrow. Check up these links and try.
Tally:
http://wvmitchell.blogspot.com/2009/10/tally-tables.htm
http://www.sqlservercentral.com/articles/T-SQL/62867/
Pivot:
http://www.mssqltips.com/tip.asp?tip=1019
http://msdn.microsoft.com/en-us/library/ms177410.aspx
Hints:
Do left outer join with tally table to get all the dates.
Then you can do a pivot to get the output.
-Arjun
https://sqlroadie.com/
March 24, 2010 at 1:11 pm
hi try this
DECLARE @Month TABLE (Names Varchar(50),Team varchar(5),[1] SMALLINT ,[2] SMALLINT ,[3] SMALLINT ,[4] SMALLINT ,[5] SMALLINT ,[6] SMALLINT ,[7] SMALLINT ,[8] SMALLINT ,[9] SMALLINT ,[10] SMALLINT ,[11] SMALLINT ,[12] SMALLINT ,[13] SMALLINT ,[14] SMALLINT ,[15] SMALLINT ,[16] SMALLINT ,[17] SMALLINT ,[18] SMALLINT ,[19] SMALLINT ,[20] SMALLINT ,[21] SMALLINT ,[22] SMALLINT ,[23] SMALLINT ,[24] SMALLINT ,[25] SMALLINT ,[26] SMALLINT ,[27] SMALLINT ,[28] SMALLINT ,[29] SMALLINT ,[30] SMALLINT ,[31] SMALLINT , TotVisited INT)
DECLARE @Your Table (Rid Int identity ,Names Varchar(50),Team varchar(5),Visit_date VARCHAR(20))
DECLARE @Temp Table (Cnt INT,Names Varchar(50))
Insert into @Your
Select 'A','A','03/03/2010'
union Select 'AA','A','03/12/2010'
union Select 'AAA','A','03/28/2010'
union Select 'B','B','03/09/2010'
union Select 'BB','B','03/17/2010'
union Select 'BB','B','03/07/2010'
union Select 'BBB','B','03/29/2010'
union Select 'C','C','03/24/2010'
union Select 'CC','C','03/11/2010'
union Select 'CC','C','03/25/2010'
union Select 'CC','C','03/28/2010'
union Select 'CC','C','03/31/2010'
In this i have created a temp table as per you u have the join O/P
Using the following query:
SELECT d.DoctorName, t.TeamName, ca.VisitDate FROM cActivity AS ca INNER JOIN doctor AS d ON ca.DoctorId = d.Id INNER JOIN team AS t ON ca.TeamId = t.Id WHERE ca.VisitDate BETWEEN '1/1/2010' AND '1/31/2010'
I have kept as @Your
After the join condition
INSERT INTO @Month
Select distinct Names,Team,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from @Your
why i am putting distinct here is to insert the entries without duplicates so that only one entry will be there in @month table
Meaning that there will not be more than one entry for each Doctor for that paticular day [Say 25 march 2010 Mr.A has come so there will be only one entry in that join condition]
Conditions you need to do:
1) You should not be able to view past and present month together
2) Doctor Name sholud be unique
I want to produce the following in reporting services:
DoctorName Team 1 2 3 4 5 6 7 ... 31 Visited
dr. As A x x ... 2 times
dr. Sc A x ... 1 times
dr. Gh B x ... 1 times
dr. Nd C ... x 1 times
Here comes the o/p
DECLARE @I INT, @Day INT,@Names Varchar(200)
set @i=1
while @i<=(Select Count(*) From @Your)
BEGIN
select @Names =Names ,@Day=day(Visit_date ) from @Your Where Rid=@I
IF @Day=1 update @Month set [1]=1 WHERE Names=@Names
IF @Day=2 update @Month set [2]=1 WHERE Names=@Names
IF @Day=3 update @Month set [3]=1 WHERE Names=@Names
IF @Day=4 update @Month set [4]=1 WHERE Names=@Names
IF @Day=5 update @Month set [5]=1 WHERE Names=@Names
IF @Day=6 update @Month set [6]=1 WHERE Names=@Names
IF @Day=7 update @Month set [7]=1 WHERE Names=@Names
IF @Day=8 update @Month set [8]=1 WHERE Names=@Names
IF @Day=9 update @Month set [9]=1 WHERE Names=@Names
IF @Day=10 update @Month set [10]=1 WHERE Names=@Names
IF @Day=11 update @Month set [11]=1 WHERE Names=@Names
IF @Day=12 update @Month set [12]=1 WHERE Names=@Names
IF @Day=13 update @Month set [13]=1 WHERE Names=@Names
IF @Day=14 update @Month set [14]=1 WHERE Names=@Names
IF @Day=15 update @Month set [15]=1 WHERE Names=@Names
IF @Day=16 update @Month set [16]=1 WHERE Names=@Names
IF @Day=17 update @Month set [17]=1 WHERE Names=@Names
IF @Day=18 update @Month set [18]=1 WHERE Names=@Names
IF @Day=19 update @Month set [19]=1 WHERE Names=@Names
IF @Day=20 update @Month set [20]=1 WHERE Names=@Names
IF @Day=21 update @Month set [21]=1 WHERE Names=@Names
IF @Day=22 update @Month set [22]=1 WHERE Names=@Names
IF @Day=23 update @Month set [23]=1 WHERE Names=@Names
IF @Day=24 update @Month set [24]=1 WHERE Names=@Names
IF @Day=25 update @Month set [25]=1 WHERE Names=@Names
IF @Day=26 update @Month set [26]=1 WHERE Names=@Names
IF @Day=27 update @Month set [27]=1 WHERE Names=@Names
IF @Day=28 update @Month set [28]=1 WHERE Names=@Names
IF @Day=29 update @Month set [29]=1 WHERE Names=@Names
IF @Day=30 update @Month set [30]=1 WHERE Names=@Names
IF @Day=31 update @Month set [31]=1 WHERE Names=@Names
-- Select * from @Month
SET @I=@I+1
END
INSERT INTO @Temp
Select COUNT(*),Names from @Your group by Names
update @Month set [TotVisited]=Cnt from @Temp AS A ,@Month as B WHERE A.Names=B.Names
Select * from @Month
While Update in @Month Table u can have Docids so that update might be little bit fast than giving Names while in Join condition itself take Docsid
For Feb Month u create seperate @monthFeb table if month of Feb has been choosen goto that table else come to normal table
There may be another ways but as far as i tired i got this way if u feel it is better then u go else if u find better solution post here so that i would be nice all can get
Hope this would help u If u are not clear come back we are here to help you
Cheers
Parthi
Thanks
Parthi
March 25, 2010 at 12:32 am
Thank you for support, Arjun Sivadasan.
Yes I thinking about using Pivot do you have any link suggestions? I 'm still learning about Pivot. If you don't mind may you could give me some pseudo code for the query. Thanks a lot
March 25, 2010 at 12:38 am
Dear Parthi,
Thank you for you help but I still found that the 29/30/31 column are not change dynamically. Thank you for your help but I think there must be a pivot query for this but I don't know how to. I'm looking forward for your help in Pivot query if you don't mind. Thanks a lot
March 25, 2010 at 1:04 am
Check out the following query to get a hang of pivot. It is part of a solution that i suggested for a post.
When you run it, you will get to see the table as such and the pivoted data.
create table #t(customernumber varchar(10), notes varchar(10), lastvisitdate smalldatetime, nr int)
insert into #t values(100000, 'text 1', '10.feb.2010',1)
insert into #t values(100000, 'text 2', '10.feb.2010',2)
insert into #t values(100000, 'text 3', '10.feb.2010',3)
select * from #t
select piv.customernumber, lastvisitdate,
notes1 = piv.[1],
notes2 = piv.[2],
notes3 = piv.[3]
from
(
select customernumber, lastvisitdate, notes, nr
from #t
) rows
pivot
(
max(notes) for
nr in ([1],[2],[3])
)piv;
drop table #t
https://sqlroadie.com/
March 25, 2010 at 1:14 am
Dear Arjun Sivadasan,
Thank you so much. I going to try the pivot on my query.
March 25, 2010 at 1:18 am
Cool, just let us know if you face any roadblock.
-Arjun
https://sqlroadie.com/
March 26, 2010 at 2:19 am
Dear Arjun,
I have learn how to make pivot query and now I have make it the pivot query to run dynamic column now. Thank you so much. But I have a road block when trying to display the query in reporting service. Can please you help me to show how to configure the matrix column for pivot column?
March 26, 2010 at 3:04 am
Can you post some data?
The input data as you get from DB and the output as you want to display it in SSRS.
- arjun
https://sqlroadie.com/
March 26, 2010 at 3:22 am
here there result that I get from db:
when numberofday = 28
DoctorName Team 1 2 3 4 5 6 7 ... 28 Visited
dr. As A x x ... 2 times
dr. Sc A x ... 1 times
dr. Gh B x ... 1 times
dr. Nd C ... x 1 times
when numberofday = 30
DoctorName Team 1 2 3 4 5 6 7 ... 30 Visited
dr. As A x x ... 2 times
dr. Sc A x ... 1 times
dr. Gh B x ... 1 times
dr. Nd C ... x 1 times
How to set the column group in matrix table so its column will be same as result in pivot query
March 26, 2010 at 3:39 am
I am guessing here, aided by faint memory from a past experience. So you may want to check this with someone who is better equipped.
If you need to display it this way (as below) in SSRS, you should not pivot the data in DB. You should rather get it in a format that can be pivoted and do group by date in report.
DoctorName Team 1 2 3 4 5 6 7 ... 28 Visited
dr. As A x x ... 2 times
dr. Sc A x ... 1 times
dr. Gh B x ... 1 times
dr. Nd C ... x 1 times
You may need data from DB in the following format.
Name |Team| Date |HasVisited
dr.As | A | 1 Mar 2010|Yes
dr.As | A | 2 Mar 2010|No
|
|
|
|
dr.As | A | 23 Mar 2010|Yes
dr.As | A | 24 Mar 2010|No
|
|
|
|
dr.As | A | 31 Mar 2010|No
and so on...
-arjun
https://sqlroadie.com/
March 26, 2010 at 3:47 am
Dear Arjun,
Well, I still don't understand. If I should got that result like that from DB then I don't understand why I got to pivot that query?
Is there any way to make the column group dynamic according the pivot result?
March 26, 2010 at 3:58 am
Chandradyani (3/26/2010)
Dear Arjun,Well, I still don't understand. If I should got that result like that from DB then I don't understand why I got to pivot that query?
Is there any way to make the column group dynamic according the pivot result?
I am sorry. I think i was leading you in the wrong direction here. I was looking at the output you needed and totally forgot that it was in SSRS.
AFAIK, you cannot make column group dynamic in SSRS.
ie, in some cases, you will have 28 + (others) columns
and in other cases, 31 + (others) columns. I don't think this is possible.
So, you will have to group unpivoted data by the date in your report so that it would appear pivoted on the date.
However, I am not very sound in SSRS. So, please check with someone else. You may post the same question in Reporting forum and refer to this thread.
https://sqlroadie.com/
March 26, 2010 at 4:16 am
Dear Arjun,
It's Ok. Thank you so much for your help.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply