Analysis Services: MDX - Date Ranges With 2 Time Dimensions
Aug 31, 2004
Hi,
I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.
Is there any way that I can have a range of dates?
Is it possible to have 2 time Dimensions for example which did the following:
a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )
Thus I would be able to select a range of dates/months.
Do you know if this is possible to write this inot the dimension?
Thanks,
David
View 3 Replies
ADVERTISEMENT
Oct 23, 2002
In Analysis Services is there a way you can change a private dimension into a shared dimension without having to recreate it
View 1 Replies
View Related
Aug 13, 2004
I have a problem where I have 3 three measures in a virtual cube:
"Actual", "Budget" and "Full Year Budget".
The dimensions I have are:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates
- Budget Name
Both "Actual" & "Budget" measures need to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates (exclusive to "Actual", "Budget")
Thus have put these in one cube
AND "Full Year Budget" needs to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Budget Name (exclusive to "Full Year Budget")
THUS have put this as one cube…
I then created a virtual cube, with the 2 cubes thinking that the dimensions I created in the original cubes would only filter the measures of the original cube measures in the virtual cube. ...BUT all dimension filters in the virtual cube filter all measures in the virtual cube, irrespective of which dimensions were created with the original cubes.
please help!
View 1 Replies
View Related
Jul 24, 2015
I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.
WITH MEMBER [Measures].[New Measure] ASÂ
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))
View 3 Replies
View Related
Jun 1, 2015
Running into an error [BC30205] and no values get colored using this syntax
=iif(DateDiff("d",Fields!Last_Reboot.Value,Now()) > 30 Â And DateDiff("d",Fields!Last_Reboot.Value,Now()) <= 59, "Orange", NOTHING),IIF(DateDiff("d",Fields!Last_Reboot.Value, Now()) >60, "Red",
NOTHING)
View 2 Replies
View Related
Jun 4, 2015
I have an SSRS report with parameters for Created On Start and Created On End. Users run this manually and choose the date range to display records for.
I would like to set up two different subscriptions
1) to send weekly on Monday morning for "last weeks" records and
2) to send monthly to send "last month's" records.
View 2 Replies
View Related
Jun 13, 2015
I'm new to MDX, and most of the time I customize existing queries rather than writing new ones. I currently have a MDX query like this
SELECTÂ
[Measures].[Fees Billed]
         on 0,
except([Age].[Day Buckets].members, {[Age].[Day Buckets].[All], [Age].[Day Buckets].&[Unknown]})
 on 1
FROMÂ
   MyCube
