--declare @start datetime
--declare @end datetime
--declare @energyorg int
SELECT vreportdatacurrentprior.extendedstandardcost,
vreportdatacurrentprior.businessentity,
vreportdatacurrentprior.product,
vreportdatacurrentprior.site,
vreportdatacurrentprior.subproduct,
SUM(vreportdatacurrentprior.orderamount_lastyear) AS [Prior Bookings],
SUM(vreportdatacurrentprior.invoiceamount_lastyear) AS [Prior Sales] ,
SUM(vreportdatacurrentprior.orderamount_currentyear) AS [Current Bookings],
SUM(vreportdatacurrentprior.invoiceamount_currentyear) AS [Current Sales],
exchangerates.exchangerate,
energysites.energyorg,
vreportdatacurrentprior.qty,
energysites.sitenumber,
MWD.Days AS monthworkingdays,
WD.Days AS workingdays,
vreportdatacurrentprior.transyear
FROM vreportdatacurrentprior
CROSS JOIN (select count(*) days
from master..spt_values
where type='P' -- series from 0-1024
and number between 0 and 60 -- max of 30 days from start
and month(number+GETDATE()-30) = month(GETDATE()) -- within start month
and (datepart(dw,GETDATE()-30 + number)+@@datefirst-2)%7+1 < 6) MWD
CROSS JOIN (select count(*) days
from master..spt_values
where type='P' -- series from 0-1024
and number between 0 and @end-@start -- range from start to end
and (datepart(dw,@start + number)+@@datefirst-2)%7+1 < 6) WD
INNER JOIN exchangerates ON MONTH(vreportdatacurrentprior.transdate) = exchangerates.xratemonth
AND vreportdatacurrentprior.transyear = exchangerates.xrateyear
AND vreportdatacurrentprior.currencycode = exchangerates.currency
INNER JOIN energysites ON vreportdatacurrentprior.site = energysites.sitenumber
INNER JOIN planjoin ON vreportdatacurrentprior.subproduct = planjoin.subproduct
WHERE ( energysites.energyorg = @energyorg )
AND ( vreportdatacurrentprior.transdate BETWEEN @start AND @end
OR vreportdatacurrentprior.transdate BETWEEN
Dateadd(yy, -1, @start) AND Dateadd(yy, -1, @end) )
AND ( vreportdatacurrentprior.businessentity = 'energy' )
GROUP BY vreportdatacurrentprior.extendedstandardcost,
vreportdatacurrentprior.businessentity,
vreportdatacurrentprior.product,
vreportdatacurrentprior.site,
vreportdatacurrentprior.subproduct,
vreportdatacurrentprior.transyear,
exchangerates.exchangerate,
energysites.energyorg,
vreportdatacurrentprior.qty,
energysites.sitenumber,
vreportdatacurrentprior.transdate
ORDER BY vreportdatacurrentprior.transdate DESC
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE