|
loyal ginger wrote: There is no DateTime type in SQLite
I find that incredible considering all the problems that arise from storing date values as strings.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a situation where I wanted to have the weekno for the given date.
If year is changing then weekno should add with the previous weekno.
Below is the table contains two columns, Bug date and corresponding weeknumber... till 12/31/2011 I am getting correctly but after that am getting wrong result. for 01/01/2012 the weeknumber should be 158 but am getting 157, can any one help me solve this problem..Pls see my logic below... am using sql server 2008.
Bug Date weeknumber
10/25/2009 44
11/01/2009 45
12/30/2009 53
12/31/2009 53
01/01/2010 53
01/03/2010 54
12/30/2010 105
12/31/2010 105
01/01/2011 105
01/02/2011 106
01/04/2011 106
12/30/2011 157
12/31/2011 157
01/01/2012 158
01/08/2012 158
DECLARE @FirstDate char(20)
DECLARE @baseyear char(20)
SET @FirstDate =(SELECT Min(Bugdate) FROM BugsDB_DefectVolume)
SET @baseyear=(SELECT Datepart(year,@FirstDate)) //am considering the first date as base year.
UPDATE BugsDB_DefectVolume
SET weeknumber= Datepart(wk,@BugDate) + ((Datepart(year,@BugDate) - @baseyear) *52),
StartDayofWeek=DATEadd(day, (1- DATEPART(dw,@BugDate)), @BugDate)
WHERE BugDate=@BugDate and [Product ID]=@ProductID
|
|
|
|
|
Does this work:
DECLARE @FirstDate char(20)
SET @FirstDate =(SELECT Min(Bugdate) FROM BugsDB_DefectVolume)
UPDATE BugsDB_DefectVolume
SET weeknumber= DateDiff(wk, @FirstDate, @BugDate) +1,
StartDayofWeek=DATEadd(day, (1- DATEPART(dw,@BugDate)), @BugDate)
WHERE BugDate=@BugDate and [Product ID]=@ProductID
Wout Louwers
|
|
|
|
|
Yes its working fine but only the problem is when date is 01/01/2012 its giving the weeknumber as 157. this is wrong. it should be 158. To achieve this where can I do the modification in my existing query???
|
|
|
|
|
What I was trying to tell you is that you should DateDiff to calculate the number of weeks. Not every year has 52 weeks!
Wout Louwers
|
|
|
|
|
It's because contrary to popular belief, there are not 52 weeks in a year. There are 52 weeks and one spare day left over. So, if you just assume 52 weeks every year, eventually your calculation gets out of line. That's why financial years occasionally have a week 53 to stop things from drifting off.
Basically about 1 year in every 5 or 6 has 53 weeks. It's not a straight 1 in 7 because leap years throw it out (every fourth year has 52 weeks and 2 days left over which makes the calculation trickier). There is an ISO standard which defines it. 2004 had 53 weeks, 2009 has 53 weeks, 2010 has 52, the next 53 week year will be 2015.
Basically, if 1st Jan is a Thursday (or if it is a leap year either a Wednesday or a Thursday) then there are 53 weeks in the year.
For what you want, you might be able to calculate the number of days from a base date and then divide by 7. It might be easier than trying to work out weeks per year. Does that give the right answer?
|
|
|
|
|
And you got down voted for that answer - FTFY
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I will be the first to admit that I don't know what I'm doing. But I have dabbled a little here and there so I'm willing to give this a try...maybe you will too?
I work at a company with some very antiquated software. We have a program that was built using no less that 8 different DOS-based applications strung together. We've managed to keep this thing working for quit a while now and still add new data to the source files and recompile them regularly. But, we're getting pretty nervous about the age and complexity of this application and want to replace it with a database-driven application.
The input to the application is a text file that contains a bunch of 5-digit alpha-numeric codes like "E5101" or "E5101QTY=2". Each code represents either some very specific instructions to our manufacturing shop or some Bill of Material data (BOM). For example the "E5101" code means to take the part we're building, put some glue into the hole, and then insert a bushing into that hole and let the glue dry. The example of the "E5101QTY=2" is a BOM code and it basically means that we will need two of the bushings that are used for the assembly. I hope this is clear as it is a simple but powerful concept.
Now, the output of the application is another text file which contains these same codes but in a logical order. For example the code to issue some raw material needs to come before the code to cut that material and that needs to come before a code to paint that material. And BOM codes need to be separated from codes that are work instructions. So the order of the codes has been revised based on some rules/tags/parameters that we have been assigning.
Finally, my question is how should I approach replacing this application with a database? Meaning, do you have some suggestions about what types of products are already out there for doing this, or can you point me to some projects here that are similar in function?
It is really pretty simple in some ways, take a limited set of strings and reorder them based on a set of rules. But the rules are as complex as a geneology tree, which is one of the main software components that comprise this application. It was developed at the Mormon university BYU where they are big into tracing their ancestry. This worked for industrial applications because to correctly order these codes the user needs to be able to build something like a database and have a way of relating the contents. But this was done before databases had been formalized.
So, please be kind to a newbie and give me some sage advice on the best way to approach this.
Thanks
Noah
|
|
|
|
|
|
Yes David,
thanks for your response, the only one so far that is meaningful! I do plenty of flow charting and process mapping so that's already been covered. I do understand our current system inputs/outputs/relations. I think the real questions center around whether there are some products out there that could be used/adapted to this or if not then what type of database structure/model would be recommended. In this case can any examples or samples from this board be referenced?
|
|
|
|
|
I guess company in which you are working,they need to buy new software to replace that old software.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi friends,
I am stucked in a problem. I am trying export the data to the server through export import wizard. The data is going but the primary key and the auto-incrementation on a column was not transfered.
I also tried through generating scripts but same results.
And on the server there is no provision to restore through .bak file.
So please assist me.
cheers,
sneha
|
|
|
|
|
I have used the method of scripting the table schema creating it in the receiving database and then do the copy. It will be interesting to see if there is a one step solution.
Buy the way if you create using MS SQL 2000 you might need to tell it to script the keys.
Good Luck
djj
|
|
|
|
|
You are talking about 2 very different things, the data and a database constraint (primary key).
Are you saying the primary key data is not exporting or that your scripting of the table does not include the primary key?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In the Import/Export Wizard make sure that you select the contstraints for data transfer.
|
|
|
|
|
hai
what is the escape character for hyphen(-) in SQL?
thanks
mahe
|
|
|
|
|
Try this
\-
Use the backslash character(\) to escape a single character or symbol. Only the character immediately following the backslash is escaped. For example, a query of code\-project matches code-project and code project.
or if you don't at all the hyphen(-) try like this
[^-] . This will negate the character class.
For more information, look here
Regular Expression Basic Syntax Reference[^]
Niladri Biswas
|
|
|
|
|
Hi,
I have one stored procedure for inserting values to 5 tables at a time.So there are 5 insert statements in a single SP.Here some fields are common ie.there is customer address and sponsor address and all these addresses are stored in a single table.Firstly I insert customer details in the customer table and contact details to the contact table.Also sponsor contact addresses are stored in the contact table.How can I handle this
Thanks
Denny
|
|
|
|
|
I assume you have primary key/foreign key relationships happening... if you do, use select @@IDENTITY to find the identify of the item you have inserted, so you can use it in the next insert.
eg:
insert into table1(name) values('Fred')
go
insert into table2(address, nameid) select '10 Smith Street', @@IDENTITY
go
|
|
|
|
|
|
...or look at SCOPE_IDENTITY()
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
hai all
i am facing some problem in SQL.I am searching the one string(ex.roll-over) in table
its returning wrong results.
my table contains,
dable datas
table_1 is table name
number1 text1
1 notify us the day you roll check over.
2 notify us theday you roll check .
3 notify us the day you roll check roll-over.
4 notify us the day you roll check roll over.
my query is
select * from table_1 WHERE CONTAINS(text1, '"roll-over"')
select * from table_1 WHERE CONTAINS(text1, 'roll-over')
in both case i am getting below results.
results
number1 text1
1 notify us the day you roll check over.
2 notify us theday you roll check .
3 notify us the day you roll check roll-over.
4 notify us the day you roll check roll over.
here it should return only one result insted of four.
is there any thing wrong in Query if not can any one help to from the correct query.
i think Bcoz os hyphen the result is returinig either roll or over presents.
thanks
mahe
|
|
|
|
|
What about:
select * from table_1 where text1 like '%roll-over%'
|
|
|
|
|
thanks for ur reply
its working fine.
but
is there any way to form query with CONTAINS key word.
mahe
|
|
|
|
|
From BOL
Is a predicate used to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches.
So you used a fuzzy search key word, got a fuzzy return when you wanted a precise return. Wrong tool for the job.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
