Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?
Thanks...
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
I need to calculate a date.example it needs to be 20 working days ago compared to today so that means it needs to not include any Saturday or Sunday in between
declare @start_date datetime
declare @end_date datetime
declare @working_days int
set @working_days = 20
[code]...
So I need to calculate @start_date but it needs to exclude any weekend days.@working_days is the number of working day I'm interested in.
Hi,
I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)
I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse
declare @WorkingDate as datetime
SELECT @WorkingDate=dt
FROM tblCalendar AS c
WHERE (@WorkingDays =
(SELECT COUNT(*) AS Expr1
FROM tblCalendar AS c2
WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1)
AND (IsHoliday = 0)
-- Return the result of the function
RETURN convert(varchar(12),@WorkingDate,106)
Hope someone can help
Thanks
I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X:
Resource: 100
Starting date: 2012-03-01 (from this date the schedule will be effective)
Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30
Record 2: same but daynumber is 2
Record 3: same but daynumber is 3 etc.
...
Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00
Record 9: same as record 2 but daynumber is 9.
etc.
...
Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber)
2014-05-02 = day 5
2014-05-03 = day 6
...
2014-05-10 = day 13
2014-05-11 = day 14
2014-05-12 = day 1
2014-05-13 = day 2
2014-05-14 = day 3
...
2014-05-24 = day 13
2014-05-25 = day 14
2014-05-26 = day 1
2014-05-27 = day 2
...
2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
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.
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
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.
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX)
@sql = ' insert into target
select from table_1_currentdate a
LEFT JOIN Table_2_currentdate-1 b
on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
The following query was used for retrieving dates for the last 7 days . Untill February this query was running fine and would return the last seven days date including today.
SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 7
However from March (not sure of the exact date)..the query below would only give us 7 days until yesterday..i.e it would list dates from 3/19,3/20,3/21,3/22,3/23,3/24,3/25 and not 3/26 ..
I changed the query to <= 6 and it works as expected. But still not sure why it would not return todays date with <= 7.
SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 6
I have the following tables
EntertainmentType
typeId PK tinyint
type varchar(30)
Entertainment
id PK int
typeId FK tinyint
title varchar(35)
description varchar(300)
purchaseDate smalldatetime
CheckedOut
recordId PK int
id >> dunno if I should make this a foreign key - relates to Entertainment.id
checkOutDate smalldatetime
dueBackDate smalldatetime
userId
returned bit
It is actually has a relationship that is similar to a regular customers, orders set of tables.
I have a list of movies and every time a movie is checked out a record gets added to the checkedout table. So while there is 1 of each movie in the entertainment table ... the movie may be referred to in the checkedout table multiple times ...
The result set that I am trying to get, and that i've spent all day on, is - all the movies and an indication of whether they are currently available for checkout.
i have the following, which I also had help with ...
select * from entertainment
where entId not in
( select entId from checkedout
where
-- checks if dates conflict, assume 2 days checkout
( checkOutDate > dateadd(d,2,getdate())
or dueBackDate < getdate() )
or
-- checks if current booking returned and is now available
( checkOutDate < getdate()
and dueBackDate > getdate()
and returned = 'true')
)
though this returns a list of all the movies that are currently available for checkout. I need to be able to show all the movies that I have, so that someone knows that I have it even if its not available right now. The relationship is very similar to a customers - orders set of tables and I suppose the equivalent would be asking for a list of all the customers indicating the lastest product they bought ...
If I replace not in with exists I get the desired result but it won't work with a join so I don't know how to indicate if its available or not. Does anyone have any suggestions ... I appreciate any help you can provide ...
executing this procedure taking time please solve it for less time any modifications
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.r_routeGetCache2 Script Date: 5/21/2008 3:07:05 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[r_routeGetCache2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[r_routeGetCache2]
GO
/*
---------------
Upgrade Version of r_routeGetCache1 to Implement Grade
By N Mohaed
2005 - 12 - 12
----------------
*/
CREATE procedure r_routeGetCache2
@pLongest varchar(32),
@pLongestCls int,
@pDate datetime,
@pSrcInt varchar(7),
@pSrcIntGroup varchar(64),
@pSrcIntDom varchar(64),
@routeclass int=0 ,
@pCLI varchar(32)='',
@pOperatorGroup int = 0 ,
@isgrade int=0,
@pRouteName varchar(32) = ''
as
begin
declare @pc varchar(2)
declare @dd int
declare @hh int
declare @mm int
set @pc=left(@pLongest,2)
set @dd=1+(@@datefirst+datepart(dw,@pDate)-2)%7
--select @hh=(datepart(hh, @pDate)-timezone) from r_info
set @hh=(datepart(hh, @pDate)-0)
set @mm=datepart(mi, @pDate)
set nocount on
--- Azam
select top 1 @routeclass = routecls from r_interface
where [group] = @pSrcIntGroup
and state='I'
if @routeclass is null set @routeclass=0
--- End
--insert
CREATE TABLE #operator_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[cls] [varchar] (1) NOT NULL ,
[pc] [varchar] (2) NOT NULL
)
--CREATE UNIQUE CLUSTERED INDEX [idx1] ON
--[dbo].[#operator_selected]([routecls], [oprId], [cls], [pc]) ON [PRIMARY]
insert into #operator_selected(routecls,oprid,cls,pc)
select x.routecls,x.oprid,x.cls,x.pc from
r20_route_timecls x (nolock),
r_TimeCls y with (nolock),
r_interface tit with (nolock),
r_timecode tco with (nolock),
r_daycode dco with (nolock)
where x.routecls=0
and tit.id = x.oprid and tit.state = 'I'
and y.tintid =x.oprid and y.cls=x.cls
and y.dc=dco.id and y.tc=tco.id and y.sc=0
and @dd between dco.d1 and dco.d2
and ((24+@hh+isnull(tit.prefixcls,0))%24) * 100 + @mm between tco.h1
and tco.h2-1
and x.pc=@pc
CREATE TABLE #timecls_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[pc] [varchar] (2) NOT NULL ,
[prefixcode] [varchar] (50) NOT NULL ,
cnt int,
clsA int,
clsP int,
clsO int,
clsW int,
[cls] [varchar] (1) NOT NULL
)
insert into
#timecls_selected(routecls,oprid,pc,prefixcode,cnt,clsA,clsP,clsO,clsW,cls)
select a.routecls, a.oprid, a.pc, max(a.prefixCode) prefixCode,
count(*) cnt,
sum(case when a.cls='A' then 1 else 0 end) clsA,
sum(case when a.cls='P' then 1 else 0 end) clsP,
sum(case when a.cls='O' then 1 else 0 end) clsO,
sum(case when a.cls='W' then 1 else 0 end) clsW, 'Z' cls
from r20_route12 a(nolock), #operator_selected b
where a.routeCls=@routeclass --b.routeCls
and a.oprId=b.oprId and a.cls=b.cls
and a.pc=b.pc
and left(@pLongest,prefixLen)=a.prefixcode
group by a.routecls, a.oprid, a.pc
update #timecls_selected
set cls = (case when (clsW>0) and (cnt = clsW) then 'W'
when (clsO>0) and (cnt = clsO) then 'O'
when (clsP>0) and (cnt = clsP) then 'P'
when (clsA>0) and (cnt = clsA) then 'A'
else 'Z' end)
update #timecls_selected
set cls = (select max(a.cls) from r20_route12 a (nolock)
where a.routecls=#timecls_selected.routecls
and a.oprid = #timecls_selected.oprId
and a.pc=#timecls_selected.pc
and a.prefixCode=#timecls_selected.prefixCode
and ((a.cls='A' and #timecls_selected.clsA<>0) or
(a.cls='P' and #timecls_selected.clsP<>0) or
(a.cls='O' and #timecls_selected.clsO<>0) or
(a.cls='W' and #timecls_selected.clsW<>0))
)
where cls = 'Z'
CREATE TABLE #route (
seqno int identity(10,10),
priority int,
[id] int,
isactive int,
reason int,
exception int,
exceptionCls int,
calcexception int,
ext int,
oprid int,
parentid int,
routecls int,
prefixcode varchar(50),
universe varchar(5),
domain varchar(5),
pdomain varchar(5),
[group] varchar(40),
interface varchar(40),
userinfo varchar(40),
hint varchar(100),
clsorg char(1),
cls char(1),
cost float,
flag int,
rating int,
access varchar(10),
redlist int ,quality float)
if @isgrade =0
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
x.state isactive, x.reason,
isnull(x.exception,0) exception,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else cast((x.exception-0.5)*10 as int)
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc and
x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.cost
end
else
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
x.state isactive, x.reason,
isnull(x.exception,0) exception,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else cast((x.exception-0.5)*10 as int)
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc
and x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.quality, x.cost
end
select
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, isnull(y.hint, '') hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade,
oprId, @pLongest routepfx, @pRouteName routename
--into #all Deleted By N Mohamed 051129
from #route x, r_interface y with (nolock) ---, r_interfaceGroup ig with (nolock)
where x.oprId=y.id
and x.quality < 10
--and ig.tintid=x.oprId
--and ig.intgroupid=@poperatorGroup
--and ig.flag=1 and x.isactive=1
and @pLongest like x.prefixcode+'%'
order by seqno+[calcException]
--order by x.quality, seqno+[calcException], x.cost
--drop table #route
--drop table #timecls_selected
--drop table #operator_selected
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Hi! I'm trying to create a query to calculate the number of days between two dates, but I only want to include working days. Is there a way to do this?
View 6 Replies View RelatedI 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 am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?
View 8 Replies View Related 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]
);
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.
View 4 Replies View RelatedHello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.
Hello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.
I have to calculate the total working hours between days, there hours must get automatically round off to nearest value example:
Date :12-05-2013 time : 4:15 will change to 4.00 and if Date :13-05-2013 time: 4:25 then needs to 4.30 and sum the above total hours and results Total : 8.30 hrs.
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.
Iam trying to calculate the number of working days between two dates. Iam getting the uouput as only 1 02 r working days??
select building_number as SchoolID,building_name as Campus, count( distinct( CASE WHEN(( DATEPART(dw, CurDate) + @@DATEFIRST)%7 NOT IN (0,1)) tHEN 1 ELSE 0 END)) as NumberofDaysServed from Sales sl join Buildings b on sl.Building_Num =b.Building_number join students2 s on s.Student_Number= sl.Student_Num join Sale_Items SI on si.UID = sl.UID where CONVERT(CHAR(10),CurDate,120) between '2015-05-01' and '2015-05-07' and VoidReview <> 'v' and SI.INum = '1' group by building_number,building_name order by building_number,Building_Name;
I am trying to compute average of Sales amount for 10 days and I am having an issue when using LAG function. Since weekends fall in between working days my LAG (10) function is also including weekends and hence I am not able to get the correct average.
For ex: D1 - 10 , D2 - 20, D3 - 30, D4 - 50, D5 - 10, D6 - W, D7 - W, D8-10, D9 - 20, D10 -30, D11 - 10, D12 - 40, D13 - W, D14 - W
I am using a sample script
SUM([Time].[Hierarchy].CurrentMEMBER : Time.[Hierarchy].Currentmember.Lag(9), [Measures].[Sales Amount])/10
When I use this and When my current member is D12 Then I am getting the value as 200/10 = 20 (from D12 to D3).
However ideally it should be 230/10 =23 (from D12 to D1) and lag should exclude D6 and D7 as it is a weekend.
I also have a working day flag in my time dimensions which says 0 for non working days and 1 for working days.
Hello
I have an accounts table in which i have a date, credit,debit,balance
columns.I gave the datatype for date column as datetime.But after entering the values,Date is displayed in a different format.How to display the date in our specified format.
And also,in the balance column i want to store the balance.amd when ever there is a credit or debit,i want to add or subtract from the balance correspondingly.How do i manipulate this .
Thanks In Advance
Poornima
Hi,
Does anyone has the DAYS360 excel formula in a function in sqlserver ?? I did this one
FUNCTION dbo.fnDays360_EXCEL
(
@startDate DateTime,
@endDate DateTime
)
RETURNS int
AS
BEGIN
RETURN (
(CASE
WHEN Day(@endDate)=31 THEN 30
ELSE Day(@endDate)
END) -
(CASE
WHEN Day(@startDate)=31 THEN 30
ELSE Day(@startDate)
END)
+ ((DatePart(m, @endDate) + (DatePart(yyyy, @endDate) * 12))
-(DatePart(m, @startDate) + (DatePart(yyyy, @startDate) * 12))) * 30)
END
But there is a bug, if the end date is bigger then february, february must have 30 days and not 28 or 29...
Does anyone has the solution ?
Thanks
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 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 RelatedI 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...
Hi...
I made some code which is to return DataReader.
And then I want to use it in other procedure. But I don't know how to extract value from Reader.
Public Function GetMyProfile(ByVal uid As string) As SqlDataReader
Using conn As New SqlConnection(ConnString)
.
.
Dim Reader As SqlDataReader = cmd.ExecuteReader()Return Reader.RecordsAffectedcmd.Dispose()End UsingEnd Function
Sub Button_Click()
dim dr as SqlDataReader
dr=GetMyProfile(ByVal uid As string)
....Is it right way ?
End Sub
I have a sql query where i want to find the highest number in a column. I believe i am querying for the data properly, but i think my problem is that i don't know how to retrieve that information in code. The error message i get is this:"Conversion from type 'DBNull' to type 'Integer' is not valid"Here is my code:------------------------------------------------------------- queryString = "SELECT MAX(SortOrder) as HighestNumber " & _ "FROM uQI_Questions WHERE (SurveyID = @SurveyID)"........ more code in between ....... ' Make sure there is 1 and only 1 record returned. If ds.Tables(0).Rows.Count = 1 Then ' retrieve the number and add 1 to it. NextNumber = ds.Tables(0).Rows(0)("HighestNumber") NextNumber += 1 Else ' Else it must be the FIRST record so return '1'. NextNumber = 1 End If-------------------------------------------------------------The line that generates the error is "NextNumber = ds.Tables(0).Rows(0)("HighestNumber")".Any suggestions?
View 1 Replies View RelatedWorking on sqlserver 2008 R2
CREATE TABLE OrderRanking
(
OrderID INT IDENTITY(1,1) NOT NULL,
CustomerID INT,
OrderDate date
)
INSERT OrderRanking (CustomerID, OrderDate)
[Code] ...
Looks fine but what I need is DRP with this:
CustomerID OrderDate 'DRP taking care of the gap in the days'
1 '01-01-2015' 1
1 '01-01-2015' 1
2 '02-01-2015' 1
2 '02-01-2015' 1
2 '05-01-2015' 4
2 '05-01-2015' 4
I have events which require certain things be done several days before the event and things be done several days after the event. I attempted to use the DATEADD function to subtract 3 days from the event date. The SQL Statement I created did just that, but, it displays 3 days back from today's date.
There are 2 tables:
CalendarCategories -- Table
CalCategoryID -- int field
CalCategoryName -- varchar field
CalendarEvents -- Table
CalCategoryID -- int Field
Title -- varchar Field
StartDate -- DateTime Field
I have to perform some tasks 3 days before the event. So, TODAY, I want to see a listing of those events which are scheduled for 3 days FROM NOW.
This is my current SQL Statement:
SELECT DATEADD(d,-3,StartDate) AS [Update Payoffs & Ins], Title AS [Closing Description] FROM CalendarEvents WHERE datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate())
This SQL Statement takes TODAY'S events and gives them a date of February 20. See example of the results at http://www.joelwilliamslaw.com/DesktopDefault.aspx?tabid=141
This is what I have already done relative to my calendar listings:
Specific Event Types for the Current Month :
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories
on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID
where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Specific Event Types for the Current Day:
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories
on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID
where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Your assistaince is much appreciated.
Joel