CODEWITHSUNDEEP
sqlsybase
Satish Uppalapati
Satish Uppalapati

Reputation: 23

Need help on a sybase query

I am using the below query to display the records,but the query is returning the duplicate records if the submitted date has a different time stamp ex:(07/30/2012 08:13:00 PM and 07/30/2012 08:14:00 PM) I need the time stamp to be displayed in Submitted date at the same time I don't want duplicates because of the group by clause convert(datetime, convert(varchar(17), fac.SUBMISSION_DT, 116)). It should only take the date part not the date with time stamp Any help to change the query to fit into this requirement will be appreciated.

SELECT  clm.REPRESENTATIVE as EMPLOYEE_ID , rep.USER_NM as EMPLOYEE_NAME,fac.SUBMITTED_BY as SUBMITTER_ID, srp.USER_NM as SUBMITTER_NAME,clm.CLAIM_AM as CLAIM_AM, sum(trn.ADJUSTED_AM) as SUBMITTED_AMOUNT,

**convert(datetime, convert(varchar(17), fac.SUBMISSION_DT, 116)) as SUBMITTED_DATE,**


csc.CLM_SCENARIO_DESC as SCENARIO_DESC, clm.CLAIM_TP as CLAIM_TP,fac.ACTION_ID as ACTION_ID, cac.ACTION_DESC as ACTION_DESC,  clm.CLAIM_NB as CLAIM_NB, cmp.CLAIM_ID as CLAIM_ID,cust.CUST_SGMNT_ID as CUST_SGMNT_ID,(CASE WHEN sum(trn.adjusted_am) <= 100000 THEN 'Y' ELSE 'N' END) as APPROVABLE, clm.LIABILITY_60DAYS_FLAG as LIABILITY_60DAYS_FLAG 


 FROM LIB clm, Book trn, CUST cust, FIN fac, secdbp01..SEC_USER rep, secdbp01..SEC_USER srp, ADO_CLM_SCENARIO_CD csc, ADO_CLAIM_ACTIONS cac, LCM_CLAIM_MAP cmp  


WHERE clm.CLAIM_NB = trn.CLAIM_NB  AND 
clm.CLAIM_NB = cust.CLAIM_NB  AND 
trn.STATUS_FLAG = 'P'         AND trn.TRAN_DISP = 'F'           AND 
trn.CLAIM_NB = fac.CLAIM_NB   AND
trn.POSTING_DT = fac.POSTING_DT   AND 
trn.TRANSACTION_ID = fac.TRANSACTION_ID  AND 
trn.BATCH_ID = fac.BATCH_ID   AND 
srp.USER_ID <> 'R300726'  AND 
clm.REPRESENTATIVE = rep.USER_ID  AND
srp.USER_ID = fac.SUBMITTED_BY  AND 
clm.CLM_SCENARIO = csc.CLM_SCENARIO  AND
clm.CLAIM_NB = cmp.REFERENCE_NUMBER  AND
fac.ACTION_ID = cac.ACTION_ID  AND 
clm.PROGRESS_CD IN ('03','04','15')  AND
isnull(clm.TRAINING_CLM_FLAG,'N') = 'N'  AND 
(clm.CLAIM_AM >= 0 AND 
clm.CLAIM_AM <= 1000) 

GROUP BY clm.REPRESENTATIVE, rep.USER_NM, fac.SUBMITTED_BY, srp.USER_NM, clm.CLAIM_AM,clm.CLAIM_NB,
**convert(datetime, convert(varchar(17), fac.SUBMISSION_DT, 116)),**

csc.CLM_SCENARIO_DESC, 
clm.CLAIM_TP, fac.ACTION_ID, cac.ACTION_DESC, cmp.CLAIM_ID, cust.CUST_SGMNT_ID, clm.LIABILITY_60DAYS_FLAG 
HAVING sum(trn.ADJUSTED_AM) <= 10000000
ORDER BY SUBMITTER_NAME asc

Upvotes: 0

Views: 236

Answers (1)

podiluska
podiluska

Reputation: 51494

Use 

convert(datetime, convert(varchar(11), fac.SUBMISSION_DT, 116))

instead of

convert(datetime, convert(varchar(17), fac.SUBMISSION_DT, 116))

Upvotes: 1

Related Questions