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 Complete Forum Thread with Replies
Related Forum Messages:
Report Distribution Based On SQL Query In RDL
I need to create multiple reports with each report being sent to the specified user. For example, 3 Managers should each get their own report. A Stored Procedure receives a ManagerID parameter, so I need 3 instances of the report. I also need each report to be sent to its associated Manager: Mgr ID Manager Name 1 John Q Manager 2 Jane Q Manager 3 Jack O Lantern I set up a rss file to run the report and can run it multiple times, passing in a different ID value each time, but it will not be data driven because I must hard-code the parameters in the ReportParmaters object of the report for each execution. Is there a way, either by designing the report in VS2005 or on the Reporting Services server, to accomplish what I'm intending to do?
View Replies !
Using Prompt Parameter Into SSRS Report Based On Sql Query
Hi All, I have a question regarding how to use report prompts in SSRS reports that are based on SQL queries. When I added the prompt into the query for use as a filter value, it says that there is an error. It does not recognise the '!' inside the parameter prompt string. Example is parameter!month_prompt.Value, which the ! is not recognised. Your help is much appreciated. Thanks & Regards, Mohd Fadzli
View Replies !
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 !
How To Use Value Calcuated In Query In Subsequent Query, All Based On Value In Querystring?
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 Replies !
Filtered Report Parameters For Custom Reports
I am trying to build a report that allows a user to enter partial text that filters the values in a drop down For examle; Lets say I am selecting a value from a drop down for States and I want to type in FL to filter the collection down so that only Florida appears in the select list. I know the above example wont work since I cannot actaully enter text into a drop down, however I have also tried to seperate this functionality by creating one report parameter for entering the search text and another parameter that will display a filtered data set using the text entered in the first parameter. Is this something that reporting services can handle ? If it is please provide a brief example. Thanks
View Replies !
Best Way For A Set-based Query
I have a database where each row has a set of keywords separated bycommas eg .ROW 1 ID,<other columns>,"green,hat,man,boat"ROW 2 ID,<other columns>,"green,boat,seaside"ROW 3 ID,<other columns>,"red,hat"I want users to be able to find similar records to the one they arelooking it (this is art BTW). ie if they are looking at row 1 andclick "Find Similar Works of Art" I want to do a query where I findrecords that have a commonality of keywords and obviously the onesthat match the most keywords should be shown first. The keywords arecurrently just stored comma-separated in a single column but I am notattached to this. Nor are they guaranteed to come in any order (iethey are not alpha sorted) but they could be.Number of records is around 15000 on a hosted server.Any tips for best query/algorithm/data storage to achieve this? orreferences to useful sources?Thanks,RB
View Replies !
OLAP Datasource: Second Parameter List To Be Filtered Based On The First Parameter
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source? I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence. Can you do this thru SSRS? Any help would be great. Thanks for your time in advance. Kent
View Replies !
Insert Based On Query
Hi everyone - I have a simple query that will return a result set that has a unique value in it (primary key) - I would like to cycle through each of these values returned from the query, and create a new row in another table. I do not want to use a cursor - The first query is pretty simple.... select p_id from table1 where active=0 the query i need to execute for each of the p_id that is returned in the first example is.... insert into table2 (p_id_field, text_field) values (@p_id, 'text') the @p_id is filled in from the primary query... please advise thanks tony
View Replies !
Query Based On The Top MTD Sales
hello, I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales Sales Gr: Active Prod gr: Adult, Girls, Plus, LG Fabric Gr: 1,2,3,4,5,6,7,8,... Sales Gr: Dance Prod gr: Adult, Girls, Plus, LG Fabric Gr: 1,2,3,4,5,6,7,8,... Sales Gr: Yoga Prod gr: Adult, Girls, Plus Fabric Gr: 1,2,3,4,5,6,7,8,... Thank you
View Replies !
Changing The Query Based On Value
in the following procedure i want to pass in a value and as a result of the value I want to change the actual body of the query: example, I pass in the value 'TS2' and I now want my query to see if TS2MIN < 70 i think I am doing it right, but it is throwing an error saying that "Conversion failed when converting the varchar value 'TS2MIN' to data type int." any idea on what i am doing wrong? ALTER proc SPU_YearlyTemp 905,'TS2',70,254,2007 @SITEID INT, @SENSOR VARCHAR(10), @MIN INT, @MAX INT, @YEAR INT AS DECLARE @S AS VARCHAR(12) SET @S=( SELECT CASE @SENSOR WHEN 'TS1' THEN 'TS1MIN' WHEN 'TS2' THEN 'TS2MIN' WHEN 'TS3' THEN 'TS3MIN' WHEN 'TS4' THEN 'TS4MIN' WHEN 'TS5' THEN 'TS5MIN' WHEN 'TS6' THEN 'TS6MIN' WHEN 'TS7' THEN 'TS7MIN' WHEN 'TS8' THEN 'TS8MIN' END AS SENSORVALUE) select top(10)* into #tempMin from sitecontroldataarchive where siteid = @SITEID and @S < @MIN --this is where the error occurs, SELECT * FROM #tempMin
View Replies !
Max Query( Based On Two Table)
i've two table Rate and Topic Sample Table Table name==>Topic --------------------- Topic|TID| -----|---| hddgg|1 trgsd|2 vnvnv|3 sdfsd|4 dfsdf|5 dsdff|6 ........ ........ ----------------- above table TID is primary key next table Table Name==>Rate ------------------- RID|TID|Rate ---|---|-------- 1--|2--|4 2--|5--|3 3--|2--|3 4--|2--|2 5--|1--|1 6--|2--|1 7--|2--|2 8--|5--|3 9--|1--|4 ......... ........ above table RID is primary key and TID is foreign key.. i need Top 3 Topic but based on Maximum Rate of value any query.........
View Replies !
How Do I Set A Variable Value Based On A SQL Query
I have a SQL query that finds a value which I need to pass into an SSIS variable so I can use it later to set the filename for my data flow task. So far, I have been unsuccessful in determining how this is done. google searches, etc seem to be fruitless for me, there are so many hits but none really seem to address what I want to do because there are so many things you can do with variables. Likewise the books online and the boks I have on SSIS also only give a very high level of info. I need some examples, if anyone could help I would appreciate it. Here is the basic gist of my query: exec SP_GET_FILE_NAME @file_name OUTPUT I need to take the resulting @file_name and store it in an ssis variable. How?
View Replies !
Complicated Query - Select Based On Value
SQL2K on W2KserverI need some help revamping a rather complicated query. I've given thetable and existing query information below. (FYI, changing thedatabase structure is right out.)The current query lists addresses with two particular types('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check eachcontact for address type 'AM39DK3KD9' and then (2) if the contact hastype 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if thecontact does not have that type then select types ('MN30D843J2','SC93JDL39D'). (Context - the current query selects two standardaddress types "Main" and "Secondary"; we've added new data and now havetypes "Alternate Main" and "Alternate Secondary". If the Contact hasAlternate addresses, I need to select those; if not, I need to selectthe standard addresses. There are other address types in use, so Imust specify which types to select.)Can anyone point me in the right direction?Thanks very much! jamilehCREATE TABLE [CONTACTS] ([CONTACT_X] [char] (10),[LONGNAME] [char] (75),[ACTIVE] [bit])CREATE TABLE [CONTACTADDRESSES] ([CONTACT_X] [char] (10),[ADDRESS_X] [char] (10),[ADDRESSTYPE_REFX] [char] (10),[ACTIVE] [bit])CREATE TABLE [ADDRESSES] ([ADDRESS_X] [char] (10),[ADDRESSLINE1] [char] (60),[ADDRESSLINE2] [char] (60),[CITY] [char] (20),[STATE] [char] (2),[ZIPCODE] [char] (11),[PHONE] [char] (10))CREATE TABLE [REFERENCETABLE] ([REFERENCETABLE_X] [char] (10),[ADDRESS_X] [char] (10),[DESCRIPTION] [char] (60))CREATE TABLE [MASTERTABLE] ([CONTACT_X] [char] (10),[RECORDTYPE] [char] (1),[ACTIVE] [bit])CREATE VIEW vw_CONTACTInfo_ListLocASSELECT CONTACTS.CONTACT_X, CONTACTS.LONGNAME,CONTACTADDRESSES.ADDRESSTYPE_REFX,Type_REFERENCETABLE.DESCRIPTION AS Type_DESCRIPTION,CONTACTADDRESSES.ADDRESS_X, ADDRESSES.ADDRESSLINE1,ADDRESSES.ADDRESSLINE2, ADDRESSES.CITY, ADDRESSES.STATE,ADDRESSES.ZIPCODE, ADDRESSES.PHONEFROM CONTACTS INNER JOIN CONTACTADDRESSES ONCONTACTS.CONTACT_X = CONTACTADDRESSES.CONTACT_X INNER JOINADDRESSES ON CONTACTADDRESSES.ADDRESS_X =ADDRESSES.ADDRESS_XINNER JOIN REFERENCETABLE Type_REFERENCETABLE ONCONTACTADDRESSES.ADDRESSTYPE_REFX =Type_REFERENCETABLE.REFERENCETABLE_XWHERE (CONTACTS.ACTIVE = 1) AND (CONTACTADDRESSES.ADDRESSTYPE_REFXIN('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESSES.ACTIVE =1)) AND(CONTACTS.CONTACT_X IN(SELECT CONTACT_X FROM MASTERTABLE WHEREACTIVE = 1 AND RECORDTYPE = 'E'))
View Replies !
Insert Based On A Select Query
Hi,I have a SQL query:select products.name, products.notes, products.purchase_date,products.serial_no, products.total_value, products.product_code,products.quantity, products.product_group, products.lhs1_name,lhs1.department, lhs1.address1, lhs1.city, lhs1.countryfrom products, lhs1where products.lhs1_id = lhs1.idI want to insert the results of this query into a table called 'temp' in thedatabase. I used to copy and paste this into excel then import it but itdoesn't always work.Is there a way to do it all in a SQL script. Please be aware that myknowledge of SQL is fairly basic so please explain things clearly.Thanks,Darren
View Replies !
Run Query Based On Values In Another Table
Newbie transiting from VBA to TSQL, using SQL Server 2005 Enterprise:Need help to do this:Open Table_AWITH TableADO UNTIL .EOFRead value from TableA.ColumnARun SQL Statement on TableB based on valueMove to the next recordLOOPENDHow do I do this in TSQL?Thanks,Bubbles
View Replies !
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 !
Select Query Based On Datetime
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 Replies !
Insert Based On Query Values
Using SQL Server 7 w/SP4. I need to insert into another table the results of a query. Thought about using a temp table for the first query results, then querying those for the insert, but can't figure out how to pass the search results as a value. Example: SELECT ID, NAME FROM tbl2 WHERE ID BETWEEN 1 AND 50 IF @@ROWCOUNT > 0 BEGIN INSERT INTO tblAudit (ID, NAME) VALUES (@ID, @NAME) END Took a look at using SELECT INTO and INSERT INTO, but counldn't get those to work, either. Thanks for any help.
View Replies !
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 !
Changing The MDX Query Based On A I/P Parameter. Is This Possible?
Hi I am generating report with my datasource to an OLAP Cube. I have scenario that there are 2 dimension tables pointing to a single fact table. According to a user input, i have to use one dimension table and not the other and vice versa. I tried using IIF statement in the MDX query designer., but was facing errors. First of all i want to know if this is possible and if yes, how? Also , Is it possible to open a window form on clicking any report data (Similar to assigning hyperlink, but i want to open a window form instead!!!) regard Sai
View Replies !
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 Replies !
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 !
Query Parameter Based On Columnna,e...
good day dudes! here's my 2nd questione for ye'all... my databases: workshift - shiftid - shiftname - timestart - timeend - flexwindow - status employeeplottedsched - employeeid - month - year - day1shiftid - day2shiftid - day3shiftid ... ... ... - day29shiftid - day30shiftid - day31shiftid how can I query the plotted shift sched of employees for a particular day? like I would like to know what shift the employees were scheduled from day1 to 15 of May 2008? the days that I would like to query would be dynamic so it can be day1 to 15 or day3 to day5 or just day20... any of you guys nice enough to enlighten me? U + U + D + D + L + R + L + R + Sel + Start...
View Replies !
Query Based On Group By Clause
Hello! suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like DEPT ABSENT ----------------------------- Accounts 10 EDP Section 0 ** Admin 2 Stationary 0** if no employee is absent in the department it has to display Zero
View Replies !
How To Execute Update Query Based On Duration
Hi All, I have a table which consists the leave details of an employee. I have the columns like paid leaves,sick leaves,personal leaves in the above table Problem : For eg: An employee joined on 21 May 2008. After 6 months i.e., 21 Nov 2008 I need to update the above columns (i e., increase the no.of leaves) So updation is to be done for every 6 months and for every 1 year. Can anyone say me how to execute the update query based on the duration. Thanks in advance. Regards, Praveen
View Replies !
Query To Count # Records In Db Based On Foreign Key
My SQL is very basic. How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db? SELECT COUNT(*) AS Expr1, tblArticle.ArticleIDFROM tblArticle INNER JOIN tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.
View Replies !
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 !
Query Table Based On Multiple Keys
Hey,I am having some confusion about how to formulate this particularquery.I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with thecolumns a1,a2,a4 forming the primary key. Table B again has 3 columnswith b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.All columns are of the same datatype in both tables. Now I want to getrows from table A which are not present in table B. Whats the best wayof doing this?Thanks--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict235166.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725
View Replies !
Create Query Based On A Field That Won't Be The Same Value In Both Tables
I have two tables: TestA and TestB. Both tables have 3 fields: ID,Name, and RunDate. I need to create a query which will join the twotables first on Name but then I need to match up the RunDates eventhough the RunDates won't be the same.CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')Here's a common scenario:User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Thenthe user updates TestB data for Account 1, 15 minutes later. I wantthese two records to match. The user must always update TestA databefore they update TestB data. Therefore, there might be more rows inTestA then in TestBHere's what the results should look like for the above data.Name TestA Date TestB Date---- ---------- ----------Account 1 9/1/2004 12:00pm 9/1/2004 12:15PMAccount 1 9/2/2004 1:00pm 9/2/2004 1:15PMAccount 1 9/3/2004 3:00pm (NULL)Account 2 9/5/2004 4:00pm 9/5/2004 4:15PMAny help would be much appreciated!!!!
View Replies !
Set-based INSERT Query - Post Rewritten Hopefully Much More Clearly
I have a table with rows where the period value is 1 - these always already exist since a separate routine will have always done this population – DDL/DML below create table tblmain (period int, rfreq int, pfreq int) insert tblmain (period , rfreq , pfreq ) select 1 , 1 , 1 union all select 1 , 1 , 3 union all select 1 , 1 , 6 union all select 1 , 3 , 3 union all select 1 , 3 , 6 union all select 1 , 6 , 6 union all select 1 , 6 , 12 I need to do inserts into this table with incremented period values (the rfreq and pfreq columns to have same values as for Period 1) up to and including a Period number assigned in table, MaxPeriods (DDL/DML below) JOINing the two tables on the respective rfreq columns CREATE TABLE MaxPeriods (rfreq INT , MaxPeriod INT) insert MaxPeriods (rfreq , MaxPeriod) select 1 , 5 union all select 3 , 3 union all select 6 , 2 I could put together a loop solution but would much rather use a set-based approach but can't get my head around it The desired result is set out below ie those rows with period >= 2 select * from tblmain order by period, rfreq, pfreq period rfreq pfreq 1 1 1 1 1 3 1 1 6 1 3 3 1 3 6 1 6 6 1 6 12 -- all rows below to be produced by required INSERT code 2 1 1 2 1 3 2 1 6 2 3 3 2 3 6 2 6 6 2 6 12 -- no period 3 or higher for rfreq value 6 because table MaxPeriods holds a MaxPeriod value of 2 where MaxPeriods.rfreq = 6 3 1 1 3 1 3 3 1 6 3 3 3 3 3 6 -- no period 4 or higher for rfreq value 3 because table MaxPeriods holds a MaxPeriod value of 3 where MaxPeriods.rfreq = 3 4 1 1 4 1 3 4 1 6 5 1 1 5 1 3 5 1 6 So to summarise, I need to insert new rows into tblmain with incremented period values from 2 up to the value set in table MaxPeriods matching MaxPeriods.rfreq to tblmain.rfreq so using the data posted in MaxPeriods, I will INSERT rows from Period 2 to Period 5 for tblmain.rfreq value 1, Period 2 to Period 3 for tblmain.rfreq value 3 and just Period 2 for tblmain.rfreq value 6 Thanks in advance
View Replies !
Query Data Based On Moving Datetime.
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 Replies !
Declare Cursor Based On Dynamic Query
Hi, I am declaring the cursor based on a query which is generated dynamically. but it is not working Declare @tempSQL varchar(1000) --- This query will be generated based on my other conditon and will be stored in a variable set @tempsql = 'select * from orders' declare cursor test for @tempsql open test This code is not working. please suggest Nitin
View Replies !
Query 8 Days Ago Based On System Date
Hi, I'm trying to query an SQL table column with date values to show 8 Days ago results. I've started with this query: SELECT ficheiro, erro, descritivo_erro, contrato, DO, movimento, data, descritivo, tipo_movimento, desconto, montante, comissao, IVA FROM status_day WHERE (YEAR(data) = YEAR(GETDATE())) AND (MONTH(data) = MONTH(GETDATE())) AND (DAY(data) = DAY(GETDATE()) -8) ORDER BY descritivo_erro, contrato The problem is that the text in red will have some problems when the month changes - If I want the 8 days ago results from January and the system date is 1st of February the query will not return any values. I read something about DATESERIAL but is wasn't conclusive on how to use it with system date. Please help me out with this query.
View Replies !
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 !
Query String Field Based On Text Format
I need to search a nvarchar field based on the format of the text. This field holds values in two formats: 000 000 000 000 and 000000. I only want to search through the records that are in the 000 000 000 000 format. Can anyone give me direction on how to go about doing this or give me some key words to search for on Google? Fixing this problem is not an option. This is a county tax DB from a poor county with almost a million records in it. Thanks for the help!
View Replies !
Building Dynamic Query Based On Dropdownlist Contents
Thanks in advance for taking the tiemt o read this post: I am workingon an application in vb.net 2008 and I have 5 drop down lists on my page. I have code that worked in .net 2005 for my databind but would like to use new features in 08 to do this same thing. Here is my 05 code how would I do this same things in 08? Dim db As New DataIDataContext Dim GlobalSQLstr As String GlobalSQLstr = "select Orig_City, ecckt, typeflag, StrippedEcckt, CleanEcckt, ManualEcckt, Switch, Vendor, FP_ID, order_class, Line_type, id from goode2 where 1=1" If (ddlOrigCity.SelectedValue <> "") Then GlobalSQLstr &= "and Orig_City = '" & ddlOrigCity.SelectedValue & "'" End If If (ddlSwitch.SelectedValue <> "") Then GlobalSQLstr &= "and switch = '" & ddlSwitch.SelectedValue & "'" End If If (ddlType.SelectedValue <> "") Then GlobalSQLstr &= "and Order_Class = '" & ddlType.SelectedValue & "'" End If If (ddlFormatType.SelectedValue <> "9") Then GlobalSQLstr &= "and typeflag = '" & ddlFormatType.SelectedValue & "'" End If If (ddlVendor.SelectedValue <> "") Then GlobalSQLstr &= "and Vendor = '" & ddlVendor.SelectedValue & "'" End IfDim AllSearch = From A In db.GoodEcckts2s If (ddlErrorType.SelectedValue <> "0") Then GlobalSQLstr &= "and ErrorType = '" & ddlErrorType.SelectedValue & "'" End IfDim cmd As New SqlClient.SqlCommand Dim rdr As SqlClient.SqlDataReaderWith cmd.Connection = New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString) .CommandType = Data.CommandType.Text .CommandText = GlobalSQLstr .Connection.Open() rdr = .ExecuteReaderMe.gvResults.DataSource = rdrMe.gvResults.DataBind() .Connection.Close() .Dispose()End With
View Replies !
Management Studio - How To Index Tables Based On Query - Please Help
We are using SQL2005. I have a stored proc that runs a Select query based on a complex view. The sproc has two input date parameters (StartDate and EndDate). We are experiencing SQL timeout problems when the sproc is run with certain Start and End Dates. We have run the SQL Profiler and created a trace (trc) file (We've used the 'Default' trace configuration). We have used the trace file in SQL Server Management Studio to try and automatically create indexes on some of our tables. Unfortunatly SQL Server Management does not make any index recommendations. I think we are not capturing the right information in our trace file to allow SQL Server Management Studio to do its job. How do I use SQL Profiler to capture a trace of my sprocs query, so that it can be used by SQL Server Management Studio, to recommend index changes? Any help appreciated. Reagrds, Paul.
View Replies !
Query Which Calculates A Field Value Based On Previous Row's Values
I need to write a t-sql query that will take the value of the previousrecord into consideration before calculating the current row's newcolumn value...Here's the situation...I have a query which return the following table structure...Full_Name Points----------------- ------------Name1 855Name2 805Name3 800Name4 775Name5 775Name6 741etc.... etc...I need to create a calculated column that tells me where the personranks in point position. The problem i run into is that in thesituation where two or more people have the same point value i need thecalculated rank column to display the same rank number (i.e. 4th orjust "4") I'm not sure how to to take into consideration the previousrow's point value to determine if it is the same as the current onebeing evaluated. If i new they were the same i could assign the samerank value (i.e. 4th or just "4").If any one has any insight that would be great.ThanksJeremy
View Replies !
Query-based Insertion Or Updating Of BLOB Values Is Not
Hi! When I try to export the Data from SQL Server to Postgres(8.0) through DTS, I get an error message on the BLOB data which says "Query-based insertion or updating of BLOB values is not supported." Actually the table is having image data type. But while import from Postgres to SQL Server it succeeds. I will be very grateful if someone helps to export image data type tables from SQL Server using DTS to Postgres. Regards, Mohan
View Replies !
|
TRACKER
