|
Common Table Expression
Yet another way to build more complex queries, Interesting
I had no idea I could express it like that, and would of never thought of looking for it in search.
I was close on the first couple of tries.
Thanks Jorgen!
I did read up on those articles you gave me, and have been designing my new stuff in such a manner.
Writing TSQL for reports can get more complex to produce the desired results.
|
|
|
|
|
Glad to be able to help. 
Appreciative feed back is the best driving force there is!
|
|
|
|
|
I have a issue when I create a trigger for insert or update
Create trigger In_Name on CheckStatus for insert, update
as
declare @id int
Select @id=id from inserted where id=@id
if exists (Select * from CheckStatus where id=@id)
begin
rollback transaction
update CheckStatus Set levels=levels+1 where id=@id
end
else
insert into CheckStatus (id,levels) values (@id,1)
But the trigger do not execute my opinion (I want to update "levels" when id field exists, inversely inserting id and levels)
Please, giving a hand, thanks a lot
|
|
|
|
|
If your database design needs you to update primary keys (ID Fields) you have bigger problems that trigger design. A primary field should never be modified, with the possible exception of deploying data across different systems.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks for answering me. My idea want to update or insert "Levels" fields, @id do not primary keys, it is a foreign keys form other table. I'm a new database studying, please help me.
|
|
|
|
|
Select @id=id from inserted where id=@id
There's your issue - @id is going to be null as you have only just declared it in the line above.
The trigger does fire it just does not do anything as @id is always going to be null.
Get rid of the
where id=@id
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
ok, I show all code again after editing
CREATE trigger In_LoaiDG1 on KhaoSat_LoaiDG1
for insert,update
as
declare @NgayDG smalldatetime
declare @MaLoaiTT int
Select @MaLoaiTT=MaLoaiTT,@NgayDG=NgayDG from inserted
if exists (Select count(*) from KhaoSat_LoaiDG1 where KhaoSat_LoaiDG1.MaLoaiTT=@MaLoaiTT and KhaoSat_LoaiDG1.NgayDG=@NgayDG)
begin
rollback transaction
update KhaoSat_LoaiDG1 set Muc1=Muc1+1 where MaLoaiTT=@MaLoaiTT and NgayDG=@NgayDG
end
else
insert into KhaoSat_LoaiDG1(MaLoaiTT,Muc1,NgayDG) values (@MaLoaiTT,1,@NgayDG)
Update is ok but not insert.I do not know why, please help me.
|
|
|
|
|
It looks like you are performing an insert within your trigger on the table the trigger is running from
CREATE trigger In_LoaiDG1 on KhaoSat_LoaiDG1
insert into KhaoSat_LoaiDG1
Any clues as to why this may be a bad idea?
Hint - infinity...
Just remove:
else
insert into KhaoSat_LoaiDG1(MaLoaiTT,Muc1,NgayDG) values (@MaLoaiTT,1,@NgayDG)
and see what happens.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 10-Mar-14 12:17pm.
|
|
|
|
|
Dear reader,
I am totally new in database-programming. I have available all the standard-books but still, I feel lost in that area. I am working with Sql Server Express-Edition
One of my basic questions is: How can I test SQL-commands from any kind of tool. It seems, that the express-edition does not have all the tools installed.
Another question is: Is it ok to start programming with "basic Ado.Net" and not focussing on EF or Linq?
Best regards
modified 10-Mar-14 13:39pm.
|
|
|
|
|
|
Frygreen wrote: Another question is: Is it ok to start programming with "basic Ado.Net" and not focussing on EF or Linq?
Oh yes!
|
|
|
|
|
I will back Jorgen on this one, do NOT use EF as your data access layer if you intend to become a serious developer who builds data centric solutions. Linq to SQL is basically a disaster wrapped in obscure syntax, learn TSQL and you will reap the benefits your entire career.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Frygreen wrote: It seems, that the express-edition does not have all the tools installed.
There are different installation options for SQL Express - the larger "Express with advanced services" or "Express with tools" downloads include Management Studio, whereas the smaller "Express" download doesn't.
If you've downloaded the version without the tools, there's a separate "Management Studio"-only download available on the same page:
http://www.microsoft.com/en-gb/download/details.aspx?id=29062[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
...for the moment.... thank you for your kind replies
|
|
|
|
|
how to convert an integer to days months years
i have a number of days resulted from sum of datediff to many rows as integer
i want to convert this days into days months years
like this
pr start end days_diff
-------------------------------------------------------
237 2010-06-01 2010-11-30 182
237 2010-12-01 2011-05-31 181
237 2011-06-01 2012-05-31 365
237 2012-06-01 2013-05-31 364
237 2013-06-01 2014-05-31 364
this total a 1456 days
i want to convert this days into
4 years 0 months 1 days
when i use the regular sum for each record into days months years it creates
3 years 12 months 1 days
or any way that produce the same result even not by calculate the sum of the days difference
but produce the same result
4 years 0 months 1 days
not
3 years 12 months 1 days
thanks for help
Lost & Forgotten
|
|
|
|
|
I didn't got, how you compute the years, months and days?
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
start end
-------------------------------------------------------
2010-06-01 2010-11-30
2010-12-01 2011-05-31
2011-06-01 2012-05-31
2012-06-01 2013-05-31
2013-06-01 2014-05-31
suppose this an experience periods
i want to get total of this experience
thanks for help
Lost & Forgotten
|
|
|
|
|
OK...
Get minimum of start (select min(start) from ... where ...)
Get maximum of end (select max(end) from ... where ...)
Do datediff between the two...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
thats right in case the periods are connected some cases the periods not connected like
start end
-----------------------------
2010-06-01 2010-11-30
2011-06-01 2012-05-31
2013-06-01 2014-05-31
Lost & Forgotten
|
|
|
|
|
I see...
In that case you can do something like this
select sum(datediff(d, start,end)) from ... where ...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
How do you do the calculation? If the number of months comes out at 12 then that is 1 year.
|
|
|
|
|
start end
-------------------------------------------------------
2010-06-01 2010-11-30
2010-12-01 2011-05-31
2011-06-01 2012-05-31
2012-06-01 2013-05-31
2013-06-01 2014-05-31
suppose this an experience periods
i want to get total of this experience
thanks for help
Lost & Forgotten
|
|
|
|
|
Assuming you are storing the values as DateTime types, then you can just use a simple subtraction to get a difference value, which can be converted to years, months and days.
|
|
|
|
|
You'll need to pick a starting date, since different months have a different number of days.
For example, 30 days from 1st March would be 30 days; from 1st April, it would be one month; from 1st February 2014, it would be one month and two days; from 1st February 2012, it would be one month and one day.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
I would like to ask how can I make a complete silent installation and configuration for the MySQL server.
i have downloaded the Windows (x86, 32-bit), MSI Installer and I want to make a silent installation for my setup package.
Technology News @ www.JassimRahma.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
