I have a date/time value (dd/MM/yyyy HH:mm:ss) stored in a field as part of each record in a dataset.
I would like to achieve the following via a single T-SQL query if possible (efficiency is not an issue as the dataset is small)
On query run;
Check if any records in the dataset are within 10 minutes + or - of the current system time (using the date/time value referenced above)
if none
Load the first record
if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.
View 14 Replies View RelatedI need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.
Sample Table
ID DeviceID Temp InsertDate
1 10 55 04-28-2015 8:00 AM
2 65 74 04-28-2015 8:00 AM
3 44 23 04-28-2015 8:00 AM
4 10 87 04-28-2015 8:01 AM
5 65 65 04-28-2015 8:01 AM
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View 6 Replies View RelatedHello, I have a table called Member in my database that I use to store information about users including the date of birth for each person. I have a search function in my application that is supposed to look through the Member table and spit out a list of users with a user-inputted age range (min and max ages). Now, I could have stored ages instead of dob in the table, but I would think that's bad practice since age changes and would need continuous recomputing (which is db intensive) as opposed to dob which stays the same.
So what I'm thinking is getting the min and max user inputted ages, convert them to dob values (of type DateTime) in the application. And then, to query the db and return a list of all users in the Member whose dob falls in between those two dates (is a BETWEEN even possible with DateTime values?).
How is the best way to go about this? There are many sites out there that return users with user specified age ranges. Is there a best way to do this that's the least taxing on the db?
TIA.
hi all,
I have a table productprice which has the following feildsid price datecreated productname 1 12.00 13/05/2007 a1 2 23.00 14/05/2007 a13 24.00 15/05/2007 a14 56.00 13/05/2007 b15 34.00 18/05/2007 b16 23.00 21/05/2007 b17 11.00 12/02/2007 c1 8 78.00 12/03/2007 c2I
need to select the rows that are highlighted here.. ie the row that has
the max(datecreated) for all the productname in the table..
plz help thanks in advance..
Hi,In the datawarehouse DB (under MS commerce server 2002) a table storesthe referer domain name. Table structure is likerefererdomainid <binary>,domainInternalFlag<0/1>,refererDomainName<varchar>e.g.<binary>|0|unknown<binary>|1|google.com<binary>|1|yahoo.com<binary>|1|google.com<binary>|1|google.com<binary>|1|google.com<binary>|1|altavista.commy problem is to build a query (using this table only) whichrefererDomainName has the max occurrence and how many times. As in thetable above it is google.com and 4 times.Can anyone help me.Thanks in advance.
View 1 Replies View RelatedHi there, very sorry if this is the wrong forum to post this in.
I want to know what is the BEST practice, the correct Microsoft way of doing this:
basically, lets say I am inserting a new record into SQL. simple customer record:
firstname
lastname
address
city
postcode
password
dateOfRegistration (SQL has this value and the default value is the getdate())
That's all very well. I want to know how I can get the recordID for this and return that back from the caller (returning is easily done) -
You cannot really after this insertion, perform a SELECT statement to get the LAST record entered, as there maybe several records that could all be inserted at the same time by coincidence. It's not the best way of going about this.
I want to know what is the best way of getting the just inserted record's recordID - I was thinking about using date and time, manually inputting them and then using that to retrieve the last record/current inserting record but again its not the best way of going about doing this.
what is the best way?
Many thanks for your help!
The following select retrieves multiple reoords for each i.number. How can I select just the first record for each i.number?
SELECT i.number, i.desc, i.it_sdate, v.entry_date FROM itemsnum as I INNER JOIN Inventor as V ON SUBSTR(i.number,1,5)=v.catalog WHERE v.entry_date<ctod("04/01/06") AND i.it_sdate < ctod("04/01/06") order by number, it_sdate desc
Thanks in advance!
Hi
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks
I have some VB.NET code to retrieve data from an SQL Server database and display it. The code is as follows: -------------------------------------------------------------------------------------------------------
sw_calendar = calendarAdapter.GetEventByID(cid)
If sw_calendar.Rows.Count > 0 Then
lblStartDateText.Text = sw_calendar(0).eventStartDate
lblEndDateText.Text = sw_calendar(0).eventEndDate
lblTitleText.Text = sw_calendar(0).title
lblLocationText.Text = sw_calendar(0).location
lblDescriptionText.Text = sw_calendar(0).description
Else
lblStartDateText.Text = "*** Not Found ***"
lblEndDateText.Text = "*** Not Found ***"
lblTitleText.Text = "*** Not Found ***"
lblLocationText.Text = "*** Not Found ***"
lblDescriptionText.Text = "*** Not Found ***"
End If
-------------------------------------------------------------------------------------------------------
If all of the fields in the database has values, everything works ok. However, if the title, location or description fields have a null value, I receive the following error message:
Unable to cast object of type 'System.DBNull' to type 'System.String'.
I've tried a bunch of different things such as:
Adding ".ToString" to the database field,
Seeing if the value is null: If sw_calendar(0).description = system.DBnull.value...
...but either I get syntax errors in the code, or if the syntax is ok, I still get the above error message.
Can anyone help me with the code required to trap the null within the code example I've provided? I'm sure there are other, and better, ways to code this, but for now I'd really like to get it working as is, and then optimize the code once the application is working (...can you tell I have a tight deadline )
Thanks,
Brad
I am having trouble returning the correct record with my stored procedure.
my problem is that i don't know how to structure the sql statement to do the following:
given a set of records that have the same loankey, i need to
1. find the record that has most recent date (lockExprDt)
2. for all records with that date, find the highest Lock Number (LockNo)
3. for the all the records with that date and that LockNo, find the highest extension number (Ext)
currently my sql statement returns a record that has the most recent date. i don't now how to write the sql to further define my query to return the record that has the most recent date with highest lock number, and finally the highest extension number.
any suggestions as to what i am doing wrong. below is my slq statement. please note that i need to add the sql that will query for the max LockNo, and max Ext. Any help is greatly appreciated. thx!
select a.loankey, a.lockrate, a.investor, a.price, a.ext, a.cost, a.lockno, a.lockstatus , CASE WHEN CONVERT(CHAR(8),a.lockdate,10)='12:00:00 AM' THEN NULL ELSE CONVERT(CHAR(8),a.lockdate,10) END as 'LockDate' , CASE WHEN CONVERT(CHAR(8),b.lockExprDt,10)='12:00:00 AM' THEN NULL ELSE CONVERT(CHAR(8),b.lockExprDt,10) END as 'LockExprDt' , Case WHEN CONVERT(CHAR(8),b.lockExprDt,10)>= CONVERT(CHAR(8),GETDATE(),10) THEN datediff(day, CONVERT(CHAR(8),GETDATE(),10), CONVERT(CHAR(8),b.lockExprDt,10)) ELSE NULL END as 'Days'
from cfcdb..locktable ainner join (select loankey, max(lockExprDt) as lockExprDtfrom cfcdb..locktablegroup by loankey) bON a.loankey = b.loankey AND a.lockExprDt = b.lockExprDt
where a.loankey = @LoanKey
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
I HAVE THIS SQL STATEMENT WHICH GET THE MIN(AMOUNT) and MAX(AMOUNT) and DISPLAY IT IN A SINGLE ROW.
HOW CAN I GET THE RECORD BEFORE THE MAX (AMOUNT) INBETWEEN DATES IN CASE THERE ARE 4 RECORDS WITH SAME EMPLOYEENO IN THE DATE RANGE SPECIFICIED ? THIS IS SORTED BY EMPLOYEENO AND STARTDATE DESC
SELECT EmployeeNo, StartDate AS StartDate, MIN(Amount) AS oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND startdate >= '2001-06-01' AND startdate <= '2007-06-02') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN
(SELECT TOP 1 ([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND Benefitcode <> 'HON' AND startdate >= '2001-06-01' AND startdate <= '2007-06-02'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate DESC
THANK YOU SO MUCH ...
I am currently using this SQL code to capture some records over the last 2 months and it has been working great. I am now being asked if I can change this code with specifications:
1) Scan the records in the system until the count (*) as Volume reaches 30 because they prefer that as a denominator when figuring an average
2) Only run the scan for a maximum of 6 months.
So, there will most likely be some records that do not reach a volume number of 30 in this date range. In this instance we will just take the maximum volume number reached at 6 months.
So, how can I write this so it will build the file each time a record has reached the maximum of 30 and keep scanning back until we reach 6 months? If someone could lead me in the right direction on the proper order of the methodology in my code to accomplish these results it would be greatly appreciated. Desperate!
declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
select
@startdate = dateadd (mm, -2, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
select
@enddate = dateadd (mm, 2 , @startdate)
select distinct
pe1.patev_loc_id as LocID,
pp_cproc_id_r as ProcID,
count (*) as Volume,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time)) as Minutes,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time))/count(*) as AvgMin
from
risdb_rch08_stag..performed_procedure (index pp_serv_time_r_ndx),
risdb_rch04_stag..patient_event pe1,
risdb_rch04_stag..patient_event pe2
where
pp_service_time_r between @Startdate and @Enddate
and pp_asn_req_no = pe1.patev_asn_req_no
and pp_asn_seq_no = pe1.patev_asn_seq_no
and pp_status_v = 'CP'
and pp_rep_id > 0
and pe1.patev_event_code = 'PB'
and (pp_asn_req_no = pe2.patev_asn_req_no
and pp_asn_seq_no = pe2.patev_asn_seq_no
and pe2.patev_event_code = 'PL')
and datediff (mi, pe1.patev_event_time, pe2.patev_event_time) > 0
group by
pe1.patev_loc_id , pp_cproc_id_r
I want to create sql for retrieving 10 records per time but need to know the total result matches.
And I have previous and next button for retrieving previous or next 10 records.
Thanks
I have a fairly complex SP with 3 tables the idea being to display a Region, Client and EventI want to show only the last event for each client
I have replaced my event table with a View which returns the TOP 1 but all I get is the most recent of all records I want eg:Region 1, Client 1, Most recent event Region 1, Client 2, Most recent event Region 1, Client 3, Most recent event
Using MSSQL 2008 R2
Given the following table
Code:
{ID, PropClass, OffSet, Amount}
{1, 1, 1, .30}
{2, 1, 2, .45}
{3, 1, 3, .50}
{4, 2, 1, .26}
{5, 2, 2, .15}
If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?
For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3
This is a query that I am trying to work on:
Code:
SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*)
FROM UPValue v
INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account
INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired
WHERE v.Year = 2012 and LeaseType = 2
group by v.District, v.PropClass, YearAquired
order by v.District, v.PropClass
Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.
I have a situation where an agent has number of activities for a certain date range. If an agent has multiple activities within certain date range, I would like BALANCE BEFORE from the first activity and BALANCE AFTER from the last activity. Here is my current SQL query that returns the following data:
DECLARE @BeginDate Datetime
DECLARE @EndDate Datetime
Set @BeginDate = '05-1-2015'
Set @EndDate = '05-31-2015'
SELECT
a.AgentName,
R.BALANCEBEFORE,
[Code] ....
AGENTNAMEÂ Â Â Â Â Â Â Â Â BALANCE BEFOREÂ Â BALANCE AFTERÂ Â Â Â Â Â Â Â Â DATE
DOUGLASÂ Â Â Â Â Â Â Â Â Â Â Â Â 9738.75Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 9782.75Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2015-05-11
DOUGLASÂ Â Â Â Â Â Â Â Â Â Â Â Â 9782.75Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 9804.75Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2015-05-12
DOUGLASÂ Â Â Â Â Â Â Â Â Â Â Â Â 9804.75Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 9837.75Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2015-05-13
In the sample data above, ideally I would like my query to return data as follow:
AGENTNAMEÂ Â Â Â Â Â Â Â Â BALANCE BEFOREÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â BALANCE AFTER
DOUGLASÂ Â Â Â Â Â Â Â Â Â Â Â Â 9738.75 (from first activity)Â Â Â 9837.75 (from last activity)
Not sure how I can write sql query to accomplish this.
I need to create records based on date range on monthly basis, please help.
Here is an example:
ID Startdate EndDate
1 20070301 20070522
and I need to create the following data set based on start and end date range
ID Startdate EndDate
1 20070301 20070331
1 20070301 20070430
1 20070301 20070522
Hello all,
We have a domain where all computers are on GMT(Greenwitch Mean Time). We have an access front end that timestamps certain fields according to the client time that the program is running on, but we will be moving our client workstations off of GMT time and keep our SQL Server on GMT time, and want to keep the timestamps GMT.
So, I wanted to know if it was possible to create a stored procedure that gets the Server's time and returns it to the Access frontend for entry into the timestamp fields?
Or, if anyone has a better idea of how to get the time from the server to use on the clients, I would greatly appreciate it!!!
Kevin Kraus
Hi there
I am using SQL2005 and would like to check the whether the time is within the valid range for instance:
Data to check: 2:15AM
Range is between: 01:00AM till 06:59AM, 07:00AM till 02:00 PM
I can use the datepart for hh but that is not what I want. I want to have a full time: HH:MM cause sometimes if you do datepart by hour and if you apply into the above excample will be:
2 BETWEEN 1 AND 6 (this is ok)
2 BETWEEN 7 AND 2 (this is not ok)
Any idea?
I want insert automatically records for Range that user definded
For example user define :
from to city
10000 12000 tehran
15000 19000 babol
I should inserted 2000 record for tehran (10000,10001,....,12000) and 4000 record for babol (15000,15001,...,19000)
Hi,
My application needs to retrieve data from a table which has more than 15 lakh records. The records keep increasing in thousands every 15 days.
Is there anyway i can reduce the time to retrieve? basically i have a select statement with a few conditions and a clause for the id's of these records.
The TSQL below all works except the bolded part at the end. I'm want to grab only the most recently logged piece of equipment not the most recent and all past ones as well which is what I've got doing minus the bolded part below. But I don't know how to say get this Equipment ID etc and only the most recently logged one to find its present location. The bolded part below is just there to show what I want it to do I know you can use an aggregate in a where clause. So in the first table listed tblRdrLog there is a column Time that I want to do this on so a.Time. I don't want to display a.Time just reference.
String dbsql = " SELECT a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";
#region Advanced Search Criteria
// Check whether advanced search submitted
if (adv)
{
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}
#endregion
dbsql += " ) " +
" AND a.Time = max(a.Time) " +
"";
Hi,
I have built a Sales Forecast model to predict the sales
value.
Along with making historic predictions for previous time
periods I also want to retrieve the actual sales values for those periods.
How can I achieve this in a time series model?
I also would like to know how do mining models store the data.
Do they store the data in the same table/view format
as their respecive data source view or in the Model Content format.
Hello,I have a table that lists a number of available time slots for a party venue. I want a user to select a particular time slot and have the query show results for that time slot plus two time slots before and two time slots after the user's selection. If the user selects a time slot that is the first of the day, I still want that user to be shown 5 results including the one he selected. The same goes for the last time slot of the day. Can anyone help me with the SQL statement?
Thank you very much in advance!
Mark
Hi there,
With a DateStamp field
Im trying to group by Day Name
and also group it By Time ranges
i.e. Morning 00:00 to 11:59
Afternoon 12:00 to 17:59
Evening 18:00 to 23.59
I can do the group By Day Name ok
Like Below
Code:
SELECT COUNT(ISNULL(createdDate, 1)) AS DayCount, ISNULL(DATENAME(dw, createdDate), 'No Date Set') AS DayOftheWeek
FROM dbo.MyTable
GROUP BY ISNULL(DATENAME(dw, createdDate), 'No Date Set')
its just the grouping by time ranges im having trouble with..
Code:
M | T | W | Th | F | S | Su
Morning ||
Afternoon ||
Evening ||
Thanks for any help ..
I have a query to determine the time range like following
SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate()
What time range that query set ??
I want to create one complex query with Date time field,  my query is I want a grouping   between the time range
Â
EX:
Â
13/09/2007 1:00:00 PM    to  14/09/2007 4:00:00 AM    will be as Day1
14/09/2007 1:00:00 PM    to  15/09/2007 4:00:00 AM    Will be as Day2
''
''
''
This way the end date for the period will be 13/10/2007 4:00:00Â as Day 30
pavan
When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
Can anyone help me on this...
when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all..
Will it take this much time to retrieve data.?.
The table does include an indexed field,
So can anyone help me on the different things i can do to make the retrieval faster?
Andy
Hi everyone, this is the exception:
System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Here's my code!
... 'datagrid code
cmdSelect.CommandText = "SELECT * FROM [Table] WHERE [Date and Time] > '" & Me.tbFromDate.Text & "' AND [Date and Time] < '" & Me.tbToDate.Text & "'"
...
Me.tbFromDate.Text and Me.tbFromDate.text look like this: 27/11/2003 1:11:43 PM
The SQL Query works fine - just that this code gives a runtime error.
Can someone help?? I'm guessing I have to do somekind of conversion!!
Andrew