Weekly Dynamic: Count of Fixed Assets Added

Continuing a look at metrics to analyze GP transactions, this week’s Weekly Dynamic looks at a count of Fixed Assets added by week. As with other metrics, this can also show past data in Microsoft Dynamics GP for trends.

The code is below and it’s available for download at: http://www.box.net/shared/vopqx25elb

--Select Fixed Assets Added by week

--Could be switched to month but you'll need to use either the FA Calendar Month or the Fiscal month if you don't use a calendar year


select Case  

 when datename(weekday,dateadded) = 'Friday' then dateadded

 when datename(weekday,dateadded) = 'Saturday' then dateadd(Day,6,dateadded)

 when datename(weekday,dateadded) = 'Sunday' then dateadd(Day,5,dateadded)

 when datename(weekday,dateadded) = 'Monday' then dateadd(Day,4,dateadded)

 when datename(weekday,dateadded) = 'Tuesday' then dateadd(Day,3,dateadded)

 when datename(weekday,dateadded) = 'Wednesday' then dateadd(Day,2,dateadded)

 when datename(weekday,dateadded) = 'Thursday' then dateadd(Day,1,dateadded)

 else '01/01/1900' end as WeekEndDate, count(Assetindex) as FAAddedCount

  from fa00100

Group by

Case  

 when datename(weekday,dateadded) = 'Friday' then dateadded

 when datename(weekday,dateadded) = 'Saturday' then dateadd(Day,6,dateadded)

 when datename(weekday,dateadded) = 'Sunday' then dateadd(Day,5,dateadded)

 when datename(weekday,dateadded) = 'Monday' then dateadd(Day,4,dateadded)

 when datename(weekday,dateadded) = 'Tuesday' then dateadd(Day,3,dateadded)

 when datename(weekday,dateadded) = 'Wednesday' then dateadd(Day,2,dateadded)

 when datename(weekday,dateadded) = 'Thursday' then dateadd(Day,1,dateadded)

 else '01/01/1900' end 

order by 1