Query Help

Hello all, question on a query
How do I turn this into an update query?
SELECT MTE.dbo.GL10110.YEAR1, MTE.dbo.GL10110.PERIODID,
tblEdit.Acct, SUM(MTE.dbo.GL10110.DEBITAMT) - SUM(MTE.dbo.GL10110.CRDTAMNT)
AS Net
FROM tblEdit INNER JOIN
MTE.dbo.GL10110 ON tblEdit.Period =
MTE.dbo.GL10110.PERIODID AND tblEdit.Yr = MTE.dbo.GL10110.YEAR1 INNER JOIN
MTE.dbo.GL00100 ON MTE.dbo.GL10110.ACTINDX =
MTE.dbo.GL00100.ACTINDX AND tblEdit.Acct = MTE.dbo.GL00100.ACTNUMBR_1
GROUP BY MTE.dbo.GL10110.YEAR1, MTE.dbo.GL10110.PERIODID, tblEdit.Acct
ORDER BY tblEdit.Acct
I want it to update the tblEdit table. Just the DeptNet Column. just
as to what appliesHello, Brian
Because you provided such a brief description of the problem, I am only
guessing that you want one of the following (equivalent) queries:
UPDATE tblEdit SET DeptNet=(
SELECT SUM(A.DEBITAMT) - SUM(A.CRDTAMNT)
FROM MTE.dbo.GL10110 A
INNER JOIN MTE.dbo.GL00100 B
ON A.ACTINDX = B.ACTINDX
WHERE tblEdit.Period = A.PERIODID
AND tblEdit.Yr = A.YEAR1
AND tblEdit.Acct = B.ACTNUMBR_1
)
WHERE EXISTS (
SELECT * FROM MTE.dbo.GL10110 A
INNER JOIN MTE.dbo.GL00100 B
ON A.ACTINDX = B.ACTINDX
WHERE tblEdit.Period = A.PERIODID
AND tblEdit.Yr = A.YEAR1
AND tblEdit.Acct = B.ACTNUMBR_1
)
UPDATE tblEdit SET DeptNet=Net
FROM tblEdit INNER JOIN (
SELECT A.YEAR1, A.PERIODID, B.ACTNUMBR_1,
SUM(A.DEBITAMT) - SUM(A.CRDTAMNT) AS Net
FROM MTE.dbo.GL10110 A
INNER JOIN MTE.dbo.GL00100 B
ON A.ACTINDX = B.ACTINDX
GROUP BY A.YEAR1, A.PERIODID, B.ACTNUMBR_1
) x
ON tblEdit.Period = X.PERIODID
AND tblEdit.Yr = X.YEAR1
AND tblEdit.Acct = X.ACTNUMBR_1
The queries are untested, because you did not provide DDL, sample data
and expected results. See: http://www.aspfaq.com/etiquette.asp?id=5006
I assume that (Period, Yr, Acct) is a unique key in tblEdit.
The first query uses ANSI syntax; the second query uses T-SQL syntax.
Razvan

Query help

I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanksrick,
try this:
update ts
set tsfbr1=(select t.tsfbr1 from ts t where t.[datetime]=(select
max(t2.[datetime]) from ts t2 where t2.[datetime]<ts.[datetime] and tsfbr1
is not null))
where tsfbr1 is null and tsfbr1on<>0
and please, don't use reserved words or typenames for column names :)
dean
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
>I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try
select t1.[datetime], "tsfbr1" =
CASE
WHEN t1.tsfbr1 IS NULL AND t1.tsfbr1on = 0 THEN NULL
WHEN t1.tsfbr1 IS NULL THEN (SELECT TOP 1 t2.tsfbr1 FROM TS t2
WHERE (t2.[datetime] < t1.[datetime] AND t2.tsfbr1 IS NOT NULL) ORDER
BY t2.[datetime] DESC)
ELSE t1.tsfbr1
END,
t1.tsfbr1on
FROM TS t1
This will produce the output that you want through a SELECT.
I'll try producing an UPDATE statement that accomplishes the same thing and
post back.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try:
update t1
set
TSFBR1 = t2.TSFBR1
from
TS t1
join
TS t2 on t2.[Datetime] =
(
select
max (t3.[Datetime])
from
TS t3
where
t3.Datetime < t1.Datetime
and t3.TSFBR1 is not null
)
where
t1.TSFBR1 is null
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanks|||Thanks Guys. I appreciate it.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>

