how to chnage the query - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to chnage the query

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2008-11-14 : 04:44:02

hi,

am trying to get row which is entered first i have given the row_nummber for that but am geting the correct data.
i had taken the rowid to the date column

ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY,0,CAH.CAH_ASSIGNED_DATE) ORDER BY CAH.CAH_SEQ_NO) AS rowid

date seq_no rowid effort activity

2008-07-04 22:54:26.767 28348 1 2.00 Configuration
2008-07-04 22:55:08.300 28349 2 2.00 Configuration
2008-07-04 22:56:13.333 28350 3 2.00 Configuration
2008-07-04 22:57:21.507 28351 4 2.00 Configuration
2008-07-04 23:00:18.197 28352 5 2.00 Configuration
2008-07-04 23:01:14.100 28353 6 2.00 Configuration
2008-07-04 23:03:28.580 28354 7 2.00 Configuration
2008-07-04 23:04:53.523 28355 8 2.00 Configuration
2008-07-11 21:56:45.800 29970 1 2.00 Configuration
2008-07-11 21:57:31.820 29971 2 2.00 Configuration
2008-10-31 01:50:21.140 41854 1 2.00 Configuration
2008-10-31 01:50:34.217 41855 2 2.00 Configuration
2008-10-31 02:22:34.967 41866 3 2.00 Configuration

i want the first date i.e 2008-07-04 22:54:26.767 for the configuration if i put the min also for the date am geting all the rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 04:49:31

SELECT TOP 1 * FROM Table1
ORDER BY date

E 12°55'05.63"
N 56°04'39.26"

shm
Yak Posting Veteran

86 Posts

Posted - 2008-11-14 : 05:12:05

hi thanks for reply
but i think i din't asked question properly
wt is if i query the result am getting is

date seq_no effort activity subactivity

2008-07-04 22:54:26.767 28348 0.50 Reviews Specification and Document Review
2008-07-04 22:54:26.767 28348 0.50 Reviews Unit test case review
2008-07-04 22:54:26.767 28348 2.00 Coding Requirement Analysis
2008-07-04 22:54:26.767 28348 2.00 Configur Configuration Management Activities
2008-07-04 22:54:26.767 28348 2.00 Testing Unit Testing ( Independent Testers)
2008-07-04 22:54:26.767 28348 6.00 Coding Coding
2008-07-04 22:55:08.300 28349 0.50 Reviews Specification and Document Review
2008-07-04 22:55:08.300 28349 0.50 Reviews Unit test case review

here for the activity and subactivity am getng cartesian product like that bcoz for the proj i have only 6 activity
but am getting 78 rows means for each activity there are 13 rows like that it is coming and also the seq_no is coming repeated it shld come 28348,29349...and so on
upto activity coding and subactivity coding it shld come and also the seq_no shld nt be same...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 05:17:08

Post your query?

E 12°55'05.63"
N 56°04'39.26"

shm
Yak Posting Veteran

86 Posts

Posted - 2008-11-14 : 05:30:05

here is the query

