Sql Query Results Based On Column Value?
I have a stored procedure which selects results based on some date calculations.
In my table I have a status column and two date fields (Approval Date and Signature Date)
If the value in the status column says approved I want to select results where approval date - signature date is less than a certain number of days.
If the status is naything other than approved i want to select results where sysdate - signature date is less than the given number of days.
How can i achieve this?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Dynamiclly Remove Duplicate Rows From Results Table Based On Column Data?
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them. each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field. Ideas? Thanks, Ad.
View Replies !
View Related
Display Results Based On Query
Hi, I don't know if anyone has encountered this before but here goes: I've a select clause below: result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'" queryLocalName is an input field that allows the user to search for non-English characters in the database. What I'm wondering is what kind of effect is the N in the where clause is having? I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.
View Replies !
View Related
Assign A Variable Based Upon Query Results...how To Do It?
I have the following code which is incomplete. Where it says: txtVendorID = I need it to equal the results of the field VendorID from my query...here is my code. What do I need to add there? Dim cmdSelect As SqlCommandDim intRecordIDintRecordID = Request.QueryString("RecordID")strConn = ConfigurationManager.AppSettings("conn")conn = New SqlConnection(strConn)cmdSelect = New SqlCommand("spMfgRepListAddaspxByRecordID", conn)cmdSelect.CommandType = CommandType.StoredProcedurecmdSelect.Parameters.AddWithValue("@RecordID", intRecordID)conn.Open()cmdSelect.ExecuteReader()txtVendorID.Text = conn.Close()
View Replies !
View Related
Query To Find A Value In Column B Based On An Aggregate Function On Column A?
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View Replies !
View Related
Query Based (partly) On CSV Column
I'll try to simplify the problem as much as I can. There's survey. It has different types of question answer pairs, but the problem is in Multiple Choices, Multiple Answers questions, where user can choose, for example 3 checkboxes from 5 at the web form, for the question. I am emphasizing three tables: tbl_questions IDquestion (primary) question (textual question) tbl_answers IDanswer (primary) IDquestion (foreign,to table tbl_questions) answer (textual answer) tbl_results IDresult (primary) IDquestion (foreign, to tbl_question) result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7") Inside those tables are data (I'll put here just two questions): Example: tbl_questions IDquestion_question 1_________Which OS do you use? 2_________Which databases do you use? tbl_answers IDanswer_IDquestion_answer 1________1_________Windows 2________1_________Linux 3________1_________Mac OS 4________2_________SQL Server 5________2_________MS Access 6________2_________MySQL 7________2_________Oracle 8________2_________Other 9________2_________No, I do not use databases tbl_results IDresult_IDquestion_results ........ 23_______1________1,2 24_______2________4,6,7 ........ Is there a possibility to make query, using columns, but also the data inside the CSV format in results (varchar or char) column? Result of the query would be: IDquestion_IDanswer_answer 1_________1________Windows 1_________1________Linux 2_________4________SQL Server 2_________6________MySQL 2_________7________Oracle Further queries based on this query should be able to make statistics (GROUP BY).
View Replies !
View Related
Add A Column Based On A Select Query
Supose I have the following select: Select Name, Age, (select TelNums from Telephone) From Person The problem is that (select TelNums from Telephone) can return more than 1 record: tel1 tel2 . . . I was wondering how I can make a select to return the tel numbers like: 'tel1,tel2,tel2' »»» Ken.A
View Replies !
View Related
Pivot Query - Assigning Row Value Based On Column Name
Hey all, i have a question for all the SQL Gurus out there. I have tried to think of a way around, it, but i cannot work it out. I have a set of data: Samples Below: Item Warehouse FOR1 FOR2 FOR3 FOR4 FOR5 FOR6 FOR7 FOR8 FOR9 FOR10 FOR11 FOR12 FOR13 FOR14 01-0001 010 329 329 335 343 317 331 328 331 31 I have written a Query to Pivot this data like below: SELECT WAREHOUSE,ITEM, QTY FROM (SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10, for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21, for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p UNPIVOT (QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7, for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19, for20,for21,for22,for23,for24))AS unpvt Warehouse Item Qty 010 01-0001 329 010 01-0001 329 010 01-0001 335 010 01-0001 343 010 01-0001 317 010 01-0001 331 010 01-0001 328 010 01-0001 331 010 01-0001 315 010 01-0001 344 010 01-0001 334 010 01-0001 321 010 01-0001 327 010 01-0001 328 010 01-0001 332 010 01-0001 342 010 01-0001 316 010 01-0001 330 010 01-0001 330 010 01-0001 331 010 01-0001 315 010 01-0001 343 010 01-0001 333 010 01-0001 322 I would like to add some more code to the query, so for each FOR% column, i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward. Example: Warehouse Item Qty Month 010 01-0001 329 1 010 01-0001 329 2 010 01-0001 335 3 010 01-0001 343 4 010 01-0001 317 5 010 01-0001 331 6 010 01-0001 328 7 010 01-0001 331 8 010 01-0001 315 9 010 01-0001 344 10 010 01-0001 334 11 010 01-0001 321 12 010 01-0001 327 13 010 01-0001 328 14 010 01-0001 332 15 010 01-0001 342 16 010 01-0001 316 17 010 01-0001 330 18 010 01-0001 330 19 010 01-0001 331 20 010 01-0001 315 21 010 01-0001 343 22 010 01-0001 333 23 010 01-0001 322 24 Does anyone know how i can do this? Many Thnank Scotty
View Replies !
View Related
Derived Column Based On Result Of Oracle Query
Hi, I need to create a derived column for each row in a SQL dataset. This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0). One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ? Thanks
View Replies !
View Related
Howto Use The Query Results As View Column?
Hi All,I have two tables, one is about member infomations, the other is thecatergoriesmember_info(id,name,email,phone)member_categories(id,category)how can create a view like this (id, name, category1, category2,category3) with high performance?Thanks in advance.Joshua
View Replies !
View Related
Select Query Results In Multiple Columns Based On &&"type&&" From Another Table
Using SQL Server 2005 Express: I'd like to know how to do a SELECT Query using the following tables: Miles Table: Date/Car/Miles/MilesTypeID =============== (some date)/Ford/20/1 (some date)Ford/20/2 (some date)Chevy/30/1 (some date)Toyota/50/3 (some date)Ford/30/3 Miles Type Table MilesTypeID/MilesType ================= 1/City 2/Highway 3/Off-Road I'd like the results to be like this: Date/Car/City Miles/Highway Miles/Off-Road Miles ===================================== (date)-Ford-20-0-0 (date)-Chevy-0-20-0 (date)-Ford-20-0-0 (date)-Toyota-0-0-50 (date)-Ford-0-0-30 Anyone? Thanks in advance!
View Replies !
View Related
Query Performance Problems With Join On UDF-based Computed Column
We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast: select * from TheTable A, FKeyTable B where A.ComputedColumn1 = B.KeyColumn but this one sends the CPU usage of SQL Server to 99% for a very long time: select * from TheTable A, FKeyTable B where A.ComputedColumn2 = B.KeyColumn The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable). Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it? This is SQL Server 2005 SP2, btw.
View Replies !
View Related
HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation: I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner: (AISC_Shapes_Table) AISC_MANUAL_LABEL W W44x300 300 W42x200 200 (and so on) WT22x150 150 WT21x100 100 (and so on) MT12.5x12.4 12.4 MT12x10 10 (etc.) I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is: Code SnippetSELECT AISC_MANUAL_LABEL, W FROM AISC_Shape_Table WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%')) It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance. Regards, Steve G.
View Replies !
View Related
Select Query Based Upon Results Of Another Select Query??
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return. This is implemented as a stored procedure declare @OwnerIdent varchar(7) set @OwnerIdent='A12345B' SELECT table1.val1 FROM table1 INNER JOIN table2 ON table1. Ident = table2.Ident WHERE table2.Ident = @OwnerIdent 'Now for each result of the above I need to run the below query SELECT Clients.Name , Clients.Address1 , Clients.BPhone, Clients.email FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey WHERE Growers.PIN = @newpin) '@newpin being the result from first query Any help appreciated
View Replies !
View Related
Column Based On Other Column (short Name Based On Name), When To Do The Transformation?
Hi! I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using: Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName Create a derived column in the SSIS flow which does the same thing Create the ProductShortName column as a computed column which uses substring on ProductName Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated Create a named calculation in the table in the Analysis Services project's data source view Create a named query in the Analysis Services project's data source view I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried? Which approach(es) do or would you use? Pros and cons? Thanks!
View Replies !
View Related
Return The Results Of A Select Query In A Column Of Another Select Query.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
View Replies !
View Related
Report Based On Filtered Query OR Based On Custom Query
adp on SLQ7 What would be wise to do. I'm creating a report based on two inner joined tables and i've got a total sum field for each line in the report. (Price * Ordered) This results in the following sql statement: Code: sqlStr = "SELECT *, [Table1].Ordered * [Table1].Price AS LineTotal FROM [Table1] INNER JOIN [Table2] ON [Table1].RecieptNumber = [Table2].Number WHERE ([Table1].RecieptNumber = " & varNumber & ")" I think I have a few options now. 1. Leave the varNumber out of the query, and save it as a regular query. And now filter the report on varNumber. 2. Write the constructed query with createquerydef to eg. "TmpQueryForReport" And set the reports recordsource fixed to TmpQueryForReport. 3. Set the recordsource of the report to constructed sqlStr on Report_open() --------------------- Option 1, I got this working but when the database grows (and it will) this might get awfully slow. Option 2 I had this working before i switched to using MSSQL server 7.00 After the switch i thought, this might be a problem with giving db access rights cause the users might need write rights to the db. Option 3. Fast and easy??? Option 4 Stored procedures???
View Replies !
View Related
DISTINCT Results Based On The Value Of One Col?
Here's one thats had me and a coworker puzzled. Hopefully it's something simple: TABLE A: (log of messages) userid, int date, datetime message, varchar(50) Table A Data: 1, 6/18/2008 @ 2:32:41, This is my message 1, 6/18/2008 @ 2:31:02, This is my message 1, 6/17/2008 @ 7:34:26, This is another message 2, 6/18/2008 @ 2:32:41, This is not his message 2, 6/16/2008 @ 11:21:32, This is my message TABLE B: (List of users) userid, int name, varchar(100) Table B Data: 1, John 2, Mike I want to extract the most recent message logged per user, i.e.: name | date | message --------|---------------------|------------------------- John | 6/18/2008 @ 2:32:41 | This is my message Mike | 6/18/2008 @ 2:32:41 | This is not his message. I have been unable to come up with a query that can return just the one value. I've tried variants of: SELECT DISTINCT b.name, a.date, a.message FROM a INNER JOIN b ON a.userid = b.userid including sub-queries and even played with the visual diagrams trying to design this in Enterprise Manager but none of the combinations I tried work. Is there an easy way to do this via a query? I don't have experience with stored procedures. Would that be necessary? TIA
View Replies !
View Related
UPDATE Based On Results?
Hello, I'm trying to update FOR_SORTING to 1, for all instances of a RESPONSE_ID when QUESTION_ID = YESNO and RESPONSE = Yes. So, for below I'm trying to update FOR_SORTING to 1, for RESPONSE_ID that has Question_ID that is equal to YESNO and Response equals Yes. No. RESPONSE_ID QUESTION_ID RESPONSE FOR_SORTING 1. 1 RATING Incredible 2. 1 YESNO Yes 1 3. 2 RATING Incredible 4. 2 YESNO No The Table is called LSN_RESPONSE_DETAILS I tried the following, but it only updates the one row. UPDATE LSN_RESPONSE_DETAIL SET FOR_SORTING = '1' WHERE QUESTION_ID = 'YESNO' and 'RESPONSE = 'Yes' Any idea's?
View Replies !
View Related
Getting The Average Results Based On Time.
Hello~, The table has columns like this. ________________________________ time smalldatetime value1 int value2 int ---------------------------------------------------------- for example, .... '2006-11-16 12:00:00',100,200 '2006-11-16 13:00:00',110,210 '2006-11-16 14:00:00',120,220 .... The record is inserted at every hour. I want get daily,monthly,yearly average and display the result ordered by time.
View Replies !
View Related
Entity Results Based On Parameter
In the report designer cant we createb report paramter to create an table using new named query. when I deploy the report model and when the power user try to access the entity it should prompt an prompt asking for which quarter information does he need. Thats is when an end user click on the entity it should prompt for paramter and based on the paramter the results of entity should be avaialble. In some cases based on user i want to limit the number of fields in an entity can an user see. Regards, Navin
View Replies !
View Related
Returning Results Based On Dates
I want to write a stored procedure that takes two three paramters based on a case statement determining which values are null something like this Select InformationDate From thisTAble Where If @dateValue IsNot @Null Informationdate = @dateValue Else Where In this second where cluase I want to be able to pull out all the results based on a date range and i am not sure how the syntaz would go InfomationDate IsBettween @daterangeFrom @dateRangeTwo Any help on this i hope i was clear...thank you!
View Replies !
View Related
Unique Results Based On Popularity Voting
I've managed to get my query to this: Product | Color | Votes ======== ======= ======= Bus Red 10 Bus Blue 5 Train Blue 1Car Red 1 Car Blue 1 I need the most popular color of the product based on the votes for each product, so MAX(Votes) GROUP BY Product solves the Bus and the Train, but I still need a result for the Car? Not really that bothered which Color is picked up, do I need to run another query, because I need another table to join to the results. Any help would be greatly appreciated. Thanks, Pete.
View Replies !
View Related
Display Results Based On Percentage Match
Dear Pals, I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match. I mean the result set should be displayed based on percentage match. For example i have the below table. create table test ( id int identity(1,1) primary key, ename varchar(10) ) insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg' I need the output something similar as below REG Reg Regsxysn psReg I have tried out with full text indexing but i could'nt get the required output. Any suggestions would be appreciated. Thanks in Advance.
View Replies !
View Related
Lookup Based On Results Of Previous Lookups
i'd like to use ssis on a certain project but am concerned that one of my transformations needs lookup results to be based on actions taken on previous lookups and that the toolkit doesnt really offer something like that. so, i have a dataflow whose first component extracts certain kinds of data from an xml document. each row returned by the latter needs a lookup but the results of that lookup may dictate a certain kind of update. The next row's lookup may need to be influenced by the previous row's update. So I think I have two challenges, 1) combining a lookup and update, 2) making sure the buffer architecture completes one lookup and update before the next lookup begins.
View Replies !
View Related
Listing Results Based On The Starting Alphabet
Hi, I have a report which lists out the employees' details. I need A-Z links above the report, On clicking on an alphabet, say "C", should display all the employee details whose name starts with the selected alphabet. In the stored procedure we can accept the character and return back those results. But it is not a drill report and we need the result in the same report. Is there any way so that on clicking each link, the output will be shown in the same report. Any help is appreciated. Thanks in advance, Sonu
View Replies !
View Related
Display Results Based On Percentage Match
Dear Pals, I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match. I mean the result set should be displayed based on percentage match. For example i have the below table. create table test ( id int identity(1,1) primary key, ename varchar(10) ) insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg' I need the output something similar as below REG Reg Regsxysn psReg I have tried out with full text indexing but i could'nt get the required output. Any suggestions would be appreciated. Thanks in Advance.
View Replies !
View Related
Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
View Replies !
View Related
Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?
Table structure as follows Employee Empno empname salary commission I want to have an other employee table named employee_modified Empno empname salary commission derived_column1(salary+commission) derived_column2(derived_column1 + xxxx) and so on derive other columns based on the earlier derived columns) Is that possible to do it.. or am I doing something wrong. something like Select empno , empname , salary , commission, (salary + commission) as derived_colum1 , (derived_colum1 + xxxxx) as derived_colum2 , (derived_colum2 + xxxxx) as derived_colum3 into employee_modified from employee
View Replies !
View Related
Fixing My Table Based On Dbcc Showcontig Results
Can someone please help me interpret this result set below and suggeston way I can speed up my table? What changes should I make?DBCC SHOWCONTIG scanning 'tblListing' table...Table: 'tblListing' (1092914965); index ID: 1, database ID: 13TABLE level scan performed.- Pages Scanned................................: 97044- Extents Scanned..............................: 12177- Extent Switches..............................: 13452- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 90.17% [12131:13453]- Logical Scan Fragmentation ..................: 0.86%- Extent Scan Fragmentation ...................: 2.68%- Avg. Bytes Free per Page.....................: 1415.8- Avg. Page Density (full).....................: 82.51%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Thank you.
View Replies !
View Related
Alter Column Results In Incorrect Syntax Near 'column'
I ran this query against the pubs database and it runs successfully ALTER TABLE publishers ALTER COLUMN state CHAR(25) I change the table & field names for my db as follows: ALTER TABLE customquery ALTER COLUMN toclause CHAR(25) and run against my database and I get the following error - Incorrect syntax near 'COLUMN'. My column name is correct - I don't know why it would run fine against pubs, but not my db. I do not have quoted identifiers turned on. I have tried using [] around my column name [toclause], but that didn't change anything. Any help would be appreciated. Thanks.
View Replies !
View Related
Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.
I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query: DECLARE @login char(20), /*This sets the rep for the query.*/ @record_date datetime, /*This is the date that we want to run this for.*/ @RWPY decimal(18,2), /*This is the required wins per year.*/ @OCPW decimal(18,2), /*This is the opportunities closed per week.*/ @OACW decimal(18,2), /*This is opportunities advanced to close per week.*/ @TOC decimal(18,2), /*This is the total number of opportunities in close.*/ @OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/ @TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/ @OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/ @TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/ @OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/ @TOI decimal(18,2), /*This is the total number of opportunities in interview.*/ @OCW decimal(18,2), /*This is the opportunities created per week.*/ @TOA decimal(18,2) /*This is the total number of opportunities in approach.*/ SET @login = 'GREP' SET @record_date = '12/18/2007' SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date))) SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SELECT loginname, CAST(@TOA AS decimal(18,1)) AS [Opps in Approach], app_time AS [Approach Average Time], app_perc_adv AS [Approach Perc Adv], CAST(@TOI AS decimal(18,1)) AS [Opps in Interview], int_time AS [Interview Average Time], int_perc_adv AS [Interview Perc Adv], CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate], dem_time AS [Demonstrate Average Time], dem_perc_adv AS [Demonstrate Perc Adv], CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate], neg_time AS [Negotiate Average Time], neg_perc_adv AS [Negotiate Perc Adv], CAST(@TOC AS decimal(18,1)) AS [Opps In Close], cls_time AS [Close Average Time], cls_perc_adv AS [Close Perc Adv] FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record. CREATE TABLE #pipelinehist ( glusftboid int IDENTITY(1,1) NOT NULL, record_date datetime NOT NULL, loginname char(20) NOT NULL, app_new float NOT NULL, app_time float NOT NULL, app_perc_adv float NOT NULL, int_time float NOT NULL, int_perc_adv float NOT NULL, dem_time float NOT NULL, dem_perc_adv float NOT NULL, neg_time float NOT NULL, neg_perc_adv float NOT NULL, cls_time float NOT NULL, cls_perc_adv float NOT NULL, target_deal money NOT NULL, annual_quota money NOT NULL, weeks int NOT NULL ) ON [PRIMARY] INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)
View Replies !
View Related
Update Column Value Based On Value In Another Column?
Hi. I'm writing a web application with VS2005 andSQL Server 2005 express edition. I have an SQL table: Table name: statistics Columns: stat_id firm_name stat_month stat_year view_count follow_count percentage When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query: UPDATE [statistics] SET percentage = follow_count / view_count * 100 WHERE (stat_id = 15) This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2) Any help would be appreciated.
View Replies !
View Related
Query Diff Results From Ent Manager Query And Query Analizer
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date) SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1 FROM testsupplierSCNCR I am getting the result of 01/01/2007 but in query analizer I get the result of 12/31/2006 Why the different dates
View Replies !
View Related
Return Value Based On Column
Hello all, I am a newbie to MSSQL Functions so forgive me if this is an elementary question. I have two tables, ContactRelations, and RelationshipName, ContactRelations FK - RelationshipID FK - Contact1ID FK - Contact2ID RelationshipName PK - RelationshipID - Relationship - InverseRelationship When the user will selects a contact and their relationship, an entry will be added with the contact id's and relationshipID. If the selected relationship is in the relationship column, the user's ID will be entered into Contact1, and if the relationship is in the InverseRelationship column, then the user's ID will be entered into Contact2. On the display page, I'll search the Contact1 and Contact2 columns for the user's Id. if the user's id is in contactI then i'll display the value from the Relationship column; if the user's ID is on contact2 i'll display the value from the InverseRelationship column. This is the part where I need some help. I need to write a function that returns a value dependent on which column he value is from. Any help? P.S. I know there are other ways to implement this functionality, but I'd appreciate any help because writing this type of function can be useful in other ways. Thanks.
View Replies !
View Related
Avg Based On Column Values
Greetings, I need to avg column data based on grid textboxes. I cannot use AVG because the column values come from a comma delimited string. I have tried using !Parameters to store a count and total for each column but they are always read only. What is the best approach. The columns come from a field which is in the format of (99,75,60,100,-1,20,-1,80,75) for each record -1 means the values are not counted in the average. I have a function for each textbox that parses the value from the list based on the column index. There can be 1 to many columns. Everytime I to set the value of a field or parameter at runtime I get a read-only error. Maybee the only way is to use calculated fields. Any ideas.
View Replies !
View Related
Identity PK Column Based On Other PK...please Help
I have this table where there is tow primary keys ,one for Year and the other for DocNumber. In this table the DocNumber takes a number based on each year,for example in year 2008 the DocNumber should start counting from 1 and increment by one for every record,when the year changes to 2009 the DocNumber should start again from 1 until and seed by one again. Those columns are PKs because there must be one doc Number for each year and they can not be null . Is there any simple way to approach this in Sql Server 2005 ,or do I have to work on trigger to do so...??? Any ideas will be really appreciated. Eng.Osman Hamad http://arabic.aspose.com
View Replies !
View Related
Column Value Based On A Formula
I am trying to do the following. While inserting a record into a table I need one field to be created based on a stored procedure output.The stored procedure will create something like this, "XX-mm-yyyy-incremental number which resets to zero at the end of every month". All other field except the Identity field and the above mentioned storeproc generated field will be inputted manually. How can I do this? Thanks Hope my question is clear enough.
View Replies !
View Related
SP To Perform Query Based On Multiple Rows From Another Query's Result Set
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 Replies !
View Related
Check Constrain Based On Another Column
Hi All, I want to create a check constrain on a column based on the values in another column. Following is the sample data Col_A Col_B AA A AA B BB C BB D I want a check constraint on col_B such that it's value is A or B if Col_A has AA and likewise col_B has C or D if Col_A has BB. Can anybody tell me how to accomplish this. Thanks in Advance Rajesh
View Replies !
View Related
Matrix And Column And Row Based Averages Without Using Avg()
A A A A B B Avg. Name 1 2 3 4 1 2 xxxxxxxxxxx 12/16 90 100 85 10/8 100 -- xxxxxxxxxxx 20/16 93 5/8 100 -- xxxxxxxxxxx 16/16 89 90 16/8 95 -- Avg. -- -- -- -- -- -- -- Greetings, I have a matrix that looks similar to the table above with two row groups and one column group. Does anyone know a way to manually do column and row based averaging without using the avg() function -as the data is not numerical? Thanks
View Replies !
View Related
Select A Column Based On A Parameter
I need to select a column based on a parameter, something like this: SELECT column FROM table WHERE (column=@parameter) or just: SELECT @column FROM table The user should be able to decide which column of data to present by choosing this in a scroll down list. Is this possible and how do I do this? Sincerely Niklas
View Replies !
View Related
|
TRACKER
