Showing posts with label value. Show all posts
Showing posts with label value. Show all posts

Monday, March 12, 2012

ODBC + CRecordset

I'm using sql serser 2000, CRecordset, CDatabase, ODBC, VS.NET 03, VC++

How cat I get from my data base ntext or nvarchar value?

if I get varchar everything is ok, but not nvarchar..(or text field type).

I need help please with this.

maybe the solusion is to cast from ntext to text?

How can I casting this in my statment?

// activity as ntext (16)

SqlString = "SELECT activity FROM tasks WHERE status_id=1";

Yael.

What is the code which works for you in the case of varchar? And what doesn't work exactly with nvarchar?

Please provide an excerpt and perhaps we could find a solution.

Thanks,

ODBC + CRecordset

I'm using sql serser 2000, CRecordset, CDatabase, ODBC, VS.NET 03, VC++

How cat I get from my data base ntext or nvarchar value?

if I get varchar everything is ok, but not nvarchar..(or text field type).

I need help please with this.

maybe the solusion is to cast from ntext to text?

How can I casting this in my statment?

// activity as ntext (16)

SqlString = "SELECT activity FROM tasks WHERE status_id=1";

Yael.

What is the code which works for you in the case of varchar? And what doesn't work exactly with nvarchar?

Please provide an excerpt and perhaps we could find a solution.

Thanks,

Wednesday, March 7, 2012

Obtaining the time from datetime field

From this value: 2002-11-22 00:00:22.000
I want to obtain in string type "00:00:22.000" including miliseconds
I try this but returns: 0:0:22:0
select
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
from cargaprocesos
Can you help me?
Thanks a lot,Enric, try:
convert(varchar(12), dt, 114)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||You should go with that:
Select Convert(varchar(12), GETDATE(), 114)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||Thanks Itzik, I had tried this:
select substring(convert(varchar(50),duracionul
t,113),13,12),
duracionult
from cargaprocesos
But it was failing.
regards,
"Itzik Ben-Gan" wrote:
> Enric, try:
> convert(varchar(12), dt, 114)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Enric" wrote:
>|||A disign suggestion:
I would create a function for this logic.
Makes the code more readable and it makes the logic reusable aswell.
Hope this is helpfull.
Marcel van Eijkel
( www.vaneijkel.com )

Obtaining Running Totals

