Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

conditionnal auto-increment Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 6:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 7:28 AM
Points: 20, Visits: 106
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!
Post #1415795
Posted Tuesday, February 5, 2013 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1415875
Posted Tuesday, February 5, 2013 10:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
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 - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1415971
Posted Tuesday, February 5, 2013 10:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1415975
Posted Tuesday, February 5, 2013 10:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
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 - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1415991
Posted Tuesday, February 5, 2013 11:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 1,970, Visits: 2,909
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1416005
Posted Tuesday, February 5, 2013 10:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 3,614, Visits: 5,228
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1416237
Posted Wednesday, February 6, 2013 3:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1416366
Posted Wednesday, February 6, 2013 4:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 7:28 AM
Points: 20, Visits: 106
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!
Post #1416407
Posted Wednesday, February 6, 2013 5:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 7:28 AM
Points: 20, Visits: 106
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.
Post #1416416
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse