can we do string manipulation on a parameter?

I populated a parameter list with dataset.
The parameter @.type_year has items look like
"plan_2000" or "Actual_2000".
Now my question is, if we can use some string function targeting @.type_year in the query.
something like "select ..... from ... where
year=getyear(@.type_year) and type=gettype(@.type_year)" ?
Please let me know if you know the feasibility. Thanks.
Sincerelyyou can. regarding your SQL database its easier to do.
for example in SQL Serbver, you can create 2 functions to extract the year
part or the type part of your parameter. (using the SQL substring function)
but I recommand to use 2 parameters
* 1 for the year
* 1 for the type
"Frank RS" <FrankRS@.discussions.microsoft.com> a écrit dans le message de
news:2B0E0EA7-2955-4B9A-BEFA-053ECA609CB9@.microsoft.com...
> I populated a parameter list with dataset.
> The parameter @.type_year has items look like
> "plan_2000" or "Actual_2000".
> Now my question is, if we can use some string function targeting
@.type_year in the query.
> something like "select ..... from ... where
> year=getyear(@.type_year) and type=gettype(@.type_year)" ?
> Please let me know if you know the feasibility. Thanks.
>
> Sincerely

Can TSQL query create new output column ?

How can i write a query to split a database column and shows 2 new columns. In my database column

I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter

and run another query.

For example a database column with average number and range number.

Thanks

Daniel

Can you post some DDL, sample data and expected results?

AMB

|||

Hai,

Can you try the below query, and let me know that, it relates to your requirement or not:

DECLARE @.Columns varchar(1000)

SET @.Columns = ''

-- Create a temporary table.

CREATE TABLE #TempTable(Items varchar(50))

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('D')

INSERT INTO #TempTable(Items) VALUES('D')

-- Before

SELECT * FROM #TempTable

-- Make a column list

SELECT

@.Columns = @.Columns + '[' + Items + '], '

FROM #TempTable

GROUP BY Items

-- Check the column values exits or not.

IF ( @.Columns IS NOT NULL ) AND ( @.Columns <> '' )

BEGIN

DECLARE @.Query nvarchar(1000)

SELECT @.Columns = SUBSTRING(@.Columns,1, LEN(@.Columns)-1)

SELECT @.Query = '

SELECT

*

FROM

(

SELECT

Items

FROM #TempTable

) AS Dummy

PIVOT

(

MAX(Items)

FOR Items IN (' + @.Columns + ')

)AS PvtTable'

EXEC(@.Query)

END

-- Drop the temporary table.

DROP TABLE #TempTable

Please clarify If I did any wrong.

Regards,

Kiran.Y

|||

Perhaps something like:

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyGroup int,
MyValue decimal(10,2)
)

INSERT INTO @.MyTable VALUES ( 1, 25 )
INSERT INTO @.MyTable VALUES ( 2, 5 )
INSERT INTO @.MyTable VALUES ( 1, 10 )
INSERT INTO @.MyTable VALUES ( 1, 15 )
INSERT INTO @.MyTable VALUES ( 1, 4 )
INSERT INTO @.MyTable VALUES ( 2, 6 )
INSERT INTO @.MyTable VALUES ( 2, 11 )
INSERT INTO @.MyTable VALUES ( 2, 0 )
INSERT INTO @.MyTable VALUES ( 1, 12 )

SELECT
Average = cast( avg( MyValue ) AS decimal(10,2)),
Range = ( cast( min( MyValue ) AS varchar(10)) + '-' +
cast( max( MyValue ) AS varchar(10)))
FROM @.MyTable
GROUP BY MyGroup

Average Range
13.20 4.00-25.00
5.50 0.00-11.00

|||

Hi Kiran

Thanks for answering my email. To clarify this below are my tables and columns and my query

Table: Item Stat_label Stat_value

column: Pack ID Stat_label_ID Stat_value_ID

Pack_Num Label ( has 2 rows Value

Ave and Range)

My query to list Pack_Num, Ave and it's value

SELECT Item.Pack_Num, Stat_label.Label, Stat_value.Value

FROM Item, Stat_label, Stat_value

WHERE Item.packID=Stat_label.Stat_label_ID AND

Stat_label.Stat_lavel_ID=Stat_value.Stat_value_ID

AND Stat_label.Label= Ave

My question: I want a query to list Pack_Num, Ave, Range and value

How can I do it?

That's mean this query need to split the Stat_label and list another

column name"Range".

Thanks
Daniel

|||

If you are using SQL 2005, look into the PIVOT function.

If you are using SQL 2000, explore using CASE.

Maybe these articles will help:

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

|||

Thanks all

I can not use "insert" because my account for this is read only and I avoid to list everything in a column and and use Excel pivot to summary.

Daniel

|||

Daniel,

If you would carefully examine the code provided, you will see that the INSERT statements are only building a sample table so that we could demonstrate a query suggestion.

You didn't bother to provide the table DDL, or sample data, so we have to waste our time creating sample data for you. and apparently, you can't read and understand example code.

|||

This may be closer to what you are hoping to find:

SELECT

i.Pack_Num,

sl.Stat_Label,
Average = cast( avg( sv.Stat_Value ) AS decimal(10,2)),
Range = ( cast( min( sv.Stat_Value ) AS varchar(10)) + '-' +
cast( max( sv.Stat_Value ) AS varchar(10)))
FROM Item i

JOIN Stat_Label sl

ON i.Pack_ID = sl.Stat_Label_ID

JOIN Stat_Value sv

ON sl.Stat_Label_ID = sv.Stat_Value_ID

WHERE sl.Label = 'Ave'
GROUP BY

i.Pack_Num,

sl.Stat_Label

|||

Thanks Anrnie but It is not working

Error at Average= cast......

