October 18, 2008 at 7:42 am
I have a table (Data_Periods) that contains a list of months, the table can contain between 1 and 12 entries. This table represents months that have elapsed e.g if we were using this table now it would contain the months Jan through to Sep.
My other table (Data_Table) contains numeric data for each month AND also a 'type' value. The 'type' value represents the actual value for each month or the forecast value for each month. For example, the forecast for Jan could have been 10 whilst the 'actual' could have been 5. There will only be 'actual' data for months that have elapsed.
The following sets up the example:
create table Data_Table
(
Line_ID int,
Jan int,
Feb int,
Mar int,
Apr int,
May int,
Jun int,
Jul int,
Aug int,
Sep int,
Oct int,
Nov int,
[Dec] int,
V_Type int
)
insert into Data_Table
select1, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 1
union all
select1, 1, 2, 3, 4, 0, 0, 0, 0, 0, 0, 0, 0, 2
create table Data_Periods
(
Period_Name varchar(3)
)
insert into Data_Periods
select 'Jan'
union all
select 'Feb'
union all
select 'Mar'
union all
select 'Apr'
The problem I have is that when I want to see a full year I want to see all of the 'actual' months followed by the remaining forecast months. Using my example this would result in seeing (on one line) Jan - Apr (actuals) and May - Dec (forecast).
Thanks
October 18, 2008 at 8:59 am
It seems like I'm overlooking an easier way to do this, but here's *a* solution. I use the variables so that I don't have to recursively scan a static table for each row.
[font="Courier New"]DECLARE
@Jan INT, @Feb INT, @Mar INT, @Apr INT, @May INT, @Jun INT,
@Jul INT, @Aug INT, @Sep INT, @Oct INT, @Nov INT, @Dec INT
SELECT @Jan = CASE WHEN EXISTS(SELECT Period_Name FROM Data_Periods WHERE Period_Name = 'Jan') THEN 1 ELSE 0 END,
@Feb = CASE WHEN EXISTS(SELECT Period_Name FROM Data_Periods WHERE Period_Name = 'Feb') THEN 1 ELSE 0 END,
--...
@Dec = CASE WHEN EXISTS(SELECT Period_Name FROM Data_Periods WHERE Period_Name = 'Dec') THEN 1 ELSE 0 END
SELECT DT.Line_ID,
Jan = CASE WHEN @Jan = 1 THEN DT1.Jan ELSE DT2.Jan END,
Feb = CASE WHEN @Feb = 1 THEN DT1.Feb ELSE DT2.Feb END,
--...
[DEC] = CASE WHEN @Dec = 1 THEN DT1.[Dec] ELSE DT2.[Dec] END
FROM Data_Table DT
LEFT JOIN Data_Table DT1 ON DT.Line_ID = DT1.Line_ID AND DT1.V_Type = 1
LEFT JOIN Data_Table DT2 ON DT.Line_ID = DT2.Line_ID AND DT2.V_Type = 2
[/font]
October 18, 2008 at 3:21 pm
The problem with this whole thing is the original form of the data. It's in the same form as the report... it's "horizontal" and the column names represent time. It's not normalized and will never be good for anything except generating this one report.
My recommendation would be to rethink the original storage method on this. Each row should probably look something like...
Period DATETIME,
Type INT, --Plan, Actual, or ???
Value MONEY
It'll be a lot easier to create reports using Cross Tabs, especially reports that "straddle a year". Right now, you have no chance of creating such additional reports without a whole lot of headache. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2008 at 7:00 pm
Agreed, and the Month name being entered into an alternate table to signify that real data should be used adds another unnecessary step. Add your projected data to the table whenever you have it, and only add your "actual" data when it's available. You can always modify your report to not display that actual data before the end of that month to cover cases when it was entered early. If you absolutely need to, you can always add a bit field to the table to show when a certain months data should use actual instead of projected, and easily update it with a simple update utility.
October 19, 2008 at 2:17 am
Hi,
I am recently into SQL Server programming and assuming we need a single row for each line, I have written this query which is giving the results as expected. Please correct me if I am wrong anywhere.
Thanks.
create table Data_Table
(
Line_ID int,
Jan int,
Feb int,
Mar int,
Apr int,
May int,
Jun int,
Jul int,
Aug int,
Sep int,
Oct int,
Nov int,
[Dec] int,
V_Type int
)
insert into Data_Table values (2,11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1,12, 1)
insert into Data_Table values (2,1, 2, 3, 4, 5, 0, 0, 0, 0, 0, 0, 0, 2)
insert into Data_Table values (1, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 1)
insert into Data_Table values ( 1, 1, 2, 3, 4, 0, 0, 0, 0, 0, 0, 0, 0, 2)
SELECT
CASE JAN
WHEN 0 THEN (SELECT JAN FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID)
ELSE JAN
END AS JAN,
CASE FEB
WHEN 0 THEN (SELECT FEB FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE FEB
END AS FEB,
CASE MAR
WHEN 0 THEN (SELECT MAR FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE MAR
END AS MAR,
CASE APR
WHEN 0 THEN (SELECT APR FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE APR
END AS APR,
CASE MAY
WHEN 0 THEN (SELECT MAY FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE MAY
END AS MAY,
CASE JUN
WHEN 0 THEN (SELECT JUN FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE JUN
END AS JUN,
CASE JUL
WHEN 0 THEN (SELECT JUL FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE JUL
END AS JUL,
CASE AUG
WHEN 0 THEN (SELECT AUG FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE AUG
END AS AUG,
CASE SEP
WHEN 0 THEN (SELECT SEP FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE SEP
END AS SEP,
CASE OCT
WHEN 0 THEN (SELECT OCT FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE OCT
END AS OCT,
CASE NOV
WHEN 0 THEN (SELECT NOV FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE NOV
END AS NOV,
CASE DEC
WHEN 0 THEN (SELECT DEC FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID = B.Line_ID)
ELSE DEC
END AS DEC,
B.Line_ID
FROM DATA_TABLE B WHERE V_Type = 2
Regards,
Prakash
---------------------------------------------------------------------------------
October 19, 2008 at 4:38 am
Garadin (10/18/2008)
Agreed, and the Month name being entered into an alternate table to signify that real data should be used adds another unnecessary step. Add your projected data to the table whenever you have it, and only add your "actual" data when it's available. You can always modify your report to not display that actual data before the end of that month to cover cases when it was entered early. If you absolutely need to, you can always add a bit field to the table to show when a certain months data should use actual instead of projected, and easily update it with a simple update utility.
Hi Jeff/Garadin,
I agree but unfortunately the table design is not mine and I have no influence to change it.
Thanks
David
October 19, 2008 at 9:10 am
I figured as much, which is why I didn't post it with the earlier solution, but then I realized that the next person that reads this thread might have the power to change their table, so we post the information anyways.
October 19, 2008 at 10:23 am
Prakash.Padmanabha (10/19/2008)
Hi,I am recently into SQL Server programming and assuming we need a single row for each line, I have written this query which is giving the results as expected. Please correct me if I am wrong anywhere.
Prakash... what if the Actuals ARE zero? It would be better if you stored unknown Actuals as NULL which means "UNKNOWN. The code get's a little simpler, too...
drop table data_table
create table Data_Table
(
Line_ID int,
Jan int,
Feb int,
Mar int,
Apr int,
May int,
Jun int,
Jul int,
Aug int,
Sep int,
Oct int,
Nov int,
[Dec] int,
V_Type int
)
insert into Data_Table values (2,11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1,12, 1)
insert into Data_Table values (2,1, 2, 3, 4, 5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2)
insert into Data_Table values (1, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 1)
insert into Data_Table values ( 1, 1, 2, 3, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2)
SELECT
(SELECT ISNULL(b.JAN,a.JAN) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS JAN,
(SELECT ISNULL(b.FEB,a.FEB) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS FEB,
(SELECT ISNULL(b.MAR,a.MAR) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS MAR,
(SELECT ISNULL(b.APR,a.APR) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS APR,
(SELECT ISNULL(b.MAY,a.MAY) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS MAY,
(SELECT ISNULL(b.JUN,a.JUN) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS JUN,
(SELECT ISNULL(b.JUL,a.JUL) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS JUL,
(SELECT ISNULL(b.AUG,a.AUG) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS AUG,
(SELECT ISNULL(b.SEP,a.SEP) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS SEP,
(SELECT ISNULL(b.OCT,a.OCT) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS OCT,
(SELECT ISNULL(b.NOV,a.NOV) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS NOV,
(SELECT ISNULL(b.[DEC],a.[DEC]) FROM DATA_TABLE A WHERE V_TYPE = 1 AND A.Line_ID=B.Line_ID) AS [DEC],
B.Line_ID
FROM DATA_TABLE B WHERE V_Type = 2
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2008 at 10:28 am
You aren't taking into account the original requirement that actual months only be used if the Month value exists in his second table. While we all agree it's a silly requirement that shouldn't be required, it still IS required for his purposes, and would need to be in any solution.
October 19, 2008 at 11:38 am
Hi Jeff,
Thanks for the input on my query.
Hi Garadin,
The initial requirement gave me an impression that the second table does not really depend on the data_period table and actual values will only be present for those months that are elapsed and all the other months will contain forecast data. Thanks
Prakash
---------------------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply