Copying data from old rows to new rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Copying data from old rows to new rows

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-07-16 : 07:58:04

Dear All,

I have a requirement like this..
I am displaying the data for perticular month id there is a data if there is no data for current month i need to display the previous month data.How can we acheive this.
Pls do help me it will be greathelpfull for me

I am using this query to display the data for perticular month

select f.plantid,sum(f.P90Efficiency) as P90Efficency,PL.BaselineEfficiency as baselineefficiancy,
rtrim(convert(char,month(p.Dateadded))) +'/01/'+convert(char, year(p.Dateadded)) Dateadded
from fanout f
join project p on p.erm=f.erm
join plant PL on PL.plantid=f.plantid
where p.approved<>0
group by f.plantid,
PL.BaselineEfficiency,
rtrim(convert(char,month(p.Dateadded))) +'/01/'+convert(char, year(p.Dateadded))

if htere is no data for perticular month then i need to display with the previous month data.

Thanks,
Gangadhar

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 08:58:29

Your posted select statement doesn't care about a particular month.
It shoes all data from table grouped by month.

No, you're never too old to Yak'n'Roll if you're too young to die.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-07-16 : 09:14:12

Hi,

Can you pls help me how to segragat ethe data by month wise and select the data for previous month if there is no data for current month upto next 84 months i need.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 13:19:33

may be this


SELECT t1.*
FROM
(SELECT TOP 1 YEAR(Dateadded) AS YearVal,MONTH(Dateadded) AS MonthVal FROM YourTable WHERE YEAR(Dateadded)=@Year AND MONTH(Dateadded)<= @Month  ORDER BY MONTH(Dateadded) DESC) t
CROSS APPLY
(
select f.plantid,sum(f.P90Efficiency) as P90Efficency,PL.BaselineEfficiency as baselineefficiancy,
dateadd(mm,datediff(mm,0,p.Dateadded),0) as  Dateadded
from fanout f
join project p on p.erm=f.erm
join plant PL on PL.plantid=f.plantid
where p.approved<>0
AND MONTH(Dateadded)=t.MonthVal
AND YEAR(Dateadded) =t.YearVal
group by f.plantid,
PL.BaselineEfficiency,
dateadd(mm,datediff(mm,0,p.Dateadded),0)
)t1

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-07-17 : 02:36:56

This approach did not approved by our lead.

this is the actual query
select f.plantid ,sum(f.p50Efficiency) as p50Efficiency,PL.BaselineEfficiency ,
rtrim(convert(char,month(p.Dateadded))) +'/01/'+convert(char, year(p.Dateadded)) Dateadded
from fanout f
join project p on p.erm=f.erm
join plant PL on PL.plantid=f.plantid
where p.approved<>0
group by f.plantid,
PL.BaselineEfficiency ,
rtrim(convert(char,month(p.Dateadded))) +'/01/'+convert(char, year(p.Dateadded))

the data is returning for 6/01/2009 (mm/dd/yyyy) currently.
suppose lets say starting date will 6/01/2009 (only month wise data will be considered).
From this date to next 7 year we need to display.
currently there is no data in our database for 7/01/2009 onwards. So need to display for remaining 7 years the same data of 6/01/2009.(if there is no data in the database)

plant | P50effciency | baselin |dateadded
id | |eeffiency |

FFO | 0.080000002 |10 |6/1/2009
KLM1 |0.080000002 |10 |6/1/2009
KLM2 |0.140000001 |10 |6/1/2009
KLM3 |0.080000002 |10 |6/1/2009
KLM4 |0.080000002 |10 |6/1/2009
PBG |0.080000002 |10 |6/1/2009

Even our lead suggested to craete new table to store the data for the month data not exist.

If anybody having any idea pls do help me..
This is vry critical for me..

Thanks in advance..

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-07-17 : 08:03:59

see the data for month 7/1/2009 should be same as 6/1/2009 as there is no data for 7/1/2009 with "dateadded" as 7/1/2009.

Pls help me in this regard..
Thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources