Count Number Of Pay Days In A Month

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


ADVERTISEMENT

Transact SQL :: Count Of Same UserID Used Within 7 Days For That Month

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

Help With Dates-finding Number Of Days Per Month

Mar 28, 2000

I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.

A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.

Thanks in advance

View 1 Replies View Related

Calculating Number Of Working Days In A Month

Sep 4, 2007

Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.

can anyone say the solution?

thanks.

View 2 Replies View Related

Transact SQL :: Number Of Days Between A Date And Its Month End

Sep 23, 2015

I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
 
Something like below,
 
DateTD       Days left
2009-01-05  14
2009-01-06      13
2009-01-07      12
2009-01-08      11
2009-01-09      10
2009-01-12       9
2009-01-13       8
2009-01-14       7
2009-01-15       6
2009-01-16       5
2009-01-19       4
2009-01-20       3
2009-01-21       2
2009-01-22       1
2009-01-23       0
2009-02-02       /
2009-02-03       /

View 28 Replies View Related

Simple Way To Determine The Number Of Days In A Month For Any Given Date

Jul 20, 2005

DECLARE @varDate datetimeDECLARE @varMonthDate datetimeDECLARE @varYear datetimeDECLARE @varFOM varchar(10)DECLARE @NumDaysInMonth as intselect @varDate = '2/1/2004'select @varMonthDate = MONTH(@varDate)select @varYear = YEAR(@varDate)select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +CAST(CAST(@varYear as int) as varchar)select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))select @NumDaysInMonth as DaysInMonth

View 1 Replies View Related

Transact SQL :: Show N Number Of Column As Per No Of Days In Month

Aug 26, 2015

I want to show this kind of output

UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31

user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.

View 10 Replies View Related

Get Count And Average Number Of Records Per Month

Sep 12, 2006

Example table structure:
Id int, PK
Name varchar
AddDate smalldatetime

Sample data:
Id Name  AddDate
1  John  01/15/2005
2  Jane  01/18/2005
.
.
.
101  Jack  01/10/2006
102  Mary  02/20/2006

First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here?

select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr
  from dbo.sampletable
  group by datename(mm, AddDate), datepart(yy, AddDate)
  order by count(id) desc

Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?

View 3 Replies View Related

T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month

Sep 10, 2014

This is my table and data

CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484

I need to convert into this result

CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months

View 5 Replies View Related

SQL Query Automatically Count Month Events B4 Today; Count Events Today + Balance Of Month

Mar 20, 2004

I would like to AUTOMATICALLY count the event for the month BEFORE today

and

count the events remaining in the month (including those for today).

I can count the events remaining in the month manually with this query (today being March 20):

SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))

Could anyone provide me with the correct syntax to count the events for the current month before today

and

to count the events remaining in the month, including today.

Thank you for your assistance in advance.

Joel

View 1 Replies View Related

No Of Days In A Month

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

Days In A Month

Dec 21, 2007

So Im trying to find the number of days in a certain month I know how I want to do but cant figure out how to code it

i want to take the first date of the getadate() and then ad a month then subtract 1 day and ill have the number of days in a month how would i code that?

View 5 Replies View Related

Calculating The No. Of Days In A Month

Jul 14, 2004

How can I calculate the no. of days in a month.I know it is a simple question but I am not able to find any code to calculate that.
Can someone help me?

View 7 Replies View Related

Woking Days In A Month

Nov 6, 2007

Hi frnds,

How to find the no. of working days for any month?

Input : YearMonth - 200702

Output : 20

Thanks in Advance

View 6 Replies View Related

DTS: Schedule Package On Different Days Each Month?

Aug 16, 2001

I wish to schedule a DTS package so that it runs on the penultimate day of each month. Is there an advanced option which allows you to choose a different date for each month? If not, is there a way this can be done programatically/

Thanks
Neill

View 1 Replies View Related

Generate Data Of All Days Of Last Month

Jun 9, 2014

What query should i use to generate a data of all days on last month, for example, if today is june so last month would be may.

View 3 Replies View Related

Calculate The SUM For 1 Month & 5 Days -display

Dec 18, 2007

Hi All,

I am new to SQL programming, i have only a fair knowledge on sql programmin.So, I apologies for any silly questions-

I have a Table1 which contains
C1-acountid
C2-date
C3-grossamount(postivie and negative decimal values)
C4-netamount

Table2
C1-groupid
C2-accountid

Table 3
C1-groupid
C2-groupname


I need create a store procedure to retrieve the following on a single table

1. top 10 losers of the day i.e. 10 AccountIDs with the greatest negative Grossamount for the day
NOTE:These 10 AccountIDs may be sam or differing each day
2.sum of Netamount for each AccountIDs listed in STEP 1 since the beginning of the month.
NOTE:These 10 AccountIDs may be same or differing each day and each day sum of netamount should be from beginning of the month till current date.
3.Sum of Netamount for the last 5 days for each accountids in STEP1


The result set must contain the columns as below

C1-accountid
C2-date
C3-net loss for 10 losers on the current date since the beginning of the month
C4-Sum of Net for last 5 days
C5-groupname


Please help me.

Below is the script that i have written, without calculating the sum

(
select top 10 a.date, a.accountid, a.gross, a.net, c.groupname
from GBSys_Sum_EOD a

join server2.dbname.dbo.table2 b on a.accontid=b.accounit=id
join server2.dbname.dbo.Table3 c on b.groupid=c.groupid

where date> getdate()-1
and gross< (floor(-00.00)) order by gross
)

Thanks in advance.

View 2 Replies View Related

Datediff Needs To Deliver Month AND Days

Aug 26, 2005

with datediff all I can get it to return is months or a total of thedays...so if the difference in dates is 12 months 4 days how do I adjust theSQL to accommodate both?

View 3 Replies View Related

How To Get All The Days Of A Month Using Select Statement

Jan 13, 2008



How to get all the days of a month using select statement in sql server 2000
please help
thanks

View 1 Replies View Related

Transact SQL :: Select Between Days Record Of Every Month

Jun 29, 2015

I have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month. How to query for that?

View 8 Replies View Related

Transact SQL :: Display All Days Of A Given Month And Year

Oct 17, 2015

I need a simple query to display all the days of a given month and year

View 2 Replies View Related

Transact SQL :: How To Find Weekend Days Per Month

May 13, 2015

I want to find the first weekend day, second weekend day, third weekend day and fourth weekend day per month using sql query. This is getting from recurring appointment. If weekdays = 65 means it accepts only saturday and sunday. So I want first, second, third and fourth weekend days for a month using query in sql server 2008...

View 17 Replies View Related

Reporting Services :: How To Get Days Of Month In SSRS

Apr 24, 2015

How can I get days of Month in SSRS report...?

View 5 Replies View Related

SQL Server 2008 :: Selecting Days In Month Between Two Dates

May 19, 2015

I need some with selecting the number of days, in a month, between a date range. For example, my data looks like:

FileNumb | startdate | enddate
1 04/25/2015 05/02/2015
2 05/01/2015 05/10/2015

The output I am looking for would be:

FileNumb | Year | Month | Days
1 2015 4 6
1 2015 5 2
2 2015 5 10

View 9 Replies View Related

Analysis :: Range Function - How To Get Last 60 Days Before A Month Starts

Apr 20, 2015

I am trying to get the last 60 days before a month starts. I have a set that is returned from the query below :

SELECT
   non empty
   [Measures].[TRANSACTIONS Count] on 0,
   non empty ([TRANSACTIONS].[Days].[Days],                                                          
                                     [TRANSACTIONS].[Transaction Month].[Transaction Month])  on 1 from [cube]   

I can get the cummulative count of last 60 days before month 2 by hardcoding the day of transaction of start of month like below :

WITH MEMBER
 [Measures].[Cumm Account Count]
AS
(
    AGGREGATE( [TRANSACTIONS].[Days].CurrentMember:NULL ,[Measures].[TRANSACTIONS Count])
)

 SELECT
   non empty [Measures].[Cumm Account Count] on 0,
   non empty [TRANSACTIONS].[Days].&[3]:[TRANSACTIONS].[Days].&[3].lead(60) on 1 from  [cube];
 
and for subsequent months by using the dates that the following month starts. How can I achieve the above result without having to use the day numbers, I tried to use the tail function (to get the months and star date) but it wont work because the range function  accepts members only...

View 4 Replies View Related

How To Get Date Difference In Terms Of Year,month,days

Mar 31, 2008



I am using oracle 10G DB as back end.I have two date fields in a table.


1)premium_paying_start_date

2)premium_paying_end_date


i have to get the premium_term i.e, the difference between the two dates(premium_paying_end_date-premium_paying_start_date).

The difference should show the year,month and no of days difference.

For example :

premium_paying_start_date : 14-10-1984

premium_paying_end_date : 01-03-2008

Difference should be : 23 Y : 4 M : 15 D (Y = years, M = months , D= days)


So please give me the solution for this.

View 6 Replies View Related

Count Of Days

Aug 28, 2007

I am looking to do the following:
CurrentDate - admit date /24 (round down to whole number)

View 14 Replies View Related

SQL Server 2012 :: Split Total Days And Amount Between Start And End Month

Mar 21, 2015

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].....

View 4 Replies View Related

DB Engine :: Creating A Schedule Task To Execute After 5 Days From The 28th Of Every Month

Jun 7, 2015

Is it possible to create a SQL schedule task to execute after 5days from the 28th of every month.

View 3 Replies View Related

Trying To Count The Days Between Two Datetime's

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

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 View Related

Need Function To Count Business Days

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

How To Count Working Days Only Between Two Dates

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







Copyrights 2005-15 www.BigResource.com, All rights reserved