July 26, 2006 at 7:45 pm
Hi all,
I need your idea/suggestion. I don’t really know how to explain it well
Hope you can understand with my sample below.
I create a store proc and run it for every quarter ( because the data will be available quarterly)
Right now my store proc is just taken 2 date para ( from and To)
I want to create a report which looks like below
Assuming today I run it for Jan-Mar,
Time Frame | nurseHome | Allied health | Accomo | Travel |
Jan – Mar Q1 | 45 | 5 | 10 | 5 |
April – Jun Q2 | 0 | 0 | 0 | 0 |
Jul – Sep Q3 | 0 | 0 | 0 | 0 |
Oct-Dec Q4 | 0 | 0 | 0 | 0 |
Total | 45 | 5 | 10 | 5 |
then when next quarter I run for April-June
Time Frame | nurseHome | Allied health | Accomo | Travel |
Jan – Mar Q1 | 45 | 5 | 10 | 5 |
April – Jun Q2 | 15 | 10 | 15 | 8 |
Jul – Sep Q3 | 0 | 0 | 0 | 0 |
Oct-Dec Q4 | 0 | 0 | 0 | 0 |
Total | 60 | 15 | 25 | 13 |
And so on till Q4
Can I use Reporting service?
How do I do it? What sort of store proc template I should use?
Thanks,
Susan
July 27, 2006 at 6:43 am
Hi ,
I Understand your posting but i need some more information to solve your problem
This Scenario is enough for how the report should look like But it is not enough to Solve your problem on the functionality
How is the data returned by stored procedure.
Is it is returning data for all Quarters?
There would be no Problem if data is not there for other Quarters
Give me a sample data how the Sp is returning the data.
I hope Reporting services has flexibility to do what you want
You can use IIF Conditions,Filters....
Thanks and regards
Raj deep.A
July 27, 2006 at 4:11 pm
Thanks for replying my email.
here what I mean
Q1,Q2,Q3,Q4 need to be taken from the visit date of the client
So for example
Q1 is all clients who have visit date between (1/1/06 -31/3/06)
Q2 is all clients who have visit date between (1/4/06 -30/6/06)
Q3 is all clients who have visit date between (1/7/06 -30/9/06)
Q4 is all clients who have visit date between (1/10/06 -31/12/06)
I thought I need to put in where.
I reckon there would be no problem if data is not there for other quarters untill the time comes.
Here the sample of my query. If it's possible I would like to write this as Store Proc
SELECT
CEILING(SUM(CASE
when se.[Service Type Code] IN (1)
then se.[Numeric Value]
ELSE 0 END)/60) AS '001 Domestic Assistance',
SUM(CASE
when se.[Service Type Code] IN (1)
then v.TravHour
ELSE 0 END)AS TravHour1,
SUM(CASE
when se.[Service Type Code] IN (1)
then v.TravKm
ELSE 0 END)AS TravKm1,
(CASE
when se.[Service Type Code] IN (1)
then count(distinct v.CNo)
ELSE 0 END)AS TotCli1
FROM [Service Entry] se left join VW_ActVisitClient1 v
on v.CNo =se.[Client No_]
and v.AdmitNo = se.[Admission No_]
and v.VisitNo =se.[Visit No_]
inner join [Service Type] st on se.[Service Type Code] = st.Code
WHERE
where VDate1 between '2006/04/01' and '2006/06/30'
and Proj ='81'
GROUP BY [Service Type Code]
Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply