.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How can i create this SQL Statement

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server


I have a database and i need to get some reports from it

table name is newatt

table content


Sname              Time             Date   

    x                  08:30            24/4/2010

    x                  10:30            24/4/2010

    x                  11:30            24/4/2010

    y                  12:00            24/4/2010

    x                 05:00              24/4/2010

    y   &nbs

View Complete Post

More Related Resource Links

Create Statement and Collation

I have DTS which includes a step to create table: CREATE TABLE [contracts] (             [ContractId] [varchar] (10) NOT NULL ,             [ClientId] [varchar] (10)   NOT NULL ,             [contract_nm] [varchar] (50)  NULL ,             [CreditLimit] [money] NOT NULL ,             [prod_cd] [varchar] (2)  NOT NULL ,             [DateOpened] [datetime]  NOT NULL ,             [interestfree_period] [int] NOT NULL ,             [DateFirstPayment] [datetime] NULL , … However, if I examine the table using sp_help after it is created the ContractId field has collation specified against it. [ContractId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , Why is collation specified against the ContractId field when I have not specified it in the create statement in the DTS? Thanks in advanceA SQL Server DBA Wannabe

CREATE GLOBAL CUBE statement - losing hierarchies due to slicing

Hi,   I have a SSAS cube from which I generate local cubes with the MDX statement below:   CREATE GLOBAL CUBE MyLocalCube   storage 'MyLocalCube.cub'   FROM [MyCube]  (          MEASURE [MyCube].[Measure1],          MEASURE [MyCube].[Measure2],           DIMENSION [MyCube].[Dimension1],          DIMENSION [MyCube].[Dimension2]          (             LEVEL [HLevel1],                     LEVEL [HLevel2],                     LEVEL [HLevel3],             MEMBER [Dimension2].[Hierarchy1].[HLevel2].&[MemberX]       ),         DIMENSION [MyCube].[Dimension3]  )   The problem is that this statement generates a local cube file which works fine but all other hierarchies defined in Dimension2 are not included. I cannot find the syntax for adding them manually. So, in other w

can create table statement be used in and ssis expresssion?

I need to modify the preparation sql task built by the import wizard. I wanted to create an expression  using the create table sql but insert the variable to append the date and another descriptor  to make it dynamic. The source is Excel The expression would look something like this "Create Table" + @user::descriptor +  @user::todaysdate+" ( name varchar(50) ..........." SSIS cannot evaluate the sql although it runs fine in ssms The column mappings will always be the same but the table name needs to be dynamic

How to avoid "'CREATE VIEW' must be the first statement in a query batch" when I need to use SET Q

I have a problem that I have been unable to solve.  I have a stored procedure in a database on server A that needs to create an indexed view in a database on server B.  (The environment is SQL Server 2005.)  The procedure on server A does the following:  (The EXEC statement inside the quotes is really a variable in my procedure, I've just substituted the value to make the example understandable.) EXEC sp_executesql 'EXEC [ServerB].[Database].dbo.sp_executesql @sqlStatement;' The "outer" sp_executesql causes the "inner" sp_executesql to be execute within the database on server B.  (@sqlStatement contains the CREATE VIEW syntax.)  I am able to create the view that I want to index with no problem.  However, when I attempt to create an index on the view in a subsequent step, it fails with the message "Cannot create index. Object '_dta_mv_51' was created with the following SET options off: 'QUOTED_IDENTIFIER'".  Fair enough, but if I attempt to precede my CREATE VIEW statement with the required QUOTED_IDENTIFIER, I receive an error to the effect that "'CREATE VIEW' must be the first statement in a query batch".  I'm not sure how to get around this problem.  Does anyone have the answer? SQL Server 2008 doesn't object to indexing a view that was created with QUOTED_IDENTIFIER

SSIS 2005 - Dynamically generate create table statement from source



I have a requirement where i had to migrate the table structure and data from ODBC database to SQL Server. I have dynamic source reader that uses sql query from variable to get the data and the next step is to get the create statement and create table and then to insert data. how can i get Create Statement the destination produces and use sql task to create the table and then insert the data using data flow task.

Please suggest



Select statement with two group level to create addtional field.


Here is an example of my question

I have  a column called Department, a column called Employee Name and a column called EmployeeAbsent.

Obviously i can easily use a select satement to get each employee's absent status. However, I would like to generate an additional field called DepartmentAbsent to see if anyone in a particular depart is absent based on the EmployeeAbset field.

how should I write the query?

Create a 12 place decimal value in a select statement

I am needing to create a 12 place decimal field in sql2005 using a select statement.  I can do select 999999999.99 as 'Amount' but I really don't want the 9's.  Any idea how this can be done without creating the 9's? 

How to create call statement as object in SSAS


Hi Experts,

                 Any one please tell me how to create as object or attribute for the below Call statement.

Otherwise please guide me how to use the below statement in SSAS.

call SQLQuery.ExecuteSql(
    "Provider=SQLNCLI10.1;Data Source=DWHSRV01;Integrated Security=SSPI;Initial Catalog=Banking"
    ,"Exec Key 1")




how to stop ScriptTransfer from generate "Create User" "Create Schema" sql statement?



I am using the following code to generate scripts, but no matter how i configure the options, it always generate create user statement




// Connection to Server
        Server srv = new Server(ConfigurationManager.AppSettings["Server"]);

        srv.ConnectionContext.LoginSecure = false;

How to create 3 tier application using LINQ

As you know that in 3 tier architecture there are three layers

User interface layer. (Is our Form in Windows application and .aspx page in Web application)
Data Access layer. (Which provides interface between Business logic layer and Database)
Business Logic layer.(Which stores your application logic)

Create ToolTip for GridView Header

The following allows you to add a tooltip to the header links of your GridView, a feature that is lacking.

Create And Run Excel Macro At Runtime in C#

You've been tasked with writing a Windows Form application using the .NET Framework that interacts with Microsoft Excel. If you are new to working with Excel in .NET and are having trouble learning how to format certain cells or cell ranges, then this little tip will come in handy. Rather than scour the internet hoping to find the correct set of constants or method syntax for formatting your spreadsheet, use the built in tools in Excel to write the familiar VBA code for you. Then, you can paste the VBA code generated by Excel into the sample C# code below and run it dynamically at runtime.

Create and Use a Typed DataSet by Using Visual C# .NET

This article shows you how to create and use a typed DataSet in a small Web application. Typed DataSets, which inherit from the DataSet class, create first-class members of a DataSet's DataTables and DataColumns, yielding the many benefits that come with the use of strong typing.

Connestion string create any database server

This links important to how to create connection string to any data base sever.

Dynamically Create Controls in ASP.NET by Using Visual C# .NET

This article demonstrates how to dynamically create a control for an .aspx page. The sample project does the following:
Creates two TextBox controls.
Verifies that the contents (TextBox.text) and the attributes of the TextBox are saved across posts to the server.
Illustrates handling events that are posted by a dynamically-created control

Dynamically Create Controls in ASP.NET with Visual Basic .NET

This step-by-step article describes how to dynamically create controls for an ASPX Web page.

The sample project does the following:
It creates two TextBox controls.
It verifies that the TextBox contents (TextBox.text) and attributes are saved across posts to the server.
It describes how events that are posted by a dynamically created control are handled.

How to create a windows mobile (Smart Device) .Cab installer

A Cab file is the default setup format for Windows CE and Windows Mobile devices (similar to windows .msi files). You probably already have installed several application using .cab files, and are familiar with the concept. One point which is often unknown is that .cab files are processed by wceloader.exe, and it can only install one .cab file at a time. That means we cannot have nested .cab files. It doesn't mean we cannot have .cab files contained by another .cab, but the contained .cab files will not be installed during the installation of the container .cab. We should install it manually after the container .cab file installation has been completed.

There are two ways to create a cabinet (.Cab) file. The traditional one, and the friendly VS Smart Device Cab project which doesn't require additional coding, but which also relies on the traditional one at low-level.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend