Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardF
Yes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_nam e
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF
sql
Showing posts with label single. Show all posts
Showing posts with label single. Show all posts
Friday, March 30, 2012
Query across multiple databases/SQL Servers
Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF
Query across multiple databases/SQL Servers
Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF
Wednesday, March 28, 2012
query
damn touchpads......
I have a query:
SELECT id, name, rank, serial_number from status
this will return many rows...if all values are null for a single row, i need
to be able to assign a unique "id" for that row (PK constraint on another
table) is there a way to do this so that for each row that has NULL values
across all columns. This way I can populate with an incrementing or otherwis
e
unique value (using d1, d2, d3... here)
id name rank serial_number
--
d1 null null null
d2 null null null
34r bill 0-6 4420
d3 null null null
d4 null null null
d5....
TIA!You can try using the following:
NOTE: This recreates the status table (truncate + re-populate)
-- BEGIN SCRIPT
create table status
(id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into status
values (null,null,null,null)
insert into status
values ('34r','bill','0-6','4420')
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
create table #status
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
create table #status2
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into #status
select * from status
insert into #status2
select * from status
update #status
set id = 'd'+convert(varchar(10), s.RowID)
from #status s, #status2 s2
where s.RowID = s2.RowID
and s.id is null
truncate table status
insert into status
SELECT id, name, rank, serial_number from #status
select * from status
drop table #status
drop table #status2
Hope it helps
"JMNUSS" wrote:
> damn touchpads......
> I have a query:
> SELECT id, name, rank, serial_number from status
> this will return many rows...if all values are null for a single row, i ne
ed
> to be able to assign a unique "id" for that row (PK constraint on another
> table) is there a way to do this so that for each row that has NULL values
> across all columns. This way I can populate with an incrementing or otherw
ise
> unique value (using d1, d2, d3... here)
> id name rank serial_number
> --
> d1 null null null
> d2 null null null
> 34r bill 0-6 4420
> d3 null null null
> d4 null null null
> d5....
> TIA!
>
I have a query:
SELECT id, name, rank, serial_number from status
this will return many rows...if all values are null for a single row, i need
to be able to assign a unique "id" for that row (PK constraint on another
table) is there a way to do this so that for each row that has NULL values
across all columns. This way I can populate with an incrementing or otherwis
e
unique value (using d1, d2, d3... here)
id name rank serial_number
--
d1 null null null
d2 null null null
34r bill 0-6 4420
d3 null null null
d4 null null null
d5....
TIA!You can try using the following:
NOTE: This recreates the status table (truncate + re-populate)
-- BEGIN SCRIPT
create table status
(id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into status
values (null,null,null,null)
insert into status
values ('34r','bill','0-6','4420')
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
create table #status
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
create table #status2
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into #status
select * from status
insert into #status2
select * from status
update #status
set id = 'd'+convert(varchar(10), s.RowID)
from #status s, #status2 s2
where s.RowID = s2.RowID
and s.id is null
truncate table status
insert into status
SELECT id, name, rank, serial_number from #status
select * from status
drop table #status
drop table #status2
Hope it helps
"JMNUSS" wrote:
> damn touchpads......
> I have a query:
> SELECT id, name, rank, serial_number from status
> this will return many rows...if all values are null for a single row, i ne
ed
> to be able to assign a unique "id" for that row (PK constraint on another
> table) is there a way to do this so that for each row that has NULL values
> across all columns. This way I can populate with an incrementing or otherw
ise
> unique value (using d1, d2, d3... here)
> id name rank serial_number
> --
> d1 null null null
> d2 null null null
> 34r bill 0-6 4420
> d3 null null null
> d4 null null null
> d5....
> TIA!
>
Monday, March 26, 2012
Query
I'm trying to find a way to create a query to obtain the last 30 hours ran
for every single part in the table and insert this data into new tabel
Here is an example: Assuming that we need to extract the last 30 ran hours
for every single part in Table A and Insert them into Table B
Here is the data from table A
Record ID Part # HrsRan Date
1 ABC 23 8/1/04
2 DBC 15 8/1/04
3 ABC 20 8/2/04
4 ABC 22 8/2/04
5 123 15 8/3/04
6 123 40 8/3/04
7 DBC 30 8/3/04
8 DBC 15 8/4/04
9 ABC 10 8/4/04
10 DBC 20 8/4/04
11 DBC 10 8/5/04
12 123 20 8/5/04
Then the record that the query should extract should be those records for
the last 30 ranhrs then the results should be the following
Table B
Record ID Part HrsRan Date
12 123 20 8/5/04
6 123 10 8/3/04
11 DBC 10 8/5/04
10 DBC 20 8/4/04
9 ABC 10 8/4/04
4 ABC 20 8/2/04
As you could see the system drop the following records ID
1 ,2,3,5,7,8
I'll only need to write ID record in the new table then use this ID against
the history data, this will save disk space or maybe just dump all the
necesary data into the new table and save performance time. If I found a way
to create a Procedure, Query, DTS to create this I'll test the performance on
both cases to see which one bring the data faster.
Thanks: Cesar Sandoval
There are probably better ways to accomplish this but the query below should
get you started.
CREATE TABLE TableA
(
RecordID int NOT NULL,
PartNumber char(3) NOT NULL,
HrsRan int NOT NULL,
RunDate smalldatetime NOT NULL
)
GO
INSERT INTO TableA VALUES(1, 'ABC', 23, '20040801')
INSERT INTO TableA VALUES(2, 'DBC', 15, '20040801')
INSERT INTO TableA VALUES(3, 'ABC', 20, '20040802')
INSERT INTO TableA VALUES(4, 'ABC', 22, '20040802')
INSERT INTO TableA VALUES(5, '123', 15, '20040803')
INSERT INTO TableA VALUES(6, '123', 40, '20040803')
INSERT INTO TableA VALUES(7, 'DBC', 30, '20040803')
INSERT INTO TableA VALUES(8, 'DBC', 15, '20040804')
INSERT INTO TableA VALUES(9, 'ABC', 10, '20040804')
INSERT INTO TableA VALUES(10, 'DBC', 20, '20040804')
INSERT INTO TableA VALUES(11, 'DBC', 10, '20040805')
INSERT INTO TableA VALUES(12, '123', 20, '20040805')
GO
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
CASE WHEN a.HrsRanRunningTotal > 30
THEN 30 - (a.HrsRanRunningTotal - a.HrsRan)
ELSE a.HrsRan END AS HrsRan,
a.HrsRanRunningTotal
FROM
(
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
a.HrsRan,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.RecordId = a.RecordId OR
(b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId > a.RecordId)))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
JOIN
(
SELECT
PartNumber,
MIN(HrsRanRunningTotal) AS HrsRanRunningTotal
FROM
(
SELECT
a.PartNumber,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId >= a.RecordId))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
WHERE HrsRanRunningTotal >= 30
GROUP BY
PartNumber
) AS b ON
b.PartNumber = a.PartNumber AND
b.HrsRanRunningTotal >= a.HrsRanRunningTotal
ORDER BY
a.PartNumber,
a.RunDate DESC
Hope this helps.
Dan Guzman
SQL Server MVP
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:692430D8-D7B6-44EA-B758-1A27101F9A4B@.microsoft.com...
> I'm trying to find a way to create a query to obtain the last 30 hours ran
> for every single part in the table and insert this data into new tabel
> Here is an example: Assuming that we need to extract the last 30 ran hours
> for every single part in Table A and Insert them into Table B
> Here is the data from table A
> Record ID Part # HrsRan Date
> 1 ABC 23 8/1/04
> 2 DBC 15 8/1/04
> 3 ABC 20 8/2/04
> 4 ABC 22 8/2/04
> 5 123 15 8/3/04
> 6 123 40 8/3/04
> 7 DBC 30 8/3/04
> 8 DBC 15 8/4/04
> 9 ABC 10 8/4/04
> 10 DBC 20 8/4/04
> 11 DBC 10 8/5/04
> 12 123 20 8/5/04
> Then the record that the query should extract should be those records for
> the last 30 ranhrs then the results should be the following
> Table B
> Record ID Part HrsRan Date
> 12 123 20 8/5/04
> 6 123 10 8/3/04
> 11 DBC 10 8/5/04
> 10 DBC 20 8/4/04
> 9 ABC 10 8/4/04
> 4 ABC 20 8/2/04
>
> As you could see the system drop the following records ID
> 1 ,2,3,5,7,8
> I'll only need to write ID record in the new table then use this ID
against
> the history data, this will save disk space or maybe just dump all the
> necesary data into the new table and save performance time. If I found a
way
> to create a Procedure, Query, DTS to create this I'll test the performance
on
> both cases to see which one bring the data faster.
> Thanks: Cesar Sandoval
>
sql
for every single part in the table and insert this data into new tabel
Here is an example: Assuming that we need to extract the last 30 ran hours
for every single part in Table A and Insert them into Table B
Here is the data from table A
Record ID Part # HrsRan Date
1 ABC 23 8/1/04
2 DBC 15 8/1/04
3 ABC 20 8/2/04
4 ABC 22 8/2/04
5 123 15 8/3/04
6 123 40 8/3/04
7 DBC 30 8/3/04
8 DBC 15 8/4/04
9 ABC 10 8/4/04
10 DBC 20 8/4/04
11 DBC 10 8/5/04
12 123 20 8/5/04
Then the record that the query should extract should be those records for
the last 30 ranhrs then the results should be the following
Table B
Record ID Part HrsRan Date
12 123 20 8/5/04
6 123 10 8/3/04
11 DBC 10 8/5/04
10 DBC 20 8/4/04
9 ABC 10 8/4/04
4 ABC 20 8/2/04
As you could see the system drop the following records ID
1 ,2,3,5,7,8
I'll only need to write ID record in the new table then use this ID against
the history data, this will save disk space or maybe just dump all the
necesary data into the new table and save performance time. If I found a way
to create a Procedure, Query, DTS to create this I'll test the performance on
both cases to see which one bring the data faster.
Thanks: Cesar Sandoval
There are probably better ways to accomplish this but the query below should
get you started.
CREATE TABLE TableA
(
RecordID int NOT NULL,
PartNumber char(3) NOT NULL,
HrsRan int NOT NULL,
RunDate smalldatetime NOT NULL
)
GO
INSERT INTO TableA VALUES(1, 'ABC', 23, '20040801')
INSERT INTO TableA VALUES(2, 'DBC', 15, '20040801')
INSERT INTO TableA VALUES(3, 'ABC', 20, '20040802')
INSERT INTO TableA VALUES(4, 'ABC', 22, '20040802')
INSERT INTO TableA VALUES(5, '123', 15, '20040803')
INSERT INTO TableA VALUES(6, '123', 40, '20040803')
INSERT INTO TableA VALUES(7, 'DBC', 30, '20040803')
INSERT INTO TableA VALUES(8, 'DBC', 15, '20040804')
INSERT INTO TableA VALUES(9, 'ABC', 10, '20040804')
INSERT INTO TableA VALUES(10, 'DBC', 20, '20040804')
INSERT INTO TableA VALUES(11, 'DBC', 10, '20040805')
INSERT INTO TableA VALUES(12, '123', 20, '20040805')
GO
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
CASE WHEN a.HrsRanRunningTotal > 30
THEN 30 - (a.HrsRanRunningTotal - a.HrsRan)
ELSE a.HrsRan END AS HrsRan,
a.HrsRanRunningTotal
FROM
(
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
a.HrsRan,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.RecordId = a.RecordId OR
(b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId > a.RecordId)))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
JOIN
(
SELECT
PartNumber,
MIN(HrsRanRunningTotal) AS HrsRanRunningTotal
FROM
(
SELECT
a.PartNumber,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId >= a.RecordId))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
WHERE HrsRanRunningTotal >= 30
GROUP BY
PartNumber
) AS b ON
b.PartNumber = a.PartNumber AND
b.HrsRanRunningTotal >= a.HrsRanRunningTotal
ORDER BY
a.PartNumber,
a.RunDate DESC
Hope this helps.
Dan Guzman
SQL Server MVP
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:692430D8-D7B6-44EA-B758-1A27101F9A4B@.microsoft.com...
> I'm trying to find a way to create a query to obtain the last 30 hours ran
> for every single part in the table and insert this data into new tabel
> Here is an example: Assuming that we need to extract the last 30 ran hours
> for every single part in Table A and Insert them into Table B
> Here is the data from table A
> Record ID Part # HrsRan Date
> 1 ABC 23 8/1/04
> 2 DBC 15 8/1/04
> 3 ABC 20 8/2/04
> 4 ABC 22 8/2/04
> 5 123 15 8/3/04
> 6 123 40 8/3/04
> 7 DBC 30 8/3/04
> 8 DBC 15 8/4/04
> 9 ABC 10 8/4/04
> 10 DBC 20 8/4/04
> 11 DBC 10 8/5/04
> 12 123 20 8/5/04
> Then the record that the query should extract should be those records for
> the last 30 ranhrs then the results should be the following
> Table B
> Record ID Part HrsRan Date
> 12 123 20 8/5/04
> 6 123 10 8/3/04
> 11 DBC 10 8/5/04
> 10 DBC 20 8/4/04
> 9 ABC 10 8/4/04
> 4 ABC 20 8/2/04
>
> As you could see the system drop the following records ID
> 1 ,2,3,5,7,8
> I'll only need to write ID record in the new table then use this ID
against
> the history data, this will save disk space or maybe just dump all the
> necesary data into the new table and save performance time. If I found a
way
> to create a Procedure, Query, DTS to create this I'll test the performance
on
> both cases to see which one bring the data faster.
> Thanks: Cesar Sandoval
>
sql
Friday, March 23, 2012
Query
I'm trying to find a way to create a query to obtain the last 30 hours ran
for every single part in the table and insert this data into new tabel
Here is an example: Assuming that we need to extract the last 30 ran hours
for every single part in Table A and Insert them into Table B
Here is the data from table A
Record ID Part # HrsRan Date
1 ABC 23 8/1/04
2 DBC 15 8/1/04
3 ABC 20 8/2/04
4 ABC 22 8/2/04
5 123 15 8/3/04
6 123 40 8/3/04
7 DBC 30 8/3/04
8 DBC 15 8/4/04
9 ABC 10 8/4/04
10 DBC 20 8/4/04
11 DBC 10 8/5/04
12 123 20 8/5/04
Then the record that the query should extract should be those records for
the last 30 ranhrs then the results should be the following
Table B
Record ID Part HrsRan Date
12 123 20 8/5/04
6 123 10 8/3/04
11 DBC 10 8/5/04
10 DBC 20 8/4/04
9 ABC 10 8/4/04
4 ABC 20 8/2/04
As you could see the system drop the following records ID
1 ,2,3,5,7,8
I'll only need to write ID record in the new table then use this ID against
the history data, this will save disk space or maybe just dump all the
necesary data into the new table and save performance time. If I found a way
to create a Procedure, Query, DTS to create this I'll test the performance o
n
both cases to see which one bring the data faster.
Thanks: Cesar SandovalThere are probably better ways to accomplish this but the query below should
get you started.
CREATE TABLE TableA
(
RecordID int NOT NULL,
PartNumber char(3) NOT NULL,
HrsRan int NOT NULL,
RunDate smalldatetime NOT NULL
)
GO
INSERT INTO TableA VALUES(1, 'ABC', 23, '20040801')
INSERT INTO TableA VALUES(2, 'DBC', 15, '20040801')
INSERT INTO TableA VALUES(3, 'ABC', 20, '20040802')
INSERT INTO TableA VALUES(4, 'ABC', 22, '20040802')
INSERT INTO TableA VALUES(5, '123', 15, '20040803')
INSERT INTO TableA VALUES(6, '123', 40, '20040803')
INSERT INTO TableA VALUES(7, 'DBC', 30, '20040803')
INSERT INTO TableA VALUES(8, 'DBC', 15, '20040804')
INSERT INTO TableA VALUES(9, 'ABC', 10, '20040804')
INSERT INTO TableA VALUES(10, 'DBC', 20, '20040804')
INSERT INTO TableA VALUES(11, 'DBC', 10, '20040805')
INSERT INTO TableA VALUES(12, '123', 20, '20040805')
GO
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
CASE WHEN a.HrsRanRunningTotal > 30
THEN 30 - (a.HrsRanRunningTotal - a.HrsRan)
ELSE a.HrsRan END AS HrsRan,
a.HrsRanRunningTotal
FROM
(
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
a.HrsRan,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.RecordId = a.RecordId OR
(b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId > a.RecordId)))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
JOIN
(
SELECT
PartNumber,
MIN(HrsRanRunningTotal) AS HrsRanRunningTotal
FROM
(
SELECT
a.PartNumber,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId >= a.RecordId))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
WHERE HrsRanRunningTotal >= 30
GROUP BY
PartNumber
) AS b ON
b.PartNumber = a.PartNumber AND
b.HrsRanRunningTotal >= a.HrsRanRunningTotal
ORDER BY
a.PartNumber,
a.RunDate DESC
Hope this helps.
Dan Guzman
SQL Server MVP
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:692430D8-D7B6-44EA-B758-1A27101F9A4B@.microsoft.com...
> I'm trying to find a way to create a query to obtain the last 30 hours ran
> for every single part in the table and insert this data into new tabel
> Here is an example: Assuming that we need to extract the last 30 ran hours
> for every single part in Table A and Insert them into Table B
> Here is the data from table A
> Record ID Part # HrsRan Date
> 1 ABC 23 8/1/04
> 2 DBC 15 8/1/04
> 3 ABC 20 8/2/04
> 4 ABC 22 8/2/04
> 5 123 15 8/3/04
> 6 123 40 8/3/04
> 7 DBC 30 8/3/04
> 8 DBC 15 8/4/04
> 9 ABC 10 8/4/04
> 10 DBC 20 8/4/04
> 11 DBC 10 8/5/04
> 12 123 20 8/5/04
> Then the record that the query should extract should be those records for
> the last 30 ranhrs then the results should be the following
> Table B
> Record ID Part HrsRan Date
> 12 123 20 8/5/04
> 6 123 10 8/3/04
> 11 DBC 10 8/5/04
> 10 DBC 20 8/4/04
> 9 ABC 10 8/4/04
> 4 ABC 20 8/2/04
>
> As you could see the system drop the following records ID
> 1 ,2,3,5,7,8
> I'll only need to write ID record in the new table then use this ID
against
> the history data, this will save disk space or maybe just dump all the
> necesary data into the new table and save performance time. If I found a
way
> to create a Procedure, Query, DTS to create this I'll test the performance
on
> both cases to see which one bring the data faster.
> Thanks: Cesar Sandoval
>sql
for every single part in the table and insert this data into new tabel
Here is an example: Assuming that we need to extract the last 30 ran hours
for every single part in Table A and Insert them into Table B
Here is the data from table A
Record ID Part # HrsRan Date
1 ABC 23 8/1/04
2 DBC 15 8/1/04
3 ABC 20 8/2/04
4 ABC 22 8/2/04
5 123 15 8/3/04
6 123 40 8/3/04
7 DBC 30 8/3/04
8 DBC 15 8/4/04
9 ABC 10 8/4/04
10 DBC 20 8/4/04
11 DBC 10 8/5/04
12 123 20 8/5/04
Then the record that the query should extract should be those records for
the last 30 ranhrs then the results should be the following
Table B
Record ID Part HrsRan Date
12 123 20 8/5/04
6 123 10 8/3/04
11 DBC 10 8/5/04
10 DBC 20 8/4/04
9 ABC 10 8/4/04
4 ABC 20 8/2/04
As you could see the system drop the following records ID
1 ,2,3,5,7,8
I'll only need to write ID record in the new table then use this ID against
the history data, this will save disk space or maybe just dump all the
necesary data into the new table and save performance time. If I found a way
to create a Procedure, Query, DTS to create this I'll test the performance o
n
both cases to see which one bring the data faster.
Thanks: Cesar SandovalThere are probably better ways to accomplish this but the query below should
get you started.
CREATE TABLE TableA
(
RecordID int NOT NULL,
PartNumber char(3) NOT NULL,
HrsRan int NOT NULL,
RunDate smalldatetime NOT NULL
)
GO
INSERT INTO TableA VALUES(1, 'ABC', 23, '20040801')
INSERT INTO TableA VALUES(2, 'DBC', 15, '20040801')
INSERT INTO TableA VALUES(3, 'ABC', 20, '20040802')
INSERT INTO TableA VALUES(4, 'ABC', 22, '20040802')
INSERT INTO TableA VALUES(5, '123', 15, '20040803')
INSERT INTO TableA VALUES(6, '123', 40, '20040803')
INSERT INTO TableA VALUES(7, 'DBC', 30, '20040803')
INSERT INTO TableA VALUES(8, 'DBC', 15, '20040804')
INSERT INTO TableA VALUES(9, 'ABC', 10, '20040804')
INSERT INTO TableA VALUES(10, 'DBC', 20, '20040804')
INSERT INTO TableA VALUES(11, 'DBC', 10, '20040805')
INSERT INTO TableA VALUES(12, '123', 20, '20040805')
GO
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
CASE WHEN a.HrsRanRunningTotal > 30
THEN 30 - (a.HrsRanRunningTotal - a.HrsRan)
ELSE a.HrsRan END AS HrsRan,
a.HrsRanRunningTotal
FROM
(
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
a.HrsRan,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.RecordId = a.RecordId OR
(b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId > a.RecordId)))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
JOIN
(
SELECT
PartNumber,
MIN(HrsRanRunningTotal) AS HrsRanRunningTotal
FROM
(
SELECT
a.PartNumber,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId >= a.RecordId))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
WHERE HrsRanRunningTotal >= 30
GROUP BY
PartNumber
) AS b ON
b.PartNumber = a.PartNumber AND
b.HrsRanRunningTotal >= a.HrsRanRunningTotal
ORDER BY
a.PartNumber,
a.RunDate DESC
Hope this helps.
Dan Guzman
SQL Server MVP
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:692430D8-D7B6-44EA-B758-1A27101F9A4B@.microsoft.com...
> I'm trying to find a way to create a query to obtain the last 30 hours ran
> for every single part in the table and insert this data into new tabel
> Here is an example: Assuming that we need to extract the last 30 ran hours
> for every single part in Table A and Insert them into Table B
> Here is the data from table A
> Record ID Part # HrsRan Date
> 1 ABC 23 8/1/04
> 2 DBC 15 8/1/04
> 3 ABC 20 8/2/04
> 4 ABC 22 8/2/04
> 5 123 15 8/3/04
> 6 123 40 8/3/04
> 7 DBC 30 8/3/04
> 8 DBC 15 8/4/04
> 9 ABC 10 8/4/04
> 10 DBC 20 8/4/04
> 11 DBC 10 8/5/04
> 12 123 20 8/5/04
> Then the record that the query should extract should be those records for
> the last 30 ranhrs then the results should be the following
> Table B
> Record ID Part HrsRan Date
> 12 123 20 8/5/04
> 6 123 10 8/3/04
> 11 DBC 10 8/5/04
> 10 DBC 20 8/4/04
> 9 ABC 10 8/4/04
> 4 ABC 20 8/2/04
>
> As you could see the system drop the following records ID
> 1 ,2,3,5,7,8
> I'll only need to write ID record in the new table then use this ID
against
> the history data, this will save disk space or maybe just dump all the
> necesary data into the new table and save performance time. If I found a
way
> to create a Procedure, Query, DTS to create this I'll test the performance
on
> both cases to see which one bring the data faster.
> Thanks: Cesar Sandoval
>sql
Monday, March 12, 2012
Qouted String
Hi all,
I have a string user_name and it is saved in table with single
I have a string user_name and it is saved in table with single
qoutes, i mean if you view the table there are single qoutes on both
sides of user_name like 'amir', it is saved with single qoutes. The
problem is how can i write sql query with = comparison, i'm
using
select * from sasuser.Followup where user_name= ' 'amir ' ';.
I need to use single qoutes for the query, coz variable is string but
how can i use single qoutes which are with the name itself.
Thanx.Khan (amir@.programmer.net) writes:
> I have a string user_name and it is saved in table with single
> qoutes, i mean if you view the table there are single qoutes on both
> sides of user_name like 'amir', it is saved with single qoutes. The
> problem is how can i write sql query with = comparison, i'm
> using
> select * from sasuser.Followup where user_name= ' 'amir ' ';.
> I need to use single qoutes for the query, coz variable is string but
> how can i use single qoutes which are with the name itself.
To include the string delimiter in a string literal you need to double
it:
SELECT * FROM sasuser.Followup WHERE user_name = '''amit''';
Note that when you work from client code, you should use parameterised
SQL commands, which makes this a non-issue..
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanx erland,, its fine now.
Friday, March 9, 2012
Q270636
I have used Delphi 6 as front end and sql server 2000 as backend.
In delphi i have used dbgrid to for data entry.In dbgrid, in a single column entry is allowed.The requirement is to show the total of the values entered in this column.For this purpose I have written after post and before edit events of dataset.
But when two users try to make entry in this grid simaltaneously from different machines 'Row can not be located for updating. Since it was last read' error occurs.it's because the methodology you selected is not designed for multi-user environment.
In delphi i have used dbgrid to for data entry.In dbgrid, in a single column entry is allowed.The requirement is to show the total of the values entered in this column.For this purpose I have written after post and before edit events of dataset.
But when two users try to make entry in this grid simaltaneously from different machines 'Row can not be located for updating. Since it was last read' error occurs.it's because the methodology you selected is not designed for multi-user environment.
Saturday, February 25, 2012
Q: username authenticated
Hello,
In SQL, suser_sname() gives me connection user name (I am using a single
user to create all connections), I authenticate users through a login form
aginst a SQL login table. Is there any way I can get a username authenticated
in an SQL query?
Thanks,Jim,
SELECT USER_NAME() will return the database user name. Is this what you are
asking for?
HTH
Jerry
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:04775C04-27FA-42CD-A766-BFF215EED008@.microsoft.com...
> Hello,
> In SQL, suser_sname() gives me connection user name (I am using a single
> user to create all connections), I authenticate users through a login form
> aginst a SQL login table. Is there any way I can get a username
> authenticated
> in an SQL query?
> Thanks,
>|||Hi Jerry,
Thanks for reply. That returns dbo.
I need the username that I used to login in my ASP.Net application, not dbo
or not connection user. It is the used authenticated through a form
authentication. Is there any way I can get the user name in my query for a
View.
"Jerry Spivey" wrote:
> Jim,
> SELECT USER_NAME() will return the database user name. Is this what you are
> asking for?
> HTH
> Jerry
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:04775C04-27FA-42CD-A766-BFF215EED008@.microsoft.com...
> > Hello,
> >
> > In SQL, suser_sname() gives me connection user name (I am using a single
> > user to create all connections), I authenticate users through a login form
> > aginst a SQL login table. Is there any way I can get a username
> > authenticated
> > in an SQL query?
> >
> > Thanks,
> >
>
>|||Hmmm...unless the value is written to a table somewhere as part of your
application, I'm not aware of a way to determine that via T-SQL.
HTH
Jerry
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8E72154A-20ED-4E6B-B03D-4B6819CD4FEB@.microsoft.com...
> Hi Jerry,
> Thanks for reply. That returns dbo.
> I need the username that I used to login in my ASP.Net application, not
> dbo
> or not connection user. It is the used authenticated through a form
> authentication. Is there any way I can get the user name in my query for a
> View.
> "Jerry Spivey" wrote:
>> Jim,
>> SELECT USER_NAME() will return the database user name. Is this what you
>> are
>> asking for?
>> HTH
>> Jerry
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:04775C04-27FA-42CD-A766-BFF215EED008@.microsoft.com...
>> > Hello,
>> >
>> > In SQL, suser_sname() gives me connection user name (I am using a
>> > single
>> > user to create all connections), I authenticate users through a login
>> > form
>> > aginst a SQL login table. Is there any way I can get a username
>> > authenticated
>> > in an SQL query?
>> >
>> > Thanks,
>> >
>>
In SQL, suser_sname() gives me connection user name (I am using a single
user to create all connections), I authenticate users through a login form
aginst a SQL login table. Is there any way I can get a username authenticated
in an SQL query?
Thanks,Jim,
SELECT USER_NAME() will return the database user name. Is this what you are
asking for?
HTH
Jerry
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:04775C04-27FA-42CD-A766-BFF215EED008@.microsoft.com...
> Hello,
> In SQL, suser_sname() gives me connection user name (I am using a single
> user to create all connections), I authenticate users through a login form
> aginst a SQL login table. Is there any way I can get a username
> authenticated
> in an SQL query?
> Thanks,
>|||Hi Jerry,
Thanks for reply. That returns dbo.
I need the username that I used to login in my ASP.Net application, not dbo
or not connection user. It is the used authenticated through a form
authentication. Is there any way I can get the user name in my query for a
View.
"Jerry Spivey" wrote:
> Jim,
> SELECT USER_NAME() will return the database user name. Is this what you are
> asking for?
> HTH
> Jerry
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:04775C04-27FA-42CD-A766-BFF215EED008@.microsoft.com...
> > Hello,
> >
> > In SQL, suser_sname() gives me connection user name (I am using a single
> > user to create all connections), I authenticate users through a login form
> > aginst a SQL login table. Is there any way I can get a username
> > authenticated
> > in an SQL query?
> >
> > Thanks,
> >
>
>|||Hmmm...unless the value is written to a table somewhere as part of your
application, I'm not aware of a way to determine that via T-SQL.
HTH
Jerry
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8E72154A-20ED-4E6B-B03D-4B6819CD4FEB@.microsoft.com...
> Hi Jerry,
> Thanks for reply. That returns dbo.
> I need the username that I used to login in my ASP.Net application, not
> dbo
> or not connection user. It is the used authenticated through a form
> authentication. Is there any way I can get the user name in my query for a
> View.
> "Jerry Spivey" wrote:
>> Jim,
>> SELECT USER_NAME() will return the database user name. Is this what you
>> are
>> asking for?
>> HTH
>> Jerry
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:04775C04-27FA-42CD-A766-BFF215EED008@.microsoft.com...
>> > Hello,
>> >
>> > In SQL, suser_sname() gives me connection user name (I am using a
>> > single
>> > user to create all connections), I authenticate users through a login
>> > form
>> > aginst a SQL login table. Is there any way I can get a username
>> > authenticated
>> > in an SQL query?
>> >
>> > Thanks,
>> >
>>
Subscribe to:
Posts (Atom)