Number formating

Just moving from Access to SQL. My end goal is to have SQL mail all my finished reports for me but my users need to having numbers separated by commas and all monetary values prefixed with £. Have looked at books on line for formats and number separators but cannot find anything
SELECT tblTradeData.[Date] AS Today,
SUM(TradeData.Units) AS TotalUnits,
SUM(tblTradeData.[Value]) AS TotalValue,
SUM(tblTradeData.Cost) AS TotalCos
FROM qryToday INNER JOI
tblTradeData ON qryToday.TodayDate = tblTradeData.[Date
GROUP BY tblTradeData.[Date]SQL Server can easily format float, real, money and datetimes... ( See the
convert command in books on line.)... For other formatting, as you want, you
will either have to do it manually using replace, etc... or (probably a
better solution) use a report writing tool. (Crystal, MS Report Services.)
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> Just moving from Access to SQL. My end goal is to have SQL mail all my
finished reports for me but my users need to having numbers separated by
commas and all monetary values prefixed with £. Have looked at books on
line for formats and number separators but cannot find anything.
>
> SELECT tblTradeData.[Date] AS Today,
> SUM(TradeData.Units) AS TotalUnits,
> SUM(tblTradeData.[Value]) AS TotalValue,
> SUM(tblTradeData.Cost) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate =tblTradeData.[Date]
> GROUP BY tblTradeData.[Date]|||I' am not completely sure what Kevin ask's but here is a suggestion :
SELECT
'$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
AS Today,
', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
FROM qryToday INNER JOIN
tblTradeData ON qryToday.TodayDate = tblTradeData.[Date]
GROUP BY tblTradeData.[Date]
Depending on the length of the 'values' varchar(20) can be altered to
a longer or shorter string, for fixed length char can also be used instead
of
varchar.
Does this help ?
ben brugman
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...
> SQL Server can easily format float, real, money and datetimes... ( See the
> convert command in books on line.)... For other formatting, as you want,
you
> will either have to do it manually using replace, etc... or (probably a
> better solution) use a report writing tool. (Crystal, MS Report Services.)
>
> "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> > Just moving from Access to SQL. My end goal is to have SQL mail all my
> finished reports for me but my users need to having numbers separated by
> commas and all monetary values prefixed with £. Have looked at books on
> line for formats and number separators but cannot find anything.
> >
> >
> > SELECT tblTradeData.[Date] AS Today,
> > SUM(TradeData.Units) AS TotalUnits,
> > SUM(tblTradeData.[Value]) AS TotalValue,
> > SUM(tblTradeData.Cost) AS TotalCost
> > FROM qryToday INNER JOIN
> > tblTradeData ON qryToday.TodayDate => tblTradeData.[Date]
> > GROUP BY tblTradeData.[Date]
>|||In addition to my last mail.
The [no lock] hint, does increase the change on problems,
because of this hint, if there is a update on the number
from another process which has not committed yet, the
code will keep looping.
If the [no lock] hint is taken out the process will wait in
this place until the other process finishes and then continue.
This will consume less CPU.
If the [no lock] was placed because of a (logical) deadlock,
(because two processes both ask for two numbers but
in a different order, one asks first for the Tracking_id and then
for the Info_ID, the other process the other way round) and
both are done in a transaction context then these two processes
keep the stored procedure looping. (Both have one number and
won't stop till they get the other number). With the [no lock] hint
taken out the sql-server will detect a deadlock and one of the
processes will be choosen as a deadlock victim.
(A deadlock victim is better than looping processes).
Please keep us informed, of what your problem was and which
solution you have choosen,
ben brugman
"ben brugman" <ben@.niethier.nl> wrote in message
news:#QEjaS$6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> I' am not completely sure what Kevin ask's but here is a suggestion :
> SELECT
> '$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
> AS Today,
> ', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate => tblTradeData.[Date]
> GROUP BY tblTradeData.[Date]
> Depending on the length of the 'values' varchar(20) can be altered to
> a longer or shorter string, for fixed length char can also be used instead
> of
> varchar.
> Does this help ?
> ben brugman
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...
> > SQL Server can easily format float, real, money and datetimes... ( See
the
> > convert command in books on line.)... For other formatting, as you want,
> you
> > will either have to do it manually using replace, etc... or (probably a
> > better solution) use a report writing tool. (Crystal, MS Report
Services.)
> >
> >
> > "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> > news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> > > Just moving from Access to SQL. My end goal is to have SQL mail all
my
> > finished reports for me but my users need to having numbers separated
by
> > commas and all monetary values prefixed with £. Have looked at books on
> > line for formats and number separators but cannot find anything.
> > >
> > >
> > > SELECT tblTradeData.[Date] AS Today,
> > > SUM(TradeData.Units) AS TotalUnits,
> > > SUM(tblTradeData.[Value]) AS TotalValue,
> > > SUM(tblTradeData.Cost) AS TotalCost
> > > FROM qryToday INNER JOIN
> > > tblTradeData ON qryToday.TodayDate => > tblTradeData.[Date]
> > > GROUP BY tblTradeData.[Date]
> >
> >
>