Help with data flow

I would like to use Integration Services to update data in my datawarehouse. I have a table called "AgentStats" that stores archived data from the past 3 years. I would like to import the current year's data from the production server into the same table in my datawarehouse and have my ETL update only the current year's day on a daily basis. The current year's data is constantly updated in the datasource, so I achive the data at the end of the year. Any ideas how I can accomplish this?

Thank You

-Sam

This seems like a simple task, so here goes-

Source (Filter on date to restrict to current year if more than that present) --> Lookup (Lookup to detect if current row exists, using PK columns) --1-> Insert when not exists, Destination

--2-> Update when exists, OLE-DB Command

Things are often more complicated than this, and there are several ways of doing the same job, but that outlines the simplest.

|||Read through this thread: Checking to see if a record exists if so update else insert

Help with Cursor to insert 100 rows at a time

Hi all,

Can one of you help me with using a cursor that would insert only 100 rows at a time from source table 1 to target table 2. I am not able to loop beyond the first 100 rows.

Here is what I have till now:

CREATE procedure Insert100RowsAtaTime
AS
SET NOCOUNT ON

declare @.Col1 int
declare @.Col2 char(9)
DECLARE @.RETURNVALUE int
DECLARE @.ERRORMESSAGETXT varchar(510)
DECLARE @.ERRORNUM int
DECLARE @.LOCALROWCOUNT int

declare Insert_Cur cursor local fast_forward
FOR
SELECT top 100 Col1,Col2 from Table1
WHERE Col1 not in ( SELECT Col1 /* Col1 is PK. This statement is used to prevent the same rows from being inserted in Table 2*/
from Table2)

set @.RETURNVALUE = 0
set @.ERRORNUM = 0

BEGIN

open Insert_Cur
fetch NEXT from Insert_Cur into @.Col1, @.Col2
while (@.@.FETCH_STATUS = 0)
insert into Table2 (Col1,Col2) select @.Col1,@.Col2

SELECT @.ERRORNUM = @.@.ERROR, @.LOCALROWCOUNT = @.@.ROWCOUNT
IF @.ERRORNUM = 0
BEGIN
IF @.LOCALROWCOUNT >= 1
BEGIN
SELECT @.RETURNVALUE = 0
END
ELSE
BEGIN
SELECT @.RETURNVALUE = 1
RAISERROR ('INSERT FAILS',16, 1)
END
END
ELSE
BEGIN
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
SELECT @.RETURNVALUE = 1
END

fetch NEXT from Insert_Cur into @.Col1, @.Col2
end

close Insert_Cur
deallocate Insert_Cur

RETURN @.RETURNVALUE
ENDFirst of all, I don't understand what you really want to do so I can't give you a usable or correct response. I can tell you almost certainly that a cursor is not the correct answer.

You have a PK. A cursor isn't needed and it will probably hurt you in terms of both complexity and performance.

Can you describe what you really want in terms of the real world? In business or end-user terms, not in geek speak.

There are definitely ways to do what you want. They are probably simple and fast. I don't know enough to help you yet, but if you describe what you are trying to do a bit better then I'd bet that someone here can help.

-PatP|||It seems to me your not inserting all rows with 100 rows at the time, you're inserting 100 rows one at the time. After row no 100, the cursor is finished and your procedure is done.

This should be more like what you descibe (albeit not the most efficient way, but at least it eliminates the cursor):
WHILE EXISTS (
SELECT 1
FROM Table1
WHERE Col1 NOT IN (SELECT Col1 FROM Table2)
)
BEGIN
INSERT table2 (Col1, Col2)
SELECT top 100 Col1,Col2
FROM Table1
WHERE Col1 NOT IN (SELECT Col1 FROM Table2)

-- Maybe do some error checking here
END

A question about your code:
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
What's this supposed to do?
1) The message is already raised the moment the error occurs
2) What about the placeholders in the messages?
3) @.@.ERROR at that moment is always 0|||Table 1 has over 500 million rows. The task is To select data from Table 1 (based on business rules) and insert into Table 2. The concern was Selecting all data may take a long time to execute and in case of any issue with the quety, a long time to roll back. Hence 100 rows at a time using a cursor.|||As mentioned, forget the cursor! They are for row-by-row processing.

Help with cursor and decimal values

The following table when using a cursor gives 0 for decimals where the value
s
are below 1. I am trying to use this to apply a factor and always get a zero
if the factor below 1, as a result my logic fails.
Can some one help me on this?
-- ========== Table & data =============
CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
INSERT INTO TestFactor VALUES (1, 0.25)
INSERT INTO TestFactor VALUES (2, 0.50)
INSERT INTO TestFactor VALUES (3, 0.75)
INSERT INTO TestFactor VALUES (4, 0.125)
INSERT INTO TestFactor VALUES (5, 0.25)
INSERT INTO TestFactor VALUES (6, 2)
INSERT INTO TestFactor VALUES (7, 1)
-- ========== Table & data =============
DECLARE @.Factor decimal
DECLARE my CURSOR
FOR SELECT FactorValue from TestFactor
OPEN my
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
END
CLOSE my
DEALLOCATE my
Thankschange DECLARE @.Factor decimal to
DECLARE @.Factor decimal (5,2) -- same as in the table
http://sqlservercode.blogspot.com/|||Change
DECLARE @.Factor decimal
to
DECLARE @.Factor decimal(5,2)
"Ram" wrote:

> The following table when using a cursor gives 0 for decimals where the val
ues
> are below 1. I am trying to use this to apply a factor and always get a ze
ro
> if the factor below 1, as a result my logic fails.
> Can some one help me on this?
> -- ========== Table & data =============
> CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
> INSERT INTO TestFactor VALUES (1, 0.25)
> INSERT INTO TestFactor VALUES (2, 0.50)
> INSERT INTO TestFactor VALUES (3, 0.75)
> INSERT INTO TestFactor VALUES (4, 0.125)
> INSERT INTO TestFactor VALUES (5, 0.25)
> INSERT INTO TestFactor VALUES (6, 2)
> INSERT INTO TestFactor VALUES (7, 1)
> -- ========== Table & data =============
>
> DECLARE @.Factor decimal
> DECLARE my CURSOR
> FOR SELECT FactorValue from TestFactor
> OPEN my
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> END
> CLOSE my
> DEALLOCATE my
> Thanks
>|||Thanks Mark & SQL
I was going crazy on this. I think I need a vacation
"Mark Williams" wrote:
> Change
> DECLARE @.Factor decimal
> to
> DECLARE @.Factor decimal(5,2)
> "Ram" wrote:
>

Help with CURRENT DATE Query

