|
select id,name
from stud s
where not exists
(
select c.id
from class c
where c.id = s.id
)
and depid in
(
select depid
from dep
where rid=@rid
)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I'm trying to write better TSQL to reduce the amount of functions I have to do things.
Here I'm trying to add the total amount of sales from a column called GrandTotal from multiple tables
into a single result. First I'm not sure if this is even possible to do, but I think it can be done.
I've come this far, but I get 2 results returned. I'm just trying to consolidate them into one.
I tried SELECT SUM(TotalAmount) as the wrapper, but I get a syntax error
I tried UNION ALL without the )( around around it, but I get an error on the last )
The tables are identical in columns
DECLARE @StartDate AS Date;
DECLARE @StopDate AS Date;
SET @StartDate = CONVERT(CHAR(10),GETDATE(),101);
SET @StopDate = CONVERT(CHAR(10),DATEADD(d,1, GETDATE()),101);
SELECT
(
SELECT SUM(GrandTotal)
FROM CompletedOrdersHistory
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)
UNION ALL
(
SELECT SUM(GrandTotal)
FROM CompletedOrders
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)
|
|
|
|
|
Try - note that I named the sum fields.
Select sum(GTotal) Grand
From(
SELECT
(
SELECT SUM(GrandTotal) as GTotal
FROM CompletedOrdersHistory
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)
UNION ALL
(
SELECT SUM(GrandTotal) as GTotal
FROM CompletedOrders
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
))
Personally I would normally include the common ID and use a Group By and then an inner join to where you need the Grand Totals
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks!
I understand the example, but I still get the ")" error in which I don't understand. There's only 3 of them.
The common ID and the GroupBy with the inner join is going to take me some time to experiment with.
I'm not really sure where to start with that one.
|
|
|
|
|
Try to turn it into a CTE and see if that works:
WITH sums AS (
SELECT SUM(GrandTotal) GrandTotal
FROM CompletedOrdersHistory
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
UNION ALL
SELECT SUM(GrandTotal) GrandTotal
FROM CompletedOrders
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)
SELECT Sum(GrandTotal) AS GrandTotal
FROM sums Or use a subquery which is the same thing but different syntax.
|
|
|
|
|
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)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
