SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dr223
Constraint Violating Yak Guru

386 Posts

Posted - 11/06/2012 :  09:52:41  Show Profile  Reply with Quote
Hi,

SELECT     TOP (100) PERCENT dbo.TblChangeControlDet.ChangeID, dbo.TblChangeType.TypeCode + '/' + dbo.TblUser.Initials + REPLACE(CONVERT(VARCHAR(10),
                       dbo.TblChangeControlDet.ChangeRaisedDate, 103), '/', '') AS RefNo, dbo.TblChangeControlDet.ChangeRaisedDate
FROM         dbo.TblChangeControlDet INNER JOIN
                      dbo.TblChangeType ON dbo.TblChangeControlDet.ChangeType = dbo.TblChangeType.TypeDesc INNER JOIN
                      dbo.TblUser ON dbo.TblChangeControlDet.RequestBy = dbo.TblUser.user_full_name
ORDER BY dbo.TblChangeControlDet.ChangeID


Results

ChangeID RefNo ChangeRaisedDate
1 AppE/EB06112012 06/11/12 14:37:04
2 AppE/EB06112012 06/11/12 14:50:18
3 AppE/EB06112012 06/11/12 14:58:11


Now, I want the RefNo to be unique any hints on extension of the RefNo to have one reference unique to another..

Thank you

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/06/2012 :  11:07:35  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
;with cte1 as
(
SELECT dbo.TblChangeControlDet.ChangeID, dbo.TblChangeType.TypeCode + '/' + dbo.TblUser.Initials + REPLACE(CONVERT(VARCHAR(10),
dbo.TblChangeControlDet.ChangeRaisedDate, 103), '/', '') AS RefNo, dbo.TblChangeControlDet.ChangeRaisedDate
FROM dbo.TblChangeControlDet INNER JOIN
dbo.TblChangeType ON dbo.TblChangeControlDet.ChangeType = dbo.TblChangeType.TypeDesc INNER JOIN
dbo.TblUser ON dbo.TblChangeControlDet.RequestBy = dbo.TblUser.user_full_name
) ,
cte2 as
(select *, seq = row_number() over (partition by RefNo order by ChangeID) from cte1)
select ChangeID, RefNo = RefNo + '_' + right('00000000' + convert(varchar(8),seq),8), ChangeRaisedDate
from cte2
ORDER BY ChangeID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

dr223
Constraint Violating Yak Guru

386 Posts

Posted - 11/06/2012 :  11:24:19  Show Profile  Reply with Quote
Note: I have only considered the RefNo here;

The Results I received is as shown below for the RefNo;

Results:

RefNo

AppE/EB06112012_00000001
AppE/EB06112012_00000001
CWU/EG06112012_00000001
JP/EG06112012_00000001
WW/EG06112012_00000001
WW/EG06112012_00000001
TT/DW06112012_00000001


What I want to have instead

Results:

AppE/EB06112012
AppE/EB06112012_1
CWU/EG06112012
JP/EG06112012_1
WW/EG06112012_2
WW/EG06112012_3
TT/DW06112012

Note:

It checks for example EG06112012 if it exists the next RefNo add _1, _2 etc so it becomes JP/EG06112012_1, WW/EG06112012_2 etc.
Meaning more than one reference number has been added for the person EG on the same date 06112012.
However, the following date it will go back to EG07112012 unless more than one record has been added then it will take in _1 as the next value.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2012 :  12:11:06  Show Profile  Reply with Quote
why is CWU/EG06112012 not having _<seqno> not added to it then? as per your rules it should be treated just as JP/EG06112012_1 as far as i understand


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dr223
Constraint Violating Yak Guru

386 Posts

Posted - 11/06/2012 :  12:35:21  Show Profile  Reply with Quote

I am only considering a user making more than one request (ie., have more than one reference number) on a single day - for the first ref_no raised an entity _1 is not necessary.
However, ok lets assume if only one reference is raised = entity _1 is added and any other raised references should have _2, _3 etc.. so the results will be like;

Results:

AppE/EB06112012_1
AppE/EB06112012_2
CWU/EG06112012_1
JP/EG06112012_2
WW/EG06112012_3
WW/EG06112012_4
TT/DW06112012_1

How will achieve this?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4603 Posts

Posted - 11/06/2012 :  12:49:17  Show Profile  Reply with Quote
Just use the ROW_NUMBER() function and concatenate the row number value to your column.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2012 :  13:39:07  Show Profile  Reply with Quote

