Query.....help

Cloumn Name, Data type, length, allow nulls:
ID int 4 0
NAAM nvarchar 254 1
DATUM smalldatetime 4 1
Dag int 4 1
Maand int 4 1
Jaar int 4 1
Reenval_Silo float 8 1
Using this table i need to get the average of "Reenval_Silo" per month
for "NAAM " for the years 2006, 2005 & 2004
Any help will be highly appreciated...thnx in advance
Hi
Since you have not posted DDL+ sample data + an expected result , I'll give
an idea about how you van do that
--Average of quantity per year group by stor_id
SELECT
stor_id,
AVG(CASE YEAR(ord_date)
WHEN 2004 THEN qty
ELSE 0
END) AS c2004 ,
AVG(CASE YEAR(ord_date)
WHEN 2005 THEN qty
ELSE 0
END) AS c2005 ,
AVG(CASE YEAR(ord_date)
WHEN 2006 THEN qty
ELSE 0
END) AS c2006
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"amatuer" <njoosub@.gmail.com> wrote in message
news:1160723854.407946.4020@.k70g2000cwa.googlegrou ps.com...
> Cloumn Name, Data type, length, allow nulls:
> ID int 4 0
> NAAM nvarchar 254 1
> DATUM smalldatetime 4 1
> Dag int 4 1
> Maand int 4 1
> Jaar int 4 1
> Reenval_Silo float 8 1
>
> Using this table i need to get the average of "Reenval_Silo" per month
> for "NAAM " for the years 2006, 2005 & 2004
> Any help will be highly appreciated...thnx in advance
>

Query.....help

Cloumn Name, Data type, length, allow nulls:
ID int 4 0
NAAM nvarchar 254 1
DATUM smalldatetime 4 1
Dag int 4 1
Maand int 4 1
Jaar int 4 1
Reenval_Silo float 8 1
Using this table i need to get the average of "Reenval_Silo" per month
for "NAAM " for the years 2006, 2005 & 2004
Any help will be highly appreciated...thnx in advanceHi
Since you have not posted DDL+ sample data + an expected result , I'll give
an idea about how you van do that
--Average of quantity per year group by stor_id
SELECT
stor_id,
AVG(CASE YEAR(ord_date)
WHEN 2004 THEN qty
ELSE 0
END) AS c2004 ,
AVG(CASE YEAR(ord_date)
WHEN 2005 THEN qty
ELSE 0
END) AS c2005 ,
AVG(CASE YEAR(ord_date)
WHEN 2006 THEN qty
ELSE 0
END) AS c2006
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"amatuer" <njoosub@.gmail.com> wrote in message
news:1160723854.407946.4020@.k70g2000cwa.googlegroups.com...
> Cloumn Name, Data type, length, allow nulls:
> ID int 4 0
> NAAM nvarchar 254 1
> DATUM smalldatetime 4 1
> Dag int 4 1
> Maand int 4 1
> Jaar int 4 1
> Reenval_Silo float 8 1
>
> Using this table i need to get the average of "Reenval_Silo" per month
> for "NAAM " for the years 2006, 2005 & 2004
> Any help will be highly appreciated...thnx in advance
>

Query.....help

Cloumn Name, Data type, length, allow nulls:
ID int 4 0
NAAM nvarchar 254 1
DATUM smalldatetime 4 1
Dag int 4 1
Maand int 4 1
Jaar int 4 1
Reenval_Silo float 8 1
Using this table i need to get the average of "Reenval_Silo" per month
for "NAAM " for the years 2006, 2005 & 2004
Any help will be highly appreciated...thnx in advanceHi
Since you have not posted DDL+ sample data + an expected result , I'll give
an idea about how you van do that
--Average of quantity per year group by stor_id
SELECT
stor_id,
AVG(CASE YEAR(ord_date)
WHEN 2004 THEN qty
ELSE 0
END) AS c2004 ,
AVG(CASE YEAR(ord_date)
WHEN 2005 THEN qty
ELSE 0
END) AS c2005 ,
AVG(CASE YEAR(ord_date)
WHEN 2006 THEN qty
ELSE 0
END) AS c2006
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"amatuer" <njoosub@.gmail.com> wrote in message
news:1160723854.407946.4020@.k70g2000cwa.googlegroups.com...
> Cloumn Name, Data type, length, allow nulls:
> ID int 4 0
> NAAM nvarchar 254 1
> DATUM smalldatetime 4 1
> Dag int 4 1
> Maand int 4 1
> Jaar int 4 1
> Reenval_Silo float 8 1
>
> Using this table i need to get the average of "Reenval_Silo" per month
> for "NAAM " for the years 2006, 2005 & 2004
> Any help will be highly appreciated...thnx in advance
>sql

Query using two column names in a table (to find rows near each other)

I have a table called Seats in my database...
CREATE TABLE [dbo].[Seats] (
[SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
[VehicleSerialNo] [int] NOT NULL ,
[RowNo] [smallint] NOT NULL ,
[ColumnNo] [smallint] NOT NULL ,
[SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
This table defines the seats that are available for a given VehicleSerialNo
(the typical scenario would be a motor coach or possibly an aircraft). A
seat has a RowNo (how far down the vehicle it is), a ColumnNo (it's position
left to right on the vehicle) and a SeatNo (the actual seat number the
customer is given - e.g. please sit in seat number 40 - these can be numeric
or like aircraft seats numbers 23D etc).
I have another table called Passengers which stores the VehicleSerialNo (the
actual motor coach) and the SeatSerialNo (the seat they are sitting in on
that motor coach). Let's now assume this table contains lots of entries
already specifying where the existing passengers will be sitting.
Example: I now want to add 6 people on this vehicle and automatically
allocate each person a seat. I am trying to figure out whether this
automatic seat selection can be achieved in SQL Server or whether this
should be done on the client side using VB. Ideally you would always like
to make sure all 6 people are sitting on the same part of the motor coach
(unless it is getting full). Would it be possible to construct a query that
would select seats that are near each other based on RowNo and ColumnNo
(excluding SeatSerialNo's that exists in the Passengers table - e.g. no
double booking of a seat)? So I want to return a query that returns the 6
seats the system thinks are best. Can such a query be performed comparing
these column names (RowNo and ColumnNo) finding seats that are near to each
other.
Many thanks,
ChrisC-W
Its hard to suggest without seeing sample data+ relationship+ expected
result.
Why you don't have a primary on the table?
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:O0cKXJanFHA.3312@.tk2msftngp13.phx.gbl...
>I have a table called Seats in my database...
>
> CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> This table defines the seats that are available for a given
> VehicleSerialNo (the typical scenario would be a motor coach or possibly
> an aircraft). A seat has a RowNo (how far down the vehicle it is), a
> ColumnNo (it's position left to right on the vehicle) and a SeatNo (the
> actual seat number the customer is given - e.g. please sit in seat number
> 40 - these can be numeric or like aircraft seats numbers 23D etc).
>
> I have another table called Passengers which stores the VehicleSerialNo
> (the actual motor coach) and the SeatSerialNo (the seat they are sitting
> in on that motor coach). Let's now assume this table contains lots of
> entries already specifying where the existing passengers will be sitting.
>
> Example: I now want to add 6 people on this vehicle and automatically
> allocate each person a seat. I am trying to figure out whether this
> automatic seat selection can be achieved in SQL Server or whether this
> should be done on the client side using VB. Ideally you would always like
> to make sure all 6 people are sitting on the same part of the motor coach
> (unless it is getting full). Would it be possible to construct a query
> that would select seats that are near each other based on RowNo and
> ColumnNo (excluding SeatSerialNo's that exists in the Passengers table -
> e.g. no double booking of a seat)? So I want to return a query that
> returns the 6 seats the system thinks are best. Can such a query be
> performed comparing these column names (RowNo and ColumnNo) finding seats
> that are near to each other.
>
> Many thanks,
> Chris
>
>|||Sorry, that's just the way I scripted the table. SeatSerialNo is the
primary key.
I will try and work on some sample data.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uwOTGPanFHA.320@.TK2MSFTNGP09.phx.gbl...
> C-W
> Its hard to suggest without seeing sample data+ relationship+ expected
> result.
> Why you don't have a primary on the table?
>|||On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:

>I have a table called Seats in my database...
>
>CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>GO
Hi Chris,
You have two good candidate keys in the table without the extra identity
column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
ColumnNo). My advice would be to drop the SeatSerialNo column, declare
one of the composite candidate keys to be the PRIMARY KEY (probably the
one with SeatNo, but it depends on a lot of factors I don't know) and
define a UNIQUE constraint for the other one.

>I have another table called Passengers which stores the VehicleSerialNo (th
e
>actual motor coach) and the SeatSerialNo (the seat they are sitting in on
>that motor coach).
That's redundant. What if a passenger has VehicleSerialNo 1 and
SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
VehicleSerialNo 2?
If you keep SeatSerialNo in Seats and use it to refer to a seat in the
Passengers table, then remove VehicleSerialNo from the Passengers table
(the seat will always be in the same vehicle, regardless of who is
sitting on it). Or, if you drop SeatSerialNo from Seats, store the
combination of VehicleSerialNo and SeatNo in the Passengers table.
(snip)
>So I want to return a query that returns the 6
>seats the system thinks are best. Can such a query be performed comparing
>these column names (RowNo and ColumnNo) finding seats that are near to each
>other.
As Uri said. A repro script that others can run to recreate your test
data and the expected output would make it much easier to help you.
See www.aspfaq.com/5006 for more details and hints.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Once i've generate the script to reproduce this I will explain the table
structure further (and hopefully will all make sense). I tried to reproduce
a simple example before but probably caused more confusion. The Seats table
does not actually contain the VehicleSerialNo. Hopefully all will make
sense when I post my script.
Thanks,
Chris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:bg3kf15cvqe3r4k8hfo1jnr7uhpf52o3el@.
4ax.com...
> On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:
>
> Hi Chris,
> You have two good candidate keys in the table without the extra identity
> column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
> ColumnNo). My advice would be to drop the SeatSerialNo column, declare
> one of the composite candidate keys to be the PRIMARY KEY (probably the
> one with SeatNo, but it depends on a lot of factors I don't know) and
> define a UNIQUE constraint for the other one.
>
> That's redundant. What if a passenger has VehicleSerialNo 1 and
> SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
> VehicleSerialNo 2?
> If you keep SeatSerialNo in Seats and use it to refer to a seat in the
> Passengers table, then remove VehicleSerialNo from the Passengers table
> (the seat will always be in the same vehicle, regardless of who is
> sitting on it). Or, if you drop SeatSerialNo from Seats, store the
> combination of VehicleSerialNo and SeatNo in the Passengers table.
>
> (snip)
> As Uri said. A repro script that others can run to recreate your test
> data and the expected output would make it much easier to help you.
> See www.aspfaq.com/5006 for more details and hints.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

query to show duplicates

mytable fld1 int primkey
fld2 varchar(20),
fld3 varchar(20)
From the definition of the above table, how do i do i modify my below query to only select the rows with duplicates in fld2. I know a groupby with a having count will display the duplicates for a given field, but i want my query to see all the fields and rows that are duplicates.
select fld1, fld2, fld3 from mytable
SELECT fld2,COUNT(*) FROM myTable GROUP BY Flt2 HAVING COUNT(*)>1

Query to see if an int field starts with a certain number

How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!Convert it to string, perform a substring, and then do your comparison.

Perhaps: substring(cast([thisfield] as varchar(50)),1,1)|||I don't know how the performance of this will compare, but if thisfield is non-negative, this should work as well:

[thisfield] / power(10, cast(log10([thisfield] as int))

Cheers,
-Isaac
|||

I hate to ask this, but the giant pink elephant in the room is "how do you have an int that doesn't start with a number?" What it sounds like you have is a column of string values that may or may not be an integer, and you want to see if the first character of the string is a number, right? For this it is:

thisColumn like '[1234567890]%'

But if the column is supposed to only contain integers, the best way to make sure that they are integers is to create the column using an integer datatype.