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.
SQL Server 2012 Standard SP 1.
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT
[Code] ...
I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name)
From msdb.dbo.backupset
where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null
and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also
group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
I have a link that inserts postid, catid, and postdate. I am trying to get count of catid(how many times its in the table) then display the number on a page. i have never tried this before. one more thing. Can you put a datalist inside a formview when the formview is databound already? Can someone help? Thank you.
Hi,
Can any one tell me
how to find out the count of individual chars in a String
ie., my string is s='ganesh kumar'
my output must be as below
Character Occurance
--------------------
g 1
a 2
n 1
e 1
s 1
h 1
k 1
u 1
m 1
r 1
----------------------
Can any one do it,
This is interview question..
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
Hello,
I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.
For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,
For 1st group
'Page 1 of 3€™ when user click next page it should be 'Page 2 of 3' etc
Similarly for other groups as wll.
Thanks!
I'm trying to write a query that returns last 30 days data and sums the amount by day. However I need to do it for every year not just the current one(I need to go back as far as 2000).
declare @t table (id int identity(1,1), dt datetime, amt MONEY)
insert into @t (dt, amt)
select '2014-11-30 23:39:35.717' as dt, 66 as amt UNION ALL
select '2014-11-30 23:29:16.747' as dt, 5 as amt UNION ALL
select '2014-11-22 23:25:33.780' as dt, 62 as amt UNION ALL
[Code] ....
--expected output
select '2014-11-30' AS dt, 71 AS Amt UNION ALL
select '2014-11-22' AS dt, 62 AS Amt UNION ALL
select '2014-11-20' AS dt, 66 AS Amt UNION ALL
select '2014-11-18' AS dt, 102 AS Amt UNION ALL
[Code] ....
get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.
DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE)
INSERT INTO @T
SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL
SELECT 1, 'A', NULL UNION ALL
SELECT 1, 'A', '2015-5-13' UNION ALL
SELECT 1, 'A', NULL UNION ALL
[code]....
This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)
myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Variable Pages is global integer.
This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
So I am trying to work out the difference between today's date (GETDATE()) and a Target Date in a specific table (targetdate)
When I use the DATEDIFF function it is including non working days in the calculation (weekends and bank holidays). Although our date calandar table provided to us from a third party supplier will tell you the weekends, it does not tell you the bank holidays.
Luckily there is another table in the database called - ih_non_work_days.
The format of the date is "2014-12-25 00:00:00.000" for example in that table.
How do I using my "targetdate" and today's date calculate in days the differance - excluding the dates that exist in the ih_non_work_days database?
So for now my basic script looks like -
SELECT com.comm_reference AS 'Referance'
,com.current_task_target_date AS 'TargetDate'
, DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation'
FROM [dbo].[em_communication] as com
I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;
SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.
Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?
I am looking to do the following:
CurrentDate - admit date /24 (round down to whole number)
I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required
For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.
Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.
As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month
Period No of Void Days Rent Loss
06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57
01/10/2014 - 15/10/2014 15 349/7*15 = 747.85
I have uploaded a screenshot of how the result on this link: [URL] ....
Declare @void Table
(
PropCode VARCHAR(10)
,VoidStartDate date
,LetDate date
,Market_Rent Money
[Code].....
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
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
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.
[Code] ......
I have two tables:
Club - Stores all clubs (id_club, name)
Match - Store all matches (id_club1, id_club2, dateMatch, result)
The match has club 1 and club 2.
And I Have to select all matches that clubs played with an interval less than three days between games.
This is my code:
SELECT DISTINCT a.*
FROM
Matches a
INNER JOIN
Matches b ON
[Code] ....
I tried too:
select *, DATEDIFF(day, m1.date, j2.data) from matches m1, matches m2
where abs(DATEDIFF(day, m1.dateMatch, m2.dateMatch))<3
and (m1.id_club1=m2.id_club2)
But it doesn't working. Because I have two clubs in a row. It's so difficult.
The topic pretty much sums up my question.
What would the sql query be to return only all the records in a table that are 45 days old from today.
Thank you for any help in this matter
Al
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
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?
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!
 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]
);
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?
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?
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
I need to associate aggregate gross_revenue with calendar year, but do not have a date field that reflects payment dates, just contract periods a start_date and an end_date. The contract periods are typically 1 or 2 years and can start at any time I.e start_date 6/1/2012, end date 5/31/13. I think by finding the number of days that fall in each calendar year and storing in a temp table, I can create a simple formula to associate revenue to each year.
View 2 Replies View RelatedHello,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.
My table structure like below.
id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes
Want to calculate yes count on any day for same id
Consider following script
create table data_set (id int primary key, col1 varchar(10))
go
insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a')
select * from data_set
I tried this below
Declare @child_ids int
@col_val varchar
@count int
select @child_ids, @col_val, @count, count(col1) as records from data_set
group by col1
order by col1
But I want my output like below .....
We have customer accounts that we measure usage. We want to run a report for all customers whose current usage is 0 and a count of how many months it has been zero consecutively. Here is an example.
declare @YourTable table (
CustomerID int,
ReadDate datetime,
usage int
)
insert into @YourTable select 1,' 1 mar 2014',0
insert into @YourTable select 1,' 1 feb 2014',0
[Code] ....
This should return
1,3
2,1
This is what I am currently using but it isn't working right
WITH cte
AS
(
SELECT *,COUNT(1) OVER(PARTITION BY cnt,CustomerID) pt FROM
(
SELECT tt.*
[Code] .....
I have the table below and want to show the prop_code if the rent_review_date count is less than 1 in 12 months. This means to show only propcode if there has not been any rent update since the first rent_review_date
DECLARE @table TABLE
( Prop_Code INT
,Current_Rent INT
,Revised_Rent INT
,Rent_Review_Date varchar(10)
,Rent_Review_Time DATEtime)
[Code] .....
using below script to compare two tables and get the values.
how to get the count of 'Table A' , 'Table B' , 'Table A & Table B' using below script.
Ex:
'Table A' -- 150
'Table B' -- 300
'Table A & Table B' -- 150
SELECT
Col1 = ISNULL(a.name,b.name),
Col2 =
CASE
WHEN ISNULL(a.name,'') = '' THEN 'Table B'
WHEN ISNULL(b.name,'') = '' THEN 'Table A'
ELSE 'Table A & Table B'
END
FROM #tableA a
FULL JOIN #tableB b
ON a.name = b.name;
Trying to get the max count grouped by all the fields. All the fields are the same, but trying to get the location for each physician that has the largest number of patients.
if the output for the sql below is:
101, 10, Jon, Smith, MD, Ortho, OR, 15
101, 10, Jon, Smith, MD Ortho, 1, 12
101, 10, Jon, Smith, MD, Ortho, 2, 10
24, 3, Mike, Jones, MD, Neuro, OR, 21
24, 3, Mike, Jones, MD, Neuro, 2, 43
I'd like to have the query rewritten so the results are as:
101, 10, Jon, Smith, MD, Ortho, OR, 15
24, 3, Mike, Jones, MD, Neuro, 2, 43
SELECT
a.attendingmdkey,e.[provider id],e.[first name],e.[last name],e.title,e.specialty,l.locationname,count(a.accountid) as Count
FROM accounts a
left outer join location l on l.locationid=a.locationid
left outer join providers e on e.[ProviderID]=a.attendingmdkey
where a.dischargedate>='2014-12-01' and a.dischargedate<'2015-01-01'
and a.divisioncode in ('1','2','$')
group by a.AttendingMdKey,e.[provider id],e.[first name],e.[last name],e.title,e.Specialty,l.locationname
order by a.AttendingMdKey