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
Hi I have got two tables and have one to many relationship on them. i ll show you the sample data
Table 1 ID DoctorName Practice Full_Time Cost Expenditure 1 ABC qw yes $100 2 ABD wer no $566 3 ZXA ddf yes $22 ........................
table 2 Practices qw wer ffgg hhjk ddf .. The scenario is like this a doctor can work in more than one practice. and he can be a full time practioner or a part time practitioner
The problem is if a doctor is a full time practioner in 1 service and a part time practitioner in another, 75% of cost goes to first practice and the rest 25% goes to other
if the doctor is non full time in more than one practice, the cost gets distributed equally in all practices.
I've been asked to find the Mode of the loads on trips we run. So I run a count on the [loads] (without grouping, in an analytical function) column, then sort on the highest counts. The problem is when there is a tie. In that case, I need to find the mean of all the tied values. This means that if there is no mode at all, it would simply take the mean of all the values.
Here is sample data:
CREATE TABLE test6 ( trip char(1) NULL, date int NULL, loads int NULL
[Code] ...
And here is what I'd like to see:
CREATE TABLE test7 ( trip char(1) NULL, loads int NULL ) insert into test (trip, loads) values ('A', 15) insert into test (trip, loads) values ('B', 17) insert into test (trip, loads) values ('C', 11)
hi need help to solved date calculation for next month i explain i have table employee and the employee insert into table the holidays the date start >>>> to date end now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month if the dates is not for the next month i don't need to see it
like this example 09/07/2008 > 09/08/2008 (is not for next month) like this example 10/09/2008 > 12/09/2008 (is not for next month)
555 EEE
09/07/2008 09/08/2008
4
666 fff 10/09/2008 12/09/2008
1
in this example i need to see only the relative dates for next month only in the view
333 cccc
01/05/2008 15/05/2008
4
4
333 cccc
01/05/2008 31/05/2008
1
30
tb_all_holiday before
id
fname
Start_Date
EndDate
val_holiday
111 aaaa
15/03/2008 10/05/2008
1
222 bbbb
02/05/2008 31/05/2008
3
333 cccc
03/04/2008 15/05/2008
4
333 cccc
29/04/2008 07/07/2008
1
444 dddd
01/05/2008 02/05/2008
1
444 dddd
09/05/2008 19/08/2008
1
555 EEE
09/07/2008 09/08/2008
4
666 fff 10/09/2008 12/09/2008
1 VIEW_all_holiday after -next month only
id
fname
Start_Date
EndDate
val_holiday sum_day_next_month
111 aaaa
01/05/2008 10/05/2008
1
4
222 bbbb
02/05/2008 31/05/2008
3
29
333 cccc
01/05/2008 15/05/2008
4
4
333 cccc
01/05/2008 31/05/2008
1
30
444 dddd
01/05/2008 02/05/2008
1
1
444 dddd
09/05/2008 31/05/2008
1
22
all the time i need to see only the relative dates for the next month only
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
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
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.
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
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 hope I explain myself clear enough. I have an integer field of date values: 20031231. Some of the values in the field are zero. I want to convert the integer to 12/31/2003. Right now I am doing it with 2 views. The first view takes the zeros and converts them to null by using case. The second view uses convert to make it into the date string I want. Is there some way I can do it all in one view? Thanks
IF the date (Date1) diffrence between any two dates for the same ID is less than 30 Days CNT1 = 1, ELSE CNT1 =0 IF the date (Date1) diffrence between any two dates for the same ID is less than 60 Days CNT2 = 1, ELSE CNT2 =0 IF the date (Date1) diffrence between any two dates for the same ID is less than 90 Days CNT3 = 1, ELSE CNT1 =0
Note : CNT1,CNT2 and CNT3 for ID =2,3,5 and 8 should be 0 because there are no multiple recors for the same ID
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.
This concerns eligibility healthcare information. A member can have multiple rows in the table showing they are eligible for different date ranges with different health plans. eff_date and term_date are the fields in this table. Term_date can be NULL.
I need a WHERE statement that shows members 1) eligible between 1/1/2004 and 8/15/2004
(term_date is null or term_date >= '8/15/2004') and eff_date <='1/1/2004'
2) members are allowed only one gap in this timeframe of up to 45 days. *NEED HELP
3) a gap of 1 day should not be counted as a gap in enrollment. *NEED HELP
I have a table with date ranges for activitys. I need to get the userid , firstdayidle, lastdayidle for when a user has been idle in the last 90 days. it gets tricky since it also must include users that have no records in activity table or only one record so it has 2 idle periods, etc
I have a hard time wrapping my head around the logic, weither using a datetables or not, so any help would be appreaciated
--my first step is to get the activitys that enter the last 90 days select * from Activity2 where (enddate > DateAdd(d, -90, getDate()) AND enddate < getDate()) OR (startdate > DateAdd(d, -90, getDate()) AND startdate < getDate())
CREATE TABLE [dbo].[InternalUser]( [userid] [int],-- IDENTITY(1,1) NOT NULL, [fullname] [varchar](50) } insert into [InternalUser] values(1,'a') insert into [InternalUser] values(2,'b') insert into [InternalUser] values(3,'c') insert into [InternalUser] values(4,'d'); insert into [InternalUser] values(5,'e'); insert into [InternalUser] values(6,'f');
CREATE TABLE [dbo].[Activity2]( [activityid] [int] NOT NULL, --IDENTITY(1,1) [userid] [int] NOT NULL, [startdate] [datetime] NULL, [enddate] [datetime] NULL )
insert into [Activity2] values(1,1,'2007-02-15 00:00:00.000','2008-03-15 00:00:00.000') insert into [Activity2] values(2,2,'2007-01-01 00:00:00.000','2008-01-02 00:00:00.000') insert into [Activity2] values(3,2,'2008-01-20 00:00:00.000','2008-04-10 00:00:00.000') insert into [Activity2] values(4,3,'2008-02-20 00:00:00.000','2008-10-10 00:00:00.000') insert into [Activity2] values(5,4,'2007-01-01 00:00:00.000','2008-01-16 00:00:00.000')
I am new to SSIS, so I am hoping there is an easier way to do this...
I need to evaluate a date in a field and determine if it is between the beginning and end of whatever the current month is... In Access, this was written as something like:
IIF(datevalue >= CDate(Format(Now(),"mm/01/yy")) AND datevalue < CDate(Format(DateAdd("m",1,Now()), "mm/01/yy)), value1, value2)
Trying to recreate this in SSIS using expressions during a derived transformation has been extremely difficult. Here is what I came up with:
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]);
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
