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'
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
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'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?
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?
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] );
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.
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 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?
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;
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)
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.
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 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?
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.
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.
hi. i'm trying to develop a report for a library to display a list of overdue books. i want it to display a list of books which are 15+ days over due. i thought i would use the DATEADD function, but it doesn't seem like it's working.
I have this in my where clause:
Code:
BookEvents.EventDate<DATEADD("d",-15,GETDATE())
Is this right? I think I'm supposed to have more books showing up. Thanks for any help!
I have a requirement where i need to calculate the age of a work order excluding the Weekends (Sat,Sun) in an SQL table, this i need to updated as a formula for a particulay column in the SQl table so when a task startdate is enterred and submitted the Age field gets populated with the number of working days.
Example: startdate = '04/09/2010' currentdate = '04/12/2010' the result should show 1 day and not 3 days.
Hi, I've searched quite a bit for help with this syntax but have given up. I need help with the where clause of a query using SQL SERVER that selects records a certain number of days before the current date. I have tried this and it's incorrect syntax: WHERE (fldDate < ({ fn NOW() - 500 }) Can someone please help me out with correct syntax for this? thanks much.
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
I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and
'17:00:00' order by [DateTime]
However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and
'09:00:00' order by [DateTime]
I need a way to indicate that the start and end times span two days. Can anybody help with this?
Hi there, I just upsized my access database which has several tables and query linked to one of the tables residing on a seperate access database. When I do add in the diagram section, I see only table, then my question how would I be able to do the same thing under SQL Server 2005? thank you
I'm trying to query an SQL table column with date values to show 8 Days ago results.
I've started with this query:
SELECT ficheiro, erro, descritivo_erro, contrato, DO, movimento, data, descritivo, tipo_movimento, desconto, montante, comissao, IVA FROM status_day WHERE (YEAR(data) = YEAR(GETDATE())) AND (MONTH(data) = MONTH(GETDATE())) AND (DAY(data) = DAY(GETDATE()) -8) ORDER BY descritivo_erro, contrato
The problem is that the text in red will have some problems when the month changes - If I want the 8 days ago results from January and the system date is 1st of February the query will not return any values.
I read something about DATESERIAL but is wasn't conclusive on how to use it with system date.
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
