By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,918 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,918 IT Pros & Developers. It's quick & easy.

SQL 2K Combining similiar rows without creating temp table?

P: 5
Good day,
I am stuck in a strange situation. SQL 2000 Server, creating a stored procedure to use in Crystal Reports 11.
I am trying to build a report without creating a table or temprorary table in the database. Not too bad but ugly nonetheless.
I succedded in creating the process using multiple stored procedures, but the report takes hours to run in Crystal which more less indicates I failed to get a workable solution.
I have a few functions building the data from multiple fields and the results generate duplicate record rows I wish to add together. They are duplicates by the PROJ_ID but they are seperate entries in the database with different results.

In the report I just wish to combine the rows with the same PROJ_ID before the statement finally offers the data as a sum of each rows (Expense, Hours, Labor) between the duplicate record fields.

The Query is as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. LEFT(MY_DATABASE.PROJ.L1_PROJ_SEG_ID + '.' + MY_DATABASE.PROJ.L2_PROJ_SEG_ID + '.' + MY_DATABASE.PROJ.L3_PROJ_SEG_ID,9) AS PROJ_ID,
  3. MY_DATABASE.PROJ.EMPL_ID AS PM,
  4. MY_DATABASE.PROJ.PROJ_NAME AS [Description],
  5. MY_DATABASE.PROJ.CUST_ID AS Customer,
  6. (MY_DATABASE.PSR_HDR.py_incur_hrs + MY_DATABASE.PSR_HDR.ytd_incur_hrs) as Hours,
  7. Labor = SUM(CASE WHEN SUB_TOT_TYPE_NO IN (2, 4, 5) THEN (PY_INCUR_AMT + YTD_INCUR_AMT) ELSE 0.0 END), 
  8. Expense = SUM(CASE WHEN SUB_TOT_TYPE_NO = 3 THEN (PY_INCUR_AMT + YTD_INCUR_AMT) ELSE 0.0 END),
  9. MY_DATABASE.PSR_HDR.bal_due_amt AS Bal_Due
  10.  
  11. FROM 
  12. MY_DATABASE.PROJ, 
  13. MY_DATABASE.PSR_FINAL_DATA, 
  14. MY_DATABASE.PSR_HDR    
  15.  
  16. WHERE 
  17. MY_DATABASE.PROJ.PROJ_ID = MY_DATABASE.PSR_FINAL_DATA.PROJ_ID
  18. and MY_DATABASE.PROJ.proj_id = MY_DATABASE.PSR_HDR.proj_id
  19. and MY_DATABASE.PSR_HDR.FY_CD IN ('2006','2007')
  20. and MY_DATABASE.PSR_HDR.pd_no = '5'
  21. AND MY_DATABASE.PSR_FINAL_DATA.FY_CD IN ('2006','2007') 
  22. AND MY_DATABASE.PSR_FINAL_DATA.PD_NO = '5' 
  23. AND MY_DATABASE.PSR_FINAL_DATA.RATE_TYPE IN ('T', 'N') 
  24.  
  25. GROUP BY 
  26. left(MY_DATABASE.PROJ.L1_PROJ_SEG_ID + '.' + MY_DATABASE.PROJ.L2_PROJ_SEG_ID + '.' + MY_DATABASE.PROJ.L3_PROJ_SEG_ID,9), 
  27. MY_DATABASE.PROJ.EMPL_ID, 
  28. MY_DATABASE.PROJ.PROJ_NAME, 
  29. MY_DATABASE.PROJ.CUST_ID,
  30. MY_DATABASE.PSR_HDR.bal_due_amt,
  31. (MY_DATABASE.PSR_HDR.py_incur_hrs + MY_DATABASE.PSR_HDR.ytd_incur_hrs) 
  32.  
  33. ORDER BY 1 ASC
  34.  
Jun 6 '07 #1
Share this Question
Share on Google+
5 Replies


P: 5
Is there an operator or function I can perform in the WHERE Statement?
I've never tried to do this so I am unsure of the context to search for answers either.
Jun 6 '07 #2

P: 5
In simpler terms,
Proj_ID |Type |SectionID
V06005.00 |PUBLIC SCHOOLS |X
V06005.00 |PUBLIC SCHOOLS |Z
V06006.00 |OFFC/INDUS/TELE |Z
V06007.00 |GOVT MUNI OFFC |X
V06007.00 |GOVT MUNI OFFC |Z

Example V06007.00 I want rows with "SectionID" X & Z as one row.
Jun 6 '07 #3

P: 44
In simpler terms,
Proj_ID |Type |SectionID
V06005.00 |PUBLIC SCHOOLS |X
V06005.00 |PUBLIC SCHOOLS |Z
V06006.00 |OFFC/INDUS/TELE |Z
V06007.00 |GOVT MUNI OFFC |X
V06007.00 |GOVT MUNI OFFC |Z

Example V06007.00 I want rows with "SectionID" X & Z as one row.

I have seen a simillar kind of problem solved by creating a function and calling it from the query to get the required result as mentioned by you .. this is really a good problem and every one in their coding time ll definitely face simillar challenge .. can some one help solve this ?
Jun 9 '07 #4

P: 5
I actually did a work around by creating another database pulling the data from the one I cannot write to.
Now I can create tables as needed.
But the same problem arises.
How can I get 3 records with different values but the same ID to sum the values of each column into one row with that ID?
Jul 18 '07 #5

P: 5
I found the main cause of the variation that gave me 3 records to begin with.
The only way to generate single records is to create the first insert using only the the Project ID, then each column I need a sum of with a GROUP BY function. The other records can have something as frustrating as a simple mis-spelling causing it to be different and nullifying the group by. So I will have to run an insert into a merged table then an update to make the records correctly.

Example:
INSERT INTO MyMergedTable (proj_id, t_hrs, t_labor, t_expense, t_billed, t_due, t_amt)

SELECT
Proj_ID,
SUM(Hours) AS T_Hrs,
SUM(Labor) AS T_Labor,
SUM(Expense) AS T_Expense,
SUM(billed_amt) AS T_Billed,
SUM(BAL_DUE_AMT) AS T_Due,
SUM(PROJ_F_TOT_AMT) AS T_Amt

FROM
MyReportTable

GROUP BY
Proj_ID

After running this I run an UPDATE on the merged table pulling details from the other items based on one of the multiple records key values i.e. where L3 = 'Z'.

Now if I can just find a way to make sure the accountant and I are speaking the same language when defining the goals of this =)
Jul 23 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.