Hi guys...
My goal is to change the given stored procedure so that I can find out the different age gorup according to users parameter and find out sumof these values for that group:
s.TVmins, s.Notional$, COUNT(*) AS Qty, SUM(s.TVmins) AS TVMinsAmt, SUM(s.Notional$) AS NotionalAmt
For that I am planning to put another parameter @.count for the group interval and I need to group accordingly.
So my answer should look like:
if the user give the @.count value as 10:
the result should:
age group TVMins Notional
1-9 1560 125632( the sum of that particluar group)
10-19 -- --
91-100 --
I have a field DOB( Date of birth) , I have to extract age from that field first and then group them according to the parameter values and then find its corresponding sums...
<CODE>
--
ALTER PROCEDURE [dbo].[sp_PlanningData]
@.ProgrammeID numeric,
@.RegionID numeric,
@.SiteID numeric,
@.COCGroup varchar(50),
@.Provider varchar(50),
@.Schedule varchar(50),
@.StartDate datetime,
@.EndDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@.sql nvarchar(4000),
@.paramlist nvarchar(4000)
SELECT @.sql = 'SELECT
dm.DOB,
dm.Suburb,
vs.RID,
s.TVmins,
s.Notional$,
COUNT(*) AS Qty,
SUM(s.TVmins) AS TVMinsAmt,
SUM(s.Notional$) AS NotionalAmt
FROM dbo.lkpService s
INNER JOIN dbo.tmpValidServices_ALL vs ON s.Code = vs.Service
INNER JOIN dbo.tmpDemographics_ALL dm ON dm.RID = vs.RID '
IF @.COCGroup IS NOT NULL
SELECT @.sql = @.sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON vs.COC = c.pvcode '
IF @.ProgrammeID IS NOT NULL
SELECT @.sql = @.sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '
SELECT @.sql = @.sql + 'WHERE s.Schedule = @.Schedule '
IF @.StartDate IS NOT NULL
SELECT @.sql = @.sql + ' AND (vs.Complete >= @.StartDate ) '
IF @.EndDate IS NOT NULL
SELECT @.sql = @.sql + ' AND (vs.Complete <= @.EndDate ) '
IF @.ProgrammeID IS NOT NULL
SELECT @.sql = @.sql + ' AND (ag.AgencyTypeID = @.ProgrammeID)'
IF @.SiteID IS NOT NULL
SELECT @.sql = @.sql + 'AND (ag.EXACT# = @.SiteID) '
IF @.COCGroup IS NOT NULL
SELECT @.sql = @.sql + ' AND (c.pvcode = @.COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @.COCGroup)) '
IF @.Provider IS NOT NULL
SELECT @.sql = @.sql + 'AND (vs.Provider = @.Provider) '
SELECT @.sql = @.sql + 'GROUP dm.Suburb,vs.RID, s.TVmins, s.Notional$ '
SELECT @.paramlist =
' @.ProgrammeID numeric,
@.RegionID numeric,
@.SiteID numeric,
@.COCGroup varchar(50),
@.Provider varchar(50),
@.Schedule varchar(50),
@.StartDate datetime,
@.EndDate datetime '
EXEC sp_executesql @.sql,@.paramlist,@.ProgrammeID,@.RegionID,@.SiteID,@.COCGroup,@.Provider,@.Schedule,@.StartDate,@.EndDate
END
-
</CODE>
Hope this will help.. it is really urgent one.. I am trying my best to find it out..
Thanks for your help..
This will give you the range.
Create Table #Temp(Interval int, MyRange varchar(20))
DECLARE @.MyNewInterval int
DECLARE @.MyNewRange varchar(max)
DECLARE @.Interval int
SET @.INTERVAL = 10 --SET YOUR INTERVAL HERE OR PASS IT IN
SET @.MyNewInterval = 0
While @.MyNewInterval < (1000 + @.Interval)
BEGIN
INSERT INTO #Temp(Interval, MyRange) VALUES(@.MyNewInterval, @.MyNewInterval + @.Interval)
SET @.MyNewInterval = @.MyNewInterval + @.INTERVAL
END
SELECT cast(Interval as varchar(50)) + ' - ' + MyRange AS [Range]
FROM #Temp
drop table #temp
UNION your aggregates and you'll have it
Adamus
|||
Hi,
I can create this range, but how can put it into the proc so that I can get the sum values other fields..
Your help is highly appreciation,
|||
UNION your aggregates
Adamus
|||
Hi,
I could not get u, How can I do that.. could you explain a bit more please..
|||
Here's a method that I think can work for you. (it seems to work anyway)
It allows you to use the size of an interval as a parameter, so that you'll group your ages by, say 10-year groups if you pass in '10', and also lets you extract other values that you can sum, count etc...
Assuming you have a dob in the format of ssyymmdd, then this is a way to calculate the current age:
select cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as age
(the cast to int is because we need an int later...)
The idea here is to use MOD as the indicator for how the ages should be grouped.
If we say that we set 10 as the range size, then for each age, the lower bound would be:
age - (age % 10)
and the higher bound would be:
age - (age % 10) + 10
To start, then, you could select the values you want to sum, along with the calculated age.
select dob,
val1,
val2,
cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as age
from test
Use this is a derived table and find out the low and high ranges for the selected grouping:
declare @.mod int
set @.mod = 10
select x.val1,
x.val2,
x.age - (x.age % @.mod) as lowrange,
(x.age - (x.age % @.mod)) + @.mod as highrange
from (
select dob,
val1,
val2,
cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as age
from test
) x
Now we can wrap this into yet another derived table and do the final grouping and sum up the values for each group.
It's not entirely necessary to wrap further out, but it keeps the code a bit more readable, since we can group by name instead of repeating the algorithms for low and high..
So the final construct should look something like this;
declare @.mod int
set @.mod = 10
select y.lowrange,
y.highrange,
sum(y.val1) as val1Sum,
sum(y.val2) as val2Sum,
count(*)
from (
select x.val1,
x.val2,
x.age - (x.age % @.mod) as lowrange,
(x.age - (x.age % @.mod)) + @.mod as highrange
from (
select dob,
val1,
val2,
cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as age
from test
) x
) y
group by y.lowrange, y.highrange
order by y.lowrange
To change the size of the groups, just set @.mod to the desired range, 5, 8, 10, 20 or whatever.
Hope it helps you some.
=;o)
/Kenneth
|||
 Bisjom wrote: |
|
| Hi, I could not get u, How can I do that.. could you explain a bit more please.. |
I will give you the answer tomorrow.
Adamus
|||
| Bisjom wrote: |
|
| Hi, I could not get u, How can I do that.. could you explain a bit more please.. |
1. Add the one field "Range" in #temp table to your existing table with null values. (allow nulls)
2. Add all fields from your existing table to the #temp table with null values. (allow nulls)
Now you have the same fields in both tables. This is key.
3. Now create your #temp table with only the ranges and populate.
4. UNION ALL your aggregate query.
4b. In your aggregeate query, you'll have to determine which row the aggregate hypothetically will go.
5. You should now have your desired results.
Does this makes sense?
Adamus
|||
Hi Adamus,
I understand what you said. But not sure whether its a good idea to add a new field to the table in my case,
As I added in the first message, I am taking values from different tables and I am unable to add a field to any of the tables due to some security reasons.
Can I add your query into my query or DO the union all ?
I cant resolve this..I am really confused now..
|||
I couldn't quite follow all the twist and turns in the dynamic SQL stuff (not sure that the literal example is actually runnable?), but from what I gathered about the need to produce the age groupings and the two sums and count, I belive that this is about what you need to get those particular items...
You may try and see if it runs on your system.
select dm.dob,
s.TVmins,
s.Notional$,
cast(ceiling(datediff(day, dm.dob, getdate()) / 365.25) as int) as age
from dbo.lkpservice s
join dbo.tmpvalidservices_all vs
on s.code = vs.service
join dbo.tmpdemographics_all dm
on dm.rid = vs.rid
where s.schedule = @.schedule
and (vs.complete >= coalesce(@.startdate, vs.complete))
and (vs.complete <= coalesce(@.enddate, vs.complete))
and (vs.provider = coalesce(@.provider, vs.provider))
Assuming that the above 'base query' is ok for the purpose, the below would (hopefully) group by the age-interval given and sum up the two values according to each group. You may need to declare additional variables @.startdate, @.enddate and @.provider though. The age-intervals returned will be only those where there are ages found in the data, there will be no empty intervals. If that is a requirement (and assuming the whole shebang actually is useful =;o), it's possible to generate empty groups with counts of zero, though that is a bit more code, and also requires a numberstable.
Another assumption is also that DOB is in the format of 'SSYYMMDD'
Anyway, try and see if the below is of any use to you.
-- with ranges contained in data (no empty intervals)
declare @.mod int
set @.mod = 10 -- set to change the range of low/high groupings
select cast(y.lowrange as varchar(10)) + '-' + cast(y.highrange as varchar(10)) as AgeGroup,
sum(y.TVmins) as TVMinsAmt,
sum(y.Notional$) as NotionalAmt,
count(*) as Qty
from (
select x.TVmins,
x.Notional$,
(x.age - (x.age % @.mod)) as lowrange,
(((x.age - (x.age % @.mod)) + @.mod) - 1) as highrange
from (
select dm.dob,
s.TVmins,
s.Notional$,
cast(ceiling(datediff(day, dm.dob, getdate()) / 365.25) as int) as age
from dbo.lkpservice s
join dbo.tmpvalidservices_all vs
on s.code = vs.service
join dbo.tmpdemographics_all dm
on dm.rid = vs.rid
where s.schedule = @.schedule
and (vs.complete >= coalesce(@.startdate, vs.complete))
and (vs.complete <= coalesce(@.enddate, vs.complete))
and (vs.provider = coalesce(@.provider, vs.provider))
) x
) y
group by cast(y.lowrange as varchar(10)) + '-' + cast(y.highrange as varchar(10))
order by cast(y.lowrange as varchar(10)) + '-' + cast(y.highrange as varchar(10))
Hope it works out for you =;o)
=;o)
/Kenneth
|||
Hi Kenneth,
Thank you very much for your help.
It seems like what I need...I will try it and let you know..
|||
I coded an answer for you:
All you have to do is pass in the interval for the sp_
You can run the #Temp3 first to see what it produces:
-==========================================================
CREATE TABLE #Temp3(Age int, BeginRange bigint)
DECLARE @.counter int
DECLARE @.counter2 int
DECLARE @.Interval int
DECLARE @.BeginRange int
DECLARE @.IntervalCounter int
SET @.Interval = 20
SET @.counter = 1
SET @.BeginRange = @.Interval
SET @.IntervalCounter = @.Interval
SET @.counter2 = 2
WHILE @.counter <> 100
BEGIN
IF @.Counter % @.Interval = 0
BEGIN
SET @.BeginRange = @.BeginRange + @.Interval
INSERT INTO #Temp3(Age, BeginRange)VALUES(@.Counter, @.BeginRange)
SET @.counter = @.counter + 1
SET @.IntervalCounter = @.Interval * @.counter2
SET @.counter2 = @.counter2 + 1
END
ELSE
BEGIN
INSERT INTO #Temp3(Age, BeginRange)VALUES(@.Counter, @.IntervalCounter)
SET @.counter = @.counter + 1
END
END
Select * from #Temp3
drop table #temp3
-==========================================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_AggregateAge](@.Interval int)
AS
CREATE TABLE #Temp3(Age int, BeginRange bigint)
DECLARE @.counter int
DECLARE @.counter2 int
--DECLARE @.Interval int
DECLARE @.BeginRange int
DECLARE @.IntervalCounter int
--SET @.Interval = 20
SET @.counter = 1
SET @.BeginRange = @.Interval
SET @.IntervalCounter = @.Interval
SET @.counter2 = 2
WHILE @.counter <> 100 --This assumes no one is older than 100
BEGIN
IF @.Counter % @.Interval = 0
BEGIN
SET @.BeginRange = @.BeginRange + @.Interval
INSERT INTO #Temp3(Age, BeginRange)VALUES(@.Counter, @.BeginRange)
SET @.counter = @.counter + 1
SET @.IntervalCounter = @.Interval * @.counter2
SET @.counter2 = @.counter2 + 1
END
ELSE
BEGIN
INSERT INTO #Temp3(Age, BeginRange)VALUES(@.Counter, @.IntervalCounter)
SET @.counter = @.counter + 1
END
END
SELECT (t3.BeginRange - @.Interval) AS BeginRange, (t3.BeginRange - 1) AS EndRange, b.Name, SUM(b.TVMins) AS TVMins, SUM(Notional) AS Notional, t3.Age
FROM #Temp3 t3 JOIN Birthday b --JOIN Your Table on Age
ON b.Age = t3.Age
GROUP BY b.Name, t3.Age, t3.BeginRange
ORDER BY t3.BeginRange
DROP TABLE #Temp3
Results:
EXEC sp_AggregateAge 20
BeginRange EndRange Name TVMins Notional Age
-- -- - -- -- --
20 39 Adam 170 10503 35
20 39 John 123 45648 36
40 59 Chris 153 123456 40
40 59 JoAnne 99 65489 53
40 59 Joe 72 5478 42
60 79 Chris 105 4652 69
Adamus
|||
Hi Kenneth,
Thank you very much for your code and it is working fine with the database.
But when I changed your code to dymanic SQL to select the null value of the parameters and check conditions( Like if Provider is not null then ....), It shows a problem.
It is giving error in the grou by line since it cant recognise the '-' character.. it is taking that as a minus operator..
group by cast(y.lowrange as varchar(10)) + '-' + cast(y.highrange as varchar(10))
Is there any way to split in to get it as range..
The modified code is as follows:
DECLARE
@.sql nvarchar(4000),
@.paramlist nvarchar(4000)
SELECT @.sql = 'select sum(y.TVmins) as TVMinsAmt, sum(y.Notional$) as NotionalAmt, count(*) as Qty, '
SELECT @.sql = @.sql + 'cast(y.lowrange as varchar(10)) '
SELECT @.sql = @.sql + ' + '
SELECT @.sql = @.sql + ' '
SELECT @.sql = @.sql + ' cast(y.highrange as varchar(10)) as AgeGroup '
SELECT @.sql = @.sql + ' from ( select x.TVmins, x.Notional$,x.Age,(x.age - (x.age % @.Interval)) as lowrange,'
SELECT @.sql = @.sql + '(((x.age - (x.age % @.Interval)) + @.Interval) - 1) as highrange'
SELECT @.sql = @.sql + ' from ( select dm.dob, s.TVmins, s.Notional$,'
SELECT @.sql = @.sql + 'cast(ceiling(datediff(day, dm.dob, getdate()) / 365.25) as int) as age'
SELECT @.sql = @.sql + 'from dbo.lkpservice s '
SELECT @.sql = @.sql + 'join dbo.tmpvalidservices_all vs '
SELECT @.sql = @.sql + 'on s.code = vs.service '
SELECT @.sql = @.sql + 'join dbo.tmpdemographics_all dm '
SELECT @.sql = @.sql + 'on dm.rid = vs.rid '
SELECT @.sql = @.sql + 'where s.schedule = @.schedule '
IF @.StartDate IS NOT NULL
SELECT @.sql = @.sql + 'and (vs.complete >= coalesce(@.StartDate, vs.complete))'
IF @.EndDate IS NOT NULL
SELECT @.sql = @.sql + 'and (vs.complete <= coalesce(@.EndDate, vs.complete))'
IF @.Provider IS NOT NULL
SELECT @.sql = @.sql + 'and (vs.provider = coalesce(@.provider, vs.provider))'
SELECT @.sql = @.sql + ') x'
SELECT @.sql = @.sql + ') y '
SELECT @.sql = @.sql + 'group by cast(y.lowrange as varchar(10)) '
SELECT @.sql = @.sql + '+'
SELECT @.sql = @.sql + ' '
SELECT @.sql = @.sql + '+'
SELECT @.sql = @.sql + ' cast(y.highrange as varchar(10)) '
SELECT @.paramlist = '@.Provider varchar(50),@.Schedule varchar(50),@.StartDate datetime,@.EndDate datetime,@.Interval numeric '
EXEC sp_executesql @.sql,@.paramlist,@.Provider,@.Schedule,@.StartDate,@.EndDate,@.Interval
Thanks
|||
Hi Adamus,
Thanks for your code, Your code is working fine for the database.. But it is taking time to create temp table and I have to deploy this code to the reporting as well..
So I have a doubt whether it is a good idea to create a temp table since it is really a big one which takes a time to execute.
Thank you for your advice and waiting to get your reply...
|||
You don't really need to group by the concatenated age group, I jsut wrote that to show how to create group values like '10-19', '20-29' etc. You can keep the low and high as separate columns instead and perhaps later do the concatenation when presenting the result, if desired.
Try with the below, where you have low and high values as separate columns.
(BTW, is there any special reason you do this as dynamic SQL? For this particular query, it's not necessary. It'll run fine as is, without sp_executesql)
declare @.mod int
set @.mod = 10 -- set to change the range of low/high groupings
select y.lowrange,
y.highrange,
sum(y.TVmins) as TVMinsAmt,
sum(y.Notional$) as NotionalAmt,
count(*) as Qty
from (
select x.TVmins,
x.Notional$,
(x.age - (x.age % @.mod)) as lowrange,
(((x.age - (x.age % @.mod)) + @.mod) - 1) as highrange
from (
select dm.dob,
s.TVmins,
s.Notional$,
cast(ceiling(datediff(day, dm.dob, getdate()) / 365.25) as int) as age
from dbo.lkpservice s
join dbo.tmpvalidservices_all vs
on s.code = vs.service
join dbo.tmpdemographics_all dm
on dm.rid = vs.rid
where s.schedule = @.schedule
and (vs.complete >= coalesce(@.startdate, vs.complete))
and (vs.complete <= coalesce(@.enddate, vs.complete))
and (vs.provider = coalesce(@.provider, vs.provider))
) x
) y
group by y.lowrange, y.highrange
order by y.lowrange, y.highrange
=;o)
/Kenneth
Bisjom wrote: