May 24, 2004 at 1:59 pm
I have a dB with the following schema: PersonName, Year, Month1, Month2, Month3 ... Month12. (Each person has a row for each year!)
The Month columns store values that I want to sum but I need the ability to control the sumarization via the following: 1) Specify a from date and thru date 2) be able to handle multiple years (e.g. user specifies from date of 07/01/2003 and thru date of 06/30/2004)
For example if the user specifies a from date of 07/01/2003 and thru date of 06/30/2004 then I'd want to select year in 2003, 2004 and sum the Month 7 thru Month 12 columns of year 2003 along with the Month 1 thru Month 6 columns of year 2004.
Is there a way to code a tSQL UDF to handle this complex selection and sumarization? ANY contributions to this problem are sincerly welcomed!!!
May 24, 2004 at 9:09 pm
May 24, 2004 at 10:30 pm
CREATE PROCEDURE GetRangeTotal @FromDateEnt smalldatetime,@ToDateEnt smalldatetime
as
DECLARE @FromYear AS int
DECLARE @ToYear AS int
DECLARE @FromMonth AS int
DECLARE @ToMonth AS int
SET @FromYear = YEAR(@FromDateEnt)
SET @ToYear = YEAR(@ToDateEnt)
SET @FromMonth = MONTH(@FromDateEnt)
SET @ToMonth = MONTH(@ToDateEnt)
SELECT rs1.PersonName,SUM(rs1.Month1 + rs1.Month2 + rs1.Month3 + rs1.Month4 + rs1.Month5 + rs1.Month6 + rs1.Month7 + rs1.Month8 + rs1.Month9 + rs1.Month10 + rs1.Month11 + rs1.Month12) AS pertotal
FROM (
SELECT personname,peryear,
Month1 = CASE WHEN (peryear >= @FromYear and @FromMonth = 1) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 1)) THEN Month1 ELSE 0 END ,
Month2 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 2) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 2)) THEN Month2 ELSE 0 END ,
Month3 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 3) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 3)) THEN Month3 ELSE 0 END ,
Month4 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 4) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 4)) THEN Month4 ELSE 0 END ,
Month5 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 5) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 5)) THEN Month5 ELSE 0 END ,
Month6 = CASE WHEN (peryear >= @fromyear and @FromMonth <= 6) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 6)) THEN Month6 ELSE 0 END ,
Month7 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 7) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 7)) THEN Month7 ELSE 0 END ,
Month8 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 8) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 8)) THEN Month8 ELSE 0 END ,
Month9 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 9) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 9)) THEN Month9 ELSE 0 END ,
Month10 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 10) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 10)) THEN Month10 ELSE 0 END ,
Month11 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 11) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth >= 11)) THEN Month11 ELSE 0 END ,
Month12 = CASE WHEN (peryear >= @FromYear and @FromMonth <= 12) and (peryear < @ToYear or (peryear = @ToYear and @ToMonth = 12)) THEN Month12 ELSE 0 END
FROM persondets
WHERE (peryear >= @FromYear and peryear <= @ToYear)
) AS rs1
GROUP BY rs1.personname
EXEC GetRangeTotal '20000131','20020731'
and some test data (note: I changed the column name Year to PerYear to stop confusion with SQL Year function:
create table PersonDets
(PersonName varchar(30), PerYear int, Month1 int,
Month2 int, Month3 int, Month4 int, Month5 int,
Month6 int, Month7 int, Month8 int, Month9 int,
Month10 int, Month11 int, Month12 int)
insert into Persondets
(PersonName,Peryear,Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)
values('Adams',2000,100,200,300,400,500,600,700,800,900,1000,1100,1200)
insert into Persondets
(PersonName,Peryear,Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)
values('Adams',2001,10,20,30,40,50,60,70,80,90,100,110,120)
insert into Persondets
(PersonName,Peryear,Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)
values('Adams',2002,1,2,3,4,5,6,7,8,9,10,11,12)
insert into Persondets
(PersonName,Peryear,Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)
values('Adams',2003,5,10,15,20,25,30,35,40,45,50,55,60)
insert into Persondets
(PersonName,Peryear,Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)
values('Brown',2002,5,10,15,20,25,30,35,40,45,50,55,60)
May 25, 2004 at 8:22 am
VERY ELEGANT Solution Chris!
Two thoughts:
1) Problem when entering a from date of '20000201' - instead of only not including Month1 of 2000 the proc does not include Month1 of 2000, 2001 and 2002. Adams should be 8508 and is shown as 8497 (when run for '20000131' thru '20020731' Adams total is 8608 thus when run for '20000201' thru '20020731', he lost 111 rather than the expected 100)
2) How would you recommend refactoring this solution (after addressing the first problem noted above) to create an alternative way of mapping of monthly columns for fiscal year. Specifically Month7 really would be January of Year 1 and Month1 really would be July. I guess this would require another proc parm DateType C or F, but how could the rest of the solution be adjusted to use this information?
AGAIN GREAT JOB! Thanks for the speedy response and i'll look forward to seeing your reply regarding my two thoughts.
May 25, 2004 at 8:19 pm
How would you recommend refactoring this solution ...It really depends on how the data is stored and what you’re expecting to retrieve. Eg On record with year = 2000, are all months actually for the 2000 calendar year, or does the data cover the period July 2000 thru to June 2001?.
Altered procedure:
alter PROCEDURE GetRangeTotal @FromDateEnt smalldatetime,@ToDateEnt smalldatetime
as
DECLARE @FromYear AS int
DECLARE @ToYear AS int
DECLARE @FromMonth AS int
DECLARE @ToMonth AS int
SET @FromYear = YEAR(@FromDateEnt)
SET @ToYear = YEAR(@ToDateEnt)
SET @FromMonth = MONTH(@FromDateEnt)
SET @ToMonth = MONTH(@ToDateEnt)
SELECT rs1.PersonName,SUM(rs1.Month1 + rs1.Month2 + rs1.Month3 + rs1.Month4 + rs1.Month5 + rs1.Month6 + rs1.Month7 + rs1.Month8 + rs1.Month9 + rs1.Month10 + rs1.Month11 + rs1.Month12) AS pertotal
FROM (
SELECT personname,peryear,
Month1 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth = 1 ) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth = 1 and @ToMonth >= 1)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 1) THEN Month1 ELSE 0 END ,
Month2 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 2) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 2 and @ToMonth >= 2)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 2) THEN Month2 ELSE 0 END ,
Month3 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 3) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 3 and @ToMonth >= 3)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 3) THEN Month3 ELSE 0 END ,
Month4 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 4) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 4 and @ToMonth >= 4)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 4) THEN Month4 ELSE 0 END ,
Month5 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 5) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 5 and @ToMonth >= 5)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 5) THEN Month5 ELSE 0 END ,
Month6 = CASE WHEN (peryear > @fromyear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 6) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 6 and @ToMonth >= 6)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 6) THEN Month6 ELSE 0 END ,
Month7 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 7) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 7 and @ToMonth >= 7)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 7) THEN Month7 ELSE 0 END ,
Month8 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 8) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 8 and @ToMonth >= 8)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 8) THEN Month8 ELSE 0 END ,
Month9 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 9) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 9 and @ToMonth >= 9)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 9) THEN Month9 ELSE 0 END ,
Month10 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 10) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 10 and @ToMonth >= 10)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 10) THEN Month10 ELSE 0 END ,
Month11 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 11) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 11 and @ToMonth >= 11)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth >= 11) THEN Month11 ELSE 0 END ,
Month12 = CASE WHEN (peryear > @FromYear and peryear < @ToYear) or ((peryear = @FromYear and peryear <> @ToYear) and @FromMonth <= 12) or ((peryear = @FromYear and peryear = @ToYear) and (@FromMonth <= 12 and @ToMonth = 12)) or ((peryear <> @FromYear and peryear = @ToYear) and @ToMonth = 12) THEN Month12 ELSE 0 END
FROM persondets
WHERE (peryear >= @FromYear and peryear <= @ToYear)
) AS rs1
GROUP BY rs1.personname
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply