I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
I understand how to find (generate) missing dates for the year 2006 if I have a range value like 1-1-2006 to 3-1-2006. (I'm just using 2006 for arguements sake - the needed approach starts back in 2004 or so and will extend to the current day)
Now the question that presents itself is, is there an elegant way to do this same process with an arbitrary number of date ranges per customer? There would be 1 record per range, per customer
What I'd like to generate is a record per missing date in 2006, which would be: 4 records for 1-1-2006 to 1-4-2006 several records for 2-1-2006 to 10-31-2006 and then more for 11-18-2006 to 12-14-2006
As I said before, the number of ranges isn't static. It could be 1 or a dozen and could increase as time goes on. At this time, all I can see to potentially make it work in query is to do a dozen joins - which kind of sucks.
My other thought is to do specific processing per customer, per range gap, but it will be terribly slow.
I want generating Valid date ranges from any list of dates.
The List of Dates could be generated from the below TSQL -
SELECT '2015-06-02' [Date] UNION ALL SELECT '2015-06-13' UNION ALL SELECT '2015-06-14' UNION ALL SELECT '2015-06-15' UNION ALL SELECT '2015-06-16' UNION ALL SELECT '2015-06-22' UNION ALL SELECT '2015-06-23' UNION ALL SELECT '2015-06-24'
And the expected output should look like -
SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL SELECT '2015-06-22' FromDate, '2015-06-24' ToDate
I have a table in which records are inserted daily and with them i am storing the dates also. Now in SSRS i need to show the data for one week . The format should be like :
In above example Business Name , Phase, Activity will repeat lonely but its work description will be generated in next section according to that business name and that date. How to achieve this task ? I have referred : [URL] ....
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID Quarterly Monthly Days Period Year BARmRev AARmRev PYRmRev
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 3462.14 5107.65
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 NULL NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
Hello All, I am trying to Add certain number of days to a particular date. and my requirement is that it need to exclude all saturdays and sundays and then give me the resultant date in Sqlserver. Please can anyone help me in achieving it. thanks Shiva Kumar
Hi can anyone help me , or am I on the wrong track.
Is there any easy way to create a stored procedure that inserts into a table the relevant months dates into a table based on the month and year as parameters. I.e say the parameters passed are 01/2001 hence based on this all of the month of January 2001 dates are inserted into a table in this format : 'Jan 01 2001 12:00:00'
We are deploying a system in the UK in SQL Server 6.5. They want the date to default to dd/mm/yy. Is there a way to set this on a permanent basis? Is there a way to set the server so any time a date is display, including getdate(), the result is dd/mm/yy?
I have a table which contains apart from other fields, 2 dates. i.e. startdate and enddate.
The startdate is always less than / equal to enddate.
I want to write a stored proc where I will give a date as a parameter and it should give me all the records there my date is between the startdate and the enddate.
Also I want to create a temp table in the procedure and the table gets populated with all the dates that comes between startdate and enddate, both dates inclusive.
I know this might be a tall order, but I am new to writing stored procedure, so I will be obliged if somebody can help me out
I have an assignment using SQL this is the first time i have used it but i can understand the basics.
It is for an instrument service company and i need to find what instruments have not been serviced for a year. i already know i have to:
SELECT instrument number, date of last service FROM Instrument table
but after this i am unsure what code i can use in order to find all instruments that have not been servided in over 365 days what code can is best to use?
I have a columns with storeID's and dates i need to be able to find the difference of date for a certain store for its Min(Date) to the End of that Month
example
Date StoreID Difference of Date to end of the Month 2008-01-02 00:00:00.000 163 2008-01-03 00:00:00.000 165 2008-01-01 00:00:00.000 167 2008-01-02 00:00:00.000 180
I am trying to calculate our job value and product costs over a given time period. But if I run my query, the numbers just dont look right at all. Please can you have a look at this query and see if anything stands out?
-- FINISHED PRODUCTS PRODUCTION REPORTS DATA -- gets current WIP Job information. -- requires item details from this. USE Staging -- declare start and end variables DECLARE @Start DATETIME DECLARE @End DATETIME SET DATEFORMAT DMY SET @Start = '01/01/2008' SET @End = '01/02/2008' SELECT SUM(jbs.Value) AS ProducionValue, SUM(prd.Cost) AS Cost, CONVERT(CHAR(10), @Start, 103) AS [From], CONVERT(CHAR(10), @End, 103) AS [End] FROM ( SELECT Job.[Job No], Job.[Cust Name], Job.[Req'd Date], Job.ProdStartActual, Job.Dept, [Job].[Value], Job.Loan FROM Job WHERE Job.[ProdStartActual] BETWEEN @start AND @End AND Job.Cancel = 0 AND Job.Sales = 1 AND Job.Invoiced = 1 OR Job.[ProdStartActual] BETWEEN @start AND @End AND Job.Cancel = 0 AND Job.Sales = 1 AND Job.Invoiced = 2 ) AS jbs INNER JOIN ( SELECT Item.[Job No], Item.[Code] FROM Item ) AS itm ON jbs.[Job No] = itm.[Job No] INNER JOIN ( SELECT Product.[Product Code], Product.[Cost] FROM Product ) AS prd ON itm.Code = prd.[Product Code]
I've got a table (TYPE, DAYMTH) where it is a compound key and theDAYMTH is a datetime but all years are 1900 ONLY THE day and month aresignificant.What I want to do is for each type, find the next n dates - so forexample:TYPE DAYMTH1 1900-02-201 1900-05-051 1900-09-141 1900-11-112 1900-03-032 1900-07-07if I want to get the next 3 dates after 22 June 2005 it should give me:1 2005-09-141 2005-11-111 2006-02-202 2005-07-072 2006-03-032 2006-07-07so, anyone got any bright ideas?I've started down this route:create table #year (yr int)insert into #year values(2005)insert into #year values(2006)insert into #year values(2007)insert into #year values(2008)select *,convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20)from DAYMTHtable,#yearwhere convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20) > getdate()order by type,convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20)and although this seems to be a good start I wonder if there are bettersolutions. Also any quick ideas on how to finish if I decide to followmy existing thought process?thanksPhil
For some reason guys my SQL string isn't working and I'm probably doingsomething stupid?SELECT *FROM sol_sessionSELECT *FROM SOL_SessionWHERE DateGiven '10/09/2006' AND <'13/09/2006'The 'DateGiven' field is a DateTime field, and I can't see quite whatswrong??I simply get a Syntax error!Cheers, Ash
To support an application, I am integrating it with with Sqlserver2000. The task is simple: get the data at regular intervals, andwrite it into a table dedicated for that application. One of thefields I receive is a date, and it is formatted as an ISO date. Asample value looks like this: "2004-07-09-15.37.52.077258".SQLServer doesn't support this date format, though. The closest Ihave found is the style code "21" or "121" which accepts input as"yyyy-mm-dd hh:mi:ss.mmm". I have looked at using several replace()and convert() calls but no solution yet.Is there a way to transform the data to conform to this style acceptedby Sqlserver? I would like to do this in the INSERT statement itselfand not have to code application logic (i.e., user-defined function,etc.) for it.
HiI would like to create a SP where it will populate TableA based from TableB.TableB will be populated on a monthly basis using a DTS and within that Iwould like to run the SP to populate TableA.Can someone here please help me create the sql statements as a startingpoint.TIA!BobTableB (source)from_date to_date curr_code ex_rate1/1/2004 1/10/2004 CAD .750001/11/2004 1/16/2004 CAD .743211/17/2004 2/4/2004 CAD .722222/5/2004 2/20/2004 CAD .711112/21/2004 2/28/2004 CAD .778883/1/2004 3/3/2004 CAD .790023/4/2004 3/14/2004 CAD .768033/15/2004 3/23/2004 CAD .700223/24/2004 4/2/2004 CAD .73365etc...TableA (destination):date curr_code ex_rate1/2004 CAD 0.738477 calculation:(.75000+..74321+.72222) / 32/2004 CAD 0.737403(.72222+.71111+.77888) / 33/2004 CAD 0.74798(.79002+.76803+.70022+.73365) / 4etc..
Hi. I hope this is the write place to post this, I hope it is.
basically, I want to be able to return me a query of giving me available dates in a table.
What I mean is that, if I have a table with fields including start date and end date, I want to return me the dates which are NOT listed/stored in the table.
kind of like a booking system, where you store the room reservation dates and obviously you don't want to double book that room, so I would like all the available dates to be returned back.
Hello people! I have a question: I have a datetime field in my database. When I add a datetime variable (in 12.03.2008 format) to stored procedure (from C# code) that actually updates that field - everything ok. but when I am trying to update a datetime field with string 12.03.2008 (no matter nvarchar or datetime type) in T-SQL code - the date that i receive - 2008-12-03, it means that month and day are swapped! How can I fix it? Thanks.
I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:
I have an application where there is a form to enter some data to the sql server 2005 database. when the form is submitted, I also use Date.Now to insert the date the article was submitted. It enters fine. E.g. if I submitted it today, it would display 08/06/2006 (which is 06 August 2006). When getting the records, I use, "dd MMM yyyy" to format the date according to my culture. However, the result I get is 08 June 2006 What might be the problem. I hate working with dates with sql server
Hi,I have an SQl query that will list the results if it lies between two dates, for example;SELECT * FROM TABLE WHERE { fn Now() } BETWEEN Date1 AND Date2This returns all results where Date1 and Date2 fall between the Current DateWhat i am looking for is a way to replace the { fn Now() } with a date of my choice.For example;SELECT * FROM TABLE WHERE '10/10/2006' BETWEEN Date1 AND Date2However this does not work.Has anyone any ideas why this may be ??Thanks in advanceAndrew Vint
I need to compare tow dates DateField1 and DateField2 and find number of hours between these two dates. Then I need to deduct non-business days and hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from this and find net hours. How can I do this?
