Select statement - 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

General SQL Server Forums

New to SQL Server Programming

Select statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-11-06 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-06 : 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

444 Posts

Posted - 2012-11-06 : 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

52326 Posts

Posted - 2012-11-06 : 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

444 Posts

Posted - 2012-11-06 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-06 : 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

52326 Posts

Posted - 2012-11-06 : 13:39:07

[code]
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

[/code]

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-11-07 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-07 : 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

52326 Posts

Posted - 2012-11-07 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources