First, I have that code whenever i disregard the "having ranges<=3" it selects record but when I include it it returns error. The problem is I want to just select records with ranges less than 3 miles..how do I do that.. and I want to count the number of selected rows and store it on a column on the same table.
This is my SQL : Select p.patientid,p.patientname,p.patientIc,pvi.DateOfAdmission,pvi.visitid,pvi.ward,pvi.bedno,pf.status,pvi.SurgeonName,(f.Title + ' ( Ver ' + (CAST(f.Version as Char(10))) + ')') as Title FROM patient p, patientvarianceinfo pvi,patientForm pf,Form f where (p.PatientName LIKE '%" & Name & "%' or p.PatientIc LIKE '%" & ic & "%' or pvi.Ward LIKE '%" & ward & "%' or pvi.Bedno LIKE '%" & bed & "%') and (p.patientid = pvi.patientid) and (p.patientid = pf.patientid) and (pvi.patientid = pf.patientid) and (pf.FormID = f.FormID)and p.patientid in (select patientid from patientform pf)how do i get the number of rows?
hi can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.
Example table structure: Id int, PK Name varchar AddDate smalldatetime
Sample data: Id Name AddDate 1 John 01/15/2005 2 Jane 01/18/2005 . . . 101 Jack 01/10/2006 102 Mary 02/20/2006
First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here?
select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr from dbo.sampletable group by datename(mm, AddDate), datepart(yy, AddDate) order by count(id) desc
Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?
how count column in pivot table- and add result row i need to calculate each column for example day1 day2 day3 day4 day5 ------------------------------------------------------------------------- 1 2 1 2 3 1 2 3 2 2 2 3 2 1 2 2 3 0 0 0 -----------------------------------------------------------new result row ok ok 1|2|3 1 3
i need to check each column if i have twice each number if not show the missing number TNX
Code Block DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT) DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP @BaseDate SMALLDATETIME, @NumDays TINYINT SELECT @WantedDate = '20080301', -- User supplied parameter value @BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'), @NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @Numdays > 28 BEGIN SELECT p.ID, p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11], p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21], p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31] FROM ( SELECT ID, DATEPART(DAY, Date) AS theDay, ShiftID FROM v_Employee WHERE Date >= @BaseDate AND Date < DATEADD(MONTH, 1, @BaseDate) ) AS y PIVOT ( min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]) ) AS p END
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0 IF (@_Stat_Count >0) BEGIN SELECT @Status = status_cd from status-table where status_id = 10 END
I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
I try to set up a testing sample table which contain one integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it? thanks!
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
I have a transformation where final result set give me 25 rows of data. Now before I put into destination table, I need to add another column which will show how many total records we have. Like.
My dataset:
A 20 abc B 24 mnp c 44 apq
Now I need to add another column within my transformation before I store the result set to destination like this:
A 20 abc 3 b 24 mnp 3 c 44 apq 3
Here. new column gives count of total rows in our dataset which was 3.
How can I achieve this? Can I use derive column to this?
I've been trying to reinitialize the membership database, but something stops me. I have tried deleting aspnetdb.mdf from the App_Data folder. This works fine, and then a new one is created when I use the asp.net adminstration tool. I can use the server explorer to view the table content (all null). Although I can open the asp.net configuration page, the problem occurs when I select security. Then I get the error There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: Database 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727asp.netwebadminfilesApp_DataASPNETDB.MDF' already exists. Could not attach file 'C:Documents and SettingsJeff LeeseMy Documentsworldmy workcurrentcounsellorApp_DataASPNETDB.MDF' as database 'ASPNETDB_devjeffleese'. All this seems to have happened after I modified my connection info in the web.config file: <connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;
 AttachDbFilename=|DataDirectory|ASPNETDB.MDF;
 user instance=true;
 Integrated Security=True;
 Initial Catalog=ASPNETDB_devjeffleese;" providerName="System.Data.SqlClient" /> </connectionStrings> So, two questions: 1) What are the 
 sections about? They seem to have been inserted automatically at some point, and I wonder if its just formatting info or should I remove them? 2) How can I get security going again. How do I deal with the 'already exists' problem that prevents 'attaching' ? Would greatly appreciate guidance in how to start over...
Hi There This is probably realy simple but since im still a newbie some help would be appreciated. I have a listbox bound to an sqldatasource which has names of people from my sql database employeeDetails table. I simply want to allow someone to select to select multiple names and insert them into another field in my database. I have this bit workng, ie they can select multiple people on the list, it does insert, however only the first selected name, not the others.Can you please help me out Kind Regads Dan
I am working on a report where some customization is need to be delivered.. situation is , i have some parameter in report @USER_ID , @Report_Type where i am proving selection to user to select Report Type (Pending Or Completed) and passing USER_ID auto matically from URL string of user login(C# code).
I have another parameter @USER_IDS which is multiple selection for user and it will be filled with the users which lie under passed @USER_ID means i just need to add dataset with the query to select users from mapper table where reporting_head =@USER_ID, simple, but i have requirement to populate the underlined users with the selection of @USER_ID and @Report_Type and it need some TSQL code to populate so i am using Another store procedure and using same parameter as my main store procedure has .
Now i am using dataset with this store procedure to fill my @USER_IDS parameter
Both parameters value will be passed from main report parameters now , when i am previewing a report i am getting error
and i also tried to write exec statement in dataset query with the main repport paramters but exec is not supported ..
Hi every body, I am making a program which is currently dealing with thousands of records. What I want is to have a button in the front END which make me able to fetch only first 100 record.If the desired record doesn't come with in first 100 record I press this button another time to fetch next 100 records i.e. from 101 to 199. How can it be possible in SQL Server stored procedure.
Eagerly waiting from all of you GENIUS people. Kailash
Recently I had an application developer approach me and asked if I could provided him with a list of sprocs by returned row count. We had an issue where the application passed in a number of parameters which attempted to return 200k plus rows of data and the application was timing out. He changed the required parameters in the application and a reasonable number of rows were returned as expected. Short term solution to this one problem.
However there are always timeout issues with this particular application and we got to thinking that maybe other sprocs that were called using parameters would also fail at some point in time because too much data was being returned.
I have a stored procedure that I give some parameters and it joins a couple of tables and returns the results. I would like to know how to update the records in one of the original tables before returning results. CREATE Procedure dbo.DS_GetSomething(@someValue char(5)AS Select table1.* ,table2.* FROM table1 left inner join table1.itemid on table2.itemid where table2.someValue = @someValue The table has a field of IMPRESSIONS that I would like to do something like: ;UPDATE table1 set impressions=impressions + 1 where recordid=?? I don't know how to access the recordids I just got from the original select statement. Thanks for any help Greg
CREATE TABLE [dbo].[tblSubscription]( [SubscriptionID] [int] IDENTITY(1000000,1) NOT NULL, [SubscriberID] [int] NOT NULL, [Status] [int] NOT NULL, [JournalID] [int] NOT NULL,
CREATE TABLE [dbo].[tblTransaction]( [TransactionID] [bigint] IDENTITY(100000000,1) NOT NULL, [TransactionTypeID] [int] NOT NULL, [SubscriptionID] [int] NOT NULL, [Created] [datetime] NOT NULL,
CREATE TABLE [dbo].[tblMailing]( [MialingID] [bigint] IDENTITY(1000000000,1) NOT NULL, [SubscriptionID] [int] NOT NULL, [MailTypeID] [int] NOT NULL, [MailDate] [datetime] NOT NULL
So for each subscription there can be 1 or more transactions and 0 or more mailings, and the mailings are not necassarily related to the transactions. What I am having difficulty doing is this:
I wish to select tblMailing.MailingID, tblMailing.MailDate, tblMailing.SubscriptionID (or tblSubscription.SubscriptionID), tblSubscription.SubscriberID, tblSubscription.Status, tblTransaction.TransactionID, tblTransaction.Created, but I only wish to retrieve rows from the transaction table where tblTransaction.Created is the latest dated transaction for that subscription. I.E. (maybe this makes more sense..:) I wish to select all rows from tblMailing along with each mailing's relevent subscription details, including details of the LATEST TRANSACTION for each of those subscriptions.
I am currently working along the lines of MAX(tblTransaction.Created) and possibly GROUP BY in a subquery, but cannot quite figure out the logic.
I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement. Many Thanks Delli
I have a store procedure that works fine when tested in SQL Management Studio and Visual Studio but when I actually running the page in a browser, it does not work. There is no result generated. Below is my store procedure.1 ALTER PROCEDURE [dbo].[spSearch] 2 -- Add the parameters for the stored procedure here 3 @schoolID int = NULL, 4 @scholarship varchar(250) = NULL, 5 @major varchar(250) = NULL, 6 @requirement varchar(250) = NULL 7 --@debug bit = 0 8 AS 9 BEGIN 10 -- SET NOCOUNT ON added to prevent extra result sets from 11 -- interfering with SELECT statements. 12 SET NOCOUNT ON; 13 14 -- Insert statements for procedure here 15 16 Declare @SQL as NVarchar(4000); 17 Declare @Params as NVarchar(3000); 18 Set @SQL = N'SELECT * FROM [scholarship] WHERE [sectionID] = @schoolID'; 19 Set @Params = N'@schoolID int,@scholarship varchar(250),@major varchar(250),@requirement varchar(250)' 20 21 If @scholarship IS NOT NULL 22 Set @SQL = @SQL + N' AND [scholarship].[schlrName] LIKE + ''%'' + @scholarship + ''%''' 23 If @major IS NOT NULL 24 Set @SQL = @SQL + N' AND [scholarship].[Specification] LIKE + ''%'' + @major + ''%''' 25 If @requirement IS NOT NULL 26 Set @SQL = @SQL + N' AND ([scholarship].[reqr1] LIKE + ''%'' + @requirement + ''%''' 27 If @requirement IS NOT NULL 28 Set @SQL = @SQL + N' OR [scholarship].[reqr2] LIKE + ''%'' + @requirement + ''%''' 29 If @requirement IS NOT NULL 30 Set @SQL = @SQL + N' OR [scholarship].[reqr3] LIKE + ''%'' + @requirement + ''%''' 31 If @requirement IS NOT NULL 32 Set @SQL = @SQL + N' OR [scholarship].[reqr4] LIKE + ''%'' + @requirement + ''%''' 33 If @requirement IS NOT NULL 34 Set @SQL = @SQL + N' OR [scholarship].[reqr5] LIKE + ''%'' + @requirement + ''%'')' 35 --If @debug = 1 36 --PRINT @SQL 37 Exec sp_executesql @SQL, @Params, @schoolID, @scholarship, @major, @requirement 38 END 39
Hi guys. I have been struggling for days now to store the result of a stored procedure from a linkedserver. To make a long story short, here is my code...
CREATE PROCEDURE F_GET_KRONOS_HRS @wono varchar(12) AS BEGIN declare @str nvarchar(2000) declare @a varchar(10)
create table #t (paycode varchar(7), hrs varchar(255)) insert into #t exec sp_executesql @str select @a = hrs from #t where paycode='ST' drop table #t print @a
if i call exec sp_execute @str, it will output this PAYCODE HRS ------- -------- ST 08: 30 OT 54: 00
(2 row(s) affected)
I want those #'s store into a temp variable OR be passed to another procedure. HOW DO I DO IT. This is the last step holding me back from completing my DataWareHouse.
