February 5, 2013 at 6:23 am
Hi everybody,
I am an intern in charge of creating a "quality dept" database for an automotive parts manufacturer. I Have a "part number ID" column where the ID is not unique (test benchs that could receive each part several times). I would need a "partial auto-increment" to know the number of times ("cycle number") the part went through the bench test. The "part number ID" together with the number could be a primary key.
Do i need to filter by "part number ID" and "date/hour" and then add 1 to the last "cycle number"? If the data table is already filtered by "part number ID" and "date/hour", will it be quick?
Sorry if the question is not interesting...I m new to sqlserver, new to slserver central, new to forums, and i think i may need your help a few times till june so i start to start!
February 5, 2013 at 8:16 am
Hi and welcome to SSC! You will find this forum is full of helpful and knowledgeable people.
It is impossible from your description to offer much advice because there just aren't enough details. It sounds like maybe you want a composite key of PartID and CycleNum? If you can provide a more detailed explanation of the problem we can probably help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2013 at 10:06 am
Welcome... it would help if you provided DDL and sample data and what you expect to see as a result. There's a link to a posting guide somewhere, one of these days I'll add it to my cool sig.
Based on what you've said here's a sample "sample" and "DDL" of what you can provide to help us help you.
declare @BenchTest as table(ID int Identity(1,1), PartNumber int
, PartName varchar(10), TestDate datetime default getdate());
insert into @BenchTest(PartNumber, PartName)
values(1,'A'),(2,'B'),(3,'C'),(4,'D'),(1,'A'),(2,'B'),(3,'C')
,(4,'D'),(1,'A'),(2,'B'),(3,'C'),(4,'D'),(1,'A'),(2,'B')
select ID
, PartNumber
, PartName
, ROW_NUMBER()Over(Partition by PartNumber Order by (Select Null)) CycleNum
, count(*)Over(Partition by PartNumber) TotalCycle
, TestDate
from @BenchTest
order by PartNumber
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
February 5, 2013 at 10:10 am
Want a cool sig (2/5/2013)
Welcome... it would help if you provided DDL and sample data and what you expect to see as a result. There's a link to a posting guide somewhere, one of these days I'll add it to my cool sig.
The link you are referring to is the first one in my signature. Unfortunately it doesn't do much good here because the OP is " in charge of creating a "quality dept" database for an automotive parts manufacturer". Since they are creating it there is no ddl yet. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2013 at 10:35 am
The link you are referring to is the first one in my signature. Unfortunately it doesn't do much good here because the OP is " in charge of creating a "quality dept" database for an automotive parts manufacturer". Since they are creating it there is no ddl yet. 😀
Thanks Sean for pointing that out.. 🙂
Gonna update my sig.
There should be a "guide to carefully reading posts" link somewhere... 😀
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
February 5, 2013 at 11:00 am
Want a cool sig (2/5/2013)
The link you are referring to is the first one in my signature. Unfortunately it doesn't do much good here because the OP is " in charge of creating a "quality dept" database for an automotive parts manufacturer". Since they are creating it there is no ddl yet. 😀
Thanks Sean for pointing that out.. 🙂
Gonna update my sig.
There should be a "guide to carefully reading posts" link somewhere... 😀
That's an awful lot to ask of an intern. Geez, that's a lot to ask of someone with only 2 yrs' full-time experience!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2013 at 10:27 pm
I'd say that Cool Sig's response was pretty close to what I would do:
DECLARE @BenchTest TABLE
(ID INT IDENTITY
,PartNumber INT
,PartName VARCHAR(10)
,TestDate DATETIME);
INSERT INTO @BenchTest(PartNumber, PartName, TestDate)
VALUES(1,'A','2012-01-01 11:00'),(2,'B','2012-01-01 12:00')
,(3,'C','2012-01-01 13:00'),(4,'D','2012-01-01 14:00')
,(1,'A','2012-01-02 12:00'),(2,'B','2012-01-02 13:00')
,(3,'C','2012-01-02 14:00'),(4,'D','2012-01-01 15:00')
,(1,'A','2012-01-03 12:00'),(2,'B','2012-01-03 13:00')
,(3,'C','2012-01-03 15:00'),(4,'D','2012-01-01 15:00')
,(1,'A','2012-01-04 12:00'),(2,'B','2012-01-04 12:00')
SELECT ID
,PartNumber
,PartName
,CycleNum=ROW_NUMBER() OVER (PARTITION BY PartNumber ORDER BY ID)
,TotalCycle=COUNT(*)Over(Partition by PartNumber)
,TestDate
FROM @BenchTest
ORDER BY PartNumber
The main difference being to order by ID in the ROW_NUMBER().
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 6, 2013 at 3:21 am
ScottPletcher (2/5/2013)
That's an awful lot to ask of an intern. Geez, that's a lot to ask of someone with only 2 yrs' full-time experience!
Its called doing it on the cheap, as interns often get significantly lower salaries than someone with the right level of experience, I hope the OP has a Technical Mentor in the company.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 6, 2013 at 4:39 am
Thanks million! That's exactly what i need! I had not imaginedd i would have a so quick answer! thank you very much
Actually the company didn't install yet sql server. They re not quick here! So i am trying to gather all the code i will need as soon as i ll be able to start. Basically, the first table i m working on will be a 200 columns table with, as i said, a "Parts number" column and manufacturing measurements for that part, the same part showing off sevral times. I thought of creating a few "views" on which i ll base the main queries. One "GLOBAL VIEW" will gather general informations, the most important and the source of most of the queries they need on a day to day basis and that's where i 'll create the "cycle number" column i think. I ll link this GLOBAL VIEW to more specific views containing measurements by categories.
I ll let you know as soon as i start!
February 6, 2013 at 5:06 am
special thanks to "dwain" and"Want a cool sig".
To jason : no, i don't have a technical mentor...it s certainly gonna be hard! i ll spend some time on sqlserver central i guess...
they didn't know they would do a database...the IT structure is limited and they re in charge of the network.
I proposed the solution of the sql server database cose they re doing the same shit everyday to collect data and prepare reports. I have a gloabl idea of what i need to do : a proper relational database and a few basic queries with reports (reports services stuff) to monitor production data.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply