| Author |
 Topic  |
|
|
dr223
Constraint Violating Yak Guru
359 Posts |
 Posted - 11/06/2012 : 09:52:41
|
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
3328 Posts |
Posted - 11/06/2012 : 11:07:35
|
;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. |
 |
|
|
dr223
Constraint Violating Yak Guru
359 Posts |
Posted - 11/06/2012 : 11:24:19
|
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.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47051 Posts |
Posted - 11/06/2012 : 12:11:06
|
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/
|
|
|
|
dr223
Constraint Violating Yak Guru
359 Posts |
Posted - 11/06/2012 : 12:35:21
|
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?
|
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3826 Posts |
Posted - 11/06/2012 : 12:49:17
|
| Just use the ROW_NUMBER() function and concatenate the row number value to your column. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47051 Posts |
Posted - 11/06/2012 : 13:39:07
|
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/
|
|
|
|
dr223
Constraint Violating Yak Guru
359 Posts |
Posted - 11/07/2012 : 06:48:21
|
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
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1416 Posts |
Posted - 11/07/2012 : 07:05:08
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47051 Posts |
Posted - 11/07/2012 : 09:14:53
|
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/
|
|
|
| |
Topic |
|
Select statement