Error at Range= (cast......

My Average and Range are decimal, no need cast

Do I have to declare a temp table?

Daniel

|||

Actually, it appears that the Stat_Value is most most likely a varchar().

Before we can help you any further, please post the table DDL and some sample data in the form of INSERT statements. Please refer to this link for help in preparing your material.

|||

Can SQL query create a new column or not?. DO NOT want to make a temp table.

Thanks

Daniel

|||

Can TSQL create a new column at the output?

If not I need 2 select statement but how to joint them? Can not use EXCEPT in TSQL? Tried to use UNION but

the results in one column.

It's complicated with creating a temp table since I do not know how to insert to temp table from database.

Thanks

Daniel

|||Please supply the requested information. (See my previous post.)

can someone improve this

Case of study:
I have warehouse which stores items.
Wharehouse has defined different locations - location is represented with
locationID.
(location has also type and distance - that is the distance in
meters from start position, but this data is used just for order)
Each location has one or more palette. Palette has unique label: itemOZN.
Each palette has many items(articles), and item is marked with itemID.
Each combination of palette and item has different stockID
(on the same palette can be items with the same itemID but different
properties)
So, if you want to define one item in stock, you must have its stockID.
When I get request to export one ot more items(articles) with some quantity,
I must find
all the positions of the items in the warehouse
(the same article can be on different locations) and take the most
appropriate ones (already reserved are at the end of the choice, and also
closer articles has
the precedence over the more distantiated ones,
also if the production date is older, than the article must go out from
stock before the new ones, and so on..)
When I find the appropriate items, I must wite them into reservation table.
That is stockID and quantity.
I hope that it's clear enough. It's simple example and I solve it with 2
cursor.
It works perfect but with slow performance.
You have my example with all DDL and test data.
I would like to know, if it's possible to create the same solution without
cursor because of performance reason.
The working code:
declare @.itemID varchar(10),@.quality int,@.requiredQ decimal(15,5),@.pDate
datetime,@.lokType int,@.stockID int
declare @.availableQ decimal(15,5)
BEGIN TRANSACTION
--first, I lookup for all required items to export from warehouse
declare cCur cursor local for select
itemID,quality,quantity,productionDate,l
okType from dbo.tblRequiredItems
open cCur
fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
while @.@.fetch_status=0
begin
--for each item I find all stocks and I order stocks from most appropriate
to the less appropriate(ORDER BY)
--if quality is gived I must find the items with required or better quality
else doesn't matter, the same is with production date
declare cCur1 cursor local for
select T1.stockID,T1.availableQ FROM
(select i.stockID,i.itemQuantity-isnull(sum(r.quantity),0) as availableQ,
reservation=case when exists(SELECT * FROM dbo.tblReservations r1 INNER
JOIN dbo.tblItems i1
ON r1.stockID=i1.stockID WHERE i1.itemID=@.itemID) then 1 else 0 end,
l.distance
from dbo.tblItems i INNER JOIN dbo.tblLocation l ON
i.locationID=l.locationID
AND (@.lokType is null OR l.lokType=@.lokType) LEFT JOIN dbo.tblReservations
r ON i.stockID=r.stockID
WHERE i.itemID=@.itemID AND (@.pDate is null OR i.itemProductionDate>=@.pDate)
AND
(@.quality is null OR i.itemQuality>=@.quality)
GROUP BY i.stockID,i.itemQuantity,l.distance
having i.itemQuantity-isnull(sum(r.quantity),0)>0)as T1
ORDER BY T1.reservation,T1.distance,T1.availableQ DESC
open cCur1
fetch next from cCur1 INTO @.stockID,@.availableQ
--I reserve items until reach the required quantity
while @.@.fetch_status=0
begin
if @.availableQ>=@.requiredQ
begin
if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
UPDATE dbo.tblReservations SET quantity=quantity+@.requiredQ WHERE
stockID=@.stockID
else
INSERT INTO dbo.tblReservations(stockID,quantity)
SELECT @.stockID,@.requiredQ
SET @.requiredQ=0
BREAK
end
else
begin
SET @.requiredQ=@.requiredQ-@.availableQ
if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
UPDATE dbo.tblReservations SET quantity=quantity+@.availableQ WHERE
stockID=@.stockID
else
INSERT INTO dbo.tblReservations(stockID,quantity)
SELECT @.stockID,@.availableQ
end
fetch next from cCur1 INTO @.stockID,@.availableQ
end
close cCur1
deallocate cCur1
if @.requiredQ>0
begin
RAISERROR(60017,11,1)
break
end
fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
end
close cCur
deallocate cCur
delete from dbo.tblRequiredItems
if @.@.error=0
COMMIT TRANSACTION
else
ROLLBACK TRANSACTION
The DDL with sample data:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblItems]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblLocation]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblLocation]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblRequiredItems]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblRequiredItems]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblReservations]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblReservations]
GO
CREATE TABLE [dbo].[tblItems] (
[stockID] [int] IDENTITY (1, 1) NOT NULL ,
[itemOZN] [varchar] (20) COLLATE Slovenian_CI_AS NOT NULL ,
[itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[itemQuantity] [decimal](15, 5) NOT NULL ,
[itemQuality] [int] NOT NULL ,
[itemProductionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLocation] (
[locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[lokType] [int] NULL ,
[distance] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblRequiredItems] (
[requireID] [int] IDENTITY (1, 1) NOT NULL ,
[itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[quality] [int] NULL ,
[quantity] [decimal](15, 5) NOT NULL ,
[productionDate] [datetime] NULL ,
[lokType] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblReservations] (
[reservationID] [int] IDENTITY (1, 1) NOT NULL ,
[stockID] [int] NULL,
[quantity] [decimal](15, 5) NOT NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblItems] ADD
CONSTRAINT [PK_tblItems] PRIMARY KEY CLUSTERED
(
[stockID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLocation] ADD
CONSTRAINT [PK_tblLocation] PRIMARY KEY CLUSTERED
(
[locationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblRequiredItems] ADD
CONSTRAINT [PK_tblRequiredItems] PRIMARY KEY CLUSTERED
(
[requireID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblReservations] ADD
CONSTRAINT [PK_tblReservations] PRIMARY KEY CLUSTERED
(
[reservationID]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
VALUES('A001',1,100)
INSERT INTO [tblLocation]([locationID], [lokType], [distance])
VALUES('B001',1,200)
INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
VALUES('C001',2,150)
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00001','0001','A001',20,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00001','0002','A001',15,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00001','0003','A001',50,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00002','0001','B001',30,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00002','0002','B001',60,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00002','0003','B001',40,1,'20050
101')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00003','0001','B001',5,1,'200502
02')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00003','0002','B001',10,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00003','0003','B001',30,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00004','0001','C001',20,1,'20050
202')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00004','0002','C001',5,1,'200502
02')
INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
[itemQuantity],[itemQuality], [itemProductionDate])
VALUES('00004','0003','C001',25,1,'20050
202')
INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
VALUES(1,5)
INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
VALUES(5,10)
INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
VALUES(9,12)
INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
[productionDate], [lokType])
VALUES('0001',1,40,'20050101',null)
INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
[productionDate], [lokType])
VALUES('0002',1,10,'20041212',null)
INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
[productionDate], [lokType])
VALUES('0003',null,10,null,2)>> can someone improve this
Nope.
"simon" wrote:

> Case of study:
> I have warehouse which stores items.
> Wharehouse has defined different locations - location is represented with
> locationID.
> (location has also type and distance - that is the distance in
> meters from start position, but this data is used just for order)
> Each location has one or more palette. Palette has unique label: itemOZN.
> Each palette has many items(articles), and item is marked with itemID.
> Each combination of palette and item has different stockID
> (on the same palette can be items with the same itemID but different
> properties)
> So, if you want to define one item in stock, you must have its stockID.
> When I get request to export one ot more items(articles) with some quantit
y,
> I must find
> all the positions of the items in the warehouse
> (the same article can be on different locations) and take the most
> appropriate ones (already reserved are at the end of the choice, and also
> closer articles has
> the precedence over the more distantiated ones,
> also if the production date is older, than the article must go out from
> stock before the new ones, and so on..)
> When I find the appropriate items, I must wite them into reservation table
.
> That is stockID and quantity.
> I hope that it's clear enough. It's simple example and I solve it with 2
> cursor.
> It works perfect but with slow performance.
> You have my example with all DDL and test data.
> I would like to know, if it's possible to create the same solution without
> cursor because of performance reason.
> The working code:
> declare @.itemID varchar(10),@.quality int,@.requiredQ decimal(15,5),@.pDate
> datetime,@.lokType int,@.stockID int
> declare @.availableQ decimal(15,5)
> BEGIN TRANSACTION
> --first, I lookup for all required items to export from warehouse
> declare cCur cursor local for select
> itemID,quality,quantity,productionDate,l
okType from dbo.tblRequiredItems
> open cCur
> fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
> while @.@.fetch_status=0
> begin
> --for each item I find all stocks and I order stocks from most appropriate
> to the less appropriate(ORDER BY)
> --if quality is gived I must find the items with required or better qualit
y
> else doesn't matter, the same is with production date
> declare cCur1 cursor local for
> select T1.stockID,T1.availableQ FROM
> (select i.stockID,i.itemQuantity-isnull(sum(r.quantity),0) as availableQ,
> reservation=case when exists(SELECT * FROM dbo.tblReservations r1 INNER
> JOIN dbo.tblItems i1
> ON r1.stockID=i1.stockID WHERE i1.itemID=@.itemID) then 1 else 0 end,
> l.distance
> from dbo.tblItems i INNER JOIN dbo.tblLocation l ON
> i.locationID=l.locationID
> AND (@.lokType is null OR l.lokType=@.lokType) LEFT JOIN dbo.tblReservation
s
> r ON i.stockID=r.stockID
> WHERE i.itemID=@.itemID AND (@.pDate is null OR i.itemProductionDate>=@.pDat
e)
> AND
> (@.quality is null OR i.itemQuality>=@.quality)
> GROUP BY i.stockID,i.itemQuantity,l.distance
> having i.itemQuantity-isnull(sum(r.quantity),0)>0)as T1
> ORDER BY T1.reservation,T1.distance,T1.availableQ DESC
> open cCur1
> fetch next from cCur1 INTO @.stockID,@.availableQ
> --I reserve items until reach the required quantity
> while @.@.fetch_status=0
> begin
> if @.availableQ>=@.requiredQ
> begin
> if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
> UPDATE dbo.tblReservations SET quantity=quantity+@.requiredQ WHERE
> stockID=@.stockID
> else
> INSERT INTO dbo.tblReservations(stockID,quantity)
> SELECT @.stockID,@.requiredQ
> SET @.requiredQ=0
> BREAK
> end
> else
> begin
> SET @.requiredQ=@.requiredQ-@.availableQ
> if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@.stockID)
> UPDATE dbo.tblReservations SET quantity=quantity+@.availableQ WHERE
> stockID=@.stockID
> else
> INSERT INTO dbo.tblReservations(stockID,quantity)
> SELECT @.stockID,@.availableQ
> end
> fetch next from cCur1 INTO @.stockID,@.availableQ
> end
> close cCur1
> deallocate cCur1
> if @.requiredQ>0
> begin
> RAISERROR(60017,11,1)
> break
> end
> fetch next from cCur INTO @.itemID ,@.quality ,@.requiredQ ,@.pDate, @.lokType
> end
> close cCur
> deallocate cCur
> delete from dbo.tblRequiredItems
> if @.@.error=0
> COMMIT TRANSACTION
> else
> ROLLBACK TRANSACTION
> The DDL with sample data:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblItems]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblLocation]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblLocation]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblRequiredItems]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblRequiredItems]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblReservations]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[tblReservations]
> GO
> CREATE TABLE [dbo].[tblItems] (
> [stockID] [int] IDENTITY (1, 1) NOT NULL ,
> [itemOZN] [varchar] (20) COLLATE Slovenian_CI_AS NOT NULL ,
> [itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [itemQuantity] [decimal](15, 5) NOT NULL ,
> [itemQuality] [int] NOT NULL ,
> [itemProductionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblLocation] (
> [locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [lokType] [int] NULL ,
> [distance] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblRequiredItems] (
> [requireID] [int] IDENTITY (1, 1) NOT NULL ,
> [itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [quality] [int] NULL ,
> [quantity] [decimal](15, 5) NOT NULL ,
> [productionDate] [datetime] NULL ,
> [lokType] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblReservations] (
> [reservationID] [int] IDENTITY (1, 1) NOT NULL ,
> [stockID] [int] NULL,
> [quantity] [decimal](15, 5) NOT NULL ,
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblItems] ADD
> CONSTRAINT [PK_tblItems] PRIMARY KEY CLUSTERED
> (
> [stockID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblLocation] ADD
> CONSTRAINT [PK_tblLocation] PRIMARY KEY CLUSTERED
> (
> [locationID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblRequiredItems] ADD
> CONSTRAINT [PK_tblRequiredItems] PRIMARY KEY CLUSTERED
> (
> [requireID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblReservations] ADD
> CONSTRAINT [PK_tblReservations] PRIMARY KEY CLUSTERED
> (
> [reservationID]
> ) ON [PRIMARY]
> GO
>
>
> INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
> VALUES('A001',1,100)
> INSERT INTO [tblLocation]([locationID], [lokType], [distance])
> VALUES('B001',1,200)
> INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
> VALUES('C001',2,150)
>
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0001','A001',20,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0002','A001',15,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0003','A001',50,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0001','B001',30,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0002','B001',60,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0003','B001',40,1,'20050
101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0001','B001',5,1,'200502
02')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0002','B001',10,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0003','B001',30,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0001','C001',20,1,'20050
202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0002','C001',5,1,'200502
02')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0003','C001',25,1,'20050
202')
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(1,5)
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(5,10)
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(9,12)
>
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0001',1,40,'20050101',null)
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0002',1,10,'20041212',null)
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0003',null,10,null,2)
>
>|||Once you design actual tables and partially miss the business requirements,
there's not much anyone can do about it. Don't design any tables until you
know exactly what you need.
Good designs look good on paper first. Identify your entities, identify
relationships, and build a use case before you start designing tables.
ML