Data results to Variables

Hello,

I am using VWD, SQL EE, VB

I have a select statement with a varaiable in it (as below). Rather than displaying the results directly in a DetailsView I want to add the results to a set of variables (Dim XXX as String etc) so I can check to see if each variable contains data or not, before then displaying only those lines which contain data. Can anyone suggest how I go about linking each data field to a variable?

SELECT PID, Postcode, Address1, Address2, Address3, Address4, Address5, Town_City, County, Postcode AS Expr1
FROM postcode
WHERE (Postcode = @.Postcode)

cheers

marco gwiliani

Hi Marco,

If you haven't done so already, i would suggest giving a really good look at the data tutorials here on asp.net. It's well worth the time and the examples are in both VB and C#.

|||

Yes Marco,

I would suggest you start from learning ADO.NET. Here is a good place to start.

http://msdn2.microsoft.com/en-us/library/h43ks021.aspx

Data partition view

Hi ,
I have question regard data partition view .

Please see below sample from BOL + sample of execution plane .

I would like to ask what is the way to avoid the optimizer scan tables out of the scope (I would expect that the only table for this query will be SUPPLY1)

Thanks,
Eyal

--This example uses tables named SUPPLY1, SUPPLY2, SUPPLY3, and SUPPLY4, which correspond to the supplier tables from four offices, located in different countries/regions.
USE tempdb
GO

--create the tables and insert the values
CREATE TABLE SUPPLY1 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
)
CREATE TABLE SUPPLY2 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
supplier CHAR(50)
)
CREATE TABLE SUPPLY3 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
supplier CHAR(50)
)
CREATE TABLE SUPPLY4 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
supplier CHAR(50)
)
GO
--create the view that combines all supplier tables
CREATE VIEW all_supplier_view
AS
SELECT *
FROM SUPPLY1
UNION ALL
SELECT *
FROM SUPPLY2
UNION ALL
SELECT *
FROM SUPPLY3
UNION ALL
SELECT *
FROM SUPPLY4
GO

INSERT all_supplier_view VALUES ('1', 'CaliforniaCorp')
INSERT all_supplier_view VALUES ('5', 'BraziliaLtd')
INSERT all_supplier_view VALUES ('231', 'FarEast')
INSERT all_supplier_view VALUES ('280', 'NZ')
INSERT all_supplier_view VALUES ('321', 'EuroGroup')
INSERT all_supplier_view VALUES ('442', 'UKArchip')
INSERT all_supplier_view VALUES ('475', 'India')
INSERT all_supplier_view VALUES ('521', 'Afrique')

GO
/* */
SELECT * FROM all_supplier_view WHERE supplyID BETWEEN 1 and 150First

THAT'S A GREAT POST

Second

While you see the optimizer plan, if you look at the number of executes for the other three, you'll see 0. It never did anywork...

And you got Index seeks going on in parallel...

pretty damn effecient, no?

Don't do sample tests in tempdb though, and provide clean up DROPs with the sample...otherwise PERFECT|||Thanks on the compliments.

I have send only sample.
I have almost the same design in a production environment with ~ 1-20 million records per each physical table.

I need to data from only one table with around 3 million recodes in most of the queries.
but I have other table with ~20 million recodes. It means that most on the execution time is useless.

I have done test with direct query to the correct physical table and got the result in 10% of the time!! Vs query the partition view .

I though that partition data view was planed to deal with such cases .

Thanks a lot ,
Eyal|||Are you saying you need to return 3 million records?

I must be misinterpreting...

What does the plan say for the large volume?

Does it show the number of executes other than 0 for the other three?

Are these the only columns in the table?

It doesn't say it, but because the other column is not in the index, it has to go to the data page to get the other column...make a non unique index on the other column...

I'll test it out and see what it does...|||1.I mean that the number of records in the target physical table are ~ 3 melon records vs. ~ 20 million records in other physical table (each physical table means other quarter). The output of the tested query was 7,000 rows.
2.The plans show 1 as number of executes for the other tree tables
3.In my tables there are 5 primary keys ( the DataID is one of it , this one is used as partition constraint) and other 10 measurement columns

eyal|||Well, ok then...

what are you going to do with 7,000 rows? can't be OLTP...are doing batch work against them?|||I insert the result to Daily table group by the extract date and one of the primary keys (means get yesterday records group by on of the rest 4 keys).

This is daily job use to reporting purpose .
Eyal|||I may be wrong, but I don't think you can avoid the reference to other tables participating the view, simply because the view is UNION-based.

Data not being partitioned properly?

Howdy all. Im trying to take advantage of this new feature, but it's not
working as I had expected. Below is the DDL and DML, with explanations of
what Im trying to accomplish and where my confusion is.
USE [AdventureWorks]
GO
/****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
15:01:28 ******/
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
15:10:35 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
/*
From BOL:
Partition 1 2 3 4
Values
col1 <= 1
col1 > 1 AND col1 <= 100
col1 > 100 AND col1 <= 1000
col1 > 1000
Now if I understand correctly, there should be 1 row of data in each
partition?*/
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
/*But this did nothing. So I try:*/
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
/*And that did nothing either. So I try:*/
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
/*And that moved every row of data with a value > 1 (99,999,9999) in the
table to PartitionTestArchive.*/
Again, my goal was just to move the row of data with value 9999 (partition
4) into PartitionTestArchive. So what am I not understanding? It seems that
I either don't understand the concept, or data isn't going into the
partition I think it should?
TIA, ChrisR"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
> Howdy all. Im trying to take advantage of this new feature, but it's not
> working as I had expected. Below is the DDL and DML, with explanations of
> what Im trying to accomplish and where my confusion is.
> USE [AdventureWorks]
> GO
> /****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
> 15:01:28 ******/
> CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> 100, 1000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
>
> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> [PTPK] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([PTPK])
>
> insert into PartitionTest (salary) values (1)
> insert into PartitionTest (salary) values (99)
> insert into PartitionTest (salary) values (999)
> insert into PartitionTest (salary) values (9999)
>
> /*
> From BOL:
> Partition 1 2 3 4
> Values
> col1 <= 1
> col1 > 1 AND col1 <= 100
> col1 > 100 AND col1 <= 1000
> col1 > 1000
>
> Now if I understand correctly, there should be 1 row of data in each
> partition?*/
>
You partitioned the table on PTPK, not Salary.
select $PARTITION.myRangePF2([PTPK]) Partition, *
from PartitionTest
Partition PTPK salary
-- -- --
1 1 1
2 2 99
2 3 999
2 4 9999
(4 row(s) affected)
David|||Woops!
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23PgDEopCHHA.4680@.TK2MSFTNGP04.phx.gbl...
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
> > Howdy all. Im trying to take advantage of this new feature, but it's not
> > working as I had expected. Below is the DDL and DML, with explanations
of
> > what Im trying to accomplish and where my confusion is.
> >
> > USE [AdventureWorks]
> >
> > GO
> >
> > /****** Object: PartitionFunction [myRangePF2] Script Date:
11/17/2006
> > 15:01:28 ******/
> >
> > CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> > 100, 1000)
> >
> >
> >
> > /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> > 15:10:35 ******/
> >
> > CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> > ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> >
> > [salary] [int] NOT NULL,
> >
> > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> >
> > (
> >
> > [PTPK] ASC
> >
> > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> >
> > ) ON [myRangePS2]([PTPK])
> >
> >
> >
> > insert into PartitionTest (salary) values (1)
> >
> > insert into PartitionTest (salary) values (99)
> >
> > insert into PartitionTest (salary) values (999)
> >
> > insert into PartitionTest (salary) values (9999)
> >
> >
> >
> > /*
> >
> > From BOL:
> >
> > Partition 1 2 3 4
> >
> > Values
> >
> > col1 <= 1
> >
> > col1 > 1 AND col1 <= 100
> >
> > col1 > 100 AND col1 <= 1000
> >
> > col1 > 1000
> >
> >
> >
> > Now if I understand correctly, there should be 1 row of data in each
> > partition?*/
> >
> >
> You partitioned the table on PTPK, not Salary.
> select $PARTITION.myRangePF2([PTPK]) Partition, *
> from PartitionTest
> Partition PTPK salary
> -- -- --
> 1 1 1
> 2 2 99
> 2 3 999
> 2 4 9999
> (4 row(s) affected)
> David
>
>

Data not being partitioned properly?

