Grouping using two stored procedures

Hi,

I am creating a set of reports in Crystal showing emails sent and delivered from users within our organisation.

Each report uses a different stored procedure.

Report 1 shows emails sent:

Sender: Person in our org
Recipient: Person outside our org
Number: number of emails sent

Report 2 shows emails recived:

Sender: person outside our org
Recipient: Person in our org
Number: Number of emails received

In crystal, the reports are grouped around the sender for report 1, and the recipient for group 2 (therefore the reports are grouped around the person in our organisation).

Now I need to create a report showing the details of the two reports combined into one, but this creates a problem when i try to group. i need to distinguish between who is in our organisation and who is it, and then to group by them.

does anyone have any idea how this may be done?

i want the report to look like this:

Person in our organisation:
----------
Sent: bob@.yahoo.co.uk 26
sally@.hotmail.com 4
peter@.msn.com 12
Subtotal: 42

Received: fred@.company.co.uk 45
vicky@.hotmail.com 10
Subtotal 55

Total 97

and so on for each person.

Many thanks if you can helpEither create it as two subreports in Crystal, or use a UNION query to create a single dataset from both SQL statements. With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".|||With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".

how do i do this?

at the moment, the most i can come up with is

select * from vw_sent
union
select * from vw_received

and that's where my question comes from really, how to determine what addresses in each result are @.mydomain.co.uk and then to group by those. Becuase the resultset of this query is:

Sender Recipient Number
person@.mydomain.co.uk person@.hotmail.com 5
otherperson@.mydomain.co.uk person@.hotmail.com 2
otherperson@.hotmail.com person@.mydomain.co.uk 10

so how do i add an extra field in my resultset to show which email address is in my domain?|||select 'SENT' as Direction, * from vw_sent
union
select 'RECEIVED' as Direction, * from vw_received

...though you really should enumerate your field names instead of using *, especially in an UNION query.

grouping select query

Hi,
I have data stored as in below sample :
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
--+--+--
--
I have to group the select query in this way :
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
--+--+--
--
When I run below grouped statement, I get follewed result:
SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
Rate FROM X GROUP BY Rate
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
--+--+--
--
How can I do a query like in 2nd sample from top?
best regards,
rustam bogubaevThis is a periodicity problem, not a SQL syntax problem.
You have to define how the period is to be divided first. In essence,
however you decide to calculate the period, the data would logically contain
the following information.
--+--+--
--
DateBegin | DateEnd | Rate |
Period
--+--+--
--
2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000 1
2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000 1
2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000 2
2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000 2
2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000 2
2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000 2
2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000 2
2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000 3
2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000 3
--+--+--
--
With the periods defined, however that is done, your problem will be easy.
Perhaps something like the following would help find the boundarys of the
periods.
SELECT b.DateBegin
FROM MyTable a JOIN MyTable b
ON a.DateEnd = b.DateBegin
WHERE a.Rate != b.Rate
RLF
<rustam.bogubaev@.gmail.com> wrote in message
news:1131461007.812709.108200@.g49g2000cwa.googlegroups.com...
> Hi,
> I have data stored as in below sample :
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
> 2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
> 2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
> 2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
> 2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
> 2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
> 2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
> --+--+--
--
>
> I have to group the select query in this way :
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
> --+--+--
--
> When I run below grouped statement, I get follewed result:
> SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
> Rate FROM X GROUP BY Rate
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> --+--+--
--
> How can I do a query like in 2nd sample from top?
> best regards,
> rustam bogubaev
>|||On 8 Nov 2005 06:43:27 -0800, rustam.bogubaev@.gmail.com wrote:
(snip)
>I have to group the select query in this way :
>--+--+--
--
> DateBegin | DateEnd | Rate
>--+--+--
--
>2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
>2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
>2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
>--+--+--[/c
olor]
Hi rustam,
If my assumptions about your table and the reasons for your expected
results are correct, then try:
SELECT a.DateBegin, MAX(b.DateEnd), a.Rate
FROM X AS a
INNER JOIN X as b
ON b.Rate = a.Rate
AND b.DateBegin >= a.DateStart
WHERE NOT EXISTS
(SELECT *
FROM X AS c
WHERE c.DateBegin = DATEADD(day, -1, a.DateBegin)
AND c.Rate = a.Rate)
AND NOT EXISTS
(SELECT *
FROM X AS d
WHERE d.DateBegin > a.DateEnd
AND d.DateEnd < b.DateBegin
AND d.Rate <> a.Rate)
GROUP BY a.DateBegin, a.Rate
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

grouping select query

Hi,

I have data stored as in below sample :

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-14 00:00:0063.0000
2005-11-14 00:00:002005-11-15 00:00:0063.0000
2005-11-15 00:00:002005-11-16 00:00:0045.0000
2005-11-16 00:00:002005-11-17 00:00:0045.0000
2005-11-17 00:00:002005-11-18 00:00:0045.0000
2005-11-18 00:00:002005-11-19 00:00:0045.0000
2005-11-19 00:00:002005-11-20 00:00:0045.0000
2005-11-20 00:00:002005-11-21 00:00:0063.0000
2005-11-21 00:00:002005-11-22 00:00:0063.0000
----------+----------+-----

I have to group the select query in this way :

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-15 00:00:0063.0000
2005-11-15 00:00:002005-11-20 00:00:0045.0000
2005-11-20 00:00:002005-11-22 00:00:0063.0000
----------+----------+-----

When I run below grouped statement, I get follewed result:

SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
Rate FROM X GROUP BY Rate

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-22 00:00:0063.0000
2005-11-15 00:00:002005-11-20 00:00:0045.0000
----------+----------+-----

How can I do a query like in 2nd sample from top?

best regards,
rustam bogubaevPYCTAM wrote:
> Hi,
> I have data stored as in below sample :
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
> 2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
> 2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
> 2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
> 2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
> 2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
> 2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
> ----------+----------+--
---
>
> I have to group the select query in this way :
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
> ----------+----------+--
---
> When I run below grouped statement, I get follewed result:
> SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
> Rate FROM X GROUP BY Rate
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> ----------+----------+--
---
> How can I do a query like in 2nd sample from top?

Care to explain by what you want to group? I cannot recognize it from
your sample output.

robert|||On 8 Nov 2005 06:42:33 -0800, PYCTAM wrote:

(snip)

Hi rustam,

You posted an exact identical copy of this question in the group
microsoft.public.sqlserver.programming, and I posted a reply there.

Please do not post the same question independently to multiple groups.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Grouping parameter value

I'm working on a stored procedure that works fine. I just want to make it possible for the user to be able to have a drop down list in reporting services to display the "question codes" grouped by whatever the first two digits are. for example.

VT01

VT02

VT03

VN01

VN02

VN03

ST01

ST02

ST03

instead of listing everything, i want the viewers to see this

VT

VN

ST

or an alias for each of these like this:

Vet Tasks

Vet National

Survey Tasks

Survey National

any ideas, here's my current code, which is pullin up anything with the added substring part

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions]

(@.Region_Key int=null,@.QuestionCode char(5))

AS

BEGIN

SELECT dbo.Qry_Questions.Territory,

dbo.Qry_Questions.SalesResponsible,

dbo.Qry_Questions.Customer,

dbo.Qry_Questions.Date,

dbo.Qry_Questions.StoreName,

dbo.Qry_Questions.PostCode,

dbo.Qry_Questions.Address2,

dbo.Qry_Questions.[Question Code],

dbo.Qry_Questions.Question,

dbo.Qry_Questions.[Response Type],

dbo.Qry_Questions.response,

dbo.Qry_Questions.sales_person_code,

dbo.Qry_Sales_Group.Region_Key,

dbo.Qry_Sales_Group.Region

FROM dbo.Qry_Questions

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

WHERE REGION_KEY=@.Region_Key

AND SUBSTRING(dbo.Qry_Questions.[Question Code],0,3)=@.QuestionCode

END

SET NOCOUNT OFF

You might try using the follwing as the grouping expression:

Code Snippet

=Left(Fields!<your_field>.Value, 2)

From there you could either set up a CASE statement or code for your aliases.

Hope this helps!

Scott

|||

I have the report working, i just want to be able to group the choices into 6 different choices. I know there is a way to do this in the report parameters properties box. I created my own non queried values that look like this:

Label Value

Survey national =IIF(Left(Fields!Question_Code.Value, 2)="SN",Fields!Question_Code.Value,nothing)

Survey vet =IIF(Left(Fields!Question_Code.Value, 2)="SV",Fields!Question_Code.Value,nothing)

Survey independent =IIF(Left(Fields!Question_Code.Value, 2)="SI",Fields!Question_Code.Value,nothing)

and so on...

But i keep getting an error :

A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field.

what am i doing wrong?

|||

I believe that if you are populating values for a parameter, you can't use the same dataset used in the report. I vaguely remember running into the same problem when I fist began using parameters. We use separate datasets for the parameters in our reports.

|||

Create a second dataset with the following query:

Select Distinct Left(Question_Code, 2)

FROM dbo.Qry_Questions

Group by Question_Code

Order by Question_Code

Change your parameter to query and point it at this new dataset. Then in your main dataset query add the following to your where statement:

Where Question_Code IN(@.question_code_parm)

|||Thanks that worked beautifully!! And i was able to hard code and rename the Question codes that were group for report parameters.

Grouping parameter value

I'm working on a stored procedure that works fine. I just want to make it possible for the user to be able to have a drop down list in reporting services to display the "question codes" grouped by whatever the first two digits are. for example.

VT01

VT02

VT03

VN01

VN02

VN03

ST01

ST02

ST03

instead of listing everything, i want the viewers to see this

VT

VN

ST

or an alias for each of these like this:

Vet Tasks

Vet National

Survey Tasks

Survey National

any ideas, here's my current code, which is pullin up anything with the added substring part

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions]

(@.Region_Key int=null,@.QuestionCode char(5))

AS

BEGIN

SELECT dbo.Qry_Questions.Territory,

dbo.Qry_Questions.SalesResponsible,

dbo.Qry_Questions.Customer,

dbo.Qry_Questions.Date,

dbo.Qry_Questions.StoreName,

dbo.Qry_Questions.PostCode,

dbo.Qry_Questions.Address2,

dbo.Qry_Questions.[Question Code],

dbo.Qry_Questions.Question,

dbo.Qry_Questions.[Response Type],

dbo.Qry_Questions.response,

dbo.Qry_Questions.sales_person_code,

dbo.Qry_Sales_Group.Region_Key,

dbo.Qry_Sales_Group.Region

FROM dbo.Qry_Questions

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

WHERE REGION_KEY=@.Region_Key

AND SUBSTRING(dbo.Qry_Questions.[Question Code],0,3)=@.QuestionCode

END

SET NOCOUNT OFF

You might try using the follwing as the grouping expression:

Code Snippet

=Left(Fields!<your_field>.Value, 2)

From there you could either set up a CASE statement or code for your aliases.

Hope this helps!

Scott

|||

I have the report working, i just want to be able to group the choices into 6 different choices. I know there is a way to do this in the report parameters properties box. I created my own non queried values that look like this:

Label Value

Survey national =IIF(Left(Fields!Question_Code.Value, 2)="SN",Fields!Question_Code.Value,nothing)

Survey vet =IIF(Left(Fields!Question_Code.Value, 2)="SV",Fields!Question_Code.Value,nothing)

Survey independent =IIF(Left(Fields!Question_Code.Value, 2)="SI",Fields!Question_Code.Value,nothing)

and so on...

But i keep getting an error :

A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field.

what am i doing wrong?

|||

I believe that if you are populating values for a parameter, you can't use the same dataset used in the report. I vaguely remember running into the same problem when I fist began using parameters. We use separate datasets for the parameters in our reports.

|||

Create a second dataset with the following query:

Select Distinct Left(Question_Code, 2)

FROM dbo.Qry_Questions

Group by Question_Code

Order by Question_Code

Change your parameter to query and point it at this new dataset. Then in your main dataset query add the following to your where statement:

Where Question_Code IN(@.question_code_parm)

|||Thanks that worked beautifully!! And i was able to hard code and rename the Question codes that were group for report parameters.

grouping in ranges

Table:

SomeFKID <pk>
StartDateTime <pk>
EndDateTime
SomeValue

Example Scenario:

Data is stored hourly. So there would be 24 records for today for each SomeFKID. I need to be able to pass a TimeSpan (in minutes), a StartDateTime, and an EndDateTime to a stored procedure and return totals in the date range grouped by the TimeSpan. So if I want all records today grouped by 2 hour intervals I would need to pass:
7/1/2004 00:00:00, 7/1/2004 23:59:59, 120 --> and return 12 records one for hours 0-2, one for hours 2-4, etc.

Any advice would be greatly appreciated!

Thanks in advance,

WheatsterThe assignment had to have given you more detail than that. Does the teacher want splits pro-rated? Do you select based solely on start time? What do you aggregate?

I think this would be a lot easier if you just posted the whole assignment and let us look at it.

-PatP|||Based soley on start time is fine. Value Column aggregated. If I am missing something really obvious then please just inform me of where I need to look in the tsql help files.

Also, this will work for the prototype I am working on now, but in the future the requested Split may be smaller than the span. In other words 15 minutes where data is stored hourly...but I can deal with that later.|||I'm still unsure of how to help you get to your goal. Can you at least post 24 hours (rows) worth of sample data, with the result sets that you'd like to see for 60, 90, and 120 minutes?

-PatP|||**For 60 I want the view below
**For 120 I want rows 1&2 grouped, 3&4 grouped, etc.
**Lets just use 60 minute increments for now. 90 can be dealt with later

ScheduleID StartDateTime EndDateTime Value
---- ---------------- ---------------- ----------------
1 2004-04-10 07:00:00.000 2004-04-10 08:00:00.000 257.0
1 2004-04-10 08:00:00.000 2004-04-10 09:00:00.000 252.0
1 2004-04-10 09:00:00.000 2004-04-10 10:00:00.000 242.0
1 2004-04-10 10:00:00.000 2004-04-10 11:00:00.000 247.0
1 2004-04-10 11:00:00.000 2004-04-10 12:00:00.000 257.0
1 2004-04-10 12:00:00.000 2004-04-10 13:00:00.000 282.0
1 2004-04-10 13:00:00.000 2004-04-10 14:00:00.000 317.0
1 2004-04-10 14:00:00.000 2004-04-10 15:00:00.000 347.0
1 2004-04-10 15:00:00.000 2004-04-10 16:00:00.000 352.0
1 2004-04-10 16:00:00.000 2004-04-10 17:00:00.000 342.0
1 2004-04-10 17:00:00.000 2004-04-10 18:00:00.000 317.0
1 2004-04-10 18:00:00.000 2004-04-10 19:00:00.000 302.0
1 2004-04-10 19:00:00.000 2004-04-10 20:00:00.000 287.0
1 2004-04-10 20:00:00.000 2004-04-10 21:00:00.000 277.0
1 2004-04-10 21:00:00.000 2004-04-10 22:00:00.000 267.0
1 2004-04-10 22:00:00.000 2004-04-10 23:00:00.000 262.0
1 2004-04-10 23:00:00.000 2004-04-11 00:00:00.000 267.0
1 2004-04-11 00:00:00.000 2004-04-11 01:00:00.000 277.0
1 2004-04-11 01:00:00.000 2004-04-11 02:00:00.000 287.0
1 2004-04-11 02:00:00.000 2004-04-11 03:00:00.000 297.0
1 2004-04-11 03:00:00.000 2004-04-11 04:00:00.000 307.0
1 2004-04-11 04:00:00.000 2004-04-11 05:00:00.000 297.0
1 2004-04-11 05:00:00.000 2004-04-11 06:00:00.000 277.0
1 2004-04-11 06:00:00.000 2004-04-11 07:00:00.000 252.0
1 2004-12-12 00:00:00.000 2004-12-12 01:00:00.000 12.0|||select dateadd(minute, datediff(minute, @.StartTime, @.TestTime)/@.IntervalMinutes, @.StartTime) as Range,
.
.
.
from YourTable
group by dateadd(minute, datediff(minute, @.StartTime, @.TestTime)/@.IntervalMinutes, @.StartTime)|||I came up with:CREATE TABLE dbo.theTable (
ScheduleID INT
, StartDateTime DATETIME
, EndDateTime DATETIME
, Value DECIMAL (4, 1)
)

INSERT INTO theTable (ScheduleId, StartDateTime, EndDateTime, Value)
SELECT 1, '2004-04-10 07:00:00.000', '2004-04-10 08:00:00.000', 257.0
UNION SELECT 1, '2004-04-10 08:00:00.000', '2004-04-10 09:00:00.000', 252.0
UNION SELECT 1, '2004-04-10 09:00:00.000', '2004-04-10 10:00:00.000', 242.0
UNION SELECT 1, '2004-04-10 10:00:00.000', '2004-04-10 11:00:00.000', 247.0
UNION SELECT 1, '2004-04-10 11:00:00.000', '2004-04-10 12:00:00.000', 257.0
UNION SELECT 1, '2004-04-10 12:00:00.000', '2004-04-10 13:00:00.000', 282.0
UNION SELECT 1, '2004-04-10 13:00:00.000', '2004-04-10 14:00:00.000', 317.0
UNION SELECT 1, '2004-04-10 14:00:00.000', '2004-04-10 15:00:00.000', 347.0
UNION SELECT 1, '2004-04-10 15:00:00.000', '2004-04-10 16:00:00.000', 352.0
UNION SELECT 1, '2004-04-10 16:00:00.000', '2004-04-10 17:00:00.000', 342.0
UNION SELECT 1, '2004-04-10 17:00:00.000', '2004-04-10 18:00:00.000', 317.0
UNION SELECT 1, '2004-04-10 18:00:00.000', '2004-04-10 19:00:00.000', 302.0
UNION SELECT 1, '2004-04-10 19:00:00.000', '2004-04-10 20:00:00.000', 287.0
UNION SELECT 1, '2004-04-10 20:00:00.000', '2004-04-10 21:00:00.000', 277.0
UNION SELECT 1, '2004-04-10 21:00:00.000', '2004-04-10 22:00:00.000', 267.0
UNION SELECT 1, '2004-04-10 22:00:00.000', '2004-04-10 23:00:00.000', 262.0
UNION SELECT 1, '2004-04-10 23:00:00.000', '2004-04-11 00:00:00.000', 267.0
UNION SELECT 1, '2004-04-11 00:00:00.000', '2004-04-11 01:00:00.000', 277.0
UNION SELECT 1, '2004-04-11 01:00:00.000', '2004-04-11 02:00:00.000', 287.0
UNION SELECT 1, '2004-04-11 02:00:00.000', '2004-04-11 03:00:00.000', 297.0
UNION SELECT 1, '2004-04-11 03:00:00.000', '2004-04-11 04:00:00.000', 307.0
UNION SELECT 1, '2004-04-11 04:00:00.000', '2004-04-11 05:00:00.000', 297.0
UNION SELECT 1, '2004-04-11 05:00:00.000', '2004-04-11 06:00:00.000', 277.0
UNION SELECT 1, '2004-04-11 06:00:00.000', '2004-04-11 07:00:00.000', 252.0
UNION SELECT 1, '2004-12-12 00:00:00.000', '2004-12-12 01:00:00.000', 12.0
GO
-- ptp 20040701 re: http://www.dbforums.com/t1003286.html

CREATE PROCEDURE dbo.theQuery
@.pdStart DATETIME
, @.pdEnd DATETIME
, @.piInterval INT
AS

IF 0 <> @.piInterval % 60 RETURN

SELECT DateAdd(minute, delta * @.piInterval, @.pdStart), Sum(Value)
FROM (SELECT DateDiff(minute, @.pdStart, StartDateTime)
/ @.piInterval AS delta, Value
FROM dbo.theTable
WHERE StartDateTime BETWEEN @.pdStart AND @.pdEnd) AS a
GROUP BY delta

RETURN
GO

EXECUTE dbo.theQuery '2004-04-10 12:00', '2004-04-11 12:00', 60
EXECUTE dbo.theQuery '2004-04-10 12:00', '2004-04-11 12:00', 90
EXECUTE dbo.theQuery '2004-04-10 12:00', '2004-04-11 12:00', 120

DROP PROCEDURE dbo.theQuery
DROP TABLE dbo.theTableBlindman's query might work just as well, and it appears to be simpler.

-PatP|||Thanks guys, I really appreciate this! Pat, I just tested and your solution definitely works fine.

Blindman I tested but and not sure what you meant by @.TestDate, I tried a few scenarios and none worked.

Wheatster|||@.TestDate is the value you are checking to see what group range it belongs in.

select dateadd(minute, datediff(minute, @.StartTime, [YourDateTimeValue])/@.IntervalMinutes, @.StartTime) as Range,
.
.
.
from YourTable
group by dateadd(minute, datediff(minute, @.StartTime, [YourDateTimeValue])/@.IntervalMinutes, @.StartTime)

Grouping Data Based On Return From Stored Procedure

I'm having some difficulty getting the appropriate results for my scenerio. I have two different datasets that I'm using. One is consisting of two joined tables and the other consisting of one sp. The sp's parameters rely on two things- one is the companyNum (inputed when the user runs the report) and two is the ContactNumType. The ContactTypeNum comes from the dataset of tables. I need to have a table consisting of this format:

ContactNumType1 (From the Tables)
File_Name1 (From the sp)
File_Name4 (From the sp)
File_Name3 (From the sp)

ContactNumType2 (From the Tables)
File_Name2 (From the sp)
File_Name7(From the sp)

ContactNumType3 (From the Tables)
File_Name5 (From the sp)

ContactNumType4 (From the Tables)
File_Name6 (From the sp)

File_Name10 (From the sp)
File_Name8(From the sp)
File_Name9 (From the sp)

So essentially what is going on is that every returned File_Name is grouped based upon the type of ContactNumType. My table returns the appropriate ContactNumTypes and the appropriate number of File_Names but returns only the first File_Name for each row. The File_Names should only grouped by the ContactTypeNums and each be unique. Is there any way to do that?

-
Edited: I still am trying to work this out. I've tried a few run-arounds but none have worked. Adding custom code apparently is too risky at this point because of the security precautions that I've been instructed to take. Any help would be greatly appreciated as this project has been going on for days now....

If I understand you correctly, the problem here is that the argument to the procedure needs come from the results of your two-table join. Question: Are you running SQL Server 2005 or SQL Server 2000? If you are running SQL Server 2005, I would suggest converting your stored procedure into a table function (if possible). If you can do this, then you will be able to use a CROSS APPLY join and pass the arguments to the newly written function based on the results of your two table join.|||The solution involved creating a new stored procedure combining the table dataset and the stored procedure. The one dataset made it much easier to directly throw each dynamic field into a row.

Grouping common functionality in multiple stored procedures

Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing:

SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);

However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say:

SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);

The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :).

Appreciate if someone could help,

Oops i found the solution straight after i posted. User defined functions. Never realized you could return more than one value with a function in sql server. If there is a better solution please let me know but this seems to tick all the boxes.

Edit: I have discovered that this is not going to work for me since my stored procedure produces different columns (based on values passed in) and it appears that the Multi-statement Table-Value User-Defined Function requires you to specify the structure you will be outputting.

|||

>SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);

It is preferable to select just the columns you require.

>However my view has become so complicated that i had to convert itto a stored procedure called sp_Documents.
>The problem now though isthat is that i wish to do queries against the data returned but I can'tsimply say:
>SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);
>The only way i can see to do it is to create a stored procedure forevery single scenario i have passing in the appropriate values asparameters
It is tempting to code complicated IF ... SELECT ... ELSE SELECT ..., however it is generally best to a code one stored procedure for each permutation as then the query engine can optimise each variation. There are some situations where serial scanning of a table is an acceptable perfomance hit and it is possible to use the COALESCE trick to search any combination of 1 to N columns for specific value. For example if table FRED has non-null columns A through D and the sp has args &A to &D and for simplicity the allowed values are non-zero integer then:
IF &A = 0 SET &A = NULL
IF &B = 0 SET &B = NULL
IF &C = 0 SET &C = NULL
IF &D = 0 SET &D = NULL
SELECT A, B, C, D FROM FRED
WHERE COALESCE(&A, A) = A
AND COALESCE(&B, B) = B AND COALESCE(&C, C) = C AND COALESCE(&D, D) = D

If say &A is the only non-zero parameter then the effect select simplifies to SELECT A, B, C, D FROM FRED WHERE &A = A, as COALESCE selects the first non-null value.

sql

Grouping by week in stored procedure

Hi all,

I am using the below statement to get some dates grouped by date, in my SP.

SELECT TOP 100 PERCENT COUNT(dbo.test.CallDate) AS CallCount, year(dbo.test.CallDate) AS CallYear, datepart(wk, dbo.test.CallDate) AS [Week]
FROM dbo.test

LEFT OUTER JOIN dbo.view1 ON dbo.test.CallID = dbo.view1.CallID
LEFT OUTER JOIN dbo.view2 ON dbo.test.CallID = dbo.view2.CallID

WHERE (dbo.view1.[ACCOUNT ID] = @.accountid

OR (dbo.view2.[ACCOUNT ID] = @.accountid

AND (convert(varchar(10),dbo.test.CallDate,121) BETWEEN CONVERT(DATETIME, @.StartDate, 102)AND CONVERT(DATETIME, @.EndDate, 102))
GROUP BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)
ORDER BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)

i gave startdate as 1/1/2007 and endDate as 2/18/2007

i am getting the reuslt as
count year week
42 2001 32
2 2001 39
1 2001 51
1 2002 17
1 2002 19
106 2002 21
183 2002 22
226 2002 23
.........................
...........................
1208 2007 1
1292 2007 2

actually i should get only the last 2 rows.
Can anyone please point out, why i am getting the 2001, 2002 data? and how to fix that?

Thanks

Looks to me like you've got your bracketing wrong round your OR clause...

It's currently WHERE ( A OR (B AND C))

Shouldn't it be WHERE (A OR B) AND C?

|||

Thanks

Great help

It worked

Grouping based on multiple fields

I am using crystal report version 7.
I am linking the stored procedure to crystal report and display it's fields. I want to create the group having 2 fields and sum the amount field. At present, I can create group with only one field and sum the amount field based on this field.
How can I have the group defined by 2 fields?Create a formula joining the two fields:
{field1}+{field2}

and then group on that formula|||Thanks Anonymous2,
That resolved my problem!

Grouping

Hi,

I have a stored procedure which is returning results in the following format:

No.of substances Count

1 29

2 89

3 876

.. ..

15 56

16 89

Now i need to display like this:

No.of substances Count %

1 20

2 89

.. ..

>=9 8766

Total

All the substances which are >=9 have display no.of substances =>9 and Count is sum of all counts which have the no.of substances>=9

How to achieve this

Thanks in advance

Hi,

I done the grouping at stored procedure level.

Thanks

Grouped Stored Procedures

I'm doing some reading up and came across Grouped Stored Procedures.
What are the benefits, of Grouped Stored Procedures?
TIA JTC ^..^
If you refer to having several stored procedures with same name, differentiated by a number, such
as:
EXEC proc
EXEC proc;2
then you'll find that very few are using it, MS are not pushing this feature (just look at the lack
of support in their tools). I see it mostly as a backwards compatibility feature.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns960987A473AA7daveJTC@.217.32.252.50...
> I'm doing some reading up and came across Grouped Stored Procedures.
> What are the benefits, of Grouped Stored Procedures?
> TIA JTC ^..^
|||These work kind of like overloaded functions in traditional programming
languages. Depending on how the procedure is called (e.g. which suffix is
appended to the procedure name), you can dictate externally which version of
the procedure is actually called.
CREATE PROCEDURE dbo.myProc
AS
SELECT 1
GO
CREATE PROCEDURE dbo.myProc;2
AS
SELECT 2
GO
EXEC myProc
EXEC myProc;2
GO
The only benefit I know of is that you can drop them all with one fell
swoop.
-- drops all instances of myProc:
DROP PROCEDURE dbo.myProc
Note that there are a lot of negative side effects to using this feature.
Different management tools, and even different GUIs within SQL Server's own
management tools, will handle these procedures with varying degrees of
success and accuracy. Same goes for external tools such as source code and
project management software packages.
Note also that it is being deprecated (some future version of SQL Server
will no longer support them).
On 2/26/05 8:19 AM, in article Xns960987A473AA7daveJTC@.217.32.252.50, "JTC
^..^" <dave@.nospamJazzTheCat.co.uk> wrote:

> TIA JTC ^..^

Grouped Stored Procedures

I'm doing some reading up and came across Grouped Stored Procedures.
What are the benefits, of Grouped Stored Procedures?
TIA JTC ^..^If you refer to having several stored procedures with same name, differentia
ted by a number, such
as:
EXEC proc
EXEC proc;2
then you'll find that very few are using it, MS are not pushing this feature
(just look at the lack
of support in their tools). I see it mostly as a backwards compatibility fea
ture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns960987A473AA7daveJTC@.217.32.252.50...
> I'm doing some reading up and came across Grouped Stored Procedures.
> What are the benefits, of Grouped Stored Procedures?
> TIA JTC ^..^|||These work kind of like overloaded functions in traditional programming
languages. Depending on how the procedure is called (e.g. which suffix is
appended to the procedure name), you can dictate externally which version of
the procedure is actually called.
CREATE PROCEDURE dbo.myProc
AS
SELECT 1
GO
CREATE PROCEDURE dbo.myProc;2
AS
SELECT 2
GO
EXEC myProc
EXEC myProc;2
GO
The only benefit I know of is that you can drop them all with one fell
swoop.
-- drops all instances of myProc:
DROP PROCEDURE dbo.myProc
Note that there are a lot of negative side effects to using this feature.
Different management tools, and even different GUIs within SQL Server's own
management tools, will handle these procedures with varying degrees of
success and accuracy. Same goes for external tools such as source code and
project management software packages.
Note also that it is being deprecated (some future version of SQL Server
will no longer support them).
On 2/26/05 8:19 AM, in article Xns960987A473AA7daveJTC@.217.32.252.50, "JTC
^..^" <dave@.nospamJazzTheCat.co.uk> wrote:

> TIA JTC ^..^

Grouped Stored Procedures

I'm doing some reading up and came across Grouped Stored Procedures.
What are the benefits, of Grouped Stored Procedures?
TIA JTC ^..^If you refer to having several stored procedures with same name, differentiated by a number, such
as:
EXEC proc
EXEC proc;2
then you'll find that very few are using it, MS are not pushing this feature (just look at the lack
of support in their tools). I see it mostly as a backwards compatibility feature.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns960987A473AA7daveJTC@.217.32.252.50...
> I'm doing some reading up and came across Grouped Stored Procedures.
> What are the benefits, of Grouped Stored Procedures?
> TIA JTC ^..^|||These work kind of like overloaded functions in traditional programming
languages. Depending on how the procedure is called (e.g. which suffix is
appended to the procedure name), you can dictate externally which version of
the procedure is actually called.
CREATE PROCEDURE dbo.myProc
AS
SELECT 1
GO
CREATE PROCEDURE dbo.myProc;2
AS
SELECT 2
GO
EXEC myProc
EXEC myProc;2
GO
The only benefit I know of is that you can drop them all with one fell
swoop.
-- drops all instances of myProc:
DROP PROCEDURE dbo.myProc
Note that there are a lot of negative side effects to using this feature.
Different management tools, and even different GUIs within SQL Server's own
management tools, will handle these procedures with varying degrees of
success and accuracy. Same goes for external tools such as source code and
project management software packages.
Note also that it is being deprecated (some future version of SQL Server
will no longer support them).
On 2/26/05 8:19 AM, in article Xns960987A473AA7daveJTC@.217.32.252.50, "JTC
^..^" <dave@.nospamJazzTheCat.co.uk> wrote:
> TIA JTC ^..^

Group everything with the same first two letters

I'm working on a stored procedure that works fine. I just want to make it possible for the user to be able to have a drop down list in reporting services to display the "question codes" grouped by whatever the first two digits are. for example.

VT01

VT02

VT03

VN01

VN02

VN03

ST01

ST02

ST03

instead of listing everything, i want the viewers to see this

VT

VN

ST

or an alias for each of these like this:

Vet Tasks

Vet National

Survey Tasks

Survey National

any ideas, here's my current code, which is pullin up anything with the added substring part

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions]

(@.Region_Key int=null,@.QuestionCode char(5))

AS

BEGIN

SELECT dbo.Qry_Questions.Territory,

dbo.Qry_Questions.SalesResponsible,

dbo.Qry_Questions.Customer,

dbo.Qry_Questions.Date,

dbo.Qry_Questions.StoreName,

dbo.Qry_Questions.PostCode,

dbo.Qry_Questions.Address2,

dbo.Qry_Questions.[Question Code],

dbo.Qry_Questions.Question,

dbo.Qry_Questions.[Response Type],

dbo.Qry_Questions.response,

dbo.Qry_Questions.sales_person_code,

dbo.Qry_Sales_Group.Region_Key,

dbo.Qry_Sales_Group.Region

