Assistance building a query...

I am trying to generate some datasets with some queries...

With a given series information, it should return PART_NOs that has STD
= 1 and a unique price at that particular 'START', and keeping the
'TYPE' in consideration...

DB examples below:

Main DB

IDPART_NOSERIESSTD
1A-1A1
2A-2A1
3A-3A1
4D-1D1
5D-2D0

Price DB

IDPART_IDTYPESTARTPRICE
501X100050
511X1000040
521Y100060
531Y1000050
542X100050
552X1000040
562Y100060
572Y1000050
582X100090

etc.

main.ID and Price.PART_ID are paired together.

So in an example case, lets say I am querying for SERIES A, with TYPE
X. A table should be outputted something like

PART_NO
A-1100050
A-11000040
A-3100090

Note how it skipped printing A2 because the price is the same as A1.

I'm really looking for the SQL code here... I can't get it to filter on
distinct price.

SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
FROM MAIN, PRICING
WHERE (MAIN.SERIES LIKE 'A')
AND (MAIN.STD = '1')
AND (PRICING.PRICE != '')
AND (PRICING.TYPE = 'X')
AND (MAIN.ID = PRICING.PART_ID)

I've been trying to use GROUP BY and HAVING to get what I need but it
doesn't seem to fit the bill. I guess I'm not terribly clear on how I
can use the SQL DISTINCT command...? If I try and use it in my WHERE
statement it gives me syntax errors, from what I understand you can
only have distinct in the select statement? I'm not sure how to
integrate that into the query to suit my needs.

Thanks for any help.A bit of clarification on my problem

If I just do a straight SQL distinct on my select statement, it does
what I want when you get down to it, but it completely destroys the
organization of the table. The part numbers were entered in a certain
manner and I do not believe they can be reorganized through any typical
sort. For example A-105A is higher then A-400B, but if you sorted in
Excel (for example) it would put 105 below 400

mazzarin@.gmail.com wrote:
> I am trying to generate some datasets with some queries...
> With a given series information, it should return PART_NOs that has STD
> = 1 and a unique price at that particular 'START', and keeping the
> 'TYPE' in consideration...
> DB examples below:
> Main DB
> IDPART_NOSERIESSTD
> 1A-1A1
> 2A-2A1
> 3A-3A1
> 4D-1D1
> 5D-2D0
> Price DB
> IDPART_IDTYPESTARTPRICE
> 501X100050
> 511X1000040
> 521Y100060
> 531Y1000050
> 542X100050
> 552X1000040
> 562Y100060
> 572Y1000050
> 582X100090
> etc.
> main.ID and Price.PART_ID are paired together.
>
> So in an example case, lets say I am querying for SERIES A, with TYPE
> X. A table should be outputted something like
> PART_NO
> A-1100050
> A-11000040
> A-3100090
> Note how it skipped printing A2 because the price is the same as A1.
>
> I'm really looking for the SQL code here... I can't get it to filter on
> distinct price.
> SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
> FROM MAIN, PRICING
> WHERE (MAIN.SERIES LIKE 'A')
> AND (MAIN.STD = '1')
> AND (PRICING.PRICE != '')
> AND (PRICING.TYPE = 'X')
> AND (MAIN.ID = PRICING.PART_ID)
> I've been trying to use GROUP BY and HAVING to get what I need but it
> doesn't seem to fit the bill. I guess I'm not terribly clear on how I
> can use the SQL DISTINCT command...? If I try and use it in my WHERE
> statement it gives me syntax errors, from what I understand you can
> only have distinct in the select statement? I'm not sure how to
> integrate that into the query to suit my needs.
> Thanks for any help.|||Actually never mind, it doesn't do exactly what I want it to do... All
the prices are still duplicated, ideally I should only have at most 3
results being returned (according to the actual data being fed in)

I am beyond confused heh

I think I might have to do the filtering outside of SQL

mazzarin@.gmail.com wrote:
> A bit of clarification on my problem
> If I just do a straight SQL distinct on my select statement, it does
> what I want when you get down to it, but it completely destroys the
> organization of the table. The part numbers were entered in a certain
> manner and I do not believe they can be reorganized through any typical
> sort. For example A-105A is higher then A-400B, but if you sorted in
> Excel (for example) it would put 105 below 400
>
> mazzarin@.gmail.com wrote:
> > I am trying to generate some datasets with some queries...
> > With a given series information, it should return PART_NOs that has STD
> > = 1 and a unique price at that particular 'START', and keeping the
> > 'TYPE' in consideration...
> > DB examples below:
> > Main DB
> > IDPART_NOSERIESSTD
> > 1A-1A1
> > 2A-2A1
> > 3A-3A1
> > 4D-1D1
> > 5D-2D0
> > Price DB
> > IDPART_IDTYPESTARTPRICE
> > 501X100050
> > 511X1000040
> > 521Y100060
> > 531Y1000050
> > 542X100050
> > 552X1000040
> > 562Y100060
> > 572Y1000050
> > 582X100090
> > etc.
> > main.ID and Price.PART_ID are paired together.
> > So in an example case, lets say I am querying for SERIES A, with TYPE
> > X. A table should be outputted something like
> > PART_NO
> > A-1100050
> > A-11000040
> > A-3100090
> > Note how it skipped printing A2 because the price is the same as A1.
> > I'm really looking for the SQL code here... I can't get it to filter on
> > distinct price.
> > SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
> > FROM MAIN, PRICING
> > WHERE (MAIN.SERIES LIKE 'A')
> > AND (MAIN.STD = '1')
> > AND (PRICING.PRICE != '')
> > AND (PRICING.TYPE = 'X')
> > AND (MAIN.ID = PRICING.PART_ID)
> > I've been trying to use GROUP BY and HAVING to get what I need but it
> > doesn't seem to fit the bill. I guess I'm not terribly clear on how I
> > can use the SQL DISTINCT command...? If I try and use it in my WHERE
> > statement it gives me syntax errors, from what I understand you can
> > only have distinct in the select statement? I'm not sure how to
> > integrate that into the query to suit my needs.
> > Thanks for any help.|||(mazzarin@.gmail.com) writes:
> I am trying to generate some datasets with some queries...
> With a given series information, it should return PART_NOs that has STD
>= 1 and a unique price at that particular 'START', and keeping the
> 'TYPE' in consideration...
> DB examples below:
> Main DB
> ID PART_NO SERIES STD
> 1 A-1 A 1
> 2 A-2 A 1
> 3 A-3 A 1
> 4 D-1 D 1
> 5 D-2 D 0
> Price DB
> ID PART_ID TYPE START PRICE
> 50 1 X 1000 50
> 51 1 X 10000 40
> 52 1 Y 1000 60
> 53 1 Y 10000 50
> 54 2 X 1000 50
> 55 2 X 10000 40
> 56 2 Y 1000 60
> 57 2 Y 10000 50
> 58 2 X 1000 90
> etc.
> main.ID and Price.PART_ID are paired together.
> So in an example case, lets say I am querying for SERIES A, with TYPE
> X. A table should be outputted something like
> PART_NO
> A-1 1000 50
> A-1 10000 40
> A-3 1000 90
> Note how it skipped printing A2 because the price is the same as A1.

But why does A-3 appear? Ir does not seem to appear in the Price DB
at all?

If there is an A-4 with the same values as A-1 would that be printed?

I'm sorry, but as you have presented the problem there are two many
unknowns. Furthermore, there is a standard recommendation that you include
in your post:

o CREATE TABLE statements for you table(s).
o INSERT statemetns with sample data.
o The desired result given the sample.

This makes it possible to easily copy and paste into Query Analyzer
to play around with the data and develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I actually ended up doing multiple queries, passing info between VBA
and SQL. Its not as nice of a solution but it works. Thank you for your
advice though. I will keep it in mind next time!

Erland Sommarskog wrote:
> (mazzarin@.gmail.com) writes:
> > I am trying to generate some datasets with some queries...
> > With a given series information, it should return PART_NOs that has STD
> >= 1 and a unique price at that particular 'START', and keeping the
> > 'TYPE' in consideration...
> > DB examples below:
> > Main DB
> > ID PART_NO SERIES STD
> > 1 A-1 A 1
> > 2 A-2 A 1
> > 3 A-3 A 1
> > 4 D-1 D 1
> > 5 D-2 D 0
> > Price DB
> > ID PART_ID TYPE START PRICE
> > 50 1 X 1000 50
> > 51 1 X 10000 40
> > 52 1 Y 1000 60
> > 53 1 Y 10000 50
> > 54 2 X 1000 50
> > 55 2 X 10000 40
> > 56 2 Y 1000 60
> > 57 2 Y 10000 50
> > 58 2 X 1000 90
> > etc.
> > main.ID and Price.PART_ID are paired together.
> > So in an example case, lets say I am querying for SERIES A, with TYPE
> > X. A table should be outputted something like
> > PART_NO
> > A-1 1000 50
> > A-1 10000 40
> > A-3 1000 90
> > Note how it skipped printing A2 because the price is the same as A1.
> But why does A-3 appear? Ir does not seem to appear in the Price DB
> at all?
> If there is an A-4 with the same values as A-1 would that be printed?
> I'm sorry, but as you have presented the problem there are two many
> unknowns. Furthermore, there is a standard recommendation that you include
> in your post:
> o CREATE TABLE statements for you table(s).
> o INSERT statemetns with sample data.
> o The desired result given the sample.
> This makes it possible to easily copy and paste into Query Analyzer
> to play around with the data and develop a tested solution.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Assigning a unique ID

I have an asp page in which the user completes some information and the data
is stored in an SQL database. I want to automatically assign an unique ID
number (can start with 1) to each record when the data is saved to the
database and display this number on the confirmation page.
This is probably much easier that I am making it. Any help would be
appreciated.
Thanks,CREATE TABLE dbo.CustomerData
(
CustomerDataID INT IDENTITY(1,1),
SomeData VARCHAR(32)
)
GO
CREATE PROCEDURE dbo.AddCustomerData
@.SomeData VARCHAR(32),
@.CustomerDataID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.CustomerData(SomeData) SELECT @.SomeData;
SET @.CustomerDataID = SCOPE_IDENTITY();
END
GO
DECLARE @.id INT;
EXEC dbo.AddCustomerData 'foo', @.id OUTPUT;
SELECT new_id = @.id;
SELECT CustomerDataID, SomeData FROM dbo.CustomerData;
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:DDAB88EE-9D62-499F-9D02-BFA99948B222@.microsoft.com...
>I have an asp page in which the user completes some information and the
>data
> is stored in an SQL database. I want to automatically assign an unique ID
> number (can start with 1) to each record when the data is saved to the
> database and display this number on the confirmation page.
> This is probably much easier that I am making it. Any help would be
> appreciated.
> Thanks,|||Do I drop this code on the asp page?
"Aaron Bertrand [SQL Server MVP]" wrote:

> CREATE TABLE dbo.CustomerData
> (
> CustomerDataID INT IDENTITY(1,1),
> SomeData VARCHAR(32)
> )
> GO
> CREATE PROCEDURE dbo.AddCustomerData
> @.SomeData VARCHAR(32),
> @.CustomerDataID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.CustomerData(SomeData) SELECT @.SomeData;
> SET @.CustomerDataID = SCOPE_IDENTITY();
> END
> GO
> DECLARE @.id INT;
> EXEC dbo.AddCustomerData 'foo', @.id OUTPUT;
> SELECT new_id = @.id;
> SELECT CustomerDataID, SomeData FROM dbo.CustomerData;
>
>
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:DDAB88EE-9D62-499F-9D02-BFA99948B222@.microsoft.com...
>
>|||No, this is T-SQL code, not ASP code. For some help running the stored
procedure from ASP, see an ASP newsgroup, if these articles don't clear it
up:
http://www.aspfaq.com/2201
http://www.aspfaq.com/params.htm
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:A33184A6-09AF-4CF8-AB13-44009E40E7CB@.microsoft.com...
> Do I drop this code on the asp page?|||Gasp! No, you copy / paste it into the OnBlur event your website's flaming
logo. Please tell me this is not a e-commerce, financial, or defense related
website!
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:A33184A6-09AF-4CF8-AB13-44009E40E7CB@.microsoft.com...
> Do I drop this code on the asp page?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Are you kidding. It is just an company Intranet page.
Man, if they let me program fo rthe feds, look out...lol
Just trying to automate a process.
Let me ask this on the example from Aaron.
I get that dbo.CustomerData is my DB name (Compliance). My ID field is
called RequestID so is that CustomerDataID or SomeData? What would the othe
r
one be (the name of the table?).
Sorry, not a programmer by trade but I certainly appreciate the help...
"JT" wrote:

> Gasp! No, you copy / paste it into the OnBlur event your website's flaming
> logo. Please tell me this is not a e-commerce, financial, or defense relat
ed
> website!
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:A33184A6-09AF-4CF8-AB13-44009E40E7CB@.microsoft.com...
>
>|||OK, it takes me awhile to grasp the concept but here is what I got:
CREATE TABLE dbo.CommunityService
(
RefNum Int IDENTITY(1,1),
BranchDept VARCHAR(75),
Region VARCHAR(50),
EmployeeName VARCHAR(50),
CompletedBy VARCHAR(50),
Organization VARCHAR(125),
Contacts VARCHAR(50),
Phone VARCHAR(50),
Address VARCHAR(255),
CensusArea NTEXT,
ServiceType NTEXT,
ServiceDesc NTEXT,
BenefitDesc NTEXT,
MoneyEquip NTEXT,
Comments NTEXT,
TimeStamp DATETIME
)
GO
CREATE PROCEDURE dbo.AddRequestID
@.Region VARCHAR(50),
@.RefNum INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.CommunityService(Region) SELECT @.Region;
SET @.RefNum = SCOPE_IDENTITY();
END
GO
DECLARE @.id INT;
EXEC dbo.AddRequestID, @.id OUTPUT;
SELECT new_id = @.id;
SELECT RefNum, Region FROM dbo.CommunityService;
My question is that I am getting an error on Line 3 near ','. It all seems
OK so what am I missing?
********************************
"Ken D." wrote:
> Are you kidding. It is just an company Intranet page.
> Man, if they let me program fo rthe feds, look out...lol
> Just trying to automate a process.
> Let me ask this on the example from Aaron.
> I get that dbo.CustomerData is my DB name (Compliance). My ID field is
> called RequestID so is that CustomerDataID or SomeData? What would the ot
her
> one be (the name of the table?).
> Sorry, not a programmer by trade but I certainly appreciate the help...
> "JT" wrote:
>|||> My question is that I am getting an error on Line 3 near ','. It all
> seems
> OK so what am I missing?
Part of the code sample I sent. What does this mean?

> EXEC dbo.AddRequestID, @.id OUTPUT;
You forgot to include your region parameter:
EXEC dbo.AddRequestID 'NorthEast', @.id OUTPUT;|||Cool. I added 'test" and changed my seed to 0. It worked wonderful.
Thanks and so sorry for the ignorance.
Have a great day Aaron.
"Aaron Bertrand [SQL Server MVP]" wrote:

>
> Part of the code sample I sent. What does this mean?
>
> You forgot to include your region parameter:
> EXEC dbo.AddRequestID 'NorthEast', @.id OUTPUT;
>
>
>
>|||Aaron,
One last question. What is I wanted to add a prefix to the ID. Is that
possible?
"Aaron Bertrand [SQL Server MVP]" wrote:

> CREATE TABLE dbo.CustomerData
> (
> CustomerDataID INT IDENTITY(1,1),
> SomeData VARCHAR(32)
> )
> GO
> CREATE PROCEDURE dbo.AddCustomerData
> @.SomeData VARCHAR(32),
> @.CustomerDataID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.CustomerData(SomeData) SELECT @.SomeData;
> SET @.CustomerDataID = SCOPE_IDENTITY();
> END
> GO
> DECLARE @.id INT;
> EXEC dbo.AddCustomerData 'foo', @.id OUTPUT;
> SELECT new_id = @.id;
> SELECT CustomerDataID, SomeData FROM dbo.CustomerData;
>
>
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:DDAB88EE-9D62-499F-9D02-BFA99948B222@.microsoft.com...
>
>