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 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 ***

need only data

hi
here is my sp.
CREATE PROCEDURE dbo.Insert_Employee
(
@.strXML ntext
)
AS
DECLARE @.Pointer int
EXEC sp_xml_preparedocument @.Pointer OUTPUT,
'<Data><woitem id="3420" type="Resistance"><Resistance><R1>0</R1><R2>1</R2><R3>2</R3><R4>3</R4><R5>4</R5><Avg>21</Avg><StdDev>22</StdDev><Cov>23</Cov></Resistance></woitem><woitem id="3421" type="Process"><Process><NipPress>1</NipPress><ZONE1>2</ZON
E1><ZONE2>3</ZONE2><ZONE3>4</ZONE3><ADAPTER></ADAPTER><MeltTemp></MeltTemp><ExSpeed></ExSpeed><Load1></Load1><BackPress></BackPress><TopLip></TopLip><BotLip></BotLip></Process></woitem></Data>'
Begin
--delete from employee where id in(select id from openxml(@.Pointer,'/Data/woitem',2) With (id int '@.id'))
Insert into employee
Select * from OpenXml(@.Pointer,'/Data/woitem',8)
With (id int '@.id', type varchar(50) '@.type', Data varchar(1000) '@.mp:xmltext')
exec sp_xml_removedocument @.Pointer
RETURN
End
GO
when i run this in Data column it is storing "<woitem>...............</woitem>". i want to eliminate <woitem>. how do i do this?
bye
ravi
Sorry for the delay in answering.
@.mp:xmltext is keeping the wrapper because it needs it for the overflow
handling semantics (to for example continue to keep not mapped attributes).
In SQL Server 2000, you have the option to use some string manipulations to
drop the wrapper element (watch for empty elements!).
In SQL Server 2005, you can use the XML datatype in the following way:
Select * from OpenXml(@.Pointer,'/Data/woitem',8)
With (id int '@.id', type varchar(50) '@.type', Data xml'*')
HTH
Michael
2.
"Ravindra" <anonymous@.discussions.microsoft.com> wrote in message
news:3E5D0CCB-18F1-439A-A69E-1037A62731FA@.microsoft.com...
> hi
> here is my sp.
> CREATE PROCEDURE dbo.Insert_Employee
> (
> @.strXML ntext
> )
> AS
> DECLARE @.Pointer int
> EXEC sp_xml_preparedocument @.Pointer OUTPUT,
> '<Data><woitem id="3420"
> type="Resistance"><Resistance><R1>0</R1><R2>1</R2><R3>2</R3><R4>3</R4><R5>4</R5><Avg>21</Avg><StdDev>22</StdDev><Cov>23</Cov></Resistance></woitem><woitem
> id="3421"
> type="Process"><Process><NipPress>1</NipPress><ZONE1>2</ZONE1><ZONE2>3</ZONE2><ZONE3>4</ZONE3><ADAPTER></ADAPTER><MeltTemp></MeltTemp><ExSpeed></ExSpeed><Load1></Load1><BackPress></BackPress><TopLip></TopLip><BotLip></BotLip></Process></woitem></Data>'

> Begin
> --delete from employee where id in(select id from
> openxml(@.Pointer,'/Data/woitem',2) With (id int '@.id'))
> Insert into employee
> Select * from OpenXml(@.Pointer,'/Data/woitem',8)
> With (id int '@.id', type varchar(50) '@.type', Data varchar(1000)
> '@.mp:xmltext')
> exec sp_xml_removedocument @.Pointer
> RETURN
> End
> GO
> when i run this in Data column it is storing
> "<woitem>...............</woitem>". i want to eliminate <woitem>. how
> do i do this?
> bye
> ravi
>

Need LoginID from within Stored Procedure

select loginame from master.dbo.sysprocesses where spid = @.@.spid
This might help. I don't know if there is any other alternative.Also you can use select SERVER_USER.

Need LoginID from within Stored Procedure

select loginame from master.dbo.sysprocesses where spid = @.@.spid
This might help. I don't know if there is any other alternative.
Also you can use select SERVER_USER.