FROM dbo.Qry_Questions

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

WHERE REGION_KEY=@.Region_Key

AND SUBSTRING(dbo.Qry_Questions.[Question Code],0,3)=@.QuestionCode

END

SET NOCOUNT OFF

You should do the following:

1. Create a separate question code types table with a type column (this will be "VT", "VN", "ST" and so on) and description column

2. Create another table that maps the question code to the types table

3. Now, for display purposes you can show the data from types table

4. Similarly, for your query instead of using the information encoded in the value (using substring etc) just join with the code to types mapping table and filter on the type column

This approach will scale better, perform better and easier to manage. Currently, you are breaking normalization rules by inferring attributes from a value.

Group by Stored Procedure

If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a totals query. Is there a way to do that in sql?Can you post DDL (in the form of CREATE TABLE statments), sample data (in
the form of INSERT statements), and what sample output you're looking for?
I'm having a lot of trouble understanding your requirements from this
description and it will be easier to work with and talk about real data.
"drbobh" <drbobh@.discussions.microsoft.com> wrote in message
news:5E8ABE2B-2928-4B1D-B337-3565508DACFD@.microsoft.com...
> If I use a group by sp to group by id number, is there any way to get a
couple of fields in the last entry in the table for that id number. One
field is date so I can use max for that field, but how about the others. In
Access you can use Last in a totals query. Is there a way to do that in sql?|||It sounds like you're asking for something along these lines:
select
id_num, your_date, other_thing, something_else
from theTable T1
where your_date = (
select max(your_date)
from theTable T2
where T2.id_num = T1.id_num
)
Another way to get the result is
...
where not exists (
select * from Table T2
where T2.id_num = T1.id_num
and T2.your_date > T1.your_date
)
So it's either "get those rows where the date is the latest among all
rows with that id_num value" or "get those rows for which there is no
other row with the same id and a more recent date".
Note that if there are multiple rows with the latest date for a given
id, you will get them all, not just 1 as you would with the Access LAST
aggregate. The Access LAST function is convenient, but something like
it in SQL Server would not guarantee repeatable results and would create
more problems than it solved. I suspect LAST() depends on the order in
which the table rows are accessed, and in SQL Server, that depends on
things other than the indexes and the query.
Steve Kass
Drew University
drbobh wrote:
>If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a totals query. Is there a way to do that in sql?
>|||> In Access you can use Last in a totals query.
> Is there a way to do that in sql?
Maybe. But what would the "last" row be if not the row with the latest date?
If you post DDL for your table and explain what you mean by "last" then we
can help you with the query.
--
David Portas
SQL Server MVP
--|||Thanks Steve for the solution and the explanation, that's what I was looking for.
"Steve Kass" wrote:
> It sounds like you're asking for something along these lines:
> select
> id_num, your_date, other_thing, something_else
> from theTable T1
> where your_date = (
> select max(your_date)
> from theTable T2
> where T2.id_num = T1.id_num
> )
> Another way to get the result is
> ...
> where not exists (
> select * from Table T2
> where T2.id_num = T1.id_num
> and T2.your_date > T1.your_date
> )
> So it's either "get those rows where the date is the latest among all
> rows with that id_num value" or "get those rows for which there is no
> other row with the same id and a more recent date".
> Note that if there are multiple rows with the latest date for a given
> id, you will get them all, not just 1 as you would with the Access LAST
> aggregate. The Access LAST function is convenient, but something like
> it in SQL Server would not guarantee repeatable results and would create
> more problems than it solved. I suspect LAST() depends on the order in
> which the table rows are accessed, and in SQL Server, that depends on
> things other than the indexes and the query.
> Steve Kass
> Drew University
> drbobh wrote:
> >If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a totals query. Is there a way to do that in sql?
> >
> >
>

Group by Stored Procedure

If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a totals
query. Is there a way to do that in sql?
Can you post DDL (in the form of CREATE TABLE statments), sample data (in
the form of INSERT statements), and what sample output you're looking for?
I'm having a lot of trouble understanding your requirements from this
description and it will be easier to work with and talk about real data.
"drbobh" <drbobh@.discussions.microsoft.com> wrote in message
news:5E8ABE2B-2928-4B1D-B337-3565508DACFD@.microsoft.com...
> If I use a group by sp to group by id number, is there any way to get a
couple of fields in the last entry in the table for that id number. One
field is date so I can use max for that field, but how about the others. In
Access you can use Last in a totals query. Is there a way to do that in sql?
|||It sounds like you're asking for something along these lines:
select
id_num, your_date, other_thing, something_else
from theTable T1
where your_date = (
select max(your_date)
from theTable T2
where T2.id_num = T1.id_num
)
Another way to get the result is
...
where not exists (
select * from Table T2
where T2.id_num = T1.id_num
and T2.your_date > T1.your_date
)
So it's either "get those rows where the date is the latest among all
rows with that id_num value" or "get those rows for which there is no
other row with the same id and a more recent date".
Note that if there are multiple rows with the latest date for a given
id, you will get them all, not just 1 as you would with the Access LAST
aggregate. The Access LAST function is convenient, but something like
it in SQL Server would not guarantee repeatable results and would create
more problems than it solved. I suspect LAST() depends on the order in
which the table rows are accessed, and in SQL Server, that depends on
things other than the indexes and the query.
Steve Kass
Drew University
drbobh wrote:

>If I use a group by sp to group by id number, is there any way to get a couple of fields in the last entry in the table for that id number. One field is date so I can use max for that field, but how about the others. In Access you can use Last in a total
s query. Is there a way to do that in sql?
>
|||> In Access you can use Last in a totals query.
> Is there a way to do that in sql?
Maybe. But what would the "last" row be if not the row with the latest date?
If you post DDL for your table and explain what you mean by "last" then we
can help you with the query.
David Portas
SQL Server MVP
|||Thanks Steve for the solution and the explanation, that's what I was looking for.
"Steve Kass" wrote:
[vbcol=seagreen]
> It sounds like you're asking for something along these lines:
> select
> id_num, your_date, other_thing, something_else
> from theTable T1
> where your_date = (
> select max(your_date)
> from theTable T2
> where T2.id_num = T1.id_num
> )
> Another way to get the result is
> ...
> where not exists (
> select * from Table T2
> where T2.id_num = T1.id_num
> and T2.your_date > T1.your_date
> )
> So it's either "get those rows where the date is the latest among all
> rows with that id_num value" or "get those rows for which there is no
> other row with the same id and a more recent date".
> Note that if there are multiple rows with the latest date for a given
> id, you will get them all, not just 1 as you would with the Access LAST
> aggregate. The Access LAST function is convenient, but something like
> it in SQL Server would not guarantee repeatable results and would create
> more problems than it solved. I suspect LAST() depends on the order in
> which the table rows are accessed, and in SQL Server, that depends on
> things other than the indexes and the query.
> Steve Kass
> Drew University
> drbobh wrote:
als query. Is there a way to do that in sql?
>

Group by Stored Procedure

If I use a group by sp to group by id number, is there any way to get a coup
le of fields in the last entry in the table for that id number. One field is
date so I can use max for that field, but how about the others. In Access y
ou can use Last in a totals
query. Is there a way to do that in sql?Can you post DDL (in the form of CREATE TABLE statments), sample data (in
the form of INSERT statements), and what sample output you're looking for?
I'm having a lot of trouble understanding your requirements from this
description and it will be easier to work with and talk about real data.
"drbobh" <drbobh@.discussions.microsoft.com> wrote in message
news:5E8ABE2B-2928-4B1D-B337-3565508DACFD@.microsoft.com...
> If I use a group by sp to group by id number, is there any way to get a
couple of fields in the last entry in the table for that id number. One
field is date so I can use max for that field, but how about the others. In
Access you can use Last in a totals query. Is there a way to do that in sql?|||It sounds like you're asking for something along these lines:
select
id_num, your_date, other_thing, something_else
from theTable T1
where your_date = (
select max(your_date)
from theTable T2
where T2.id_num = T1.id_num
)
Another way to get the result is
...
where not exists (
select * from Table T2
where T2.id_num = T1.id_num
and T2.your_date > T1.your_date
)
So it's either "get those rows where the date is the latest among all
rows with that id_num value" or "get those rows for which there is no
other row with the same id and a more recent date".
Note that if there are multiple rows with the latest date for a given
id, you will get them all, not just 1 as you would with the Access LAST
aggregate. The Access LAST function is convenient, but something like
it in SQL Server would not guarantee repeatable results and would create
more problems than it solved. I suspect LAST() depends on the order in
which the table rows are accessed, and in SQL Server, that depends on
things other than the indexes and the query.
Steve Kass
Drew University
drbobh wrote:

>If I use a group by sp to group by id number, is there any way to get a couple of f
ields in the last entry in the table for that id number. One field is date so I can
use max for that field, but how about the others. In Access you can use Last in a to
tal
s query. Is there a way to do that in sql?
>|||> In Access you can use Last in a totals query.
> Is there a way to do that in sql?
Maybe. But what would the "last" row be if not the row with the latest date?
If you post DDL for your table and explain what you mean by "last" then we
can help you with the query.
David Portas
SQL Server MVP
--|||Thanks Steve for the solution and the explanation, that's what I was looking
for.
"Steve Kass" wrote:

> It sounds like you're asking for something along these lines:
> select
> id_num, your_date, other_thing, something_else
> from theTable T1
> where your_date = (
> select max(your_date)
> from theTable T2
> where T2.id_num = T1.id_num
> )
> Another way to get the result is
> ...
> where not exists (
> select * from Table T2
> where T2.id_num = T1.id_num
> and T2.your_date > T1.your_date
> )
> So it's either "get those rows where the date is the latest among all
> rows with that id_num value" or "get those rows for which there is no
> other row with the same id and a more recent date".
> Note that if there are multiple rows with the latest date for a given
> id, you will get them all, not just 1 as you would with the Access LAST
> aggregate. The Access LAST function is convenient, but something like
> it in SQL Server would not guarantee repeatable results and would create
> more problems than it solved. I suspect LAST() depends on the order in
> which the table rows are accessed, and in SQL Server, that depends on
> things other than the indexes and the query.
> Steve Kass
> Drew University
> drbobh wrote:
>
als query. Is there a way to do that in sql?[vbcol=seagreen]
>

Group By in Xml

