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.
| Author |
Topic |
|
jhermiz
3564 Posts |
 Posted - 2005-06-10 : 09:58:06
|
Question in regards to temp table in rs...All of my reports have used tables in general so far...Now I'm runnining into a dilemma....and not sure If I should use a temp table or just a scheduled job to import data into a dummy table.Here's the issue, I have a sproc with this:CREATE PROCEDURE rsp_sls_tasks @PROJECTNAME varchar(255) = NULL, @TEXT_26 varchar(255) = NULLASBEGINSET NOCOUNT ONSELECT MAX(pt.PROJECTNAME) AS PROJECTNAME, pt.NAME, pt.TEXT_26, MAX(pt.TEXT_27) AS WBSDescription, MAX(pt.CONTACT) As CONTACT, MAX(pt.EXPECTED_START_DATE) AS ESD FROM PROJ_TASK pt INNER JOIN PROJECT p ON p.PROJECTID=pt.PROJECTIDWHERE (pt.PROJECTNAME = @PROJECTNAME OR @PROJECTNAME IS NULL)AND(pt.TEXT_26 = @TEXT_26 OR @TEXT_26 IS NULL)AND(pt.NAME = 'Initial transport assembly' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'Receive and payout of materials' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'Transport assembly' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'Mechanical assembly' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'Pipe and wire' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'In-house commissioning' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME LIKE '%Ship' AND pt.TASK_STATUS <> 2 AND pt.CONTRACTUAL_MS <> 0 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME LIKE '%All Material Due' AND pt.TASK_STATUS <> 2 AND pt.CONTRACTUAL_MS <> 0 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME LIKE '%Ship' AND pt.TASK_TYPE = 0 AND pt.CONTRACTUAL_MS = 0)OR (pt.NAME = 'Material Forecast' AND pt.TASK_STATUS <> 2 AND pt.CONTRACTUAL_MS <> 0 AND p.CATEGORY <> 'SERVICE')GROUP BY TEXT_26, pt.NAME ORDER BY PROJECTNAME, TEXT_26, NAMESet NOCOUNT OFFEndGO This returns data like so:quote:
C04386130 CMS-C.04386.130 All material due C.04386.130 NULL cliang 2005-06-15 08:00:00.000------------------------------------------------------------------C04386130 CMS-C.04386.130 Teardown & ship C.04386.130 Qualification loop extension A2 NULL 2005-06-16 08:00:00.000------------------------------------------------------------------C04386130 Teardown & ship C.04386.130 Qualification loop extension A2 amartin 2005-06-15 08:00:00.000------------------------------------------------------------------C04387130 CMS-C.04387.130 All material due C.04387.130 Qualification loop extension cliang 2005-06-13 08:00:00.000------------------------------------------------------------------C04387130 CMS-C.04387.130 Teardown & ship C.04387.130 Qualification loop extension NULL 2005-06-14 08:00:00.000------------------------------------------------------------------C04387130 Teardown & ship C.04387.130 Qualification loop extension amartin 2005-06-13 08:00:00.000-------------------------------------------------------------------C04431200 CMS-Teardown & ship C.04431.200 Modification of dunnage rack NULL 2005-06-10 15:23:00.000-------------------------------------------------------------------C04431200 Mechanical assembly C.04431.200 Modification of dunnage rack jmuegge 2005-06-10 08:00:00.000-------------------------------------------------------------------
Basically you have 6 fields here, the first is ProjectName, the second is the task, the third is the job number, the fourth is the description of the job number, 5th is the contact, and 6th is a date field.So one row:C04431200 Mechanical assembly C.04431.200 Modification of dunnage rack jmuegge 2005-06-10 08:00:00.000The project is: C04431200THe task is: Mechanical assemblyThe job is: C.04431.200Description is: Modifiction of dunnage...Contact is: jmueggeDate is: 2005-06-10...The thing is this report is a cross tab report (matrix in rs) with the name of the task being the header. The problem is not with all the rows, the problem is with the rows that have the projectname / job inside of the actual task text. For instance in my example of the returned results I had the following:------------------------------------------------------------------C04387130 CMS-C.04387.130 Teardown & ship C.04387.130 Qualification loop extension NULL 2005-06-14 08:00:00.000------------------------------------------------------------------C04387130 Teardown & ship C.04387.130 Qualification loop extension amartin 2005-06-13 08:00:00.000-------------------------------------------------------------------These two have the job names C.04387.130 right inside the tasks "Teardown & Ship". Because of this it creates seperate headers for the report. I advised the users to not include project names in the tasks because that would make the matrix (cross tab) grow horizontally 'n' times (based on the number of tasks). But this was an argument I did not win because of some other reasons not worth mentioning. So what my thought was to get this query and kind of massage the data to get rid of the project names in the tasks. That way we end up with only say 5-7 tasks. The 5-7 tasks would become the columns in my cross tab query. Right now I could easily have 1000's of columns just because the task name changes because of the project name being embedded into it.So what do you think I should do, should I snatch the data, massage it and then use it ? Can I or even should I think of a temp table?Sample code of the process would be nice, but advise is definately fine with me!Thanks,Jon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
jhermiz
3564 Posts |
Posted - 2005-06-10 : 10:09:22
|
I guess I could do this:DROP TABLE P1SELECT MAX(pt.PROJECTNAME) AS PROJECTNAME, pt.NAME, pt.TEXT_26, MAX(pt.TEXT_27) AS WBSDescription, MAX(pt.CONTACT) As CONTACT, MAX(pt.EXPECTED_START_DATE) AS ESD INTO P1 FROM PROJ_TASK pt INNER JOIN PROJECT p ON p.PROJECTID=pt.PROJECTIDWHERE (pt.NAME = 'Initial transport assembly' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'Receive and payout of materials' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'Transport assembly' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'Mechanical assembly' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'Pipe and wire' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME = 'In-house commissioning' AND pt.TASK_STATUS <> 2 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME LIKE '%Ship' AND pt.TASK_STATUS <> 2 AND pt.CONTRACTUAL_MS <> 0 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME LIKE '%All Material Due' AND pt.TASK_STATUS <> 2 AND pt.CONTRACTUAL_MS <> 0 AND p.CATEGORY <> 'SERVICE')OR (pt.NAME LIKE '%Ship' AND pt.TASK_TYPE = 0 AND pt.CONTRACTUAL_MS = 0)OR (pt.NAME = 'Material Forecast' AND pt.TASK_STATUS <> 2 AND pt.CONTRACTUAL_MS <> 0 AND p.CATEGORY <> 'SERVICE')GROUP BY TEXT_26, pt.NAME ORDER BY PROJECTNAME, TEXT_26, NAMESELECT * FROM P1UPDATE P1 SET NAME='CMS-All material due' WHERE NAME LIKE '%All material due%'UPDATE P1 SET NAME='CMS-Teardown & ship' WHERE NAME LIKE '%Teardown & ship%'SELECT * FROM P1 And use P1 as my report source ?
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
 |
|
|
|
|
|
|
|
temp table ?