Query Data Based On Moving Datetime.

Feb 5, 2008

I'm trying to query data from a database for a report that looks for the last 2 weeks starting at 10pm, taking a value once every 24 hours. Using

AND DateTime >= DateAdd(wk,-2,GETDATE())

AND DateTime <= GetDate()")
I easily get the last two weeks but the query obviously only grabs the data at the time the query runs. I need to be able to run it any time of the day but only grab the data at 10pm. I'm very new at this so please excuse my ignorance but I could really use some help with this. Thanks very much.

View 4 Replies


ADVERTISEMENT

Select Query Based On Datetime

Feb 12, 2002

I need to select certain rows based on a "datetime" column. I need to select rows from 8am yesterday until 8am today.
In Oracle I would use:
select * from foo where TIMESTAMP >= trunc(sysdate - 1) + 8/24 AND TIMESTAMP < trunc(sysdate) + 8/24.
This would start at 8am yesterday and end at 7:59am today.

How would I do this with T-SQL?

thank you,

Mark
Fiesta_donald@email.com

View 1 Replies View Related

T-SQL (SS2K8) :: Grouping Of Data Based On Moving Time Period

Mar 13, 2014

To give you some context we have a new amendments application (nothing fancy, excel based with SQL Server back end) that allows users to submit amendments to product data (Product Info, PO Prices, Dates etc.). There is also an admin tool that a team uses to action these amendments in the various systems.

The old version of this tool, users submitted amendments by style and could if need be submit multiple amendments against one product at the same time. The new tool, I believe for audit reasons, users submit by amendment type, so for example I would submit a cost price change for a given style.

The issue now is that on the occasions where a user has multiple amendments, they now come through separately. So cost price would be Amendment 1 and a date change would be amendment 2 even though they could be the same product. This could potentially mean that the admin team would be duplicating work if the paperwork is updated and sent after each amendment, whereas before they would make both changes and only send the paperwork once.

Having not built either of these tools, I've been tasked with trying to fix this, my two thoughts being either to amend the user form to somehow capture/ allow users to submit amendments together or try to use the existing data and doing the grouping dynamically in the back end. Use that lag to look at grouping any submitted amendments that occur within 30mins of the first occurrence of that style

This grouping would then be given a joint time so when the 'time lag' period passes the amendments will be visible together.I've tried a few things and a few head on desk moments trying to get a set based approach but haven't been able to get where i want, its either an issue where amendments span an hour, such as 9:59 and then 10:03 or grouping together amendments that happen after the 30mins of the first one.

Here is some sample data

USE FF_Winning_Together;
IF OBJECT_ID(N'tempdb..#AmendTest',N'U') IS NOT NULL
DROP TABLE #AmendTest;
CREATE TABLE #AmendTest
(
AmendmentIDINT IDENTITY(1,1)NOT NULL,
StyleCHAR(1)NOT NULL,
AmendmentStatusVARCHAR(10)NOT NULL,
DTDATETIMENOT NULL

[code]....

View 7 Replies View Related

Moving From Access To A Web Based Approach

Aug 7, 2006

Hi all.

I am not a programer but managed to piece together an Access database that is the backbone of my company.

My IT group advised me to move to an SQL server back end and move to a web based front end.

My question. I am very comfortible with Access and modify the databases regularly. What front end is most like Access for modifying forms, reports, macros, etc.

Thanks!

View 2 Replies View Related

System.Data.SqlClient.SqlException: The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Dec 14, 2005

After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.

System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.

Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.

At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.

anyone having this problem before?? hopefully you guys can help.

Thanks,

View 4 Replies View Related

Way Of Segregating Data Based On Datatype Using Any Query?

Oct 22, 2013

Is there any way of segregating data in sql, based on data-type using any query?

View 2 Replies View Related

Web Based Data Mining Query... Installation Issue..

Dec 4, 2006

Hey gang,
I have ran into an installation issue that has been bugging me all weekend. I'm trying to run the preditive analytics query from a web based application (ASP.NET). I have installed a data pump as directed by this article "Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003" because this isn't set up under a domain. I also set appropriate roles in Data Mining Database (set it to everyone for testing purpose).

ConnectionString is "MSOLAP.3;Persist Security Info=True;Data Source=http://1.0.0.1/olap/msmdpump.dll;Initial Catalog=DataMiningDB"

Try
Dim connection As AdomdConnection = Me.Connect
Dim command As New AdomdCommand(txtQuery, connection)
Dim adapter As New AdomdDataAdapter(command)
Dim dataset As New DataSet

' Fill the dataset
adapter.Fill(dataset)

' Bind to grid
Return dataset
Catch ex As Exception
Throw ex
End Try
Return Nothing

Exception Details: Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Errors in the high-level relational engine. A connection could not be made to the data source specified in the query.
Errors in the high-level relational engine. A connection could not be made to the data source specified in the query.
OLE DB error: OLE DB or ODBC error: Login failed for user 'VMSQL2006IUSR_VMSQL2006'.; 28000.

It happens on the adapter.Fill(dataset). Any ideas?

I have also tried to set the olap application pool to use different identity instead of "Network Services", but I always get the same error message (but with user I specified instead of I_USR).

-Young K

View 3 Replies View Related

Sql Server Express Problem When Moving From VWD File Based To IIS

Dec 5, 2006

Hi All,
 
We have a file based asp.net app built using Visual Web Developer and Sql Server Express 2005. We have finished development and testing and are now moving to the deployment stage. As a first step, we would like to be able to view it on a test machine using IIS (instead of VWDs built in web server). We have created a virtual directory in IIS and can view our app correctly at http:localhost/ForIIS_test.
 
However, when we get to a page that tries to access our Sql Sever Express database, we get the following error:
An attempt to attach an auto-named database for file C:Documents and SettingsClaudeMy DocumentsVisual Studio 2005WebSitesForIIS_testApp_Data estDatastore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Does anyone know how to overcome this problem? Any help appreciated.
Claude.

View 1 Replies View Related

Moving Average Using Select Statement Or Cursor Based?

Jul 30, 2007

ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE)
1 01/01/2007 PURCHASE 10 1000
2 01/01/2007 PURCHAES 5 1100
3 01/01/2007 SALES -5 *TobeCalculated
4 02/01/2007 Purchase 20 9000
5 02/01/2007 SALES -10 *TobeCalculated
5 02/01/2007 purchase 50 8000
6 03/01/2007 Sales -10 *TobeCalculate
7 01/01/2007 Purchase 20 12000

I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?

View 20 Replies View Related

SQL Query - Use Date Format Data And Match It To MS-SQL's Datetime Datatype?

Apr 12, 2007

Need some quick help here.. The data I got from the text file use the "04/11/2007" date format and the StockDate in MS-SQL use the datetime datatype.

My understanding is that the "04/11/2007" will default to the 04/11/2007 12:00 am" format in MS-SQL.

So, when I use the sql query, how do search only for the date part of the data in MS-SQL and match it to the data from text file? I tried this SQL Query below.

--snip--
SELECT RawID FROM tblPurchaseRaw WHERE VIN = '" & sVin.ToString.Trim & "' AND StockDate = '" & dStockDate.ToString.Trim & "'
--snip--

That way, if I get a row then I know the data is there. If I don't get a row then I know the data is not there.

Bold: I get it now. It is all automatic as MS-SQL does it automatically...

View 5 Replies View Related

Sql Query To Retrieve Comments Based On Selected Month And Data

Jan 4, 2008

hi,
i am generating report for my project.
my table goes like
ID   VASID     VASSID     JanMail   JanVisit    JanPhone    JanComment    FebMail   FebVisit    FebPhone   FebComment................. DecPhone  
1      25            4              True         False         False            Mail me             False       True           False            Visit me                  
 
My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively.. 
once user check on Mail and select a month suppose January in my report,the report should generate only January Comments.
here user can select any number of months.if user selects january,february and march, report should generate respective months Comments.
Please help me in writing SQL Query to get the comments.......
 
if any one know the solution for the above problem,Please help me
 
Thanks & Regards,
 

View 2 Replies View Related

Query To Select Data Based On Alphanumeric (surname) Information.

Jul 31, 2007

Hello,

I am trying to write a query that will be able to select different segments of data based on spelling of the last name.

For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ'

then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on...

I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way?

Thanks

View 6 Replies View Related

Transact SQL :: Query Based On Date - Get Data From Table Above WHERE DateofBirth Is Tomorrow

Oct 30, 2015

Lets say I have a table, tblPersons

FirstName |  LastName   |  DateofBirth
----------------------------------------
  Thomas   | Alva Edison |   10-10-2015
  Benjamin | Franklin    |     10-10-2015
  Thomas   | More        |     11-10-2015
  Thomas   | Jefferson   |    12-10-2015

Suppose today's date is 09-10-2015 in (dd-MM-yyyy format), I want to perform a query in such a way that I should get the data from the table above WHERE DateofBirth is tomorrow, so I could get the following result.

FirstName |  LastName   |  DateofBirth
----------------------------------------
  Thomas   | Alva Edison |   10-10-2015
  Benjamin | Franklin    |     10-10-2015

View 15 Replies View Related

Implicit Conversion From Data Type Datetime To Int Is Not Allowed. Use The CONVERT Function To Run This Query.

Mar 26, 2008

Hey im trying to store a category name and the date into a database. For some reason i keep getting this error
 Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
This error is the error im getting back from the database. the datetime field in the database is a datatype (DateTime) so what exactly is going on ?protected void InsertNewCat_Click(object sender, EventArgs e)
{                    string insertSql = "INSERT into Category (CategoryName,Date) VALUES (@Category, @Date)";
                    string conString = WebConfigurationManager.ConnectionStrings["ProCo"].ConnectionString;                    SqlConnection con = new SqlConnection(conString);
                    SqlCommand cmd = new SqlCommand(insertSql, con);                   cmd.Parameters.AddWithValue("@Category", NewCat.Text);
                    cmd.Parameters.AddWithValue("@Date",DateTime.Now);
 
try
{
              con.Open();             int update = cmd.ExecuteNonQuery();            CatInsertStatus.Text = update.ToString() + " record updated.";
}catch (Exception Err)
{
             CatInsertStatus.Text = Err.Message;
}
finally
{
             con.Close();
}
}

View 9 Replies View Related

Datetime-Based Operations

Apr 26, 2007

I'm new to SQL Server and relatively new to database design and I have a specific problem I'm trying to resolve.  I have a collection of records in a table (let's call them 'tasks') which represent some list of things that needs to be completed.  Each task has an associated datetime on which this action is to commence, and my applications is responsible for executing these tasks.  My first instinct is to regularly poll the 'tasks' table to determine if there are any tasks which have not been processed and are past their schedule start datetime.   But, I'm wondering if there is some sort of database feature that would  recognize that a task's starttime has arrived, and could somehow communicate this to my application without my application having to constantly poll the database.  Thanks.

View 5 Replies View Related

Slipt Rows Based On Datetime

May 24, 2006

Hello!

I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table:

row1:

initial_time: 2006-05-24 8:00:00

final_time: 2006-05-24 8:30:00

row2:

initial_time: 2006-05-24 8:35:00

final_time: 2006-05-24 9:15:00

I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into:

row2_a:

initial_time: 2006-05-24 8:35:00

initial_time: 2006-05-24 8:59:59

row2_b:

initial_time: 2006-05-24 9:00:00

initial_time: 2006-05-24 9:15:00

Is it possible to do it in a query, I mean, without using procedures?

Thank you!

View 3 Replies View Related

SQL Query: Finding Records Between Datetime Inside Datetime

Mar 17, 2007

Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int)     |    stamp_start (Type: DateTime)        |      stamp_end (Type: DateTime)           |      Username (varchar)0             |      17-03-07 12:00:00                      |            17-03-07 12:30:00                     |     Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
 Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark

View 2 Replies View Related

Getting Error : : The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value

Jan 28, 2008

update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?

View 5 Replies View Related

Problem Updating A Record Based On A Datetime.

Sep 24, 2007

Hi People,
hope someone can help me out here with a little problem.
 Basically i've go a asp.net page which has a listbox on. This list box is populated from a SQL database table with the datetime of the a selected field. Thus in the list box you get a list of strings looking like this "24/09/07 12:58"
Also on the page is a submit button, and some other editing textboxes. The main issue here is the when the submit button is used i get the currently selected listbox timedate string and then pass this along with other items to update a record in the database based on the datetime in the listbox control.
 
Below is how i get the string from the listbox control
Dim except_time As DateTime
except_time = DropDownList1.SelectedValue
The expect_time is then passed to store procedure along with some other vars, which looks like this 
-- =============================================-- Author: Lee Trueman-- Create date: 1st Sept 2007-- =============================================CREATE PROCEDURE [dbo].[spExcept_UpdateData]  -- Add the parameters for the stored procedure here @validated bit, @update_time datetime, @except_time datetimeASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON
     -- Insert statements for procedure here UPDATE exceptions SET    validated = @validated,    update_time = @update_time WHERE  (except_time = @except_time)END
So validated and update_time should be updated when except_time is equal to @except_time
 
