Adding User Define fields together

This is driving me crazy! The SQL Statement refenced is shown at the end of this email.

When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?

SQL View......

SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,

Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR

(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total

FROM dbo.QRY_FacNew_Term

WHERE (Exclude <> 'Y')

ORDER BY Category, Client_NameUse brackets if name includes space:

[drawn_GT5MIL_LE365Days ] = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

adding two view columns together

I have created a new view where I joined two other views I want to use in
reporting services. I want to add two columns one from each view. The join
allows all records from both views. This creates a lot of null values in one
of the columns I want to add and therefore I get a null value as the answer
instead of the desired result of adding the two columns. Is there a way to
put zeros in place of the nulls within the select statement so that all
records will add ?
Ex: Col1 + Col2 = Col3
2 3 5
null 2 null (desired answer is 2)
I have been able to do this by using a temporary table but that makes things
more complicated.
Thank you for your help.Use the SQL statement IsNull. From books online:
USE AdventureWorks;
GO
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max
Quantity'
FROM Sales.SpecialOffer;
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JD" <jim@.microsoft.com> wrote in message
news:emMfrtvKIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have created a new view where I joined two other views I want to use in
>reporting services. I want to add two columns one from each view. The join
>allows all records from both views. This creates a lot of null values in
>one of the columns I want to add and therefore I get a null value as the
>answer instead of the desired result of adding the two columns. Is there a
>way to put zeros in place of the nulls within the select statement so that
>all records will add ?
> Ex: Col1 + Col2 = Col3
> 2 3 5
> null 2 null (desired answer is 2)
> I have been able to do this by using a temporary table but that makes
> things more complicated.
> Thank you for your help.
>|||You can also use in reporting services in your Col3 the following expression
=cint(Fields!Col1) + cint(Fields!Col2)
and of course, you can use cint function in your Col1 and Col2 columns.
Hope this helps,
Mónica
"JD" <jim@.microsoft.com> escribió en el mensaje
news:emMfrtvKIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have created a new view where I joined two other views I want to use in
>reporting services. I want to add two columns one from each view. The join
>allows all records from both views. This creates a lot of null values in
>one of the columns I want to add and therefore I get a null value as the
>answer instead of the desired result of adding the two columns. Is there a
>way to put zeros in place of the nulls within the select statement so that
>all records will add ?
> Ex: Col1 + Col2 = Col3
> 2 3 5
> null 2 null (desired answer is 2)
> I have been able to do this by using a temporary table but that makes
> things more complicated.
> Thank you for your help.
>

Adding two group sums

Hi guys,

I'm very new to Crystal reports and I've scoured the internet looking how to add two 'sums together in a field.

In group header 3 I'm trying to add the following two sums together

Sum ({WMLocnHandlingMediaConf.QtyStor}

and

Sum ({WMLocnHandlingMediaConf.QtyIn}

And I really don't have a clue on how to do, I am a novice on Crystal and this is probably the most complicated thing I've attempted to do.

If anyone can help it would be very much apperciated.

Many Thanks
NickTry inserting and configuring a Running Total Field|||Make sure the the two sums you have already created are physically on the report. (If you do not wanrt to show them just format/suppress).

Create a new formula. When looking for the two fields to sum in the formula, look into report fields. You should see the tweo previously created sums.

Adding to outputs together to retrieve the top(10) - Is it possible?

Hi There,

I have been struggling day and night with the creation of a store procedure due to not being able to retieve the rows I need for SUM and AVG functions.

I have two tables ('actions' & 'incident_types') which both have a score value for each record. In my database, I have reports that contain both action codes and incident_type codes based on a personnel_code.

In simple terms I'm trying to do the following:-

For all reports, find each personnel member and thier attached incident_types and action codes and then, for each score from the actions and incident_types tables, create the SUM of the 'combined' scores.

I have successfully retrieved the output for the scores individually but I need the TOP(10) of the combined output.....

I'm currently using to seperate queries as follows :--

--This gives me the incident type output...... Creating the 'Volume' column which is the total Score for incidetn_types

SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(incident_types.type_score) as 'Volume', avg(incident_types.type_score) as 'Average' INTO 'Incident_Scores' from Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND report_basedon.personnel_code <> 0

AND report_header.record_status=@.recordStatus

group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname

-- I then use the following to get the total of all action scores, again in the 'Volume' column

SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(actions.action_score) as 'Volume', AVG(actions.action_score) as 'Average' from profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND personnel_details.personnel_code <> 0

AND report_header.record_status=@.recordStatus

AND profile.record_status=@.recordStatus

group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname

So my question is - How can I get the sum(incident_types.type_score) + sum(actions.action_score) and then get the TOP(10) rows?

Is it possible to output these 2 result to a new table and then join the new tables?

Thanks for any assistance, this is really draining me at the moment..... :-/

here it is,

Select TOP (@.Number)

incident_Data.personnel_code

, incident_Data.personnel_forename

, incident_Data.personnel_Surname

, incident_Data.Volume

, incident_Data.Average

, actions_data.Volume

, actions_data.Average

, incident_Data.Volume + actions_data.Volume as Total_Volume

, incident_Data.Average + actions_data.Average as Total_Average

from

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(incident_types.type_score) as 'Volume'

, avg(incident_types.type_score) as 'Average'

from

Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND report_basedon.personnel_code <> 0

AND report_header.record_status=@.recordStatus

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as incident_Data

Inner Join

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(actions.action_score) as 'Volume'

, AVG(actions.action_score) as 'Average'

from

profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND personnel_details.personnel_code <> 0

AND report_header.record_status=@.recordStatus

AND profile.record_status=@.recordStatus

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as actions_data

On actions_data.personnel_code = incident_Data.personnel_code

|||

Thank you so much for your reply this has really made my day!

I forgot to mention that the action record is not always present but the incident record is. Therefore I made the actions_data join = 'Left Outer Join' (see code below)... This gives me a problem in that when the action_data record contains 'NULL' the Volume is also 'NULL' - is it possible to make NULL = 0 [zero] when there is no action record?

Thanks again (see code and output).....

[code I've removed some of the selection criteria from previous post for testing purposes]

Select TOP (1000)

incident_Data.personnel_code

, incident_Data.personnel_forename

, incident_Data.personnel_Surname

, incident_Data.Volume

, incident_Data.Average

, actions_data.Volume

, actions_data.Average

, incident_Data.Volume + actions_data.Volume as Total_Volume

, incident_Data.Average + actions_data.Average as Total_Average

from

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(incident_types.type_score) as 'Volume'

, avg(incident_types.type_score) as 'Average'

from

Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE

report_basedon.personnel_code <> 0

AND report_header.record_status='1'

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as incident_Data

Left Outer Join

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(actions.action_score) as 'Volume'

, AVG(actions.action_score) as 'Average'

from

profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE

personnel_details.personnel_code <> 0

AND report_header.record_status='1'

AND profile.record_status='1'

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as actions_data

On actions_data.personnel_code = incident_Data.personnel_code

Order by incident_data.personnel_code

[example of results]

15 Oscar Freeman 0 0 NULL NULL NULL NULL
16 Sofia Daniels 0 0 NULL NULL NULL NULL
19 Megan Hardy 25 12 120 40 145 52
20 Finley Randall -25 -25 NULL NULL NULL NULL|||

observations

1. since actions_data subquery may be missing [i.e. OUTER JOIN] you should use this line instead

, incident_Data.Volume + ISNULL(actions_data.Volume,0) as Total_Volume

, incident_Data.Average + ISNULL(actions_data.Average,0) as Total_Average

2. the Total_Average calculation is flawed, e.g.
4 rows of incident_Data with average of 50 and 1 row of actions_data with average of 20 would yield a Total_Average of 70

when what you probably wanted was 44 (or 35 for simplicity if you don't care about weighting)

Dick

|||

Thank you Dick, for both points, your absolutly correct. I was so concerned with not being ale to extract the data that I hadn't thought this though. You've propably saved me a lot more pain!

Just for completeness, I had to go on and resolve a divide by zero error when calculating the final averages etc etc and I found a very useful link http://www.sql-server-helper.com/error-messages/msg-8134.aspx which shows 3 ways of dealing with this error.

Thanks again for everyones help - I can now get on with developing the program!!!!

|||

Yes. ISNULL will fix the issue... If the average is your issue the following query might be a best one..

Not sure why we need a TOP here.. It wont give any performance gain.

Select TOP (@.Number)

personnel_code

, personnel_forename

, personnel_Surname

, sum(case when type='incident' then Score end) as incident_volume

, avg(case when type='incident' then Score end) as incident_average

, sum(case when type='actions' then Score end) as actions_volume

, avg(case when type='actions' then Score end) as actions_average

, sum(Score) as Total_Volume

, avg(Score) as Total_Average

From

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, (incident_types.type_score) as 'Score'

, 'incident' Type

from

Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND report_basedon.personnel_code <> 0

AND report_header.record_status=@.recordStatus

UNION ALL

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, (actions.action_score) as 'Score'

, 'actions' Type

from

profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND personnel_details.personnel_code <> 0

AND report_header.record_status=@.recordStatus

AND profile.record_status=@.recordStatus

) as data

Group By

personnel_code

, personnel_forename

, personnel_Surname

Adding times together

Hi guys,

I have a field in my DB called EventDate as a DateTime field,
therefore it holds both the date and time together like this:
'2004-10-14 08:42:57.000'.

I need to add together all the times in this column for a particular
date range (BETWEEN).

Any suggestions will be great.

Thanks
Sunny:)Sunny K (sunstarwu@.yahoo.com) writes:
> I have a field in my DB called EventDate as a DateTime field,
> therefore it holds both the date and time together like this:
> '2004-10-14 08:42:57.000'.
> I need to add together all the times in this column for a particular
> date range (BETWEEN).

If I take you by the word, it sounds like the answer is:

SELECT SUM(datefiff(ss, convert(char(8), EventDate, 112), EventDate)
FROM tbl
WHERE EventDate BETWEEN ... AND ...

But it looks a little funny.

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You certainly can use BETWEEN with the DATETIME datatype but if you are
querying values with times other than midnight it's often more convenient to
use use >= and < instead of BETWEEN. For example

This:

SELECT *
FROM YourTable
WHERE eventdate >= '20041014'
AND eventdate < '20041015'

Is equivalent to this:

SELECT *
FROM YourTable
WHERE eventdate
BETWEEN '2004-10-14T00:00:00.000'
AND '2004-10-14T23:59:59.997'

Hope that answers your question.

--
David Portas
SQL Server MVP
--|||> I need to add together all the times in this column

I missed that bit from my first post - maybe because I've no idea what it
means! Just what would you expect to be the result of, for example
'2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
you want to add up a DATETIME?

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||Sunny K (sunstarwu@.yahoo.com) writes:
> Thats for the reply, and admitly i was very vague in what i meant to
> say. From your above example the time result of the two times would
> give me 08:42:57.000, as the time added was 00:00:00.000.
> Maybe this will help explain what i mean a bit better. Here is a few
> typical lines from my table:
> Name EventDate EventID
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>
> My 'exact' requirements are to SUM all the 6005 EventID times together
> and SUM all the 6006 EventID times together then find the difference
> between the two times. The dates in the column are of no use.
> Ive been banging my head over how to do this for a few days now. Any
> suggestions?

I repeat from my previous post:

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

In this case, the part with the desired result is very important,
because I am not sure what result you are looking for, and I don't
feel like guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
> Sunny K (sunstarwu@.yahoo.com) writes:
> > Thats for the reply, and admitly i was very vague in what i meant to
> > say. From your above example the time result of the two times would
> > give me 08:42:57.000, as the time added was 00:00:00.000.
> > Maybe this will help explain what i mean a bit better. Here is a few
> > typical lines from my table:
> > Name EventDate EventID
> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
> > My 'exact' requirements are to SUM all the 6005 EventID times together
> > and SUM all the 6006 EventID times together then find the difference
> > between the two times. The dates in the column are of no use.
> > Ive been banging my head over how to do this for a few days now. Any
> > suggestions?
> I repeat from my previous post:
> A common advice for this type of query is that you post
> o CREATE TABLE statement for your table.
> o INSERT statements with sample data.
> o The desired result, given the sample data.
> This make it easy to cut and paste and compose a tested solution.
> In this case, the part with the desired result is very important,
> because I am not sure what result you are looking for, and I don't
> feel like guessing.

Hi,

Here is the script to create the table with some sample data:

CREATE TABLE [dbo].[tbltemp23] (
[Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[EventDate] [datetime] NOT NULL ,
[EventID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)

Now I need to work out the the total time of all the 6006 EventIDs
(the date is here is not needed) which should equal: 93:32:16 then the
the total time of all the 6005 EventIDs which equals: 93:42:44. Then
finally find the difference between the two times, which should equal:
00:10:28 in this case.

I hope this is enough information.

Thanks
Sunny|||Thanks for the DDL and data.

SQL Server doesn't have a timespan data type. The query below uses
1900-01-01 as the base date from which durations are calculated, ignoring
the date component of the table data. You can format the returned values
according to your reporting requirements.

SELECT
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006) AS EventId6006Duration,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005) AS EventId6005Duration,
DATEADD(s,
DATEDIFF(s,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006),
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005)),
'19000101'
) AS EventDurationDifference

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sunny K" <sunstarwu@.yahoo.com> wrote in message
news:1ecdad8f.0411080132.4d6627fe@.posting.google.c om...
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
> news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
>> Sunny K (sunstarwu@.yahoo.com) writes:
>> > Thats for the reply, and admitly i was very vague in what i meant to
>> > say. From your above example the time result of the two times would
>> > give me 08:42:57.000, as the time added was 00:00:00.000.
>>> > Maybe this will help explain what i mean a bit better. Here is a few
>> > typical lines from my table:
>>> > Name EventDate EventID
>> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
>> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
>> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
>> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>>>> > My 'exact' requirements are to SUM all the 6005 EventID times together
>> > and SUM all the 6006 EventID times together then find the difference
>> > between the two times. The dates in the column are of no use.
>>> > Ive been banging my head over how to do this for a few days now. Any
>> > suggestions?
>>
>> I repeat from my previous post:
>>
>> A common advice for this type of query is that you post
>>
>> o CREATE TABLE statement for your table.
>> o INSERT statements with sample data.
>> o The desired result, given the sample data.
>>
>> This make it easy to cut and paste and compose a tested solution.
>>
>> In this case, the part with the desired result is very important,
>> because I am not sure what result you are looking for, and I don't
>> feel like guessing.
>
> Hi,
> Here is the script to create the table with some sample data:
>
> CREATE TABLE [dbo].[tbltemp23] (
> [Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [EventDate] [datetime] NOT NULL ,
> [EventID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.
> I hope this is enough information.
> Thanks
> Sunny|||Sunny K (sunstarwu@.yahoo.com) writes:
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.

To be honest, this still seem very strange to me. Sure, there is enough
information to write a solution, but somehow I wonder what is the real
problem.

Looking at your data, it seems that event 6006 means start and 6005
means end, and what you really are computing is the total duration of
all start-stop sequences. Given that, I wrote this query:

SELECT convert(char(8), dateadd(ss, SUM(diff), '19000101'), 108)
FROM (select diff = datediff(ss, a.EventDate,
(SELECT MIN(EventDate)
FROM tbltemp23 b
WHERE b.EventDate > a.EventDate
AND b.EventID = 6005))
FROM tbltemp23 a
WHERE a.EventID = 6006) AS c

Of course, this query breaks down if the 6006 and 6005 can come in
any order, but in that case I have no clue of what might be going on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What can I say you guys, you really know your stuff. Thanks for all
the help, its given me the exact results I've needed.

I will consider you guys when I face another problem:-P

Sunny

Adding Rows Together in SQL (SQL Server 2000)

Dear friends,
We are trying to achieve the following. Please advice
select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
from tbl1 a, tbl2 b
where b.data_id = a.id
and b.data_id=314
group by a.lastname,a.firstname,b.dev_address,b.dev_description
aaaa bbb 4234234 Pager
aaaa bbb 2342342 Work
aaaa bbb 23434 Home
aaaa bbb 23434 Cell Phone
kkk ccc 234234 Home
kkk ccc 23434 Cell
We want the result should be as follows:
aaaa bbb 4234234 Pager 2342342 Work 23434 Hom
e 23434 Cell Phone
kkk ccc 234234 Home 23434 Cell
Any ideas would be apprecaited.. we are using SQL Server 2000
ThanksHOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"Info Request" wrote:

> Dear friends,
> We are trying to achieve the following. Please advice
>
> select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
> from tbl1 a, tbl2 b
> where b.data_id = a.id
> and b.data_id=314
> group by a.lastname,a.firstname,b.dev_address,b.dev_description
>
> aaaa bbb 4234234 Pager
> aaaa bbb 2342342 Work
> aaaa bbb 23434 Home
> aaaa bbb 23434 Cell Phone
> kkk ccc 234234 Home
> kkk ccc 23434 Cell
> We want the result should be as follows:
> aaaa bbb 4234234 Pager 2342342 Work 23434 Hom
e 23434 Cell Phone
> kkk ccc 234234 Home 23434 Cell
> Any ideas would be apprecaited.. we are using SQL Server 2000
> Thanks
>|||Thanks a lot. This works great.|||Rotation works perfect as long as we have one work phone number, if we
have more than one work phone number then it breaks with "Subquery
returned more than 1 value" error message.
Any ideas?

Adding Rows Together in SQL (SQL Server 2000)

Dear friends,
We are trying to achieve the following. Please advice
select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
from tbl1 a, tbl2 b
where b.data_id = a.id
and b.data_id=314
group by a.lastname,a.firstname,b.dev_address,b.dev_descrip tion
aaaabbb4234234Pager
aaaabbb2342342Work
aaaabbb23434Home
aaaabbb23434Cell Phone
kkkccc234234Home
kkkccc23434Cell
We want the result should be as follows:
aaaa bbb4234234Pager2342342Work23434Home23434Cell Phone
kkk ccc234234Home23434Cell
Any ideas would be apprecaited.. we are using SQL Server 2000
Thanks
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/default...74&Product=sql
AMB
"Info Request" wrote:

> Dear friends,
> We are trying to achieve the following. Please advice
>
> select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
> from tbl1 a, tbl2 b
> where b.data_id = a.id
> and b.data_id=314
> group by a.lastname,a.firstname,b.dev_address,b.dev_descrip tion
>
> aaaabbb4234234Pager
> aaaabbb2342342Work
> aaaabbb23434Home
> aaaabbb23434Cell Phone
> kkkccc234234Home
> kkkccc23434Cell
> We want the result should be as follows:
> aaaa bbb4234234Pager2342342Work23434Home23434Cell Phone
> kkk ccc234234Home23434Cell
> Any ideas would be apprecaited.. we are using SQL Server 2000
> Thanks
>
|||Thanks a lot. This works great.
|||Rotation works perfect as long as we have one work phone number, if we
have more than one work phone number then it breaks with "Subquery
returned more than 1 value" error message.
Any ideas?

Adding rows together

Hello,
I'm having trouble finding a site with basic introduction to SQL Reporting
Services. I'm using the VS.nET reporting services IDE to build some reports
and I have a few questions.
How do I SUM two "report items" on my report? Like textbox12.value +
textbox13.value. I know how to do it with the "Fields" on my report but not
actualy report items.
Thanks
nTry using the "=ReportItems!Textbox12.value + ReportItems!Textbox13.value".
"nigel" wrote:
> Hello,
> I'm having trouble finding a site with basic introduction to SQL Reporting
> Services. I'm using the VS.nET reporting services IDE to build some reports
> and I have a few questions.
> How do I SUM two "report items" on my report? Like textbox12.value +
> textbox13.value. I know how to do it with the "Fields" on my report but not
> actualy report items.
> Thanks
> n

Adding multiple fields together

Okay hopefully this is quick and easy.

I have 3 fields (Annual Salary, Medical, Pension) and I now need to working out Cost To Company which is just adding those three together.

I tried to do that in a formula but it doesn't return me anything... This is what I had.

=(Sum(Fields!AnnualSalary.Value, "CoreEmployeeData")+Sum(Fields!AnnualMedicalAid.Value, "CoreEmployeeData")+Sum(Fields!AnnualPension.Value, "CoreEmployeeData"))

So it should add the 3 records?

While you are here :), Can you tell me how I can put a currency symbol in the front of these numbers and add the thousand comma delimiter?

ok, What is the "CoreEmployeeData" section, I would think this is affecting your calcs.

Also i would braket each individual sum too as below, I have also added in the ￡ and thousand delimiter too.

="￡" & round(cdec((Sum(Fields!AnnualSalary.Value))+(Sum(Fields!AnnualMedicalAid.Value))+(Sum(Fields!AnnualPension.Value), 2)), 2)

I have not tested this but should provide what you are looking for.

Andy

|||CoreEmployeeData is the DataSet name.
If I leave that out I get an out of scope error.

If I leave it there I still get no response at all....?|||Okay well it's working now.
I had to paste the code in the "Format" and "Value" fields.

Not sure why it was required in both but it seems to work now. I think I need some more practice :)

Thanks,
Gavin|||

the easiest way to get aorund this issue is split out your sum to individual columns, create 3 new columns in your report, and put the Sum() in each, make sure that return the correct data in the new columns.

If so then just copy and paste each one into your total column and + them together making sure you bracket each one and wrapper the whole thing in brackets too.

((sum(A)) + (sum(B)) + (sum(B)))

Now add in my code above to format to dec and add ￡ and you should be ok

|||

Cool! - Its is one of those things, to be honest i dont use the format area at all but add my formatting only into the expression for each field, at least then you only have to change one area of needed.

SSRS certianly has its quirks but you get used to it.

Andy

Adding large amounts of text

this may seem like a simple question, but I have a report/lease agreement I need to put together and wanted to know the simpliest way to add large amounts of text. Basically its all the legal stuff most leases include in the amount of some 14 pages.

Should this be just one long string-- or does ssrs have another way to format this

thanks as always

KM

Have them all in one textbox and set the width of the textbox to the maximum required and set CanGrow=true to allow increase in height.

Then you can type your long string in the textbox and do som formatting in it. You can use Chr() function to do some formatting like carriage return, line feed, tab and many more.

Shyam

|||

thanks will give it a try and let ya know

km

Adding Computed Columns Together

I have two computed columns (subtotal1, subtotal2) and I
want to add them together to get a Total. I want to show
it on a data access page that links to a SQL DB. This is
like a Purchase Order Database. Is this possible? What is
the easiest way to do this?
Thanks,
Nate SUse a view or query. Totals and subtotals don't belong in a table.
CREATE VIEW Something_with_totals (col1, col2, total)
AS
SELECT col1, col2, col1+col2
FROM Something
--
David Portas
--
Please reply only to the newsgroup
--

Adding Column Values Together in SQL SELECT Statement

I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin. My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result. My DB is SQL 2005 Express SP2. My select statement is below:

SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescriptionAS CPU, dbo.MFG_DATA_Parts_CPU.PartCostAS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescriptionAS Motherboard, dbo.MFG_DATA_Parts_Motherboard.PartCostAS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescriptionAS RAM, dbo.MFG_DATA_Parts_RAM.PartCostAS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescriptionAS HDD, dbo.MFG_DATA_Parts_HDD.PartCostAS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescriptionAS OpticalDrive, dbo.MFG_DATA_Parts_OpticalDrive.PartCostAS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescriptionAS Video, dbo.MFG_DATA_Parts_Video.PartCostAS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescriptionAS OS, dbo.MFG_DATA_Parts_OS.PartCostAS OSCost, dbo.MFG_DATA_Parts_Modem.PartDescriptionAS Modem, dbo.MFG_DATA_Parts_Modem.PartCostAS ModemCost, dbo.MFG_DATA_Parts_FloppyDrive.PartDescriptionAS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCostAS FloppyDriveCost, dbo.MFG_DATA_Parts_CardReader.PartDescriptionAS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCostAS CardReaderCost, dbo.MFG_DATA_Parts_PowerSupply.PartDescriptionAS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCostAS PowerSupplyCost, dbo.MFG_DATA_Parts_CaseType.PartDescriptionAS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCostAS CaseTypeCost, dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride, (dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost + dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost + dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost) * ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100)AS SalePriceFROM dbo.MFG_DATA_MachinesLEFTOUTER JOIN dbo.MFG_DATA_Parts_CaseTypeON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_MotherboardON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_VideoON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_RAMON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_PowerSupplyON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_OSON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_OpticalDriveON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_ModemON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_HDDON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_FloppyDriveON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_CPUON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_CardReaderON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID

First thing I would do is to besure you have a value for all the columns your adding together.

2nd thing would be to use IsNull(dbo.MFG_DATA_Parts_FloppyDrive.PartCost, 0) for each of your columns. What this will do is if there is a NULL value in any of the columns then it will replace it with a 0 thus returning a total and not a null value.

|||

That was exactly it! Thanks for the prompt reply

Adding bit fields

I tried to add 7 bit filelds together but it failed. I have 7 bit fields to
indicate days of w that person works, e.g. Mon, Tue,....Sun and want to
add them together to come up with # days worked in a w. I was trying
[Mon]+[Tue]+[Wed]......+[Sun] but it failed. Is there any easier way than
multiple IF statements? Thanks.
DavidTry CAST([Mon] AS INTEGER) + CAST([Tue] AS INTEGER) + ...
Are you sure you want to use a bit field there? Personally I find they're
more trouble than they're worth.
Thx,
Mike C.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OHNnCXcHFHA.2620@.tk2msftngp13.phx.gbl...
>I tried to add 7 bit filelds together but it failed. I have 7 bit fields
>to indicate days of w that person works, e.g. Mon, Tue,....Sun and want
>to add them together to come up with # days worked in a w. I was trying
>[Mon]+[Tue]+[Wed]......+[Sun] but it failed. Is there any easier way than
>multiple IF statements? Thanks.
> David
>|||How about a change of design. Much easier to write queries against
something like this:
CREATE TABLE WorkingTime (ssn VARCHAR(10) NOT NULL REFERENCES Employees
(ssn), work_dt DATETIME NOT NULL, PRIMARY KEY (ssn,work_dt))
For example:
SELECT ssn, COUNT(*)
FROM Timesheet
WHERE work_dt >= '20050228'
AND work_dt < '20050307'
GROUP BY ssn
As you are discovering, repeating groups of BIT columns create all
sorts of problems. It is a cardinal error to represent data in column
names.
If you can't change it then use CASE:
SELECT
CASE mon WHEN 1 THEN 1 ELSE 0 END +
CASE tue WHEN 1 THEN 1 ELSE 0 END +
CASE wed WHEN 1 THEN 1 ELSE 0 END +
..
David Portas
SQL Server MVP
--|||I do want the bit fields because they work great with checkboxes in
Access forms.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||That doesn't make them a good design from a database perspective. You
probably only have to design one Access form but might have to develop
many queries against the data so it's more important to have a logical
design that works well for data manipulation and reporting.
David Portas
SQL Server MVP
--|||You may be right. This was an uploaded Access database and in Access
the reporting and querying worked fine. Now that it is on SQL maybe we
will change it. It just seemed odd that I could total up yes/no field
in Access, but not on something as powerful as SQL Server.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi David,
SQL tends to make you re-think things the 'right' way. For instance, Access
might have let you total Yes/No fields, but that concept really doesn't make
much sense. After all, what is YES + YES + YES equal to? An "Emphatic
YES!!"? In SQL you would most likely use a COUNT() function in a SELECT
statement to determine how many YES's you had, which is what it sounds like
you're really after here. If you want to stay with the bits, David Portas
gave you the CASE statement that will do the trick, or you can do the CAST
conversion; I would recommend normalizing your DB and getting away from bits
though.
Thanks,
Mike C.
"David" <daman@.biteme.com> wrote in message
news:e96y3TeHFHA.3484@.TK2MSFTNGP12.phx.gbl...
> You may be right. This was an uploaded Access database and in Access
> the reporting and querying worked fine. Now that it is on SQL maybe we
> will change it. It just seemed odd that I could total up yes/no field
> in Access, but not on something as powerful as SQL Server.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Adding all non-null fields

I have 50 fields that I would like to sum together in an sql statement. However, some of them may be null causing the statement to fail. Can someone send me a statement that would add up only fields that are not null.

My current sql stmt is "select x1 + x2 + x3 + ... x50 from xtable"

Thanksselect ISNULL(x1,0)
+ ISNULL(x2,0)
+ ISNULL(x3,0)
+ ...
+ ISNULL(x50,0) from xtable|||Thanks a lot.