Hello, I have a table that lists my bank transactions. I have a uniqueid in
the first field and several other fields...one of which is DATE... now, I
want to run a query where my view is based upon the DATE. however I want to
only show transactions that are the same date as the system date (this is to
see how much work my employees have done). Please see my example below, this
code works...
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE ([date] = '9/9/2007')
However, where it has 9/9/2007, I want it to be TODAYS Date
Thanks for your help in advance!WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
"SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> Hello, I have a table that lists my bank transactions. I have a uniqueid
> in
> the first field and several other fields...one of which is DATE... now,
> I
> want to run a query where my view is based upon the DATE. however I want
> to
> only show transactions that are the same date as the system date (this is
> to
> see how much work my employees have done). Please see my example below,
> this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Try:
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE
[date] >= convert(char(8), getdate(), 112) and
and [date] < dateadd(day, 1, convert(char(8), getdate(), 112))
go
AMB
"SQL Brad" wrote:
> Hello, I have a table that lists my bank transactions. I have a uniqueid in
> the first field and several other fields...one of which is DATE... now, I
> want to run a query where my view is based upon the DATE. however I want to
> only show transactions that are the same date as the system date (this is to
> see how much work my employees have done). Please see my example below, this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Aaron...thanks for your help, it worked perfectly!! I also changed the 0 to
a 1 and it went to yesterday....very much appreciated!
"Aaron Bertrand [SQL Server MVP]" wrote:
> WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
>
> "SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
> news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> > Hello, I have a table that lists my bank transactions. I have a uniqueid
> > in
> > the first field and several other fields...one of which is DATE... now,
> > I
> > want to run a query where my view is based upon the DATE. however I want
> > to
> > only show transactions that are the same date as the system date (this is
> > to
> > see how much work my employees have done). Please see my example below,
> > this
> > code works...
> >
> > SELECT transid, [date], description, amt, taxamt
> > FROM dbo.taxtransactions
> > WHERE ([date] = '9/9/2007')
> >
> > However, where it has 9/9/2007, I want it to be TODAYS Date
> >
> > Thanks for your help in advance!
> >
>
>sql

Help with crosstab (was "Query Help Needed!")

Hey,

i have a table which has the foll data:

employeecode Amount AmountDescription
1 100 x
2 200 y
3 150 x
4 300 z

now i need to fetch this data such that i can display the output as :

empcode x y z
1 100
2 200
3 150
4 300

any suggestions???

platform: SQL Server 2000

thanx!sorry, no suggestions.... unless we know

1) why do u need it (the practical scenario)
2) how do u ensure that the string "x" fits a column name
3) how do u ensure that the number of columns is within the max limit for select/table
4) what would be the value against row 1, col x of the output|||I think this will do what you want.

SELECT empcode
, Max(CASE WHEN AmountDescription = 'x' THEN amount ELSE 0 END) As 'x'
, Max(CASE WHEN AmountDescription = 'y' THEN amount ELSE 0 END) As 'y'
, Max(CASE WHEN AmountDescription = 'z' THEN amount ELSE 0 END) As 'z'
FROM MyTable
GROUP BY empcode|||no, george, that will put 0s where they didn't exist in the data|||just tweak that last response to use null values instead of 0's

select empcode, case when AmountDescription = 'x' then amount else null end as X,
Case when AmountDescription = 'y' then amount else null end as Y,
Case when AmountDescription = 'z' then amount else null end as Z
from MyTable
Group by Empcode|||but don't lose your MAXes ;)|||Max means that it becomes aggregated and doesn't have to be used in the GROUP BY clause ;)|||i need to fetch this data such that i can display the output as :
empcode x y z
1 100
2 200
3 150
4 300
SELECT empcode
, Amount
, x = NULL
, y = NULL
, z = NULL
FROM MyTable


:)|||<sigh />

pootle, please forgive the lack of proper spacing in the original post

this is what was intended (and you can see this if you open up the original post in Edit) --

empcode x y z
1 100
2 200
3 150
4 300sql

Help with cross products and functions

Hello,
I have two tables that i need to join. The first table has a column which
is an encoded addition of one or more values in a second table.
table 1:
col1 col2 col3
1 a b 12
...
table 2:
col1 col2
1 x 4
2 y 8
3 z 16
...
the record for table 1, maps to records 1 and 2 from table 2. I know that
the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of this
math function that decodes the encryption.
I have a function that is able to return a table that lists all the values
in table 2 that correspond to the supplied value in table 1..ie if i call
function fn_mappings(12) i get a table called @.result:
table2value
1 4
2 8
what i need to do is then combine these records together to get a view as
follows
col1 col2 table2value
1 a b 4
2 a b 8
...
is this possible to do? i need this to happen for EACH row in table 1 to
find corresponding records in table 2.
thanks for any and all help!
BenIn order to link both tables, I took the liberty of "re-create" a convenient
environment to get the results you asked. I hope it is ok.
Let me know if it works for you
-- Begin Script
create table tbl1
( id int primary key
, col1 varchar(10)
, col2 varchar(10)
, col3 int
)
create table tbl2
(
id int primary key
, fkid int
, col1 varchar(10)
, col2 int
)
insert into tbl1
values (1, 'a', 'b', 12)
insert into tbl2
values (1, 1, 'x', 4)
insert into tbl2
values (2, 1, 'x', 8)
insert into tbl2
values (3, 2, 'x', 16)
-- View the output of both tables
select * from tbl1
select * from tbl2
go
create function dbo.fn_mappings(@.in int)
returns table
as
return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
col3 = @.in))
go
-- View the dbo.fn_mappings() output
select * from dbo.fn_mappings(12)
-- Output requested
select t1.id
, t1.col1
, t1.col2
, t2.col2
from tbl1 t1
inner join tbl2 t2
on t1.id = t2.fkid
-- Drop all objects
drop function dbo.fn_mappings
drop table tbl1
drop table tbl2
"Ben" wrote:

> Hello,
> I have two tables that i need to join. The first table has a column which
> is an encoded addition of one or more values in a second table.
> table 1:
> col1 col2 col3
> 1 a b 12
> ...
> table 2:
> col1 col2
> 1 x 4
> 2 y 8
> 3 z 16
> ...
> the record for table 1, maps to records 1 and 2 from table 2. I know that
> the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of thi
s
> math function that decodes the encryption.
> I have a function that is able to return a table that lists all the values
> in table 2 that correspond to the supplied value in table 1..ie if i call
> function fn_mappings(12) i get a table called @.result:
> table2value
> 1 4
> 2 8
> what i need to do is then combine these records together to get a view as
> follows
> col1 col2 table2value
> 1 a b 4
> 2 a b 8
> ...
> is this possible to do? i need this to happen for EACH row in table 1 to
> find corresponding records in table 2.
> thanks for any and all help!
> Ben|||Edgardo,
Thank you for your response...however, I do not have the liberty of changing
table2's structure. The data is provided by an external source and I am
creating custom reporting for it. Therefor i cant give it a foreign key typ
e
column.
do you have any other ideas?
Thanks again
Ben
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In order to link both tables, I took the liberty of "re-create" a convenie
nt
> environment to get the results you asked. I hope it is ok.
> Let me know if it works for you
> -- Begin Script
> create table tbl1
> ( id int primary key
> , col1 varchar(10)
> , col2 varchar(10)
> , col3 int
> )
> create table tbl2
> (
> id int primary key
> , fkid int
> , col1 varchar(10)
> , col2 int
> )
> insert into tbl1
> values (1, 'a', 'b', 12)
> insert into tbl2
> values (1, 1, 'x', 4)
> insert into tbl2
> values (2, 1, 'x', 8)
> insert into tbl2
> values (3, 2, 'x', 16)
> -- View the output of both tables
> select * from tbl1
> select * from tbl2
> go
> create function dbo.fn_mappings(@.in int)
> returns table
> as
> return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
> col3 = @.in))
> go
> -- View the dbo.fn_mappings() output
> select * from dbo.fn_mappings(12)
> -- Output requested
> select t1.id
> , t1.col1
> , t1.col2
> , t2.col2
> from tbl1 t1
> inner join tbl2 t2
> on t1.id = t2.fkid
> -- Drop all objects
> drop function dbo.fn_mappings
> drop table tbl1
> drop table tbl2
>
> "Ben" wrote:
>

