Hello all,
Follows a simplified DDL of the table where i'm working on:
CREATE TABLE Test
(
TestID bigint NOT NULL ,
SubTestID bigint NOT NULL ,
Name varchar (50),
Master bigint NOT NULL ,
CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
(
TestID,
SubTestID
)
)
What I'm trying to achieve is to collect and retrieve all rows of which
Master differs from the value 2 and TestID is unique.
So far, the best I achieved was:
SELECT
counter.TestID
FROM
(
SELECT
TestID,
COUNT(TestID) AS Total
FROM
Test
WHERE Master <> 2
GROUP BY
TestID
) as counter
WHERE counter.Total = 1
However this strategy doesn't suit me since I need the TestID/SubTestID
pair to be returned. Needless to say, introducing SubTestID in the
above query will invalidate de aggregate function.
Can someone please help?Mario
Needless to say, that posting sample data + expected result will help to
bring a right solution. No offence.
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Lazy solution:
SELECT TestID, MAX(SubTestID ) AS SubTestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1
More structured solution:
SELECT t1.TestID, t1.SubTestID
FROM Test t1
INNER JOIN (
SELECT TestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1) a
ON t1.TestID = a.TestID
Jacco Schalkwijk
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Try,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
COUNT(distinct TestID) = 1
) as a
on t.TestID = a.TestID
go
AMB
"Mario F." wrote:
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||One method:
SELECT
a.TestID,
a.SubTestID
FROM Test a
JOIN
(
SELECT
TestID
FROM Test
WHERE Master <> 2
GROUP BY
TestID
HAVING COUNT(*) = 1
) AS counter ON counter.TestID = a.TestID
Hope this helps.
Dan Guzman
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Correction,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
min(SubTestID) = max(SubTestID)
) as a
on t.TestID = a.TestID
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> t.*
> from
> test as t
> inner join
> (
> SELECT
> TestID
> FROM
> Test
> WHERE
> Master <> 2
> GROUP BY
> TestID
> having
> COUNT(distinct TestID) = 1
> ) as a
> on t.TestID = a.TestID
> go
>
> AMB
> "Mario F." wrote:
>|||The Having clause. Of course. Thank you everyone.
Mario F.
*** Sent via Developersdex http://www.examnotes.net ***sql
Showing posts with label ddl. Show all posts
Showing posts with label ddl. Show all posts
Wednesday, March 21, 2012
Find the first day the account passes a threshold
Using daily transactions, I need to find the first day an account deposits
more than 20 dollars (+ or -)
please see the DDL below, a sql statement, and the desired output. Thanks
create table #bankaccounts
(
accountid int not null
, transdate datetime not null
, primary key (accountid, transdate)
, amttran decimal(19,2) not null
)
set nocount on
insert #bankaccounts
values (1, '20060101', 10)
insert #bankaccounts
values (1, '20060102', 5)
insert #bankaccounts
values (1, '20060103', 12)
insert #bankaccounts
values (1, '20060106', 15.50)
insert #bankaccounts
values (1, '20060107', 13)
insert #bankaccounts
values (2, '20060101', 15)
insert #bankaccounts
values (2, '20060102', -6)
insert #bankaccounts
values (2, '20060103', 2)
insert #bankaccounts
values (2, '20060106', 2.75)
insert #bankaccounts
values (2, '20060107', 4)
insert #bankaccounts
values (2, '20060111', 5)
insert #bankaccounts
values (2, '20060116', 9)
insert #bankaccounts
values (3, '20060115', 7)
insert #bankaccounts
values (3, '20060116', 8)
insert #bankaccounts
values (3, '20060122', 9)
insert #bankaccounts
values (3, '20060126', 10)
insert #bankaccounts
values (4, '20060108', 9)
insert #bankaccounts
values (4, '20060112', 10)
insert #bankaccounts
values (5, '20060107', -16)
insert #bankaccounts
values (5, '20060108', 3)
insert #bankaccounts
values (5, '20060109', -12)
insert #bankaccounts
values (5, '20060111', -16)
select accountid
, sum(amttran) [sum]
, min(transdate) [mindate]
, max(transdate) [maxdate]
, ' date over the 20 dollar threshold ' [thresholddate]
from #bankaccounts
group by accountid
having abs(sum(amttran)) > 20
1 55.50 2006-01-01 2006-01-07 2006-01-03
2 31.75 2006-01-01 2006-01-16 2006-01-11
3 34.00 2006-01-15 2006-01-26 2006-01-22
5 -41.00 2006-01-07 2006-01-11 2006-01-09Thom,
this works, but can be improved
select accountid
, [sum]
, [mindate]
, [maxdate]
, (
select min(transdate) from #bankaccounts b
where b.accountid = t.accountid
and ((select sum(amttran) from #bankaccounts b1 where b1.transdate
<= b.transdate
and b.accountid = b1.accountid
)>20
or
(select sum(amttran) from #bankaccounts b1 where b1.transdate <=
b.transdate
and b.accountid = b1.accountid
)<-20)
) as thresholddate
from(
select accountid
, sum(amttran) [sum]
, min(transdate) [mindate]
, max(transdate) [maxdate]
from #bankaccounts
group by accountid ) t
more than 20 dollars (+ or -)
please see the DDL below, a sql statement, and the desired output. Thanks
create table #bankaccounts
(
accountid int not null
, transdate datetime not null
, primary key (accountid, transdate)
, amttran decimal(19,2) not null
)
set nocount on
insert #bankaccounts
values (1, '20060101', 10)
insert #bankaccounts
values (1, '20060102', 5)
insert #bankaccounts
values (1, '20060103', 12)
insert #bankaccounts
values (1, '20060106', 15.50)
insert #bankaccounts
values (1, '20060107', 13)
insert #bankaccounts
values (2, '20060101', 15)
insert #bankaccounts
values (2, '20060102', -6)
insert #bankaccounts
values (2, '20060103', 2)
insert #bankaccounts
values (2, '20060106', 2.75)
insert #bankaccounts
values (2, '20060107', 4)
insert #bankaccounts
values (2, '20060111', 5)
insert #bankaccounts
values (2, '20060116', 9)
insert #bankaccounts
values (3, '20060115', 7)
insert #bankaccounts
values (3, '20060116', 8)
insert #bankaccounts
values (3, '20060122', 9)
insert #bankaccounts
values (3, '20060126', 10)
insert #bankaccounts
values (4, '20060108', 9)
insert #bankaccounts
values (4, '20060112', 10)
insert #bankaccounts
values (5, '20060107', -16)
insert #bankaccounts
values (5, '20060108', 3)
insert #bankaccounts
values (5, '20060109', -12)
insert #bankaccounts
values (5, '20060111', -16)
select accountid
, sum(amttran) [sum]
, min(transdate) [mindate]
, max(transdate) [maxdate]
, ' date over the 20 dollar threshold ' [thresholddate]
from #bankaccounts
group by accountid
having abs(sum(amttran)) > 20
1 55.50 2006-01-01 2006-01-07 2006-01-03
2 31.75 2006-01-01 2006-01-16 2006-01-11
3 34.00 2006-01-15 2006-01-26 2006-01-22
5 -41.00 2006-01-07 2006-01-11 2006-01-09Thom,
this works, but can be improved
select accountid
, [sum]
, [mindate]
, [maxdate]
, (
select min(transdate) from #bankaccounts b
where b.accountid = t.accountid
and ((select sum(amttran) from #bankaccounts b1 where b1.transdate
<= b.transdate
and b.accountid = b1.accountid
)>20
or
(select sum(amttran) from #bankaccounts b1 where b1.transdate <=
b.transdate
and b.accountid = b1.accountid
)<-20)
) as thresholddate
from(
select accountid
, sum(amttran) [sum]
, min(transdate) [mindate]
, max(transdate) [maxdate]
from #bankaccounts
group by accountid ) t
Subscribe to:
Posts (Atom)