SELECT CR.CRQ_CREATED_DATE AS INITIATED_DATE, CR.RECEIPT_DATE RECEIPT_DATE,
CR.CRQ_TO_BE_CLOSED_DATE AS EXPECTED_CLOSURE_DATE,CR.ACTUAL_IMPLEMENTED AS START_DATE,
CAH.CAH_ASSIGNED_DATE AS ASSIGNED_DATE,CAH.CAH_SEQ_NO,CEA.CR_ESTIMATED_VALUE AS ESTIMATED_EFFORT,
SDA.SDLC_ITEM_DESC AS ACTIVITY,SA.SUB_ITEM_DESC AS SUBACTIVITY, CR.ACTUAL_CLOSURE_DATE ACTUAL_CLOSURE_DATE
FROM CHANGE_REQ AS CR
INNER JOIN PROJECT_MODULE PM ON PM.PMO_MOD_SEQ_NO = CR.PMO_MOD_SEQ_NO
INNER JOIN PROJECT_PHASE PP ON PP.PPH_SEQ_NO = PM.PPH_SEQ_NO
INNER JOIN PROJECT P ON P.PRJ_SEQ_NO = PP.PRJ_SEQ_NO
LEFT OUTER JOIN SYS_BUSINESS_CODE_DETAIL AS SB ON SB.SBD_ITEM_CODE = CR.CRQ_COMPLEXITY_ITEM_CODE AND SB.SBM_TYPE_CODE = 'CM'
LEFT OUTER JOIN SYS_BUSINESS_CODE_DETAIL AS SB1 ON SB1.SBD_ITEM_CODE = CR.CRQ_INITIATED_BY_ITEM_CODE AND SB1.SBM_TYPE_CODE = 'CC'
LEFT OUTER JOIN SYS_BUSINESS_CODE_DETAIL AS SB2 ON SB2.SBD_ITEM_CODE = CR.CRQ_PRIORITY_ITEM_CODE AND SB2.SBM_TYPE_CODE IN ('SV', 'PCR')
LEFT OUTER JOIN SYS_BUSINESS_CODE_DETAIL AS SB3 ON SB3.SBD_ITEM_CODE = CR.CRQ_STATUS_ITEM_CODE AND SB3.SBM_TYPE_CODE = 'SS'
LEFT JOIN CHANGE_REQ_ASSIGN_HISTORY CAH ON CAH.CRQ_SEQ_NO =CR.CRQ_SEQ_NO
LEFT OUTER JOIN SYS_BUSINESS_CODE_DETAIL AS SB4 ON SB4.SBD_ITEM_CODE = CR.CRQ_CHANGE_TYPE_CODE AND SB4.SBM_TYPE_CODE = 'TC'
LEFT OUTER JOIN EMPLOYEE AS EMP ON EMP.EMP_SEQ_NO = CR.CRQ_ASSIGNED_TO_EMP_ID
LEFT OUTER JOIN CUSTOMER_CONTACT AS CC ON CC.CCO_SEQ_NO = CR.CRQ_INITIATED_CCO_SEQ_NO
LEFT OUTER JOIN CR_ESTIMATED_ACTUAL_VALUES AS CEA ON CEA.CRQ_SEQ_NO = CR.CRQ_SEQ_NO
LEFT OUTER JOIN SUB_ACTIVITY AS SA ON SA.SUB_ITEM_CODE = CEA.SUB_ITEM_CODE
LEFT OUTER JOIN SDLC_ACTIVITY AS SDA ON SDA.SDLC_ITEM_CODE = SA.SDLC_ITEM_CODE
LEFT OUTER JOIN PROJECT_TASK AS PT ON PT.CRQ_SEQ_NO = CR.CRQ_SEQ_NO
LEFT OUTER JOIN PROJECT_LOG_TIME AS PLT ON PLT.PLT_OBJECT = PT.PTK_SEQ_NO AND PLT.PLT_ACT_TYPE_CODE = SA.SUB_ITEM_CODE

WHERE P.PRJ_SEQ_NO = 11 AND CR.CRQ_NUMBER ='CS03032-12'
AND(CR.CRQ_SEQ_NO NOT IN (SELECT DISTINCT CREATED_FROM_CR
FROM PROJECT_MODULE
WHERE (CREATED_FROM_CR IS NOT NULL))) AND (PT.CRQ_SEQ_NO IS NOT NULL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 05:36:46

SELECT *
FROM ( your selet here, row_NUMBER() over (partition by ... order by ...) AS recid from ...) AS d
where d = 1

E 12°55'05.63"
N 56°04'39.26"

shm
Yak Posting Veteran

86 Posts

Posted - 2008-11-14 : 05:53:56

i tried wt u tld above but the data is not coming correct only 4 activity is coming another 2 activtiy is not thr...
i tried in diff ways but am nt getting the correct data...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources