T-SQL (SS2K8) :: Finding Out Missing Segments From Source Dataset
Oct 23, 2014
I have 2 tables, #MainSample and #SampleCode
In the #MainSample, Line_ID is the Primary key
Line_ID BegMeasure EndMeasure
656 0.00 254500.00
657 0.00 7000.00
658 0.00 308000.00
659 0.00 20000.00
#Sample Code
Line_ID BegMeasure EndMeasure Code
659 665.00 9456.00 APL-XL
657 0.00 200 BHP
From this, I have to find out the segments for which there is no defined code value. I want the output as
Line_ID BegMeasure EndMeasure
656 0.00 254500.00
657 200.00 7000.00
658 0.00 308000.00
659 0.00 665.00
659 9456.00 20000.00
How to achieve this?
View 0 Replies
ADVERTISEMENT
Jun 5, 2008
ok folks, I need help.
Here's the table and some sample data:
declare @t table
(
segment int,
subsegment int,
primary key (segment,subsegment)
)
insert @t
select 1,33 union all
select 1,22 union all
select 2,33 union all
select 2,22 union all
select 3,33 union all
select 3,22 union all
select 3,44
What I want is to find all segments that are in some sense complete duplicates of other segments. a segment is made up of subsegments. a subsegment is not a segment - it's a completely different entity. this table is not hierarchical.
So in the sample data above, segments 1 and 2 are dupes because they share exactly the same subsegments: 22 and 33. Segment 3 is not a dupe because it has a third subsegment the other two don't have: 44.
when a duped segment is found, I need to know which other segment it duplicates. so an acceptable result set for the above sample data would be:
segment partner
------- -------
1 2
this would also be fine:
segment partner
------- -------
1 2
2 1
ps: i already posted this on dbforums - just broadening the audience a little.
elsasoft.org
View 20 Replies
View Related
Mar 7, 2001
Hi,
OK, here's a question for all you SQL Gurus.
I want to find all the records that are in t2 but are not in t1.
(NOTE: table print outs at bottom of post)
I could write this:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
select t2.t from t2 where t not in (select t from t1)
t
-----------
6
(1 row(s) affected)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
BUT it takes to long.
So I thought of this:
select t2.t , t1.t from t2 left join t1 on t2.t = t1.t where t1.t = null
BUT it doesn't work.
WHY NOT?
Is there anything similar that i can write???
It should work, heres the output for the simple left join:
select t2.t , t1.t from t2 left join t1 on t2.t = t1.t
t t
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 NULL
7 7
(7 row(s) affected)
Thanks,
Benny Hill
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
select * from t1
t
-----------
1
2
3
4
5
7
(6 row(s) affected)
select * from t2
t
-----------
1
2
3
4
5
6
7
(7 row(s) affected)
View 4 Replies
View Related
Aug 25, 2014
I am creating a report that will identify the website categories that our items are in. The purpose is to find items that are not in categories that they should be so they can be fixed. Attached is a csv of a subset of the data.
The data I attached has all of our Baskets by SKU (ItemNoSKU) and the associated web categories that those items are in. For example, we can see that ItemNoSKU AB107 is in web categories 4, 22, and 23.
What I have already done is get a Total Baskets vs Web Cat Baskets. I know the total baskets is 44:
Code:
SELECT MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU)
FROM myTable
GROUP BY MerchCatDesc, MerchSubCatDesc
And I know the number of baskets in WebCat 23 is 43:
Code:
SELECT WebCatCd,MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU)
FROM myTable
GROUP BY WebCatCd, MerchCatDesc, MerchSubCatDesc
So in this instance, I know there is 1 Basket ItemNoSKU that is not in WebCatCd 23. I need to list this "missing" ItemNoSKU along with the WebCatCd it is missing from. I am struggling on how to write the code to accomplish this for my full list of many categories and web categories.
View 11 Replies
View Related
Jun 6, 2008
Friends
I'm using Sql Server 2005, in which I've table like this
USE [Sample]
GO
/****** Object: Table [dbo].[Table1] Script Date: 06/07/2008 03:10:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[TimesheetDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpID] [int] NULL
) ON [PRIMARY]
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-05 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-17 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-01 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-06 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-08 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-10 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-11 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-12 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-14 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',13)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-24 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',15)
My task is I want to find Missing Dates (Except Saturday, Sunday)
for each Employee.
Note: Missing Dates should be Working Days only (Excluding Saturday & Sunday)
Help me out
Thanks
Rajesh N.
View 6 Replies
View Related
Nov 21, 2006
I have a web page that has been in production for over a year. On occasion the web app users report sporadic errors which last a few minutes then go away. The page loads up a generic DataSet, and then that DataSet is assigned to a datagrid on the page. The error is that a column that is expected to be in the dataset is not found. I know this column must exist in the database because its a required field. After we reset IIS, then all returns to normal and the errors go away. Below is the stack trace.
Exception: System.IndexOutOfRangeException Message: Cannot find column LastName. Source: System.Data at System.Data.DataTable.ParseSortString(String sortString) at System.Data.DataView.UpdateIndex(Boolean force) at System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, DataFilter newRowFilter) at System.Data.DataView..ctor(DataTable table, String RowFilter, String Sort, DataViewRowState RowState) at MySystem.Web.GuestSearch.ShowResults(DataSet ds, Int32 currentPageIndex) at MySystem.Web.GuestSearch.LoadResults(Int32 currentPageIndex) at MySystem.GuestSearch.btnSearch_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain()
Our DBA can find no problems on the database side and our web servers don't log any system event problems either. I do not understand why resetting IIS helps the issue. I need to find the real cause of the problems rather than just resetting IIS all the time. Any ideas?
View 1 Replies
View Related
Jun 2, 2011
How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:
Select col1, col2,col3,col4,
(col1 + col2) as addcol1,
(col2 + col3) as addcol2,
(col3 + col4) as addcol3,
Max(addcol1,addcol2,addcol3) as maxvalue
from Table A
I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.
View 9 Replies
View Related
Mar 25, 2014
I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:
CREATE TABLE #SessionTest(SessionId int,Logindatetime datetime, Logoutdatetime datetime)
INSERT INTO #SessionTest
SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL
SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL
SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL
SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL
SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL
[code]....
View 6 Replies
View Related
Sep 13, 2013
I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset.
Example dataset SQL below:
CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME)
INSERT INTO #test
SELECT 1, '20130428', '20130523'
UNION
SELECT 1, '20130526', '20130823'
[Code] ....
I would expect a dataset to be returned consisting of:
1, 24/05/2013, 25/05/2013
1, 24/08/2013, 25/08/2013
2, 16/05/2013, 24/05/2013
I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...
View 9 Replies
View Related
Oct 30, 2014
I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.
i planned to test it as follows
declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone_optimized @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'
----- for the SP that is before optimize
declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'
View 9 Replies
View Related
Nov 2, 2015
I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
[code]....
View 9 Replies
View Related
Jun 2, 2014
I have various ways of getting the parameters of a stored procedure:
I have a procedure that has all defaults 4 are null and 2 are 0.
The following shows most of what I need but no defaults
SELECT PARAMETER_NAME ,
ORDINAL_POSITION ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
CHARACTER_OCTET_LENGTH ,
NUMERIC_PRECISION ,
[Code] ...
This one has two values:
PARAMETER_HASDEFAULT (always 0) and PARAMETER_DEFAULT (always 0)
sp_procedure_params_rowset proc procedure
Is there something else that would tell me if there is a default on a parameter and what the default is if there is one.
View 2 Replies
View Related
May 13, 2015
I have data similar to the below
CREATE TABLE #TEMP
(
TYPE VARCHAR(10),
SEQ INT,
SUB_TYPE VARCHAR(10))
[Code] ....
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1
CROSS APPLY
(SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN
#TEMP t3
ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
View 3 Replies
View Related
Jul 27, 2015
I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.
View 4 Replies
View Related
Apr 19, 2006
Hi All! Looking for some help with SQL errors.
We are using SQL 2005. Our server has been failing (service just stops responding) several times a week. We don't have a DB admin, just me, the developer, so please be patient.
I can view all the errors in the existing trace through the Profiler, but not sure how to follow error to query source.
How do you use the trace logs to find the problem query?
I have two significant errors in my logs. One is Hash Warning and the other is Missing Join Operand. I'm not interested in the general causes of these types of errors, that info has been easy to find.
I don't know how to link or otherwise associate a particular instance of an error in the trace logs to the query that caused it to determine the query that is causing the trouble.
The traces are already on the server. I've never created a new trace. I believe they are generated automatically by the server...
Can someone shed some light on how these existing traces are being generated and how I can modify them to include the data I need? When I open an existing trace on the server and view the properties, events tab, there appears to be no way to select the column "TextData" (no checkboxes) for the two errors we are seeing (listed above).
If the TextData column for the errors will allow me to see the offending query, can someone please tell me how to enable that column in my traces that are currently being generated on the server?
Thank you!
Drew
View 4 Replies
View Related
Mar 25, 2014
I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.
Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013
View 2 Replies
View Related
Mar 27, 2014
I'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! )
Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.
Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.
I thought I could do this with a CTE, but I'm having a bit of difficulty..
Here's the code:
CREATE TABLE #FileSystem (
FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY
,ParentFSIDINTEGER NULL
,NameVARCHAR(100)
,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e
[Code] ....
View 1 Replies
View Related
Aug 18, 2014
I've a request: I've a table with a date column and an if numeric data type.
I've to check from now on 7 days ago if any value is missing in my date field.
So if I've f.i. in my table
ID DAYS
1 2014-08-11
2 2014-08-12
3 2014-08-13
4 2014-08-14
5 2014-08-17
then my output should be:
2014-08-15
2014-08-16
2014-08-18
How to do this?
View 1 Replies
View Related
May 22, 2006
I wanted to demonstrate how SSIS can easily read an Excel file into a database. I started up the wizard (dtswizard) looked and looked by couldn't find "Excel" in the list of datasources. Next I started up the SSIS IDE, found an Excel Destination, and created a flow that errored out on the copy to Excel step.
>>>
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package, Connection manager "Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Error: 0xC020801C at Data Flow Task, Excel Destination [666]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Destination" (666) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
>>>
I don't understand that message. I suspect the problem is that Excel is NOT installed on the box where I'm running the package. Does that seem right to you? Also, if Excel is needed on the box, how can I develop such a package on a laptop (in the airport) that doesn't have Excel?
Confused, but good!
Barkingdog
P.S. As the saying goes, "I never had this much trrouble importingexporting an Excel spreadsheet in sql 2000 DTS."
View 8 Replies
View Related
Nov 5, 2014
The database consists of the following tables:
create table dbo.customer (
customer_id int identity primary key clustered,
customer_name nvarchar(256) not null
)
create table dbo.purchase_order (
purchase_order_id int identity primary key clustered
customer_id int not null,
amount money not null,
order_date date not null
)
Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.
Possible result: [URL] ......
View 7 Replies
View Related
Jun 3, 2015
I have the following table:
SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
[Code] .....
I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output
View 3 Replies
View Related
Jul 31, 2014
Any script handy to fill in a century into a date string. Right now, I'm getting dates in the following format:
7/26/29 = converts to 2029.
I'm looking for a SQL statement that will now to put a 19 or 20 in the century.
View 9 Replies
View Related
Apr 16, 2007
Hello. I€™m new to SSIS. This forum and Kirk Haselden€™s book are my teachers. I€™m having a hard time grasping something basic to get started defining a set of packages to automate the ETL process, however, and perhaps I€™m simply misunderstanding the best practices of SSIS.
I have source data in two different transactional databases, and use OleDb connection managers (and OleDb Source components in the Data Flow) to extract the data. I use a Script Task and several Lookup widgets in the Data Flow to transform the data, and output each to two different package-scope DataSet variables.
How do I join these two datasets in a third Data Flow task for loading into my data warehouse? It seems I can iterate through them in the Control Flow, but I can€™t write a query against them in the Data Flow, since there is no connection manager that allows me to €śconnect€? to a package-level variable. Should I instead be storing my extracted, transformed data in temporary database tables, and then joining these to do the final load?
Any advice greatly appreciated. Thanks in advance.
View 1 Replies
View Related
Mar 31, 2008
Hi,
Is it possible to use datatable/dataset as source to a report in Reporting Services. That is possible in e.g. Crystal Reports. I can only find Report Server Model and XML as non-database sources.
Best regards
Lars
View 10 Replies
View Related
Jul 9, 2007
I want to skip running the SSIS data flow task when the source file is missing. We have a scheduler that copies the source file to the staging area. This SSIS package runs as SQL server job. So when a SSIS package fails due to missing file the remaining steps in the SQL scheduler won't execute. I want to handle the missing source file condition grace fully. Please advise.
Thanks in advance.
View 1 Replies
View Related
May 9, 2008
Is there a limit on the number of fields that can be displayed in a table object in a data source view in SSAS 05?
One of the tables (the fact table) in my data source view is displaying only 50 fields. The table actually has many more than that. One of the fields that is not displaying is a foreign key that I need to link to a new dimension table. I have tried refreshing the view, but it doesn't bring in any additional fields.
View 10 Replies
View Related
Nov 3, 2014
I am trying to find a way to provide three metrics for a table that only provides 2 of them. I need to derive a third column which tallies based on conditions that can be found in the dataset.
The table I'm querying shows
(1) total referrals
(2) total moves
but it is missing a column that tallies whenever the referral moved into the same property_id that it was referred to.
create table dimFacts
(date_value datetime
, month_name varchar(9)
, year_number varchar(4)
, month_number tinyint
[Code] ....
How can I use sql to create one additional column for the above data set, ie. let's call it 'property_conversion' such that on the fifth row there will be a '1' showing that on 2012-12-30 lead_id 10038655 moved into the property it was referred to back on 2012-10-15 ?
View 4 Replies
View Related
Feb 5, 2015
Imagine the following scenario: two tables (say, "requests" and "details") are joined in a 1:n relationship on MSSQL 2008. Both tables contain an ID (autoincrement field) and a timestamp field for proper concurrency management. Data access in the frontend is provided by a typed dataset in VS 2010. There are SPs on the server which select, update, insert or delete data in each of the two tables (so, 8 SPs alltogether: uspRequestsSelect, uspDetailsInsert etc.). These SPs are used for data access in the dataset. The GUI is a Windows form with 2 datagridviews, one for request datatable and one for the child-relation-based datatable FK_request_details. So, each request shows its details. The form works well so far.
Now, trouble strikes. A business rule says: "the first details row of a request (=row with lowest ID) always has a 0 in column "additional fee". For additional detail rows, this field has to be set to constant value 45". In short: the first detail row of each request is free, second and later details are charged 45 €.
So, I created a SP "uspRequestFeeManager", which recalculates all (!) detail rows of a request. This SP is called in uspDetailsInsert, uspDetailsUpdate and uspDetailsDelete, as each of this cases causes the additional fee to be recalculated for all rows (as rowcount can change). In Management Studio, this works as well!
But: as the uspRequestFeeManager changes data even for rows the user did NOT touch, there's a concurrency exception in my frontend in the following case:
In a request with 2 detail rows (first row has fee = 0, second = 45), the user deletes the row with fee = 0. Committing via TableAdapterManager calls uspDetailsDelete, which calls uspRequestFeeManager, which sets the remaining single details row to a fee value of 0 (which is correct!). This causes TableAdapterManager.UpdateAll to fail ("concurrency exception; delete command has handled 0 of 1 expected records"), as uspRequestFeeManager has "edited" a row which the user didn't touch, and thus updated its timestamp as well. So, the list is out of sync.
The uspRequestFeeManager looked like a good idea... but it seems not to be.
Approach would be: instead of calling the uspRequestFeeManager from within the SP, call it programmatically after TableAfterManager.UpdateAll, and after that, 're-fill' the details datatable with the updated data. But that would transfer business logic from server to client. I don't like that...
View 3 Replies
View Related
Sep 9, 2014
I have written a simple routine to do some address checking between sources. The business owner wanted the check done to the address as a whole and to report the address' that did not return a match. The comparison works perfectly fine.
Well, now the customer has come back wanting to know what element(s) of the address make the match fail
Here's the basic address compare:
SELECT *
FROM Full_Address_Map fam
WHERE fam.StreetName + ' ' + fam.RoadType + ' ' + fam.CityName + ' ' + fam.StateName + ' ' + fam.Country COLLATE Cyrillic_General_CI_AI NOT IN
(
SELECT s.base_name + ' ' + s.road_type + ' ' + p.name + ' ' + v.name + ' ' + v.country_name COLLATE Cyrillic_General_CI_AI
[Code] ....
You can see that we are building the address by sticking the 5 columns together with a space in between to create a single string. We are then doing the same thing with the other source data and simply comparing the string. This worked perfectly and was nice and simple... until the customer decided he wanted to see the element(s) causing the issue.
For example, let's say there is an address of '123 Main ST Dallas Texas United States' in one source, but it's '123 Main Dallas Texas United States' in the other. The customer wants to know that it is the road type (ST) that caused the mismatch.
Now, I wrote a bunch of queries to try and single out each element in order to determine if it is the one causing the issue or not. It seems to work partially, but I don't believe it is the best approach and it doesn't work if more than one column is the culprit.
View 3 Replies
View Related
Nov 6, 2014
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1
aaa7
bbb2
bbb5
bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0
aaa 1 1
aaa 2 0
aaa 3 0
...
bbb 0 0
bbb 1 0
bbb 2 1
...
and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
View 6 Replies
View Related
Oct 19, 2007
I have inherited a SSRS setup and am trying to do some clean up. Problem is I cannot see the Data Sources Folder. I am setup as SA and SU on both the RS Server and SQL Server. When I go check the catalog in SQL db it does truly exist as well as a number of data sources in the folder that reports are currently using. How can I see this folder?
View 7 Replies
View Related
May 21, 2007
Hi guys,
How can I pass a parameter used for the DataSet query? I'm using this DataSet file as data source of my .rdlc report for Windows Forms.
I've already done a .rdlc report to Web Forms where I passed the query parameter by ObjectDataSource.SelectParameters, but in Windows Forms this object was not created. I just hava the following created objects:
- despesaTableAdapter (from myDataSetTableAdapter)
- despesaBindingSource (Windows.Forms.BindingSource)
- RelatorioDataSet (from myDataSet)
- relatorioDataSet1 (from myDataSet)
Any help will be very appreciated.
Tks and rgds,
Luis Antonio
View 1 Replies
View Related
May 12, 2015
Could we use SSRS Shared Dataset as a source to the SSIS package?
View 3 Replies
View Related
