In the article Improving your Client Budgeting and Tracking the budgeting data is entered by month, so it only makes sense that the actual sales be entered by month as well. So in this example new Sales will be considered project contract values for projects created this year and then broken out by month so you can see how your project sales are progressing throughout the year.

There’s a few different ways that this could be accomplished, but the easiest is to just use the Datepart function. This is a great tool, which you should familiarize yourself with, but the basic functionality allows you to tell the function what part of a date you want extract from a given date. For example you might want the year from a date, so if you passed used DatePart(YYYY, ‘1-1-2014′) the result would be 2014, if you used DatePart(MM, ’12-1-2014’) the result would be 12. This datepart function is used in two different ways to get this Sales data, the first is to filter the projects created this year, in conjunction with the getDate function to get todays date. So this looks like this “DATEPART(yyyy, isnull(startdate, pr.createdate)) = DATEPART(yyyy, getdate())”, where the first part get’s the year the project was created, and the second part gets the year from todays date, so this results in projects create this year. The second part is to break out the projects by month, so for that you can use “group by DATEPART(MM, pr.createdate)” , which in SQL means group the results by Month. So now you have the data grouped by month, the first part of the query will select the sum of the contract values(fee, reimballow, consfee) from the project tables (pr), along with the clientid, which needs to be in the group by. This part of the sql is put in the a subselect which is then selected by month in the overall select statement by using case statements. Which basically reads if month =1 then sum up that amount from the subselect, you can read more about case statements here in Creating Stored Procedures article .

The other component you’ll notice below is another subselect that is joined to the first by using union all. Union All allows you to take two separate statements and combine the results into a single output. The only requirement is that both statements need to return the same number and type of columns. I this example the second statement is searching the Contract module for entries that of a change order type. Another item to take note of is after all the case statements there’s a final sum statement to total up all the amounts, this allows you to have a final total. Finally just as a shortcut, the sales data is cleared by using a delete from Table, and then the insert is used to just re insert all the data. If it wasn’t done this way you would have to combine insert and update statements, basically doubling or more the size of the sql.

delete
from dbo.Clients_ActualSales

 

insert
into Clients_ActualSales

select clientid, 1,

sum(Case
when smonth =

‘1’
then amount else 0 end),

sum(Case
when smonth =

‘2’
then amount else 0 end),

sum(Case
when smonth =

‘3’
then amount else 0 end),

sum(Case
when smonth =

‘4’
then amount else 0 end),

sum(Case
when smonth =

‘5’
then amount else 0 end),

sum(Case
when smonth =

‘6’
then amount else 0 end),

sum(Case
when smonth =

‘7’
then amount else 0 end),

sum(Case
when smonth =

‘8’
then amount else 0 end),

sum(Case
when smonth =

‘9’
then amount else 0 end),

sum(Case
when smonth =

’10’
then amount else 0 end),

sum(Case
when smonth =

’11’
then amount else 0 end),

sum(Case
when smonth =

’12’
then amount else 0 end),

sum(Case
when smonth =

’13’
then amount else 0 end),

sum(amount)
as total

,‘System’,
getdate(),
‘System’,
getdate()

from

(

select clientid,
sum(fee+reimballow + consultfee)as amount, DATEPART(MM, isnull(startdate, pr.createdate))
as smonth

from pr

where pr.wbs2=

and
DATEPART(yyyy, isnull(startdate, pr.createdate))
= DATEPART(yyyy, getdate())

and chargetype =
‘R’

and clientid is
not
null

group
by clientid,
DATEPART(MM, isnull(startdate, pr.createdate))

union
all


select clientid,
sum(cd.fee+cd.reimballow + cd.consultfee)
as feeamount, 13 as smonth

from pr

left
outer
join Contracts c on c.wbs1= pr.wbs1

left
outer
join ContractDetails CD on cd.Contractnumber = c.Contractnumber and cd.wbs1= c.wbs1

where pr.wbs2=

and chargetype =
‘R’

and c.ContractType in
(’02’,’03’)

and c.ContractStatus =
’04’

and clientid is
not
null

and
DATEPART(yyyy, isnull(startdate, c.ApprovedDate))
= DATEPART(yyyy, getdate())

group
by clientid

)
as x

group
by clientid


Tagged on:         

Leave a Reply

Your email address will not be published. Required fields are marked *