Need to alter identity property

I need to alter all our identity columns to be not for replication (currently they do not have this condition). I checked how Microsoft is performing this task by recording a script while doing this manually in SSMS. They simply create another table with identity column not for replication, pour data into new table, drop the old one and sp_rename for new one. This is not the case for us - some our tables have billions of records so we can't afford keeping these tables off-line for so long.

Are there any ways to alter it ? Any wok arounds, except directly updating sys tables which is not recommended ?

Thanks

There is no TSQL statement to modify identity column properties or add not for replication to existing column. There might be a replication system SP that will do this for you. So I am moving this thread over to the SQL Server Replication forum.|||

I believe you can do it using T-SQL: You probably need sp4 for this. I know it works in SQL 2005. I was told it was added in SP4. Check it out though.

alter table dbo.yourTable

alter column [yourIDColumn] add NOT FOR REPLICATION

|||

Thanks a lot Dinakar, that's very helpful.

Is there similar scripts for check and foreign keys constraints ?

Need the Guru HELP.

-- START of DB Objects CREATE
scripts ----
--
CREATE TABLE [dbo].[Customers] (
[AG_ID] [int] IDENTITY (1, 1) NOT NULL ,
[AG_TYPE] [tinyint] NULL ,
[AG_STATE] [tinyint] NULL ,
[AG_CODE] [smallint] NULL ,
[AG_REG_NO] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[AG_REG_NAME] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[AG_REG_DATE] [datetime] NULL ,
[AG_PRINT_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AG_SEARCH_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AG_CR_DATE] [datetime] NULL ,
[AG_MD_DATE] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DealPRICE] (
[DP_ID] [int] IDENTITY (1, 1) NOT NULL ,
[AG_ID] [int] NULL ,
[ART_ID] [int] NULL ,
[DP_DATE] [datetime] NULL ,
[DEAL_PRICE] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Items] (
[ART_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ART_TYPE] [tinyint] NULL ,
[ART_STATE] [tinyint] NULL ,
[ART_FOLDER_ID] [int] NULL ,
[ART_MSK_ID] [int] NULL ,
[ART_DIN_ID] [int] NULL ,
[ART_LEVEL] [tinyint] NULL ,
[ART_INDEX] [smallint] NULL ,
[ART_NO] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[ART_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[ART_V1] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V2] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V3] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V4] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V5] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_CR_DATE] [datetime] NULL ,
[ART_MD_DATE] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemsDIN] (
[DIN_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DIN_TYPE] [tinyint] NULL ,
[DIN_INDEX] [tinyint] NULL ,
[DIN_GROUP] [int] NULL ,
[DIN_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[DIN_ALTER] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_TEXT] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_TEXT_STR] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_PRICE_TYPE] [tinyint] NULL ,
[DIN_PRICE_UP] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemsMASK] (
[MSK_ID] [int] IDENTITY (1, 1) NOT NULL ,
[MSK_TYPE] [tinyint] NULL ,
[MSK_INDEX] [smallint] NULL ,
[MSK_MAIN] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[MSK_DESCRIPTION] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[MSK_MASK] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[MSK_PART1] [int] NULL ,
[MSK_PART2] [int] NULL ,
[MSK_PART3] [int] NULL ,
[MSK_PART4] [int] NULL ,
[MSK_PART5] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Stores] (
[SKD_ID] [int] IDENTITY (1, 1) NOT NULL ,
[SKD_TYPE] [tinyint] NULL ,
[SKD_STATE] [tinyint] NULL ,
[SKD_ART_ID] [int] NULL ,
[SKD_UPDATED] [bit] NULL ,
[SKD_NOW_QUANT] [money] NULL ,
[SKD_NOW_REZRV] [money] NULL ,
[SKD_NOW_PREP] [money] NULL ,
[SKD_NOW_UNREG] [money] NULL ,
[SKD_NOW_MOD] [money] NULL ,
[SKD_NOW_NED] [money] NULL ,
[SKD_LIMIT_MIN] [money] NULL ,
[SKD_LIMIT_MAX] [money] NULL ,
[SKD_PRICE] [money] NULL ,
[SKD_LAST_SALE] [datetime] NULL ,
[SKD_CHG_DATE] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[AG_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DealPRICE] WITH NOCHECK ADD
CONSTRAINT [PK_DealPRICE] PRIMARY KEY CLUSTERED
(
[DP_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Items] WITH NOCHECK ADD
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[ART_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemsDIN] WITH NOCHECK ADD
CONSTRAINT [PK_ItemsDIN] PRIMARY KEY CLUSTERED
(
[DIN_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemsMASK] WITH NOCHECK ADD
CONSTRAINT [PK_ItemsMASK] PRIMARY KEY CLUSTERED
(
[MSK_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Stores] WITH NOCHECK ADD
CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED
(
[SKD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DealPRICE] ADD
CONSTRAINT [FK_DealPRICE_Customers] FOREIGN KEY
(
[AG_ID]
) REFERENCES [dbo].[Customers] (
[AG_ID]
),
CONSTRAINT [FK_DealPRICE_Items] FOREIGN KEY
(
[ART_ID]
) REFERENCES [dbo].[Items] (
[ART_ID]
)
GO
ALTER TABLE [dbo].[Items] ADD
CONSTRAINT [FK_Items_ItemsDIN] FOREIGN KEY
(
[ART_DIN_ID]
) REFERENCES [dbo].[ItemsDIN] (
[DIN_ID]
),
CONSTRAINT [FK_Items_ItemsMASK] FOREIGN KEY
(
[ART_MSK_ID]
) REFERENCES [dbo].[ItemsMASK] (
[MSK_ID]
)
GO
ALTER TABLE [dbo].[Stores] ADD
CONSTRAINT [FK_Stores_Items] FOREIGN KEY
(
[SKD_ART_ID]
) REFERENCES [dbo].[Items] (
[ART_ID]
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.qBaseQUERY
AS
SELECT dbo.Items.*, dbo.Stores.*, dbo.ItemsDIN.DIN_NAME AS DIN_NAME,
dbo.ItemsMASK.MSK_PART1 AS MSK_PART1,
dbo.ItemsMASK.MSK_PART2 AS MSK_PART2,
dbo.ItemsMASK.MSK_PART3 AS MSK_PART3, dbo.ItemsMASK.MSK_PART4 AS MSK_PART4,
dbo.ItemsMASK.MSK_PART5 AS MSK_PART5
FROM dbo.Items LEFT OUTER JOIN
dbo.ItemsDIN ON dbo.Items.ART_DIN_ID =
dbo.ItemsDIN.DIN_ID LEFT OUTER JOIN
dbo.ItemsMASK ON dbo.Items.ART_MSK_ID =
dbo.ItemsMASK.MSK_ID LEFT OUTER JOIN
dbo.Stores ON dbo.Items.ART_ID = dbo.Stores.SKD_ART_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.qExpectedRESULT
AS
SELECT ART_ID, ART_NAME, SKD_NOW_QUANT, SKD_PRICE, 'from DealPRICE
table' AS DEAL_PRICE
FROM dbo.qBaseQUERY
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- END of DB Objects CREATE
scripts ----
--
-- Fill
Tables ---
---
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerA','CustomerA','Customer
A')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerB','CustomerB','Customer
B')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerC','CustomerC','Customer
C')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerD','CustomerD','Customer
D')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerE','CustomerE','Customer
E')
INSERT INTO Items(ART_NAME) VALUES('ItemA')
INSERT INTO Items(ART_NAME) VALUES('ItemB')
INSERT INTO Items(ART_NAME) VALUES('ItemC')
INSERT INTO Items(ART_NAME) VALUES('ItemD')
INSERT INTO Items(ART_NAME) VALUES('ItemE')
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(1,453,10.95)
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(3,675,15.95)
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(5,134,20.95)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,1,GETDATE(),10.55)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,2,GETDATE(),13)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,3,GETDATE(),13.5)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(2,3,GETDATE(),14.3)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(3,4,GETDATE(),15)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(4,5,GETDATE(),18.9)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(5,5,GETDATE(),19.1)
-- END of Fill
Tables ---
---
Query qExpectedRESULT for Customers.AG_ID=1 must return the next list:
1 ItemA 453 10,95 10,55
2 ItemB NULL NULL 13
3 ItemC 675 15,95 13,5
4 ItemD NULL NULL NULL
5 ItemE 134 20,95 NULL
for Customers.AG_ID=2:
1 ItemA 453 10,95 NULL
2 ItemB NULL NULL NULL
3 ItemC 675 15,95 14.3
4 ItemD NULL NULL NULL
5 ItemE 134 20,95 NULL
In a real DB like:
Customers - 30 000 rows
Items - 25 000 rows
Stores - 15 000 rows
DealPRICE - 1 000 rows
im not select all 25000 rows:
SELECT qBaseQUERY.* -- base query
FROM qBaseQUERY -- about 25 000 rows
next text added on clients terminals depended on their needs, like:
WHERE qBaseQUERY.ART_MSK_ID=13 -- return 10...30 rows
AND ((((qBaseQUERY.MSK_PART1) = 1))
AND (((qBaseQUERY.ART_V1) = '100')))
AND ((((qBaseQUERY.MSK_PART2) = 3))
AND (((qBaseQUERY.ART_V2) = '050')))
AND ((((qBaseQUERY.MSK_PART3) = 12))
AND (((qBaseQUERY.ART_V3) = '058')))
AND ((((qBaseQUERY.MSK_PART4) = 11))
AND (((qBaseQUERY.ART_V4) = '001')))
AND ((((qBaseQUERY.MSK_PART5) = 36))
AND (((qBaseQUERY.ART_V5) = '105')))
AND (((qBaseQUERY.SKD_NOW_QUANT)>0) OR ((qBaseQUERY.SKD_NOW_UNREG)>0) )
ORDER BY qBaseQUERY.ART_FOLDER_ID, qBaseQUERY.ART_LEVEL,
qBaseQUERY.ART_INDEX
--
What do you think about it ?I won't pretend to understand what you are trying to do exactly, but this
query returns the values as you wanted:
select items.art_name, SKD_NOW_QUANT, SKD_Price, dealPrice.deal_price
from items
left outer join stores
on items.art_id = stores.skd_art_id
left outer join dealPrice
join customers --this might not be right, but something like this should be
on customers.ag_Id = dealPrice.ag_id
and customers.ag_id = 2 --<--this is the variable
on items.art_id = dealPrice.art_id
Your naming conventions made it pretty difficult to follow, but because you
included the scripts I was able to build a database, use the diagrams and
see kind of what was going on. Thanks for doing that!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Kachmaryk Yuriy" <kachya@.ua.fm> wrote in message
news:%23R1ZcTtHGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
> -- START of DB Objects CREATE
> scripts ----
--
> --
> CREATE TABLE [dbo].[Customers] (
> [AG_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [AG_TYPE] [tinyint] NULL ,
> [AG_STATE] [tinyint] NULL ,
> [AG_CODE] [smallint] NULL ,
> [AG_REG_NO] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
> [AG_REG_NAME] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [AG_REG_DATE] [datetime] NULL ,
> [AG_PRINT_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
> [AG_SEARCH_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
> [AG_CR_DATE] [datetime] NULL ,
> [AG_MD_DATE] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[DealPRICE] (
> [DP_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [AG_ID] [int] NULL ,
> [ART_ID] [int] NULL ,
> [DP_DATE] [datetime] NULL ,
> [DEAL_PRICE] [money] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Items] (
> [ART_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ART_TYPE] [tinyint] NULL ,
> [ART_STATE] [tinyint] NULL ,
> [ART_FOLDER_ID] [int] NULL ,
> [ART_MSK_ID] [int] NULL ,
> [ART_DIN_ID] [int] NULL ,
> [ART_LEVEL] [tinyint] NULL ,
> [ART_INDEX] [smallint] NULL ,
> [ART_NO] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
> [ART_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
> [ART_V1] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
> [ART_V2] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
> [ART_V3] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
> [ART_V4] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
> [ART_V5] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
> [ART_CR_DATE] [datetime] NULL ,
> [ART_MD_DATE] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemsDIN] (
> [DIN_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [DIN_TYPE] [tinyint] NULL ,
> [DIN_INDEX] [tinyint] NULL ,
> [DIN_GROUP] [int] NULL ,
> [DIN_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
> [DIN_ALTER] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [DIN_TEXT] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [DIN_TEXT_STR] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [DIN_PRICE_TYPE] [tinyint] NULL ,
> [DIN_PRICE_UP] [money] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemsMASK] (
> [MSK_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [MSK_TYPE] [tinyint] NULL ,
> [MSK_INDEX] [smallint] NULL ,
> [MSK_MAIN] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
> [MSK_DESCRIPTION] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
> [MSK_MASK] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [MSK_PART1] [int] NULL ,
> [MSK_PART2] [int] NULL ,
> [MSK_PART3] [int] NULL ,
> [MSK_PART4] [int] NULL ,
> [MSK_PART5] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Stores] (
> [SKD_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [SKD_TYPE] [tinyint] NULL ,
> [SKD_STATE] [tinyint] NULL ,
> [SKD_ART_ID] [int] NULL ,
> [SKD_UPDATED] [bit] NULL ,
> [SKD_NOW_QUANT] [money] NULL ,
> [SKD_NOW_REZRV] [money] NULL ,
> [SKD_NOW_PREP] [money] NULL ,
> [SKD_NOW_UNREG] [money] NULL ,
> [SKD_NOW_MOD] [money] NULL ,
> [SKD_NOW_NED] [money] NULL ,
> [SKD_LIMIT_MIN] [money] NULL ,
> [SKD_LIMIT_MAX] [money] NULL ,
> [SKD_PRICE] [money] NULL ,
> [SKD_LAST_SALE] [datetime] NULL ,
> [SKD_CHG_DATE] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD
> CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
> (
> [AG_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[DealPRICE] WITH NOCHECK ADD
> CONSTRAINT [PK_DealPRICE] PRIMARY KEY CLUSTERED
> (
> [DP_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Items] WITH NOCHECK ADD
> CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
> (
> [ART_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemsDIN] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemsDIN] PRIMARY KEY CLUSTERED
> (
> [DIN_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemsMASK] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemsMASK] PRIMARY KEY CLUSTERED
> (
> [MSK_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Stores] WITH NOCHECK ADD
> CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED
> (
> [SKD_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[DealPRICE] ADD
> CONSTRAINT [FK_DealPRICE_Customers] FOREIGN KEY
> (
> [AG_ID]
> ) REFERENCES [dbo].[Customers] (
> [AG_ID]
> ),
> CONSTRAINT [FK_DealPRICE_Items] FOREIGN KEY
> (
> [ART_ID]
> ) REFERENCES [dbo].[Items] (
> [ART_ID]
> )
> GO
> ALTER TABLE [dbo].[Items] ADD
> CONSTRAINT [FK_Items_ItemsDIN] FOREIGN KEY
> (
> [ART_DIN_ID]
> ) REFERENCES [dbo].[ItemsDIN] (
> [DIN_ID]
> ),
> CONSTRAINT [FK_Items_ItemsMASK] FOREIGN KEY
> (
> [ART_MSK_ID]
> ) REFERENCES [dbo].[ItemsMASK] (
> [MSK_ID]
> )
> GO
> ALTER TABLE [dbo].[Stores] ADD
> CONSTRAINT [FK_Stores_Items] FOREIGN KEY
> (
> [SKD_ART_ID]
> ) REFERENCES [dbo].[Items] (
> [ART_ID]
> )
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.qBaseQUERY
> AS
> SELECT dbo.Items.*, dbo.Stores.*, dbo.ItemsDIN.DIN_NAME AS DIN_NAME,
> dbo.ItemsMASK.MSK_PART1 AS MSK_PART1,
> dbo.ItemsMASK.MSK_PART2 AS MSK_PART2,
> dbo.ItemsMASK.MSK_PART3 AS MSK_PART3, dbo.ItemsMASK.MSK_PART4 AS
> MSK_PART4,
> dbo.ItemsMASK.MSK_PART5 AS MSK_PART5
> FROM dbo.Items LEFT OUTER JOIN
> dbo.ItemsDIN ON dbo.Items.ART_DIN_ID =
> dbo.ItemsDIN.DIN_ID LEFT OUTER JOIN
> dbo.ItemsMASK ON dbo.Items.ART_MSK_ID =
> dbo.ItemsMASK.MSK_ID LEFT OUTER JOIN
> dbo.Stores ON dbo.Items.ART_ID =
> dbo.Stores.SKD_ART_ID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.qExpectedRESULT
> AS
> SELECT ART_ID, ART_NAME, SKD_NOW_QUANT, SKD_PRICE, 'from DealPRICE
> table' AS DEAL_PRICE
> FROM dbo.qBaseQUERY
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> -- END of DB Objects CREATE
> scripts ----
--
> --
> -- Fill
> Tables ---
--
> ---
> INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
> VALUES('CustomerA','CustomerA','Customer
A')
> INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
> VALUES('CustomerB','CustomerB','Customer
B')
> INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
> VALUES('CustomerC','CustomerC','Customer
C')
> INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
> VALUES('CustomerD','CustomerD','Customer
D')
> INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
> VALUES('CustomerE','CustomerE','Customer
E')
> INSERT INTO Items(ART_NAME) VALUES('ItemA')
> INSERT INTO Items(ART_NAME) VALUES('ItemB')
> INSERT INTO Items(ART_NAME) VALUES('ItemC')
> INSERT INTO Items(ART_NAME) VALUES('ItemD')
> INSERT INTO Items(ART_NAME) VALUES('ItemE')
> INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(1,453,10.95)
> INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(3,675,15.95)
> INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(5,134,20.95)
> INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
> VALUES(1,1,GETDATE(),10.55)
> INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
> VALUES(1,2,GETDATE(),13)
> INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
> VALUES(1,3,GETDATE(),13.5)
> INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
> VALUES(2,3,GETDATE(),14.3)
> INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
> VALUES(3,4,GETDATE(),15)
> INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
> VALUES(4,5,GETDATE(),18.9)
> INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
> VALUES(5,5,GETDATE(),19.1)
> -- END of Fill
> Tables ---
--
> ---
> Query qExpectedRESULT for Customers.AG_ID=1 must return the next list:
> 1 ItemA 453 10,95 10,55
> 2 ItemB NULL NULL 13
> 3 ItemC 675 15,95 13,5
> 4 ItemD NULL NULL NULL
> 5 ItemE 134 20,95 NULL
> for Customers.AG_ID=2:
> 1 ItemA 453 10,95 NULL
> 2 ItemB NULL NULL NULL
> 3 ItemC 675 15,95 14.3
> 4 ItemD NULL NULL NULL
> 5 ItemE 134 20,95 NULL
> In a real DB like:
> Customers - 30 000 rows
> Items - 25 000 rows
> Stores - 15 000 rows
> DealPRICE - 1 000 rows
> im not select all 25000 rows:
> SELECT qBaseQUERY.* -- base query
> FROM qBaseQUERY -- about 25 000 rows
> next text added on clients terminals depended on their needs, like:
> WHERE qBaseQUERY.ART_MSK_ID=13 -- return 10...30 rows
> AND ((((qBaseQUERY.MSK_PART1) = 1))
> AND (((qBaseQUERY.ART_V1) = '100')))
> AND ((((qBaseQUERY.MSK_PART2) = 3))
> AND (((qBaseQUERY.ART_V2) = '050')))
> AND ((((qBaseQUERY.MSK_PART3) = 12))
> AND (((qBaseQUERY.ART_V3) = '058')))
> AND ((((qBaseQUERY.MSK_PART4) = 11))
> AND (((qBaseQUERY.ART_V4) = '001')))
> AND ((((qBaseQUERY.MSK_PART5) = 36))
> AND (((qBaseQUERY.ART_V5) = '105')))
> AND (((qBaseQUERY.SKD_NOW_QUANT)>0) OR ((qBaseQUERY.SKD_NOW_UNREG)>0) )
> ORDER BY qBaseQUERY.ART_FOLDER_ID, qBaseQUERY.ART_LEVEL,
> qBaseQUERY.ART_INDEX
> --
> What do you think about it ?
>

Need Query Help

I'm need a query that takes the number from the identity column, then uses
that for the next routine in a range...something like;
Select IdentityNumber
From TableName
Where LastName = 'Somebody'
(Then it takes that IdentityNumber, say row 100, an uses it to grab the rows
on both sides of 100, say 20 rows in each direction. So the second 1/2
of the operation would look like this.
Select FirstName, Lastname, Address
From TableName
Where identitynumbe(100) minus 20 rows and Idenittynumber(100) plus 20 rows.
Thanks in advance
JeffDefine "direction". Rows are not stored in any particular order. Specify a
criteria to sort the data, pot the DDL and some sample data.
ML|||SELECT b.FirstName, b.LastName, b.Address
FROM TableName a JOIN TableName b
ON b.IdentityNumber
BETWEEN (a.IdentityNumber -20) AND (a.IdentityNumber + 20)
WHERE a.LastName = 'Somebody'|||Try something like the following:
select b.FirstName, b.Lastname, b.Address
from TableName a
join TableName b on b.IdentityNumber between a.IdentityNumber - 20 and
a.IdentityNumber + 20
where a.LastName = 'Somebody'
--Brian
(Please reply to the newsgroups only.)
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F46D2C51-A7CB-45B7-A29B-3A297DF00F98@.microsoft.com...
> I'm need a query that takes the number from the identity column, then uses
> that for the next routine in a range...something like;
> Select IdentityNumber
> From TableName
> Where LastName = 'Somebody'
> (Then it takes that IdentityNumber, say row 100, an uses it to grab the
> rows
> on both sides of 100, say 20 rows in each direction. So the second 1/2
> of the operation would look like this.
> Select FirstName, Lastname, Address
> From TableName
> Where identitynumbe(100) minus 20 rows and Idenittynumber(100) plus 20
> rows.
>
> Thanks in advance
> Jeff|||create table Names (
IdentityNumber int,
LastName varchar(255),
FirstName varchar (255)
LogTime datetime, service varchar( 255), machine varchar( 255)
)
87, Peters, Henry, 08/17/2005 10:14:00.000
88, Smith, John, 08/17/2005 10:15:00.000
89, Johnson, Sally, 08/17/2005 10:16:00.000
90, Harris, Betty, 08/17/2005 10:17:00.000
91, Thomas, Steve, 08/17/2005 10:17:30.00
So the query would search and find Johnson,
but return say 20 rows before sally Johnson
(rows 68-88) and 20 rows after Sally Johnson
(rows 90-110)
Thanks Again!!
"ML" wrote:

> Define "direction". Rows are not stored in any particular order. Specify a
> criteria to sort the data, pot the DDL and some sample data.
>
> ML|||>> So the query would search and find Johnson, but return say 20 rows before
Generate a rank column based on the whichever column you want to use to
sequence your data and then use it in your WHERE clause. There are several
ways you can write this & here is one with a derived table construct using
the datetime column used for sequencing :
SELECT t1.*
FROM tbl t1, ( SELECT rank - 20, rank + 20
FROM ( SELECT t1.fname, COUNT( * )
FROM tbl t1, tbl t2
WHERE t2.dt <= t1.dt
GROUP BY t1.id, t1.fname, t1.lname, t1.dt
) T ( fname, rank )
WHERE fname = 'Johnson' ) D ( r1, r2 )
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.dt <= t1.dt ) BETWEEN r1 AND r2 ;
A view could give you a easier read like:
CREATE VIEW vw ( id, fname, lname, dt, rank ) AS
SELECT t1.id, t1.fname, t1.lname, t1.dt, COUNT( * )
FROM tbl t1, tbl t2
WHERE t2.dt <= t1.dt
GROUP BY t1.id, t1.fname, t1.lname, t1.dt
Now, the query is simpler:
SELECT *
FROM vw v1, vw v2
WHERE v1.rank BETWEEN v2.rank - 2 AND v2.rank + 2
AND v2.fname = 'Johnson' ;
Anith|||On Thu, 18 Aug 2005 08:20:03 -0700, Jeff wrote:

>create table Names (
>IdentityNumber int,
>LastName varchar(255),
>FirstName varchar (255)
>LogTime datetime, service varchar( 255), machine varchar( 255)
> )
>87, Peters, Henry, 08/17/2005 10:14:00.000
>88, Smith, John, 08/17/2005 10:15:00.000
>89, Johnson, Sally, 08/17/2005 10:16:00.000
>90, Harris, Betty, 08/17/2005 10:17:00.000
>91, Thomas, Steve, 08/17/2005 10:17:30.00
>So the query would search and find Johnson,
>but return say 20 rows before sally Johnson
>(rows 68-88) and 20 rows after Sally Johnson
>(rows 90-110)
>Thanks Again!!
Hi Jeff,
Untested (since you didn't post the sample data as INSERT statements):
SELECT n1.IdentityNumber, n1.LastName, n1.FirstName, n1.LogTime
FROM Names AS n1
WHERE EXISTS
(SELECT *
FROM Names AS n2
WHERE n2.LastName = 'Johnson'
AND n1.IdentityNumber BETWEEN n2.IdentityNumber - 20
AND n2.IdentityNumber + 20)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

need query by MAX date

Hello All,
I have the following table structure:
CREATE TABLE [dbo].[tbl_PP_PermitStatusDates] (
[Status_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Permit_ID] [int] NOT NULL ,
[Status_Type] [tinyint] NOT NULL ,
[Status_Date] [datetime] NOT NULL
) ON [PRIMARY]
My data is:
INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,2,'4/2/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,2,'4/4/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(818,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,2,'4/2/2005')
I need to make a query to get out a recordset that is the entire row for
the MAX(Status_Date) for that Permit_ID, so like this:
Status_ID Permit_ID Status_Type Status_Date
2 816 2 '4/2/2005'
4 817 2 '4/4/2005'
5 818 1 '4/1/2005'
7 819 2 '4/2/2005'
Mia J.
*** Sent via Developersdex http://www.examnotes.net ***Try,
select [Status_ID], [Permit_ID], [Status_Type], [Status_Date]
from [dbo].[tbl_PP_PermitStatusDates] as a
where [Status_Date] = (select max(b.[Status_Date]) from
[dbo].[tbl_PP_PermitStatusDates] as b where b.[Permit_ID] = a.[Permit_ID])
AMB
"Mij" wrote:

> Hello All,
> I have the following table structure:
> CREATE TABLE [dbo].[tbl_PP_PermitStatusDates] (
> [Status_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Permit_ID] [int] NOT NULL ,
> [Status_Type] [tinyint] NOT NULL ,
> [Status_Date] [datetime] NOT NULL
> ) ON [PRIMARY]
> My data is:
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,2,'4/2/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,2,'4/4/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(818,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,2,'4/2/2005')
> I need to make a query to get out a recordset that is the entire row for
> the MAX(Status_Date) for that Permit_ID, so like this:
> Status_ID Permit_ID Status_Type Status_Date
> 2 816 2 '4/2/2005'
> 4 817 2 '4/4/2005'
> 5 818 1 '4/1/2005'
> 7 819 2 '4/2/2005'
> Mia J.
> *** Sent via Developersdex http://www.examnotes.net ***
>|||On Fri, 01 Apr 2005 13:25:54 -0800, Mij wrote:
(snip)

>I need to make a query to get out a recordset that is the entire row for
>the MAX(Status_Date) for that Permit_ID, so like this:
Hi Mia,
Thanks for posting CREATE TABLE and INSERT statements. You can use:
SELECT Status_ID, Permit_ID, Status_Type, Status_Date
FROM tbl_PP_PermitStatusDates AS a
WHERE Status_Date =
(SELECT MAX(Status_Date)
FROM tbl_PP_PermitStatusDates AS b
WHERE b.Permit_ID = a.Permit_ID)
ORDER BY Status_ID
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||That does seem to work. Thanks.
Mia J.
*** Sent via Developersdex http://www.examnotes.net ***|||Hello Mij,
This one works fine :
select (select Status_ID
from tbl_PP_PermitStatusDates B
WHERE B.Status_Date = MAX(A.Status_Date)
and B.Permit_ID = A.Permit_ID) StatusID,
A.Permit_ID ,
(select Status_Type
from tbl_PP_PermitStatusDates B
WHERE B.Status_Date = MAX(A.Status_Date)
and B.Permit_ID = A.Permit_ID) StatusType,
MAX(A.Status_Date)
from tbl_PP_PermitStatusDates A
Group by A.Permit_ID
Thanks,
Gopi
"Mij" <mdsj@.infi.net> wrote in message
news:uJxBkFwNFHA.2392@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I have the following table structure:
> CREATE TABLE [dbo].[tbl_PP_PermitStatusDates] (
> [Status_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Permit_ID] [int] NOT NULL ,
> [Status_Type] [tinyint] NOT NULL ,
> [Status_Date] [datetime] NOT NULL
> ) ON [PRIMARY]
> My data is:
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,2,'4/2/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,2,'4/4/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(818,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,2,'4/2/2005')
> I need to make a query to get out a recordset that is the entire row for
> the MAX(Status_Date) for that Permit_ID, so like this:
> Status_ID Permit_ID Status_Type Status_Date
> 2 816 2 '4/2/2005'
> 4 817 2 '4/4/2005'
> 5 818 1 '4/1/2005'
> 7 819 2 '4/2/2005'
> Mia J.
> *** Sent via Developersdex http://www.examnotes.net ***