I need to show the count that occurred during the previous 30min. I've been told the following query doesn't work.
SELECT COUNT(*) FROM sampledata
WHERE (id = 254 AND sub_id = 731) AND (sampledate >= DATEADD(mi, -30, GETDATE()))
Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
What would my statement look like if I have a column in a table (SoftwareInstall) called InstalledOn which stores a date in shortDateString format and I want to select all the records where that date is <= 30 days previous to today's date. Any ideas?
View 1 Replies View RelatedI have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.
;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Thanks in advance.
I have these columns. TicketID, User, Date...I want to select 10 random tickets for each user for the past 30 days. I not sure whether to do this via sql or using VB in the report design.
View 5 Replies View Related
How to pull null (empty) records from SQL database,
what query am I suppose to use to get result
help me
thank you
maxs
I have the following table:
SEQ DATE
123 JAN-01-2013
123 JAN-01-2013
124 FEB-28-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
I need a script to pull the above records ONLY if the date repeats more than one within the same SEQ number. The proper script would output as follows:
SEQ DATE
123 JAN-01-2013
123 JAN-01-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
I'm trying to pull all records from one table and just a single record from another. I have this join, (see below). It works ok, but the problem is if a blog record doesn't have a corresponding image record it doesn't return. The end result should be the blog record and a single corresponding image record. But always a blog record.
SELECT
[Blogs].[ID],
[Blogs].[BlogTitle],
[Blogs].[BlogType],
[Blogs].[BlogText],
[code]...
I have 3 tables.
Table 1:
ID Name Description
1 ABc xyz
2 ABC XYZ
Table 2:
RoleID Role
1 Admin
2 QA
Table 3:
ID RoleID Time
1 1 09:14
2 1 09:15
1 2 09:16
Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.
Hi Gurus,
I have a table having sales records and there are more than one record per one customer. The sales table has a reference number like below.
CustomerID
Sales_Ref
2
H_1123
2
H_2344
2
H_4322
I need to do a query and generate the following query.
CustomerID Ref
2 H_1123,H_2344,H_4322
Could someone help me on this.
Thanks.
Cheers,
Vijay
I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?
select id, list_date
from tableA
where list_date > DATEADD(year,-1,GETDATE())
I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.
What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).
I have tried 2 statements:
#1:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
GROUP BY tblTable1.Name, tblTable1.INDate
ORDER BY tblTable1.Name;
#2:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));
Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.
Example:
NameINOUT
John Smith1/1/20141/2/2014
John Smith1/1/2014(blank)
I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?
Hi all,
I have following problem:
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
{
engine.CreateDatabase();
}
serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";
using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(
Configuration.Default.SyncServerAddress, "", "", connStr))
{
rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");
}
Everythink works fine, when I use 'sa' user account in serverConnStr.
But, when I change conn string to:
"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Please help.
Thank you.
Fipil.
hi, this stored procedure below takes 25 mins to execute. is this normal? and is there a faster way to do this? its not like i have millions of records, i only have about 170.
Create Procedure sp_PayrollComplete
AS
declare @weDate nvarchar(10)
Select @weDate= we FROM cbetts.tblArchive
WHERE we = (select max(convert(datetime,we)) from cbetts.tblarchive)
declare @uid int
declare @job nvarchar(10)
declare @name nvarchar(30)
declare @Location nvarchar(30)
declare @Direct float
declare @LBO float
declare @g1099 float
declare @agency float
declare @nonbill float
declare @holiday float
declare @vacation float
declare @total float
delete cbetts.tblpayroll
Declare payroll_cursor CURSOR FOR
SELECT distinct userid FROM cbetts.v_PayrollTotals
where (we = @weDate)
ORDER BY userid
OPEN payroll_cursor
-- Perform the first fetch.
FETCH NEXT FROM payroll_cursor into @uid
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
select @location=location from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
select @name = name from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
select @Direct =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='direct'
and left(jobno,3) != '900'
select @LBO =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='LBO'
select @g1099 =
sum(totals)from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='1099'
select @agency =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='Agency'
select @nonbill=
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and left(jobno,3)='900'
select @holiday =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and (jobno='90010' or jobno='90011')
select @vacation =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and jobno='90020'
set @total = ISNULL(@direct,0) + ISNULL(@lbo,0) + ISNULL(@g1099,0) + ISNULL(@agency,0) + ISNULL(@nonbill,0) + ISNULL(@holiday,0) + ISNULL(@vacation,0)
set @nonbill = ISNULL(@nonbill,0) - (ISNULL(@holiday,0) + ISNULL(@vacation,0))
insert into cbetts.tblpayroll (Name,userid,location,direct,lbo,g1099,agency,nonbill,holiday,vacation)
values (@name,@uid,@location,ISNULL(@direct,0), ISNULL(@lbo,0), ISNULL(@g1099,0), ISNULL(@agency,0),ISNULL(@nonbill,0), ISNULL(@holiday,0), ISNULL(@vacation,0))
FETCH NEXT FROM payroll_cursor into @uid
END
CLOSE payroll_cursor
DEALLOCATE payroll_cursor
-- get rid of the 0 hour entries
delete cbetts.tblpayroll
where total = 0
GO
The following transaction runs, then reverts back after 10 mins.
begin Tran [UpdateSundays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-2,BusinessDate)
where datepart(weekday,BusinessDate) = 1
commit tran [UpdateSundays]
begin Tran [UpdateSaturdays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-1,BusinessDate)
where datepart(weekday,BusinessDate) = 7
commit tran [UpdateSaturdays]
Dear All,
Before i get crazy please help me out, i have creatd a linked server between sql 2005 and sql 2000.
The security of linked server is how,
that i have used a window authitcation accout like domainuser and i m using this account as impersonate and with the option that "be made with the login's current security context".
Now the problem is when i used the application it is working fine, but after if login the applciation with a break of 15 mins or half an hour, i get the error
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
I am unbale to undersatnd that why it so, y it fails after few mins, and if i check the particular Sp from backend it is working fine.
Moreover if a again create the link server with same setting as mentioned above it will work fine, but again after half an hour it will come up with same problem.
But if i create linked server with sql account that is available on both server using option "be made using this security context" every thing is working fine.
please help why problem in 1st case, and if my concept regarding linked server security is confusing do let me know any good link
Thnx l
How can i plan the execution of a package ?
For example, i want to execute the package every 2 minutes for all the time.
I do not want to use SQL Server Agent to do this. I want the package to self contained to do it.
Thanks
Rohit
Does any one have sample script which notify if "XYZ' job takes more than 5 mins to complete then needs to notify about the job status.
Dear friends, We have a server put at US side and we are trying to load the data into another related database in India site, now the thing is that we want to update India data at an interval of 5 mins?
Is there any way to do it without effecting the performance of the US server?
thanks,
This has been driving me crazy.
First noticed it when I went from JIT compiling of my .net app to pre-compiled via the command prompt, although I can't see that that's go anything to do with it...
My database is around 2gb and I'm using service broker.
If I drop a new version of my app into production, sqlserver.exe goes nuts and eats up the cpu.
Tonight, I stopped IIS and SQL Server, then dropped a new version in. I hadn't re-started IIS at this point... As soon as I started SQL Server, off it went again - eating up the CPU. I quickly launched a trace via the Profiler - no activity showing.
So, I started IIS after a couple of mins (still with the high cpu) and then just waited. After about 10 mins, SQL Server settled down and everything was normal again.
What is it doing when I start the SQL Server service?
Si
i have a time series table which generates a flag for every 10 mins. the table looks as below. I am trying to write a sql query which gives me the start date and end date of a particular flag, each time the flag starts. i tried to do it using cursors and actually joining the table to itself based on a 10 min offset but couldn't get the required results.
startdate enddate
flag
2009-11-02 23:30 2009-11-02 23:50
-999
2009-12-08 04:50 2009-12-08 04:50 -990
I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
Is there an option is SQL Server or SSMS to view the past few already executed queries.
I need to check what are all the queries fired on SQL server.
------------------------
I think, therefore I am - Rene Descartes
Hello all,
Is there a way for me to copy a row, do some modification in a certain row and insert it to the same table as a new row?
Forward with thousand thanks.
Best regards,
Tee Song Yann
Hello All
Our Collection team has a report that has an error that I'm tring to fix. The Person who created this report just hard coded the current month due as =MonthName(Month(Fields!CurrentDue.value) &" " &year((Fields!CurrentDue.Value).
For the Year 2007 it works fine. But now that the new year has started the report Current Month Due read Dec 2008, but it should be Dec 2007. I thought Of putting in some IIF statements but there has to be a easier way. Is there a function that check the past due and automatically increases the year by one. Say the bill in sent in Nov 2007 past due should be 30 days, Dec 2007. 60 Day should be Jan 2008 and so one.
Last Report Hours
=monthname(month(Fields!LastReportedHours.Value)) & " " & year(Fields!LastReportedHours.Value)
Days Past Due
=Fields!DaysPastDue.Value
Current Month Due
=monthname(month(Fields!CurrentDue.Value)) & " " & year(Fields!CurrentDue.Value)
Current Output
last reported Days Past due Current Month Due
September 2007 75 Days December 2008
Output should be!
last reported Days Past due Current Month Due
September 2007 75 Days December 2007
I want past two and next two quarters from the present quarterLike now its 4th quarter i should get 3rd and 2nd quarter and next two quarters that is 1st and 2nd also this is my table and when the year changes data is not fetched correctly
View 10 Replies View RelatedHow should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
View 5 Replies View RelatedIf I have and invoice date column and I want to now what invoices are 15 days past due or 30 days past due, how do I do this in a where clause?
WHERE tblInvoices.InvoiceDate ???
I have a database of the table payments
and I want to create another table with
the olds payments (18 months past)
a have a field with the payment date
date_pay.
How I can do that?
Thanks
Luiz Lucasi
Hi,
I have a date column and I am trying to build a query that will give me the dates > 48 hours ago (in the past).
For example using todays date 2006.10.05 and time 23:00 I would be looking for the dates prior to 2006.10.03 and time 23:00.
Your help is greatly appreciated!
Thanks!
How to find the jobs executed in the past for a perticular period.
e.g. jobs ran between 2-3pm in the past.
I am using sysjobhistory but not really able to use column run_time
------------------------
I think, therefore I am - Rene Descartes
All,
I am trying to design a graph to shows out build status for past 7-8 weeks. I have to write the SQL query first.
After spending a good chunk of time, I could get some basic chart going. Problem is that I could give it a starting time till now (static) but I am not sure how to get the results for past 7-8 wekks.
1) What is a good approach for this since time is not static and it is changing.
any example or piece of sql syntax is greately appreciated.
Thaks in advance,
aa-
Hi,
I have some set of variables that I want to add in all my packages. Like other components, is there any way to copy & paste the variables from one package to another package?
Thx.