Howdy all. Im trying to take advantage of this new feature, but it's not
working as I had expected. Below is the DDL and DML, with explanations of
what Im trying to accomplish and where my confusion is.
USE [AdventureWorks]
GO
/****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
15:01:28 ******/
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
15:10:35 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
/*
From BOL:
Partition 1 2 3 4
Values
col1 <= 1
col1 > 1 AND col1 <= 100
col1 > 100 AND col1 <= 1000
col1 > 1000
Now if I understand correctly, there should be 1 row of data in each
partition?*/
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
/*But this did nothing. So I try:*/
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
/*And that did nothing either. So I try:*/
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
/*And that moved every row of data with a value > 1 (99,999,9999) in the
table to PartitionTestArchive.*/
Again, my goal was just to move the row of data with value 9999 (partition
4) into PartitionTestArchive. So what am I not understanding? It seems that
I either don't understand the concept, or data isn't going into the
partition I think it should?
TIA, ChrisR
Woops!
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23PgDEopCHHA.4680@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
of[vbcol=seagreen]
11/17/2006
> You partitioned the table on PTPK, not Salary.
> select $PARTITION.myRangePF2([PTPK]) Partition, *
> from PartitionTest
> Partition PTPK salary
> -- -- --
> 1 1 1
> 2 2 99
> 2 3 999
> 2 4 9999
> (4 row(s) affected)
> David
>
>

Data not being partitioned properly?

