T-SQL (SS2K8) :: Building Groups Out Of References / Eliminate Cross References
Oct 7, 2014
I got the following problem to solve in TSQL. I don't want to use a cursor. But with the set based solution I am stuck.
Here is my problem:
DECLARE @tmp TABLE (CustomerID INT, CustomerLink INT, PRIMARY KEY(CustomerID))
INSERT @tmp
VALUES(100001,0)
,(100002,100001)
,(100003,100001)
,(100004,100003)
,(100005,100006)
[Code] ....
Desired result:
[CustomerID of a group member],[smallest CustomerID per Group]
|(GroupID)|CustomerID|CustomerLink|
|100001 |100001 |100001 |
|100001 |100002 |100001 |
|100001 |100003 |100001 |
|100001 |100004 |100001 |
|100005 |100005 |100005 |
[Code] .....
doesn't work for crossreferences :.(
RESULTSET:
CustomerID CustomerLink
100001 100001
100002 100001
100003 100001
100004 100001
100005 100005
100006 100006 --wrong
[Code] .....
View 9 Replies
ADVERTISEMENT
Oct 22, 2007
Hi,
I'm doing a web application that will get some information from an ERP.
At this moment I have 2 databases:
1) The aspnetdb, where I have the tables for Merbership and Role
2) The ERP database
I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB.
I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right?
Do you think this will work? Can someone make some comments about this situation, and give me some tips?
Thank you!
View 6 Replies
View Related
Aug 31, 2006
Whats the definitoin. I've been digging for a while, but cannot locate.
TIA, cfr
View 4 Replies
View Related
Oct 11, 2007
Normally, you establish referential integrity so that foreign key in one table points to a primary in another. Here is a composite key:
(A_ref, B_ref) => (A, B)
Consider a situation where A is a primary key in table T1. It is refered by T2, which has A,B as it's prmary key. An example of this situation would be a table of printers and table of batches a printer has printed at specific date. The batches are identified by Printer, Date, BatchNo within the date. Now, we create a temporary table T3, which addresses "today" batches. The "today" reference is taken from a date is taken from some record in the DB and, combined with the Printer ID and BatchNo, must point to a record in T2. Is it possible to specify such a complex relationship to ensure the referential integrity?
The advantages of the integirty are:
-- the referred records are pinned down from removal
-- it is not possible to refer unexisting object; thus, the referee is ensured to refer an existing one.
Thanks.
View 6 Replies
View Related
May 4, 2007
Hi All -
I have come across the need to use a cube in AS 2005 for an application. Unfortunately this will be the first time writting an ASP.Net application which uses one. I was wondering if any of you had any good books or references that I could look at to get an idea of how I should approach this issue.
Thanks in advance.
View 2 Replies
View Related
Feb 18, 2004
I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.
SELECT FldA = CASE
WHEN .... THEN CurQty * 1.5
WHEN .... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE .....
NewValue = CASE
WHEN .... THEN FldA * CurValue
WHEN .... THEN FldB * CurValue
etc.
View 9 Replies
View Related
Jul 20, 2005
HelloSuppose a database Db1 with tables tl1 and tl2 and a second database db2with tables tl3 et tl4.Is it possible to make a join between tables of the two databases ?As for example, Select * from tl1 INNER JOIN tl3 where tl1.Field1 =tl3.Field3Thank for any helpThierry
View 1 Replies
View Related
Jul 20, 2005
I have a task to where I need to move a column from one table toanother. I want to be sure I update any view, stored procedure,trigger, etc. that references the column. I simply want a query thatwill report the related objects and then I will update them manuallybut before I go and try and figure out how to do this by querying thesys tables is there an sp_sproc that will do this?
View 1 Replies
View Related
Jul 10, 2007
I have a data list that will grow over time. The values are listed vertically in a column; most recent value at the bottom. I am trying to figure out how to setup a formula to figure out the standard deviation on the most recent 30 values automatically. For instance if the column contains 30 values and I add the 31st value, I'd like to have to have the standard deviation displayed in a cell and automatically shift from calculating on values 1-30 to values 2-31. Is this possible?
Jeff.
View 1 Replies
View Related
Apr 27, 1999
We installed SQL7 over the weekend. Everything was working peachy through yesterday. This morning SQL server is requiring all of the queries to require references to the username in the from clause...
For example
"Select * from mytable" used to work fine. Not it requires "select * from username.mytable"
I'm logged in as the same username, which is the DB Owner as well. Any idea why this is happening all the sudden?
TIA
Kevin
View 1 Replies
View Related
May 15, 2008
Hey.
I'm trying to create some tables in my database but I'm getting some errors... The one which is causing the most trouble is Msg 1767, Level 16, State 0, Line 38
Foreign key 'ten_fk' references invalid table 'Tenant'.
I'm not sure why it's complaining... can anyone help me out here?
Cheers!
-- Mitch Curtis
-- A2create.sql
-- Set the active database to KWEA.
USE KWEA;
-- Drop existing tables (if any).
DROP TABLE Ownership;
DROP TABLE Tenant;
DROP TABLE Staff;
DROP TABLE Property;
DROP TABLE Property_Status_Report;
DROP TABLE Property_Owner;
DROP TABLE Placement_Record;
DROP TABLE Candidate_Tenant;
DROP TABLE Waiting_List;
-- Create new tables.
CREATE TABLE Waiting_List
(
waiting# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
candidate_name VARCHAR(20) NOT NULL,
anticipated_start_date SMALLDATETIME NULL,
anticipated_end_date SMALLDATETIME NULL,
max_affordable_rent SMALLMONEY NOT NULL
);
CREATE TABLE Candidate_Tenant
(
candidate# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
waiting# INT NULL,
name VARCHAR(20) NOT NULL,
phone_number INT NOT NULL,
required_property_type VARCHAR(10) NOT NULL,
CONSTRAINT w_fk FOREIGN KEY(waiting#) REFERENCES Waiting_List(waiting#)
);
CREATE TABLE Placement_Record
(
opening# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
tenant# INT NOT NULL,
start_date SMALLDATETIME NOT NULL,
end_date SMALLDATETIME NOT NULL,
total_bonds SMALLMONEY NOT NULL,
weekly_rent SMALLMONEY NOT NULL,
CONSTRAINT ten_fk FOREIGN KEY(tenant#) REFERENCES Tenant(tenant#)
);
CREATE TABLE Property_Owner
(
owner# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
name VARCHAR(20) NOT NULL,
phone_number INT NOT NULL
);
CREATE TABLE Property_Status_Report
(
address VARCHAR(30) NOT NULL,
report_date SMALLDATETIME NOT NULL,
weekly_rent SMALLMONEY NOT NULL,
month_rent_start_date SMALLDATETIME NOT NULL,
month_rent_end_date SMALLDATETIME NOT NULL,
maintenance_fee SMALLMONEY NOT NULL,
month_inspection_history VARCHAR(30) NULL,
CONSTRAINT ar_pk PRIMARY KEY(address, report_date),
FOREIGN KEY(address) REFERENCES Property(address)
);
CREATE TABLE Property
(
address VARCHAR(30) PRIMARY KEY NOT NULL,
staff# INT IDENTITY(1,1) NOT NULL,
type VARCHAR NOT NULL,
occupant_limit INT NOT NULL,
comments VARCHAR(30) NULL,
FOREIGN KEY(staff#) REFERENCES Staff(staff#)
);
CREATE TABLE Staff
(
staff# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
manager# INT NOT NULL,
name VARCHAR(20) NOT NULL,
FOREIGN KEY(manager#) REFERENCES Staff(staff#)
);
CREATE TABLE Tenant
(
tenant# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
staff# INT NOT NULL,
property_address VARCHAR(30) NOT NULL,
name VARCHAR(20) NOT NULL,
phone_number INT NOT NULL,
street VARCHAR(20) NOT NULL,
city VARCHAR(20) NOT NULL,
postcode INT NOT NULL,
category VARCHAR(10) NOT NULL,
comments VARCHAR(30) NULL,
FOREIGN KEY(staff#) REFERENCES Staff(staff#),
FOREIGN KEY(property_address) REFERENCES Property(address)
);
CREATE TABLE Ownership
(
address VARCHAR(30) NOT NULL,
owner# INT NOT NULL,
CONSTRAINT ao_pk PRIMARY KEY(address, owner#),
FOREIGN KEY(address) REFERENCES Property(address),
FOREIGN KEY(owner#) REFERENCES Property_Owner(owner#)
);
-- Display tables.
SELECT * FROM Waiting_List;
SELECT * FROM Candidate_Tenant;
SELECT * FROM Placement_Record;
SELECT * FROM Property_Owner;
SELECT * FROM Property_Status_Report;
SELECT * FROM Property;
SELECT * FROM Staff;
SELECT * FROM Tenant;
SELECT * FROM Ownership;
Errors:
Msg 3701, Level 11, State 5, Line 8
Cannot drop the table 'Ownership', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 9
Cannot drop the table 'Tenant', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 10
Cannot drop the table 'Staff', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 11
Cannot drop the table 'Property', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 12
Cannot drop the table 'Property_Status_Report', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 13
Cannot drop the table 'Property_Owner', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 14
Cannot drop the table 'Placement_Record', because it does not exist or you do not have permission.
Msg 1767, Level 16, State 0, Line 38
Foreign key 'ten_fk' references invalid table 'Tenant'.
Msg 1750, Level 16, State 0, Line 38
Could not create constraint. See previous errors.
View 4 Replies
View Related
Sep 24, 2006
How do you add a reference if the assembly you want to reference does not live in C:WindowsMicrosoft.NETFrameworkv2.0.50727? It doesn't seem to make sense that you cannot access the connections the AquireConnection() method serves since the interfaces are defined in Microsoft.SQLServer.DTSRuntimeWrap.dll (in C:Program FilesMicrosoft SQL Server90SDKAssemblies).
For example the code below works fine, in order to make it work I had to copy DTSRuntimeWrap.dll to v2.0.50727, surely any assembly which is been added to the GAC should be available as a referece?
Public Sub Main()
Dim cmgr As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = Dts.Connections("FTP Connection Manager")
Dim cn As Object = cmgr.AcquireConnection(Nothing)
Dim ftpConnection As IDTSFtpClientConnection90
ftpConnection = TryCast(cn, IDTSFtpClientConnection90)
ftpConnection.Connect()
Dim folders As String()
Dim files As String()
ftpConnection.GetListing(folders, files)
ftpConnection.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
View 4 Replies
View Related
May 30, 2006
Hi everyone,
When you've got -for example- a derived column task linked with a Flat file and then you change any field and come back to derived column task again you have select that field
with two possibilities:
1-€˜Leave as invalid column reference€™ /
2-€˜Delete invalid column reference€™
It€™s easy when just one is affected but when you have eight or ten is very tedious do the same one by one. Any way for to do same but selecting more than one?
Thanks for any input,
View 3 Replies
View Related
Mar 19, 2008
Hi All,
I'm pretty new with SSIS. I have written some custom component which will read from flat file and write it into database. Between those processes, there are other processes which will analyse and transform the data being transfered. Everytime, I remove a column from upstream component, I have to go through each component to fix up column reference mapping using Invalid Column References Editor. There are lots of clicking involves just to remove a column from upstream component. My package has about 15 components. I am just wondering, is there anyway for us to prevent that editor to pop up, instead fixing invalid column reference programatically in ReInitializeMetadata?
Thanks,
Will
View 7 Replies
View Related
Jan 28, 2007
Hi all,
Would anyone know of any references (online or books) that make it easier for experienced Oracle people to Learn SQL Server. The type of things I'd like to know for example are
The environment for SQL server on a PC (e.g. where the
datafiles are, what's the replacement for tnslistener - general architecture info)
Any significant differences that I'd need to know for creating tables and applications, like what do I use instead of varchar2 for example.
that should get me started.
tia,
Dave
View 1 Replies
View Related
Sep 8, 2015
I need to join 2 tables but the join needs to account for 2 seperate columns.
for example:
select
a. type
a. prod_code
a. prod_type
b. division
from table1 a
left join table2 b
on a. prod_code = b. prod_code
and a. prod_type = b. prod_type
The issue is that you may have only the prod_code or prod_type and null value for the other in table1.
Ideally I want it to check for both then if 1 isn't available then it draws the division of the available. having both or one or the other determines the division it falls under.
View 5 Replies
View Related
Oct 12, 2015
I have a database of 900+ tables with around 3000 SPs, and views. Manually I reviewed few tables and found that tables are not referenced with FK and I applied few. There are lots of tables and SPs using them in join statement, Is there any way with which I can get each tables missing references, any DMV or other manual script which tells about this?
View 2 Replies
View Related
Sep 11, 2007
Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.
Thanks.
View 1 Replies
View Related
Oct 4, 2007
Hello and thank you for the help in advance.
I know this has to be possible maybe I am just missing somthing.
I am creating a matrix report which will compare year by year quotes to orders The issue is quotes and orders each have their own dataset. I will be pivoting on JobType which is in both datasets and spelled the same. Is there a way to do this or will I have to figure out how to union the tables? If not possible why does it allow you to name the dataset in the expression?
Thanks, Leo
View 1 Replies
View Related
Feb 12, 2014
Is there any possibility to create a foreign key references more than one tables.
say for example,
Create table sample1(id int primary key)
Create table sample2(id int primary key)
Create table sample3(
id int primary key,
CONSTRAINT fk1 FOREIGN KEY REFERENCES sample1 (ID),CONSTRAINT fk1 FOREIGN KEY REFERENCES sample2 (ID))
this shows no error while creating, but in the data insertion it shows error..
View 8 Replies
View Related
Apr 9, 2007
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
View 3 Replies
View Related
Apr 26, 2006
This is my problem: I have a dynamic web reference for a SQL Server UDF coded in C#, but I am unsure of where URL for the webservice is being read from.
I am working with SQL Server 2005 to create a User Defined Function, however I need to have a web reference to the SQLReportingService2005 web service. I will be distributing this function to my customers so I need this web reference to be dynamic.
Although the webservice says it is dynamic, I do not see a app.config file to place the changing URL. Does anyone know where a dynamic web service pulls the URL from in this case?
Thanks in advance,
Sean
View 1 Replies
View Related
Feb 6, 2008
Hi There,
Our company deals with financial education and typically has 9 different databases which have some cross referenced stored procedures. Every time we replicate Production database into TEST and DEV environments, we had to manually update the database references in Stored procedures. and it usually takes atleast a week and until then all the dev and test work has to wait.
Hence, I wanted to write a script, Here the code below.
-- These two variables must contain a valid database name.
DECLARE @vchSearch VarChar(15),
@vchReplacement VarChar(15)
SET @vchSearch = 'Search'
SET @vchReplacement = 'Replacement'
/*
-- Select the Kaplan Database Names in the Current Server
*/
DECLARE @tblDBNames TABLE (vchDBName VarChar(30))
INSERT INTO
@tblDBNames
SELECT
Name
FROM
MASTER.DBO.SYSDATABASES
WHERE
Has_DBAccess(Name)=1
And Name IN ( 'DB_DEV', 'DB_TEST', 'DB_PROD', 'WEBDB_DEV', 'WEBDB_TEST', 'WEBDB_PROD' , 'FINDB_DEV', 'FINDB_TEST', 'FINDB_PROD')
--SELECT * FROM @DBNames
IF @vchSearch NOT IN (SELECT vchDBName FROM @tblDBNames)
BEGIN
PRINT 'Not a Valid Search DB Name'
GOTO Terminate
END
IF @vchReplacement NOT IN (SELECT vchDBNAME FROM @tblDBNames)
BEGIN
PRINT 'Not a Valid Replacement DB Name'
GOTO Terminate
END
-- We have Valid DB Names, lets proceed...
--USE @vchReplacement
SET @vchSearch = '%' + @vchSearch + '..%'
SET @vchReplacement = '%' + @vchReplacement + '..%'
-- Get Names of Stored Procedures to be altered
DECLARE @tblSProcNames TABLE (vchSPName VarChar(100))
INSERT INTO
@tblSProcNames
SELECT
DISTINCT so.Name
FROM
SYSOBJECTS so
INNER JOIN SYSCOMMENTS sc
ON sc.Id = so.Id
WHERE
so.XType='P'
AND sc.Text LIKE @vchSearch
ORDER BY
so.name
-- Now, the table @tblSprocNames has the names of stored procedures to be updated.
-- And we have to Some HOW ?!! grab the stored proc definition and use REPLACE() to
-- update the database reference
-- Then, use cursors to loop through each stored proc and upate the reference
Now, I have got stuck how to extract the body of a stored procedure into a variable.
Please Help.... I dont want spend weeks of time in the future to do this work manually.
Madhu
View 24 Replies
View Related
Feb 3, 2015
Is there any way to enforce table references in stored procedures? For Example, we have stored procedures with a ton of different formats, "dbo.table", "table", "db.dbo.table", etc. Can we make it so that for every stored procedure, the reference must be at least "dbo.table"?
View 1 Replies
View Related
Dec 14, 2006
Hi
I created a new database called "TestReportServer" as mentioned in the installation instruction but I didn't
see (or could select) the option "Create the report server database in SharePoint integrated mode".
How can I select this option? Do I need to remove the reporing services and reinstall it again? Any suggestions?
After creating the database I get the error 'Some or all identity references could not be translated'.
The user I selected is a local administrator and has permission to all groups starting with wss.
I guess the database is not created as a sharepoint integration mode as I can start Server Management Studio
and see the database. Is that a correct assumption?
I hope somebody out there can help as I am strating to bang my head towards my desk right now :-)
View 6 Replies
View Related
Oct 23, 2004
Hello:
Need some serious help with this one...
Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?
The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
Thanks!
Sky
View 1 Replies
View Related
Feb 22, 2008
I've just found out about the concept that a table should only be references by foreign keys a maximum of 253 times throughout the database. I was hoping someone could give me a better idea of the dangers of disregarding this recommendation.
In our database, we have a Users table, which contains all of the users of a given system. In nearly every other table in our database, we have a field to indicate which user created the record. This is a reference back to the Users table. But as the number of tables has grown, we've surpassed that 253 limit. All I can tell, practically, is that I can no longer delete from the Users table. The query will fail, telling me the query optimizer ran out of memory, and that I should simplify my query.
The Users table is the only table that even comes close to this number of foreign key references. I don't even mind being unable to delete from the table. I'm mostly wondering if there are other problems with my design that will cause issues?
Is there a better way to keep track of who created a record in the database? Is it bad design to reference my Users table in so many places?
Thanks,
View 1 Replies
View Related
Jun 17, 2014
See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.
DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))
INSERT INTO @t
SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL
SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL
[Code] .....
View 9 Replies
View Related
Aug 11, 2014
i've been looking at moving one of our processed from excel (+vba) into t-sql to make life easier but am stuck.
We have lots of tasks that are assigned to work groups which we want to distribute evenly across the work groups. This is a simple task for ntile.. However when these tasks are no longer required they are removed which leaves the groups uneven. When new tasks are added we want to still try to keep these groups balanced.
EG Existing groups :
GroupName - Task Count
Group1 - 1000
Group2 - 999
Group3 - 998
If we were to add 6 new tasks they would have more assigned to Group 2 & 3 as they have less than group 1.
Task 1 - Group3
Task 2 - Group3
Task 3 -Group2
Task 4 - Group1
Task 5 - Group2
Task 6 - Group3
Sample tables
Create table GroupTable
(GroupID int, Name varchar(200) )
Insert into GroupTable values (1,'Group1')
Insert into GroupTable values (2,'Group2')
Insert into GroupTable values (3,'Group3')
Create table Jobs(jobid int identity(1,1), name varchar(100),Groupid int)
--Existing tasks
Insert into Jobs(name,Groupid) values ('Task1',1)
Insert into Jobs(name,Groupid) values ('Task2',1)
Insert into Jobs(name,Groupid) values ('Task3',1)
Insert into Jobs(name,Groupid) values ('Task4',1)
Insert into Jobs(name,Groupid) values ('Task5',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task7',3)
-- New tasks
Insert into Jobs(name) values ('TaskA')
Insert into Jobs(name) values ('TaskB')
Insert into Jobs(name) values ('TaskC')
Insert into Jobs(name) values ('TaskD')
Insert into Jobs(name) values ('TaskE')
Insert into Jobs(name) values ('TaskF')
This gives us 6 unassigned tasks and a uneven group assignment
GROUPNAME TASK_COUNT
<none> 6
Group1 4
Group2 3
Group3 2
This means the new tasks will be assigned like this
TaskA - Group3
TaskB - Group3
TaskC - Group2
TaskD - Group1
TaskE - Group2
TaskF - Group3
View 5 Replies
View Related
Sep 9, 2014
When sizing products we use predefined size groups that the users can choose any or all of the sizes from. For example if i size group consisted of sizes (6,8,10) they could use all sizes (6,8,10) or just (6,8) or just (10) if required. Similarly, if a group consisted of (S,M,L,XL) they could choose to only buy (S,L). They cannot choose across groups, so would not be able to choose (6,S)
Once the required sizing is determined they then assign size mixes to the sizes to denote how much of the buy will be in that size. So for example if we had 3 sizes: (6,8,10) and they had the associated mixes (25%,25%,50%) that would mean we would buy 25% of size 6 and 50% of size 10. All size mixes must add up to 100% in total.
The users do analysis to determine what sizes they wish to buy and how much of it.
We also have a franchise portion of the business that have some predefined size mixes. They use the same base size groups as above, but the rule is that they can only use sizes that the particular product is being bought in.
So if the assigned franchise mix is S (50%), M (50%) and the main mix was S (100%) then the franchise mix would only be able to then have the S size.
We would then eliminate the sizes from the franchise mix and then to ensure that the franchise mix still adds to 100 we would then pro-rate up the franchise mix to give a new mix. To do this I divide one by the total the remaining size mixes to get a ratio and then multiple the mixes by this factor.
In the case above not be able to use the M size and would only use the S.This would be
-Total of remaining mixes, in this case only size S for simplicity
1 / 0.5 = 2
-multiple original mix by this factor
0.5 * 2 = 1
size S would now be 100% instead of 50%
The issue I'm having is that on occasion some of the totals are adding up to 100.01% because another one of the requirements is that it needs to be 4 decimal places (0.1015 would represent 10.15% in excel)
Here is a shortened version of the code with some test data:
DECLARE @SizeRange TABLE
(
SizeProfileIDINTNOT NULL,
CPCVARCHAR(3)NOT NULL,
SizeNameVARCHAR(5)NOT NULL,
SizeMixDECIMAL(14,4)NOT NULL
[Code] ...
How to get this to round to 4dp and still total 100%?
View 2 Replies
View Related
Feb 26, 2015
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
View 9 Replies
View Related
Jan 21, 2015
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore
, lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
View 3 Replies
View Related
Jan 25, 2008
I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.
I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:
ID1
ID2
DisplayData
1
1
A
1
2
B
1
3
C
2
1
A
2
2
B
2
3
C
Parent group is on ID1, child group is on ID2, report would show:
1
1
A
2
A
3
A
2
1
A
2
A
3
A
Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...
View 2 Replies
View Related