My problem is that the database never updates. If i debug the asp.net page the watch var shows the datetime in US format (I.e "09/24/07 12:58"), if this is true then this would explain why when it is passed to the stored proc nothing gets updated, as there would not be a date match.
can anyone see any silly mistakes i'm doing here ???? 

View 2 Replies View Related

The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Apr 19, 2008

Advance thanks ....... My table is  TimeSheet:-----------------------------------  CREATE TABLE [dbo].[TimeSheet](    [autoid] [int] IDENTITY(1,1) NOT NULL,    [UserId] [int] NOT NULL,    [starttime] [datetime] NOT NULL,    [endtime] [datetime] NOT NULL,    [summary] [nvarchar](50) NOT NULL,    [description] [nvarchar](50) NULL,    [dtOfEntry] [datetime] NOT NULL,    [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution  

View 5 Replies View Related

The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Aug 3, 2005

Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards

View 6 Replies View Related

SQL DateTime Limit = Enemy Of Historical Based Websites

Jun 18, 2007

I'm trying to create a website based on interesting historical facts, Searchable by date. I need each fact entry to have a datetime field in the database, but unfortunately this method only lets me go as far back as the year 1753 (for an inexplicable reason, I can go up to the year 9999).Is there something I'm overlooking here, or did Microsoft goof this one? Will I have to store my dates as 3 separate custom month/day/year fields? If anybody has a solution for this, please let me know (a B.C./A.D enabled solution isn't at all needed, but I'm open to that too).

View 1 Replies View Related

SELECT From DateTime Field Based On Current Date

Feb 5, 2008

I am trying to match records that are >= the current date. I have tried using:
SELECT DISTINCT name
FROM table
WHERE datefield >= DATEPART(month, GETDATE()) AND datefield >= DATEPART(day, GETDATE()) AND datefield >= DATEPART(year, GETDATE())
ORDER BY name
but this is not giving me the result that I am looking for. What is the best way to match a DateTime field type using the current date without the time?

View 5 Replies View Related

How To Convert UTC Based Datetime Values To Local Setting?

Sep 11, 2007

Hello:

I've recently discovered the MS CRM 3.0 stamps any datetime field in SQL as UTC (aka GMT). Even though the end user may select 12:00 noon on the CRM gui interface, the datetime is stamped in SQL as 16:00 (I'm in EST).

So, is there an easy way to read the 16:00 back to local EST, taking into account daylightsavings (DST)? Apparently in VB or C# this conversion is a trivial matter of using ToLocalTime(), but I don't believe SQL 2005 has such a convenient function.

I'm currently building a SQL view that I wish to use to support multiple reports, and it would be ideal for the view to translate the UTC datetime fields (even if via a user-defined function) to EST rather than coding every report or custom app to translate the UTC.

Any suggestions or links to code will be greatly appreciated!

Thanks,
Pete

View 5 Replies View Related

Datetime Data Type Resulted In An Out-of-range Datetime Value. Please Help

May 13, 2006

Hi,
I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date
 '31/08/2006 23:28:59'
 I get the error "...datetime data type resulted in an out-of-range datetime value"
I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?
cheers,
Ernest

View 2 Replies View Related

Insert Adjusted Datetime Into Sql Field Based On User Locale

Jul 29, 2004

Is there a way to adjust a date and time depending on the users locale before inserting into the database?

Users login in and their country of residence is stored in the database on signup. My server is in the US and I want to reset part of the users db record when it's the end of the day in their country so I need to enter the date and time into the db plus or minus the time difference, is there a function written to do this already or would i need to write my own?

View 1 Replies View Related

X-Axis Scalar Mode Based On DateTime Field - Not Working

Mar 8, 2007

I have a chart showing quantity against time. I had been using X-Axis in category mode, but wanted to include all dates, so changed it to Scalar mode by checking "Numeric or time-scale values".

However instead of displaying dates it simply shows integers, and to make things worse, Y-axis values have all come out as zero. It appears that the integer values are simply sequential values for each datapoint (i.e. 1,2,3,4 etc).

The category field for the X-Axis is a field called "Date" which comes from a Date Type field in the cube based on a datetime column in the DSV. The properties of the category in the chart are : Group On =Fields!Date.Value and Label =Fields!Date.Value.

Am I missing something obvious?

View 10 Replies View Related

Stored Proc To Get Single Person From A Table Based On Earliest Datetime

Oct 13, 2005

Hi,

I'm having problems with a stored procedure, that i'm hoping someone can help me with.

I have a table with 2 columns - Username (varchar), LastAllocation (datetime)

The Username column will always have values, LastAllocation may have NULL values. Example

Username | LastAllocation
------------------------
Greg | 02 October 2005 15:30
John | 02 October 2005 18:00
Mike | <NULL>

My stored procedure needs to pull back a user name with the following criteria:

If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation

Then update the LastAllocation column with GETDate() for that username.

This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln?
Thanks
Greg
------------------------------------------------------------------------------
------------------------------------------------------------------------------
CREATE PROCEDURE STP_GetNextSalesPerson AS
DECLARE @NextSalesPerson varchar(100)

BEGIN TRAN

IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC
END
ELSE
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC
END

SELECT @NextSalesPerson
UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson


COMMIT TRAN
GO

View 2 Replies View Related

ODBC-based To OLE DB-based Data Transfer

Oct 7, 2006

I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!

View 1 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View 1 Replies View Related

How To Use Value Calcuated In Query In Subsequent Query, All Based On Value In Querystring?

Jun 5, 2008

I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string.  But, I don't know how to get a value returned from one sql statement into a second.  Here's what I'm trying to do...
From the QueryString, we know that the MatterID = 4  ( xxx.aspx?MatterID=4)
Knowing that the Matterid=4, I query the database to get the OfficeId for that MID  (Select OfficeID from tMatter where Mid=4)   ~This returns an OfficeID of 6
So, then I need to do another query to get the employees where OfficeID = 6   (Select EmployeeID from tEmployees where OfficeID = 6)
How do I do these in one query, or how do I use the Calculated Value for the OfficeID in the 2nd statement? 
 

View 3 Replies View Related

Returning Results Based On One Query Into Existing Query

Aug 2, 2012

linking two tables together to get an end result

find below the code i have used

The first part of the query provides me with the info i need

SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp

[Code].....

View 1 Replies View Related

Problem With SQL Query Not Moving Through Every Record

Jul 1, 2004

I was wondering if anyone could quickly identify why the query is not parsing through each line in the temp table? I am sure its something stupid and easy, but if anyone has an idea, I would greatly appreciate the help!

My results are the same row repeated exactly the same for the number of rows in the temp table.






Declare @Topic varchar(150)
Declare @CustomTitle varchar(150)
Declare @FullName varchar(100)
Declare @starttime datetime
Declare @endtime datetime




select
TOPIC = t.TopicName,
CustomTitle = e.ssCustomTitle,
StartTime = v.StartDateTime,
eFirstName = FirstName,
eLastName = LastName,
eEndTime = ssEndTime

INTO #tmpwork

FROM
brSession e
INNER JOIN v_SessionStartDateTime v on v.ssSessionId=e.ssSessionId
LEFT OUTER JOIN Topic t on t.TopicId=e.ssTopicId
LEFT JOIN brssPresenter ep ON (e.ssSessionID = ep.prSessionId)
LEFT OUTER JOIN Personnel p on p.PersonnelNbr=ep.prPerNbr
LEFT OUTER JOIN brVirtualRoom vr ON vr.vrBriefingId=e.ssBriefingId AND vr.vrVirtualRoomId=e.ssVirtualRoomId
LEFT OUTER JOIN brLocation bl ON bl.loBriefingId=vr.vrBriefingId AND bl.loVirtualRoomId=vr.vrVirtualRoomId
LEFT OUTER JOIN location BR ON BR.LocationId=bl.loLocationId
LEFT OUTER JOIN brssDetail1 dt on dt.dt1SessionId=e.ssSessionId
LEFT OUTER JOIN Competitor c on CompetitorId=dt.dt1CompetitorId
LEFT OUTER JOIN PresentationStyle ps ON ps.PresentationStyleId=dt.dt1PresentationStyleId
WHERE
e.ssBriefingID = 11749
and((not prConfirmModeId = 0) or (prPerNbr is null))
ORDER BY ssStartTime

SELECT @Topic = Topic,
@CustomTitle = CustomTitle,
@FullName = eFirstname + ' ' + eLastName,
@StartTime = starttime,
@EndTime = eEndTime
From #tmpwork




IF (@CustomTitle is not null)
IF (not @CustomTitle = '') --correct problem of ZLS
Begin
set @Topic = @CustomTitle
End

SELECT
Topic = @Topic,
StartTime = @StartTime,
FullName = @FullName,
EndTime = @endtime

INTO #Final

FROM #tmpwork


select * from #Final


drop table #tmpwork
drop table #Final

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved