Grouping By Custom Datepart For Shift Patterns
Apr 27, 2006
My application has a log table with a timestamp field which represents the time when the record was inserted.
We would like query the the table and group the results into date units based on the value of the timestamp.
The grouping specification is held in another table but only specified for a single week.
For example
WeekDay Start End
1 06:00:00 14:00:00
2 08:10:00 17:00:00
What this specification means is that when the log table is queried records with a timestamp that fall on a Monday between 06:00:00 and 14:00:00 should be grouped together, on Tuesday the group is records between 08:10:00 and 17:00:00 and so on...
The only way I can think of doing this is to generate a temporary table when the log table is queried that has a unique record for each time period for the span of the query and then attempt to join this table to the log table using the timestamp and then group based on the temporary table unique ID.
I'm not sure how to generate such a temporary table from the specification table so any help would be appreciated.
Thanks in advance.
View 5 Replies
ADVERTISEMENT
Aug 23, 2007
I am having some trouble with grouping. I have a custom function that returns the field to group on depending on the parameters provided by the user. This is not working, however if I select the same value from the grouping drop down list that is returned by the custom function the grouping works. My custom funtion is as follows:
Function OrganisationGroup() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!EnterpriseOption.Value
Case "1"
Group = Group1Value()
Case "2"
Group = "DepartmentDesc"
Case "3"
Group = "OrganizationDesc"
Case "4"
Group = "OrganizationDesc"
End Select
If Group = "" Then
Return "0"
Else
Return "Fields!" & Group & ".Value"
End If
End Function
Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1"
Group = "StockFamilyDesc"
Case "2"
Group = "GroupDesc"
Case "3"
Group = "Saleable"
Case "4"
Group = "Saleable"
Case "5"
Group = "SaleableGLCodeDesc"
Case "6"
Group = "Saleable"
End Select
Return Group
End Function
Does anyone have any ideas on why the grouping would not be working with the custom function?
Thanks in advance.
View 1 Replies
View Related
Jun 25, 2007
Hello everyone,
I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.
TH
GH1 (hidden)
GH2 (hidden)
Det (hidden)
GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)
GF1 =Code.ShowAndResetSubTotal()
TF =Code.GrandTotal
I have the following in my Code window.
Dim Public SubTotal as Decimal
Dim Public GrandTotal as Decimal
Function ShowAndResetSubTotal() as Decimal
ShowAndResetSubTotal = SubTotal
SubTotal = 0
End Function
Function AddValue(newValue as decimal) as Decimal
SubTotal += newValue
GrandTotal += newValue
AddValue = newValue
End Function
This gives me incorrect results and I can't figure out why. Here's how it shows on my report:
Part Number
Quantity
Cost
Regular Subtotal Method
Using Custom Code
Part 1
4,000
1.49
$5,947.20
Customer 1
$11,894.40
$0.00
Part 2
10
1.01
$10.07
Customer 2
$50.34
$5,947.20
Part 3
1
0.44
$0.44
Part 4
6,050
0.25
$1,530.41
Part 5
0
1.25
$0.00
Part 6
0
1.23
$0.00
Customer 3
$42,851.86
$10.07
Part 7
16,250
0.24
$3,922.59
Customer 4
$19,612.94
$1,530.85
Part 8
17,250
0.38
$6,544.82
Part 9
27,225
0.20
$5,380.20
Customer 5
$66,891.69
$3,922.59
Grand Total
$141,301.23
$0.00
The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.
As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?
Jarret
View 2 Replies
View Related
Aug 10, 2015
I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:
SELECT COUNT(*)
FROMdbo.People
WHERE Current_Status = ‘A’
GROUP BY People_Code
The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.
For example:
Building NamePeople_CodeEmployee Count
Building A617535
Building B985665
Building C529212
Building C529932
Building C419816
Building D326974
Building D781024
Building E25365
Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.
Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.
I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.
People_CodeNameGroupNameGroupPeopleCode
6175Building ABuilding A6175
9856Building BBuilding B9856
5292Building CBuildingCGroup5292
5299Building C AnnexBuildingCGroup5292
4198Building C Floor6BuildingCGroup5292
Etc…
The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.
View 5 Replies
View Related
Dec 2, 2006
Hello there,I am having a small problem which been challenging me for few days andneed help or advice.I am trying to calculate the day-shift for employees based on the timethey started and finish working, I will only have 2 shifts 1 or 2 .Shift one changes based on the location however any thing else is shift2. The problem I am having is when someone signed in after midnight; Ineed to report his time under shift 2 for the previous day date. So ifhe signs at 12:30 AM on 12-12-2006, I need to report that as shift 2 on12-11-2006 and that’s where my problem is. Is there a way to subtractthe date by 1. I am using SQL Server and here is a simplified tables Iam working with:Employee tableEmployeID LocationID StartTime EndTime123 555 11:00:00 AM 3:00:00 PM183 559 7:00:00 AM 11:00:00 AM…Shift tableShiftNumber LocationID StartTime EndTime1 555 7:00:00AM 2:00:00PM2 555 2:00:00PM 12:00:00AM1 559 6:00:00AM 4:00:00PM…..So I am trying something likeCASE WHEN (select ShiftNumber FROM Shift WHERE LocationID=TR.LocationIDAND(TR.StartTime>=StartTimeANDTR.EndTime<= EndTime)) =1 THEN 1ELSEWHEN (select ShiftNumber FROM Shift WHERE LocationID=TR.LocationIDAND(TR.StartTime>=StartTimeANDTR.EndTime<= EndTime)) =2 THEN 2ELSE?????????????? NEED HELP HERE WHEN EMPLOYEE SIGN AFTER MIDNIGHTEND)FROMEmployee TRINNER JOIN Shift ON LocationID = TR.LocationID*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
Feb 21, 2007
hi all, what sql data type should i use to store variable length bit patterns no longer than 30 bits?
View 4 Replies
View Related
Feb 13, 2008
Design patterns are indispensable to improve the quality and productivity of system under development. Even though there are books available addressing the needs of object oriented programming, but useful information for SQL design patterns are hard to find.
I would appreciate if you could share any of the resources that you may have come across.
View 21 Replies
View Related
Dec 27, 2007
Hello,
I have a BIDS project with 14 reports, all formatted the same way. On one report, any text box or image that is placed in the center or right of center in the form, shifts very far to the right of the tables and charts when it's rendered.
The text box placed at the top left of the form stays put. This is really wierd.
Has anyone ever heard of anything like this.
I have a couple of screen shots if you have a place I can put them.
Thanks
Dan
View 3 Replies
View Related
Feb 11, 2008
Here is my current challenge that I realize I could do a few different ways but nothing efficient or flexible comes to mind. Hoping one of you guys can help me out with an elequent efficient T-SQL script.
Employee workstate information in our system is stored by segment. Segment 1 cooresponds to the entire shift while segments greater than 1 coorespond to subsequent breaks during the shift (Segment 1). i.e.
SegmentNumber SegmentStart SegmentEnd
1 10:00am 06:00pm
2 12:00pm 13:00pm
3 03:15pm 04:00pm
What I would like to do is generate continuous nonoverlapping records. i.e.
SegmentNumber SegmentStart SegmentEnd
1 10:00am 12:00pm
2 12:00pm 13:00pm
3 13:00pm 03:15pm
4 03:15pm 04:00pm
5 04:00pm 06:00pm
*Notice End has changed to match start of first break and so on. Also records have been added to fill gaps between breaks during the shift.
What adds to the challenge is that the segment number for a given employee/report day can be 1 (meaning no breaks) to any number (lots of breaks). The segment start and end times can be any increment. In addition Breaks can be divided into paid time and unpaid time but I imagine given a solution to the above I could apply it to another level down on my own.
Thank you in advance for your help!
View 3 Replies
View Related
Feb 6, 2007
The default behaviour for pressing SHIFT + SPACE when editing text inside a cell in the Table Editor view in Microsoft SQL Server Management Studio Express is to change focus to select all cells comprising the current row. This behaviour lends itself to the default behaviour for pressing SHIFT + {LEFT | RIGHT | UP | DOWN } in changing the focus to select adjacent cells.
However, I find this is quite annoying as the SHIFT + SPACE combination is quite a common miss-type when entering text in UpperCaps or when entering text with spaces while holding the SHIFT key.
Anyone else have this gripe with MSSMSE? Anyone know how to disable this behaviour?
Simon
View 2 Replies
View Related
Apr 10, 2006
for the record, this is my first post and i'm not sure this is the right place to post, but it makes sence to me.
i've been spending a great deal of time with our new MS SQL server and love it but i've noticed there's a fairly large diff between the SQL i'm used to seeing in MS Access and the SQL that the server uses. as you can see by my below statement i'm starting to get the hang of this but i still run into new issues...
my question... what's wrong with my IIf statements? this is what the server spits out...
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'LIKE'.
[ACCPAC_OESHDT].[TERRITORY] is an nvarchar(6) and contains data such as '1', '2', '3', etc...
i've tried many many variants and always have the same outcom. i've tried casting as an int, rtrim to rid the value of any possible white space, expanded versions.
oh, almost forgot... my environment is... I open the SQL Management Studio then logon to SQL Database Engine and then create a New Query.
i'm stuck, please advise. ;-)
SELECT
ACCPAC_OESHDT.YR,
ACCPAC_OESHDT.PERIOD,
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory'))) AS TERRITORY,
SUM(CAST([SAMTSALES] AS MONEY)) AS Sales
FROM ACCPAC_OESHDT
GROUP BY
ACCPAC_OESHDT.YR,
ACCPAC_OESHDT.PERIOD,
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory')))
HAVING ((SUM(CAST([SAMTSALES] AS MONEY)))>0);
View 7 Replies
View Related
May 17, 2015
I have the following shift table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
[code]...
I need to identify which shift is currently running on by providing the current time.I used the following query to get the result. It is giving correct result for Morning & Afternoon Shift but failed to produce result for Night Shift.
SELECT ShiftName, Shiftid
FROM WHShifts
WHERE DATEPART(HOUR, @Currenttime)>=(DATEPART(HOUR,CAST(ShiftInTime AS Datetime))) AND DATEPART(HOUR, @Currenttime)<=(DATEPART(HOUR,CAST(ShiftOutTime AS Datetime)))
Is there any way to find out the night shift from a given time.
View 15 Replies
View Related
May 9, 2006
I am looking for something similar to patterns for table design, but I have been unable to find anything on that, or good rule of thumb performance guides for table design.
Most of the situations we face look like perfect candidates for patterns, and some good rules of thumb -- problems like scalable OLAP requirements PLUS real time reporting.
I am looking for several differnet approaches and a good summary of the trade-offs for each one.
Thanks!
View 1 Replies
View Related
Jul 13, 2015
We are maintaining 3 Shifts in our database. Problem in maintaining date and time for 3rd Shift. For example, today date is 13th July and third shift timing is 11 PM - 7 AM. Then I have to display the beginning date as 13/07/2015 11 PM and end date as 14/07/2015 7 AM. Please find the data(in seconds) available in database which I need to use for my calculation.
Date(Fcreacion)
Start time in Seconds(Hcreacion)
End time in seconds(Hcerrar)
turno(Shift)
[code]...
View 3 Replies
View Related
Nov 9, 2015
I am doing some analysis on shifts and what I want to do is say if the time from a timestamp field is between 00:00 and 05:59 then the figures belong to the day before. Eg. We have a night shift that work 22:00 - 06:00.
Here is my coding which logically looks sound to me but when I run it, it has an error:
SELECTe.ID,
CAST(e.TimestampasDate)asEventDate,
CAST(e.TimestampasTime)asEventTime,
CASE
WHENCAST(e.TimestampasTime)BETWEEN'00:00:01'and'05:59:59'
THENCAST(e.TimestampasDate)-1
ELSECAST(e.TimestampasDate)
END
FROMIntouch.Event
View 5 Replies
View Related
Jul 6, 2012
string starts with 3 digit same number
111 H,777GGG,9999 H etc
string starts with NULL
string that starts with sequential digits
123g,897 k
string that starts with sequential alphabets
abcmki12, ghimkkk, rst123 5 etc
string only one character (digit or letter)
string only has same character repeated 3 times except for OOO
string only has three characters, 1 digit and two letters or 2 digit one letter
string that has only two characters one digit and one letter
View 1 Replies
View Related
Oct 17, 2007
Hi,
I am trying to design some functionality that emulates the recurrence functionality of MS Outlook calendar. In the calendar, users can choose a recurrence pattern (i.e. daily, weekly, monthly or yearly). Depending on the radio button that is selected, different choices appear. I am unsure as to how to model this in the database.
Here's what I have so far:
A ScheduleDetails table that will store information applicable to all schedules, regardless of recurrence pattern. One of the columns in this table will store the recurrence pattern type (0 for daily, 1 for weekly, etc.). The valid values for this column will be stored in a look-up table.
Now, if daily is selected, the user can select a radio button called 'Every <#> days' OR a radio button called 'Every weekday'. My plan is to have a new ScheduleDailyRecurrences table that has a Days field. If 'Every <#> days' is selected, the value of Days will be the value entered by the user. Otherwise, if the user has selected 'Every weekday', the value will be -1. Is this a good way to do it? I feel as though I may be attaching too much meaning to the particular value.
If monthly is selected, it gets even more complicated. The user can either select 'Day <#> of every <#> month(s)' OR 'The <count> <day> of every <#> month(s)'. I'm not sure how to model this. In my ScheduleMonthlyRecurrences table, should I have a field called 'Pattern'? Depending on the value of this field, the details would be then be stored in yet another table? Or should I just have columns in ScheduleMonthlyRecurrences for each user-entered value? This would mean that some of the fields would not be applicable depending on the radio button that the user has selected.
It's a little difficult to explain, but if you go to the Recurrence button in Outlook calendar, you'll see what I mean.
Thanks for any advice.
View 5 Replies
View Related
Feb 17, 2008
I have a database which contains time series data (historical stock prices) which I have to search for patterns on a day to day basis. But searching this historical data for patterns is very time consuming not only in writing the complex t-sql scripts but also executing them.
Table structure for one min data:
[Date] [Time] [Open] [High], [Low], [Close], [Adjusted_Close], [MA], [DI].....
Tick Data:
[Date] [Time] [Trade]
Most time consuming queries are with lots of inner joins. So for example if I have to compare first few mins data then I have to do inner join like:
With IntervalData AS
(
SELECT [Date], Sum(CASE WHEN 1430 = [Time] THEN [PriceRange] END) AS '1430',
Sum(CASE WHEN 1431 = [Time] THEN [PriceRange] END) AS '1431',
Sum(CASE WHEN 1432 = [Time] THEN [PriceRange] END) AS '1432'
FROM [INDU_1] GROUP BY [Date]
)
SELECT [Date] ,[1430], [1431], [1432], [1431] - [1430] As 'Range' from IntervalData
WHERE ([1430] > 0 AND [1431] < 0 AND [1432] < 0) OR ([1430] < 0 AND [1431] > 0 AND [1430] > 0)
------------------------------------------------------------------------
select ind1.[Time], ind1.PriceRange,ind2.[Time], ind2.PriceRange from INDU_1 ind1
INNER JOIN INDU_1 ind2 ON ind1.[Time] = ind2.[Time] - 1 AND ind1.[Date] = ind2.[Date]
where (ind1.[Time] = 2058) AND ((ind1.PriceRange > 0 AND ind2.PriceRange >0) OR (ind2.PriceRange < 0 AND ind1.PriceRange < 0))
ORDER BY ind1.[Date] DESC;
Is there anyway I can use Sql 2005 Data mining models to make this searching faster?
View 1 Replies
View Related
Nov 22, 2006
We are experiencing problems in presenting Unicode data stored in MSSQL as Shift-jis format in ASP application.
In MSSQL we are storing kanji text in database with a field type as nchar. Other application loads these tables using codepage 932. In MSSQL data is stored in Kanji correctly.
In ASP application, when we are trying to read data from these tables, Kanji characters are coming as '?????'. We are using shift-jis character set in ASP.
Templates for field and column header uses shift-jis, but a record set coming from MSSql is Unicode.
Is there a way to convert from Unicode to Shift-jis in MSSql or in ASP .
Please help!!
View 1 Replies
View Related
Sep 5, 2007
---Checks that input only contains numbers
if PatIndex('%[^0-9]%','11') > 0
Begin
Print 'Not all numbers'
End
Else
Begin
Print 'All numbers'
End
---Checks that input only contains letters
if PatIndex('%[^a-z]%','aaaaa') > 0
Begin
Print 'Not all letters'
End
Else
Begin
Print 'All letters'
End
--Checking for mixed input
If PatIndex('%[^0-9][0-9]%','abc') > 0
Begin
Print 'Alpha numeric data'
End
Else
Begin
Print 'Either all numbers or all letters'
End
--Checks that value must start with a letter and a number
If PatIndex('[^0-9][0-9]%','A1anamwar11') > 0
Begin
Print 'Starts with a letter and a number'
End
Else
Begin
Print 'Does not start with a letter and a number'
End
--Checks that value must End with a letter and a number
If PatIndex('%[^0-9][0-9]','A1anamwar11a1') > 0
Begin
Print 'Ends with a letter and a number'
End
Else
Begin
Print 'Does not End with a letter and a number'
End
--Checks that value must Start with a letter and Ends with a number
If PatIndex('[^0-9]%[0-9]','namwar1') > 0
Begin
Print 'Starts with a letter and ends with a number'
End
Else
Begin
Print 'Does not start with a letter and ends with a number'
End
Namwar
<Link removed by georgev>
View 13 Replies
View Related
May 16, 2012
I have a report with a chart that utilizes both colors and background patterns to distinguish various pieces of data.
The background patterns work great (they are very clear and provide excellent contrast) when viewed in the report viewer or printed straight from the report viewer.
However, when I export to PDF, the background patterns become compressed and small, almost to the point of being too small to distinguish that there is a pattern at all. The result ends up being what appears to be just a different shade of the background color. This is particularly a problem when you then print the PDF on paper, since hatching patterns are similar to how printers create different shades in the first place.
I am somewhat suspicious that this might have to do with resolution settings for PDF exports. If I could lower the resolution, perhaps the patterns would not become so tight and compressed. Below are examples of what I'm describing:
Screenshot of the report viewer:
Screenshot of the PDF:
View 2 Replies
View Related
Mar 8, 2007
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
View 5 Replies
View Related
Nov 26, 2007
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
View 1 Replies
View Related
Dec 29, 2006
Hi,
I would appreciate any thoughts/ideas on the following use case for the distributed service broker application we plan to migrate from our existing proprietary tcp based message protocol using database tables for reliability.
There are two ssb services running in separate sql server instances, each on a different server machine. For simplicity, let us assume the ssb endpoint names are SSBA, SSBB. SSBB is the Initiator of the Dialog while SSBA is the Target. Now the requirement is that if the underlying network communication between the two ssb endpoints(SSBA and SSBB) is broken or if the critical service SSBB is down, then processing of any incoming message into SSBA's queue from a third service broker service (say SSBEXPR) running within a SqlExpress instance should be delayed until SSBB is alive and network communication between SSBA and SSBB is established. In our existing implementation (wherein SSBA, SSBB and SSBEXPR are windows services) we use a combination of TCP socket disconnects and Heartbeat messages between SSBA and SSBB to determine the health of network connection and that of the SSBB service.
Now my understanding of how the underlying network connection for a ssb dialog works is that if there is no activity on a dialog for a certain amount of time then the underlying network connection is closed. Is there a way to specify the amount of time to say infinite value or something and thus change this behavior? My other question is how can one query the underlying network connection (i.e. a row from sys.dm_broker_connections) associated with a particular conversation? If none of this is possible, then any other patterns/ideas/approach is welcome.
Thanks,
View 8 Replies
View Related
Feb 6, 2007
Hi,
Using the Microsoft Patterns and Practices "Object Builder" (Dependency Injection/Builder library), I wrote an SQL CLR Stored Procedure (using VS 2005 Professional).
All compiles and deploys ok (to SQL Server 2005 Express).
However, at run-time, I get the following error upon a "BuilderContext" object's instantiation: {"Attempted to perform an operation that was forbidden by the CLR host."}
Thoughts on how to get ObjectBuilder working in the SQLCLR?
Thanks!
Andy
(posted below is the sample code...with the run-time exception occuring on the ...BuilderContext cxt = new ... call)
Microsoft.Practices.ObjectBuilder.BuilderStrategyChain chain =
new Microsoft.Practices.ObjectBuilder.BuilderStrategyChain();
chain.Add(new Microsoft.Practices.ObjectBuilder.CreationStrategy());
Microsoft.Practices.ObjectBuilder.Locator locator1 =
new Microsoft.Practices.ObjectBuilder.Locator(null);
// Get error when new'ing BuilderContext: "Attempted to perform an operation that was forbidden by the CLR host."
Microsoft.Practices.ObjectBuilder.BuilderContext cxt =
new Microsoft.Practices.ObjectBuilder.BuilderContext(chain, locator1, null);
View 1 Replies
View Related
Feb 29, 2008
Thanks in advanace for taking the time to read this post.
I am using MSSQL 2005 and have created a function that allows me to use regular expressions in my SQL queries.
My question is I have a pattern buried in a field of misc data that I need to pull out just that pattern and discard the rest of the data. Here is the Regular Expression I am using
select field1 from table1
where dbo.RegExMatch (field1,'[a-zA-Z]{4}[0-9]{6}[a-zA-Z]{2,4}')=1
This returns all values in the field that match the expression. What I want to do now is remove all data from the field on the left and right of the expression that does not match the expression. How would I accomplish this without reading through the 200k+ records and writing rules for every exception I run across?
so I could have Gar b/a ge 'THE GOOD DATA' m/or1 ba4d da....ta. All I want to do is return 'THE GOOD DATA'
View 4 Replies
View Related
Aug 14, 2007
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
View 6 Replies
View Related
Apr 2, 2007
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
View 5 Replies
View Related
Jan 31, 2008
I have a datetime field in a sql db named "arrdate". For what I am doing I only need to extract the date formatted as mm/dd/yyyy. Can someone give me an example of the proper syntax?
Something like seems like it should work:
SELECT id, DATEPART(mm/dd/yyyy, arrdate)
FROM guest
but of course it doesn't.....
View 3 Replies
View Related
May 7, 2001
Why would this return a year of 1905: If @tempdate was a varchar
it would return just 2001, but by it being a dateime it returns
the year 1905.
declare @tempdate datetime
select @tempdate = (select datepart(yy,getdate()))
select @tempdate
View 4 Replies
View Related
Mar 18, 2006
Datepart function is different from general ms using in MSSQL
I need something like this
DatePart ( interval, date, [firstdayofweek], [firstweekofyear])
I try to get which week of year.. but first day of week for some country is not sunday..
How can I determine that the first day of week is monday?
View 1 Replies
View Related
Apr 21, 2008
CREATE PROCEDURE [dbo].[sp_TRAK_PROG_TOTALS]
@Frequency varchar (1),
@Rpt_Yr smallint,
@Prog_Yr varchar (2)
AS
SET NOCOUNT ON
DECLARE
@PROG1VARCHAR (20),
@PROG2VARCHAR (20);
--@PROG_YR VARCHAR (2);
SET @PROG1 = 'TRAK08'
SET @PROG2 = 'TRAK208'
SET @PROG_YR = Select DatePart(YY, GetDate()) as Current_Year
SELECT @Rpt_Yr = case @Frequency
WHEN 'M' then (select dateadd(mm,datediff(mm,0,getdate())-1,0))
WHEN 'O' then convert(varchar,@Start_Date,101) + ' 00:00:00'
ELSE convert(varchar,getdate()-1,101)
end
--------------------------------------------------------------------------------------------------
I have done most ot the script for the rest of my pgoram but I am having problems with the date requirements.
I was told to parse the year so that the user can enter the 2 digit date.
When it is 'O' (other) was told build a string to parse the year using datepart so that the user can enter the program year (2 digit format). (@Rpt_Yr)
When it is 'M' (monthly) then I am to goto to table tk_prog get the active program
I have no clue how to correct the above.
View 2 Replies
View Related
May 31, 2008
Hi All,
i'm trying to format SQL so that I retrive the day of the week and the hr in the same column.
SELECT Datepart([hour], Time) as Hour, SUM(Total) as Sales, count(TransactionNumber) as Customers, SUM(Total)/count(TransactionNumber) as 'Ave Sale'
FROM [transaction]
WHERE time between '05/30/2008' and '05/31/08'
GROUP BY datepart([hour],Time)
Output desired is
05/30/08 09
05/30/08 10
05/30/08 11
...
View 1 Replies
View Related