Help with creating SQL Statement to get data from single table...

Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table:

I have the following table of data

__User___Votes___Month

__A_______14______2
__A_______12______3
__A_______17______4
__A_______11______5

__B_______19______2
__B_______12______3
__B_______15______4

I want to beable to pull out the total number of votes a user has had over a period of months.

eg Total up each users users votes for months 4 and 5

that would give:

__User____TotalVotes

___A________28
___B________15

An added complecation is that user B does not have any data for month 5

Any help or pointers would be fanstatic

Many thanks

select sum(Votes)
from table
where month in (4, 5)
group by User|||Of cource! thank you.|||rather,
select
[user], sum(Votes) as TotalVotes
from
yourTable
where
month in (4, 5)
group
by [User]

Help with counting query

This is a counting issue following an inner join.
I have two tables (simplifying it for clarity)
Offering Table: OfferingID, year
Registration Table: RegistrationID, OfferingID, Registration_Status
-- Start Table Definitions
--
CREATE TABLE [dbo].[Test1] (
[OfferingID] [int] NOT NULL ,
[Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[test2] (
[RegistrationID] [int]NOT NULL ,
[OfferingID] [int] NOT NULL ,
[Registration_Status] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Test1 Values (1,2005)
INSERT INTO Test1 Values (2,2005)
INSERT INTO Test1 Values (3,2006)
INSERT INTO Test2 Values (1,1,1)
INSERT INTO Test2 Values (2,1,1)
INSERT INTO Test2 Values (3,1,2)
INSERT INTO Test2 Values (4,1,2)
INSERT INTO Test2 Values (5,1,2)
INSERT INTO Test2 Values (6,2,1)
INSERT INTO Test2 Values (7,2,1)
INSERT INTO Test2 Values (8,3,1)
INSERT INTO Test2 Values (9,3,2)
-- END Table Definitions
--
Now I want to do some counting, basically I want to count the number of
offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
I also want to count the total number of registrations of
Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 and
4
respectivly)
This issue is I want them all in a single record. I came up with this SQL
Query
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Returns:
Attended Cancelled 2005 2006
-- -- -- --
5 4 7 2
It returns the correct Registration Status counts, but not the correct
number of offerings per year because the records are counted over and over
due to the join. What I really want is to count only distinct records in
test1.
Attended Cancelled 2005 2006
-- -- -- --
5 4 2 1
Thanks!You need to use COUNT(distinct).
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
THEN t1.OfferingID ELSE NULL END) AS [2005],
COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
THEN t1.OfferingID ELSE NULL END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Note that the ELSE NULL is optional; if there is no ELSE clause the
CASE defaults to NULL when not matched. But it is a bit clearer with
the explicit assignment. COUNT does not count NULLs.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 11:05:02 -0700, Ramez
<Ramez@.discussions.microsoft.com> wrote:

>Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
>Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
>Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005]
,
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
>FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID|||Actually you don't need a join.
try this
select * from
(Select SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled from test2) as t2,
(select SUM(CASE WHEN Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
from test1) t1
Hope this helps.
--
"Ramez" wrote:

> This is a counting issue following an inner join.
> I have two tables (simplifying it for clarity)
> Offering Table: OfferingID, year
> Registration Table: RegistrationID, OfferingID, Registration_Status
> -- Start Table Definitions
> --
> CREATE TABLE [dbo].[Test1] (
> [OfferingID] [int] NOT NULL ,
> [Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[test2] (
> [RegistrationID] [int]NOT NULL ,
> [OfferingID] [int] NOT NULL ,
> [Registration_Status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Test1 Values (1,2005)
> INSERT INTO Test1 Values (2,2005)
> INSERT INTO Test1 Values (3,2006)
> INSERT INTO Test2 Values (1,1,1)
> INSERT INTO Test2 Values (2,1,1)
> INSERT INTO Test2 Values (3,1,2)
> INSERT INTO Test2 Values (4,1,2)
> INSERT INTO Test2 Values (5,1,2)
> INSERT INTO Test2 Values (6,2,1)
> INSERT INTO Test2 Values (7,2,1)
> INSERT INTO Test2 Values (8,3,1)
> INSERT INTO Test2 Values (9,3,2)
> -- END Table Definitions
> --
> Now I want to do some counting, basically I want to count the number of
> offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
> I also want to count the total number of registrations of
> Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 an
d 4
> respectivly)
> This issue is I want them all in a single record. I came up with this SQL
> Query
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005
],
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006
]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Returns:
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 7 2
> It returns the correct Registration Status counts, but not the correct
> number of offerings per year because the records are counted over and over
> due to the join. What I really want is to count only distinct records in
> test1.
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 2 1
> Thanks!|||Hello, Ramez
To get the desired result, you can simply use something like this:
SELECT
(SELECT COUNT(*) FROM test2 WHERE Registration_status=1) AS Attended,
(SELECT COUNT(*) FROM test2 WHERE Registration_status=2) AS Cancelled,
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2005) AS [2005],
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2006) AS [2006]
If you really want to use a join (but I don't see any good reason for
this), you can use the following query:
SELECT
SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS Cancelled,
COUNT(DISTINCT CASE WHEN Offering_Year = '2005' THEN t1.OfferingID
END) AS [2005],
COUNT(DISTINCT CASE WHEN Offering_Year = '2006' THEN t1.OfferingID
END) AS [2006]
FROM test1 t1 INNER JOIN test2 t2 ON t1.OfferingId = t2.OfferingID
However, this comes at the expense of a warning: "Warning: Null value
is eliminated by an aggregate or other SET operation."; the warning can
be eliminated by using SET ANSI_WARNINGS OFF, but this is not
recommended (one of the reasons is because ANSI_WARNINGS is required to
be ON for using indexes on computed columns and indexed views).
Razvan|||Roy,
Do we need the join and case and distinct ops for this scenario. I
seriously doubt it.
--
"Roy Harvey" wrote:

> You need to use COUNT(distinct).
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
> THEN t1.OfferingID ELSE NULL END) AS [2005],
> COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
> THEN t1.OfferingID ELSE NULL END) AS [2006]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Note that the ELSE NULL is optional; if there is no ELSE clause the
> CASE defaults to NULL when not matched. But it is a bit clearer with
> the explicit assignment. COUNT does not count NULLs.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 11:05:02 -0700, Ramez
> <Ramez@.discussions.microsoft.com> wrote:
>
>