Howdy all. Im trying to take advantage of this new feature, but it's not
working as I had expected. Below is the DDL and DML, with explanations of
what Im trying to accomplish and where my confusion is.
USE [AdventureWorks]
GO
/****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/20
06
15:01:28 ******/
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
15:10:35 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
/*
From BOL:
Partition 1 2 3 4
Values
col1 <= 1
col1 > 1 AND col1 <= 100
col1 > 100 AND col1 <= 1000
col1 > 1000
Now if I understand correctly, there should be 1 row of data in each
partition?*/
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
/*But this did nothing. So I try:*/
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
/*And that did nothing either. So I try:*/
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
/*And that moved every row of data with a value > 1 (99,999,9999) in the
table to PartitionTestArchive.*/
Again, my goal was just to move the row of data with value 9999 (partition
4) into PartitionTestArchive. So what am I not understanding? It seems that
I either don't understand the concept, or data isn't going into the
partition I think it should?
TIA, ChrisR
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
> Howdy all. Im trying to take advantage of this new feature, but it's not
> working as I had expected. Below is the DDL and DML, with explanations of
> what Im trying to accomplish and where my confusion is.
> USE [AdventureWorks]
> GO
> /****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/
2006
> 15:01:28 ******/
> CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (
1,
> 100, 1000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/20
06
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
>
> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> [PTPK] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([PTPK])
>
> insert into PartitionTest (salary) values (1)
> insert into PartitionTest (salary) values (99)
> insert into PartitionTest (salary) values (999)
> insert into PartitionTest (salary) values (9999)
>
> /*
> From BOL:
> Partition 1 2 3 4
> Values
> col1 <= 1
> col1 > 1 AND col1 <= 100
> col1 > 100 AND col1 <= 1000
> col1 > 1000
>
> Now if I understand correctly, there should be 1 row of data in each
> partition?*/
>
You partitioned the table on PTPK, not Salary.
select $PARTITION.myRangePF2([PTPK]) Partition, *
from PartitionTest
Partition PTPK salary
-- -- --
1 1 1
2 2 99
2 3 999
2 4 9999
(4 row(s) affected)
David|||Woops!
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23PgDEopCHHA.4680@.TK2MSFTNGP04.phx.gbl...
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
of[vbcol=seagreen]
11/17/2006[vbcol=seagreen]
> You partitioned the table on PTPK, not Salary.
> select $PARTITION.myRangePF2([PTPK]) Partition, *
> from PartitionTest
> Partition PTPK salary
> -- -- --
> 1 1 1
> 2 2 99
> 2 3 999
> 2 4 9999
> (4 row(s) affected)
> David
>
>

data manipulation group by problem

Hi I have a data manipulation query. I have the table below which gets
populated on a daily basis
CREATE TABLE [dbo].[FreeSpace] (
[Drive] [char] (1) not null ,
[MB_Free] [int] not null ,
[day_time] [datetime] default getdate()NOT NULL
) ON [PRIMARY]
insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
populates table
I run the query included below but the data comes out as example below
drive monday tuesday etc....
c 100mb null
c null 100mb
d 200mb null
d null 200mb
I would like to display like this
drive monday tuesday etc....
c 100mb 100mb
D 200mb 200mb
My query below
SELECT Drive,
case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Monday,
case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Tuesday,
case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Wednesday,
case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Thursday,
case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Friday,
case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Saturday,
case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Sunday
from FreeSpace
group by Drive,datepart(dd, day_time),MB_Free
order by drive
thanks for any help
SammySammy
See if this helps you
SELECT Drive,
MAX(case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +'
MB
Free Space'
end) as Monday,
MAX(case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +'
MB
Free Space'
end )as Tuesday
......
from FreeSpace
group by Drive
order by drive
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:E4C10338-8829-42B6-A3B9-F87B1224EFF9@.microsoft.com...
> Hi I have a data manipulation query. I have the table below which gets
> populated on a daily basis
> CREATE TABLE [dbo].[FreeSpace] (
> [Drive] [char] (1) not null ,
> [MB_Free] [int] not null ,
> [day_time] [datetime] default getdate()NOT NULL
> ) ON [PRIMARY]
> insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
> populates table
> I run the query included below but the data comes out as example below
> drive monday tuesday etc....
> c 100mb null
> c null 100mb
> d 200mb null
> d null 200mb
> I would like to display like this
> drive monday tuesday etc....
> c 100mb 100mb
> D 200mb 200mb
> My query below
> SELECT Drive,
> case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Monday,
> case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Tuesday,
> case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Wednesday,
> case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Thursday,
> case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Friday,
> case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Saturday,
> case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Sunday
> from FreeSpace
> group by Drive,datepart(dd, day_time),MB_Free
> order by drive
> thanks for any help
> Sammy|||Thanks Uri
"Sammy" wrote:

> Hi I have a data manipulation query. I have the table below which gets
> populated on a daily basis
> CREATE TABLE [dbo].[FreeSpace] (
> [Drive] [char] (1) not null ,
> [MB_Free] [int] not null ,
> [day_time] [datetime] default getdate()NOT NULL
> ) ON [PRIMARY]
> insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
> populates table
> I run the query included below but the data comes out as example below
> drive monday tuesday etc....
> c 100mb null
> c null 100mb
> d 200mb null
> d null 200mb
> I would like to display like this
> drive monday tuesday etc....
> c 100mb 100mb
> D 200mb 200mb
> My query below
> SELECT Drive,
> case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Monday,
> case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Tuesday,
> case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Wednesday,
> case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Thursday,
> case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Friday,
> case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Saturday,
> case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Sunday
> from FreeSpace
> group by Drive,datepart(dd, day_time),MB_Free
> order by drive
> thanks for any help
> Sammy

Data Locking Method

Dear All,
I have a receiving application. The app works like below:
1. The operator must key in the delivery notes of the truck at the
receiving offices
2. The operator scans each items within a truck at the warehouse.
After finish unloading the items of one delivery notes (of truck), I
want the operator at the warehouse can't add or delete items anymore.
So the reconciliation report won't be changed in the future.
Does anyone have any suggestion how to solve this problem?
Thanks
Robert LieBEGIN TRANSACTION when the delivery is put on the truck for delivery.
COMMIT TRANSACTION when the deliver arrives at the warehouse and is put on
shelf.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:emexYRTrFHA.2996@.tk2msftngp13.phx.gbl...
> Dear All,
> I have a receiving application. The app works like below:
> 1. The operator must key in the delivery notes of the truck at the
> receiving offices
> 2. The operator scans each items within a truck at the warehouse.
> After finish unloading the items of one delivery notes (of truck), I want
> the operator at the warehouse can't add or delete items anymore.
> So the reconciliation report won't be changed in the future.
> Does anyone have any suggestion how to solve this problem?
> Thanks
> Robert Lie|||On Tue, 30 Aug 2005 14:37:52 +0700, Robert Lie
<robert.lie24@.gmail.com> wrote:
>I have a receiving application. The app works like below:
>1. The operator must key in the delivery notes of the truck at the
>receiving offices
>2. The operator scans each items within a truck at the warehouse.
>After finish unloading the items of one delivery notes (of truck), I
>want the operator at the warehouse can't add or delete items anymore.
>So the reconciliation report won't be changed in the future.
>Does anyone have any suggestion how to solve this problem?
Not really a data locking issue, it's more about application design, i
think.
J.