Grand Total for field problem

Hello,

In my SRS report, I have a field called "Alternate" where I need to check to find out if my picklist value = 2. This works out just great.

I have created a variable called "Total_Alternate". I don't seem to be able to get the right Grand Total here by using the code below

(1) Value in expression field for textbox9 - gives right total in this field for each group
=SUM(IIF(Fields!new_rpcstatus.value=2, CInt(Fields!Total_Alternate.value), 0))

(2) Value in Table Footer for the above field - doesn't give me the right number
=(Fields!Total_Alternate.Value)

What am I doing wrong?

If you want

number then

=Count(Fields!Total_Alternate.Value)

or if you want sum of these then

=Sum(Fields!Total_Alternate.Value)

sql

Grabbing CURRENT Parameter Data

So it looks like when you add an expression which grabs a parameter value
(Parameters!SRNumber.Value), it grabs the data from when the "View Report"
button was originally pressed. My goal is to allow someone to punch in new
data into a parameter and without having to re-run the report, be able to use
that for an action. Any ideas? Example of what I am attempting to do below.
= "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
+Parameters!SRNumber.Value + " has been assigned to you"...
(SRNumber is a blank parameter which isn't actually used in any of my
queries, but rather just there for this purpose)On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> So it looks like when you add an expression which grabs a parameter value
> (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> button was originally pressed. My goal is to allow someone to punch in new
> data into a parameter and without having to re-run the report, be able to use
> that for an action. Any ideas? Example of what I am attempting to do below.
> = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> +Parameters!SRNumber.Value + " has been assigned to you"...
> (SRNumber is a blank parameter which isn't actually used in any of my
> queries, but rather just there for this purpose)
This is a long shot, but you could try using javascript. Normally, the
last value selected before the View Report button is selected is shown
in an expression based on a parameter. Sorry that I could not be of
further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks, that is what I was thinking too-- just hoping there was a secret call
I could make to get the current value :)
"EMartinez" wrote:
> On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> > So it looks like when you add an expression which grabs a parameter value
> > (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> > button was originally pressed. My goal is to allow someone to punch in new
> > data into a parameter and without having to re-run the report, be able to use
> > that for an action. Any ideas? Example of what I am attempting to do below.
> >
> > = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> > +Parameters!SRNumber.Value + " has been assigned to you"...
> > (SRNumber is a blank parameter which isn't actually used in any of my
> > queries, but rather just there for this purpose)
>
> This is a long shot, but you could try using javascript. Normally, the
> last value selected before the View Report button is selected is shown
> in an expression based on a parameter. Sorry that I could not be of
> further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Dec 12, 10:21 pm, Peetaah <Peet...@.discussions.microsoft.com>
wrote:
> Thanks, that is what I was thinking too-- just hoping there was a secret call
> I could make to get the current value :)
> "EMartinez" wrote:
> > On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> > > So it looks like when you add an expression which grabs a parameter value
> > > (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> > > button was originally pressed. My goal is to allow someone to punch in new
> > > data into a parameter and without having to re-run the report, be able to use
> > > that for an action. Any ideas? Example of what I am attempting to do below.
> > > = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> > > +Parameters!SRNumber.Value + " has been assigned to you"...
> > > (SRNumber is a blank parameter which isn't actually used in any of my
> > > queries, but rather just there for this purpose)
> > This is a long shot, but you could try using javascript. Normally, the
> > last value selected before the View Report button is selected is shown
> > in an expression based on a parameter. Sorry that I could not be of
> > further assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

grabbing a value from listbox to query database

hello forum,

I need to grab astring value from alist box in from a web form,
and pass it to a sql select command statement where that value is equal to
all values in a database table(sql 2000).

example

zip code list box
33154
33254
84578
85475
35454

selected value is 85475

I am putting that value in a string like this:

dim string_zip as string
string_zip = zip_ListBox.text

Question, how do i pass that value to sql stament, i am using this but does not work.

SqlCommand1 =New SqlCommand("SELECT zip FROM table WHERE zip =string_zip", SqlConnection1)

You should use this

SqlCommand1 =New SqlCommand("SELECT zip FROM table WHERE zip ='" &string_zip &"'", SqlConnection1)

Regards

|||

Actually, you should use this:

SqlCommand1=new sqlcommand("SELECT zip FROM table WHERE zip=@.zip",SqlConnection1)
SqlCommand1.parameters.add(new sqlparameter("@.zip",sqldbtype.varchar))
SqlCommand1.parameters("@.zip").value=string_zip

Using the string concatenation method is a good way to get yourself hacked.

|||

Motley wrote:

Actually, you should use this:

SqlCommand1=new sqlcommand("SELECT zip FROM table WHERE zip=@.zip",SqlConnection1)
SqlCommand1.parameters.add(new sqlparameter("@.zip",sqldbtype.varchar))
SqlCommand1.parameters("@.zip").value=string_zip

Using the string concatenation method is a good way to get yourself hacked.

Yes, this is preferred over my solution becuase it is more secure.

Thanks Motley

|||

Thanks for you help, it works, but I encounter another problem.

Problem:

multiple selection from list box is allowed, I am getting a string value from all selected choices like this:

Dim listofstringsAsString
Dim itemAs ListItem

ForEach itemIn listbox.Items
If item.SelectedThen
listofstrings = listofstrings & item.Text & ","
EndIf
Next

so i havelistofstrings = (selectedvalue1,selectedvalue2,selectedvalue3,......)

I need to select all values from a table in database where any of those values corresponds.

NOTE: Values in database can also be in the format of (value1,value2,value3,.....) or just a single (value1,)

|||

Search for messages on the UDF named "Split" one was posted recently.

SELECT *
FROM table
WHERE field IN (SELECT * FROM Split(@.listofstrings))

OR
SELECT *
FROM table
JOIN Split(@.listofstrings) s ON (table.field=s.id)

Globals!ReportName behaves differently when SSRS integrated with M

I have a report where I use Globals!ReportName in the header of the report
for the report title. In Development and on SSRS stand alone the value for
Globals!ReportName is in mixed case and the file extension is omitted. When
the report is published to a MOSS server integrated with SSRS the value for
Globals!ReportName is all in lower case and the file extension is included.
Is there any reason for this change in behavior and is there a way I can put
back the mixed case and omit the file extension?I just noticed this is also affecting the root level of the Document Map with
one exception. The case is all in lower case but the file extension is
omitted when rendered on MOSS. It works fine on SSRS.
"Ben Holcombe" wrote:
> I have a report where I use Globals!ReportName in the header of the report
> for the report title. In Development and on SSRS stand alone the value for
> Globals!ReportName is in mixed case and the file extension is omitted. When
> the report is published to a MOSS server integrated with SSRS the value for
> Globals!ReportName is all in lower case and the file extension is included.
> Is there any reason for this change in behavior and is there a way I can put
> back the mixed case and omit the file extension?

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global Variable question

Anyone out there know how to bring in the global variable value for where the
report was run from, whether it's the core report or a linked report? There
already is a global variable for Report Folder but that displays where the
core object sits so if you have a linked report sitting in a different
folder, you get misleading data.
It would be nice to be able to display the actual folder from where the
report was run.
Any ideas out there? Any help appreciated.
Thanks
rjsehOne thing you might try is to create a parameter, and have its data set query
the reporting server database to follow the link back to its parent...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rjseh2001" wrote:
> Anyone out there know how to bring in the global variable value for where the
> report was run from, whether it's the core report or a linked report? There
> already is a global variable for Report Folder but that displays where the
> core object sits so if you have a linked report sitting in a different
> folder, you get misleading data.
> It would be nice to be able to display the actual folder from where the
> report was run.
> Any ideas out there? Any help appreciated.
> Thanks
> rjseh

Global Variable in SQL Server

Hi,

I have a question on SQL Server.

How do I have a value that passing from application to Stored Proc. Then the variable will pass from stored proc to the trigger without storing into any table.

I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work. Anyone know any alternative?

Please help.

Thanks

If those passed parameters are already inserted/updated on your table then you can access those values from the trigger using INSERTED table. (Note Inserted table only accessable from the Trigger Scope & it will have the same table structure as the main table).

Inside Your Trigger:

Code Snippet

Declare @.SomeValue as Varchar(100);

Select @.SomeValue = SomeColumn From Inserted;

It is not good idea to use Gloabal Variable / Temp Table on triggers. You can't say the values always inserted from your SP.

|||

No. the passed parameter I do not want to stored in the table due to some reason. How can I pass the value from stored proc to triggers without storing into table?

|||

You cannot

pass the value from stored proc to triggers

without storing the data in the table.

All input data for the TRIGGER MUST exist within the TABLE that the TRIGGER fires on.

Of course, if you do not wish that the data be kept in the database for concern about security/visibility, you could have the TRIGGER set the field to NULL -thereby obliterating the data that was initially input.

|||Can we do something on tempdb? I mean can I stored the value into tempdb in stored proc then retrieve it from tempdb in triggers?|||

According to you,

I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work.

Did it work?

|||

The following example may help you...

Code Snippet

Create Table ThisIsIt (
[Id] int,
[Value] varchar(100))

go

Create Table ThisIsLog (
[Users] varchar(100),
[When] datetime,
[Id] int,
[Operation] int,
[ValuesAffected] Varchar(8000))

go

Create Trigger trg_ThisIsIt_logger on ThisIsIt For Insert
as
Begin
Declare @.ValuesAffected as Varchar(8000);
Declare @.User as varchar(100);
Declare @.Id as Int;

Select @.ValuesAffected = '"?1";"?2"'
Select @.Id = Id, @.ValuesAffected = Replace(Replace(@.ValuesAffected,'?1', Id),'?2',Value)
From Inserted
If Exists(Select ID From tempdb..Sysobjects Where id = Object_id('tempdb..#Info'))
Select @.User = [User] from #Info;

Insert Into ThisIsLog Values
(@.User, getdate(), @.Id, 1, @.ValuesAffected)
End

go

Create proc InsertThisIsIt
(
@.Id int,
@.Value varchar(100),
@.User varchar(100)
)
as
Begin
Create table #Info
(
[User] varchar(100)
)

Insert Into #Info Values(@.User);

Insert Into ThisIsIt Values(@.Id, @.Value);
End

Global Variable

Hi All,

I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an error

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

I've seen some examples online to get global varaibles in task script and all of them display the same code

Any idea

Franck

To access variable this way it has to be included in the list of ReadOnlyVariables or ReadWriteVariables on your script task properties SCRIPT properties section. Remember also that variables names are case sensitive.

Global Variable

Dear All!
I want to know that how can i declare a global variable in database, assign some value to it, then using it in multiple triggers and procedure then deallocating that.
Please provide a smal example.
Regards,
Shabber.Create a User-Defined Function that returns the value?|||And deallocate it?

What the heck are you doing?

UDF is the only way to simulate a global variable, but if you then wipe out your UDF it will break your sprocs.|||I'd assume Shabber has no big need for deallocating. It's probably just that if there would have been such a thing as a global variable, then it would have been a good habit to deallocate once it wasn't needed anymore.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.|||You call the function, which will return the value you're after.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.

CREATE TABLE MyParameter (
SiteID int IDENTITY (1,1) NOT NULL,
SiteName varchar(255) NOT NULL
)

CREATE FUNCTION udfMyFunction
(@.p1 int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.sTemp varchar(255)

SELECT @.sTemp = SiteName FROM MyParameter WHERE SiteID = @.p1
RETURN @.sTemp
END

Example Data and Usage:

insert into MyParameter(SiteName) Values ('Foo')
insert into MyParameter(SiteName) Values ('Bar')

SELECT dbo.udfMyFunction (2)

I don't know that this example is all that useful, but maybe it will give you some ideas.

Regards,

hmscott

Global Property in SQL?

Here's one for you all...
[SQL Server 2000]
I need to somehow create a place (in memory?) that will store a value.
This value needs to be visible to all sessions
BUT - it needs to die if and when the session that created it dies.
Because a session can die unexpectedly, I cannot rely on removing this "glob
al property" programmatically.
I cannot use a standard table, because if the session dies the value will re
main...
The closest I can come to is to create a temp object in tempdb and name that
object the value of the "global property".
This way others could query tempdb..sysobjects and get the value -- and the
object would die when the session does.
But I would hate to implement a hack like that.
There has got to be a better way!
BTW I have to do this in TSQL but if there an extended sp out ther that can
help me...?
Haven't found anything in BOLSound just like what a global ##temptable does...?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:O%23CrMPziGHA.412@.TK2MSFTNGP05.phx.gbl...
Here's one for you all...
[SQL Server 2000]
I need to somehow create a place (in memory?) that will store a value.
This value needs to be visible to all sessions
BUT - it needs to die if and when the session that created it dies.
Because a session can die unexpectedly, I cannot rely on removing this "glob
al property"
programmatically.
I cannot use a standard table, because if the session dies the value will re
main...
The closest I can come to is to create a temp object in tempdb and name that
object the value of the
"global property".
This way others could query tempdb..sysobjects and get the value -- and the
object would die when
the session does.
But I would hate to implement a hack like that.
There has got to be a better way!
BTW I have to do this in TSQL but if there an extended sp out ther that can
help me...?
Haven't found anything in BOL|||See if this helps:
http://groups.google.com/group/micr...br />
b19b10bd
Anith|||Thanks Anith,
Exactly what I was looking for.
Robert
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message news:uUVZSlziGHA.1204@.TK2MSFTNGP0
2.phx.gbl...
> See if this helps:
> http://groups.google.com/group/micr... />
08b19b10bd
> --
> Anith
>

GLobal Lock Timeout

Is there any way to set a SQL instance to a default lock timeout value? From my understanding, this can be set per session but not globally. I think that any thread waiting longer than say 5 minutes for a lock should fail and would like this make the default in some of our servers.Are you talking about Query Timeout setting on the server?|||Originally posted by rdjabarov
Are you talking about Query Timeout setting on the server?

Maybe. Is the Query Timeout check box in the server Connection tab the default timeout value for locks?|||The check box in the server properties is for Remote queries which is intended to account for network latency and other delays. This is set to 600 (the default i think). However, this does not effect the lock timeout limit.

Global expression apply to all fields in report

Hi All,

I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.

Any helps are appreciated.

Cheers

Nick

Moved to SSRS forum....(from SSIS)|||

Hi Nick,

You can declare a public variable in the Custom code section of the SSRS reports. The syntax for this variable declaration should be VB. Once declared, it can be used in any part of the report by referring to the field as Code."Variable Name". eg. Code.IntCounter.

Regards,

Pradeep

|||

Yeah, i did this way, and it works.. Thanks mate

But i have a little question, it always hightlight the global variable name and indicate it is not declared object in expression area. even through it still works.

Cheers

Nick

Global expression apply to all fields in report

Hi All,

I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.

Any helps are appreciated.

Cheers

Nick

Moved to SSRS forum....(from SSIS)|||

Hi Nick,

You can declare a public variable in the Custom code section of the SSRS reports. The syntax for this variable declaration should be VB. Once declared, it can be used in any part of the report by referring to the field as Code."Variable Name". eg. Code.IntCounter.

Regards,

Pradeep

|||

Yeah, i did this way, and it works.. Thanks mate

But i have a little question, it always hightlight the global variable name and indicate it is not declared object in expression area. even through it still works.

Cheers

Nick

Global Auto Increment Value

Hi ,

In Sybase, there is concept of global autoincrement

The range of default values for a particular database is pn + 1 to p(n + 1), where p is the partition size and n is the value of the public option GLOBAL_DATABASE_ID. For example, if the partition size is 1000 and GLOBAL_DATABASE_ID is set to 3, then the range is from 3001 to 4000

If synchornization is done betrween 2 database . then in server , the new record will be created
The value of increment value would be max value at server +1

For Example
Database id of database(1) is 1

Table A has 3 rows
The values are 1000000001
1000000002
1000000003

This database id of this database(2) is 2
The records in table A in this database 2000000001
2000000002

Then synchronization is performed.
The records from database(2) comes to Database(1)

So the records in Database(1) are

1000000001
1000000002
1000000003
2000000001
2000000002

In sybase , when we insert a new record in Database(1), the new record will be 1000000004

I have transferred this type of sybase database(1) to Sql Server using DTS.

When I create a new record on Sql Server , the new record which is getting inserted is 2000000003. I want the new record to be inserted is 1000000004.

How is it possible..

Thanks in advance

MS SQL does not have any way to do this built in. IDENTITY columns are always unique within the table, not the database.

I have simulated what you are talking about by creating a seperate table with just one field of the global increment number and using that.|||

Create a DatabaseID column in each table with the tinyint data type and create a default constraint for it with the database number for the corresponding database. Create an identity column. Make the primary key a compound of the DatabaseID and the identity column.

SQL Server's approach to uniqueness across replicated databases is to use the uniqueidentifier data type with a default value based on calling newid(). If you care about the database that owns the row you'll have to have some kind of DatabaseID column then too though.

|||

There is no built-in functionality available to recycle old identity values or gaps. You will have to do that yourself. The link below has a solution that can be used to find gaps in sequential numbers and you can use that to get the next id:

http://www.umachandar.com/technical/SQL6x70Scripts/Main67.htm

Note that you will have to perform the appropriate locking depending on your concurrency requirements to get the correct results.

Giving Value for Identity column while inserting

I have a table with an Identity column. The latest Identity value is 1298. A row with Identity 324 was deleted by mistake and I need to insert it now. But if I insert it now it will have Identity value of 1299. I tried giving the value 324 for the Identity column in the Insert statement but it throws error. How can I insert this row with the same Identity value as before??

Quote:

Originally Posted by sajithamol

I have a table with an Identity column. The latest Identity value is 1298. A row with Identity 324 was deleted by mistake and I need to insert it now. But if I insert it now it will have Identity value of 1299. I tried giving the value 324 for the Identity column in the Insert statement but it throws error. How can I insert this row with the same Identity value as before??

Are you not able to insert using a plain insert statement on query analyser?|||You can do this by
SET IDENTITY_INSERT [table_name] ON
and then turn it off

Giving a total on a column value change

I have the following data in a table
date StartTm ElapseTm shift Job# Op#
WorkCtr Qty1 Qty2
20051218 0030 0030 13 165990 225 9 .0000 0
20051218 0100 0030 13 165990 223 9 .0000 0
20051218 0130 0145 13 165990 200 9
26219.0000 28220
20051218 0315 0030 13 165990 300 9 .0000 0
20051218 0345 0115 13 165988 100 9 .0000 0
20051218 0500 0030 13 165900 300 9 .0000 0
20051218 0530 0130 13 165975 100 9 .0000 0
20051218 2300 0100 13 166436 300 9 .0000 0
20051218 2400 0030 13 165990 100 9 .0000 0
I need to know how to calculate the quantities based on totals of
successive jobs.
For example the first total for job 165990 would be 26219
the next total for job 165990 would be 0 for Qty1
the first total for job 165988 would be 0 for Qty1
so aggregated I would want to see
165988 0
165990 26219
165990 0
I need to be able to apply a identifier to these groups of successive
jobs by date and by WorkCtr.
I group the values by work_ctr, day, start_tm, job_no
Within that grouping when the job changes give a total.
I hope I am being clear. Thanks for your time in advance.I'm .
Why is the row with 26219 the first total for job 165990? It seems that
based on start time this row is the 3rd of 4 for this job. What is the
criteria you want to use for ordering the rows for a specific job.
When you say aggregated what do you mean? Do you just mean ordered? Or are
you intending to combine the rows somehow.
Also, I am by your last set of statements. what do you mean by
apply an identifier to the groups. And what does it mean when you say when
the job changes give a total?
If you can give the full version of your desired output based on the data
that you provide and answer by above confusion it will make it easier for us
.
Thanks
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"g3000" wrote:

> I have the following data in a table
> date StartTm ElapseTm shift Job# Op#
> WorkCtr Qty1 Qty2
> 20051218 0030 0030 13 165990 225 9 .0000 0
> 20051218 0100 0030 13 165990 223 9 .0000 0
> 20051218 0130 0145 13 165990 200 9
> 26219.0000 28220
> 20051218 0315 0030 13 165990 300 9 .0000 0
> 20051218 0345 0115 13 165988 100 9 .0000 0
> 20051218 0500 0030 13 165900 300 9 .0000 0
> 20051218 0530 0130 13 165975 100 9 .0000 0
> 20051218 2300 0100 13 166436 300 9 .0000 0
> 20051218 2400 0030 13 165990 100 9 .0000 0
> I need to know how to calculate the quantities based on totals of
> successive jobs.
> For example the first total for job 165990 would be 26219
> the next total for job 165990 would be 0 for Qty1
> the first total for job 165988 would be 0 for Qty1
> so aggregated I would want to see
> 165988 0
> 165990 26219
> 165990 0
> I need to be able to apply a identifier to these groups of successive
> jobs by date and by WorkCtr.
> I group the values by work_ctr, day, start_tm, job_no
> Within that grouping when the job changes give a total.
> I hope I am being clear. Thanks for your time in advance.
>|||Sorry, I actually solved the problem my issue was this
Date Job# Qty
20051201 165203 20
-- break here and total is 20 unique id
20051201 165202 15
-- break here and total is 20 unique
id
20051201 165203 10
20051201 165203 10
20051201 165203 10
20051202 165203 10
-- break here and total is
40. The above four records shoud have a unique group id
20051202 165222 05
blah blah blah
bottom line I have jobs that run and stop then start again
if the same job is started, stopped and restarted in succession that is
a unique group
if the next job is not the same as the first i needed a new unique
identifier and a total
I wanted to get totals for these groups but had no way to group those
by
just did a cursor to a table and looped through and had new colun in my
table
clear as mud?|||It's actually quite simple to solve if you don't let
the complexities of sql cloud the issue.Every block
gets an ascending unique integer.Then you can group
by this new integer.The convoluted sql comes in when
trying to derive a new integer for a new block.It's a rank
problem at most.
Ranking was implemented years ago in the RAC utility:)
Perhaps someone can wrap their head around sql 2005
ranking functions and solve it,if not I can post RAC solution:)
Check out RAC @.
www.rac4sql.net|||On 12 Jan 2006 12:40:25 -0800, g3000 wrote:

>Sorry, I actually solved the problem my issue was this
>Date Job# Qty
>20051201 165203 20
> -- break here and total is 20 unique id
>20051201 165202 15
> -- break here and total is 20 unique
>id
>20051201 165203 10
>20051201 165203 10
>20051201 165203 10
>20051202 165203 10
> -- break here and total is
>40. The above four records shoud have a unique group id
>20051202 165222 05
> blah blah blah
>bottom line I have jobs that run and stop then start again
>if the same job is started, stopped and restarted in succession that is
>a unique group
>if the next job is not the same as the first i needed a new unique
>identifier and a total
>I wanted to get totals for these groups but had no way to group those
>by
>just did a cursor to a table and looped through and had new colun in my
>table
>clear as mud?
Hi g3000,
You don't need a cursor for this. The following single query should
produce the same results. You might wish to compare this with your
current solution and retain the version that performs best or is easiest
to maintain (dfepending on what ranks top priority in your DB).
SELECT a.Job#, SUM(a.Qty)
FROM YourTable AS a
GROUP BY a.Job#, (SELECT COUNT(DISTINCT b.Job#)
FROM YourTable AS b
WHERE b."Date" <= a."Date")
Hugo Kornelis, SQL Server MVP|||thank you Hugo,
I will try that. Didnt think about that. Looks like what I
want. I am not familiar with ranking. So ill have to look into that
later.