WHERE ([Fiscal Period].[Fiscal Year].&[2015], [Customer].[City].&[Auckland] )
Which brings the fees billed by age buckets where the customer's city is Auckland. I also have another dimension called [Sales Agent] with a member [City] in it, and there is a member in [Customer] called [Customer].[Sales Agent]
I am trying to retrieve the same information where the customer's sales agent's city is Auckland rather than the customer's city.
If it is SQL, I will join Customer and SalesAgent on Customer.SalesAgentUno = SalesAgent.SalesAgentUno and bring in the desired data. Any way in MDX to do this?
View 4 Replies
View Related
Jun 25, 2015
I need to show the dimensions of my model like columns in the result. I have this query
withÂ
member [Measures].[Customer] as [Customers].[Customer].CURRENTMEMBER.Name
member [Measures].[UCs] as [UCs].[UC].CURRENTMEMBER.Name
member [Measures].[Order Type] as [Order Types].[Order Type].CURRENTMEMBER.Name
member [Measures].[UC Dates] as [UC Dates].[UC Date].CURRENTMEMBER.Name
[Code] .....
View 14 Replies
View Related
Oct 22, 2015
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?Â
View 2 Replies
View Related
May 4, 2015
Actually I want to do distinct sum on a measure group, please find the below table as sample
XL Measure group
LKÂ Â Â Â OKÂ Â Â Â Â Amount
1Â Â Â Â Â Â Â 10Â Â Â Â Â Â Â Â 100
1Â Â Â Â Â Â Â Â 11Â Â Â Â Â Â Â Â 100
3Â Â Â Â Â Â Â Â Â 30Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â Â 31Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â Â 32Â Â Â Â Â Â Â 250
For the above measure group two dimensions have relationships, One is L dimension which is having relationship with XL on LK and One is O dimension which is having relationship with XL on OK. If I drag L dimension attributes  it should show results as below
LK LName Amount
1Â Â Â Â AÂ Â Â Â Â Â Â Â 100
3Â Â Â Â CÂ Â Â Â Â Â Â Â Â 250
But above results are coming as below
LK LName Amount
1Â Â Â Â AÂ Â Â Â Â Â Â Â 200
3Â Â Â Â CÂ Â Â Â Â Â Â Â Â 750
If I drag O dimension attributes along with L dimension, it should show results as below.
LK  LName  OK     OKName  Amount
1Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â 10Â Â Â Â Â Â XYZÂ Â Â Â Â Â Â Â 100
1Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â 11Â Â Â Â Â Â UVWÂ Â Â Â Â Â Â 100
3Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â 30Â Â Â Â Â Â PQRÂ Â Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â Â 31Â Â Â Â Â KLMÂ Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â Â 32Â Â Â Â Â TUVÂ Â Â Â Â Â Â Â 250
I used formula Measures.Amount/Measures.Count, this formula is not showing correct results when I don't drag any dimensions, it is showing results for All member as 425, but it should show as 350.
So I made a same change ([L].[LK].Currentmember, Measures.Amount)/([L].[LK].Currentmember,Measures.Count), this worked fine but performance is very low and so stopped working on this.
Atlast I did the measure group like this
LK    OK     LAmount  OAmount
1Â Â Â Â Â Â Â 10Â Â Â Â Â Â Â Â 100Â Â Â Â Â Â Â 100
1Â Â Â Â Â Â Â Â 11Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â 100
3Â Â Â Â Â Â Â Â Â 30Â Â Â Â Â Â Â 300Â Â Â Â Â Â Â 300
3Â Â Â Â Â Â Â Â Â 31Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â 300
I want to show Measures.LAmount when only L dimension is querying and want to show OAmount when both L dimension and O dimension are querying. Is this possible ?
View 3 Replies
View Related
Jul 24, 2015
SSAS 2008 R2
Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension, Â a Hospital dimension and a wait time measure.
For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?
I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.
View 4 Replies
View Related
Aug 4, 2015
I have make a calculated member for previous period of an given date range. The previous period is the same date range from the previous year, and I have managed to achieve that with the calculated member:
Create member currentcube.[Measures].[PrevPeriod] as
(ParallelPeriod( [Start Date].[Cal Hierarchy].[Year], 1, [Start Date].[CAL Hierarchy].CurrentMember), [Measures].[Count]);
This member returns the correct result as long as my query uses the time dimension, which makes sense... but I also need to show results sliced by other dimensions in bar charts that do not display the time dimension. For example, I have a dimension with only 3 members called [Region].[Area].[AreaName].
The result set for the bar chart needs to look like this:
[AreaName]Â | [Count]Â |Â [PrevPeriod]
East           |   43     |      56
West          |   53     |      95
But the [PrevPeriod] only returns values if I include the time dimension. I essentially need to sum the results of the time dimension/AreaName/[PrevPeriod] tuple down to just Areaname/[PrevPeriod] for whatever date range may be involved.
I don't know if this is significant to the issue, but the client tool that generates the bar charts builds the query with the date range as a subcube in the FROM statement. If the [PrevPeriod] is outside of the subcube that is still OK, as long as the time dimension is included in an Axis on the final select statement, so at least I know I am not suffering from the members inside the subcube. I've also found in SSMS that it makes no difference if I make the query a subcube, or put the date range in a where clause instead; I still get NULL for [PrevPeriod] without the dates.
I can't imagine that this is an unusual situation, so I hope I've explained it adequately! What is the recommended technique for summarizing a Parallelperiod by dimensions without displaying the time/dates ?
View 6 Replies
View Related
Sep 22, 2015
I have a cube with 2 many-to-many dimensions where a special mdx query needs about 5 seconds. When I resolve the many to many relationships by multiplying the data in the fact table the query needs 21 seconds.
In general do many-to-many dimensions slow down query performance of a cube?
Without the many-to-many dimensions of course the fact table has much more rows. Could this be the reason for the performance loss?
how to tweak query performance of a cube in general?
View 3 Replies
View Related
Jul 28, 2015
I have a requirement to set Description values for our cube dimensions and attributes.Â
I've done this for regular dimensions but I cant seem to find a way for role playing dimensions. I can set the base dimension descriptions but not the 'clones'. Is this possible?Â
View 2 Replies
View Related
Oct 28, 2010
The all-level of dimensions doesn't show up in the PivotTable Field List? I have reports where I want to show one member of a dimensions compared to the total of the dimension (and not the total of the members shown). But I can't select the ALL-level. Is there any way to do this?
View 5 Replies
View Related
Jul 11, 2013
how to set 'Ignore Unrelated Dimensions' property in Tabular Model.
View 3 Replies
View Related
Jul 5, 2015
I have one dimension and one measure group. I deployed and processed the cube. Now I am able to browse the data. Now I added one more dimension. I deployed and reprocessed again the Cube. Now I am not able to see any values. Â I am getting like below.
View 6 Replies
View Related
Jun 9, 2015
I am using a "Client" dimension that includes a "Holding - Client" hierarchy. I have to make sure, that only the appropriate roles may access appropriate members from this dimension, but I only have the information which role may access which ClientID - I do not have the information which HoldingID should be accessible. Also, "Client" is used as the key column of the dimension with "ClientID" and "HoldingID" as key columns. The hierarchy is strict, no client may belong to multiple holdings.
I cannot seem to find the right MDX for the allowed member set. My MDX expression would need to look like this:
[Client].[Holding - Client].[Client].&[*]&[123]
In this example I want to give access for client &123, no matter the holding, so &1&123 and &2&123 would be allowed.
Is this doable?
View 4 Replies
View Related
Jul 15, 2015
I am modelling two fact tables of Actuals and Budget which are at different granularity, Actuals are at day, customer and product sub category level. Budgets are at month, Region and Product category level.
Month, Region and Product Category is present in Date, Region and Product Category dimension respectively. I have only three dimensions as Customer, Product and Date. Linking those dimensions to Actual Fact table is not an issue, what is the best way and options are there to link budget fact table to those three dimensions.
View 2 Replies
View Related
Jul 6, 2015
I built my first tabular model and see that my fact tables are also appearing as dimensions. In Multi dimensional mode i could choose which are the dimensions. How do i do that in tabular model.
View 2 Replies
View Related
Jun 29, 2015
I am currently creating calculations for weekly and fortnightly ranges by day.
Is it better to use LastPeriods or Tail? For example, the last 14 days could be:
LastPeriods(14,Â
StrToMember
(
"[Date].[Year Week Day].[Date].&[" + FORMAT(Now(),"yyyy-MM-ddT00:00:00")
+"]"
) Â Â
))
Tail(
Filter(
{[Date].[Year Week Day].[Day]},Â
([Date].[Year Week Day].CURRENTMEMBER, [Measures].[Test Measure]) > 0
), 14
)
What is the advantages or disadvantages?
View 3 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
May 30, 2007
I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.
So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.
SELECT measures.turnover ON COLUMNS,
nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS
FROM [Itdev1 Hk]
I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.
Now, how do I get it to default to the last valid member in the list?
View 6 Replies
View Related
Jun 7, 2007
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes?
Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
Thanks very much in advance for your help.
Regards,
Steve
View 7 Replies
View Related
Sep 24, 2007
Hello -
Building a small app for a school nurse......
Have a SP that takes 2 parameters (@login, @logout) both in the form of "convert(varchar,@Login,108)" or 08:00 AM, etc
What I need to do is then check to see:
1. What period did they login in?
2. What period did they logout in?
3. How much time in a period did they actually miss?
The ranges look like:
Set @Period1Start='08:20 AM'
Set @Period1End='09:10 AM'
Set @Period2Start='09:15 AM'
Set @Period2End='10:00 AM'
...
...
...
So.....
if @login = 08:30 AM and @logout = 08:45 then they missed 15 minutes of period 1
If @login = 08:30 AM and @logout = 09:45 then they missed 40 minutes of period 1 AND 30 minutes of period 2
Not knowing all of the time functions in SQl, I am looking for some ideas on how to accomplish this.
Thanks!
Dan B
View 5 Replies
View Related
Oct 17, 2007
Good afternoon all,
I have created a cube in Analysis Services with a time dimension named Time. The data is only needed at the month level, so the fields in the table that the dimension is based on are [DTE MM] and [DTE YR]. In Visual Studio 2005 or in SQL Server Mgmt Studio I can browse the dimension and I see that Time has the following attributes: Year and Month both Regular attributes and Time (Key attribute). There is also a hierarchy named [Year - Month] and if I browse that it looks good dril down from All to Year to Month.
However, when I point my Reporting Services Datasource to the cube, and start Query Builder, I see two dimensions, [DTE MM] and [DTE YR] and no Time dimension.
[DTE MM] has attributes [DTE MM].[Month], [DTE MM].[Time], [DTE MM].[Year], [DTE MM].[Year - Month].
[DTE YR] has attributes [DTE YR].[Month], [DTE YR].[Time], [DTE YR].[Year], [DTE YR].[Year - Month]
This is causing me huge problems in my report. I need to use a date range in the query. To do this, I created Query Parameters and refer to Report Parameters that will be passed in. For example, I use ="[DTE YR].[Year - Month].[Year].&[" + Parameters!StartYear.Value + "].&[" + Parameters!StartMonth.Value +"]" (thanks to Simon Philips) as the query parameter for the Start Year Month, but if I use the [DTE YR].[Year - Month] in my SELECT, the data is not sliced in my query results. That is to say that the Year and Month show correctly, according to the date range, but the Measure is equal for each month and is equal to the total for the cube. To slice the data, I have to use [DTE YR].[Year].[Year] and [DTE MM].[Month].[Month] in my select, but then the data is shown for every month, i.e. the date range is ignored.
What am I doing wrong, or is this a quirk of RS that I can work around?
Thanks,
Kathryn
View 3 Replies
View Related
Feb 21, 2007
Hi,
I'm trying to create a DM model using TS algorithm, to predict sales for different products and channels but I can only get it to work using one of those two "dimensions" or columns the other one is ignored (This is, my fact table contains a key for time, a key for channel a key for product and the metrics and the model only seems to allow working with time, the metrics and only one of the other dimensions product or channel ..) Am I missing something?
Thanks
View 1 Replies
View Related
May 25, 2008
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View 4 Replies
View Related
May 25, 2008
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View 3 Replies
View Related
May 25, 2008
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com
View 2 Replies
View Related
Jun 3, 2008
I'm trying to populate the sp_fact_sit_budget_test2 table with the employee_key from the base_employee_test2 table where the person_id's match and the the base_employee_test2 time_added_fis<= max sp_fact_sit_budget_test2 time_added_fis. So for the data shown below the sp_fact_sit_budget_test2 employee_key should get the value 312436.
CREATE TABLE [dbo].[SP_FACT_SIT_BUDGET_TEST2] (
[TIME_ADDED_FIS] datetime NULL,
[PERSON_ID] int NULL,
[EMPLOYEE_KEY] int NULL)
ON [PRIMARY]
GO
insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE TABLE [dbo].[BASE_EMPLOYEE_TEST2] (
[PERSON_ID] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIME_ADDED_FIS] datetime NULL,
[LAST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FIRST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMPLOYEE_KEY] int NULL)
ON [PRIMARY]
GO
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-23 00:00:00','x','Guillermo',312436)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-13 00:00:00','x','Guillermo',332196)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-16 00:00:00','x','Guillermo',336180)
GO
View 2 Replies
View Related
Mar 31, 2008
Hi, I have the following query that is not working correctly
Basically I have a fields called TimeLeave (datetime) and I want to classify every record according to that field
The multiplication by the field b.Zulu is just to convert to local time, once converted I want to classify the local time with those numbers, but for example nothing falls in the first category and I get a bunch of records with a wrong ID
Any idea what should bet the correct sql statement to classify the records??
Thanks in Advance
CASE
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '22:30' AND '05:59' THEN 1
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '06:00' AND '08:59' THEN 2
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '09:00' AND '11:59' THEN 3
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '12:00' AND '14:59' THEN 4
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '15:00' AND '16:59' THEN 5
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '17:00' AND '19:59' THEN 6
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '20:00' AND '22:29' THEN 7
END
View 3 Replies
View Related
May 8, 2008
I'm looking for a way to link records that would be related to an employee that worked during that shift.
we have 2 shifts days and nights going from 6:00AM to 6:00PM
we have records that are written to a table as events happen on equipment on site.
"Equip_Trans"
"Start_time" "Equip_ID" "Shift_date" "Status" "End_Time"
Date-Time, int, Date, varchar(3), Date-time
5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03
5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM
I have another transaction table "Operator_Trans"
"Operator" "Date" "Shift"
jane Doe, 5/6/2008, 1
Pete Who, 5/6/2008, 2
Sam What, 5/7/2008, 1
Pete Who, 5/7/2008, 2
john Doe, 5/8/2008, 1
jane Doe, 5/8/2008, 2
and last a third table "Shift"
Shift, Start_Time, End_Time
1, 6:00AM, 6:00PM
2, 6:00PM, 6:00AM
I need out put that will show all Equipment status's that started during the shifts worked by an operator
Pete Who, 5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
Pete Who, 5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03 AM
Pete Who, 5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
Pete Who, 5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
john Doe, 5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
john Doe, 5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM
I am not completely sure the direction I should start off with. I have some SQL knowlege but I would still consider myself new.
Thanks
View 1 Replies
View Related
