I'm using the following query to determine the TotTons produced daily. A
sample data set is included.
On days that two grades are produced, I'd like the Grade name to be a
combination of the two grade names separated by a \. For example the desire
d
Grade name for 08-01-05 would be H\V. At this point I'm not concerned about
the order in which the letters appear in the new string.
Is this possible?
Thanks in advance,
Raul
SELECT
MAX(Datestamp1) AS DateStamp,
SUM(Tons) AS TotTons,
MAX(Grade) AS Grade 'just for example
FROM
(
SELECT
DateStamp as Datestamp1,
NumBatches,
Grade,
Tons
FROM
DailyTonsByGrade
) inrqry
GROUP BY Datestamp1
ORDER BY DateStamp ASC;
CREATE TABLE DailyTonsByGrade (
DateStamp smalldatetime,
NumBatches int,
Grade Varchar(20),
Tons real)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('06-01-05', 48, 'V', 403.2)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('07-01-05', 62, 'V', 520.8)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('08-01-05', 12, 'H', 112.8)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('08-01-05', 31, 'V', 285.6)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('09-01-05', 44, 'H', 413.6)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('10-01-05', 60, 'H', 564.0)
Or
DateStamp NumBatches Grade Tons
06-01-05 48 V 403.2
07-01-05 62 V 520.8
08-01-05 12 H 112.8
08-01-05 34 V 285.6
09-01-05 44 H 413.6
10-01-05 60 H 564would you ever have more than 2 grades on the same day ?
Message posted via http://www.webservertalk.com|||
In order to do that you need code that processes the multiple records in one
day's group, and outputs a concatenation of the Grade NAmes...
It can be done, but it involves row-processing (using a cursor, or a temp
table or table Variable) probably in a Stored Proc, or User efined FUnction.
One solution(using Latter)
would be
Create Functiondbo.FuelGrades(@.Dt DateTime)
Returns VarChar(500)
As
Begin
Declare @.Out VarChar(500) Set @.Out = ''
Declare @.Grade VarChar(50) Set @.Grade = ''
While Exists
(Select * From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade)
Select @.Out = @.Out + Min(Grade) + '/',
@.Grade = Min(Grade)
From DailyTonsByGrade
Where DateStamp = @.Dt
-- --
If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
Return @.Out
End
Then, in your query, just refer to this UDF...
Select Max(Datestamp) DateStamp,
Sum(Tons) TotTons,
dbo.FuelGrades(Datestamp) Grade
From DailyTonsByGrade
Group By Datestamp
Order By Max(Datestamp);
This will work, but performance will be poor...
"Raul" wrote:
> I'm using the following query to determine the TotTons produced daily. A
> sample data set is included.
> On days that two grades are produced, I'd like the Grade name to be a
> combination of the two grade names separated by a \. For example the desi
red
> Grade name for 08-01-05 would be H\V. At this point I'm not concerned abo
ut
> the order in which the letters appear in the new string.
> Is this possible?
> Thanks in advance,
> Raul
> SELECT
> MAX(Datestamp1) AS DateStamp,
> SUM(Tons) AS TotTons,
> MAX(Grade) AS Grade 'just for example
> FROM
> (
> SELECT
> DateStamp as Datestamp1,
> NumBatches,
> Grade,
> Tons
> FROM
> DailyTonsByGrade
> ) inrqry
> GROUP BY Datestamp1
> ORDER BY DateStamp ASC;
> CREATE TABLE DailyTonsByGrade (
> DateStamp smalldatetime,
> NumBatches int,
> Grade Varchar(20),
> Tons real)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('06-01-05', 48, 'V', 403.2)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('07-01-05', 62, 'V', 520.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 12, 'H', 112.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 31, 'V', 285.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('09-01-05', 44, 'H', 413.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('10-01-05', 60, 'H', 564.0)
> Or
> DateStamp NumBatches Grade Tons
> 06-01-05 48 V 403.2
> 07-01-05 62 V 520.8
> 08-01-05 12 H 112.8
> 08-01-05 34 V 285.6
> 09-01-05 44 H 413.6
> 10-01-05 60 H 564
>|||opps, I left out something...
THe UDF should be
Create Functiondbo.FuelGrades(@.Dt DateTime)
Returns VarChar(500)
As
Begin
Declare @.Out VarChar(500) Set @.Out = ''
Declare @.Grade VarChar(50) Set @.Grade = ''
While Exists
(Select * From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade)
Select @.Out = @.Out + Min(Grade) + '/',
@.Grade = Min(Grade)
From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade -- This is line I left Out
-- --
If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
Return @.Out
End
"Raul" wrote:
> I'm using the following query to determine the TotTons produced daily. A
> sample data set is included.
> On days that two grades are produced, I'd like the Grade name to be a
> combination of the two grade names separated by a \. For example the desi
red
> Grade name for 08-01-05 would be H\V. At this point I'm not concerned abo
ut
> the order in which the letters appear in the new string.
> Is this possible?
> Thanks in advance,
> Raul
> SELECT
> MAX(Datestamp1) AS DateStamp,
> SUM(Tons) AS TotTons,
> MAX(Grade) AS Grade 'just for example
> FROM
> (
> SELECT
> DateStamp as Datestamp1,
> NumBatches,
> Grade,
> Tons
> FROM
> DailyTonsByGrade
> ) inrqry
> GROUP BY Datestamp1
> ORDER BY DateStamp ASC;
> CREATE TABLE DailyTonsByGrade (
> DateStamp smalldatetime,
> NumBatches int,
> Grade Varchar(20),
> Tons real)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('06-01-05', 48, 'V', 403.2)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('07-01-05', 62, 'V', 520.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 12, 'H', 112.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 31, 'V', 285.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('09-01-05', 44, 'H', 413.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('10-01-05', 60, 'H', 564.0)
> Or
> DateStamp NumBatches Grade Tons
> 06-01-05 48 V 403.2
> 07-01-05 62 V 520.8
> 08-01-05 12 H 112.8
> 08-01-05 34 V 285.6
> 09-01-05 44 H 413.6
> 10-01-05 60 H 564
>|||if you are NEVER going to exceed 2 grade on any one day then this should
work
SELECT
MAX(Datestamp) AS DateStamp,
SUM(Tons) AS TotTons,
case
when MAX(Grade) <> Min(grade) then MAX(Grade)+ ''+ Min(grade)
else min(grade)
end AS Grade --just for example
FROM
DailyTonsByGrade
GROUP BY Datestamp
ORDER BY DateStamp ASC
Message posted via http://www.webservertalk.com|||It is unlikely that we will produce more than two grades in one day.
This is a pretty clever solution.
Thanks a bunch,
Raul
"baie dronk via webservertalk.com" wrote:
> if you are NEVER going to exceed 2 grade on any one day then this should
> work
> SELECT
> MAX(Datestamp) AS DateStamp,
> SUM(Tons) AS TotTons,
> case
> when MAX(Grade) <> Min(grade) then MAX(Grade)+ ''+ Min(grade)
> else min(grade)
> end AS Grade --just for example
> FROM
> DailyTonsByGrade
> GROUP BY Datestamp
> ORDER BY DateStamp ASC
> --
> Message posted via http://www.webservertalk.com
>|||I'll try this solution also.
Thank you,
Raul
"CBretana" wrote:
> opps, I left out something...
> THe UDF should be
> Create Functiondbo.FuelGrades(@.Dt DateTime)
> Returns VarChar(500)
> As
> Begin
> Declare @.Out VarChar(500) Set @.Out = ''
> Declare @.Grade VarChar(50) Set @.Grade = ''
> While Exists
> (Select * From DailyTonsByGrade
> Where DateStamp = @.Dt
> And Grade > @.Grade)
> Select @.Out = @.Out + Min(Grade) + '/',
> @.Grade = Min(Grade)
> From DailyTonsByGrade
> Where DateStamp = @.Dt
> And Grade > @.Grade -- This is line I left Out
> -- --
> If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
> Return @.Out
> End
>
> "Raul" wrote:
>
