Excluding repeated values in a sum

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z
Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

Could you sum the totals and divide by the rowcount? This would give an accurate total for the repeated values within the group, but I haven't tested it to know if you can accurately retrieve a grand total.|||Otherwise known as the AVG function. Guess I'm not thinking to well this morning. With that being the case, there would be an issue with the Grand Total.|||Hi Simone,

Yeah the grand total would not work for that, otherwise it would work I guess.. any other ideas?

Thanks,
Neil
|||

Hello Neil,

One thing you could try is to create a public variable and a function to increment your variable (via your code window), then in each detail row, increment if the previous record's contract number is different from the current number.

In each detail row, call your function to increment the value. =Code.IncrementValue(IIf(Previous(Fields!ContractNum.Value) <> Fields!ContractNum.Value, Fields!ContractNum.Value, 0))

In your total textbox, display your variable. =Code.ContractTotal

I haven't tried this, but hopefully it will get you started.

Hope this helps.

Jarret

Exclude weekends

Hi I have simple SELECT query
SELECT StartDateTime, EndDateTime, Machines.[Name],
SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
,CycleTime
FROM ProductionData
INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
WHERE MachineID = 1 AND Quantity > 50
and I want to exclude wends from the Duration field
any suggestions much appreciatedGary
What is a datatype of Duration column?
Is it datetime or int/smallint/tinyint/varchar/char/text/ntext?
Why not to post DDL?
"Gary Spence" <GarySpence@.discussions.microsoft.com> wrote in message
news:21B45505-FD93-4FD3-8479-9AE36D36D4A2@.microsoft.com...
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||You could use the DATEPART function to determine if the day you are quering
is a wday.
...
WHERE DATEPART(dw, yourcolumn) IN (7,1) (Saturday and Sunday in English
settings)
The Values (7,1) depends on your language settings or the settings of your
SET @.@.DATEFIRST
Look in the help of BOL to gather more information about that.
HTH, Jens Suessmeyer.
"Gary Spence" <GarySpence@.discussions.microsoft.com> schrieb im Newsbeitrag
news:21B45505-FD93-4FD3-8479-9AE36D36D4A2@.microsoft.com...
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||check out the datename function so you can exclude saturdays and sundays.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Gary Spence" wrote:

> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||set datefirst 1 -- monday = 1st day of the w
add following to the WHERE filter, to exclude both StartDateTime and
EndDateTime that falls on Sat. & Sun.
(datepart(dw, StartDateTime) in (6,7) or datepart(dw, EndDateTime) in
(6,7) )
"Gary Spence" <GarySpence@.discussions.microsoft.com> wrote in message
news:21B45505-FD93-4FD3-8479-9AE36D36D4A2@.microsoft.com...
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||Hi Gary
Here is the query:
Hi I have simple SELECT query
SELECT StartDateTime, EndDateTime, Machines.[Name],
SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
,CycleTime
FROM ProductionData
INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
WHERE MachineID = 1 AND Quantity > 50
AND DATEPART(dw, StartDateTime) NOT IN (1,7)
AND DATEPART(dw, EndDateTime) NOT IN (1,7)
Please let me know your comments
best Regards,
Chandra
---
"Gary Spence" wrote:

> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||Thanks Chandra
It works great
"Chandra" wrote:
> Hi Gary
> Here is the query:
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> AND DATEPART(dw, StartDateTime) NOT IN (1,7)
> AND DATEPART(dw, EndDateTime) NOT IN (1,7)
> Please let me know your comments
> --
> best Regards,
> Chandra
> ---
>
> "Gary Spence" wrote:
>|||Hi Gary,
I have seen many replies for your question but those would not help if the
START DATE TIME and END DATE TIME doesn’t belong to same day.
Example:-
START DATE TIME = ’29-Apr-2005 23:50’
END DATE TIME = ’02-May-2005 00:01’
I would prefer to have a function that would calculate duration in minutes
excluding wends, i.e. from SAT 00:00 hrs to SUN 23:59 hrs.
Here is the function:
create function dbo.GetWDayMinutes
(
@.dtStart datetime,
@.dtEnd datetime
)
returns int
as
begin
declare @.dtTemp datetime
declare @.intMinutes int
select @.dtTemp = dateadd(hh, - datepart(hh, @.dtStart), @.dtStart)
select @.dtTemp = dateadd(mi, - datepart(mi, @.dtTemp), @.dtTemp)
set @.intMinutes = 0
while ( @.dtTemp <= @.dtEnd )
begin
if datepart(dw, @.dtTemp) not in (1, 7)
begin
set @.intMinutes = @.intMinutes
+ datediff(mi,
case when @.dtTemp < @.dtStart then @.dtStart else @.dtTemp end,
case when dateadd(dd, 1, @.dtTemp) > @.dtEnd then @.dtEnd else dateadd(dd,
1, @.dtTemp) end)
end
set @.dtTemp = @.dtTemp + 1
end
return @.intMinutes
end
Sample:
select dbo.GetWDayMinutes ('29-Apr-2005 23:50', '02-May-2005 00:01')
this will return 11 minutes.
So you have to rewrite you statement as:
SELECT StartDateTime, EndDateTime, Machines.[Name],
SUM(dbo.GetWDayMinutes (StartDateTime, EndDateTime)) As Duration,
Quantity
,CycleTime
FROM ProductionData
INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
WHERE MachineID = 1 AND Quantity > 50
Hope this helps you.
Best Regards,
Lakshman.
"Gary Spence" wrote:

> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||Hi Lakshman,
Your solution is to deduct the wend time computation from the duration.
But if to exclude all duration computation that falls on the wend, I've
suggested the following:
SELECT * FROM
( -- testing data
select '29-Apr-2005 23:50' AS d_start, '02-May-2005 00:01' AS d_end
UNION
select '14-Apr-2005 23:50' AS d_start, '15-Apr-2005 00:01' AS d_end -- only
this will record to be returned
UNION
select '11-Jun-2005 23:50' AS d_start, '12-Jun-2005 00:01' AS d_end
) a
WHERE ((datepart(dw,d_start) NOT IN (6,7) AND datepart(dw,d_end) NOT IN
(6,7)))
AND
(6 NOT BETWEEN datepart(dw,d_start) AND (datepart(dw,d_start) +
datediff(day, d_start, d_end)) OR
7 NOT BETWEEN datepart(dw,d_start) AND (datepart(dw,d_start) +
datediff(day, d_start, d_end))
)
"Lakshman" <Lakshman@.discussions.microsoft.com> wrote in message
news:DF45EB13-032D-435C-BBAD-7E51B6C0CDF3@.microsoft.com...
> Hi Gary,
> I have seen many replies for your question but those would not help if the
> START DATE TIME and END DATE TIME doesn't belong to same day.
> Example:-
> START DATE TIME = '29-Apr-2005 23:50'
> END DATE TIME = '02-May-2005 00:01'
> I would prefer to have a function that would calculate duration in minutes
> excluding wends, i.e. from SAT 00:00 hrs to SUN 23:59 hrs.
> Here is the function:
> create function dbo.GetWDayMinutes
> (
> @.dtStart datetime,
> @.dtEnd datetime
> )
> returns int
> as
> begin
> declare @.dtTemp datetime
> declare @.intMinutes int
> select @.dtTemp = dateadd(hh, - datepart(hh, @.dtStart), @.dtStart)
> select @.dtTemp = dateadd(mi, - datepart(mi, @.dtTemp), @.dtTemp)
> set @.intMinutes = 0
> while ( @.dtTemp <= @.dtEnd )
> begin
> if datepart(dw, @.dtTemp) not in (1, 7)
> begin
> set @.intMinutes = @.intMinutes
> + datediff(mi,
> case when @.dtTemp < @.dtStart then @.dtStart else @.dtTemp end,
> case when dateadd(dd, 1, @.dtTemp) > @.dtEnd then @.dtEnd else dateadd(dd,
> 1, @.dtTemp) end)
> end
> set @.dtTemp = @.dtTemp + 1
> end
> return @.intMinutes
> end
>
> Sample:
> select dbo.GetWDayMinutes ('29-Apr-2005 23:50', '02-May-2005 00:01')
> this will return 11 minutes.
> So you have to rewrite you statement as:
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(dbo.GetWDayMinutes (StartDateTime, EndDateTime)) As Duration,
> Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> Hope this helps you.
> Best Regards,
> Lakshman.
>
> "Gary Spence" wrote:
>