Showing posts with label time. Show all posts
Showing posts with label time. Show all posts

Sunday, March 25, 2012

Calling SQL 2005 stored procedure that returns a value

I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table.

I am calling it from an ASP page. Initially I just called it and didn't worry about the return value. However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).

I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value.

I am calling the SP as follows:

Shared Sub createlogentry(ByVal ID, ByVal tme, ByVal val)
Dim result As String
Dim cs As String = ConfigurationManager.ConnectionStrings("connecttion1").ToString
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand()
cmd.Connection = con
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "CreateLogEntry"
cmd.Parameters.Add("@.ChID", Data.SqlDbType.Int)
cmd.Parameters("@.ChID").Value = ID
cmd.Parameters.Add("@.Value", Data.SqlDbType.Int)
cmd.Parameters("@.Value").Value = val
result = cmd.ExecuteNonQuery().ToString
End Using
End Sub

I have tried amending the ExecuteNonQuery line to ExecuteReader()

Any help appreciated

Regards

Clive

Greetings Clive

I would have made the stored procedure use an output parameter as more outputs can always be added. A return can only return one value!

So as output you would add the lines:

cmd.Parameters.Add("Fred"), Data.SqlDbType.Int)

cmd.Parameters("Fred").Direction = Output;

after the result add

Dim iResult As Integer = md.Parameters("Fred").Value

|||

Thanks for that - you are right the ability to return more than one value would be useful.

At the moment in my T-SQL I use the RETURN construct to generate the return value. How would I change that to use an output parameter instead?

regards

Clive

|||

As in

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clive Chinery
-- Create date: 13Aug2007
-- Description: Test for records for today and tomorrow
-- =============================================
CREATE PROCEDURE dbo.DoDateTest
@.Test1 BIT OUTPUT,
@.Test2 BIT OUTPUT
AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tblToday WHERE Date = GetDate()) SET @.Test1 = 1 ELSE SET @.Test1 = 0
IF EXISTS(SELECT * FROM tblToday WHERE Date = DateAdd(day, 1, GetDate()))
SET @.Test2 = 1 ELSE SET @.Test2 = 0
------- This is the End ----
GO

This returns two output values. I normally put the output parameters at the end of the declaration.

Regards

Clive Chinery

Thursday, March 22, 2012

Calling nested stored proedure many times

There are two stored procedures. One calls the other one time. It works.
create procedure nestedSP
@.p float
as
declare @.q float
set @.q = @.p
select @.q
go
create procedure CallingNestedSP
@.p float
as
exec nestedSP @.p
go
exec CallingNestedSP 111.11
How do I get the resultset as 111.11, 111.11 by calling the nexted twice.
Please advise. Thanks
PeterHi Peter
There is no best way of joining the two results from SPs ...
You can maybe try to create the called procedure as a UDF and
call it twice.Or if you only have to use a SP,
then insert the result into a temp table. Your calling SP will have the code
like this..
create procedure CallingNestedSP
@.p float
as
begin
create table #temp (a decimal(...))
INsert into #temp
exec nestedSP @.p
select a,a from #temp
end..
Though I would prefer a udf for a situation similar to yours.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

Monday, March 19, 2012

Calling a Web Service with data from a SQL Server table one row at a time

Hello

I am trying to call a Web Servie within a SSIS package. I want to pull a row at a time from a SQL Server 2005 table and pass this data values onto the web service.

Any help is highly appreciated!

Kind Regards

Gayatri

I guess it rather depends on what you want to do with the Web Service. Can you give us more details?

You can certainly call a web service row-by-row from a script component in the data flow. This may work for you, but it rather depends on what you want to do once you have called the service.

Donald

|||

Hi Donald

Many thanks for your reply. Web Service is written to accept fields of the records from the table I am reading. I've gone as far as accessing record fields in a script task whlie the records are being read. Where I am lost is assigning those field values to the web service input fields.

My Web service interface is as follows:

Service: PersonWebserviceService

Method: CreatePersonRecord

CreatePersonRecord Method accepts as input a structure to hold fields data values as follows:

(On the input tab)

Name: Key

Type: LegacysystemData

Values: each value in turn have a Name, Type and Value components (eg: Name: ClientNo Type: String and Value). Value is the field I need to access in the script task to assign the record field value I read in)

Your help is much appreciated.

Gayatri

|||

Hi Donald

During the course of the day I managed to Create a web service proxy for my web service and reference it in the script task. But when I run the SSIS Package I get the following error:

Could not load file or assembly 'PersonWebserviceService, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

Hope you could shed some light

many thanks


Gayatri

Saturday, February 25, 2012

Call a Stored Procedure within an SQL statement

Hi All,
I created a stored procedure which updates a specific row whose row ID is passed.
But what I want to do now, (which is a one time requirement) is to update all rows
is it possible to call this stored procedure within an sql statement and pass each row ID to it?

(would i have to go to cursor approach -- damn will have to learn that )
thanks

Quote:

Originally Posted by Shashi Sadasivan

Hi All,
I created a stored procedure which updates a specific row whose row ID is passed.
But what I want to do now, (which is a one time requirement) is to update all rows
is it possible to call this stored procedure within an sql statement and pass each row ID to it?

(would i have to go to cursor approach -- damn will have to learn that )
thanks


You can call a function from a SQL statement and not a STORED PROCEDURE.

Restrictions on calling a Function from a SQL statement:

1. Function should not have RETURN TYPE as BOOLEAN
2. Function should not perform any side effects. (ie No DML operations)|||

Quote:

Originally Posted by Shashi Sadasivan

Hi All,
I created a stored procedure which updates a specific row whose row ID is passed.
But what I want to do now, (which is a one time requirement) is to update all rows
is it possible to call this stored procedure within an sql statement and pass each row ID to it?

(would i have to go to cursor approach -- damn will have to learn that )
thanks


if it's a one time thing, can you just write a query in query analyzer to update the field you need? what field are you updating? what kind of update?

Friday, February 24, 2012

Calendar Control issues

Hi all,

When I click on a calendar control for date parameters the page will freeze or throw a java script error. This does not happen all the time, and is not isolated to one PC. It happens randomly on several PCs. Is there any known issue with this control?

regards,

Bill

The only issue that I have seen is if you try to open the calendar control while it is still loading the iframe for the calendar. Next time it happens see if the IE status bar is still showing the page loading.|||It is in the process of loading. There are times though, when it takes 10 seconds or more to load locally, and much longer if a user is accessing the page outside of the local LAN. If the user clicks on the control during the load, a java error occurs and the page is disabled. This cant be by design , could it? :-)|||

No it is not by design. :) I am surprised that it does take so long to load. Is this happening the first time you view the report or when navigating through calendar months? If it is the first time, the calendar is one of the last things loaded on the page, if the entire page is still loading (IE will render objects and allow interaction even though the page hasn't finished loading) the calander may not be loaded until the page is loaded. Are you viewing the reports through Report Manager or your own pages? If they are your own pages, perhaps there is some improvement that can be made there to speed up the page load.

As far as anything you can do to Report Server, there isn't anything that can currently be done to affect this behavior. We are aware of the issue and are looking to fix it in a future version. In the meantime users need to be aware of the issue and wait to open the drop down until the page has fully loaded.

|||Thank you for your answer. The page is displayed through report services, not my own page. I hope you have a fix soon, as it is making report services for my company very difficult to use. On a side note, report services displayed in IE7 with a calendar control will not work at all.|||

There have been quite a few reports of problems with the calendar control (just search the forums) and the basic answer seems to be "we cant replicate any problem" so I think MS dont know what to fix.

Certainly I have the same issue as you, the calendar controls throwing script errors and taking ages to load.

|||I am just hoping that some sort of hot fix is available soon. Like I said though, it does not work at all with IE7 so if MS is planning to release IE7 as a critical update like it is rumored, it will break all RS sites.|||IE7 is only in beta 3 mode, I believe the issues with IE7 and RS will be resolved but there is no fix for IE7 Beta 3.|||So I guess the answer is "deal with it" At least most companies dont use parameters in reporting , so it should not be a big issue. |||Just to clarify, we are aware of the issue with the script errors and how to fix it. In order to be fixed in a QFE, a customer will need to contact CSS and request the fix, otherwise look for the fix in a future release. If your are hindered by this issue, your best course of action is to contact Customer Support and request a fix.|||It appears it is an IE issue. I am able to work with the calendar control fine with Firefox. Can anyone verify this?

Calendar Control issues

Hi all,

When I click on a calendar control for date parameters the page will freeze or throw a java script error. This does not happen all the time, and is not isolated to one PC. It happens randomly on several PCs. Is there any known issue with this control?

regards,

Bill

The only issue that I have seen is if you try to open the calendar control while it is still loading the iframe for the calendar. Next time it happens see if the IE status bar is still showing the page loading.|||It is in the process of loading. There are times though, when it takes 10 seconds or more to load locally, and much longer if a user is accessing the page outside of the local LAN. If the user clicks on the control during the load, a java error occurs and the page is disabled. This cant be by design , could it? :-)|||

No it is not by design. :) I am surprised that it does take so long to load. Is this happening the first time you view the report or when navigating through calendar months? If it is the first time, the calendar is one of the last things loaded on the page, if the entire page is still loading (IE will render objects and allow interaction even though the page hasn't finished loading) the calander may not be loaded until the page is loaded. Are you viewing the reports through Report Manager or your own pages? If they are your own pages, perhaps there is some improvement that can be made there to speed up the page load.

As far as anything you can do to Report Server, there isn't anything that can currently be done to affect this behavior. We are aware of the issue and are looking to fix it in a future version. In the meantime users need to be aware of the issue and wait to open the drop down until the page has fully loaded.

|||Thank you for your answer. The page is displayed through report services, not my own page. I hope you have a fix soon, as it is making report services for my company very difficult to use. On a side note, report services displayed in IE7 with a calendar control will not work at all.|||

There have been quite a few reports of problems with the calendar control (just search the forums) and the basic answer seems to be "we cant replicate any problem" so I think MS dont know what to fix.

Certainly I have the same issue as you, the calendar controls throwing script errors and taking ages to load.

|||I am just hoping that some sort of hot fix is available soon. Like I said though, it does not work at all with IE7 so if MS is planning to release IE7 as a critical update like it is rumored, it will break all RS sites.|||IE7 is only in beta 3 mode, I believe the issues with IE7 and RS will be resolved but there is no fix for IE7 Beta 3.|||So I guess the answer is "deal with it" At least most companies dont use parameters in reporting , so it should not be a big issue. |||Just to clarify, we are aware of the issue with the script errors and how to fix it. In order to be fixed in a QFE, a customer will need to contact CSS and request the fix, otherwise look for the fix in a future release. If your are hindered by this issue, your best course of action is to contact Customer Support and request a fix.|||It appears it is an IE issue. I am able to work with the calendar control fine with Firefox. Can anyone verify this?

Calendar Control

We use the RS 2005 Calendar control for date/time parameters on our
reporting. Unfortunatley we have been running into a performance problem
becuase the Calendar control does a round trip to the server once a date is
chosen. On many of our desktops, this round trip is very evident.
Is there any way to provent the round trip to the server when a date is
chosen?
Thanks,
EricNo way except you need to write a custom code.
Amarnath
"echeeze" wrote:
> We use the RS 2005 Calendar control for date/time parameters on our
> reporting. Unfortunatley we have been running into a performance problem
> becuase the Calendar control does a round trip to the server once a date is
> chosen. On many of our desktops, this round trip is very evident.
> Is there any way to provent the round trip to the server when a date is
> chosen?
> Thanks,
> Eric

calculations in a self-join (was "Query")

Hi all,
If I have a table like the following: (Table Name is A)
ID Date Time DeviceNumber Sum
1 1/12/2004 06:00:00 1 200
2 1/12/2004 08:00:00 1 600
3 1/12/2004 06:00:00 2 300
4 1/12/2004 08:00:00 2 800

If I write the following SQL statment:
"Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.ID=Six.ID
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");

How SQL interpret this statment. I make the same table innered joined be ID field.
By purpose is to calculate the Difference between the Sum of the different hourse of the two devices.
I mean,
'06:00:00 - 08:00:00' 1 400 --> (600-200)
'06:00:00 - 08:00:00' 2 500 --> (800-300)

Please help me ti under stand and solce the problem.Looks look you shoudl be joining on DeviceNumber and not on ID.|||Hi,
I don't understand right now, How SQL pull out data according to the where criteria.
This will help me to design the query.
Here I thought to make two copies of the same table, and substract the Sum of 06:00:00 o'clock from 08:00: o'clock.

Can you help me to correct the statment.
thanks...|||What I meant was:

Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.DeviceNumber=Six.DeviceNumber
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");

Calculations at Run Time

Hi,
I am writting a report & want to calculate %age difference in last year &
current year's sales.
how do I write the expression or will have to write it in the SP I'm using
to run the report.
The problem is, I need to check the previous years sales value only if it is
greater than 0, then calculate the %age difference so it is something like
this
if prevYearSales > 0 then
currYearSales - prevYearSales / prevYearSales.
ThanksYou do it in an SP or as the following report expression
=IIF(Fields!prevYearSales.Value > 0,
Fields!currYearSales.Value - FIelds!prevYearSales.Value /
Fields!prevYearSales.Value,0)
It sets other values to 0 (the last expression)
--
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.
"Vishal" wrote:
> Hi,
> I am writting a report & want to calculate %age difference in last year &
> current year's sales.
> how do I write the expression or will have to write it in the SP I'm using
> to run the report.
> The problem is, I need to check the previous years sales value only if it is
> greater than 0, then calculate the %age difference so it is something like
> this
> if prevYearSales > 0 then
> currYearSales - prevYearSales / prevYearSales.
> Thanks
>
>

Sunday, February 19, 2012

calculation values

Hi all,
I had posted a problem,and I didn't get the obvious solution, can you help me please:

My table looks like the following:

Date\Time Counter1 Counter2

The counters are taken by an automatic system in three shifts:
First in : 6:00 AM Second in: 15:00 PM Third in : 11:00 PM

I want a query to calculate the difference of the counters between the three shifts (Let's begin with this).

For example: The following table
22/11/04 11:00 PM 0 0
23/11/04 6:00 AM 10 20
23/11/04 15:00 PM 20 30
23/11/04 23:00 PM 30 40

The output is:
23/11/04 6:00 AM 10 20
23/11/04 15:00 PM 10 10
23/11/04 11:00 PM 10 10

I'm new I the SQL world. can you please help me and give me sum information to solve the problem.the answer is here -- http://www.dbforums.com/t1063139.html|||But, the answer isn't sufficient to my problem.
thanks...|||then you will have to ask your question a bit more clearly

maybe we do not understand the problem|||I'll explain secondly,
my really table is the following:
1. CollectDate (Date/Time): The date and time of collecting data.
2. MachineNum (Number): The machine number of the data. I have 40...50 machines.
3. WorkOrder (Number): A number witch discribe the part of the machine.
4. TotalGood (Number): An accumulator of the current parts that the machine did.

The total task of the query is to calculate how many parts did the machine do in a certain date but by shifts.
E.g:
the final date collection was:
CollectDate MachineNum WorkOrder TotalGood
25/11/04 23:00:00 40 00111 100
26/11/04 6:00:00 40 00111 200
26/11/04 15:00:00 40 00111 500
26/11/04 23:00:00 40 00111 1000

The query output is:
Shifts(26/11/04) MachineNum WorkOrder TotalGood
6:00:00 40 0011 100 (200-100)
15:00:00 40 0011 300 (500-200)
23:00:00 40 0011 500 (1000-500)

Please help me.
Best regards...

calculation query

Hi all,
I need your help of building the following query.
I have the table "CountersTbl".
The table's fields are.
Date\Time Counter1 Counter2
The tables holds counters in different dates ant time.e.g:
19/11/04 06:00 am 10 20
19/11/04 15:00 pm 15 25
19/11/04 11:00 pm 35 90
...

In the above table we have the counters in three shifts in one day. Each day I hae the same shifts readings.

My task is:
I want to build a query that calculate the difference between the shifts counters in a givven day.
So, the output of the query of the 19/11/04 day is:

From 06:00 am - 15:00 pm 5 5
From 15:00 pm - 11:00 pm 20 65

I you please help me to build the code of this query.
Best regards...select date(three.datetimecol) as ShiftDate
, 'From 06:00 - 15:00' as Shift
, three.Counter1
-six.Counter1 as Counter1Diff
, three.Counter2
-six.Counter2 as Counter2Diff
from CountersTbl as three
inner
join CountersTbl as six
on date(three.datetimecol)
= date(six.datetimecol)
where time(three.datetimecol) = '15:00'
and time(six.datetimecol) = '06:00'
union all
select date(eleven.datetimecol) as ShiftDate
, 'From 15:00 - 23:00' as Shift
, eleven.Counter1
-three.Counter1 as Counter1Diff
, eleven.Counter2
-three.Counter2 as Counter2Diff
from CountersTbl as eleven
inner
join CountersTbl as three
on date(eleven.datetimecol)
= date(three.datetimecol)
where time(eleven.datetimecol) = '23:00'
and date(three.datetimecol) = '15:00'
order
by ShiftDate
, Shifthere datetimecol is the name of your "Date\Time" column

also, please note, date and time represent whatever functions are available in your particular database system for extracting the date only and time only portions of the datetime values

i was going to write them using the standard sql EXTRACT function but the "standard sql" book which i own is pretty crappy and does not give enough decent examples for me to know how to extract dates and times

and anyway, most common database systems don't support EXTRACT, they have their own proprietary date functions

calculation problem

Hello,

We have time dimension (month, week)

and fact table, total_revenue, no_of_work_day, order, order type....

We added a calculated member : Ave revenue/work_day
Expression: "[Measures].[Tot Revenue]/[Measures].[No Of Work Day]"
in BI studio, but result is wrong.

Something wrong with 'no_of_work_day' , but how to correct it?

Thank you very much for any help.

In spite of using calculated member inside cube, try to create a named calculation inside datasourceview!?

In the table of datasourceview, right-click and add a named calculation a insert your formula TotRevenue/NoOfWorkDay...

Regards!

|||

Can you show us your mdx query?

You try using the SUM(...) function on the measures members and dividing with it...

I would also like to say that you can create your calculated member in the cube...


Regards,

|||Thank you very much PedroCGD and Luis for the advice,

I tried using named calculation, it works. Great!

What is different between named calculation and calculate command inside cube?

I really appreciated your help.
|||

Dear Olap_user,

Always try to use named calculations... only when you need to calculate inside aggregations you use calculated member. The big advantage of using named calculation is that the system spend time to do calculates only once, and with calculated member each time you query the cube. So:

Named Calculation > each time your process cube

Calculated member > each time you query the cube!

Helped you?

Regards!!!

|||Thank you very much for the detail explanation, using Named Calculation is better for the performance.

Your explanation is very helpful.

Thanks a lot.

Thursday, February 16, 2012

Calculating time elapsed (weekdays only)

What is the best way to calculate time elapssed between two dates and factor
in wdays only (exclude Saturday and Sunday).
Example:
Friday Jan 28th at 10pm thru Monday Jan 31st at 2am should calculate to 4
hours.
Friday Jan 21st at 10pm thru Monday Jan 31st at 2am should calculate to 124
hours
Thanks in advance,
MarkOn Mon, 31 Jan 2005 15:56:42 -0500, Mark Andrews wrote:

>What is the best way to calculate time elapssed between two dates and facto
r
>in wdays only (exclude Saturday and Sunday).
>Example:
>Friday Jan 28th at 10pm thru Monday Jan 31st at 2am should calculate to 4
>hours.
>Friday Jan 21st at 10pm thru Monday Jan 31st at 2am should calculate to 124
>hours
>Thanks in advance,
>Mark
>
Hi Mark,
Use DATEDIFF to find the total number of hours between the two dates.
Use a calendar table to find the number of non-workdays between the two
dates, multiply by 24 and subtract.
Why should I consider using an auxiliary calendar table? www.aspfaq.2519
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||See if this helps:
How do I count the number of business days between two dates?
http://www.aspfaq.com/show.asp?id=2453
Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
AMB
"Mark Andrews" wrote:

> What is the best way to calculate time elapssed between two dates and fact
or
> in wdays only (exclude Saturday and Sunday).
> Example:
> Friday Jan 28th at 10pm thru Monday Jan 31st at 2am should calculate to 4
> hours.
> Friday Jan 21st at 10pm thru Monday Jan 31st at 2am should calculate to 12
4
> hours
> Thanks in advance,
> Mark
>
>

Calculating time difference between two records....


I have a data set like so:

UTC_TIME Timestamp NodeID Message Flag
Line
Station
11/19/2005 10:45:07 1132397107.91 1 3 5 1028
1034
11/3/2005 21:05:35 1131051935.20 2 3 5 1009
1043
11/25/2005 21:12:16 1132953136.59 3 3 5 1037
1049

I added the UTC_TIME column in as aconversion of the unix timestamp in

the TIMESTAMP column.

Keeping things simple and straightforward, I need to be able to
calculate the difference from one record to the next (ordered by
TIMESTAMP or UTC_TIME) and output the result into another column in the

table.

NODEID is the unique id.

First, what is the function to do so if, say, I only wanted to
calculate the difference between 2 records as just a basic SELECT
statement. That way I can answer quick question based on any one or two

NODEID's.

Second, how would I further that to continually calculate (as stated
above)?

WOuld this be a stored procedure? A trigger? A cursor?

I am learning as I go here. Any help is greatly appreciated.

R.DATEDIFF ( datepart , startdate , enddate ) is the function that will
return the interval between two dates.
Check the help in QA for the datepart arguments.

As far to tell the difference between two rows: this isnt perfect.
I wrote this using the NORTHWIND database, but it I think it is exacly
what you are looking for:
SELECT
ORDERID,
ORDERDATE,
DATEDIFF(d,orderdate, (SELECT TOP 1 ORDERDATE FROM ORDERS WHERE
ORDERID > O.ORDERID ORDER BY ORDERID )) AS DAYS
FROM ORDERS O|||Your narative is vague and does not compile; why did you not post DDL
that everyone could use for testing?

The usual way to model events is to have a (start_time, end_time) pair
in the table because time is a continuum. A NULL end_time means the
event is on-going. The druation of the event is trival at that point.

A row is not a record -- a row has to be a complete fact, while a
record does not.|||actually, the way R. set it up, he is using facts in his rows. At x
time, an event occurred. I think you are ASSUMING start and end times,
and attempting to get him to change his data model to become
non-normalized.

Shame on you.

To answer more of the person's question, you can use the datediff
function against teh current time to find out how long ago an event
occured from whenever you run the report.

doug|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

You don't say what time part (days, months, years, hours, minutes,
seconds) you want returned. My example will show hours.

SELECT NodeID, LineStation, MessageFlag, UTC_Time,
DateDiff(Hour, (SELECT MAX(UTC_Time) FROM table_name
WHERE NodeID=T.NodeID AND LineStation=T.LineStation
AND UTC_Time < T.UTC_Time),
UTC_Time) As HoursInterval

FROM table_name As T
WHERE ... < your criteria > ...
ORDER BY NoteID, LineStation, UTC_Time

The "table_name" in both the main query & the subquery should be the
same.

Your data implies that the LineStation is receiving a message
(MessageFlag) at specified times (UTC_TIME). Therefore, I set up the
query to return info on LineStations on the same NodeID. If you just
want to track messages on the NodeID, no matter the LineStation, then
remove the "AND LineStation=T.LineStation" part of the subquery's
criteria (WHERE clause), and remove the LineStation from the ORDER BY
clause.

See the SQL Server BooksOnLine for more info on DateDiff() function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ7HpM4echKqOuFEgEQJWFgCdEMfPaY7aQPYOL66ZJryJpR 4fTMMAoO0r
WEDMaPHxQiZ352eHx0ER72Ur
=YKsf
--END PGP SIGNATURE--

iamonthisboat@.gmail.com wrote:
>
>
> I have a data set like so:
>
> UTC_TIME Timestamp NodeID Message Flag
> Line
> Station
> 11/19/2005 10:45:07 1132397107.91 1 3 5 1028
> 1034
> 11/3/2005 21:05:35 1131051935.20 2 3 5 1009
> 1043
> 11/25/2005 21:12:16 1132953136.59 3 3 5 1037
> 1049
>
> I added the UTC_TIME column in as aconversion of the unix timestamp in
> the TIMESTAMP column.
>
> Keeping things simple and straightforward, I need to be able to
> calculate the difference from one record to the next (ordered by
> TIMESTAMP or UTC_TIME) and output the result into another column in the
> table.
>
> NODEID is the unique id.
>
> First, what is the function to do so if, say, I only wanted to
> calculate the difference between 2 records as just a basic SELECT
> statement. That way I can answer quick question based on any one or two
> NODEID's.
>
> Second, how would I further that to continually calculate (as stated
> above)?
>
> WOuld this be a stored procedure? A trigger? A cursor?
>
> I am learning as I go here. Any help is greatly appreciated.
>
> R.

Calculating Time Difference

Hi,

I have a table called Bookings which has two important columns;
Booking_Start_Time and Booking_End_Time. These columns are both of type
DATETIME. Given any day how can I calculate how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they have unbooked on a particular day (i.e. 8.5 hours less the time
of any bookings on that day), can this be done with a query
or do I have to work it out in my code?

Thanks for your helpShaun,

Take a look at DateDiff function in books online. Something like this will
help you. This query return the available time in minutes.

select Date,
DateDiff(minute, '09:00','17:30') - Sum(BookTime)
from (select convert(char(10), Booking_Start_Time, 112) as Date,
DateDiff(minute, Booking_Start_Time, Booking_End_Time) as
BookTime
from YourTable
) as Tmp
group by Date

Shervin

"Shaun" <shaun@.mania.plus.com> wrote in message
news:blc5m3$fug$1@.reader-00.news.insnet.cw.net...
> Hi,
> I have a table called Bookings which has two important columns;
> Booking_Start_Time and Booking_End_Time. These columns are both of type
> DATETIME. Given any day how can I calculate how many hours are available
> between the hours of 09.00 and 17.30 so a user can see at a glance how
many
> hours they have unbooked on a particular day (i.e. 8.5 hours less the time
> of any bookings on that day), can this be done with a query
> or do I have to work it out in my code?
> Thanks for your help|||"Shaun" <shaun@.mania.plus.com> wrote in message news:blc5m3$fug$1@.reader-00.news.insnet.cw.net...
> Hi,
> I have a table called Bookings which has two important columns;
> Booking_Start_Time and Booking_End_Time. These columns are both of type
> DATETIME. Given any day how can I calculate how many hours are available
> between the hours of 09.00 and 17.30 so a user can see at a glance how many
> hours they have unbooked on a particular day (i.e. 8.5 hours less the time
> of any bookings on that day), can this be done with a query
> or do I have to work it out in my code?
> Thanks for your help

Here's a solution that allows bookings to overlap, multiple interested
non-overlapping intervals to be specified (you had specified one, 0900
to 1730) and both bookings and interested intervals to span more than
one day.

-- Bookings can overlap
CREATE TABLE Bookings
(
booking_start_time DATETIME NOT NULL,
booking_end_time DATETIME NOT NULL,
CHECK (booking_end_time >= booking_start_time),
PRIMARY KEY (booking_start_time, booking_end_time)
)

-- Sample data
INSERT INTO Bookings (booking_start_time, booking_end_time)
VALUES ('20030930 8:30AM', '20030930 10:30AM')
INSERT INTO Bookings (booking_start_time, booking_end_time)
VALUES ('20030930 10:00 AM', '20030930 12:30PM')
INSERT INTO Bookings (booking_start_time, booking_end_time)
VALUES ('20030930 2:00PM', '20030930 4:30PM')
INSERT INTO Bookings (booking_start_time, booking_end_time)
VALUES ('20030930 9:00PM', '20030930 11:30PM')

-- Interested intervals can't overlap
CREATE TABLE InterestedIntervals
(
interval_start DATETIME NOT NULL,
interval_end DATETIME NOT NULL,
CHECK (interval_end >= interval_start),
PRIMARY KEY (interval_start, interval_end)
)

-- Sample data
-- Note that multiple interested intervals can be specified
INSERT INTO InterestedIntervals (interval_start, interval_end)
VALUES ('20030930 8:00AM', '20030930 1:00PM')
INSERT INTO InterestedIntervals (interval_start, interval_end)
VALUES ('20030930 2:00PM', '20030930 6:00PM')
INSERT INTO InterestedIntervals (interval_start, interval_end)
VALUES ('20030930 8:00PM', '20030930 11:59PM')

CREATE VIEW OrderedBookings (interval_start, interval_end, seq)
AS
SELECT B1.booking_start_time,
B1.booking_end_time,
(SELECT COUNT(*)
FROM Bookings AS B2
WHERE B2.booking_start_time < B1.booking_start_time OR
(B2.booking_start_time = B1.booking_start_time AND
B2.booking_end_time < B1.booking_end_time))
FROM Bookings AS B1

-- A DATETIME value can be from date 17530101 to date 99991231
CREATE VIEW BookingGaps (interval_start, interval_end)
AS
SELECT '17530101' AS interval_start, interval_start AS interval_end
FROM OrderedBookings
WHERE seq = 0 AND interval_start > '17530101'
UNION ALL
SELECT OI1.interval_end AS interval_start,
COALESCE(OI2.interval_start, '99991231') AS interval_end
FROM OrderedBookings AS OI1
LEFT OUTER JOIN
OrderedBookings AS OI2
ON OI2.seq = OI1.seq + 1
WHERE OI2.interval_start > OI1.interval_end OR
OI2.seq IS NULL

CREATE VIEW InterestedBookingGaps (interval_start, interval_end)
AS
SELECT CASE WHEN G.interval_start BETWEEN
I.interval_start AND I.interval_end
THEN G.interval_start
ELSE I.interval_start
END,
CASE WHEN G.interval_end BETWEEN
I.interval_start AND I.interval_end
THEN G.interval_end
ELSE I.interval_end
END
FROM BookingGaps AS G
INNER JOIN
InterestedIntervals AS I
ON NOT(I.interval_start >= G.interval_end OR
I.interval_end <= G.interval_start)

-- Booking gaps with respect to interested intervals
SELECT interval_start, interval_end
FROM InterestedBookingGaps
ORDER BY interval_start

interval_start interval_end
2003-09-30 08:00:00.000 2003-09-30 08:30:00.000
2003-09-30 12:30:00.000 2003-09-30 13:00:00.000
2003-09-30 16:30:00.000 2003-09-30 18:00:00.000
2003-09-30 20:00:00.000 2003-09-30 21:00:00.000
2003-09-30 23:30:00.000 2003-09-30 23:59:00.000

-- Free time
SELECT COALESCE(SUM(DATEDIFF(MINUTE, interval_start, interval_end))
/ 60.0, 0) AS hours_free,
COALESCE(SUM(DATEDIFF(MINUTE, interval_start, interval_end)), 0)
AS minutes_free
FROM InterestedBookingGaps

hours_free minutes_free
3.983333 239

Regards,
jag

Tuesday, February 14, 2012

Calculating number of subscriptions in time

Hi all,

I'm new to SQL AS and MDX in general but I'm trying to build the following and maybe you guys can help me.

I have a snowflake database with a table containing subscription. Each subscription has a date/time at which it starts and a date/time at which it is terminated:

subscription:

id

start_date

end_date

....

100

10-jul-2005 17:00

10-aug-2005 11:00

...

101

11-jun-2005 17:00

12-dec-2006 18:00

...

102

1-may-2005 17:00

17-jan-2007 13:00

...

103

10-apr-2005 17:00

10-sep-2005 15:00

...


And there is a dimension-table called datetime:

id

year

month

....

10-jul-2005 17:00

2005

06

...

10-jul-2005 18:00

2005

06

...

...

..

..

...

11-jul-2005 17:00

2005

06

...

11-jul-2005 18:00

2005

06

...

AS will generate the count-measures for start-date and end-date (e.g. the number of subscriptions started at any given moment: 1 started at 10-jul-2005 17:00). But I also want to know how many subscription are active at any given moment (e.g. 0 at 1-jan-2005 12:00, but 2 at 1-jan-2006 12:00 and 4 at 1-aug-2005 12:00). In SQL I would produce something like 'select count(*) from subscription where measuredate between start_date and end_date' but how do you create something like that in AS using MDX?

TIA,
Ferdinand

Not sure how well this Adventure Works query corresponds to your scenario, but it computes the number of products that are "active" during the selected Calendar Date member:

>>

With

Member [Measures].[ProductCount] as

Count(Filter(existing [Product].[Product].[Product],

[Product].[Start Date].MemberValue <=

ClosingPeriod([Date].[Calendar].[Date]).MemberValue

AND ([Product].[End Date].CurrentMember is

[Product].[End Date].[Active]

OR [Product].[End Date].MemberValue >=

OpeningPeriod([Date].[Calendar].[Date]).MemberValue)))

select

[Date].[Calendar Year].Members *

{[Measures].[ProductCount]} on 0,

[Product].[Category].Members on 1

from [Adventure Works]

All Periods CY 2001 CY 2002 CY 2003 CY 2004
ProductCount ProductCount ProductCount ProductCount ProductCount
All Products 606 283 411 534 406
Accessories 35 3 13 32 22
Bikes 125 30 65 95 60
Clothing 48 7 28 41 20
Components 189 34 96 157 95

>>

|||

Sorry for my 'not so very fast'-reply, but I had some other bussines to attend to. The above example is not entirly what I need. I need to create three measures with the model I posted:

1. I need to count all activations (e.g. all subscriptions that are activated (=start_date) at an given moment

2. All terminations (=end_date) at an given moment

3. All active subscriptions (= given date between start_date and end_date) at an given moment.

1 and 2 are relativly simple. These are accomplshed by using the generated [Count of Subscription] measure and the also generated [Start DateTime] and [End DateTime] dimensions. But the third measure is not so simple (for me that is). Has anyone got a clue on this?

The pivot I whish to create will look something like this:

Number of subscriptions

Number of activations

Number of terminations

01-jan-2007

100

1

0

02-jan-2007

101

0

5

03-jan-2007

96

0

0

04-jan-2007

96

0

0

|||

Well, in your report there is only one date for number of subscriptions, there isn't start_date and end_date. So I assume it means number of active subscriptions at that date. The standard way of doing it is maintaining in fact table measure "Number of subscriptions", and then you will put 1 for it when there is an activation and -1 when there is a termination. By implementing running sum calculation on the lowest level of Date dimension - you will get what you need. This problem is the same as classic inventory management problem - you can read more about it here: http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

|||

Thanks for your tip Mosha.

I've created a view where there is a 1 for each activation and a -1 for each termination and indeed when you sum them up at a specified day the number of active subscriptions is acurate. I use this MDX script to create the sum at the specified day:

CREATE MEMBER CurrentCube.[Measures].[Number of active subscriptions] as
sum
(
[Date Time].[Day].Level.members(0):ClosingPeriod([Date Time].[Day].Level),
[Measures].[SubSum]
),
visible=true;

However, this script does not seem to work at other levels then day (my time dimension contains two hierarchies: year->quarter->month->day->hour and year->week->day->hour). The results at month level for example is the result of the sum of all records the fall within that month and not the sum of all records up to the first day of the month (which should be the case). Same goes for week, quarter and year level. The lowest level (hour) works fine, that is, it contains the same value as for the whole day.

Can you tell me what I'm doing wrong?

|||

I don't think at the month level you want to sum up to the first day of the month. I think you want to get the sum up to the current day. While it looks like this problem is for LastNonEmptyChild semiadditive calculation, it actually can be solved more efficiently and still give correct results with LastChild. And if you don't want to work with LastChild semiadditive aggregation, you can simulate it with the series of lines like

SCOPE (Measures.[Number of active subscriptions);

[Date Time].[YQMDH].[Month] = [Date Time].[YQMDH].LastChild;

[Date Time].[YQMDH].[Quarter] = [Date Time].[YQMDH].LastChild.LastChild;

[Date Time].[YQMDH].[Year] = [Date Time].[YQMDH].LastChild.LastChild.LastChild;

// etc

END SCOPE;

|||Thanks, that last part did the trick!

Calculating number of subscriptions in time

Hi all,

I'm new to SQL AS and MDX in general but I'm trying to build the following and maybe you guys can help me.

I have a snowflake database with a table containing subscription. Each subscription has a date/time at which it starts and a date/time at which it is terminated:

subscription:

id

start_date

end_date

....

100

10-jul-2005 17:00

10-aug-2005 11:00

...

101

11-jun-2005 17:00

12-dec-2006 18:00

...

102

1-may-2005 17:00

17-jan-2007 13:00

...

103

10-apr-2005 17:00

10-sep-2005 15:00

...


And there is a dimension-table called datetime:

id

year

month

....

10-jul-2005 17:00

2005

06

...

10-jul-2005 18:00

2005

06

...

...

..

..

...

11-jul-2005 17:00

2005

06

...

11-jul-2005 18:00

2005

06

...

AS will generate the count-measures for start-date and end-date (e.g. the number of subscriptions started at any given moment: 1 started at 10-jul-2005 17:00). But I also want to know how many subscription are active at any given moment (e.g. 0 at 1-jan-2005 12:00, but 2 at 1-jan-2006 12:00 and 4 at 1-aug-2005 12:00). In SQL I would produce something like 'select count(*) from subscription where measuredate between start_date and end_date' but how do you create something like that in AS using MDX?

TIA,
Ferdinand

Not sure how well this Adventure Works query corresponds to your scenario, but it computes the number of products that are "active" during the selected Calendar Date member:

>>

With

Member [Measures].[ProductCount] as

Count(Filter(existing [Product].[Product].[Product],

[Product].[Start Date].MemberValue <=

ClosingPeriod([Date].[Calendar].[Date]).MemberValue

AND ([Product].[End Date].CurrentMember is

[Product].[End Date].[Active]

OR [Product].[End Date].MemberValue >=

OpeningPeriod([Date].[Calendar].[Date]).MemberValue)))

select

[Date].[Calendar Year].Members *

{[Measures].[ProductCount]} on 0,

[Product].[Category].Members on 1

from [Adventure Works]

All Periods CY 2001 CY 2002 CY 2003 CY 2004
ProductCount ProductCount ProductCount ProductCount ProductCount
All Products 606 283 411 534 406
Accessories 35 3 13 32 22
Bikes 125 30 65 95 60
Clothing 48 7 28 41 20
Components 189 34 96 157 95

>>

|||

Sorry for my 'not so very fast'-reply, but I had some other bussines to attend to. The above example is not entirly what I need. I need to create three measures with the model I posted:

1. I need to count all activations (e.g. all subscriptions that are activated (=start_date) at an given moment

2. All terminations (=end_date) at an given moment

3. All active subscriptions (= given date between start_date and end_date) at an given moment.

1 and 2 are relativly simple. These are accomplshed by using the generated [Count of Subscription] measure and the also generated [Start DateTime] and [End DateTime] dimensions. But the third measure is not so simple (for me that is). Has anyone got a clue on this?

The pivot I whish to create will look something like this:

Number of subscriptions

Number of activations

Number of terminations

01-jan-2007

100

1

0

02-jan-2007

101

0

5

03-jan-2007

96

0

0

04-jan-2007

96

0

0

|||

Well, in your report there is only one date for number of subscriptions, there isn't start_date and end_date. So I assume it means number of active subscriptions at that date. The standard way of doing it is maintaining in fact table measure "Number of subscriptions", and then you will put 1 for it when there is an activation and -1 when there is a termination. By implementing running sum calculation on the lowest level of Date dimension - you will get what you need. This problem is the same as classic inventory management problem - you can read more about it here: http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

|||

Thanks for your tip Mosha.

I've created a view where there is a 1 for each activation and a -1 for each termination and indeed when you sum them up at a specified day the number of active subscriptions is acurate. I use this MDX script to create the sum at the specified day:

CREATE MEMBER CurrentCube.[Measures].[Number of active subscriptions] as
sum
(
[Date Time].[Day].Level.members(0):ClosingPeriod([Date Time].[Day].Level),
[Measures].[SubSum]
),
visible=true;

However, this script does not seem to work at other levels then day (my time dimension contains two hierarchies: year->quarter->month->day->hour and year->week->day->hour). The results at month level for example is the result of the sum of all records the fall within that month and not the sum of all records up to the first day of the month (which should be the case). Same goes for week, quarter and year level. The lowest level (hour) works fine, that is, it contains the same value as for the whole day.

Can you tell me what I'm doing wrong?

|||

I don't think at the month level you want to sum up to the first day of the month. I think you want to get the sum up to the current day. While it looks like this problem is for LastNonEmptyChild semiadditive calculation, it actually can be solved more efficiently and still give correct results with LastChild. And if you don't want to work with LastChild semiadditive aggregation, you can simulate it with the series of lines like

SCOPE (Measures.[Number of active subscriptions);

[Date Time].[YQMDH].[Month] = [Date Time].[YQMDH].LastChild;

[Date Time].[YQMDH].[Quarter] = [Date Time].[YQMDH].LastChild.LastChild;

[Date Time].[YQMDH].[Year] = [Date Time].[YQMDH].LastChild.LastChild.LastChild;

// etc

END SCOPE;

|||Thanks, that last part did the trick!

Calculating Median value from measures and dimensions

I am facing some problem in calculating Median

I am trying to calculate the median value using one of the measures and a dimension value.

Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:

opid time median

1 55

2 23

3 23

Total 23

The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.

What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.

I am using a calculated field Median with the following expression.

MEDIAN

( [Dim Operation].[Dim Operation].currentmember.children ,[Measures].[Elapsed Time])

Thanks

I think the reason you're getting a null for the first three rows is that the .Children function won't return any members if you're at the leaf level already. Instead, you'll want to use the Descendants function. Something like the following should do the trick:

Median(

Descendants([Dim Operation].[Dim Operation].CurrentMember, ,LEAVES)

,[Measures].[Elapsed Time]

)

Now if you're expecting to calculate a median value for opid 1 for more than simply the opid 1 value, then you'll need to add another dimension to the equation.

Calculating length of time a Ticket was suspended


I'm trying to find a solution to for a report i am building and was hoping that some of you will share your expertise with me. Basically, I need to work out how long an Incident Ticket was suspended (ie it's status is "on hold").

The data is stored something similar to the following...

Ticket Date Status
-
333 14/03/2005 10:24:19 "to on hold"
333 14/03/2005 15:23:01 "from on hold"
334 14/03/2005 11:14:11 "to on hold"
334 14/03/2005 16:26:15 "from on hold"
335 15/03/2005 10:10:15 "to on hold"
335 15/03/2005 11:15:35 "from on hold"
335 15/03/2005 13:26:10 "to on hold"
335 15/03/2005 14:30:59 "from on hold"
335 16/03/2005 14:00:05 "to on hold"
335 16/03/2005 16:45:15 "from on hold"
336 16/03/2005 10:10:15 "to on hold"
336 16/03/2005 12:45:12 "from on hold"

This is the result i'd like to see....

Ticket Start Hold End Hold Time Suspended
- -- --
333 14/03/2005 10:24:19 14/03/2005 15:23:01 datediff(...
334 14/03/2005 11:14:11 14/03/2005 16:26:15 datediff(...
335 15/03/2005 10:10:15 15/03/2005 11:15:35 datediff(...
335 15/03/2005 13:26:10 15/03/2005 14:30:59 datediff(...
335 16/03/2005 14:00:05 16/03/2005 16:45:15 datediff(...
336 16/03/2005 10:10:15 16/03/2005 12:45:12 datediff(...

Has anybody done something along the same lines? If so, could you point me in the right direction?

Thanks in anticipation

Jon

Hello Jon,

This will give you the TicketID and the Start and End Date for Hold status. From your report, you can get the time difference for 'Time Suspended' (or you could do the datediff directly in this query).

select tl.TicketID,
(select min(date) from TicketLog tl1 where TicketID = tl.TicketID and tl1.Status = 'To On Hold') as StartHold,
(select max(date) from TicketLog tl2 where TicketID = tl.TicketID and tl2.Status = 'From On Hold') as EndHold,
from TicketLog tl

Here is another way, the min and max date are returned for any log that has 'On Hold' in the status. So, if it is required that each ticket can be on hold only once and you're reporting tickets that are no longer on hold, this would work.

select tl.TicketID, min(tl.date), max(tl.date)
from TicketLog tl
where tl.Status like '% on Hold'
group by tl.TicketID
having count(*) > 1

Hope this helps.

Jarret

|||

Hi Jarret

Thanks for your input.

I was able to get the same results as the ones your code generates... but unfortunately it's not quite what I need.

The above code generates only one row for each ticket with the first date a ticket went into suspend status and the last date a ticket left suspend status. A Ticket can enter and leave suspend status many times (or never) during it's life. I need to see a row for each time a ticket entered and left suspend status. I can then do a datediff and sum up all the times in suspend status. Then I can take this time off the total length of time the ticket has been open.

Cheers

Jon

|||

I have now got a view with data something like the following...

Ticket Clock HoldStart HoldStop

331 Start 2007-02-01 10:00:00 NULL
331 Stop NULL 2007-02-01 11:00:00
332 Start 2007-02-15 12:01:00 NULL
332 Stop NULL 2007-02-15 13:45:11
333 Start 2007-02-17 10:00:06 NULL
333 Stop NULL 2007-02-17 11:07:00
333 Start 2007-02-18 11:11:12 NULL
333 Stop NULL 2007-02-18 16:01:00
333 Start 2007-02-18 18:07:00 NULL
333 Stop NULL 2007-02-19 17:00:00
334 Start 2007-02-20 10:00:00 NULL
334 Stop NULL 2007-02-20 11:00:00
334 Start 2007-02-20 14:00:00 NULL
334 Stop NULL 2007-02-21 17:00:00

A little bit closer, but still not what I need... from this, I'd to see...


Ticket Clock HoldStart HoldStop

331 Start 2007-02-01 10:00:00 2007-02-01 11:00:00
332 Start 2007-02-15 12:01:00 2007-02-15 13:45:11
333 Start 2007-02-17 10:00:06 2007-02-17 11:07:00
333 Start 2007-02-18 11:11:12 2007-02-18 16:01:00
333 Start 2007-02-18 18:07:00 2007-02-19 17:00:00
334 Start 2007-02-20 10:00:00 2007-02-20 11:00:00
334 Start 2007-02-20 14:00:00 2007-02-21 17:00:00

any ideas?


|||

I think you are going to need to loop through your dataset with a cursor (or similar) to get the values you're looking for.

Jarret

Calculating last non empty of measures that are not included in a month

Hi!

I have two tables.

One is measures like this

Product Measure Date

Prod A 10 12, Jan 2006

Prod A 15 20, Jan 2006

Prod A 11 5, Mar 2006

The other is time dimension

When I create a cube, I would like to see by months last value of measure.

So in Feb I should get 15.

My question is - should I create a query and run it on server to

1. Find which months are not icluded in measuers

2. Then find last value for each product

3. Write into new table measures, where February will have last non empty value from January?

Or can I do it cube in some way?

The best way to solve this in AS2005 is to define measure aggregation function as LastNonEmpty - it will do exactly what you need.