Return Different Fields Based On Expression

Hello Out There,
I have a dataset set up to return a set of billing information. I have this
information displayed in a matrix. However I have requirement where;
a) If the account type = ODC then return the vendor_name field
b) If the account type = ODC and venor_name field is null then return the
employee_name field
c) In any other case return the employee_name field.
Is there a way to set up an expression in a text box on the detail line of a
matrix to reflect the info needed?
There's a little more to be desired with my expression syntax skills so any
help would be greatly appreciated.
ThanksYou should do this in the SQL and return it in a single column.
In SQL Server,
NameField = CASE
WHEN accountType = ODC
CASE
WHEN vendor_name is null then employee_name
ELSE vendor_name
END
else
EMPLOYEE_NAME
END
-tIM
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:2ADF13EE-A421-4F4A-8E5E-A47C1D55D89B@.microsoft.com...
> Hello Out There,
> I have a dataset set up to return a set of billing information. I have
> this
> information displayed in a matrix. However I have requirement where;
> a) If the account type = ODC then return the vendor_name field
> b) If the account type = ODC and venor_name field is null then return the
> employee_name field
> c) In any other case return the employee_name field.
> Is there a way to set up an expression in a text box on the detail line of
> a
> matrix to reflect the info needed?
> There's a little more to be desired with my expression syntax skills so
> any
> help would be greatly appreciated.
> Thanks|||I often don't see the forest through the trees. Thanks for the quick help.
JD
"Tim Dot NoSpam" wrote:
> You should do this in the SQL and return it in a single column.
> In SQL Server,
> NameField = CASE
> WHEN accountType = ODC
> CASE
> WHEN vendor_name is null then employee_name
> ELSE vendor_name
> END
> else
> EMPLOYEE_NAME
> END
> -tIM
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:2ADF13EE-A421-4F4A-8E5E-A47C1D55D89B@.microsoft.com...
> > Hello Out There,
> >
> > I have a dataset set up to return a set of billing information. I have
> > this
> > information displayed in a matrix. However I have requirement where;
> >
> > a) If the account type = ODC then return the vendor_name field
> > b) If the account type = ODC and venor_name field is null then return the
> > employee_name field
> > c) In any other case return the employee_name field.
> >
> > Is there a way to set up an expression in a text box on the detail line of
> > a
> > matrix to reflect the info needed?
> >
> > There's a little more to be desired with my expression syntax skills so
> > any
> > help would be greatly appreciated.
> >
> > Thanks
>
>

Return attributes from all child nodes

Ok this is the last step I hope. Trying to return a table based on
this XML:
<PARENT>
<ITEM spec="spec1 specval="red" /ITEM>
<ITEM spec="spec2 specval="white" /ITEM>
<ITEM spec="spec3 specval="blue" /ITEM>
</PARENT>
I need to return a table:
SPEC SPECVAL
spec1 red
spec2 white
spec3 blue
Thanks for any help on this.
-RichardHow about something like this?
DECLARE @.selxml xml
SET @.selxml ='
<PARENT>
<ITEM spec="spec1" specval="red" />
<ITEM spec="spec2" specval="white" />
<ITEM spec="spec3" specval="blue" />
</PARENT>'
SELECT x.value('data(./@.spec)', 'nvarchar(MAX)') as SPEC,
x.value('data(./@.specval)', 'nvarchar(MAX)') as SPECVAL
FROM @.selxml.nodes('/PARENT/ITEM') T(x)
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sk8man31" <me@.aol.com> wrote in message
news:dqk3925c44br94nrn7m3msru3glgpq7h2c@.
4ax.com...
> Ok this is the last step I hope. Trying to return a table based on
> this XML:
> <PARENT>
> <ITEM spec="spec1 specval="red" /ITEM>
> <ITEM spec="spec2 specval="white" /ITEM>
> <ITEM spec="spec3 specval="blue" /ITEM>
> </PARENT>
> I need to return a table:
> SPEC SPECVAL
> spec1 red
> spec2 white
> spec3 blue
> Thanks for any help on this.
> -Richard

Return all months within a range of dates

I currently have a stored procedure that returns a list of dates based on a date range a user enters.

CREATE PROCEDURE sp_GetContactScheduleDates
@.MonthFrom int,
@.YearFrom int,
@.MonthTo int,
@.YearTo int,
@.DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 >= CAST(cast(@.MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@.YearFrom as nvarchar) as smalldatetime)
 And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 <= CAST(cast(@.MonthTo as nvarchar) + '/' + cast(@.DaysInMonth as nvarchar) + '/' + cast(@.YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO

However, this only brings back those dates that are in the table. I need to get ALL dates within the range.

For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?

Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.

Can some sort of function be used? What would the code look like?I would create a table variable with one field that will hold the date. The do a loop to populate it. I'd make sure @.startdate and @.enddate have the time stripped off. Not tested, but should work with minor tweaks.

set @.date = @.startdate
set @.x = datediff(d, @.startdate, @.enddate)
set @.y = 0
While @.y <= @.x
 Begin
 insert into @.table (datefield) values (dateadd(d, @.y, @.startdate))
 set @.y = @.y + 1
 End

|||ooo that's a nice loop. :)

Return 2 entries per group based on the two lowest values in group

Below is a query that I need to modify so that it returns just the 2
MastNUMs, Stores, and Distances where the Distances are the two lowest for a
mastnum. So the results would be more like this
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000068 76 4.447685
000000068 70 5.516853
000000069 70 3.836682
000000069 76 6.331691
000000070 76 3.729323
SELECT TOP 15 MastNUM, Store, Distance
FROM DistTest
GROUP BY MastNUM, Store, Distance
ORDER BY MastNUM, Distance
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000067 69 8.112116
000000067 112 19.924702
000000068 76 4.447685
000000068 70 5.516853
000000068 69 6.874022
000000068 112 18.622366
000000068 71 19.396528
000000069 70 3.836682
000000069 76 6.331691
000000069 69 8.924779
000000069 112 16.709897
000000069 71 17.462224
000000070 76 3.729323
--
KoryI can get the lowest for each with the following but how can I get the
lowest 2?
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||I think I have found a way to get the lowest and 2nd lowest, but I hope
their is a better way than the following..
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
(
SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest t2
WHERE Distance > (
SELECT MIN(Distance)
FROM DistTest d2
WHERE t2.MastNum = d2.MastNum
GROUP BY MastNum
)
GROUP BY MastNum
) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
order by T1.mASTnUM
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:51 AM CDT ] [Execution: 16/ms]
MastNUM Store Distance
-- -- --
000000067 70 7.082444
000000068 70 5.516853
000000069 76 6.331691
000000070 70 6.957127
000000071 70 5.944537
000000072 76 6.277529
000000073 70 5.487792
000000074 70 7.193161
000000076 70 5.201532
000000077 70 5.412091
000000078 76 5.295454
000000079 76 5.395715
000000080 76 5.950446
000000081 70 5.920339
000000082 70 5.529576
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:52 AM CDT ] [Execution: 93/ms]
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:uVgDQB9WDHA.2268@.TK2MSFTNGP11.phx.gbl...
> I can get the lowest for each with the following but how can I get the
> lowest 2?
> SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
> FROM DistTest t1,
> ( SELECT MastNUM, MIN(Distance) AS MinDist
> FROM DistTest
> GROUP BY MastNum ) as Dmin
> WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
> go
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000068 76 4.447685
> 000000069 70 3.836682
> 000000070 76 3.729323
> 000000071 76 4.046238
> 000000072 70 3.928709
> 000000073 76 4.663551
> 000000074 76 3.206388
> 000000076 76 4.745636
> 000000077 76 5.338428
> 000000078 70 5.121837
> 000000079 70 4.580213
> 000000080 70 4.338181
> 000000081 76 4.069455
> 000000082 76 4.465975
>
> "Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
> news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> > Below is a query that I need to modify so that it returns just the 2
> > MastNUMs, Stores, and Distances where the Distances are the two lowest
for
> a
> > mastnum. So the results would be more like this
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000070 76 3.729323
> >
> > SELECT TOP 15 MastNUM, Store, Distance
> > FROM DistTest
> > GROUP BY MastNUM, Store, Distance
> > ORDER BY MastNUM, Distance
> >
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000067 69 8.112116
> > 000000067 112 19.924702
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000068 69 6.874022
> > 000000068 112 18.622366
> > 000000068 71 19.396528
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000069 69 8.924779
> > 000000069 112 16.709897
> > 000000069 71 17.462224
> > 000000070 76 3.729323
> >
> > --
> >
> > Kory
> >
> >
>|||The initial step creating this list is as follows:
SELECT g.MASTNUM, s.STORE, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG ) AS Distance
INTO DistTest
FROM geomailing g, geoStore s
WHERE master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) <= 20
GROUP BY g.Mastnum, s.Store, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG )
ORDER BY g.Mastnum, master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG )
Can anyone suggest a way that it only inserts into this table the 2 lowest
Distances ( master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) ) ? So
as to avoid having to pull out the two lowest distances later?
Thanks.
Kory wrote in message news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||Does anyone have any suggestions on how to get just the 2 lowest values
grouped?|||I would imagine a lot of people have a lot of ideas but with what you have
given us it would be stabbing in the dark.
1. Can you post a simple table structure
2. Post sample data to enter in
3. Tell us what you expect to see as the end result.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:OPqoHUDXDHA.208@.tk2msftngp13.phx.gbl...
> Does anyone have any suggestions on how to get just the 2 lowest values
> grouped?
>

