Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.
Hello In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.private DateTime _siparis;public DateTime Siparis { get { return _siparis; }set { _siparis = value; } }protected void Button1_Click(object sender, EventArgs e) {Timer1.Enabled = true; DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);Session["siparis"] = sonTarih; TimeSpan aradakiFark = sonTarih - DateTime.Now;StringBuilder sb = new StringBuilder(); sb.Append("Remaining time is ");if (aradakiFark.Days > 0) { sb.Append(aradakiFark.Days.ToString());sb.Append(" days "); }if (aradakiFark.Hours > 0) { sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours "); }if (aradakiFark.Minutes > 0) { sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes "); } lblSure.Text = sb.ToString(); }protected void Timer1_Tick(object sender, EventArgs e) {DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13); TimeSpan aradakiFark = (DateTime)Session["siparis"] - DateTime.Now;StringBuilder sb = new StringBuilder(); sb.Append("Remaining time is ");if (aradakiFark.Days > 0) { sb.Append(aradakiFark.Days.ToString());sb.Append(" days "); }if (aradakiFark.Hours > 0) { sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours"); }if (aradakiFark.Minutes > 0) { sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes "); } lblSure.Text = sb.ToString(); } }
I've tried a select query that uses getdate and returns me to the manually arranged holidays in the Holidays table, but I've failed. I wish to do that, this query arranges holidays and adds +1 day on that holiday, if this one day added day is a holiday again, than adds one day again. How can I write this code? Thanks.
Hi!I have a large table in SQL Server 2000 with a datetime-column 'dt'. I wantto select all rows from that table, excluding days which fall on holidays orweekends. What is the best way to accomplish this? I considered creating anew table called "holidays" and then selecting all rows (sort of "where notin (select * from holidays)") , but I was looking for a better solutionsince that implies that I have to populate the "holidays" table.Suggestions are welcome!Sincerely,Nils Magnus Englund
In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.
First things first - I have a table which holds employee information (tbl_EmployeeDetails), and another table which holds information about the holidays they have booked (tbl_Holidays).
If an employee books 5 days off, 5 rows will appear in the tbl_Holidays table, each line showing 1 under the day field and 7 under the hours field.
I'm trying to produce a function which will do the following :
1) Pass in the employee number 2) Establish whether the employee works full time or part time by looking up to tbl_employeedetails, and checking the fulltime flag 3) If full time, look up to tbl_Holidays and count number of days 4) If part time, look up to tbl_Holidays and count number of hours 5) After this, return the number of holidays booked
My code is as follows : ============= CREATE FUNCTION [dbo].[fn_Get_Booked_Holidays_Current_Year] ( @EmpNo int )
RETURNS Float AS BEGIN
-- Declare fields DECLARE @FullTime int
-- Determine if Part Time or Full Time SET @FullTime = SELECT FullTime FROM tbl_EmployeeDetails
IF @FullTime = 1 SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef FROM dbo.tbl_Holidays GROUP BY EmployeeNumber HAVING (EmployeeNumber = @EmpNo)
IF @FullTime = 0 SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef FROM dbo.tbl_Holidays GROUP BY EmployeeNumber HAVING (EmployeeNumber = @EmpNo)
END ==========
Can someone please let me know where I'm going wrong, or what I need to do to get this function done?
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select -- count returns unit data Count(substring(m.PTNumber,3,3)) as PTCnt, -- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt, -- attempting to calculate the percent by PTCnt/TotalCnt returns 0 (Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct -- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
Hi everyone, I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it. I would like do this in a stored proc but I could use C# if necessary. Thanks in advance, Jessica
I inherited a report that counts patient visits per month. Holidays are hard coded into the query. What is the best way to handle holidays without hardcoding?
Is there an equivalent function in SQL to the'NETWORKDAYS(start_date,end_date,holidays)' function in Excel ?NetworkDays in Excel returns the number of whole working days betweenstart_date and end_date. Working days exclude weekends and any datesidentified in holidays. This part isn't vital but would be nice tohave. The weekend stuff is more important.I realise this will most likely need to be some method I write myself(possibly based on the name of the day - Mon-Fri), but any pointerswould be appreciated if anyone has done this before.ThanksRyan
I use the NETWORKDAYS(start_date, end_date,holidays) function in Excel and Access regularly. I'm trying to find a similar function in TSQL to use on Server2005. NetWorkDays allows me to calculate the difference between my ticket open and close dates while excluding weekends and holidays, (we are nice to our staff and let them off).
I'm trying to find an easy way to do this in TSQL without writing a large amount of code.
The below code works fine to measure the difference in days between two dates. However, there is an additional business requirement to subtract week-ends, and holidays, from the equation. Any ideas on how to accomplish this task, and leverage the below, existing code? Thanks in advance! (SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "
Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.
So while looking at Bretts blog and another post on here, i thought i'd post this on here http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698
Special thanks to rockmoose & BOL (as always)
Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend
SET DATEFIRST 1 SET NOCOUNT ON GO
--Create ISO week Function (thanks BOL) CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int AS BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') --Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 --Special case: Dec 29-31 may belong to the next year IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END GO --END ISOweek
--CREATE Easter algorithm function --Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689) CREATE FUNCTION fnDLA_GetEasterdate(@year INT) RETURNS CHAR (8) AS BEGIN -- Easter date algorithm of Delambre DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT, @H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT
SET @A = @YEAR%19 SET @B = @YEAR / 100 SET @C = @YEAR%100 SET @D = @B / 4 SET @E = @B%4 SET @F = (@B + 8) / 25 SET @G = (@B - @F + 1) / 3 SET @H = ( 19 * @A + @B - @D - @G + 15)%30 SET @I = @C / 4 SET @K = @C%4 SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7 SET @M = (@A + 11 * @H + 22 * @L) / 451 SET @O = 22 + @H + @L - 7 * @M
IF @O > 31 BEGIN SET @R = @O - 31 + 400 + @YEAR * 10000 END ELSE BEGIN SET @R = @O + 300 + @YEAR * 10000 END
RETURN @R END GO --END fnDLA_GetEasterdate
--Create the table CREATE TABLE MyDateTable ( FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED, Period int, ISOWeek int, WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y' ) GO --End table create
--Populate table with required dates DECLARE @DateFrom datetime, @DateTo datetime, @Period int SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required WHILE @DateFrom <= @DateTo BEGIN SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6)) INSERT MyDateTable(FullDate, Period, ISOWeek) SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom) SET @DateFrom = DATEADD(dd,+1,@DateFrom) END GO --End population
/* Start of WorkingDays UPDATE */ UPDATE MyDateTable SET WorkingDay = 'B' --B = Bank Holiday --------------------------------EASTER--------------------------------------------- WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday GO
UPDATE MyDateTable SET WorkingDay = 'B' --------------------------------NEW YEAR------------------------------------------- WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7) GROUP BY DATEPART(yy,FullDate)) ---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------ OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1 GROUP BY DATEPART(yy,FullDate)) OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1 GROUP BY DATEPART(yy,FullDate)) --------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------ OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1 GROUP BY DATEPART(yy,FullDate)) --------------------XMAS(Move to next working day if on Sat/Sun)-------------------- OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN DATEADD(dd,+2,FullDate) ELSE FullDate END FROM MyDateTable WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26)) GO
---------------------------------------WEEKENDS-------------------------------------- UPDATE MyDateTable SET WorkingDay = 'N' WHERE DATEPART(dw,FullDate) IN (6,7) GO /* End of WorkingDays UPDATE */
--SELECT * FROM MyDateTable ORDER BY 1 DROP FUNCTION fnDLA_GetEasterdate DROP FUNCTION ISOweek --DROP TABLE MyDateTable
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime) RETURNS int AS BEGIN /* Description: Function designed to calculate the number of business days (In hours) between two dates.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I have a pretty simple query: Select title, avg(score) from votes group by title order by avg(score) Desc This returns a result like this: Abbey Road 4.0The White Album 3.5Meet the Beatles 3.0 The values in the score field are always 1, 2, 3, 4, or 5 What I really need from my query is 5 more columns (1, 2, 3, 4, and 5) with a count of how many votes each of those columns received. So the result set might be like this. Abbey Road 4.0 0 0 1 1 1 The White Album 3.5 0 1 0 0 1Meet the Beatles 3.0 2 2 2 2 2 I have tried to use the count function, but it counts all the values in the field. I can't figure out how to just count the values that match a certain criteria. When I tried to create subqueries, I got an error that said my subqueries where returning a multiple result sets. Any suggestions would be greatly appreciated, Chris
I am trying to count a column in my tbl. I have a table with the following, What i am trying to do is count the number of days of dtAttendance for an individual where bitpresent = 1. So this individual should have 2 days of attendance. I wrote some code belwo but it does not seem to count correctly, some it does and some it does not. intAssignedPersonnelID intUICID strSSN dtAttendance bitPresent intDrillStatus intMileage bitDatePayed210 1 333333333 5/26/2008 1 2 210 1 333333333 5/27/2008 1 2 210 1 333333333 5/28/2008 0 2
& "(select count(*) from saddotnet.dbo.tblAssignedPersonnel h where p.strSSN = h.strSSN and h.dtattendance <= p.dtattendance) " _ & "as count_attendance_as_at_dtattendance, p.dtattendance, bitpresent, intdrillstatus, " _ & "'<input name=chbxPresent' + convert(varchar(10), p.intAssignedPersonnelID) + ' type=checkbox>' as 'theCheckBox' " _ & "from cms.dbo.tblSIDPERS as s INNER JOIN " _ & "saddotnet.dbo.tblAssignedPersonnel as p on p.strSSN = s.sidstrSSN_SM where sidstrSSN_SM = left('" & SSN & "', 9)"
I have a table that is grouped by a date. Some records have the same date. I am just trying to return the total row count of the query.
Here is the sql:
SELECT ActiveWeek FROM wcWARS WHERE CreatedBy = 9034 AND FlagComplete = 1 GROUP BY ActiveWeek
I just want to return the total grouped column.... I thought Oracle had a ROWCOUNT function that returned the total rows of the query. I was unable to find one for SQL Server.
**Note: I already tried @@ROWCOUNT ~ This returns the total rows of the table.
Hello All, I have two tables A and B. A and B have column x in common. The relationship between A and B is one to many. I would like to count the number number of x's in table B and group by the x and then join this result back to table a. My attempt : SELECT COUNT(T1.x), T1.x, A.meeting_invite_idFROM (SELECT meeting_invite_idFROM REMINDER) T1, AWHERE A.meeting_invite_id *= T1.meeting_invite_idGROUP BY A.meeting_invite_id Anyone with any ideas.
assume that there are many stores listed in the table... how can i have the number of stores that has an ordercount?... ive tried some mdx queries but i still cant get it right...
I have two tables, Sales Headers and SalesLines. The SalesHeaders table will hold basic details of a sale, and the Sales Lines table will hold details of the items in the sale.
select distinct csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi as "Document Reference No.", csd1.csd_labl + ' ' as "Description", csd1.csd_issu as "Docu Dt", csd1.csd_altr as "Alternate Number", csd2.csd_altr as "Transmittal Reference No.", csd2.csd_issu as "TIssu Dt", trd1.trd_recd as "TRecd Dt", apr1.apr_reqd as "RReqd Dt", case when apr1.apr_stat is null then 'Unknown / Pending / No Reply' else apc1.apc_libe end as "Document Status",
csd3.csd_altr as "Reply Reference No.", csd3.csd_issu as "RIssu Dt", trd2.trd_recd as "RRecd Dt"
order by csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi
---
I need to count the resulting records of the query. How do i have to do this, could you please tell me ... I am new to SQl and I tried but could not solve it ...
( select emp_id, row_number() over (order by emp_id) as rownum from employee ) temp
where temp.rownum <=10
group by temp.emp_id
I would like to know whether there is a way to retrieve the no. of rows returned by the inner select query which could be displayed in the outer select query. I am not allowed to use temporary variables or tables variables for this purpose.
Hi I had posted this question earlier but could not get the solution, may be i was not clear with the doubts i had
I need to count the no of students for the different Intervention field which are like 14 different types against the field gender (male and Female )and field Ethnicity (horizontal field headers) 5 different types below
A student can be Male and Hispanic Type 1 also Male Hispanic Type 2
Male Hispanic Type 3
So his count is made in three places
I need to do this for the whole District level then for each Center under District level then for each school under Center level
I need to get a query result from a Derived Table that I have. Here is an example of the data from my Derived Table, based on AdvID. The following is a result of students that are in relation with the advisor.
studentPIDM AdvPIDM AdvID AdvFirstName AdvLastName Sprhold StvHlDesc 123456 14000 N12345678 John Smith 01 Letter Sent 123456 14000 N12345678 John Smith E1 Library 654321 14000 N12345678 John Smith NULL NULL 134567 14000 N12345678 John Smith 01 Letter Sent 134567 14000 N12345678 John Smith AM Admin Hold 134567 14000 N12345678 John Smith E1 Library 155544 14000 N12345678 John Smith NULL NULL 233555 14000 N12345678 John Smith NULL NULL
What I want, is to get the count of students that have holds, and the count of students that do not have a hold. From the data above, the students that do not have a hold have a NULL value on sprhold column. Also from this data, you can see that some students have multiple holds, but they should be counted as only once when the Count is performed. The desired result would look like the following:
I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below
I need to get count of the field employee id for each region 1 through 8 and for each status value *, 0, 1 ,2 ,3 ,4
SELECT ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHERE EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita
hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)
HelloI have this stored procedure:SELECT @openissue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND DateDIFF( day, TOpenIssue .DateStart, GETDATE() ) >= 0SELECT @oiclosed=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code = 'CLOSED' and Project=@project SELECT @oipastdue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND TOpenIssue .DateEnd<getdate()Is there away to optimize it in only one select statement?Thanks