Database

You're welcome. It took me more time to type in the sample code than it did to work out how to to it and run it. :-\ The Entity-Attribute-Value (EAV) pattern is one that is well known in database design. Many people have tried to use it instead of the standard normalized approach. It is not generally something that I would recommend but it suits your requirements perfectly. One thing to remember is to have a Clustered Index on the ObjectId and AttributeId to prevent table scans. This isn't really an issue with small data sets but will become an issue when you have more objects and attributes. Ian

Vuyiswa Maseko wrote: SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '') FROM #Temp (NOLOCK) The #Temp table would be empty there? How about using a real table, instead of a temporary one? I are Troll :suss:

This works too!   Thanks Russell.

Go with Luc's suggestion rethink your data structure, this one will drive you nuts. Try this CREATE TABLE [dbo].[Attr]( [AttrID] [int] IDENTITY(1,1) NOT NULL, [CustomerID] int, [AttrType] [varchar](255) NULL, [AttrValue] [varchar](255) NULL, [Modified] [datetime] NULL, [ModifiedBy] [varchar](50) NULL) Where AttrType = your column Name and AttrValue = 1/0. CustomerID is the foreign key to the entity you are applying the attributes to. You would only have 4 rows representing the ACDF colums (0 deemed to not exist) Never underestimate the power of human stupidity RAH

If you want to advertise with CP then go through the usual channel. Never underestimate the power of human stupidity RAH

Is the picture on your pc or the server? When you run this SQL it executes on the server hosting sql server, so directories are located on there, not your PC Bob Ashfield Consultants Ltd Proud to be a Code Project MVP

Try this Assuming the tables have the following structure with data Tbl1 ID1 NUM1 1 10 2 20 Tbl2 ID2 NUM2 1 11 2 22 Tbl3 ID3 NUM3 1 1 2 2 3 3 Tbl4 ID4 NUM4 1 41 Tbl5 ID5 NUM5 1 109 Tbl6 ID6 NUM6 1 61 2 62 Query: SELECT AvgNum = AVG(N)FROM( SELECT N=NUM1 FROM Tbl1 UNION ALL SELECT NUM2 FROM Tbl2 UNION ALL SELECT NUM3 FROM Tbl3 UNION ALL SELECT NUM4 FROM Tbl4 UNION ALL SELECT NUM5 FROM Tbl5 UNION ALL SELECT NUM6 FROM Tbl6 )X Output: AvgNum 31 Niladri Biswas

Thank you Holmes.

alright so here is my edit, that works. (Had an idea) Is it a good idea? SELECT InvoicePayment.DateCreated AS InvoicePaymentDate, InvoicePayment.InvoiceID, InvoicePayment.Amount, Customer.Name, Invoice.CustomerID, Invoice.InvoiceNumber FROM (Select * from CustomerAccountPayment where CustomerID = @CustomerID and EntryDate <=@EndDate) as CustomerAccountPayment INNER JOIN CustomerAccountPaymentDetail ON CustomerAccountPayment.ID = CustomerAccountPaymentDetail.CustomerAccountPaymentID RIGHT OUTER JOIN InvoicePayment INNER JOIN Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN Customer ON Invoice.CustomerID = Customer.ID INNER JOIN PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID ON CustomerAccountPaymentDetail.InvoicePaymentID = InvoicePayment.ID WHERE (Customer.ID = @CustomerID) AND (CustomerAccountPaymentDetail.ID IS NULL) AND (Invoice.Void = 'False') AND (PaymentMethod.IsCredit = 'True') and (InvoicePayment.DateCreated <= @EndDate) union SELECT InvoicePayment.DateCreated AS InvoicePaymentDate, InvoicePayment.InvoiceID, InvoicePayment.Amount - SUM(case CustomerAccountPayment.Void when 'True' then 0 when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2) end) AS Amount, Customer.Name, Invoice.CustomerID, Invoice.InvoiceNumber FROM InvoicePayment INNER JOIN Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN Customer ON Invoice.CustomerID = Customer.ID INNER JOIN CustomerAccountPaymentDetail ON InvoicePayment.ID = CustomerAccountPaymentDetail.InvoicePaymentID INNER JOIN PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID INNER JOIN (Select * from CustomerAccountPayment where CustomerID = @CustomerID and EntryDate <=@EndDate) as CustomerAccountPayment ON CustomerAccountPaymentDetail.CustomerAccountPaymentID = CustomerAccountPayment.ID WHERE (Invoice.Void = 'False') AND (PaymentMethod.IsCredit = 'True') and Invoice.CustomerID = @CustomerID and InvoicePayment.DateCreated <= @EndDate GROUP BY InvoicePayment.DateCreated, InvoicePayment.InvoiceID, Invoice.InvoiceNumber, InvoicePayment.Amount, InvoicePayment.DateCreated, Customer.Name, Invoice.CustomerID HAVING (SUM(case CustomerAccountPayment.Void when 'True' then

With SQL all you are doing is providing the Servername and Database name to connect. This is the same no matter where you connect from. Directly connecting to the underlying files is something that is only of use in on-the-fly demos and not to be used for serious applications. SQL Express will work fine for this, though it has some limitations See this Microsoft article for details[^]. The two main issues will be a reduced functionality Management Studio GUI and the 4GB data limit. If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) [My Articles]  [My Website]

Pleased to have been of help Bob Ashfield Consultants Ltd Proud to be a Code Project MVP

Not all years have a week 53, only in the 1-1 in that weeks is thursday or later In Word you can only store 2 bytes. That is why I use Writer.

Your challenge now is to get the bool check to one line/statement. Breaking the steps up is the correct way to go when understanding the process of achieving a goal, well done. Then finding and understanding the correct syntax that achieves the 5 lines of code in 1 statement is the real bonus :) ! Never underestimate the power of human stupidity RAH

This will only work if there are no more than 1 record in qtnmaster per itemcode.

I think d@nish's solution is better for your case, even though you found my inner join to be new and helpful to you. I am sorry I don't have a book or website to recommend to you on SQL. I learned something about SQL by reading an Oracle manual on SQL*Plus (which has lots of very interesting examples) about 20 years ago (in 1990) when I was working on a project for a friend. I moved a few times (to different countries) ever since and lost that manual. It was for an old version of Oracle anyway (which ran on VMS with no graphical user interface.) The new Oracle manuals seem to have nothing similar to the one I read. Your best bet is to find a good book on SQL.

thanx man...i'll try that...

Oracle is quite different to MSSql in that aspect. Databases in Oracle are really quite separated from each other. So I suggest that you read up on schemas and tablespaces, and eventually database links in Oracle.

Hi Mycroft, You are right. It is applicable for Insert, Update & Delete. Have a look a) OUTPUT Clause (Transact-SQL)[^] b) SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE[^] Niladri Biswas