Cant figure out this query

I have a table with multiple records.

Some have the same value in the 'subkey' field.
I want to select all the records from the table that have their highest MAINKEY.

So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)

Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.

So the tables looks sort of lLike this:

ID SK MK
1 10 2
2 10 3
3 25 2
4 25 3

I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.

I can not figure this out. :eek:

Any help would be GREATLY appreciated.This works...

SELECT [ID]
FROM yourtable T1
WHERE EXISTS (
SELECT SK, MAX(MK) AS MK
FROM yourtable T2
WHERE T1.SK=T2.SK
GROUP BY SK
HAVING T1.MK=MAX(T2.MK))|||select a.id, a.sk, a.mk from yourtable a
where a.mk in(select max(b.mk) from yourtable b
where a.sk = b.sk)|||Simpler even:

select a.sk, max(a.mk) as MK from yourtable a
group by a.sk

Can't Figure out SQL Statement

Perhaps someone can help me on this SQL statement. Let's say my table
has only 2 fields:
RowId Name
-- --
1 A
2 A
3 A
4 A
5 B
6 B
7 B
8 B
I want to return a dataset containing the rows with the lowest RowId
for each DISTINCT Name. Result:
RowId Name
-- --
1 A
5 B
Can anybody help me?
Thanks,
JasonOne method:
SELECT
MIN(RowId) AS RowId,
Name
FROM MyTable
GROUP BY
Name
Hope this helps.
Dan Guzman
SQL Server MVP
"daokfella" <jjbutera@.hotmail.com> wrote in message
news:1126619489.331236.145060@.g49g2000cwa.googlegroups.com...
> Perhaps someone can help me on this SQL statement. Let's say my table
> has only 2 fields:
> RowId Name
> -- --
> 1 A
> 2 A
> 3 A
> 4 A
> 5 B
> 6 B
> 7 B
> 8 B
> I want to return a dataset containing the rows with the lowest RowId
> for each DISTINCT Name. Result:
> RowId Name
> -- --
> 1 A
> 5 B
> Can anybody help me?
> Thanks,
> Jason
>|||SELECT MIN(RowID), Name FROM [my table] GROUP BY Name
"daokfella" <jjbutera@.hotmail.com> wrote in message
news:1126619489.331236.145060@.g49g2000cwa.googlegroups.com...
> Perhaps someone can help me on this SQL statement. Let's say my table
> has only 2 fields:
> RowId Name
> -- --
> 1 A
> 2 A
> 3 A
> 4 A
> 5 B
> 6 B
> 7 B
> 8 B
> I want to return a dataset containing the rows with the lowest RowId
> for each DISTINCT Name. Result:
> RowId Name
> -- --
> 1 A
> 5 B
> Can anybody help me?
> Thanks,
> Jason
>

cant figure out how to write query..

