November 19, 2008 at 12:35 pm
Hi-
I need to create a function that I can call from my asp.net page that will perform a calculation and then populate the values into columns in an existing table. I have the query that creates the calculation and another query that will update the table, but how do i create the function? I have a primary key that is referenced in both tables and is passed through as a query parameter in the asp.net webpages.
Table 1: WorkDays
Fields: Id, StartDate, EndDate, Quarter, WorkDays
Table 2: Project
Fields: Id, StartDate, EndDate, Q1, Q2, Q3, Q4
For my calculations, my query reads in the StartDate and EndDate from Project and determines the number of days in each quarter. A row is created for each quarter in the project that contains workdays.
The update query populates Project with the number of days in each quarter for EVERY project.
I need the database to update the information for every project, but i will only want to report the data for the selected id.
November 19, 2008 at 2:13 pm
Hi terickso,
This query (some column names will differ) gives the basic info you are after...
select #project.projectnum, #WorkCalendar.q, count(#WorkCalendar.dt) as days
from #project inner join #WorkCalendar on #project.startdate = #WorkCalendar.dt
where #WorkCalendar.isWeekDay=1 and #WorkCalendar.isHoliday=0
group by #project.projectnum,#WorkCalendar.q
But it is not in the right format. It needs to be pivoted to go into the other table.
with cteProjectDays (projectnum, q, days)
as
(select #project.projectnum, #WorkCalendar.q, count(#WorkCalendar.dt) as days
from #project inner join #WorkCalendar on #project.startdate = #WorkCalendar.dt
where #WorkCalendar.isWeekDay=1 and #WorkCalendar.isHoliday=0
group by #project.projectnum,#WorkCalendar.q)
, cteProjectSummary (projectnum, Q1, Q2, Q3, Q4)
as
(select projectnum, isnull([1],0) as Q1, isnull([2],0) as Q2, isnull([3],0) as Q3, isnull([4],0) as Q4
from
(select projectnum, q, days from cteProjectDays ) p PIVOT
( sum(days) for q in ([1], [2], [3], [4])
) as pvt )
select * from cteProjectSummary
update #project set Q1Days =Q1 , Q2Days =Q2, Q3Days =Q3 , Q4Days =Q4
from #project inner join cteProjectSummary on #project.projectnum = cteProjectSummary.projectnum
Have a look at the select statement first - it should be roughly the same as your Projects table. If you are happy then remove the select * from cteProjectSummary and use the UPDATE statement instead.
Does that help....?
B
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply