computing for the number of hours from 2 date attributes

Hi,

I want to build a calculated fields that compute for the number of hours between 2 date attributes.

let's say I have 2 dates:

1/1/2007 15:30

1/2/2007 9:10

It will give me 18 hours.

cherriesh

Try

VBA!DateDiff('h', DateValue1, DateValue2)

|||

Depend where you want ir and the importance for your project...

You can do that calculation inside a report, in a cube... or you can create it using SQL statment in a named calculation in teh datasourceview in Analysis Services...

helped?

Regards!

|||

Problem resolved?

regards!!

Composite Key?

I'm just now learning both SQL and ASP.NET, and I cannot seem to figure out how to build my data structure. I believe the answer to my problem is a composite key, but I cannot seem to get it to work. Here is an example. My database is of recorded dances, with exact locations within a ballroom. I believe I need 2 tables

Table #1 - DanceTable
Columns: DanceID, Name, Description, Tags

Table #2 - StepsTable
Columns DanceID, StepID, longLocation, latLocation, Action, Description

Within my ASP.NET application I want to be able to enter data about a dance, including metadata and a series of steps. The Dance and metadata content to be stored in DanceTable, and the series of moves stored in the StepsTable. I want the steps to be IDed as 1, 2, 3, 4...x with the first step being labled 1. and I want each dance to have it's own unique ID (DanceID). Right now I'm using "ExecuteNonQuery()" to add my data to my SQL database, and when I add new steps to the StepsTable SQL just finds the largest ID within StepID and increments it by one. So my steps are labeled as:

Dance1:
Step1, Step2, Step3, Step4

Dance2:
Step5, Step 6, Step7

What I really want is (or I think what I want is) is a composite primary key.

Dance1:
Step1, Step2, Step3, Step4

Dance2:
Step1, Step2, Step3

That way the StepID is used as both a primary key and it indicates the position within the dance. I guess I could just use a standard SQL table, let SQL auto generate StepID's and then add a new column called something like "StepNumber", but it just seems goofy to be generating a stepID and never using it. With composite keys (If I understand them) each step would have a unique key as a combination of the DanceID+StepID (AKA Dance 345, steps 1-10).

I pull up data by searching for dances, and then sort by StepNumber, and those should all be unique...if I can figure out how to build them.

A composite key is just a key made from multiple fields. In your case, it would be DanceID,StepID.

Your tables look fine, although if you make the StepID an identity field, you really don't need to store the "StepNumber". It's redundant. You can derive the "StepNumber" by the number of records that have the same DanceID and a lower StepID.

This would get you the @.StepNumber-th step:

SELECT TOP 1 *

FROM ( SELECT TOP (@.StepNumber) *

FROM StepsTable

WHEREDanceID=@.DanceID

ORDER BY StepID ASC) t1

ORDER BY t1.StepID DESC

Or get them all in order with "StepNumber":

SELECT *,ROW_NUMBER() OVER (ORDER BY StepID) As StepNumber

FROM StepsTable

WHEREDanceID=@.DanceID

ORDER BY StepID

But of course there is nothing prohibiting you from including a StepNumber field (Like in case you don't always want the steps to be renumbered, or they aren't contiguious, like step 1, 3, 5 with no step 2 or 4, etc). In this case, your primary key would be StepID, and I would create a unique index/constraint on DanceID,StepNumber.

Complete grammar for SQL 2000 and 2005?

I would like to build a TSQL parser and lexer for various reasons. One of them involves complex data import procedures where we need to ensure that columns are populated before they are used as data sources or criteria for other columns. There are actually many very cool and useful things that could be done with a good TSQL parser.

My question is this: are there any grammars available online for DDL, DML, whatever you call the DBCC statements, etc.? I should think Microsoft would have to have something like an EBNF for their own parsers; would it be possible to make them public?
No. The grammar for TSQL is not public information. Why does data import procedures need to look at the code? You can verify data post or pre or during processing or declaratively using foreign keys, constraints, indexed views etc.|||Declarative programming has limits that we have exceeded. Constraints simply won't catch all of the errors that can and have happened to us. The alternative to what I describe is painstaking checking of code, work that can and therefore should be done automatically.

Another use for the parser would be to build a little editor that provides intellisense and also shows better error messages, such as in cases where a column name is ambiguous (why not use intellisense to suggest one of the valid aliases/table names?). Usage of indexes in queries could be indicated by coloring of column names, extended properties of columns could be displayed using tooltips, etc. Basically, I have a desire to bring the sort of power of Visual Studio 2005 offers C# developers to SQL developers. It would be much easier to do this with a grammar from Microsoft, instead of building my own via trial and error. To be honest, I should think Microsoft would be in favor of such tools, as they would provide additional incentive for developers to use SQL Server.

|||

I agree that a parser will help for providing more productive environment. But I just do not see how it helps to check data purity for instance. In most cases, you will have to analyze complex procedural logic which may or may not be entirely on the server-side. So it is probably best to catch those at the source or destination.

Anyway, I don't know of any plans in the near future to release the grammar for TSQL or SQL implementation. If you are using ANSI SQL compatible coding standards then you can get the grammar for free on the internet. This should help you for the SQL part not TSQL syntax.

|||The data import process works like the following. I first preprocess a single text file I receive from a client and generate several tab-delimited files consumable by BCP. Along with the files, I create format files based on metadata. I run BCP, dumping the data in a primary table. After that, I run numerous scripts against the data to converting text codes to foreign keys, verify data adhere to specified formats, categorize data using fairly complex SQL statements, etc. Some statements depend on the values assigned by previous statements. Once the complexity of this interdependence gets to a certain level, it gets to be a pain to maintain. Verifying correctness would be trivial if I could parse SQL and see which fields are assigned to, which fields are assigned from, and which fields are used in criteria. The alternative is to manually recreate these three collections of fields.

The above is not the only use I have for TSQL parsing. Our current application is based of a metadata architecture used to describe databases so that our client has absolutely zero database-specific code. This involves, among other things, a query generation engine that probably equals if not exceeds the abilities of the .NET 3.5 Entity Framework. We now have two versions of the query generation engine: a VB6 one and a C# one. It would be invaluable to test the C# one by ensuring that it generates the same effective SQL as the VB6 one. I would rather not enforce character-for-character equality -- my time can be spent in better ways that completely reverse engineering the old algorithm so that the new one matches the old character-for-character. Being able to treat TSQL code as data, Lisp style, is the best solution.

I don't want to come off as sounding antagonistic, although it may be too late. I just don't understand why Microsoft wants to make my life (and by proxy, of many of their loyal developers) harder by not providing a completely derivable piece of data. I wouldn't be competing with them by having a full grammar; I would be enhancing one of their existing products, making it more lucrative. Surely Microsoft understands the power of treating code as data -- there is the CodeDom for generating .NET code, and the .NET GC was originally prototyped in Lisp, a language renowned for its treatment of code as data. DLINQ depends on Expression<T>, which can be generated by lambda functions, which are code converted to data.

I have a feeling all of the above is for nought. My impression is that Microsoft can be completely opaque if it wants and a little guy like me has no chance in getting any sort of insightful answer from them to a question like this. I would absolutely love to be wrong!

Microsoft understands how much of a big deal intellisense is.