Hi,
I have 3 tables, a person table, a timeRecords table, and a
RegionPersonHistory table.
The timeRecords table holds how many days were worked for a particualr date,
and the RegionPersonHistory keeps track of the persons Region. People can be
allocated to work on different regions so they might be working in the US fo
r
3 days then the following 4 days are in Europe etc.
I am trying to figure out how to write a query that will calulate how many
days were worked in any one region of a any specific date period. The tricky
thing is that the RegionPersonHistory table only holds records for a person
if they change from their default region (their default region is held in th
e
Person table). It doesnt always hold records for the person.
Have a look at the sql below which sets up the tables and see if you
understand my problem.
Here is the sql for the tables and some sample data...
CREATE TABLE [SYSDBA].[RegionPersonHistory] (
[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startdate] [datetime] NOT NULL ,
[enddate] [datetime] NOT NULL ,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
go
CREATE TABLE [SYSDBA].[TimeRecords] (
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startdate] [datetime] NOT NULL ,
[days] [integer] NOT NULL,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SYSDBA].[Person] (
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
go
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-01',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-02',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-03',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-04',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-05',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-06',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-07',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-08',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-09',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-10',1
go
insert [SYSDBA].[Person]
select 'smithd','US'
go
insert [SYSDBA].[RegionPersonHistory]
select 'smithd','Europe','2006-01-04','2006-01-08'Can you give a sample result that you might need from the inputs?
"NH" wrote:

> Hi,
> I have 3 tables, a person table, a timeRecords table, and a
> RegionPersonHistory table.
> The timeRecords table holds how many days were worked for a particualr dat
e,
> and the RegionPersonHistory keeps track of the persons Region. People can
be
> allocated to work on different regions so they might be working in the US
for
> 3 days then the following 4 days are in Europe etc.
> I am trying to figure out how to write a query that will calulate how many
> days were worked in any one region of a any specific date period. The tric
ky
> thing is that the RegionPersonHistory table only holds records for a perso
n
> if they change from their default region (their default region is held in
the
> Person table). It doesnt always hold records for the person.
> Have a look at the sql below which sets up the tables and see if you
> understand my problem.
> Here is the sql for the tables and some sample data...
> CREATE TABLE [SYSDBA].[RegionPersonHistory] (
> [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [startdate] [datetime] NOT NULL ,
> [enddate] [datetime] NOT NULL ,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [SYSDBA].[TimeRecords] (
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [startdate] [datetime] NOT NULL ,
> [days] [integer] NOT NULL,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [SYSDBA].[Person] (
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> go
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-01',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-02',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-03',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-04',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-05',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-06',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-07',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-08',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-09',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-10',1
> go
> insert [SYSDBA].[Person]
> select 'smithd','US'
> go
> insert [SYSDBA].[RegionPersonHistory]
> select 'smithd','Europe','2006-01-04','2006-01-08'|||Hello again Omnibuzz,
well, if I was to try to calculate the sum(days) that were worked in the US
region between 2006-01-01 and 2006-01-10 I would find that difficult because
theres a few days there that person 'smithd' logged while he was allocated t
o
the 'Europe' region. His default region is the US but the query needs to
check to see if any of the days logged between thoese dates were actually
part of a different region.
Those this make sense?
"Omnibuzz" wrote:
> Can you give a sample result that you might need from the inputs?
> "NH" wrote:
>|||Try this and let me know if this was what you wanted.
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-04'
select a.Persid,coalesce(b.region,c.region), count(a.days)
from Person c, TimeRecords a left outer join RegionPersonHistory b on
a.startdate between b.startdate and b.enddate
and a.persid = b.persid
where a.startdate between @.a and @.b
and a.persid = c.persid
group by a.persid,coalesce(b.region,c.region)|||Thats not quite right, that returns 8 days when it should be only 4.
Then also the query needs to take in a thrid paramter to filter for a
particualr region.
Maybe this is just a bit too messy...
"Omnibuzz" wrote:

> Try this and let me know if this was what you wanted.
>
> declare @.a datetime, @.b datetime
> set @.a = '2006-01-01'
> set @.b = '2006-01-04'
> select a.Persid,coalesce(b.region,c.region), count(a.days)
> from Person c, TimeRecords a left outer join RegionPersonHistory b on
> a.startdate between b.startdate and b.enddate
> and a.persid = b.persid
> where a.startdate between @.a and @.b
> and a.persid = c.persid
> group by a.persid,coalesce(b.region,c.region)|||It worked fine for the data you gave.
Can you give the data for which the error and tell me what are the filters
and what is the expected result
"NH" wrote:
> Thats not quite right, that returns 8 days when it should be only 4.
> Then also the query needs to take in a thrid paramter to filter for a
> particualr region.
> Maybe this is just a bit too messy...
> "Omnibuzz" wrote:
>|||sorry your query does return the same value I get.
I gave you a mistake in the source data, can you run this...
delete from RegionPersonHistory
insert [SYSDBA].[RegionPersonHistory]
select 'Europe','smithd','2006-01-04','2006-01-08'
I have this modified query now...
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-05'
select a.Persid, count(a.days)
from Person c, TimeRecords a
left join RegionPersonHistory b on (a.startdate between b.startdate and
b.enddate
and b.region='us')
where a.startdate between @.a and @.b
and a.persid = c.persid
and c.region='us'
group by a.persid
I am trying to only return days worked in the US... but cant figure it out..
.
"Omnibuzz" wrote:
> It worked fine for the data you gave.
> Can you give the data for which the error and tell me what are the filters
> and what is the expected result
> "NH" wrote:
>|||Okay, this works for me. You added the filter wrong.
I have changed my first query. And I saw the mitake in the insert and I
changed it :)
Check this and let me know if this works.
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-05'
select a.Persid,coalesce(b.region,c.region), count(a.days)
from Person c, TimeRecords a left outer join RegionPersonHistory b on
a.startdate between b.startdate and b.enddate
and a.persid = b.persid
where a.startdate between @.a and @.b
and a.persid = c.persid
and coalesce(b.region,c.region) = 'us'
group by a.persid,coalesce(b.region,c.region)|||Thanks Omnibuzz, it looks like this is working.
I appreciate your help once again.
NH
"Omnibuzz" wrote:

> Okay, this works for me. You added the filter wrong.
> I have changed my first query. And I saw the mitake in the insert and I
> changed it :)
> Check this and let me know if this works.
>
> declare @.a datetime, @.b datetime
> set @.a = '2006-01-01'
> set @.b = '2006-01-05'
> select a.Persid,coalesce(b.region,c.region), count(a.days)
> from Person c, TimeRecords a left outer join RegionPersonHistory b on
> a.startdate between b.startdate and b.enddate
> and a.persid = b.persid
> where a.startdate between @.a and @.b
> and a.persid = c.persid
> and coalesce(b.region,c.region) = 'us'
> group by a.persid,coalesce(b.region,c.region)
>

Can't figure out how to write query

I have a table TABLE1. My company has 2 sites. This table contains employees
with the amount of hours they worked on which project at which sites.

code:

CREATE TABLE #TABLE1 (
Calldate varchar(10) NULL,
Employee varchar(10) NULL,
Project varchar(10) NULL,
Hours decimal(10,4) NULL,
Site varchar(1) NULL)
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '246', 'EACQ5', 3, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ5', 2, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 5, '2')

I need to figure out the following:
I need the total hours of employees from both sites ONLY if they worked on a
project that ended in a 5. If employees worked on projects that did not end
in 5 I need the totals for their site only. A parameter of site will be
passed to the stored procedure.
So for example: If site parameter of 1 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 1.5
If site parameter of 2 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 5
Any help would be greatly appreciated,
Thanks,
ninel
Message posted via http://www.webservertalk.comThanks for posting DDL and sample data.
declare @.site varchar(1)
set @.site = '1'
select calldate, project,
sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
from #table1
group by calldate, project
set @.site = '2'
select calldate, project,
sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
from #table1
group by calldate, project
"ninel g via webservertalk.com" wrote:

>
I have a table TABLE1. My company has 2 sites. This table contains employe
es
>
with the amount of hours they worked on which project at which sites.
>
>

code:

>
 CREATE TABLE #TABLE1 (
>
 Calldate varchar(10) NULL,
>
 Employee varchar(10) NULL,
>
 Project varchar(10) NULL,
>
 Hours decimal(10,4) NULL,
>
 Site varchar(1) NULL)
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '246', 'EACQ5', 3, '2')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ5', 2, '1')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ6', 5, '2')
>

>
>
I need to figure out the following:
>
I need the total hours of employees from both sites ONLY if they worked on
a
>
project that ended in a 5. If employees worked on projects that did not en
d
>
in 5 I need the totals for their site only. A parameter of site will be
>
passed to the stored procedure.
>
>
So for example: If site parameter of 1 is passed.
>
I need to see the following results:
>
>
Calldate Project TotalHours
>
20060217 EAUD5 2.5
>
20060217 EACQ5 5
>
20060217 EACQ6 1.5
>
>
If site parameter of 2 is passed.
>
I need to see the following results:
>
>
Calldate Project TotalHours
>
20060217 EAUD5 2.5
>
20060217 EACQ5 5
>
20060217 EACQ6 5
>
>
Any help would be greatly appreciated,
>
>
Thanks,
>
ninel
>
>
--
>
Message posted via http://www.webservertalk.com
>
|||Thnak you so much for teh quick response.
Mark Williams wrote:
>Thanks for posting DDL and sample data.
>declare @.site varchar(1)
>set @.site = '1'
>select calldate, project,
>sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>set @.site = '2'
>select calldate, project,
>sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>
>[quoted text clipped - 49 lines]
Message posted via http://www.webservertalk.comsql

Can't figure out how to pass a comma delimeted value?

I am trying to pass a comma delimited value from an input box "attempting to
allow for multple entries within a string" and have the results returned from
the report. The stored procedure that I created will allow me to get what I
want but when I try to make the report make use of the sproc from within a
parameter I get nothing returned if I enter multiple values seperated by a
comma into the input box. If I put in just a single entry, it works.
Again, when I run the stored procedure within the database it works with
multiple values, when I run and test within the data view in the report it
runs and accepts multiple entries when prompted. Only when I run the report
does it fail?
Here is the sproc...
USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[usp_TestRprtArray]
@.subscriberarray varchar(500)
as
declare @.sql varchar(8000)
set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
+ "))"
exec (@.sql)
--
Anthony E. Castro - MCDBAIf the comma seperated value is passed from thr UI screen of a web
page.. try url encoding the page...
acmcdba68 wrote:
> I am trying to pass a comma delimited value from an input box "attempting to
> allow for multple entries within a string" and have the results returned from
> the report. The stored procedure that I created will allow me to get what I
> want but when I try to make the report make use of the sproc from within a
> parameter I get nothing returned if I enter multiple values seperated by a
> comma into the input box. If I put in just a single entry, it works.
> Again, when I run the stored procedure within the database it works with
> multiple values, when I run and test within the data view in the report it
> runs and accepts multiple entries when prompted. Only when I run the report
> does it fail?
> Here is the sproc...
> USE [mydatabase]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[usp_TestRprtArray]
> @.subscriberarray varchar(500)
> as
> declare @.sql varchar(8000)
> set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
> set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
> EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
> + "))"
> exec (@.sql)
> --
> Anthony E. Castro - MCDBA|||try putting in a textbox in your report that shows what parameter RS is
sending your sproc. that can be helpfule in seeing just what is passing in
the back end. Are you using a Multi-value parameter box? that format is
usually '<value>,<value>,...'
What works for me is to loop through the string, parsing out each value and
inserting the results into a temp table that can then be sorted, filtered,
etc as you like.
"acmcdba68" wrote:
> I am trying to pass a comma delimited value from an input box "attempting to
> allow for multple entries within a string" and have the results returned from
> the report. The stored procedure that I created will allow me to get what I
> want but when I try to make the report make use of the sproc from within a
> parameter I get nothing returned if I enter multiple values seperated by a
> comma into the input box. If I put in just a single entry, it works.
> Again, when I run the stored procedure within the database it works with
> multiple values, when I run and test within the data view in the report it
> runs and accepts multiple entries when prompted. Only when I run the report
> does it fail?
> Here is the sproc...
> USE [mydatabase]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[usp_TestRprtArray]
> @.subscriberarray varchar(500)
> as
> declare @.sql varchar(8000)
> set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
> set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
> EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
> + "))"
> exec (@.sql)
> --
> Anthony E. Castro - MCDBA

Cant figure out error message in SQL

Can someone please look at my stored procedure? I am trying to create the following stored procedure, but get the following error
messages:

Msg 102, Level 15, State 1, Procedure InsertWork, Line 3
Incorrect syntax near '7'.
Msg 102, Level 15, State 1, Procedure InsertWork, Line 25
Incorrect syntax near'@.7am8am'.

USE [Work]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertWork]
(
7am8am nvarchar(500),
8am9am nvarchar(500),
9am10am nvarchar(500),
10am11am nvarchar(500),
11am12noon nvarchar(500),
12Noon1pm nvarchar(500),
1pm2pm nvarchar(500),
2pm3pm nvarchar(500),
3pm4pm nvarchar(500),
4pm5pm nvarchar(500),
5pm6pm nvarchar(500),
6pm7pm nvarchar(500),
7pm8pm nvarchar(500),
8pm9pm nvarchar(500),
9pm10pm nvarchar(500),
10pm11pm nvarchar(500),
Notes nvarchar(500),
Date nvarchar(15)
)
AS BEGIN
INSERT INTO WorkDay
VALUES
@.7am8am,
@.8am9am,
@.9am10am,
@.10am11am,
@.11am12Noon,
@.12Noon1pm,
@.1pm2pm,
@.2pm3pm,
@.3pm4pm,
@.4pm5pm,
@.5pm6pm,
@.6pm7pm,
@.7pm8pm,
@.8pm9pm,
@.9pm10pm,
@.10pm11pm,
@.Notes,
@.Date
END

You cannot have variables/parameters names that starts with a number. Try prefixing them with an underscore (quick fix).

|||

johram:

You cannot have variables/parameters names that starts with a number.

Actually you can. The parameters have to start with @. for SQL Server to know its a parameter..

CREATE PROCEDURE [dbo].[InsertWork](@.7am8amnvarchar(500),@.8am9amnvarchar(500),@.9am10amnvarchar(500),@.10am11amnvarchar(500), @.11am12noonnvarchar(500),@.12Noon1pmnvarchar(500),@.1pm2pmnvarchar(500),@.2pm3pmnvarchar(500),@.3pm4pmnvarchar(500),@.4pm5pmnvarchar(500), @.5pm6pmnvarchar(500),@.6pm7pmnvarchar(500),@.7pm8pmnvarchar(500),@.8pm9pmnvarchar(500),@.9pm10pmnvarchar(500), @.10pm11pmnvarchar(500),@.Notesnvarchar(500),@.Datenvarchar(15))AS BEGIN INSERT INTO WorkDayVALUES (@.7am8am, @.8am9am,@.9am10am, @.10am11am, @.11am12Noon,@.12Noon1pm,@.1pm2pm,@.2pm3pm,@.3pm4pm,@.4pm5pm,@.5pm6pm,@.6pm7pm,@.7pm8pm, @.8pm9pm,@.9pm10pm,@.10pm11pm,@.Notes,@.Date )END

|||

ndinakar:

Actually you can. The parameters have to start with @. for SQL Server to know its a parameter..

Of course :-)

cant do that request

Hi,
I'm trying to do a request but I can't figure out how to do it.
Basically I have 1 table with 3 fields (field1,field2,field3).
Values in those 3 fields can be the same, but I want to select all of
these values only once (i.e distinct values)

Example

Field1 Field2 Field3
A A A
B E B
C F F
D G H

The select should return: A,B,C,D,E,F,G,H
I've tried with union and a select not in but I ended up with something
big and I got confused.
Can someone help ?
ThxSELECT col1
FROM YourTable
UNION
SELECT col2
FROM YourTable
UNION
SELECT col3
FROM YourTable

If that doesn't give the result you wanted then please post DDL and
sample data as described here:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||Seems to work. Thanks !
Regarding the DDL, I've looked at it but I post my messages via the
google group thing and I'm not sure if I can upload attached stuff ?|||Glad it helped.

Put DDL and other scripts in the body of your message. Most people
can't or won't download attachments anyway.

--
David Portas
SQL Server MVP
--

Cant Create Subscription...

I've got a strange problem that I can't quite figure out. Whenever I go to setup a new subscription for a report (any report) I can't select a schedule or create the subscription.
I get to the Subscription page, but pressing any of the buttons: "Ok", "Cancel", "Select Schedule" does nothing.
Anyone else ever have this issue?
Any feedback would be greatly appreciated.

Hi
can you able to see SubscriptionProperties.aspx Page after clicking on New Subscription.
Thanx
Rohit|||

Yeah, pressing "New Subscription" takes me to the form (SubscriptionProperties.aspx), but not matter what button I press on the form - Ok, Cancel, Select Schedule... nothing happens.
It's as if you were creating an .aspx form and set "AutoPostBack" on a control to false. Clicking on any of the buttons does nothing. Therefore, I'm not able to create a new subscription or alter
any of the subscriptions schedules that I currently have.
I've restarted the reportserver service and I've restarted the application pool. Neither helps.

|||

Just wanted to follow up on this in case that someone else runs accross this. This was a framework issue with SP1. Not really related to Reporting Services, but possibly relevant nonetheless.

http://channel9.msdn.com/showpost.aspx?postid=21650