I am working on a report and need to figure out a way to come up with
running totals based on a column value. Let me explain...
As shown in the query below, I am displaying "totalmealsize", "minmealsize",
and "maxmealsize" for a particular interval number (which can be anywhere
between 1 and 24). What I would like to do is display a running total for
each interval that summarizes all previous intervals. For example, given
the following data:
INTERVAL TOTAL RUNNING-TOTAL
1 5 5
2 3 8
3 1 9
4 7 16
5 4 20
As you can see, the RUNNING-TOTAL is the sum of all previous interval values
(I would want to do something similar with the min/max values as well). I
have tried various things but have not succeeded. I realize I can do this
in (C#) code as well as probably even create a user-defined function to
handle it. However, I would like to keep it in standard SQL if possible.
Any ideas?
<BEGIN SQL QUERY>
SELECT t.Name, tl.Level, id.sequencenumber, id.intervalnumber,
id.RunAnimalID,
id.totalmealsize AS SMS, id.minmealsize AS SMS_MIN, id.maxmealsize
AS SMS_MAX
FROM intervaldetail id
INNER JOIN TreatmentLevel tl ON id.RunAnimalID = tl.RunAnimalID
INNER JOIN Treatment t on t.TreatmentID = tl.TreatmentID
WHERE id.intervalsetid = 99
ORDER BY t.Name, tl.Level, id.sequencenumber
<END SQL QUERY>
-- Thanks, JeffTry this
Declare @.t table(INTERVAL int, TOTAL integer)
insert into @.T values(1,5)
insert into @.T values(2,3)
insert into @.T values(3,1)
insert into @.T values(4,7)
insert into @.T values(5,4)
select *,(select sum(total) from @.t where interval <=T.Interval) as
'Running Total' from @.t T
Madhivanan|||Here is a repro,
CREATE TABLE #Totals(ID int IDENTITY(1,1), total int)
INSERT INTO #Totals VALUES(100)
INSERT INTO #Totals VALUES(200)
INSERT INTO #Totals VALUES(300)
INSERT INTO #Totals VALUES(400)
SELECT A.ID , A.Total,(SELECT SUM(total)
FROM #Totals B WHERE B.ID <=A.ID) As RunningTotal
FROM #Totals A
DROP TABLE #Totals
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jeff B." <jsb@.community.nospam> wrote in message
news:e6k6GiVHFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I am working on a report and need to figure out a way to come up with
>running totals based on a column value. Let me explain...
> As shown in the query below, I am displaying "totalmealsize",
> "minmealsize", and "maxmealsize" for a particular interval number (which
> can be anywhere between 1 and 24). What I would like to do is display a
> running total for each interval that summarizes all previous intervals.
> For example, given the following data:
> INTERVAL TOTAL RUNNING-TOTAL
> 1 5 5
> 2 3 8
> 3 1 9
> 4 7 16
> 5 4 20
> As you can see, the RUNNING-TOTAL is the sum of all previous interval
> values (I would want to do something similar with the min/max values as
> well). I have tried various things but have not succeeded. I realize I
> can do this in (C#) code as well as probably even create a user-defined
> function to handle it. However, I would like to keep it in standard SQL
> if possible.
> Any ideas?
> <BEGIN SQL QUERY>
> SELECT t.Name, tl.Level, id.sequencenumber, id.intervalnumber,
> id.RunAnimalID,
> id.totalmealsize AS SMS, id.minmealsize AS SMS_MIN, id.maxmealsize
> AS SMS_MAX
> FROM intervaldetail id
> INNER JOIN TreatmentLevel tl ON id.RunAnimalID = tl.RunAnimalID
> INNER JOIN Treatment t on t.TreatmentID = tl.TreatmentID
> WHERE id.intervalsetid = 99
> ORDER BY t.Name, tl.Level, id.sequencenumber
> <END SQL QUERY>
> -- Thanks, Jeff
>|||Thanks everyone for your replies. The subquery did the trick. I had tried
that one before but I must have had the syntax wrong because I wasn't able
to get it to work. Now it is working just fine.
-- Thanks, Jeff
"Jeff B." <jsb@.community.nospam> wrote in message
news:e6k6GiVHFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I am working on a report and need to figure out a way to come up with
>running totals based on a column value. Let me explain...
> As shown in the query below, I am displaying "totalmealsize",
> "minmealsize", and "maxmealsize" for a particular interval number (which
> can be anywhere between 1 and 24). What I would like to do is display a
> running total for each interval that summarizes all previous intervals.
> For example, given the following data:
> INTERVAL TOTAL RUNNING-TOTAL
> 1 5 5
> 2 3 8
> 3 1 9
> 4 7 16
> 5 4 20
> As you can see, the RUNNING-TOTAL is the sum of all previous interval
> values (I would want to do something similar with the min/max values as
> well). I have tried various things but have not succeeded. I realize I
> can do this in (C#) code as well as probably even create a user-defined
> function to handle it. However, I would like to keep it in standard SQL
> if possible.
> Any ideas?
> <BEGIN SQL QUERY>
> SELECT t.Name, tl.Level, id.sequencenumber, id.intervalnumber,
> id.RunAnimalID,
> id.totalmealsize AS SMS, id.minmealsize AS SMS_MIN, id.maxmealsize
> AS SMS_MAX
> FROM intervaldetail id
> INNER JOIN TreatmentLevel tl ON id.RunAnimalID = tl.RunAnimalID
> INNER JOIN Treatment t on t.TreatmentID = tl.TreatmentID
> WHERE id.intervalsetid = 99
> ORDER BY t.Name, tl.Level, id.sequencenumber
> <END SQL QUERY>
> -- Thanks, Jeff

Obtaining count for identity

Is there a way to query a SQL server such that it will return the
current counter value of a field that increments automatically (an
identity field)? Using a MAX query might not necessarily give you the
correct information because it is possible that the high value records
were deleted. I checked the system tables and none of them appear to
have what I need. I suppose it would be possible to maintain a table
that lists all the incrementing columns, but ideally there would be a
way already built into SQL Server.

Thanks.On 15 Jun 2005 14:00:46 -0700, hartley_aaron@.hotmail.com wrote:

>Is there a way to query a SQL server such that it will return the
>current counter value of a field that increments automatically (an
>identity field)? Using a MAX query might not necessarily give you the
>correct information because it is possible that the high value records
>were deleted. I checked the system tables and none of them appear to
>have what I need. I suppose it would be possible to maintain a table
>that lists all the incrementing columns, but ideally there would be a
>way already built into SQL Server.
>Thanks.

Hi hartley_aaron,

Look up IDENT_CURRENT in Books Online.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Look up IDENT_CURRENT or @.@.IDENTITY in Books Online.

HTH,
Stu|||It worked great.

Thank you both!

Friday, February 24, 2012

Obtaining a value from Openquery?

Hi all,

I have an Informix Dynamic Server linked within my MS SQL 7 server. What I want to achieve is to be able to obtain a value from the informix table and then to use this value to update the MS SQL server table. I am doing this within a trigger on SQL Server. I am not doing this from infromix as I cant get informix to see the SQL Server.

My problem is that I dont know how to assign the query result to a variable so I can use it in my Update. Can anyone help me with my syntax?? Below is my variable settings and query within the Insert trigger...(Not sure if its correct)

DECLARE @.TSQL VARCHAR(100)
DECLARE @.NAMEID VARCHAR(10)

SET @.NAMEID = (Select Inserted.NameID from Inserted)

SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

EXEC (@.TSQL)

How do I set a variable with the nar_num value that I get back from the informix server. Any Help would be great.

Thanks
Anthonyyou would need to fill in the data type for nar_num but give this a try:

DECLARE @.TSQL VARCHAR(100)
, @.NAMEID VARCHAR(10)

create table #tmp(nar_num <data type>)

select @.NAMEID = min(NameID) from Inserted
while (@.NAMEID is not null) begin
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

truncate table #tmp
insert into #tmp
EXEC (@.TSQL)

select @.NAMEID = min(NameID) from Inserted where nameid > @.NAMEID
end

Please note that I changed things a bit to handle more than one one record.

Obtain unit percent with unit count divided by total count in query

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:

--Test Count/Count subquery

declare @.Date datetime

set @.date = '8/15/2007'

select

-- count returns unit data

Count(substring(m.PTNumber,3,3)) as PTCnt,

-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date) as TotalCnt,

-- attempting to calculate the percent by PTCnt/TotalCnt returns 0

(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date)) as AUPct

-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@.Date) and @.Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc

Thanks. Dan

I figured out my solution - The top integer needs to be cast as a decimal:

(CAST(Count(substring(m.PTNumber,3,3)) as Decimal(15,5))/

(select Count(substring(m1.PTNumber,3,3)) from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date)) * 100 as AUPct