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.
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?
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.
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?
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.
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 ***
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.
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.
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.
*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.
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?
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')))
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.
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.
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)
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
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.
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?
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!!
---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
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:
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'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:
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_]
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.
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();
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);
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'
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.....
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.
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)