Help with count the registers

Hi
I have a table Cuenta
uno dos tres
-- -- --
dos dosjaja dos jeje
tres tresjaja tres jeje
uno unojaja uno jeje
and i want this:
uno
-- --
dos 1
tres 2
uno 3
count the registers, but no with count function.
thanks
CesarCesar,
What are you asking for? Row numbers?
HTH
Jerry
"Cesar" <hgfdhfg@.jksjn.com> wrote in message
news:ui%238KdY1FHA.2132@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have a table Cuenta
> uno dos tres
> -- -- --
> dos dosjaja dos jeje
> tres tresjaja tres jeje
> uno unojaja uno jeje
> and i want this:
> uno
> -- --
> dos 1
> tres 2
> uno 3
> count the registers, but no with count function.
> thanks
> Cesar
>

Help with Count

Hi all!

I have a table that has Order Number and an Account Number, I want to count all the account numbers. However there might be more then one order number with the same account number...

See example:

Table:

[Order Number] [Account Number]
12312 1234
13231 2342
14352 1311
23423 1313
11422 1234

Output should be: 4

I want to count([account number]) and get 4, notice there are 5 rows, the 1st and last have the same account number.

How do I write this query?

Thanks,

KenFound the anwser I was looking for!

select count(distinct [account number]) from table|||select count(distinct [Account Number])
from tbl|||Good timing! I found the answer just as you posted!

Thanks for the reply!

Ken

Help with constraint

Hi,
I have a table where I want a certain condition with 3 of
its fields. I want only 1 of them not to be null.
For example, if the fields are A,B,C these combinations are ok:
A B C
NULL 3432 NULL
NULL NULL 554
333 NULL NULL
Howere these are not:
A B C
NULL NULL NULL
NULL 5454 554
333 545 6858
I'm not very familiar with the syntax of constraints, and I did this:
check(
case when A is null then 0 else 1 end+
case when B is null then 0 else 1 end+
case when C is null then 0 else 1 end
=1)
but I get a syntax error.
Any ideas?
Thanks!You could do something like this.
check(
A IS NOT NULL
OR B IS NOT NULL
OR C IS NOT NULL)
"Star" wrote:

> Hi,
> I have a table where I want a certain condition with 3 of
> its fields. I want only 1 of them not to be null.
> For example, if the fields are A,B,C these combinations are ok:
> A B C
> NULL 3432 NULL
> NULL NULL 554
> 333 NULL NULL
> Howere these are not:
> A B C
> NULL NULL NULL
> NULL 5454 554
> 333 545 6858
> I'm not very familiar with the syntax of constraints, and I did this:
> check(
> case when A is null then 0 else 1 end+
> case when B is null then 0 else 1 end+
> case when C is null then 0 else 1 end
> =1)
> but I get a syntax error.
> Any ideas?
> Thanks!
>|||That doesn′t work but I appreciate your help.
Even with that condition I can have something like this:
A B C
NULL 5454 554
I want only one of them to be populated.
Patrik wrote:
> You could do something like this.
> check(
> A IS NOT NULL
> OR B IS NOT NULL
> OR C IS NOT NULL)
>
> "Star" wrote:
>|||drop table aa
go
create table aa(a int, b int, c int)
go
alter table aa add constraint chk_abc check(
(a is null and b is null and c is not null) or
(a is null and b is not null and c is null) or
(a is not null and b is null and c is null)
)
go
insert into aa values(1,null,null)
insert into aa values(null,1,null)
insert into aa values(null,null,1)
insert into aa values(1,1,null)
insert into aa values(null,1,1)
insert into aa values(1,null,1)
insert into aa values(1,1,1)
insert into aa values(null,null,null)
go
select * from aa|||I think I got it to work.
Just in case someone is interested:
(case when ([A] is null) then 0 else 1 end + case when ([B] is null)
then 0 else 1 end + case when ([C] is null) then 0 else 1 end = 1)|||Ok, misunderstood you.
Try this
check(
(case isnull(a,-1) when -1 then 0 else 1 end + case isnull(b,-1) when -1
then 0 else 1 end + case isnull(c,-1) when -1 then 0 else 1 end) = 1
)
"Star" wrote:

> That doesn′t work but I appreciate your help.
> Even with that condition I can have something like this:
> A B C
> NULL 5454 554
> I want only one of them to be populated.
>
>
> Patrik wrote:
>|||drop table aa
go
create table aa(a int, b int, c int)
go
alter table aa add constraint chk_abc check(
(a is null and b is null and c is not null) or
(a is null and b is not null and c is null) or
(a is not null and b is null and c is null)
)
go
insert into aa values(1,null,null)
insert into aa values(null,1,null)
insert into aa values(null,null,1)
insert into aa values(1,1,null)
insert into aa values(null,1,1)
insert into aa values(1,null,1)
insert into aa values(1,1,1)
insert into aa values(null,null,null)
go
select * from aa|||On Thu, 26 Jan 2006 13:51:41 -0500, Star wrote:

>Hi,
>I have a table where I want a certain condition with 3 of
>its fields. I want only 1 of them not to be null.
>For example, if the fields are A,B,C these combinations are ok:
>A B C
>NULL 3432 NULL
>NULL NULL 554
>333 NULL NULL
>Howere these are not:
>A B C
>NULL NULL NULL
>NULL 5454 554
>333 545 6858
>I'm not very familiar with the syntax of constraints, and I did this:
>check(
>case when A is null then 0 else 1 end+
>case when B is null then 0 else 1 end+
>case when C is null then 0 else 1 end
>=1)
>but I get a syntax error.
>Any ideas?
Hi Star,
Though you've gotten some alternative formulations, I fail to see why
you would have gotten syntax errors. The code below runs fine for me:
CREATE TABLE Star(A int, B int, C int,
check(case when A is null then 0 else 1 end +
case when B is null then 0 else 1 end +
case when C is null then 0 else 1 end = 1)
)
go
-- Accepted
INSERT INTO Star (A, B, C)
select 1, null, null
union all
select null, 1, null
union all
select null, null, 1
-- Rejected
INSERT INTO Star (A, B, C)
SELECT 1, 1, null
INSERT INTO Star (A, B, C)
SELECT null, null, null
INSERT INTO Star (A, B, C)
SELECT 1, 1, 1
-- Show results
select * from Star
go
DROP TABLE Star
go
Hugo Kornelis, SQL Server MVP|||>From the samp[le data, it looks like they are all positive integers, so
we can use this trick:
CHECK ( COALESCE (SIGN(a), 0)
+ COALESCE (SIGN(b), 0)
+ COALESCE (SIGN(c), 0) = 1)
if you have negative numbers use SIGN (ABS(x)) and if you have zeroes,
use SIGN (ABS(x+1))|||usually problems like this arise when mutually exclusive subtypes are
stored in one table. Is that the case? Are you considering splitting up
the table?

Help with complicated query...

Hello All,
I have a simple table that stores messages of different types from
different sources. The definition of the table is shown below. I need
to devise an efficient query to return a "list of the N more recent
messages for a subset of sources within a specified time frame."
create table MessageTbl
(
src nvarchar(50), -- Source of the message
type nvarchar(50), -- Type of the message.
msg nvarchar(1000), -- Text of the message
dt datetime -- When the message was posted
)
We are given the following parameters:
1. declare @.startTime datetime -- Start of the time frame
2. declare @.endTime datetime -- End of the time frame
3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
contains a list of sources/types for which we want to obtain the
messages.
4. N -- How many messages per source/type
If all I wanted was the 20 more recent messages for source1/type1
between @.startTime and @.endTime, I could do something like:
SELECT TOP 20 *
FROM MessageTbl
WHERE (src ='source1') AND ( type='type1')
AND ( dt BETWEEN @.startTime AND @.endTime )
ORDER BY dt DESC
In my case, however, I have a number of pairs (src,type) in the local
table @.myTable. Therefore, what I ultimately want is equivalent to the
UNION of the results of such query for each pair(src,type). Another
thing missing is that I am using a hardcoded value for the TOP clause.
This also varies.
All the solutions that I can think of are very inneficient, cumbersome,
and involve a number of temporary tables. I was wondering if the
experts could lead me to a cleaner query design.
Thank you
- CDOn 25 Oct 2005 14:57:37 -0700, crbd98@.yahoo.com wrote:

>Hello All,
>I have a simple table that stores messages of different types from
>different sources. The definition of the table is shown below. I need
>to devise an efficient query to return a "list of the N more recent
>messages for a subset of sources within a specified time frame."
>create table MessageTbl
>(
> src nvarchar(50), -- Source of the message
> type nvarchar(50), -- Type of the message.
> msg nvarchar(1000), -- Text of the message
> dt datetime -- When the message was posted
> )
>We are given the following parameters:
>1. declare @.startTime datetime -- Start of the time frame
>2. declare @.endTime datetime -- End of the time frame
>3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
>contains a list of sources/types for which we want to obtain the
>messages.
>4. N -- How many messages per source/type
>
>If all I wanted was the 20 more recent messages for source1/type1
>between @.startTime and @.endTime, I could do something like:
>SELECT TOP 20 *
>FROM MessageTbl
>WHERE (src ='source1') AND ( type='type1')
> AND ( dt BETWEEN @.startTime AND @.endTime )
>ORDER BY dt DESC
>In my case, however, I have a number of pairs (src,type) in the local
>table @.myTable. Therefore, what I ultimately want is equivalent to the
>UNION of the results of such query for each pair(src,type). Another
>thing missing is that I am using a hardcoded value for the TOP clause.
>This also varies.
>All the solutions that I can think of are very inneficient, cumbersome,
>and involve a number of temporary tables. I was wondering if the
>experts could lead me to a cleaner query design.
>Thank you
>- CD
Hi CD,
Try if this works:
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your reply. The query that you posted produces the
correct result but it is very expensive. It takes almost 30 seconds
when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
the execution plan and it seems there is a lot of table scanning going
on.
Maybe I have to look into a solution that does not use the join...
Any Ideas?
Thanks
- CD|||Are there any indexes? Your original DDL had no keys and you used a
proprietary table variable.
Also, you might want to fix the data element names. They are horrible.|||Hello Celko,
The table MessageTbl has a compound primary key involving src and type.
There are no other indexes. My local table variable has no keys and no
indexes. Do you think this is a problem? Is there any problem in using
table variables or are you just concerned about the portability of the
code?
Any suggestions?
Thank you
CD
--CELKO-- wrote:
> Are there any indexes? Your original DDL had no keys and you used a
> proprietary table variable.
> Also, you might want to fix the data element names. They are horrible.|||On 25 Oct 2005 16:22:34 -0700, crbd98@.yahoo.com wrote:

>Hello Hugo,
>Thank you for your reply. The query that you posted produces the
>correct result but it is very expensive. It takes almost 30 seconds
>when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
>the execution plan and it seems there is a lot of table scanning going
>on.
>Maybe I have to look into a solution that does not use the join...
>
>Any Ideas?
Hi CD,
Try if adding this index helps:
CREATE INDEX ProperNameHere
ON MessageTbl (src, type, dt DESC)
On 26 Oct 2005 10:50:55 -0700, crbd98@.yahoo.com wrote:

>Hello Celko,
>The table MessageTbl has a compound primary key involving src and type.
Huh? If there's a compount primary key on src and type, then how can you
find the 20 most recent messages between two moments for a given src and
type? As a result of the primary key, there will be only one message for
each src / type combination!!
But since you apparently have a PRIMARKY KEY that was not included in
your first post, please post the complete CREATE TABLE statement, WITH
all constraints, properties and indexes. My suggestion above might well
be invalidated by your current keys and indexes.

>There are no other indexes. My local table variable has no keys and no
>indexes. Do you think this is a problem?
You might have duplicates in the table variable, which will never
improve performance.
You can't define indexes for a table variable, but you can define
PRIMARY KEY or UNIQUE constraints (and they DO automatically add an
index). In your case, try if adding a PRIMARY KEY (src, type) helps the
performance. And if it doesn't, but doesn't hinder performance either,
then do leave it in.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for the suggestion of creating an index with the src, type,
and dt. That reduced the execution time from 30 sec to less than a
second. My last problem was to parameterize the value for TOP clause. I
created a huge dynamic SQL statement that I use with sp_executesql.
VERY UGLY!!! Do you have any alternative suggestion?
Thanks
Cassiano|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:

> Hello Hugo,
> Thank you for the suggestion of creating an index with the src, type,
> and dt. That reduced the execution time from 30 sec to less than a
> second. My last problem was to parameterize the value for TOP clause. I
> created a huge dynamic SQL statement that I use with sp_executesql.
> VERY UGLY!!! Do you have any alternative suggestion?
> Thanks
> Cassiano
Issue a SET ROWCOUNT in the stored procedure:
CREATE PROCEDURE foo (@.nRows int, @.bar varchar(30))
AS
SET ROWCOUNT @.nRows
SELECT * FROM sysobjects where name <> @.bar
SET ROWCOUNT 0
GO
SQL Server specific, but it works, and there's no dynamic SQL or even
recompilation.|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:

>Hello Hugo,
>Thank you for the suggestion of creating an index with the src, type,
>and dt. That reduced the execution time from 30 sec to less than a
>second. My last problem was to parameterize the value for TOP clause. I
>created a huge dynamic SQL statement that I use with sp_executesql.
>VERY UGLY!!! Do you have any alternative suggestion?
>Thanks
>Cassiano
Hi Cassiano,
The suggestion made by Ross (SET ROWCOUNT) is fine if you wwant to limit
the total number of rows returned by the query. But I seem to recall
that your problem was more complex than that.
Going back in the thread, I see this query I posted a few days ago - is
this the one you are using, and where you want to replace TOP 20 with a
variable number?
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
The easiest answer is to wait a few ws. TOP @.variable will be
suppported in SQL Server 2000, which will hit the streets in the w of
November 7th.
Or use the following (which is ANSI standard to boot):
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND (SELECT COUNT(*)
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
AND c.dt <= a.dt) <= 20
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you very much for your reply. This whole discussion has been very
enlightening.
I compared the performance of two variations of the solution:
#1. The last solution that you suggested.
#2. Based on your original solution (the one with hardcoded TOP
clause). The only modification that I made was to create the query
string dynamically to simmulate the effect of a variable TOP clause.
The table has the following indices (in addition to a PK index on a
MsgId column that I did not include in the posting).
CREATE INDEX IX_MessageTbl1
ON MessageTbl (src, type, dt DESC)
and
CREATE INDEX IX_MessageTbl2
ON MessageTbl (dt DESC)
I noticed that #1 was 6 times slower than #2. Although #2 is faster, I
do not like it, because I create the query dynamically. Do you know if
there is any index that I can create or any hint that I can use to
speed-up the query #1.
Thank you
- CDsql

