How To Selectively Count Days
Jul 30, 2014
So here is the question, on the last line, MAX(LAST T.LastDate) has a date, or it's NULL.
How can I change the statement to say, IF MAX(LAST T.LastDate) IS NULL use NULL?
SELECT DISTINCT
TOP (100) PERCENT O.CompID, O.Comp_Name, COUNT(DISTINCT O.Comm_Unit) AS Apartments, FL.First_MI, FL.Last_MO, O.Bldg, DATEDIFF(day, FL.First_MI,
FL.Last_MO) AS days, O2.MoveoutDescription
FROM dbo.OccupancyHistory AS O RIGHT OUTER JOIN
(SELECT O.CompID, MIN(FIRST_T.FirstDate) AS First_MI, MAX(LAST_T.LastDate) AS Last_MO, MAX(LAST_T.LAST_ID) AS LAST_ID
View 7 Replies
ADVERTISEMENT
Aug 28, 2007
I am looking to do the following:
CurrentDate - admit date /24 (round down to whole number)
View 14 Replies
View Related
Feb 10, 2004
Hi everyone,
I'm trying to work out how to count the days between two datetimes, eg:
2004-01-01 10:02:23
and
2004-29-01 21:24:13
would give me 29 days (inclusive). How could I go about doing this?
Thanks
Andrew
View 2 Replies
View Related
Apr 21, 2000
I need a function to count business days (exclude Sat, Sun) that I can call within a view. I would rather not build a "calendar table" this will be used ongoing for years into the future.
Does anyone have anything like this they could share? If there is another source you could direct me to I would appreciate that as well.
TIA
Phil
View 1 Replies
View Related
Dec 16, 2013
I have the following simple SQL which counts the days difference between two date fields:
SELECT
DATEDIFF(dd,central_enquiry.enquiry_time, GETDATE()) as Days_Open
FROM
central_enquiry
---
How do I get it to exclude weekends?
I also have a table nonworkingdays which has a nonworking_date field where users can manually record national holidays and bank holidays etc.
Example date
nonworking_date
01/01/2014
18/04/2014
05/05/2014
How can I include this table in the calculation too?
View 16 Replies
View Related
Apr 11, 2008
I have a query that counts the number of pay checks received in a month, but I need to compare that to the number of actual paydays in a month. If we were on a set pay schedule (i.e. the 15th of each month) it would be easy. Unfortunately that's not the case. We are paid biweekly on Fridays and our last pay day was April 4th.
So I need to know how many pay periods were in a specified month. For example, April would have 2 (April 4th and 18th), May would have 3 (May 2nd, 16th, and 30th), etc.
As always, your help is very much appreciated!
View 10 Replies
View Related
Nov 23, 2015
 I would like to count the working days of a spesific time range and then find the Average Daily Dispatches.Currently the time range is at WHERE statement. how to count the dates in a month range. I prefer not to add a new calc Member.
WITH MEMBER [Measures].[Working Days] AS
COUNT(Date.[Working Date].&[1])--Doesn't work
MEMBER [Measures].[Average Daily Dispatches] AS
[Measures].[Total Dispatches]/[Measures].[Working Days]
SELECT [Measures].[Average Daily Dispatches] ON 0
FROM [cube]
WHERE (
[Date].[Month].&[2015-01-01T00:00:00]:[Date].[Month].&[2015-08-01T00:00:00]
);
View 4 Replies
View Related
Dec 24, 2013
If I have a table like this:
ID User Date
20 22 1-1-2013
21 22 1-1-2013
22 31 1-1-2013
23 22 1-1-2013
24 22 1-2-2013
25 22 1-2-2013
etc...
How can I get the count of records grouped by date and user?
Date 22 31
1-1-2013 3 1
1-2-2013 2 0
etc...
Is this possible?
View 4 Replies
View Related
Jul 9, 2015
I am trying to get a cumulative count of business days. I have a column in my date dimension BusinessDayInd which is 'Y' or 'N'. I have been trying to create a calculated column in the cube but have not been successful yet. I think I'm close with this:
  COUNTROWS(FILTER(DATESMTD(PostingDate[Posting Date]), PostingDate[BusinessDayInd] = "Y"))
However, this is giving me a count of calendar days for all business days and null for non-business days. How do I apply the filter to DATESMTD? I've tried many iterations of this using CALCULATETABLE, CALCULATE, and FILTER. All are giving me the same result.
What am I doing wrong?
View 7 Replies
View Related
Nov 19, 2015
I want to find out count of same UserID used within 7 days for that month.
For example, in below scenario, UserID 458's 1st TTo is 8/26/2015 and it used again on 8/28/2015 which is less than 7 so result should be 1. (DateDiff between 1st TTo and 2nd Tfrom should be less than 7) This is for month of Aug.
ID TFrom TTo
9876 8/1/2015 8/7/2015
4140 8/21/2015 9/4/2015
458 8/23/2015 8/26/2015
458 8/28/2015 9/8/2015
Scenario 2,for UserId 458, TTo is 9/20 and it used again Tfrom on 9/20 so result should be 1Â
user ID TFrom T To
592 9/1/2015 9/24/2015
526 9/3/2015 9/11/2015
292 9/11/2015 9/25/2015
352 9/12/2015 9/24/2015
458 9/14/2015 9/20/2015
458 9/20/2015 10/2/2015
706 9/22/2015 10/6/2015
View 5 Replies
View Related
Aug 5, 2014
i am using the followig query :
select count (*) from MEMBERS,dbo.MEMBER_PROFILE
where MEMBER_PROFILE.member_no = members.member_no
AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59'
and email <> 'selfbuy_customer@cafepress.com'
and ROOT_FOLDER_NO is not null
and email not like '%bvt.bvt'
This returns the count for the month but I want to see what the total each day was.
View 5 Replies
View Related
Aug 3, 2006
I have a scenario whereby I will have 200+ clients putting messages onto a service broker queue. This message will go through a pipes and filters based messaging system, and ultimately the message will pop out the other end.
Here's the question: what is a good way of making sure the same client gets the response to the message he received. Is there anyway I can selectively receive messages from a queue, i.e., pass a correlation id in with the message, and then filter messages based on that id.
Or if someone knows a better way to do it altogether i'd really appreciate it.
Many thanks,
Paul
View 9 Replies
View Related
Mar 20, 2007
Based on some report parameters value?
My dataset to display is coming from a stored procedure. Now based on a report parameter which user selects, I want to filter the results being displayed but only if user selects certain value in the parameter drop down. I tried using iif statement and I can use it to filter dataset based on parameter but can't get how NOT to apply the filter for a particular value of the user selected parameter.
Hope I am clear.
View 9 Replies
View Related
Dec 4, 2007
This might be tricky, but I wanted to know if I can add a row to the table and decide wht keys I want to generate for that row.
For example: I have a table which have 4 different keys. I want to generate only 2 of them for certain rows and all for the remaining rows. Is this possible? This does sound like an advanced feature for sql databases, but its easy to do with custom dbs like berkeley db
Trust me, its a very helpful feature.
Thanx in advance,
Premal
View 4 Replies
View Related
May 3, 2008
I have a bit of an issue with an app I'm working on. The app integrates two different SQL Server applications - both of which employ recursive triggers to some extent. My integration basically serves to establish a link between different tables in the applications, and maintains consistency between the two applications (where common fields exist) by employing INSERT, UPDATE, and DELETE triggers.
In order to prevent infinitely recursive triggers (AppA.TableA's update trigger updates AppB.TableA. AppB.TableA's update trigger updates AppA.TableA...and so on, and so on) I need to be able to somehow selectively prevent these triggers only from executing recursively. For example, if the trigger in AppA is what calls the trigger in AppB, I do not want AppB's trigger to fire (and vice versa).
Further Information:
The apps may be on the same, or different, SQL servers.
I'm fully aware of the database-wide options to disable trigger recursion (ALTER DATABASE), but I can't disable recursion for the balance of the triggers in the databases.
The integration will run on either SQL 2000 or SQL 2005 - and perhaps one server on 2000, and one on 2005 (depending upon the deployment).
I'm perfectly amenable to handling it in the trigger code, if possible, but I'm at a bit of a loss as to how to properly and efficiently manage that.
I know that SQL Server will kill infinitely recursive triggers once it detects them, but that doesn't exactly solve my original problem.
Thanks very much for any input you can offer.
View 3 Replies
View Related
Apr 24, 2014
I have about 1200 sql files in one of my folders. Almost all of these files do data inserts and updates, so they should be run only once. As and when required I have manually ran around 150 of them already. Whenever I ran any of these scripts, I log that file name into a log table in my sql server including the execution time. Since running 1000+ more files takes a lot of time, I want to automate running of these files through a batch file. But I also want to filter the files that are already run.My file list looks like follows.
InsertToOrderTypes.sql
UpdateClientAddress.sql
DeleteDuplicateOrders.sql
InsertToEmailAddress.sql
ConsolidateBrokerData.sql
UpdateInventory.sql
EliminateInvalidOfficeLocations.sql
My log table in the database looks like this.
select * from sqlfileexecutionlog
FileNameRunTimeResult
---------------------
DeleteDuplicateOrders.sql03/12/2014 14:23:45:091Success
UpdateInventory.sql04/06/2014 08:44:17:176Success
Now I want to create a batch file to run the remaining files from my directory to my sql server. I also want to wrap each of these sql file executions in a transaction and log success/failure along with the runtime and filename into sqlfileexecutionlog table. As I add new sql files into this directory, I should be able to run the same batch file and execute only the sql files that have not bee run.
View 9 Replies
View Related
Jan 7, 2014
I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.
View 1 Replies
View Related
Oct 25, 2006
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
View 1 Replies
View Related
Aug 6, 2006
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
View 5 Replies
View Related
May 25, 2015
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
View 3 Replies
View Related
Jul 3, 2013
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
View 9 Replies
View Related
Nov 26, 2007
I use SQL 2000
I have a Column named Bool , the value in this Column is 0�0�1�1�1
I no I can use Count() to count this column ,the result would be "5"
but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is 2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.
thank you very much
View 5 Replies
View Related
Jul 23, 2005
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies
View Related
Aug 21, 2007
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
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
View 1 Replies
View Related
Jun 25, 2007
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
View 6 Replies
View Related
Jun 9, 2008
How would I bring back a column that shows all the days from the begining of the year to the current date. Also make sure this moves onto the next year.
View 9 Replies
View Related
Jan 24, 2007
Did a bit of searching, but couldn't find it. I'm looking to get data from the past 7 days:
select * from inquiry
where created_date is within the last 7 days
Thanks,
View 2 Replies
View Related
Jul 10, 2007
Hey heyI've written my site in asp.net 2 and have set up roles and various logins etc which work beautifully locally. Now, when i upload to my host I get the: An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26
- Error Locating Server/Instance Specified)The thing is, I've checked my connection string a million times and it's perfect, I've also checked all the database settings on my host and they're all fine. However, when i use SQL Server Management Studio Express to copy my database from the local to the remote server (from a tutorial on these forums), I get the "Operation completed successfully" message yet when I expand my tables, there is no data in them. I've tried copying and pasting the data in manually but i still get the same issue. It's driving me absolutely insane! Anyone any ideas?Thanks
View 6 Replies
View Related
Aug 6, 2007
Hi all,
I have a table in which I have two fields in my DB.
FromDate and ToDate.
Both are stored as Varchar(MAX).
I would like to have an SP which gives me the Days in Between them.
Regards,
Naveen.
View 4 Replies
View Related
Aug 25, 2005
Is there any way to find out in sql server that whether it is Monday to friday or saturday sunday.I have to write a stored procedure in which the logic shoukld be done only if there is a weekend.if saturday or sunday select * from status_tempPlease let me know if there is any function that is available in sql
View 4 Replies
View Related
Jan 24, 2006
Is there anyway to get SQL to convert a date to days of the week?
Example, I have a query where the date is "01/01/2006" but I would like it to display "Sunday". Thank you.
View 2 Replies
View Related
Feb 3, 2006
How can I select records from a schedule table that have end dates older than 90 days?
Can I do getdate() -90?
Code:
select *
from dbo.schedule
where enddate <> endate-(90 days?)
View 1 Replies
View Related
Feb 26, 2006
Hi,
I just want to know, is there anything more better solution than this one to find out the no of days in a month ?
I have done this but I am not satisfied,anybody has a smarter solution?
Plz comment..
My Solution :
/* check leap year*/
if year(getdate())%4<>0
set @noofdays=(select case month(getdate())-1
when 1 then 31
when 2 then 28
when 3 then 31
when 4 then 30
when 5 then 31
when 6 then 30
when 7 then 31
when 8 then 31
when 9 then 30
when 10 then 31
when 11 then 30
when 12 then 31
end )
else
set @noofdays=(select case month(getdate())-1
when 1 then 31
when 2 then 29
when 3 then 31
when 4 then 30
when 5 then 31
when 6 then 30
when 7 then 31
when 8 then 31
when 9 then 30
when 10 then 31
when 11 then 30
when 12 then 31
end)
Joydeep
View 2 Replies
View Related
