Set Format - 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

Set Format

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-09-21 : 08:08:42

Hi,

I have the following query as shown;

SELECT     dbo.tblProjectPatients.Prac_no, dbo.tblProjectPatients.Prac_eid, COUNT(dbo.tblProjectPatients.Prac_no) AS Patients, dbo.tblProjects.ProjectDesc, 
                      dbo.tblProjects.FullFee, dbo.tblProjects.PartFee, dbo.tblPaymentRun.PaymentRunID
FROM         dbo.tblProjects INNER JOIN
                      dbo.tblProjectPatients ON dbo.tblProjects.ProjectID = dbo.tblProjectPatients.ProjectID INNER JOIN
                      dbo.tblPaymentRun ON dbo.tblProjectPatients.ProjectID = dbo.tblPaymentRun.ProjectID
WHERE     (dbo.tblProjectPatients.PayStatus = 'Full Pay') OR
                      (dbo.tblProjectPatients.PayStatus = 'Part Pay') AND (dbo.tblProjectPatients.DatePaid IS NULL)
GROUP BY dbo.tblProjectPatients.Prac_no, dbo.tblProjectPatients.Prac_eid, dbo.tblProjects.ProjectDesc, dbo.tblProjects.FullFee, dbo.tblProjects.PartFee, 
                      dbo.tblPaymentRun.PaymentRunID

No problem with the code, however, I want dbo.tblPaymentRun.PaymentRunID to be set to 4 digits...

For example if it is 290 it should be 0290, if it is 22 it should be 0022 etc...

Any help please..

Many thanks

Sachin.Nand

2937 Posts

Posted - 2010-09-21 : 08:26:10

quote:

Originally posted by dr223

Hi,

I have the following query as shown;

SELECT     dbo.tblProjectPatients.Prac_no, dbo.tblProjectPatients.Prac_eid, COUNT(dbo.tblProjectPatients.Prac_no) AS Patients, dbo.tblProjects.ProjectDesc, 
                      dbo.tblProjects.FullFee, dbo.tblProjects.PartFee,right('0000'+ '' + convert(varchar(20),dbo.tblPaymentRun.PaymentRunID),4)FROM         dbo.tblProjects INNER JOIN
                      dbo.tblProjectPatients ON dbo.tblProjects.ProjectID = dbo.tblProjectPatients.ProjectID INNER JOIN
                      dbo.tblPaymentRun ON dbo.tblProjectPatients.ProjectID = dbo.tblPaymentRun.ProjectID
WHERE     (dbo.tblProjectPatients.PayStatus = 'Full Pay') OR
                      (dbo.tblProjectPatients.PayStatus = 'Part Pay') AND (dbo.tblProjectPatients.DatePaid IS NULL)
GROUP BY dbo.tblProjectPatients.Prac_no, dbo.tblProjectPatients.Prac_eid, dbo.tblProjects.ProjectDesc, dbo.tblProjects.FullFee, dbo.tblProjects.PartFee, 
                      dbo.tblPaymentRun.PaymentRunID

PBUH

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-09-21 : 11:54:28

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 11:57:53

whats the need of that '' in between? wont this suffice?

right('0000'+ convert(varchar(20),dbo.tblPaymentRun.PaymentRunID),4)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources