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


ADVERTISEMENT

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 :: 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

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

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

T-SQL (SS2K8) :: Selecting Data By Date For Last Five Days AND Avoiding Weekend Dates

Apr 16, 2014

What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:

DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG
I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment)
,(
((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE

[Code]....

The query works with the following exceptions:

My issues are:

1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.

2. Avoid weekend dates. I need the query to display only weekdays

3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.

View 6 Replies View Related

Transact SQL :: How To Find If User Not Logged In For 45 Days

Nov 20, 2015

I have created table called Login in sql server where i have column usercode, email and login_date (login_date is datetime type)So, i created web application using .net. whenever user logged in, i am allowing based userLoged table and  i am inserting into login table.

login table usercode  email       login_date
001          a@gmail.com    2015-11-18 22:02:41.153
001          a@gmail.com    xxx
.
.
.

I have another table called userLoged where i have column usercode,email and web_access 

UserLoged table usercode email         web_access
001         a@gmail.com   Y

Now, if a@gmail.com (001) is not logged in for 45 days, i need to update web_access to be 'N' how to know if he /she not logged for 45 days.

View 4 Replies View Related

Transact SQL :: Find Total Days Of Stay From First Arrival To Last Departure Day

Nov 12, 2015

i have a table with customer number, arrival and departure days and days difference between them. i need to find out the total number of days from first arrival day to last departure day if departure and next arrival days are same. for example, for customer Number, 1120, departure date and next arrival days are same, so the total number of days of stay is, 28+28+42+42. how to find out this total suing SQL.

CustomerNumber
ArrivalDate
DepartureDate
Days

1120
10/24/2014
11/21/2014
28

[Code] ....

View 8 Replies View Related

Transact SQL :: Find If A Table Is Partitioned By Month Or Day

Nov 6, 2015

Is there a way to figure out if a table is partitioned by month/day in SQL. 

View 2 Replies View Related

Transact SQL :: Function To Find Last Date Of Month One Year Ago - RETURNS ERROR

Apr 28, 2015

I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example,  it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:

Operand type clash: date is incompatible with int

Why is this error being returned?

Here is my function:

CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
       RETURNS date
AS
BEGIN;
  DECLARE @Result date;
  SET @Result =  convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
    RETURN @Result;
END;

View 11 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

Transact SQL :: Displaying Sales Data In A Month By Month Grid

Aug 11, 2015

Most of the data is in one table. 

Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264

I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.

 SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January', 

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

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

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

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

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

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

Reporting Services :: Calculating Average Amount Of Working Days In A Month - SSRS 2005 Matrix

Jun 22, 2015

I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?

View 7 Replies View Related







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