help with complex SQL query

I need some help from prog. gurus with sql query.

I have a commodity table and I would like to pull information from it based on the conditions. I can write code to pull straight forward information from the table but I need help in writing query such that the later columns are based on the former columns.

To clear my question, I am writing the sample code below.
The column 4, 5 and 6 are based on column 1, 2, 3.
Column6 is column3/column2 (which is simple)
But column4 & 5 are too complex for me to write the code for. Column4 is the % of Quantity (cty_code)/(all countries) for that particular commodity. cty_code correspends to a country.

Table information:

[dbo].[2005exp](
[dom_or_for] [char](1) NOT NULL,
[commodity] [char](10) NOT NULL,
[cty_code] [char](4) NOT NULL,
[district] [char](2) NOT NULL,
[stat_month] [char](2) NOT NULL,
[cards_mo] [decimal](8, 0) NOT NULL,
[all_qy1_mo] [decimal](12, 0) NOT NULL,
[all_val_mo] [decimal](12, 0) NOT NULL
)

---

Table data snapshot:

Query1:
select top 10 commodity, sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp]
where cty_code=5310 group by Commodity order by price desc

Output:

Commodity	Quantity	Price	Column4	Column5	Price/Quantity
8517305000	0	46307629
8517905000	0	11990255
3003100000	2268	2687905	0.35%	29.92%	1185.1437
8524990000	148	1815000
8471300000	2591	1673570
9802400000	0	1560247
9880004000	0	1197407
8802300080	1	1100000
3819000000	601192	899417
9802200000	0	811996

Query2: (based on the results of query 1 i.e., commodity)
select Sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp]
where commodity=3003100000

Output:

Quantity	Price
645261	8982928

2268 / 645261 = 0.35% Column 4

2687905 / 8982928 = 29.92% Column 5

Quantity/Price = Average (2687906/2268 = 1185.1437) i.e. column3/column2=Column6

The final output should like: (There are additional 2 columns in the table below but they are not required)

select a.commodity, sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as Price,

CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b

where a.commodity=b.commodity)<>0 THEN (sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b

where a.commodity=b.commodity))*100.00 ELSE NULL END as col4, CASE WHEN (select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as col5,

CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as col6

from [2005exp] a

WHERE a.cty_code=5310

GROUP BY a.commodity

|||

Thank you so much. The code works perfectly. I really appreciate all your help.

I wanted to add another column 7 which is column5/column4. How can I save the column5 and Column4 values in 2 different variables and then perform the division ?

I tried to use just the alias names i.e col5/col4, but its not working.

I tried using the actual code for both columns and its working but its taking 4 minutes to execute the query. Its repeating the same steps twice. How is it done right way ?

Also, I was trying to delcare a variable for the table name but without any success. Who is it done ?

Use test
Go

Declare @.Country Int
Set @.Country=5310

select top 10 a.commodity as Commodity, c.descrip_1 as Description, c.quantity_1 as Unit,

sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as [Value],

CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S. Share(Quantity) %],

CASE WHEN (select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S. Share(Value) %],

CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as [Average Price],

CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b where a.commodity=b.commodity)<>0 THEN
(((sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)))/((sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b where a.commodity=b.commodity)))) ELSE NULL END as [Price Ratio]

from [2005exp] a inner join concord c on a.commodity=c.commodity

WHERE a.cty_code=@.Country
GROUP BY a.commodity, c.descrip_1, c.quantity_1 order by [Value] desc

Help with complex query

Hi Everyone,

I need some help writing a query that joins a table to a UNION query. I was wondering what is the most efficent way to do this.

Tables

Employees (EMPID, FULLNAME)

DailySchedules (SCHID,EMPID, SCHDATE,DEPTID)

GeneralSegments (GSID,EMPID,STARTTIME,STOPTIME)

DetailSegments (DSID,EMPID,STARTTIME,STOPTIME)

I need to join the records from GeneralSegments and DetailSegments THEN inner join DailySchedules and Employees.

Query must output:

EMPID, FULLNAME, SCHDATE,STARTTIME,STOPDTIME

Thank You

Can you provide some sample data (preferrably in the form of insert statements) along with a data representation of what you want for output?

Thanks.

|||

Untested, but, it should give you an idea

Code Snippet

SELECT Segments.EMPID,
Employees.FULLNAME,
DailySchedules.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME

FROM

(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID

|||

Hi David,

That example will work. One question though, I have to display the next 14 days (2 weeks) on the web. Where should I place my WHERE clause? In the outer query or include the date range in both union statements?

ie. WHERE SCHDATE BETWEEN GETDATE() AND GETDATE() + 14

Thank You

|||

Is SCHDATE column present in the tables in the DailySchedule table or the segments tables? In any case, it doesn't matter. SQL Server will automatically roll the schdate predicate into the inner queries as well based on their reference. For example, if you want to filter on STARTTIME then you can include just one WHERE clause like below and it will be applied to both GeneralSegments & DetailSegments

Code Snippet

SELECT Segments.EMPID,
Employees.FULLNAME,
d.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME

FROM

(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID
WHERE Segments.STARTTIME >= @.Start

sql

Help with column Insert

Hi,

I have some values I want put into a table, but the values are from other sources and I dont know how to retrieve them..

I'll show my code, and the bold is explaining what I want inserted and where from. I'd apprechiate if someone could help me with syntax etc. There are 2 about getting value from another table and one about just putting in straight forward text..:

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

Thanks alot if anyone can help me with those three things..

Jon

If 10 people are to receive the message, are there 10 message records or one?

If one, how are the 10 usernames supposed to be formatted? comma-separated values, separated by semi-colons?

|||

I was hoping to have 1 record, with a large list of usernames. I assume it will work if they are seperated by anything, but with a space - peoples messages are called up if the recievername column has their username in it. Will it work if it also has other writing (i.e. other peoples usernames)?

Thanks

Jon

|||

jbear123:

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

I just took the time toreally pay attention to what you are doing in business terms instead of just the coding issues.

This is a collection's application? If you sent out a message with my name on it, telling hundreds of other people I hadn't paid up, I would be really angry.

Angry enough to tell you impolite things and never do business with you again.

Depending upon what country you live in or are sending the messages to, it might even be illegal. Particularly if you made a mistake and they did not owe you anything - that would be libel under US law.

That said, I have one other question. The payment instructions are tied to the user who is logged into the page, not the user receiving the message? That was a bit surprising. Or are they tied to the user receiving the message?

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

|||

david wendelken:

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?

Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.

Thanks for your help!

Jon

|||

david wendelken:

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?

Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.

Thanks for your help!

Jon

|||

You already know how to issue a sql command via the SqlCommand object. Instead of an update command, you need to issue a query:

My guess as to your query would be "select distinct usersname from transactions where itemid = @.itemid"

You will have to pass in the itemid as a parameter, and issue an ExecuteQuery instead of an ExecuteNonQuery. You will be putting the results of the query into a DataReader and looping thru it. Just google or look it up any pretty much any asp book. This is basic, beginner level stuff and it's well documented all over the place - so I'm not going to do it again. :)

Inside the DataReader loop, you can concatenate the usernames you return into a string. It's best to use the StringBuilder class when you are looping. Again, google or look up StringBuilder in the manual.

I think you would be better served slogging thru this step yourself rather than having someone hand it to you. You'll learn it better, and this is bread-and-butter code that you'll use all the time, so it needs to be second nature. I'll keep an eye out on this thread in case you get stuck someplace.

|||

Hi,

I think I have the idea - could you confirm this for me:

I keep the insert commands, but before them I create a command to select all the info that I want inserted (from different tables etc), then using the insert command reference the results of the select command info?

If thats not what yopu meant, would that work anyway?

Thanks,

Jon

|||

I hope it will work as I have done it now..I created datalists showing the data I want to be inserted.

The only thing I cant do is link the results of the datalist (where i selected the data I want(visible=false)) etc to the add parameter section.

E.g:

command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.recievername",how do I link to results of the datalist here? I'd have to have a comma in between each result);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());

Please could you briefly explain, as soon as I know that I can apply it to problemsall over the page and my site will be done!

Thanks alot

Jon

|||

Before you get to this point, create a string to hold the receivername values.

Loop thru the datalist, and for each entry in the datalist, append the next receiver.

|||

I'll give it a go. Just briefly before I leave you alone for a while (sorry!)-

If I create a string, how do I do that for many values (i.e. many usernames),

By loop thru, you mean just use find the string on the datalist?

And what do you mean by append the next receiver? use: + 'next receiver'?

Cheers,

Jon

|||

Hi,

I have:

SqlCommand command = new SqlCommand();
command.Connection = con;

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";
command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
DataView dv = SqlDataSource2.Select(DataSourceSelectArguments.Empty) as DataView;
string receivername = dv[0]["receivername"].ToString();
command.Parameters.AddWithValue("@.recievername", receivername);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

con.Close();
command.Dispose();
return true;

so far. How can I make values seperate by commas?

Thanks,

Jon

sql

help with clr trigger

hi all.

i'm writing a simple clr trigger.

the trigger reads values from the INSERTED table.

and i use try - catch statement.

in the catch () i want to inesrt a new row to my Logtbl Table.

how can i insert row to a table in another tables trigger?

can you please give me a simple example?

thanks.

How about: "insert into Logtbl values(your values)" as a SqlCommand.

Niels
|||

should i use the same connection object for this sqlCommand?

|||Sure.

Niels

help with Check contraints

I need to add a check contraint to a table in SQL Server 2000 that would do
the following
if the value of [type] = 1 then [year] cannot be null
is this possible to do? because for the other values of [type] i want the
[year] to be null
thanks for any help!
benALTER TABLE your_table
ADD CONSTRAINT CK_your_table__type_year_match
CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
Jacco Schalkwijk
SQL Server MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>I need to add a check contraint to a table in SQL Server 2000 that would do
> the following
> if the value of [type] = 1 then [year] cannot be null
> is this possible to do? because for the other values of [type] i want the
> [year] to be null
> thanks for any help!
> ben|||Thank you very much. That was exactly what i was looking for
"Jacco Schalkwijk" wrote:

> ALTER TABLE your_table
> ADD CONSTRAINT CK_your_table__type_year_match
> CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>
>|||Sorry, one more question about check contraints.
is it possible to have a contraint that only allowed entries where for every
pair of columns [a] and [b] there is only 1 value in the [type] column? or
can this only be done through stored procedures and functions?
thanks again
"Jacco Schalkwijk" wrote:

> ALTER TABLE your_table
> ADD CONSTRAINT CK_your_table__type_year_match
> CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>
>|||Please explain "only 1 value in the [type] column".
ML|||well, ill have 3 possible valuse for type: 1,2,3. the other columns
category_ID, area_ID are used to specify a location in a grid like system.
i
want there to only be 1 value in the type column for every combination of
category_id and area_id. this will have to be similar to a query on the
entire table to ensure that the pair doesnt have an entry with type = 1 and
a
entry with type = 2 but multiple entries of type=1 is alowed.
i hope that helped a little
"ML" wrote:

> Please explain "only 1 value in the [type] column".
>
> ML|||On Fri, 28 Oct 2005 08:16:08 -0700, Ben <ben_1_ AT hotmail DOT com>
wrote:

>Sorry, one more question about check contraints.
>is it possible to have a contraint that only allowed entries where for ever
y
>pair of columns [a] and [b] there is only 1 value in the [type] column? or
>can this only be done through stored procedures and functions?
Hi Ben,
UNIQUE (a, b, type)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||that is a valid check contraint? i tried that and it was giving me errors.
sql server 2k. also, i dont think that will allow me to have multiple type=
1
values for each a,b pair. I need that to be allowed, but the type values
cannot be different.
"Hugo Kornelis" wrote:

> On Fri, 28 Oct 2005 08:16:08 -0700, Ben <ben_1_ AT hotmail DOT com>
> wrote:
>
> Hi Ben,
> UNIQUE (a, b, type)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||In that case you should have a separate table with (category_ID, area_ID) as
the Primary Key and Type as the other column. Your database is not properly
normalised, and this will cause all kinds of problems.
Feel free to post your table definitions and a description of your business
problem, and people will give you advise on how to improve your database.
In the mean time, if you don't have the scope or authority to make these
changes, you can apply a band aid with an indexed view (untested):
CREATE vw_chk_your_table
WITH SCHEMA_BINDING
AS
SELECT category_ID, area_ID, Type, COUNT_BIG(*) AS cnt
FROM your_table
GROUP BY category_ID, area_ID, Type
GO
CREATE UNIQUE CLUSTERED INDEX ixc_vw_chk_your_table
ON vw_chk_your_table (category_ID, area_ID)
Jacco Schalkwijk
SQL Server MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:45CE651A-D484-4FD1-8230-1D64BA2E34E8@.microsoft.com...
> well, ill have 3 possible valuse for type: 1,2,3. the other columns
> category_ID, area_ID are used to specify a location in a grid like system.
> i
> want there to only be 1 value in the type column for every combination of
> category_id and area_id. this will have to be similar to a query on the
> entire table to ensure that the pair doesnt have an entry with type = 1
> and a
> entry with type = 2 but multiple entries of type=1 is alowed.
> i hope that helped a little
> "ML" wrote:
>|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 52
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newshub.sd
su.edu!newsfeed.news2me.com!newsfeed2.easynews.com!newsfeed1.easynews.com!ea
synews.com!easynews!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-02!sn-post-01!supe
rnews.com!corp.supernews.co
m!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:562181
On Fri, 28 Oct 2005 23:44:03 -0700, Ben <ben_1_ AT hotmail DOT com>
wrote:
(cut topposting)
>"Hugo Kornelis" wrote:
>
(paste topposting)
>that is a valid check contraint? i tried that and it was giving me errors.
>sql server 2k.
Yes, it's valid. What were the errors you got? And what was the exact
text of the complete statement you used it in?

>also, i dont think that will allow me to have multiple type=1
>values for each a,b pair. I need that to be allowed, but the type values
>cannot be different.
Re-reading what you write, I now see I misinterpreted your question. The
asnwer to your original question should have been:
UNIQUE (a, b)
That would allow only 1 value for type for every pair of values for a
and b, as you originally requested. As such, it would NOT allow multiple
type=1 for a a,b pair, since multiple type=1 is incompatible with "only
1 value in the [type] column".
At this point, I have sincere doubts if these columns really should be
combined in the same table at all. But I must also admit that I'm no
longer sure if I actuallly understand your requirement. It might help if
you posted a few concrete examples of rows of data that can or can not
be in the table at the same time. It would also be a tremendous help if
you could explain the actual business problem that you're trying to
solve.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with CASE in Stored Procedure

Hi there,
I have a table with the following columns:
wgt_id
wgt_lower_weight
wgt_higher_weight
wgt_country
wgt_Parcel_Price
wgt_RMSD_Price
wgt_RMSD_Pre_9_Price
wgt_RMSD_Pre_1_Price
wgt_RMSD_Sat_Price
wgt_Citylink_Price
wgt_Citylink_Sat_Price
I want to create a stored procedure that is passed:
weight
country
shippingtype
This is what I have so far:
SELECT *
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower < @.Weight)
AND (wgt_weight_higher > @.Weight)
I need to add the parameter shippingtype. This will select the
matching column and output the price. I think it needs the use of CASE
but I can't figure it out. Can anyone help me create my store
procedure?
This is what I have tried but its not returning the Price, its
returning all the rows but blank.
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(10))
AS
SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
Price
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO
Any ideas where I am going wrong?
|||(a) never use sp_ prefix on stored procedures.
(b) provide DDL for the tblShippingRates table (another questionable prefix,
btw), some sample data, and desired results. I have no idea what data is in
the table, what parameter values you are passing in, what should be returned
by the query, and what "all the rows but blank" means...
Aaron Bertrand
SQL Server MVP
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186057919.366437.11420@.d55g2000hsg.googlegro ups.com...
> This is what I have tried but its not returning the Price, its
> returning all the rows but blank.
> CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
> @.Weight decimal(12,2), @.Shipping varchar(10))
> AS
> SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
> WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
> WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
> WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
> WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
> WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
> Price
> FROM dbo.tblShippingRates
> WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
> AND (wgt_weight_higher >= @.Weight)
> GO
> Any ideas where I am going wrong?
>
|||Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Pricewgt_Citylink_Sat_Price
00.5uk4.9013.005.608.1010.4025.40
0.511uk6.2015.007.009.5010.4025.40
1.012uk6.7018.709.2011.7010.4025.40
2.014uk9.7023.0010.4025.40
4.016uk23.0010.4025.40
6.018uk23.0010.4025.40
8.0110uk23.0010.4025.40
10.0115uk12.9027.90
15.0120uk15.4030.40
I am passing the stored procedure country = uk weight = 2 and shipping
= RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve
|||Oh, OK. So you'd have a CASE:
CASE @.shipping
WHEN 'RMSD9' then wgt_RMSD_Pre9_Price
WHEN 'RMSD1' then wgt_RMSD_Pre1_Price
...
END
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186062055.667781.242240@.r34g2000hsd.googlegr oups.com...
On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.
|||>I worked it out!
Your procedure suddenly started returning data because you changed AS
ShippingPrice to ShippingPrice = and CASE WHEN @.Shipping = to CASE @.Shipping
WHEN ? That doesn't seem right. Anyway, how about readability? Do you
need to repeat dbo.tblShippingRates 18 times?
CREATE PROCEDURE dbo.usp_GetShippingCharge
@.Country VARCHAR(2),
@.Weight DECIMAL(12,2),
@.Shipping VARCHAR(12)
AS
BEGIN
SET NOCOUNT ON;
SELECT
ShippingPrice = CASE @.Shipping
WHEN 'Parcel' THEN wgt_Parcel_Price
WHEN 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN wgt_Citylink_Price
WHEN 'CitylinkSat' THEN wgt_Citylink_Sat_Price
END
FROM
dbo.tblShippingRates
WHERE
wgt_country = @.Country
AND (@.Weight BETWEEN wgt_weight_lower AND wgt_weight_higher);
END
GO
Finally, since this will only ever return one column and *should* only be
returning one row, why not make it a scalar function, or at least capture
the data via an output parameter?
Aaron Bertrand
SQL Server MVP
|||On Thu, 02 Aug 2007 06:22:29 -0700, Dooza wrote:

>I worked it out!
Hi Dooza,
Good for you.
However, I think you'd be better off with a different design of your
table. As it is, you'll have to keep adding and removing columns and
changing your code every time a new courier option comes around, when an
option is removed, or even when an option is renamed.
Instead of different price columns for each courier option, you should
have one column Price and one column CourierOption. The latter should of
course be included in the table's key. That would make this query lots
easier!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Aug 2, 9:06 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Thu, 02 Aug 2007 06:22:29 -0700, Dooza wrote:
> Hi Dooza,
> Good for you.
> However, I think you'd be better off with a different design of your
> table. As it is, you'll have to keep adding and removing columns and
> changing your code every time a new courier option comes around, when an
> option is removed, or even when an option is renamed.
> Instead of different price columns for each courier option, you should
> have one column Price and one column CourierOption. The latter should of
> course be included in the table's key. That would make this query lots
> easier!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Hi Hugo,
You are correct, and this is what I have ended up doing, as my
previous attempt didn't allow me to also save the type of shipping in
the database. The way I am doing it now is much better. I can now
create a drop down list with the available options for that particular
weight, before I couldn't do that, and I now have an ID for the
shipping type that I can store in the database with the order.
The user is now presented with the drop down list to select the type
of shipping, once selected the id of the shipping type is inserted
into the database, I will then lookup the ID and pass the price to the
cart to be included with the total. A much neater solution.
Thank you all for steering me in the right direction!
Dooza