Hi everybody!
First of all, execute me for my english, because I'm not English ...
I've got this kind Xml stored in a column (tmx) of my db (the name of
the table is TA_TMX) :
<tmx>
<header>
...
</header>
<body>
<tu>
<tuv lang=3D"EN">
<seg> Hello </seg>
</tuv>
<tuv lang=3D"Es">
<seg> Hola </seg>
</tuv>
</tu>
<tu>
<tuv lang=3D"EN">
<seg> Bye </seg>
</tuv>
<tuv lang=3D"Es">
<seg> Adios </seg>
</tuv>
</tu>
</body>
</tmx>
I want to do a query to obtain this:
IdTu Language Text
-- -- --
1 EN Hello
1 ES Hola
2 EN Bye
2 ES Adios
I mean, I want to get the language (lang attribute of tuv node) and the
text (text of seg node) of each tuv node. I've done this like this and
it works.
select ref.value('@.lang[1]','nvarchar(10)') as Language,
ref.value('seg[1]','nvarchar(max)') as Text
from ta_tmx cross apply tmx.nodes('/tmx/body/tu/tuv') as
R(ref)
where id_tmx=3D1
Besides I would like to know if it's possible to get the number of tu
node which belongs that tuv node to. So, =BFIs it possible to group by
tu node and get that column of numbers'
Thanks!Here's a solution, although I suspect there may
be a better way.
It requires a numbers table (created on the fly here).
with Digits(Num) as
(select 0 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9),
Numbers(Num) as
(select cast(a.Num + 10*b.Num as int)
from Digits a,Digits b)
select Numbers.Num,
ref.value('@.lang[1]','nvarchar(10)') as Language,
ref.value('seg[1]','nvarchar(max)') as Text
from ta_tmx
cross join Numbers
cross apply
tmx.nodes('/tmx/body/tu[position()=sql:column("Numbers.Num")]/tuv') as
R(ref)|||Slight improvement, change
cross join Numbers
to
inner join Numbers on Num between 1 and
tmx.value('count(/tmx/body/tu)','int')|||A simpler way
select ref.value('for $a in .. return count(../../*[. << $a]) + 1',
'int') as IdTu,
ref.value('@.lang[1]','nvarchar(10)') as Language,
ref.value('seg[1]','nvarchar(max)') as Text
from ta_tmx
cross apply tmx.nodes('/tmx/body/tu/tuv') as R(ref)
where id_tmx=1|||It works. Thank you very much.
Would you mind explaining me what you're doing here ?
" ref.value('for $a in .. return count(../../*[. << $a]) + 1', 'int')",
because I don't understand it.
Thanks in advance for your help.
Regards.|||for $a in .. return count(../../*[. << $a]) + 1
Here's a brief explanation of the above
The query uses nodes('/tmx/body/tu/tuv')
which generates subtrees from tuv downwards
such that . starts at the tuv level.
.. is the nodes parent.
"for a$ in .." assigns a$ to the current nodes parent (the tu node).
"count(../../*[. << $a])" returns the number of nodes
at the "tu" level that precedes the current node. The
../../* simply navigates back to the root from the "tu" node.
As this count won't include the current node we add one to give
the desired result.

Group By Error Under SQL 2005

Our stored procedure throws the following error when running on 2005
w/compatibility set to 90, but not 80:
"Each GROUP BY expression must contain at least one column that is not an
outer reference. Severity 15, State 1, Procedure "procname", line 351"
The code in question is:
....
OR
(EXISTS (SELECT e.logical_seat_row, b.logical_seat_num, count(*)
FROM #ZoomSet e
WHERE e.logical_seat_num >= b.logical_seat_num
and e.logical_seat_num <= c.logical_seat_num
and e.bit_col & 64 = 64
and e.logical_seat_row = b.logical_seat_row
GROUP BY e.logical_seat_row, b.logical_seat_num
HAVING count(*) >= @.num_wc_ind))
)
.....
I've searched support.msft.com, as well as this newsgroup, and all of the
web, but can't find this error anywhere. Any ideas would be appreciated.
Thanks!
Steven Bras
Tessitura Network, Inc.>> Our stored procedure throws the following error when running on 2005 w/co
mpatibility set to 90, but not 80:: "Each GROUP BY expression must contain a
t least one column that is not an outer reference. Severity 15, State 1, Pr
ocedure "procname", line 35
1" <<
As best I can tell from the fragment posted, the code lookd fine from a
standards viewpoint (ignoring the bit operator crap). I would clean it
up for human use (one BETWEEN is easier to read and understand than two
comparisons) and see if that helps.
Since it is an EXISTS() predicate, use the * instead of a list; my
thought is that the engine might be trying to build the list when all
it needs to do is find is one row.
OR
(EXISTS (SELECT *
FROM #ZoomSet AS E
WHERE E.logical_seat_num
BETWEEN B.logical_seat_num
AND C.logical_seat_num
AND E.bit_col & 64 = 64
AND E.logical_seat_row = B.logical_seat_row
GROUP BY E.logical_seat_row, B.logical_seat_num
HAVING COUNT (*) >= @.num_wc_ind))
)
The other things are to re-write the whole query to get rid of the
temp table and the assembly language bit fiddling.
It looks like you are trying to find a block of vacant seats on the
same row. Ihave queries for that in SQL FOR SMARTIES which are
simpler.|||Thanks; I do appreciate your response and am a long-standing admirer of your
columns and books.
But why does the error now occur under 2005 where it didn't used to under
SQL 2000?
--
Steven Bras
Tessitura Network, Inc.
"--CELKO--" wrote:

351" <<
> As best I can tell from the fragment posted, the code lookd fine from a
> standards viewpoint (ignoring the bit operator crap). I would clean it
> up for human use (one BETWEEN is easier to read and understand than two
> comparisons) and see if that helps.
> Since it is an EXISTS() predicate, use the * instead of a list; my
> thought is that the engine might be trying to build the list when all
> it needs to do is find is one row.
> OR
> (EXISTS (SELECT *
> FROM #ZoomSet AS E
> WHERE E.logical_seat_num
> BETWEEN B.logical_seat_num
> AND C.logical_seat_num
> AND E.bit_col & 64 = 64
> AND E.logical_seat_row = B.logical_seat_row
> GROUP BY E.logical_seat_row, B.logical_seat_num
> HAVING COUNT (*) >= @.num_wc_ind))
> )
> The other things are to re-write the whole query to get rid of the
> temp table and the assembly language bit fiddling.
> It looks like you are trying to find a block of vacant seats on the
> same row. Ihave queries for that in SQL FOR SMARTIES which are
> simpler.
>|||> But why does the error now occur under 2005 where it didn't used to under
> SQL 2000?
In the Books Online topic 'sp_dbcmptlevel', it states the following:
Compatibility level setting of 80 A GROUP BY clause in a subquery that
references a column from the outer query succeeds.
Compatibility level setting of 90 A GROUP BY clause in a subquery that
references a column from the outer query returns an error as per the SQL
standard.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"StevenBr" <sbras@.community.nospam> wrote in message
news:ACFBFA3A-8CAF-4875-A83F-38607B5BDC23@.microsoft.com...
> Thanks; I do appreciate your response and am a long-standing admirer of
> your
> columns and books.
> But why does the error now occur under 2005 where it didn't used to under
> SQL 2000?
> --
> Steven Bras
> Tessitura Network, Inc.
>
> "--CELKO--" wrote:
>|||n Mon, 26 Jun 2006 17:39:15 -0700, "Gail Erickson [MS]"
<gaile@.online.microsoft.com> wrote:

> In the Books Online topic 'sp_dbcmptlevel', it states the following:
>Compatibility level setting of 80 A GROUP BY clause in a subquery that
>references a column from the outer query succeeds.
>Compatibility level setting of 90 A GROUP BY clause in a subquery that
>references a column from the outer query returns an error as per the SQL
>standard.
Interesting.
It may also be worth pointing out why this would be so. Any reference
within the subquery that refers to the outer query is, for the purpose
of the subquery, a reference to a constant. And there is no reason to
include a constant in a GROUP BY list.
In the specific example posted:

>....
> OR
> (EXISTS (SELECT e.logical_seat_row, b.logical_seat_num, count(*)
> FROM #ZoomSet e
> WHERE e.logical_seat_num >= b.logical_seat_num
> and e.logical_seat_num <= c.logical_seat_num
> and e.bit_col & 64 = 64
> and e.logical_seat_row = b.logical_seat_row
> GROUP BY e.logical_seat_row, b.logical_seat_num
> HAVING count(*) >= @.num_wc_ind))
> )
>.....
it is the reference to b.logical_seat_num in the GROUP BY that is
redundant, since there can be only one value for any given evaluation
of the subquery. But we can go farther and observe that since
e.logical_seat_row = b.logical_seat_row, the reference to
e.logical_seat_row in the GROUP BY is also redundant.
Which means the entire GROUP BY clause is not required, as they
resolve to a single row, and since (as has already been pointed out)
the SELECT list for an EXISTS subquery should be *, the GROUP BY
should be redundant... BUT WAIT! Will it be legal to have a HAVING
clause reference an aggregate expression COUNT when the select list is
an *? Good question, I'm not sure.
--Quick test in 2000 and 2005, using system tables, demonstrates that
--it works!
select *
from sysobjects
where exists
(select * from syscolumns
where syscolumns.id = sysobjects.id
having count(*) > 30)
But it still looks funny to my sensitive nature. I think it might be
safer to rewrite without the EXISTS:
OR
(@.num_wc_ind <=
(SELECT count(*)
FROM #ZoomSet e
WHERE e.logical_seat_num >= b.logical_seat_num
and e.logical_seat_num <= c.logical_seat_num
and e.bit_col & 64 = 64
and e.logical_seat_row = b.logical_seat_row)
)
Roy Harvey
Beacon Falls, CT|||Hi,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.