I have a table that hold a couple of fields.
STARTDATE - The day the service starts
FREQTYPE - This field tells me how often a schedule occurs. Here are the vaules -1=oncall, 0=days, 1=weeks, 2=months
FREQPERIOD - This is the number of days, weeks, or months between services. a value of -1 would indicate being oncall.
DayOfWeek, Day of week for week or month-based frequencies 0=sunday 6=saturday -1=if on call
WEEKOFMONTH - week of month for month based frequencies 1-4, value of 5 if it the last week of the month,
So what I need is for this report to look at the start date then figure out what the frequency is (days, weeks, months) then look at the frequency period (how many days are between services) what day of the week it falls on and list each service date on a report. There is an example at the bottom
I have tried numerous things and I can't get it and I need this asap today. I can provide what ever anybody needs, Many thanks.
Here is sample data with the relationships
http://www.balzoutonline.com/reporting/database.jpg
Here is the service table schema
http://www.balzoutonline.com/reporting/service.jpg
Here is the serviceinfo table schema
http://www.balzoutonline.com/reporting/serviceinfo.jpg
Here is the serviceschedule table schema
http://www.balzoutonline.com/report...iceschedule.jpg
And here is the site table schema
http://www.balzoutonline.com/reporting/site.pdf
Here is something that was created in excel wich is pretty easy but a manual process. This is what I would like to replicate in crystal
http://www.balzoutonline.com/reporting/Schedule.xls
Hi, How can i calculate the date out? i am using vb sql server database...db saved my date as dd/MM/yyyy, form display my date as dd/MM/yyyy too 1.I have a selected date by user from calendar in tb_dop.text 2.creditDate.text for user to enter number of days to add to tb_dop.text date 3.dDate.text to display the calculated date Private Sub Calendar1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged 'display selected date from calendar tb_dop.Text = Calendar1.SelectedDate() End SubPrivate Sub btn_add2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_add2.ClickDim myDate As New DateTime myDate = tb_dop.Text Dim i As Integer i = creditDate.TextDim dDate As New DateTime dDate = myDate.AddDays(i) dueDate.Text = dDate ERROR The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.The statement has been terminated. I know it is becos diff format of date calculation, there4, how can i change it to calculate in mm/dd/yyyy format? Funny rite? i can saved and display in dd/MM/yyyy but i cant calculate using this format Note: i cant change my form display format to mm/dd/yyyy cos i need it to be user friendly in my country ty (URGENT) Once again thanks
I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.
hello everyone, I have a problem of calculating a date, for example, how do i find out the begining date of the week and ending date of the week for certain date, and how do i find out the beginning date of the month and end date of the month for a certain date, thanks
Hi All! I need a query to find all dates from today to one-year back. If I start from today day I need find all dates until 11/12/98. Thanks a lot. Greg.
1. I have data something like this: start 07.30 end 16.00 How can i count how many hours and minutes from start to end?
2. Another data start: 20050805 -> August 5, 2005 end: 20050810 How can I return value that when i insert 20050809 it is between start and end, and if 20050811 it will say false that the date is between start and end
Can someone please guide me how to extract this particular data. It is quite complex, the raw data is not in a good shape and I am not sure if it is even possible. For simplification, I am copying a sample of only 3 columns from my table.
From and to date represent a duration of an event, but in some instances the data has multiple lines but they are all to be considered one event. For example all the from dates (row 1 to 8) in Oct 06 are covered by ID/row 05 because 6/9/06 to 6/12/06 is one event, all other rows of June 06 data are just redundant and not needed in the resultset. There isn't a unique event name either so comparing and picking row 05 just based on the above data is an issue. Another problem is for example the dates in October (10/5 - 10-16) all represent 1 event but the date span is spread all over. Multiple events are also possible in one month, so gettign MIN and MAX based on the month will not work.
The desired result set need to have duration of each unique event for example (1 in June, 1 in August, 1 in Sept, and 1 in Oct) and also need to have the number of events after that date and the calculated from and to date of that event:
ID FromDate ToDate Events after this date NextEventFromDate NextEventToDate 05 06/09/2006 06/12/2006 4 08/30/2006 09/05/2006 09 08/30/2006 09/05/2006 3 0913/2006 09/26/2006 13 09/13/2006 09/26/2006 2 10/05/2006 10/16/2006 ? 10/05/2006 10/16/2006 1 10/20/2006 10/25/2006 ? 10/20/2006 10/25/2006 0
I created a Year To Date Calculation via the Buisness Intellligence Wizard. The Script is as follows:
( [Acctg Date].[Acctg Time Acctg Date Calculations].[Year to Date], [Acctg Date].[AcctgYear].[AcctgYear].Members ) = Aggregate( { [Acctg Date].[Acctg Time Acctg Date Calculations].DefaultMember } * PeriodsToDate( [Acctg Date].[Acctg Time].[AcctgYear], [Acctg Date].[Acctg Time].CurrentMember ) )
Now I'd like to edit it so that it produces "Inception To Date" to date ("Inception To Date" picks up all amounts from the beginning which in my case is 2002, as opposed to Year To Date which is from the beginning of the year).
To change to Inception To Date can I change the PeriodsToDate function to:
I am doing monthly reporting for whole months, all starting on the 1st of each month and finishing on the last day of the month I use dateAdd to calculate the end of the month - so I add 1 month and subtract 1 second (start time is always 00:00:00).
--dFrom is Jan 01 2008 00:00:00 select @dTo=dateadd(ss,-1,dateadd(mm,1, @dFrom))
when I print dTo it is not Jan 31 2008 23:59:59 but Feb 01 2008. Why is this? Only starts to behave when I set the number of seconds I am adding from -1 to -31.
Time of date is unimportant: as storing all dates as midday (any data type which ONLY supports date? not interested in the time really).
Subtracting one minute from midnight works fine, but that said: I am curious to understand why I am getting the funny behaviour above.
1)Where do I do the dateDiff calculation, at report or cube level? 2)How do I work out which dates belong to the above groups? I'm assuming i have to check if the dateDiff lies between those numbers?
I have some location assignment data that I need to convert. I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.
E.g. Account 1 stayed in USA for 31 days in January, and 15 days in February. Account 1 stayed in UK for 13 days in February and 26 days in March. Etc.
create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime) insert into #temp select 1,'USA','2014-01-01','2014-02-15' insert into #temp select 1,'UK','2014-02-16','2014-03-26' insert into #temp select 1,'AU','2014-03-27','2014-06-07' insert into #temp select 2,'UK','2014-08-15','2014-09-01' insert into #temp select 2,'AU','2014-09-02','2014-10-17' select * from #temp drop table #temp
Hi experts,I am working on SQL server 2005 reporting services and i am getting aproblem in writting a query.Situation is given below.There is one table in database Named ChildNow i have to find the All childrens whoes Age is 13 years Base onSome given parameter.If User select Augus 2007 then It has to calculate the Childs who bornin August 1994 And if he select September Then queryshould show only those child Who born in September 1994 and soon..... And use can select another year month also likeAugust 2009 ...I am writting the following querySelect Child_Name, DOb from Childwhere ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)If i know already month and year then i can write easily parameter1and parameter2 But since these are comming from user so i m notfinding how to handle this.Now please suggest me what i have to write in Where statement I thinka lot but not getting any idea about it.Any help wil be appriciated.RegardsDinesh
I've read this file in PowerPivot and I need to calculate the time in weeks between the two dates for the same JO, Candidate and when the to_StatusId = From_StatusID. In other words, the number of weeks that took from going From Status "1" to Status "2".
Is it possible to do something like this using DAX? Do I need to create a calculated Column?
I’m using DAX to calculate the prior MTD count of a specific column. My data ends on 2/8/2013 and that day's PriorMTD is incorrectly corresponding to 1/31/2013. Whereas, the previous 7 days in February correctly match their corresponding January dates..Below is an image of my pivot table and I have outlined the values in red that are in question.Below are my DAX formulas used each column visible in my image:
Distinct Count of Events:=DISTINCTCOUNT([EventID])CurrentMTD:=CALCULATE([Distinct Count of Events], DATESMTD(Events[EventDate]), ALL (dimDate) )PriorMTD:=CALCULATE([Distinct Count of Events], DATEADD(DATESMTD(Events[EventDate]), -1, MONTH), all(dimDate)) ParallelMonth:= CALCULATE ([Distinct Count of Events], ParallelPeriod(Events[EventDate], -1, MONTH), ALL(dimDate))
I have a fact table with sales data at line item level, meaning lots of rows with sales orders, revenue etc. per product group pr month. Then I have a budget table with the budgetted revenue pr product group pr month, meaning a lot less detailed. I followed the method at daxpatterns.com/budget-patterns/ with the headline "Complete pattern" to make it work. Now I have a working data model where I can see actual and budget as seperate values.
I then need to calculate the actual revenue month to date and the budgetted revenue month to date. For actual it has worked fine by doing:
The problem occurs for me doing the same on the budget figure. Since I don't have a budget per day, but only at "YearMonth" level, I have calculated the amount of workdays per month and then used the DatesMTD formula to get the cumulative workdays during the month. Furthermore I calculated the total amount of workdays in the month and made a ratio between this figure and the cumulative workdays so I get a figure in percent that tells me per day how much of the month that has went by (0-100 %). I would then multiply this percentage with the budget and get the budget month to date. This does not work since my budget figure is not at day level.
How I can get it to work. My desired result will be all the dates during a month in my rows and then actual revenue Month to date in values and a corrosponding figure for the budget.
I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive. I'm sure this is simple, but I work with dates so infrequently that I need a refressher.
Hi, I really need this help urgently. I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email... Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load con1.Open() Dim cmd As New SqlCommand cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)" 'Set the connect the command object should use cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet da.Fill(ds) con1.Close() If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then Dim objEmail As MailMessage = New MailMessage objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg")) objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName") objEmail.Priority = MailPriority.Normal Dim SmtpMail As New SmtpClient("servername") SmtpMail.Send(objEmail) End If End If End Sub Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy) Realli veri urgent Thanks so much for ur'll help
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query sum(CASE WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31 WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0 ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)) AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2 ((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
Dear All expertise please help me becuase I am very beginner
I have a Holiday Table in which contain each employee's holiday from date and todate . I want to get an sql to get the record having holiday from the given date range
example
Employee A having holiday from 01-01-2008 to 06-01-2008 Employee B having holiday from 05-01-2008 to 10-01-2008 Employee C having holiday from 20-01-2008 to 30-01-2008
I given the criteria as follows to get list of employeed having holiday in given date range
Holiday from 01-01-2008 to 06-01-2008 . So Employee A and Employee B should come in the list. Please give me an SQL
Hi, I am using a sql database and vb.net... In form i display a date from calendar as smalldatetime in the format of dd/MM/yyyy by changing the web.config file to culture = "en-GB"... It did display as dd/MM/yyyy with no doubt... Problem is in sql database it is display as mm/dd/yyyy, there4 when i retrieve it out, it is not the format i wan... so can any1 helps mi? I need do many adding and subtracting to the date so a constant dd/MM/yyyy format is veri important to mi... I cant save it as yyyy/mm/dd cos my boss dun wan it... If use Convert method when i select * out how to convert the date zzz Helps would be greatly appreciated
Hello all .. I have a form that views the date automatically in a textbox... the date of this text box must be stored in the database after the user press the submit button ... However, I am getting a wrong date which is 01/01/1900 .. although I see it in the form as 15/01/2008 .. this is the code ... please give me a solution ASAP ..
Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click 'Declaring the variablesDim objConnection As SqlConnection Dim objDataCommand As SqlCommand Dim courseId, traineeName, traineeId, department, comment1, comment2 As String Dim formDate As Date Dim RB1, RB2, RB3, RB4, RB5, RB6, RB7, RB8, RB9, RB10, RB11, RB12, RB13, RB14 As String Dim ConnectionString, evaluationSQL, TraSQL, iTotal As String 'Save form values in variables formDate = Convert.ToDateTime(DateTxt.Text) courseId = CourseIDTxt.Text traineeId = EmailTxt.Text traineeName = TraineeNTxt.Text department = DeptDropDownList.SelectedValue RB1 = RBL1.SelectedValue RB2 = RBL2.SelectedValue RB3 = RBL3.SelectedValue RB4 = RBL4.SelectedValue RB5 = RBL5.SelectedValue RB6 = RBL6.SelectedValue RB7 = RBL7.SelectedValue RB8 = RBL8.SelectedValue RB9 = RBL9.SelectedValue RB10 = RBL10.SelectedValue RB11 = RBL11.SelectedValue RB12 = RBL12.SelectedValue RB13 = RBL13.SelectedValue RB14 = RBL14.SelectedValue comment1 = CommentTxt1.Text comment2 = CommentTxt2.TextConnectionString = WebConfigurationManager.ConnectionStrings("GRPConnectionString").ConnectionString 'Create and open the connection objConnection = New SqlConnection(ConnectionString) objConnection.Open()evaluationSQL = "Insert into Evaluation (Eva_Date, CourseD_Id, Tra_Id , Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Comment1, Comment2 ) " & _ "values(" & formDate & ",'" & courseId & "','" & traineeId & "','" & RB1 & "','" & RB2 & "','" & RB3 & "','" & RB4 & "','" & RB5 & "','" & RB6 & "','" & RB7 & "','" & RB8 & "','" & RB9 & "','" & RB10 & "','" & RB11 & "','" & RB12 & "','" & RB13 & "','" & RB14 & "','" & comment1 & "','" & comment2 & "')"objDataCommand = New SqlCommand(evaluationSQL, objConnection) objDataCommand.ExecuteNonQuery()
Hi, I have a query as follows:select transactionDate, (select count(transID) from ......)I am having a problem which is that dates are not shown uniquely.For example,oct 1, 2005 00:12:00 - 12oct 1, 2005 01:30:00 - 12oct 1, 2005 01:13:00 - 12I want only to have:oct 1, 2005 01:13:00 - 12The select is working on time and not on a date, can you help me please?thank you.
I've a column of Date/Time type 'SenOn' in which NULL is allowed. If I enter some date in that column, and then next time again I want to make it empty (NULL I mean) there is an error that this value cant be changed.
Please note that there are no dependencies, so that is certainly not the problem.
Hi,What I have is a booking table, and when updating/inserting I need toensure that there are no date/time overlaps. The problem I'm having isthat while the following script works for events on the same day, itfails miserably when a booking starts on a previous day.I've just spent the last hour going through previous posts and justcan't seem to it right.My DB structure (Sql Server 2000):Table: CollateralBooking-- CBID - int, identity(1, 1)-- CBcPartNumber - varchar(50) (foreign key)-- CBdDateTimeFrom - smalldatetime-- CBdDateTimeTo - smalldatetime-- CBcAlias - varchar(50) (foreign key)My current script (in a stored proc):IF (SELECT COUNT(*) FROM CollateralBookingWHERE (((@CBdDateTimeFrom > CBdDateTimeFrom) AND (@CBdDateTimeFrom < CBdDateTimeTo))OR ((@CBdDateTimeTo > CBdDateTimeFrom) AND (@CBdDateTimeTo < CBdDateTimeTo)))AND (CBcPartNumber = @CBcPartNumber)) <> 0BEGIN-- Return an error.END-- ... Other checks & finally, the insert/update.--Posted via http://dbforums.com
I have a date "4/25/2007" but the requirement is to have data as "04/25/2007" .if month is single digit it should have leading zeroes, same with date, can some tell me how to convert this.