Query help

CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your helpYep:
select
s.c1
, x.a
from
stuff
cross join
(
select 1 union all
select 2 union all
select 3
) as x (a)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jack" <jack@.jack.net> wrote in message
news:9uaje.13324$4d6.11003@.trndny04...
CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your help|||> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare|||Well,
I did write one or two that involved a cross join. Here's one that
un-pivots a table:
create table Budgets
(
Contract int not null
, Nominal int not null
, Budget_01 int null
, Budget_02 int null
, Budget_03 int null
, primary key (Contract, Nominal)
)
go
insert Budgets values (1, 123, 1000, 1000, 2000)
insert Budgets values (1, 234, 500, 500, 1000)
insert Budgets values (2, 456, 3000, 4500, 3000)
insert Budgets values (2, 567, 800, 800, 800)
insert Budgets values (3, 789, 500, 500, 500)
insert Budgets values (3, 987, 5000, 500, NULL)
go
select
*
from
(
select
b.Contract
, b.Nominal
, x.Period
, case x.Period
when 1 then b.Budget_01
when 2 then b.Budget_02
when 3 then b.Budget_03
end as Budget
from
Budgets as b
cross join
(
select 1 as Period
union all
select 2
union all
select 3
) as x
) as y
where
Budget is not null
order by
Contract
, Nominal
, Period
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23l4YUHOXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare

Query Frustrations

Hi i got this query

SELECT COUNT(dbo.Table1.activity) AS TOTAL, dbo.Table2.name
FROM dbo.Table1 LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.activity = dbo.Table2.type
WHERE (dbo.Table1.activity = 2)
GROUP BY dbo.Table2.name

#-------------------------
and the result is this.....

name TOTAL
Not Sold 12179
Hangup 12179
Doesn't Want to Give ACH 12179
Doesn't Want to Give CC 12179
#-------------------------

what chages should i make in my query to have a result like this....

name TOTAL
Not Sold 13
Hangup 300
Doesn't Want to Give ACH 25
Doesn't Want to Give CC 30

here's my table definitions
Table1
---
activity
2
3
2
3
5

Table2
-------
type | name
2 | Not Sold
3 | Blah
2 | Hangup
2 | Doesn't Want to Give ACH
5 | Do Not Call
2 | Doesn't Want to Give CCType needs to be unique in Table2.sql

Query database on linked server where database has name like [ABC.DEF]

A database exists on a linked server that I would like to query
When I run the following command
SELECT * from [INSTANCE_NAME].[ABC.DEF].dbo.sysfiles
-- Note the name of database has an "." in it (I cannot change this)
I get the error
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object '"ABC.DEF"."dbo"."sysfiles"'. The OLE DB
provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to
process object, since the object has no columnsProviderName='SQLOLEDB',
Query="ABC.DEF"."dbo"."sysfiles"'].
This query works on other databases on this instance and other
instances.
Does anybody have any ideas?Does the SQL Server has multiple instances in the same box? I have noticed
that Link Server is unable to recognize\access the second instance when
there are multiple instances..
"eahind@.yahoo.co.uk" wrote:
> A database exists on a linked server that I would like to query
> When I run the following command
> SELECT * from [INSTANCE_NAME].[ABC.DEF].dbo.sysfiles
> -- Note the name of database has an "." in it (I cannot change this)
> I get the error
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object '"ABC.DEF"."dbo"."sysfiles"'. The OLE DB
> provider 'SQLOLEDB' indicates that the object has no columns.
> OLE DB error trace [Non-interface error: OLE DB provider unable to
> process object, since the object has no columnsProviderName='SQLOLEDB',
> Query="ABC.DEF"."dbo"."sysfiles"'].
> This query works on other databases on this instance and other
> instances.
> Does anybody have any ideas?
>|||This is the only instance on the host. I can also query other databases
on this instance - they do not have "." in their name.