SELECT     TOP (100) PERCENT dbo.TblChangeControlDet.ChangeID, dbo.TblChangeType.TypeCode + '/' + dbo.TblUser.Initials + REPLACE(CONVERT(VARCHAR(10),
                       dbo.TblChangeControlDet.ChangeRaisedDate, 103), '/', '') + '_' + CAST(ROW_NUMBER() OVER (PARTITION BY  dbo.TblUser.Initials ,dbo.TblChangeControlDet.ChangeRaisedDate ORDER BY dbo.TblUser.Initials) AS varchar(5)) AS RefNo, dbo.TblChangeControlDet.ChangeRaisedDate
FROM         dbo.TblChangeControlDet INNER JOIN
                      dbo.TblChangeType ON dbo.TblChangeControlDet.ChangeType = dbo.TblChangeType.TypeDesc INNER JOIN
                      dbo.TblUser ON dbo.TblChangeControlDet.RequestBy = dbo.TblUser.user_full_name
ORDER BY dbo.TblChangeControlDet.ChangeID




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dr223
Constraint Violating Yak Guru

386 Posts

Posted - 11/07/2012 :  06:48:21  Show Profile  Reply with Quote
Ok Tried the above suggestion by visakh16

Result:

ChangeID RefNo ChangedRaisedDate
1 AppE/EB06112012_1 06/11/12 14:37:04
2 CWU/EB06112012_1 06/11/12 16:12:50
3 CWU/GM06112012_1 06/11/12 16:13:30
4 CWU/EB07112012_1 07/11/12 11:43:16
5 CWU/EB07112012_1 07/11/12 11:45:31
6 CWU/EB07112012_1 07/11/12 11:46:07


Note: for the RefNo it is adding the entity _1 at all the RefNos.

I expect the result to be kind of;

ChangeID RefNo ChangedRaisedDate
1 AppE/EB06112012_1 06/11/12 14:37:04
2 CWU/EB06112012_2 06/11/12 16:12:50
3 CWU/GM06112012_1 06/11/12 16:13:30
4 CWU/EB07112012_1 07/11/12 11:43:16
5 CWU/EB07112012_2 07/11/12 11:45:31
6 CWU/EB07112012_3 07/11/12 11:46:07

Any help please..

Thanks

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 11/07/2012 :  07:05:08  Show Profile  Reply with Quote
change your query as follows:

CAST(ROW_NUMBER() OVER (PARTITION BY dbo.TblUser.Initials, convert(varchar(10),dbo.TblChangeControlDet.ChangeRaisedDate,103) ORDER BY dbo.TblUser.Initials) AS varchar(5)) AS RefNo,

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/07/2012 :  09:14:53  Show Profile  Reply with Quote
quote:
Originally posted by dr223

Ok Tried the above suggestion by visakh16

Result:

ChangeID RefNo ChangedRaisedDate
1 AppE/EB06112012_1 06/11/12 14:37:04
2 CWU/EB06112012_1 06/11/12 16:12:50
3 CWU/GM06112012_1 06/11/12 16:13:30
4 CWU/EB07112012_1 07/11/12 11:43:16
5 CWU/EB07112012_1 07/11/12 11:45:31
6 CWU/EB07112012_1 07/11/12 11:46:07


Note: for the RefNo it is adding the entity _1 at all the RefNos.

I expect the result to be kind of;

ChangeID RefNo ChangedRaisedDate
1 AppE/EB06112012_1 06/11/12 14:37:04
2 CWU/EB06112012_2 06/11/12 16:12:50
3 CWU/GM06112012_1 06/11/12 16:13:30
4 CWU/EB07112012_1 07/11/12 11:43:16
5 CWU/EB07112012_2 07/11/12 11:45:31
6 CWU/EB07112012_3 07/11/12 11:46:07

Any help please..

Thanks




ok reason was i think you had tiome in dates. just tweak it like


SELECT     TOP (100) PERCENT dbo.TblChangeControlDet.ChangeID, dbo.TblChangeType.TypeCode + '/' + dbo.TblUser.Initials + REPLACE(CONVERT(VARCHAR(10),
                       dbo.TblChangeControlDet.ChangeRaisedDate, 103), '/', '') + '_' + CAST(ROW_NUMBER() OVER (PARTITION BY  dbo.TblUser.Initials ,DATEDIFF(dd,0,dbo.TblChangeControlDet.ChangeRaisedDate) ORDER BY dbo.TblUser.Initials) AS varchar(5)) AS RefNo, dbo.TblChangeControlDet.ChangeRaisedDate
FROM         dbo.TblChangeControlDet INNER JOIN
                      dbo.TblChangeType ON dbo.TblChangeControlDet.ChangeType = dbo.TblChangeType.TypeDesc INNER JOIN
                      dbo.TblUser ON dbo.TblChangeControlDet.RequestBy = dbo.TblUser.user_full_name
ORDER BY dbo.TblChangeControlDet.ChangeID




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000