Query help

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)

-- select statment
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)

--selecting all record
select * from @.test

need helpDo you want to delete all except the last date in each account or only delete the earliest date in each account?|||

Dhaval:

I looked through what you requested. See if below is what you mean

Dave

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1180, '07/05/2006',5 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

/*
--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)
*/

print '-- '
print '-- Records before deletions: -- '
print '--'
select * from @.test order by accountNo, invoiceDate

delete from @.test
from ( select accountNo,
min (invoiceDate) as min_invoiceDate
from @.test
group by accountNo
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.min_invoiceDate = a.invoiceDate

delete from @.test
from (
select accountNo,
invoiceDate,
min (dex_row_id) min_dex_row_id,
count(*) as recCt
from @.test
group by accountNo,
invoiceDate
having count(*) > 1
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.invoiceDate = a.invoiceDate
and x.min_dex_row_id <> a.dex_row_id

-- select statment
/*
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)
*/

--selecting all record
print ' '
print ' '
print '-- '
print '-- Records after deletions: -- '
print '--'
select * from @.test

--need help

-- --
-- -- Records before deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-04-05 00:00:00.000 3
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1180 2006-07-05 00:00:00.000 5
-- 1181 2006-05-05 00:00:00.000 3
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2
-- 1182 2006-06-05 00:00:00.000 1

-- --
-- -- Records after deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2

Query help

Hi,
I have a single row in a table:

Title Desc Quantity
----------
aaaa bbbbb 4

and I need the query to insert "Qty" number of records into a second table, e.g

Title1 Desc1
------
aaaa bbbbb
aaaa bbbbb
aaaa bbbbb
aaaa bbbbb

I reckon its some sort of self join but any help would be appreciated.

gregFor the example below I use a function, but you can also use any table that contains sequencial numbers with no gaps. A table with IDENTITY field that did not have any deletes would do.

set nocount on
create table t1 (
title char(4) not null,
[desc] varchar(50) not null,
quantity int not null)
go
create table t2 (
title1 char(4) not null,
desc1 varchar(50) not null)
go
insert t1 values ('aaaa', 'bbbbb', 4)
insert t1 values ('bbbb', 'ccccc', 1)
insert t1 values ('cccc', 'ddddd', 3)
insert t1 values ('dddd', 'eeeee', 2)
insert t1 values ('eeee', 'fffff', 5)
go
insert t2
select title, [desc] from dbo.fn_CartesianProduct() f
inner join t1 on f.[id] < t1.quantity order by 1
go
drop table t1, t2
go|||Excellent - I have an Integers table that substitutes nicely.
Thanks.

Query help

CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your helpYep:
select
s.c1
, x.a
from
stuff
cross join
(
select 1 union all
select 2 union all
select 3
) as x (a)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jack" <jack@.jack.net> wrote in message
news:9uaje.13324$4d6.11003@.trndny04...
CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your help|||> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare|||Well,
I did write one or two that involved a cross join. Here's one that
un-pivots a table:
create table Budgets
(
Contract int not null
, Nominal int not null
, Budget_01 int null
, Budget_02 int null
, Budget_03 int null
, primary key (Contract, Nominal)
)
go
insert Budgets values (1, 123, 1000, 1000, 2000)
insert Budgets values (1, 234, 500, 500, 1000)
insert Budgets values (2, 456, 3000, 4500, 3000)
insert Budgets values (2, 567, 800, 800, 800)
insert Budgets values (3, 789, 500, 500, 500)
insert Budgets values (3, 987, 5000, 500, NULL)
go
select
*
from
(
select
b.Contract
, b.Nominal
, x.Period
, case x.Period
when 1 then b.Budget_01
when 2 then b.Budget_02
when 3 then b.Budget_03
end as Budget
from
Budgets as b
cross join
(
select 1 as Period
union all
select 2
union all
select 3
) as x
) as y
where
Budget is not null
order by
Contract
, Nominal
, Period
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23l4YUHOXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare

Query data type of column in DB table

Hi All

I want to retrieve the data type of table column and validate the input data whether same as data type of table column before insert into database. Any suggestion?

I use asp.net + msde

Thank you.

You could use TypedDataSets or TableAdapters to ensure your data type.|||

Thank you for your help.

Could I use both functions in web programming?

I searched both functions in MSDN and seems was used by visual studio.

Thank you.

|||Yes, Typed DataSets have been around since ASP.Net 1.1, and Table Adapters are new to ASP.Net 2.0. I use Table Adapters quite extensively now. They're really easy to use once you know how to set them up.|||

Thank you very much.

any web site I can read to know the syntax? Because I am a beginner of ASP.NET

THANKS!!

|||Here's a couple of links to a good tutorial on how to create a Data Access Layer and Business Logic Layer which includes usage of TableAdapters:

http://msdn.microsoft.com/asp.net/reference/data/default.aspx?pull=/library/en-us/dnaspnettut/html/aspnet_tutorial01_dataaccesslayer_cs.asp
http://msdn.microsoft.com/asp.net/reference/data/default.aspx?pull=/library/en-us/dnaspnettut/html/aspnet_tutorial02_businesslogiclayer_cs.asp|||

Thanks a lot.