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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Select only works with datetime variable, not table

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server
 

Hello,

I am a learning-as-I-go person without formal training.  I have been working with Access databases for about 10 years, and have begun working with SQL in the last 5 years or so.  The system in user here is a 2K3 server (8xXeon 2.8Ghz, 12GB RAM, 1TB HDD) running SQL Server 2005 (32bit).  I am trying to develop a SQL query (view) which looks for values of one table' field at specific times based on a table of those times.  When I use a datetime variable, that particular value at that particular datetime will be returned.  When I try to replace the variable with the datetime result of a second query(view), no data is returned.  The code is below.

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2010-09-30 04:59:00'		-- Need to replace this value with the the list of 'PriVolCk' datetimes
SET NOCOUNT 
                                     
                                            
                                        

View Complete Post


More Related Resource Links

SQL Select With Variable Table - Using with ODBC Data Source

  

Hello all,

Having difficulty with an idea for a SQL query today.  I am working with a data set that points to an ODBC data source.  The data source has several TRANSACTIONmmyyyy tables (mm - month, yyyy - year ), each holding data for a particular month, going back several years. Each table has identical fields and data types.

If I were to run the query below for just one month ( say I took out mmyyyy and plugged in a month and year, 052010 ) it will pull totals for the AccountNum I specify.

 

SELECT
 DISTINCT
 TOTALS.Description, TRANSACTION
mmyyyy.AcctNum, SUM
 (TRANSACTION
mmyyyy.Amount ) As
 "Current Month"

FROM TRANSACTION mmyyyy, TOTALS
WHERE TRANSACTION mmyyyy.AcctNum = TOTALS.AcctNum AND
TRANSACTION mmyyyy.Region = TOTALS.Region AND
TRANSACTION mmyyyy.Region

SELECT statement to return NULL by matching data from another table.

  
Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

select max record to join another table sybase

  
select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)

  
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work.  Be sure and create the 'Audit' table first though. The following code write audit entries to a Table called'Audit' with columns 'ActionType'    //varchar'TableName'    //varchar'PK'    //varchar'FieldName'    //varchar'OldValue'    //varchar'NewValue'    //varchar'ChangeDateTime'    //datetime'ChangeBy'    //varchar using System;using System.Data;using System.Data.SqlClient;using Microsoft.SqlServer.Server; public partial class Triggers{//A Generic Trigger for Insert, Update and Delete Actions on any Table[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")] public static void AuditTrigger(){SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Contextstring TName; //Where we store the Altered Table's Namestring User; //Where we will store the Database UsernameDataRow iRow; //DataRow to hold the inserted valuesDataRow dRow; //DataRow to how the deleted/overwritten valuesDataRow aRow; //Audit DataRow to build our Audit entry withstring PKString; //Will temporarily store the Primary Key Column Names and Values hereusing (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection{conn.Open();//Open the Connection//Build the AuditAdapter an

can alias in select be used for selecting other column in that table?

  
Hi All,I want to use an alias name in a select clause to select other column in that table? select   top 1 (   case when CreatedByName <> '' then 'yy'         else 'xx' end) as filName, (filName + 'xx')from Order       But it throws error like " Invalid column name 'fileName'."Could you please help me out?

How to load a new table with the value of a variable from SSIS package?

  
Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

  
Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

Using EXEC with a table variable?

  
I want to pass the results of a stored proc into a table variable in SQL SEVER 2000. Something like this declare @a table ( employeeid int )   exec @a=dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2   ---------------------------------------------------------------------------------------------------------------------------------- I tried insert @a execute dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2    but got this error: Msg 197, Level 15, State 1, Line 6 EXECUTE cannot be used as a source when inserting into a table variable.   How can i do this.   Thanks.

how to select >= the datetime

  
i got a select statement where the datetime i want more than 10min or equal to the datetime from DateStore field, how can i do that? select .. from..where DateTime between DateStore And DateStore + 10 MINUTE -- something like this

Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime

  
Hi, I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL: stor_id     char(4)     not null, ord_num  varchar(20) not null, date         datetime    not null I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016: select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales') The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime]. So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k Starting copy... 30 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.) C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k Starting copy..

Tricky SELECT query from a Single Table

  
Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks

Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime

  
Hi, I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL: stor_id     char(4)     not null, ord_num  varchar(20) not null, date         datetime    not null I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016: select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales') The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime]. So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k Starting copy... 30 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.) C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k Starting copy..

Stored Procedure utilizing table variable

  
I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword.  The code looks like this: ALTER PROCEDURE [dbo].[studentBranchLogin] -- Add the parameters for the stored procedure here @userName varchar(50), @userPass varchar(20) AS BEGIN DECLARE @dActive bit; DECLARE @TT TABLE (dept int, active bit); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO @TT(dept, active) SELECT deptid, active FROM srcuDeptId WHERE active = TRUE -- Insert statements for procedure here SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT END I get a Msg 170, Level 15 state 1 Line 24: Incorrect syntax near '@TT'. So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.   Any assistance is greatly appreciated.

Using SELECT with LIKE % surrounding a variable

  
Hi  Can anyone give me some basic help please. I'm just trying to learn to write to SQL to make my job easier. I've started using variables but the SELECT statement does not work properly when I use it with LIKE and try to surround my variable with % wildcards. Can anyone tell me the correct syntax please :-   DECLARE @location varchar SET @location='L'   SELECT DESCRIPTION,ID,* FROM LOCATION WHERE UPPER(DESCRIPTION) LIKE ('%' + @location + '%')  Many thanks Matthew

How to declare a table variable in SSIS and then insert rows into it

  
Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

Passing Table Variable to Stored Procedure

  
My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

SELECT COUNT(*) FROM [Table] from an Oracle database

  
Hi friends, I have problem when retrieving a result from SELECT COUNT(*) FROM [Table] from an Oracle database. When I try to put the result (single row) in a variable I get the following error message. [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowsSource": "Unsupported data type on result set binding RowsSource.". Pls help me Mahe
Categories: 
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