November 29, 2017 at 8:32 am
USE [iMIS_Prod]
GO
/****** Object: Table [dbo].[BM_OnlinePass_Subscription_Archive] Script Date: 11/29/2017 9:52:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BM_OnlinePass_Subscription_Archive](
[id] [int] NULL,
[clientID] [varchar](10) NULL,
[startDate] [datetime] NULL,
[endDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Table [dbo].[Name] Script Date: 11/29/2017 9:54:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Name](
[ID] [int] NOT NULL CONSTRAINT [DF_Name_ID] DEFAULT (''),
[CO_ID] [int] NOT NULL CONSTRAINT [DF_Name_CO_ID] DEFAULT (''),
[COMPANY] [varchar](30) NOT NULL CONSTRAINT [DF_Name_COMPANY] DEFAULT ('')
CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
Insert into [dbo].[Client] (ID, CO_ID, COMPANY)
values
(111793,10065,'LAW OFFICES OF MICHAEL P HEALY'),
(156909,10065,'LAW OFFICES OF MICHAEL P HEALY'),
(157298,10065,'LAW FIRM OF MICHAEL P. HEALY A'),
(157997,10065,'LAW OFFICES OF MICHAEL P HEALY'),
(228875,10065,'LAW OFFICES OF MICHAEL P HEALY')
Insert into [dbo].[Subscription] (ID, ClientID, StartDate, EndDate)
Values
(403,10065,'2012-12-27 00:00:00.000','2013-12-26 00:00:00.000'),
(3549,10065,'2013-12-26 00:00:00.000','2014-12-26 00:00:00.000'),
(8862,10065,'2014-12-26 00:00:00.000','2015-12-26 00:00:00.000')
select
n.CO_ID as FirmID,
n.COMPANY as "Company Name",
b.startDate,
b.endDate
from Client as n --inner join Orders as o on n.ID = o.BT_ID
CROSS APPLY (SELECT TOP 1 *
FROM BM_OnlinePass_Subscription_Archive ba
WHERE ba.ClientID = n.CO_ID
ORDER BY StartDate DESC) as b
Select Distinct does not work here since Company Name can be different (sometimes very different) for the same CO_ID. Any suggestions? Thank you.
November 29, 2017 at 8:58 am
Thanks for providing DDL and sample data, but please test before posting.
What results are you expecting?
John
November 29, 2017 at 9:01 am
There's a standard way to deal with a many-to-many relationship - a junction table. Think Invoices and Invoice LineItems and Products.
CREATE TABLE LineItem(
InvoiceNo INT,
ProductID INT,
Quantity TINYINT
PRIMARY KEY (InvoiceNo, ProductID),
FOREIGN KEY InvoiceNo REFERENCES Invoice(InvoiceNo)
,FOREIGN KEY ProductID REFERENCES Product(ProductID)
...
November 29, 2017 at 9:28 am
John Mitchell-245523 - Wednesday, November 29, 2017 8:58 AMThanks for providing DDL and sample data, but please test before posting.What results are you expecting?
John
Sorry for the error in Create Table. It should be datatype INT not Varchar in ClientID.
The result I expect is one line
10065, LAW OFFICES OF MICHAEL P HEALY, 2014-12-26 00:00:00.000, 2015-12-26 00:00:00.000
Right now I have two lines.
November 29, 2017 at 9:40 am
rkordonsky 63916 - Wednesday, November 29, 2017 9:28 AMJohn Mitchell-245523 - Wednesday, November 29, 2017 8:58 AMThanks for providing DDL and sample data, but please test before posting.What results are you expecting?
John
Sorry for the error in Create Table. It should be datatype INT not Varchar in ClientID.
Not just that - the table names are wrong.
The result I expect is one line
10065, LAW OFFICES OF MICHAEL P HEALY, 2014-12-26 00:00:00.000, 2015-12-26 00:00:00.000
How do you decide which Company Name should be displayed?
Right now I have two lines.
Interesting - I have five! Maybe the modifications I made to your code weren't the right ones.
John
November 29, 2017 at 10:42 am
John Mitchell-245523 - Wednesday, November 29, 2017 9:40 AMrkordonsky 63916 - Wednesday, November 29, 2017 9:28 AMJohn Mitchell-245523 - Wednesday, November 29, 2017 8:58 AMThanks for providing DDL and sample data, but please test before posting.What results are you expecting?
John
Sorry for the error in Create Table. It should be datatype INT not Varchar in ClientID.
Not just that - the table names are wrong.
The result I expect is one line
10065, LAW OFFICES OF MICHAEL P HEALY, 2014-12-26 00:00:00.000, 2015-12-26 00:00:00.000How do you decide which Company Name should be displayed?
Right now I have two lines.
Interesting - I have five! Maybe the modifications I made to your code weren't the right ones.
John
I have two because I use "select distinct ..."
November 29, 2017 at 11:44 pm
With all the corrections I finally used the following which to be honest I don't like as it is a kludge and will give varying results if the company name is continually misspelled:
SELECT co_id, MAX(company), B.startdate, B.enddate
FROM dbo.Client n
CROSS APPLY (SELECT TOP 1 *
FROM dbo.BM_OnlinePass_Subscription_Archive ba
WHERE ba.ClientID = n.CO_ID
ORDER BY StartDate DESC) AS b
GROUP BY co_id, B.startdate, B.enddate;
However it gives you want you want given the limited data. There is clearly something amiss as your Client table is not normalised; the current table looks more like a matters table.
...
December 1, 2017 at 10:00 am
HappyGeek - Wednesday, November 29, 2017 11:44 PMWith all the corrections I finally used the following which to be honest I don't like as it is a kludge and will give varying results if the company name is continually misspelled:
SELECT co_id, MAX(company), B.startdate, B.enddate
FROM dbo.Client n
CROSS APPLY (SELECT TOP 1 *
FROM dbo.BM_OnlinePass_Subscription_Archive ba
WHERE ba.ClientID = n.CO_ID
ORDER BY StartDate DESC) AS b
GROUP BY co_id, B.startdate, B.enddate;However it gives you want you want given the limited data. There is clearly something amiss as your Client table is not normalised; the current table looks more like a matters table.
I solve it by using Temp Table.
December 1, 2017 at 10:02 am
rkordonsky 63916 - Wednesday, November 29, 2017 8:32 AM.
Can you please get a book on basic RDBMS and what a table is? It has to have a key, by definitioC. This is not an optioC. This is a fundamental rule. But what you posted because all the columns are nullable in your archives table, there’s no, he can ever have a key. The tables have no relations among themselves. So by using this is in RDBMS? Why do you believe in a generic “idâ€; this is not exist in RDBMS to be an identifier, it must identify something in particular. Since you can do know math on an identifier, it can never be a numeric value. This is basic data modeling. What you are doing is mimicking pointer chains from your old filesystems. I’m going to try and rewrite your invalid DDL and add the constraints that you apparently do not know about.
Tables model sets of entities. So you’re telling us you have only one client, only one subscription and you don’t even know a name is an attribute of an entity, not an entity itself. You might also learn what a DUNS is and how it is used as an identifier of legal entities.
SQL Server has had a DATE data type for some time now.
CREATE TABLE BM_OnlinePass_Subscription_Archive
(client_duns CHAR(9) NOT NULL
REFERENCES Clients(client_duns)
ON UPDATE CASCADE,
subscription_start_date DATE NOT NULL,
subscription_end_date DATE NOT NULL,
CHECK (subscription_start_date <= subscription_end_date),
PRIMARY KEY (client_duns, subscription_start_date, subscription_end_date));
see how we have a key and constraints in between columns within the table? This is how you design a table.
CREATE TABLE Clients
(client_duns CHAR(9) NOT NULL PRIMARY KEY,
client_name VARCHAR(30) NOT NULL);
yes, it is possible to have more than one DUNS assigned to the same legal entity, or for there to be more than one entity with the same name. Once this error is discovered, it has to be corrected one of two ways. Either the redundant DUNS has to be removed or a new legal name for the entity has to be provided to one of the redundant DUNS. This is why the DUNS is the only acceptable identifier and we don’t make up our own if we can avoid it.
-– garbage data
(‘11179300’, 'LAW OFFICES OF MICHAEL P HEALY'),
(‘15690900’, 'LAW OFFICES OF MICHAEL P HEALY'),
(‘15729800’, 'LAW FIRM OF MICHAEL P. HEALY A'),
(‘15799700’, 'LAW OFFICES OF MICHAEL P HEALY'),
(‘22887500’, 'LAW OFFICES OF MICHAEL P HEALY');
what you need to do is pick one of these and use it as the key for the table. You can pick the smallest DUNS number
INSERT INTO Subscriptions (client_duns, subscription_start_date, subscription_end_date)
VALUES
(‘11179300’, '2012-12-27', '2013-12-26'),
(‘11179300’, ‘2013-12-26', '2014-12-26'),
(‘11179300’, '2014-12-26', '2015-12-26');
>>SELECT DISTINCT does not work here since client_name can be different (sometimes very different) for the same client_id. Any suggestions?<<
start over and correct your design. You basically have taken paper files, copied them into pointer chains, not even bothered with REFERENCES, keys or constraints, and of course is produced garbage. If you don’t fix the problem, or you’re going to wind up with is a huge non-performant kludge
Please post DDL and follow ANSI/ISO standards when asking for help.
December 1, 2017 at 10:21 am
pietlinden - Wednesday, November 29, 2017 9:01 AMThere's a standard way to deal with a many-to-many relationship - a junction table. Think Invoices and Invoice LineItems and Products.CREATE TABLE LineItem(
InvoiceNo INT,
ProductID INT,
Quantity TINYINT
PRIMARY KEY (InvoiceNo, ProductID),
FOREIGN KEY InvoiceNo REFERENCES Invoice(InvoiceNo)
,FOREIGN KEY ProductID REFERENCES Product(ProductID)
...
Terms like "parent table", "child table" and "junction table" are not part of RDBMS. Those referred to pointer chain constructs in the old network databases.In RDBMS, we use the concepts of referenced and referencing tables.
Furthermore, your sample DDL is wrong. By definition, a table must have a key; a key cannot be made up of nullable columns. Identifiers like invoice numbers and product codes cannot be integers (you do not do math on them!). "Quantity" is what ISO 11179 calls an attribute property, it has to have an attribute; that means it has to be the quantity of something in particular, not a vague generic thing. Finally, unlike the terminology used in the old network databases, a table has a plural or collective name because it models a set. The network databases are based on record at a time processing and traversals, so their files were singular. Here is one way to write your line items table in RDBMS, and get out of the old network database mindset:
CREATE TABLE LineItems
(invoice_nbr CHAR(10) NOT NULL
REFERENCES Invoices(invoice_nbr)
ON DELETE CASCADE,
product_upc
REFERENCES Products(product_upc)
ON UPDATE CASCSADE,
order_qty INTEGER NOT NULL
CHECK (order_qty > 0),
PRIMARY KEY (invoice_nbr, product_upc),
);
Please post DDL and follow ANSI/ISO standards when asking for help.
December 4, 2017 at 2:33 am
jcelko212 32090 - Friday, December 1, 2017 10:21 AMTerms like "parent table", "child table" and "junction table" are not part of RDBMS.
A rose by any other name still smells sweet. But if you're going to be a stickler for terminology, maybe you shouldn't have said in your previous post, "The tables have no relations among themselves". A table is a relation, so that's nonsense. I think you meant relationship.
John
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply