Help Needed Creating Select Statement
Hi,
I have a need to create a table detailing the ID of all contacts and the
last time they were contacted. This information is stored in 2 tables,
'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
in the 'activity' table).
I guess I need some sort if iteration to go through each contact and find
find the last activity that took place against each of them (there many be
more than 1 activity against each contact) and then place the output values
into the new table.
Can anyone show me how to go about this?
Thanks!
View Complete Forum Thread with Replies
Related Forum Messages:
Help Needed In Select Statement
Hi, I am trying to do a simple select statement which returns data from the table. The criteria is only if the count of the records is more than 10. Say, if a vendor called test exists 10 times in the table, I would like to display that vendor only, if less than 10, then that vendor won't be displayed at all. Here is what I have started. Select vendor_id, vendor_name, Invoice_Date, Invoice_Number from Vendors where Invoice_Date Between '01/01/2007' And '03/03/2008' Group By vendor_id, vendor_name, Invoice_Date, Invoice_Number Having Count(Invoice_Number) > 10 This doesn't seem to work. What am I doing wrong here?
View Replies !
Select Statement Help Needed
Hi, Table A member_no - character 5course1 - character 1course2 - character 1 Table Bcourse_no - character 1course_name - character 20I'd like a resultant view that contains A.member_no, A.course1, B.coursename as COURSE1NAME, A.course2, B.course_name as COURSE2NAMEI know how to make the join and pick up the name for course1 but I DON'T know how to pick up both names of course 1 and course 2 at the same time.I'm thinking I have to make a join on one column and then join the result of that a second time to pick up the other column's value? Thanks in advance for any assistance,Stewart
View Replies !
Sql Select Statement Help Needed.
I have worked on developing a project that I am pleased to say is comming along nicely considering I have only been doing programming for a few weeks but I am now stuck and may be going down the wrong path. I have two tables one that is used for logging via username and password, it also holds a third coloumn realting to the suers account that they do not enter. Once they have logged in I am recalling the users identity and running a query to establish what account the user belongs to. Later on in the script I am trying to run a query on a table called orders that list all orders belonging to that account that was established in the earlier query labelled as 'accountrequesting'. The script I have below returns all orders for all accounts even though I have tried a wide variety of endings on the Sql search on the orders table. The code may look a little untidy and to advanced programmers may look rough but I am still learning.....please can anyone help. sub Page_Load(sender as Object, e as EventArgs) 'collecting the user identity Dim user as string user=request("User.Identity.Name") 'running a query to establish what account they have access to all from users table Dim accountrequesting as string accountrequesting = "select account_id from users where username = " & user & "'" 'opening the data source and connection Dim ConnectionString As String = "Data Source=xxxx;Database=xxxx;User ID=xxxxxxx;Password=xxxxxxx" Dim Myconn as New SQLConnection(connectionstring) 'this is the line I am stuck on, the account id in this orders table needs to match against the value gained in accountrequesting above tried a lot of different endings Dim MySQL as string ="select prodshipped, account_id from orders" 'produces output Dim ds as Dataset=New Dataset() Dim Cmd as new SQLDataAdapter (MySQL, Myconn) Cmd.Fill (ds,"orders") MyDataGrid.datasource=ds.tables("orders").defaultview MyDataGrid.Databind() End Sub
View Replies !
SQL Select Statement Needed
I have a table consisting of two fields, OStype and OSversion, withentries like:OStypeOSversionsolaris 2.5solaris 2.6redhat 6.2redhat 6.2solaris 8redhat AS4solaris 10solaris 10redhat AS2.1redhat AS3redhat AS4I want to create a select statement that returns for each OS type, thetotal number of entries and for each version the total number ofentries.In the example the result would be:OStype OStype Count OSversion OSversion Countsolaris 52.5 12.6 18 110 2redhat 66.2 2AS2.1 1AS3 1AS4 2Thanks in advance for your help.Ian
View Replies !
Help Needed With Select Statement Using Calculation
Hi, Any help with this would be great. I have SQL tables all in the following format: ID_PRODUCT, ID_MARKET, ID_BUCKET, ID_COLLECTION, ID_MEASURE, MEASURE_VALUE Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g (326-229) + 278 = new measure_value. This is for each id-product where the following 3 conditions meet: the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great. thanks Jay p.s this what i have started with at the moment as a test however it dont work. SELECT ID_PRODUCT, ID_MARKET, ID_BUCKET, ID_COLLECTION, ID_MEASURE, MEASURE_VALUE FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT, dbo.DPOUT_EXCEPTIONS_326.ID_MARKET, dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET, dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION, dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE, dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE' FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)
View Replies !
Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings, I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered. How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column. ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name, A.index FROM ... ... ORDER BY A.[Index], A.Name ASC END ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index] END Thanks
View Replies !
Creating Table With A Select Statement
Dear folks, create table temptable(eno, ename) as select eno, ename from emp. here the problem is it is asking for the datatype for the temporary table. is it not possible to create the temp table without providing the datatypes? thank you very much. Vinod
View Replies !
Creating A Select Statement With Subqueries To 3 Other Tables...
I have four total tables.Table One (Documents)- List of Documents. Each record has two fieldsrelated to this issue. First field (Document_ID) is the ID of thedocument, second field is the ID of the record (Task_ID) it isassociated to in Table Two.Table Two (Activities)- List of activities. Each record has two fieldsrelated to this issue. First field (Activity_ID) is the ID of theactivity, the second field (Group_ID) is the ID of the record it isassociated to in Table Three.Table Three (Groups) - List of groups. Each record has two fieldsrelated to this issue. First field (Group_ID) is the ID of the group,the second field (Stage_ID) is the ID of the record it is associated toin Table four.Table Four (Stages)- List of Event Stages. Each record has two fieldsthat is related to this issue. The first field (Stage_ID) is the ID ofthe stage of an event, the second record is the ID number associated tothe event. This last ID is a known value.20000024 = the Event IDI'm trying to come up with a list of Documents from the first tablethat is associated to an Event in the Fourth table.Query Analyzer shows no errors within the script. It just doesn'treturn any data. I know that it should, if it does what I'm wanting itto do.SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROMStages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID= A.Activity_ID))))))= '20000024')
View Replies !
Help Creating A SELECT Statement For &"today&"
Hello,I am attempting to build a MS SQL query that will return data from"today"; today being current day 8:00AM-10:00PM today. My goal is toreturn the data from a table that is written to throughout the day, thequery will provide the current grade of service in our call center.I am having difficulty defining my where clause:- I can accomplish my goal my statically defining my 'date between' asthe actual date and time (not ideal)- I can accomplish the second part of my date using CURRENT_TIMESTAMP;but I am unable to define the starting pointHere is where I am thus far:/* We are going to count the total calls into each queue from start ofbusiness today (8:00AM) to now */select COUNT(Result) as "Total Sales Calls Offered" fromdbo.QueueEncounterwhere Direction='0'andQueueID='1631'and/* This is where I get lost */Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())Clearly the last line returns zero as there are no calls between thesame date range. How can I add to that line, or write this to work?Any thoughts?Thanks for the help.-Chris
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
Help Needed For Creating View
Hi Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus. Suppose I have five records item itemgroup status 1 1 complete 2 1 Xyz 3 2 complete 4 2 complete 5 2 complete my view should be itemgroup status 1 incomplete 2 complete All the Statuses are not predefined...they get added as and when required........ Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help. Thanks
View Replies !
Creating Relationships--Help Needed
I know the method where you select each and every table and manually create foreign key relationships to other tables in the database visually or by sql. Is there any way in SQL server 2005, to create these relationships using wizard, where it checks for the same column names in different tables and creates the relationships. If yes, please let me know how to do it in SQL server 2005.
View Replies !
Urgent Help Needed - Creating A Trigger?
I want a stored procedure to run everytime the batch Status field is set to 'Released' <Code> CREATE TRIGGER [CSITSS].[tdecker].[FB401BV_TRIGGER] ON [CSITSS].[dbo].[Fbbatch] FOR UPDATE [Status] AS DECLARE @RC int DECLARE @Batch int SET @Batch = (??? BATCH NUMBER THAT WAS SET TO RELEASE ???) EXEC @RC = [CSITSS].[tdecker].[GLP_FB401BV_BATCH] @Batch </Code>
View Replies !
Creating Queries Is Hard! Help Needed!
I am using a poker application that imports all played hands into a PostgreSQL database. I'm trying to write some queries for that database so I can make good use of the data. I figure PostgreSQL and SQL server probably have similar language. And I know these forums are great. So I'm posting here for help. This is a working code snippet that retreives three properties of a player: Code Snippet SELECT sum(totalhands) AS HANDS, sum(vpiphands) AS VPIPHANDS, sum(pfrhands) AS PFRHANDS FROM compiledplayerresults WHERE compiledplayerresults_id in ( SELECT compiledplayerresults_id FROM compiledresults WHERE play SELECT player_id FROM players WHERE playername='PLAYERNAME')); This is all I can do with my skills right now. However I very much want to learn to query for more things: - I'd like to be able to make a query for several players at the same time - I'd like to be able to get the aggregate properties for all players who are not PLAYERNAME1 or PLAYERNAME2 - I'd like to be able to get the aggregate vpiphands property from all players whose totalhandsis > 100 and < 1000. I hope you can help me with that. If you can, that will make me very happy and grateful! Thanks!
View Replies !
Hewlp Needed Creating A Stored Proceedure
Can someone please help me....I have created a DNN module that works on the test site but when I upload the module zip to a new site I get an error on creating my stored proceedure as follows: StartJob Begin Sql execution Info Executing 01.00.00.SqlDataProvider StartJob Start Sql execution: 01.00.00.SqlDataProvider file Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'. Line 51: Incorrect syntax near '@Str_Title'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC EndJob End Sql execution: 01.00.00.SqlDataProvider file The SP looks like this: /* ------------------------------------------------------------------------------------- / ListTAS_Journal / ------------------------------------------------------------------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal @PortalID int, @SortOrder tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND Text like COALESCE('%' + @Str_Text + '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR Text like COALESCE('%' + @Str_Text + '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO This SP works on the test site. Any help would be creatly apreciated Mark
View Replies !
Help Needed: For Creating Synchronous Transform Component
Hi I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system, then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that was read, else send the data down the unmacthed output which will be the same as the input. So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows load in memory. Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer, as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well. Thanks Steve
View Replies !
Help Needed : Not Able To See Fields Value When Creating A Report By Calling A Stored Procedure
Details : Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003 In Report Design view In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message. "The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope." Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM. If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered select * from tblRCM at Query string area, the report is running fine. Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server. In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab. Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server. I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue. Thanks.
View Replies !
SQL Statement Needed!!
Hi.. I need help with this SQL statement. I don't know if this can be done with a single SQL statement or not! I have two tables: tblProducts: ProductID, ProductName tblOrders: OrderID, ProductID I need to "SELECT tblProducts.ProductName FROM tblProducts WHERE (tblProducts.ProductID is not in tblOrders Table)". Thanks in advance...
View Replies !
Help Needed With This Case Statement..
Hi, In my database i have a column Called EntryDates which has 'Jan1' etc.. stored in it. and i am getting an Xml object and in the object i am getting @EntryDAtesMatch and @EntryDatesPS and they have different months stored in it. So i want to insert into Entrydates or set the Entrydates as to whats been passed in following scenarios If @Entrydates is Not Null then i want @EntryDates to be set as @EntryDates. Suppose @EntryDates is NULL But @EntryDatesMatch OR @entryDAtesPS are not null then i want @EntryDates to be = @EntryDatesMatch.. Suppose is @EntryDates @EntryDatesMatch and null i want @EntryDates to be set as @EntryDatesPS... This is what ihave tried doing.. but doesnt work as the way i want it.. SET @EntryDates = CASE --When @EntryDatesMatch = NULL Then @EntryDates When @EntryDates = NULL and @EntryDatesMatch = NULL Then @EntryDatesPS When @EntryDates = NULL and @EntryDatesPS = NULL then @EntryDatesMatch End Any help will be appreciated.. Regards, Karen
View Replies !
Help Needed With Insert Statement
Hi, I am trying to insert the follows rows to my production database... and this the sample data Row Plan PART_ID FUND_ID TOT_ACT1 TOT_ACT2 Number Num 1170925 129602759 19765P471 BB4928.47 CT0.00 DV26.30 GL153.75 TF0.00 WD0.00 OT0.00 EB5108.52 205.0110 24.04 206.0720 24.79 2170925 129602759 35472P406 BB2663.64 CT325.00 DV87.46 GL26.42 TF530.92 WD0.00 OT0.00 EB3633.44 189.0450 14.09 254.6210 14.27 3170925 129602759 LOAN BB1506.88 CT0.00 DV25.48 GL0.00 TF-530.92 WD0.00 OT0.00 EB1001.44 1506.88 1.00 1001.44 1.00 4170925 148603737 19765L587 BB25.14 CT0.00 DV0.46 GL-0.45 TF0.00 WD0.00 OT0.00 EB25.15 5.3830 4.67 5.4790 4.59 5170925 148603737 19765P471 BB7.48 CT0.00 DV0.05 GL0.23 TF0.00 WD0.00 OT0.00 EB7.76 0.3110 24.04 0.3130 24.79 6170925 148603737 35472P208 BB12.53 CT0.00 DV0.28 GL0.09 TF0.00 WD0.00 OT0.00 EB12.90 0.9360 13.39 0.9570 13.48 7170925 148603737 35472P604 BB7.48 CT0.00 DV0.24 GL0.15 TF0.00 WD0.00 OT0.00 EB7.87 0.4720 15.85 0.4870 16.16 8170925 148603737 315805549 BB29.72 CT0.00 DV0.00 GL2.15 TF0.00 WD0.00 OT0.00 EB31.87 1.5320 19.40 1.5320 20.80 9170925 148603737 197199102 BB5.00 CT0.00 DV0.06 GL0.27 TF0.00 WD0.00 OT0.00 EB5.33 0.1650 30.32 0.1670 31.94 So the number of rows in this table is 1007 right now my insert query inserts all the data but excepts LOAN and i want Loans inserted in a seperate column in my production dataabse but thats not happening so can some one pls take a look at this query and see whats wrong... My query is as follows1 INSERT INTO Statements..ParticipantPlanFundBalances1 2 ( 3 PlanId, 4 ParticipantId, 5 PeriodId, 6 FundId, 7 Loans, 8 --PortfolioId, 9 Act1, 10 TotAct1, 11 Act2, 12 TotAct2, 13 Act3, 14 TotAct3, 15 Act4, 16 TotAct4, 17 Act5, 18 TotAct5, 19 Act6, 20 TotAct6, 21 Act7, 22 TotAct7, 23 Act8, 24 TotAct8, 25 Act9, 26 TotAct9, 27 Act10, 28 TotAct10, 29 Act11, 30 TotAct11, 31 Act12, 32 TotAct12, 33 Act13, 34 TotAct13, 35 Act14, 36 TotAct14, 37 Act15, 38 TotAct15, 39 Act16, 40 TotAct16, 41 Act17, 42 TotAct17, 43 Act18, 44 TotAct18, 45 Act19, 46 TotAct19, 47 Act20, 48 TotAct20, 49 OpeningUnits, 50 OPricePerUnit, 51 ClosingUnits, 52 CPricePerUnit, 53 AllocationPercent 54 ) 55 SELECT 56 cp.PlanId, 57 p.ParticipantId, 58 @PeriodId, 59 Case When a.FUND_ID <> 'LOAN' Then f.FundId ELSE 0 END, 60 CASE When a.FUND_ID = 'LOAN' Then 'LOAN' END as Loanfunds, 61 --planinfo.PortfolioId, 62 Case WHEN a.ACT_ID1 = 'BB' Then 1 END, 63 a.TOT_ACT1, 64 Case WHEN a.ACT_ID2 = 'CT'Then 2 END, 65 a.TOT_ACT2, 66 CASE When a.ACT_ID3 = 'DV' then 3 END, 67 a.TOT_ACT3, 68 CASE When a.ACT_ID4 = 'GL' Then 4 End, 69 a.TOT_ACT4, 70 CAse When a.ACT_ID5 = 'TF' THEN 5 END, 71 a.TOT_ACT5, 72 CASE When a.ACT_ID6 = 'WD' THEN 6 END, 73 a.TOT_ACT6, 74 CASE WHEN a.ACT_ID7 = 'OT' THEN 7 END, 75 a.TOT_ACT7, 76 CASE When a.ACT_ID8 = 'EB' THEN 8 END, 77 a.TOT_ACT8, 78 a.ACT_ID9, 79 a.TOT_ACT9, 80 a.ACT_ID10, 81 a.TOT_ACT10, 82 a.ACT_ID11, 83 a.TOT_ACT11, 84 a.ACT_ID12, 85 a.TOT_ACT12, 86 a.ACT_ID13, 87 a.TOT_ACT13, 88 a.ACT_ID14, 89 a.TOT_ACT14, 90 a.ACT_ID15, 91 a.TOT_ACT15, 92 a.ACT_ID16, 93 a.TOT_ACT16, 94 a.ACT_ID17, 95 a.TOT_ACT17, 96 a.ACT_ID18, 97 a.TOT_ACT18, 98 a.ACT_ID19, 99 a.TOT_ACT19, 100 a.ACT_ID20, 101 a.TOT_ACT20, 102 a.UNIT_OP, 103 a.PRICE_OP, 104 a.UNIT_CL, 105 a.PRICE_CL, 106 IsNull(i.ALLOC_PER1,'0.00') 107 FROM 108 ASDBF a 109 110 --Derive the unique Plan Id 111 INNER JOIN Statements..ClientPlan cp 112 ONa.PLAN_NUM = cp.ClientPlanId 113 AND 114 cp.ClientId = @ClientId 115 --Derive the unique ParticipantId from the Participant table 116 INNER JOIN Statements..Participant p 117 ONa.PART_ID = p.PartId 118 -- Derive the unique fund id from the Fund Table 119 INNER JOIN Statements..Fund f 120 ONa.FUND_ID = f.Cusip 121 OR 122 a.FUND_ID = f.Ticker 123 OR 124 a.FUND_ID = f.ClientFundId 125 Left Outer JOIN INVSRC i 126 ONa.FUND_ID = i.INV_ID 127 AND 128 a.PLAN_NUM = i.Plan_Number 129 AND 130 a.PART_ID = i.PART_ID 131 --Get the unique portfolio name ffor the PArticipant Funds.. 132 WHERE 133 --Ignore rows that failed the scrub. 134 a.Import = 1 135 AND 136 --Import only those that are not already in the ParticipantPlanFundBalances table 137 NOT EXISTS ( 138 SELECT * 139 FROM 140 Statements..ParticipantPlanFundBalances1 pfb 141 WHERE 142 pfb.PlanId = cp.PlanId 143 AND 144 pfb.ParticipantId = p.ParticipantId 145 AND 146 pfb.PeriodId = @PeriodId 147 AND 148 pfb.FundId = f.FundId 149 ) any help is appreciated. Regards Karen
View Replies !
Help Needed With SQL UPDATE Statement
I am having a heck of a time getting an UPDATE statement to work. Can anyone point out what it is I'm doing wrong? Here is my statement...... strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @IDParam" Thanks! Eugh
View Replies !
Missing A Needed Using Statement But Not Sure Which One
I'm trying to get my feet wet with creating a Sql Server project within VS 2005 and I have the using statements below present but when I compile I get errors stating that SqlContext, SQLPipe, and SQLCommand do not exist in the current context. What am I missing? Under the references node I have 3 references present (System, System.Data, and System.XML). What am I missing? TIA using System; using System.Data; using System.Data.Sql; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
View Replies !
CASE Statement Help Needed
I want to Add in a Case Statement to my Stored Procedure to Identify 5 classifications: A: Birth to 6 yrs old B: Female 7 yrs to 18 yrs C: Male 7 yrs to 18 yrs D: Female over 18 yrs old E: Male over 18 yrs old So Im looking for something like this: CASE WHEN [Patient Age] >= 6 THEN 'A' WHEN [Patient Age] between ..... Any help is appreciated! Declare @ApptDate datetime Select @ApptDate = a.ApptStart FROM Appointments a WHERE a.AppointmentsId = @AppointmentsId SELECT '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''), '291.PatLast'=IsNull(pp.Last,''), '292.PatFirst'=IsNull(pp.First,''), '293.PatMiddle'=IsNull(pp.Middle,''), '294.PatientAddr1'=IsNull(pp.Address1,''), '295.PatientAddr2'=IsNull(pp.Address2,''), '296.PatientCity'=IsNull(pp.City,''), '297.PatientState'=IsNull(pp.State,''), '298.PatientZip'=IsNull(pp.Zip,''), '299.PatientCountry' = ISNULL(pp.Country,''), '300.PatientBirthdate' = pp.Birthdate, '301.PatientSex'=IsNull(pp.Sex,''), '302.PatientPhone1'=IsNull(pp.Phone1,''), '303.PatientSSN'=IsNull(pp.SSN,''), '304.PatOccupation'=IsNull(pp.EmpOccup,''), '305.PatSchool'=IsNull(pp.MedicalRecordNumber,''), '306.PatBudget'=IsNull(g.Budget,0), '307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0), '308.PatSuffix'=IsNull(pp.Suffix,''), '400.PatientAge' = CASE WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys' WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks' WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos' WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos' WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs' ELSE '' END FROM PatientProfile pp LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId WHERE pp.PatientProfileID = @PatientProfileId
View Replies !
Update Statement Help Needed
Below is my table structure: CREATE TABLE [reports].[MDC_DRG]( [InsDrgMdc] [nvarchar](50) NULL, [InsDrgMdcDesc] [nvarchar](50) NULL, [MDC] [nvarchar](100) NULL, [Drg] [nvarchar](50) NULL, [DrgDesc] [nvarchar](255) NULL, [AnyDx] [nvarchar](100) NULL, [Cases] [decimal](18, 0) NULL, [IntOOS] [int] NOT NULL, [ChronicOther] [nvarchar](50) NULL ) ON [PRIMARY] Here is a sample select from the AnyDx field: AnyDx ,85225,85225,5304 ,5181 ,80501,E8190,3485, ,1983 ,1983 ,49121,1625 ,34550,79381,78079,30001,V1, ,2396 ,2396 ,78039,311, ,33390,33390,31401,49390, ,3410,3410,2768,3051,72293,V146,V4579,V1041, ,43491,43491,25000,4019 ,V1581, ,43491,43491,25000,4919 ,3569 ,4019 ,72252,78650,71, ,43491,43491,2768,30590,34290,4019,V146,7845,2724,2, ,43491,43491,34290,40290,25000,7843,7872,41401,4278, ,43491,43491,412 ,25000,41401,4019 ,2724 ,29680,V1, I need to issue an update statement to the ChronicOther field. If the range below is in the AnyDx field, then I want the ChronicOther field to say "Chronic", if not then I want it to say "Other". where AnyDx between '25000' and '25091' or AnyDx between '41400' and '41406' or AnyDx between '42800' and '42890' or AnyDx between '40100' and '40291' or AnyDx between '34200' and '34292' or AnyDx between '14000' and '19910' or AnyDx between '49600' and '49600' or AnyDx between '27240' and '27240') My problem is what is the best way to look for this range (below) in the AnyDx field without multiple like statements? example: Update reports.MDC_DRG SET ChronicOther = CASE When AnyDx like ('%,25000,%') Then 'Chronic' Else 'Other' etc., etc.....
View Replies !
Case Statement Help Needed
Hi, here is what i am trying to do, when @call=2, then use table1.call else use table2.call and get syntax error no matter what I try, case @services=2 then table1.call else table2.call end
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
Help Needed Creating An RS Report Comparing 2 Time Periods Using An SSAS Data Source
I'm hoping someone can help me figure out how to implement this report, I've run into several limitations with using SSAS in SSRS and am not sure what to try next. I'm using SSRS 2005, with the data source being an SSAS 2005 cube. The issue is that I have 20-something parameters, most of which are multivalued (these represent most of the dimensions in the cube and essentially work in a way similar to cube browsing). In this single report, I need to compare data (with the parameter values applied) from two different time periods (defined by start and end dates entered via parameters). This data needs to be displayed in a table format in the report itself. I'm not finding a good way to make this work, due to the following constraints: I can't use built-in time functions such as ParallelPeriod, since the time periods are defined by the user who is entering start and end dates (month and year), so they are not necessarily parallel. Someone may be comparing 2 months this year with 12 months last year, or last quarter of this year, etc. If I pull the regular and prior period data in two RS datasets * I can't find a way to "match up" the values from the 2 datasets for comparison in the datatable. * Aside from the date parameters, the other parameters should be applied to both datasets. If I build this using the MDX Designer, it would create duplicate parameters. Now, I may be able to go in and in the parameters tab of the dataset after the fact and change them to the existing parameters, I haven't tried that yet. If I pull the regular and prior period data in the same dataset, I don't have the issue of "matching up" the data in the table, but: * I haven't been able to find a way to apply these start and end date parameters through the functions in the MDX query designer in order to end up with 2 different sub-sets - can someone help me with that? * I have been able to get close to the MDX that I need, just writing it in SSMS. One way that seemed to work better was to create 2 named sets, one for the current and the other for the prior period, and then use those in a tuple in selecting the measures I needed. When I create the named set, I'm using a date range based on a start and end month. When I apply the named set to the measure, it's coming out as one number per month for each month in that range, not one value per measure. I'd rather it not be split out by months as some of these measures are calculated measures and wouldn't have the same value if they're split up and then aggregated again. Also, if I don't use the MDX Query designer - if I edit the MDX directly - I can enter this MDX statement, however, SSRS won't accept this syntax when using the regular AS provider (.NET Framework Service Provider for Analysis Services). * I have seen blogs stating that you can get around some of the MDX syntax limitations above by using the OLEDB provider instead (OLEDB Provider for Analysis Services) and then entering the MDX as an expression (for instance http://joeblog.homnick.com/Lists/Posts/Post.aspx?ID=33). I did try that, and it didn't work for me because the OLEDB Provider doesn't support multivalued parameters. I have to use multivalued parameters, so I'm stuck with the .NET SSAS Provider. Finally, I wanted to mention that these reports will be accessed via a web application, which will independently contain the possible values for the paramters, the default values, etc. So it is not important that we keep the datasets that populate these parameter values in the report definition itself. Actually it would be helpful to get rid of these, because with 20+ parameters, Visual Studio is taking a long time to manage any changes in the data tab of the report. Any help you can give me would be greatly appreciated. Again, at this point, I'm looking for a way to: * Use the regular .NET SSAS Provider in the RS data source. * Query the cube to bring back one dataset with a few multivalued parameters applied, and to split the measures in this dataset between 2 time periods, defined by user parameters entered by the user. Or some other creative solution that will get me the same result in the report. Thanks.
View Replies !
(urgent) Help Needed For This Insert Statement
Hi, I am trying to insert the Source name and clientId to a table called clientSource... The User will send in some Dbf File.... So in a particular file called PlanDbf.. I have the following fields PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name.... SRC20Name 170234 Constructions Employee Deferral Employer Discretionary Employer Matching.... And in another table called SourceDBF i have the following fields with data PlanNumber PARTID SOURCE_NUM etc... 170234 123456789 1 170234 123456789 3 170234 451231334 1 So how do i match the Source_NUM with SRCnames when i insert it into the table.. INSERT INTO Statement..ClientSource(@ClientId, SourceName)SELECT s.SOURCE_NUM FROM SourceDBF .. but i am stuck.. any help will be appreciated. Regards Karen
View Replies !
Help Needed With Simple Case Statement In SQL
Hello, I am looking to modify this Case Statement. Where it says ELSE '' I need it to display the actual contents of the cell. 1 = Yes , 0 = No, (any other integer) = actual value. Right now if the value is anything other than 1 or 0, it will leave the cell blank. CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus Thank You.
View Replies !
CASE Statement Help Needed - SQL 2005
In my Database, I do not have a [Last Visit Date]. I have had to pull it by doing the following: (select top 1 visit from patientvisit pv where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date] My client would like to have a listing of patients with a visit within the past 2 years and without a visit in the past 2 years. What I would like to do is have a case statement that evaluates like: Case When dateadd(y,[LastVisitDate],getdate())<2 then 'Less Than 2' When dateadd(y,[LastVisitDate],getdate())>=2 then '2 or more' else 'No detected visit' END So basically, either your in 2 yrs or your not. My Current Query: /* Patient List*/ SET NOCOUNT ON DECLARE @Zip varchar(40) SELECT @Zip = LTRIM(RTRIM('NULL')) + '%'; WITH cteMedlitsPatientStatus AS ( SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus' ) SELECT PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0), PatientName=CASE WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) END, PatientAddr1=pp.Address1, PatientAddr2=pp.Address2, PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip, PatientRespName=CASE WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) END, PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City, PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'), Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate, Status = ml1.Description, pp.BirthDate, (select top 1 visit from patientvisit pv where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date] FROM PatientProfile pp LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId WHERE etc ......
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Syntax Check Needed On Case Statement
Hello experts! I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using.... Code Snippet WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime) >= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore) Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected. Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter. Any help is appreciated.
View Replies !
Select Help Needed
i m getting results like metrics mdate items total ------------------------------------------------ 001awer 2007-1-14 1 100.00 net100 2007-1-14 1 10.00 214sdf 2007-1-14 1 25.00 net16wrew 2007-2-14 2 80.00 so in short i want values of same items - total but different metrics like metrics mdate items total -------------------------------------------------------- 001awer 2007-1-14 1 135.00 net100 net100 net16wrew 2007-2-14 2 80.00 how should i get this?
View Replies !
Question-Advice Needed: Creating A System To Synch Handheld Entered Data With Main Database.
Greetings! I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database. I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
View Replies !
Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement: SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Replies !
Help Needed With Select Query
Hi all, I need some help please with an select query. here's so scenario:3x Tables linked: Vehicles, Requests & ResultsTable Vehicles - vId, make, model, priceTable Requests - rId, name lastname, requestedmake, requestedmodelTable Results - vId, rId I am trying to do a select on the Vehicles table where vId is not already also in the Results table for an given rId What happens on the app: 1. dealers add cars on website - populate Vehicles tbl. 2. clients do a request for a vehicle - Populates Requests tbl. 3. dealers go page where they can asossiate their vehicles with requests - which populates the results tbl with both id's.All help is very much appreciated. RegardsJacques
View Replies !
Help Needed With The Select Query
Hi, I have 2 tables. Plan and User. In my User table I have the following columns UserId ClientId Login Password PlatformId 1 1 abc XXX 1 4 1 jcn XXX 2 20 1 djfd XXX 1 and the Plan table PlanId PlanName 1 abc plan 2 Plan etc.. and ihave a sproc that will return the all the plans based on the clientId but i want to also get all the plans that have PlatformID 1 for the same client. This is my Sproc.. SELECT NULL PlanId, '< All Plans >' PlanName UNION SELECT Distinct PlanId, PlanName FROM Plan cp Join User u on cp.ClientId = u.ClientId WHERE u.PlatformId = 1 AND cp.ClientId = @ClientId OR ( cp.ClientId = cp.ClientId AND @ClientId IS NULL ) ORDER BY PlanName ASC But they dont filter it by PlatformId any help will be appreciated. Regards, Karen
View Replies !
Complicated SQL Select Help Needed
Hi, My users table contains a field called researchInterestId which looks like this: 1, 5, 10 This is because users where allows to select multiple options when choosing their research interests. I have another table which contains the names of those research interests, which looks like this: researchInterestId researchInterestName 1 Biology 2 Cancer My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10) Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called? Thanks Sam
View Replies !
SQL Select Help Needed - Dates
Hi everyone, I've got a problem that I've been unable to work out. I'm wanting to count the number of people that fall into a specific age group. For simplicity, lets say I'm JUST after people that are 10 years old. This is what I'm trying to do: SELECT 'Aged 10 years old', count(*) FROM [People] WHERE ([DOB] + [Date Entered]) = ?5 years? Does anyone have a solution to this? Thanks Andrew
View Replies !
Select Cast Help Needed
What is the correct syntax for querying the first 20 characters of amemo field?I'm trying toSELECT CAST (varchar(20) fieldname) as newfieldnameand i get a bunch of errorsCan someone please help me?MS Access DB used with ColdFusionThanks
View Replies !
Sub-Select Query Needed
Hey guys, Hoping someone can help me ot here.. I am developing an SQL statement that can be run on DB2 and have got something already setup, but i need to modify it so that it only returns the highest record for a certain field. let me explain/Show you what i have got, and what i want to acheive: The highlighted rows, is what i want to work with, as i only want to return the row with the highest value in ICPNLX (first Column) Im hoping someone can help me out please, one person told me to use a sub-select query, but i am having trouble doing it. SQL CODE: Select icpnlx , IBPUSL, IBPUST, IBSUNO, XBXSMT, ICPUNO, IBITNO,ICPNLI, ICPNLS, ICREPN, IBRVQA-IBSDQA as REMAIN, MBWHSL FROM mvxbdtaCF3.MPLIND INNER Join mvxbdtaCF3.MPLINE On ICCONO = IBCONO And ICFACI = IBFACI And ICWHLO = IBWHLO And ICPUNO = IBPUNO And ICPNLI = IBPNLI And ICPNLS = IBPNLS INNER Join mvxbdtaCF3.XANLIN On ICPUNO = XBPUNO And ICPNLI = XBPNLI And ICPNLS = XBPNLS And IBCONO = XBCONO And IBFACI = XBFACI INNER JOIN MVXBDTACF3.MITBAL ON ibitno = mbitno and ibcono = mbcono and ibwhlo = mbwhlo WHERE (IBCONO = 200) And (IBFACI = '010') And (IBWHLO = '010') And (XBXSMT = '91730') AND IBITNO = '8552-6654' AND (ICREPN > 0) And (IBPUSL >= '50' And IBPUSL < '75') RESULTS: ICPNLX IBPUSL IBPUST IBSUNO XBXSMT ICPUNO IBITNO ICPNLI ICPNLS ICREPN REMAIN MBWHSL 2 50 70 GRCUSA 91730 T05091 8552-6654 10 1 16978004 4 G-25-1 3 50 70 GRCUSA 91730 T05091 8552-6654 10 1 16978004 4 G-25-1 4 50 70 GRCUSA 91730 T05091 8552-6654 10 1 16978004 4 G-25-1 2 50 50 GRCUSA 91730 T50296 8552-6654 30 0 16978008 9 G-25-1 2 50 50 GRCUSA 91730 T50296 8552-6654 50 1 16978009 23 G-25-1 many many thanks guys. Scotty
View Replies !
Select Statement Problem - Group By Maybe Nested Select?
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View Replies !
Crazy SQL Statement Help Needed... Converting The Rows To Columns (sort Of)...
This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).Employees table:empNameJane DoeAlton BrownJohn DoeTrainingCourse table:courseNameWeldingBrain SurgeryScuba DivingResults table:empName: courseName: completeDate:Jane Doe Welding 2/2/2002Jane Doe Brain Surgery 3/7/2005Alton Brown Scuba Diving 9/23/2004Alton Brown Welding 11/4/2004John Doe Brain Surgery 6/14/2003End result of select statement: Welding Brain Surgery Scuba DivingJane Doe 2/2/2002 3/7/2005 Alton Brown 11/4/2004 9/23/2004John Doe 6/14/2003 Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.
View Replies !
Creating SQL Statement
Alright, so let me explain the details first.I have two tables. One is the default aspnet_users table that themembership class builds. that has GUID, username, lowereduser, and such.then I have another table called "UserSkills". That stores the GUID of the member, then the skills they have. so in that table i have. userID as GUID, then about 12 languages in 'bit' format.. (thats becuase in the webpage when they fill out there profile, all these are checkboxes. Basically all of the info is here http://www.listofcoders.com/profile.aspx?name=fenixsn. so there are a couple of bit fields, 1 text, and couple of varchars.anways, so i wanna build a powerful search thingy. where the users have the option to search a user that only does for ex say php, asp, asp.net. and is from location "Canada". ok so when they fill out the info, I want my SQL statement to do the following search the userskills table for the required fields. there might be more then 1 person that has the same profile, but different GUID. and then maybe using "Join" or another sql statement, grab there username, and last activity date from the users table that memberhship createes. so in short, how do i make a dynamic sql statement.
View Replies !
Creating A SQL Statement
Hello all - newbie post forthcoming.... I have a page that is writing to a database (Access) and I am having problems getting the actual SQL statement to execute properly. The code in question is as follows: sql = "INSERT INTO article (maincat, subcatid, subject, article)" sql = sql & " Values ('" sql = sql & quotes(Request.form("maincat")) & "', '" sql = sql & quotes(Request.form("subcatid")) & "', '" sql = sql & quotes(Request.form("subject")) & "', '" sql = sql & quotes(Request.form("article")) & "')" The response I get is.... "Data Type Mismatch in Criteria Expression" The post data does not have quotes around the data - I am thinking this is the problem. Any help would be appreciated!
View Replies !
SQL Select Statement To Select The Last Ten Records Posted
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
View Replies !
DTS And EXCEL Imports -- Using A Variable In The SELECT... Help Needed
Hi, here's my dilemma. I'm running a DTS package that takes an EXCEL spreadsheet and imports to a SQL database on a 1x per month basis. Manually running the DTS package works start to finish, but with a condition. The excel sheet is updated each month, and while the excel document's name stays the same, the name of the individual worksheet inside the xls file changes each month... this is causing the DTS package to break, since its looking for a SPECIFIC worksheet name that changes each month. Here is the select to give you an idea of what I'm doing... SELECT `PCPName`,`PCPAssignEffDate`,`CarrierMemberID`,`MemSSN`,`MemFirstName`,`MemMidName`, `MemLastName`,`MemSex`,`MemDOB`,`MemEnrollEffDate`,`MemEnrollTermDate`,`PaytoProvName`, `MemberAddress1`,`MemberAddress2`,`MemberCity`,`MemberState`,`MemberZip`,`MemberPhone`,`ProgramDesc`, `MemLanguage`,`CSHCN` FROM `'300905112123#rpt$'` In the select, the FROM criteria '300905112123#rpt$' is what needs to change... is there a way to designate a variable in that instance? I would need it to take ANY .rpt filename in the XLS file... but Im not sure if I can program a wildcard variable in the FROM statement that tells the DTS to grab ANY .rpt name within the "File.XLS" excel file. Is this something you can do? Ive never heard of wildcards in a FROM statement, but I dont know how else to do it so that we can automate this process... Hopefully this makes sense, the coffee has yet to kick in this morning =)
View Replies !
|
TRACKER