Return 0 if null

Hi all,
I have got a query that returns values based on a date range and grouped by
week. I need to return a value of 0 if there are no entries for that week. At
the moment it just skips that week all together.
Any ideas?
Thanks
Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
left outer join if you're using multiple tables and a lack of entries for
that week table is eliminating the week row. But to give a specific answer,
we'd need more details.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
> Hi all,
> I have got a query that returns values based on a date range and grouped
> by
> week. I need to return a value of 0 if there are no entries for that week.
> At
> the moment it just skips that week all together.
> Any ideas?
> Thanks
|||Hi Paul,
My current query is as follows.
SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
Type_QRY].CountOfType) AS SumOfCountOfType
FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN [Counting
Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting Type_QRY].Branch,
[Counting Type_QRY].Type;
This works great counting my entries and putting then grouping by week.
Trouble is if there is no info for a week it skipps that week.
34 = 4
35 = 2
37 = 7
I need it to return
34 = 4
35 = 2
36 = 0
37 = 7
I am fairly new to this so please excuse the query if it is not great. Just
trying to grow my skills in the real world.
Thanks
"Paul Nielsen" wrote:

> Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
> left outer join if you're using multiple tables and a lack of entries for
> that week table is eliminating the week row. But to give a specific answer,
> we'd need more details.
> --
> -Paul Nielsen, SQL Server MVP
> SQL Server 2000 Bible, Wiley Press
>
> "Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
> news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
>
>
|||Andrew Jurgens wrote:
> Hi Paul,
> My current query is as follows.
> SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
> Type_QRY].CountOfType) AS SumOfCountOfType
> FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN
> [Counting Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
> GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type;
> This works great counting my entries and putting then grouping by
> week. Trouble is if there is no info for a week it skipps that week.
> 34 = 4
> 35 = 2
> 37 = 7
> I need it to return
> 34 = 4
> 35 = 2
> 36 = 0
> 37 = 7
> I am fairly new to this so please excuse the query if it is not
> great. Just trying to grow my skills in the real world.
> Thanks
Sounds like you need an OUTER JOIN. I don't know your data, so use an
outer join against the table that may not have a foreign key
relationship. If you wanted all Accounts from the accounts table even if
some of the accounts didn't have a comment in the comments table, you
would:
From Accounts Outer Join Comments on Accounts.id = Comments.id
David Gugick
Imceda Software
www.imceda.com
|||Thanks David,
My problem is that I am querying a date range but there may not be entries
for all dates within that range in the table. I still need to return all
dates even if there is no data. Hence my previous
This works great counting my entries and putting then grouping by[vbcol=seagreen]
I really appreciate your input. Have been stuck for a bit and need to get
out of this hole!
Thanks.
"David Gugick" wrote:

> Andrew Jurgens wrote:
>
> Sounds like you need an OUTER JOIN. I don't know your data, so use an
> outer join against the table that may not have a foreign key
> relationship. If you wanted all Accounts from the accounts table even if
> some of the accounts didn't have a comment in the comments table, you
> would:
> From Accounts Outer Join Comments on Accounts.id = Comments.id
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Thu, 14 Oct 2004 01:59:20 -0700, Andrew Jurgens wrote:

>Thanks David,
>My problem is that I am querying a date range but there may not be entries
>for all dates within that range in the table. I still need to return all
>dates even if there is no data. Hence my previous
Hi Andrew,
Looks like you need a calendar table.
See http://www.aspfaq.com/show.asp?id=2516.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Retrieving RowCount from a dataset

Hi,
I need to calculate a percentage of population based on the number of rows
returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
there is no way for SRS to receive multiple result sets in a single data set.
I could execute the query twice, once for the count and second with the
count included as a column. I hate to run the query twice. What I could use
is a rowcount from the dataset in SRS, but I don't this property exists.
Does anyone have any ideas?
Thank you,
BobBob,
As you are using a SProc to return the recordset, just add the @.@.rowcount to
the end of the SELECT statement and it will be returned with each row. This
will allow a single call only, and the amount of 'extra' data returned (2
bytes per row maybe?) will be mnore efficient than recalling the SProc a
second time.
Tony
"Bob" wrote:
> Hi,
> I need to calculate a percentage of population based on the number of rows
> returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> there is no way for SRS to receive multiple result sets in a single data set.
> I could execute the query twice, once for the count and second with the
> count included as a column. I hate to run the query twice. What I could use
> is a rowcount from the dataset in SRS, but I don't this property exists.
> Does anyone have any ideas?
> Thank you,
> Bob|||Hi Tony,
I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
only valid after the select has completed. I was hoping that a rowcount
property would be available within SRS.
Thank you for the suggestion.
Bob
"Logicalman" wrote:
> Bob,
> As you are using a SProc to return the recordset, just add the @.@.rowcount to
> the end of the SELECT statement and it will be returned with each row. This
> will allow a single call only, and the amount of 'extra' data returned (2
> bytes per row maybe?) will be mnore efficient than recalling the SProc a
> second time.
> Tony
> "Bob" wrote:
> > Hi,
> >
> > I need to calculate a percentage of population based on the number of rows
> > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > there is no way for SRS to receive multiple result sets in a single data set.
> > I could execute the query twice, once for the count and second with the
> > count included as a column. I hate to run the query twice. What I could use
> > is a rowcount from the dataset in SRS, but I don't this property exists.
> > Does anyone have any ideas?
> >
> > Thank you,
> > Bob|||Bob,
Have you tried the CountRows function? (Syntax should be:
=CountRows("yourDataset")) Just a suggestion. I have never used this
technique.
Ernie
Bob wrote:
> Hi Tony,
> I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> only valid after the select has completed. I was hoping that a rowcount
> property would be available within SRS.
> Thank you for the suggestion.
> Bob
> "Logicalman" wrote:
> > Bob,
> >
> > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > the end of the SELECT statement and it will be returned with each row. This
> > will allow a single call only, and the amount of 'extra' data returned (2
> > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > second time.
> >
> > Tony
> >
> > "Bob" wrote:
> >
> > > Hi,
> > >
> > > I need to calculate a percentage of population based on the number of rows
> > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > there is no way for SRS to receive multiple result sets in a single data set.
> > > I could execute the query twice, once for the count and second with the
> > > count included as a column. I hate to run the query twice. What I could use
> > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > Does anyone have any ideas?
> > >
> > > Thank you,
> > > Bob|||I figured out how to do this.
In the table HEADER section, which is the entire scope, I placed a
CountDistinct(fld!name.value) for the unique item in the dataset. Then I
made the column invisible. Lastly, I referenced this value within the table
via ReportItems!textboxname.value. This gave me the count I was looking for.
"Bob" wrote:
> Hi Tony,
> I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> only valid after the select has completed. I was hoping that a rowcount
> property would be available within SRS.
> Thank you for the suggestion.
> Bob
> "Logicalman" wrote:
> > Bob,
> >
> > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > the end of the SELECT statement and it will be returned with each row. This
> > will allow a single call only, and the amount of 'extra' data returned (2
> > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > second time.
> >
> > Tony
> >
> > "Bob" wrote:
> >
> > > Hi,
> > >
> > > I need to calculate a percentage of population based on the number of rows
> > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > there is no way for SRS to receive multiple result sets in a single data set.
> > > I could execute the query twice, once for the count and second with the
> > > count included as a column. I hate to run the query twice. What I could use
> > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > Does anyone have any ideas?
> > >
> > > Thank you,
> > > Bob|||Bob,
Excellent result. We are all still on the learning curve, I'll have to
remember that one.
Regarding using the @.@.RowCount feature, you could also have used a simple
variable in the sproc and set it to Select Count(*) FROm tblename and then
passed that as an extra column in the final select statement.
Tony
"Ernie Gutierrez" wrote:
> Bob,
> Have you tried the CountRows function? (Syntax should be:
> =CountRows("yourDataset")) Just a suggestion. I have never used this
> technique.
> Ernie
> Bob wrote:
> > Hi Tony,
> >
> > I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> > only valid after the select has completed. I was hoping that a rowcount
> > property would be available within SRS.
> >
> > Thank you for the suggestion.
> >
> > Bob
> >
> > "Logicalman" wrote:
> >
> > > Bob,
> > >
> > > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > > the end of the SELECT statement and it will be returned with each row. This
> > > will allow a single call only, and the amount of 'extra' data returned (2
> > > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > > second time.
> > >
> > > Tony
> > >
> > > "Bob" wrote:
> > >
> > > > Hi,
> > > >
> > > > I need to calculate a percentage of population based on the number of rows
> > > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > > there is no way for SRS to receive multiple result sets in a single data set.
> > > > I could execute the query twice, once for the count and second with the
> > > > count included as a column. I hate to run the query twice. What I could use
> > > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > > Does anyone have any ideas?
> > > >
> > > > Thank you,
> > > > Bob
>

Retrieving Data from a DB based on output of a conditional split

This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need.

Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing.

Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command.

How can I do this?

Thank you!

Nolan

Use a lookup component.

Retrieving a datetime with a time of midnight (from a typical datetime)

Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.

A frequent method used is to (1) convert it to varchar using CONVERT with the 101 flavor and then (2) re-convert it back to datetime. Here are some examples:

Code Snippet

select convert(datetime, convert(varchar, getdate(), 101))
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select dateadd(day, datediff (day, 0, getdate()), 0)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select cast(floor(cast(getdate() as float)) as datetime)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

|||

Another way:

Code Snippet

select dateadd(d, datediff(d,0,[PostedDate]),0)

|||I used the dateadd method both of you suggested and it worked perfectly. Thank you very much.