Help with custom total in matrix (hopefully easy adjustment)

Hey guys. I have a matrix that is working the way I want it as far as
groupings and data. I am doing a custom total that works for the first row
group with the following code:
Private currentTotal As Double = 0
Function SetCurrentValue(ByVal currentValue As Double) As Double
currentTotal += currentValue
return currentTotal
End Function
Function GetCurrentValue() As Double
return currentTotal
End Function
Function ResetTotal() As Boolean
currentTotal=0
return True
End Function
In my detail I have:
=IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
If I use only the first IIF statement I get the correct values 19, 38 , 57,
76, 95, 114, 133, 152, 171, 190, 209, and 228.
If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
361, 399, 437 in the first row and in the second row I get:
494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
I need to run the reset method in between row groupings to get it to go back
to 19 for the second row (it will be a different starting value eventually,
but for now I need it to reset).
I tried putting the code as a second row grouping and the column subtotal,
but it won't reset in the proper spot. Let me know!
Thanks!,
BJYou can use rownumber with scope/group so that it resets each and everytime
group changes. Provided your 19,38,57 etc.. are fixed values...
Amarnath
"bjkaledas" wrote:
> Hey guys. I have a matrix that is working the way I want it as far as
> groupings and data. I am doing a custom total that works for the first row
> group with the following code:
> Private currentTotal As Double = 0
> Function SetCurrentValue(ByVal currentValue As Double) As Double
> currentTotal += currentValue
> return currentTotal
> End Function
> Function GetCurrentValue() As Double
> return currentTotal
> End Function
> Function ResetTotal() As Boolean
> currentTotal=0
> return True
> End Function
> In my detail I have:
> =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> If I use only the first IIF statement I get the correct values 19, 38 , 57,
> 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> 361, 399, 437 in the first row and in the second row I get:
> 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> I need to run the reset method in between row groupings to get it to go back
> to 19 for the second row (it will be a different starting value eventually,
> but for now I need it to reset).
> I tried putting the code as a second row grouping and the column subtotal,
> but it won't reset in the proper spot. Let me know!
> Thanks!,
> BJ|||Where would I use the rownumber function? Would I keep track of this in my
custom code or when I call the function in my detail textbox?
"Amarnath" wrote:
> You can use rownumber with scope/group so that it resets each and everytime
> group changes. Provided your 19,38,57 etc.. are fixed values...
>
> Amarnath
> "bjkaledas" wrote:
> > Hey guys. I have a matrix that is working the way I want it as far as
> > groupings and data. I am doing a custom total that works for the first row
> > group with the following code:
> >
> > Private currentTotal As Double = 0
> > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > currentTotal += currentValue
> > return currentTotal
> > End Function
> >
> > Function GetCurrentValue() As Double
> > return currentTotal
> > End Function
> >
> > Function ResetTotal() As Boolean
> > currentTotal=0
> > return True
> > End Function
> >
> > In my detail I have:
> >
> > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> >
> > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> >
> > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > 361, 399, 437 in the first row and in the second row I get:
> >
> > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> >
> > I need to run the reset method in between row groupings to get it to go back
> > to 19 for the second row (it will be a different starting value eventually,
> > but for now I need it to reset).
> >
> > I tried putting the code as a second row grouping and the column subtotal,
> > but it won't reset in the proper spot. Let me know!
> >
> > Thanks!,
> >
> > BJ|||You can use in the textbox so that when it displays it resets exactly.
Amarnath
"bjkaledas" wrote:
> Where would I use the rownumber function? Would I keep track of this in my
> custom code or when I call the function in my detail textbox?
> "Amarnath" wrote:
> > You can use rownumber with scope/group so that it resets each and everytime
> > group changes. Provided your 19,38,57 etc.. are fixed values...
> >
> >
> > Amarnath
> >
> > "bjkaledas" wrote:
> >
> > > Hey guys. I have a matrix that is working the way I want it as far as
> > > groupings and data. I am doing a custom total that works for the first row
> > > group with the following code:
> > >
> > > Private currentTotal As Double = 0
> > > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > > currentTotal += currentValue
> > > return currentTotal
> > > End Function
> > >
> > > Function GetCurrentValue() As Double
> > > return currentTotal
> > > End Function
> > >
> > > Function ResetTotal() As Boolean
> > > currentTotal=0
> > > return True
> > > End Function
> > >
> > > In my detail I have:
> > >
> > > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> > >
> > > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> > >
> > > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > > 361, 399, 437 in the first row and in the second row I get:
> > >
> > > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> > >
> > > I need to run the reset method in between row groupings to get it to go back
> > > to 19 for the second row (it will be a different starting value eventually,
> > > but for now I need it to reset).
> > >
> > > I tried putting the code as a second row grouping and the column subtotal,
> > > but it won't reset in the proper spot. Let me know!
> > >
> > > Thanks!,
> > >
> > > BJ

Help with COUNT in SELECT Statement

Could someone assist with getting the count function working correctly
in this example please. I know the count function will return all rows
that do not have null values, but in this case I want to count all the
rows except those with a zero sale price, (which are unsold).

The table shows works offered for sale by an artist, with a positive
figure under SalePrice indicating a sale, and I want to count the
number sold by each auction house, and sum the sale price by auction
house. The table is as follows:

NameSalePriceAuction
Dowling12000Christies
Dowling 0Christies
Dowling10000Christies
Dowling 0Christies
Dowling 0Christies
Dowling 6000Sothebys
Dowling 0Sothebys
Dowling 0Sothebys
Dowling 8000Sothebys
Dowling 0Sothebys
Dowling 0Sothebys
Dowling 0Sothebys

When I run this query:

SELECT MyTable.Name, Count(MyTable.Name) AS [Number],
Sum(MyTable.SalePrice) AS TotalSales, MyTable.Auction
FROM MyTable
GROUP BY MyTable.Name, MyTable.Auction
HAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));

The results are:

NameNumberTotalSalesAuction
Dowling 5 22000 Christies
Dowling 7 14000 Sothebys

The TotalSales is correct, but the Number (Count) is incorrect, as the
rows with zero were also included. The results should be:

NameNumberTotalSalesAuction
Dowling 2 22000 Christies
Dowling 2 14000 Sothebys

How do I prevent the unsolds (zeros) being counted?

Thanks in advance,

John FurphyAssuming you also want to exclude NULL saleprices (if any):

SELECT name, COUNT(NULLIF(saleprice,0)) AS number,
SUM(saleprice) AS totalsales, auction
FROM MyTable
GROUP BY name, auction
HAVING name='Dowling' AND SUM(saleprice)>0;

--
David Portas
----
Please reply only to the newsgroup
--|||"John Furphy" <johnfurphy@.a1.com.au> wrote in message
news:4ce579e8.0312010326.115691db@.posting.google.c om...
> Could someone assist with getting the count function working correctly
> in this example please. I know the count function will return all rows
> that do not have null values, but in this case I want to count all the
> rows except those with a zero sale price, (which are unsold).
> The table shows works offered for sale by an artist, with a positive
> figure under SalePrice indicating a sale, and I want to count the
> number sold by each auction house, and sum the sale price by auction
> house. The table is as follows:
> Name SalePrice Auction
> Dowling 12000 Christies
> Dowling 0 Christies
> Dowling 10000 Christies
> Dowling 0 Christies
> Dowling 0 Christies
> Dowling 6000 Sothebys
> Dowling 0 Sothebys
> Dowling 0 Sothebys
> Dowling 8000 Sothebys
> Dowling 0 Sothebys
> Dowling 0 Sothebys
> Dowling 0 Sothebys
> When I run this query:
> SELECT MyTable.Name, Count(MyTable.Name) AS [Number],
> Sum(MyTable.SalePrice) AS TotalSales, MyTable.Auction
> FROM MyTable
> GROUP BY MyTable.Name, MyTable.Auction
> HAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));
> The results are:
> Name Number TotalSales Auction
> Dowling 5 22000 Christies
> Dowling 7 14000 Sothebys
> The TotalSales is correct, but the Number (Count) is incorrect, as the
> rows with zero were also included. The results should be:
> Name Number TotalSales Auction
> Dowling 2 22000 Christies
> Dowling 2 14000 Sothebys
> How do I prevent the unsolds (zeros) being counted?
>
> Thanks in advance,
> John Furphy

SELECT "Name",
COUNT(*) AS Number,
SUM(SalesPrice) AS TotalSales,
Auction
FROM MyTable
WHERE SalesPrice > 0
GROUP BY "Name", Auction

Regards,
jag

Help with connecting to the object explorer ON Microsoft SQL Server Management Studio Expr

Hi All,

I have just installed Microsoft SQL Server Management Studio Express.. Once I did this first it was working fine, but now their is this error each time I try to connect the object explorer.. the error is as follows :-

Could not load file or assembly 'System.Data, Version=2.0.0.0, Culture=neutral publickeyToken=b77a5c561934e089' or one of its dependencies. Signature specified is zero-sized. (Exception from HRESULT: 0x80131237).

Could anybody help me with getting rid of this error. I have no clue what to do?Try closing everything, uninstall SMSS, reboot, reinstall. Make sure only necessary programs and network connections are on when you try to open SMSS again.sql

Help with addition to Missing date query

Hi Everyone,
I hope someone can help out some. I have a report that displays the days our
clients missed there sessions. That was working great, and I've got some new
requirements now for the report. They needed to store the number of days the
client is suppose to attend, so I created a field called ClientSchedule that
stores a value 1-7 (days as week). The thing that has got me now is that they
also want the report to represent this new field. So the report needs to not
include the client in the report if they have attended for there scheduled
days for that week. But the kicker is that the report is based on a date
range and that range is variable, it could be set to one week, two weeks, 1
1/2 weeks, ect.. I have a query that partially works, but I could not figure
out how to work out the missed days within a week with a variable date range.
Somehow it needs to compare the number of days the client is scheduled
(ClientSchedule) with the number of days attended for a week, if days
attended is lower than ClientSchedule for that week, include the missed days
in the report(including all weeks that may be part of the Date range the user
selected).
The base query I have is as follows:
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.CounselorId int,
@.FromDate smalldatetime,
@.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
Delete from RangeTest
if @.PatientId = -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate
Print 'First Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
Print 'Second Set'
end
end
if @.PatientId > -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate and PatientId = @.PatientId
Print 'Third Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
and PatientId = @.PatientId
Print 'Last Set'
end
end
Delete from Range
if @.PatientId = -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON 1
= 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.PatientId > -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
Patientid = @.PatientId) Test ON 1 = 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.CounselorId = -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID = dbo.nf_GetLastDayVisited.PatientId
where PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
if @.CounselorId <> -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID = dbo.nf_GetLastDayVisited.PatientId
Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
GO
I have worked on it for several hours and not sure where to take it from
here. Do I have to start to use a curser, or is there a way to do this with
just a modification to the query I have so far. Thanks for any suggestions.
MichaelHi Michael
Have you thought of using a calendar table for this ?
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
John
"Michael" wrote:
> Hi Everyone,
> I hope someone can help out some. I have a report that displays the days our
> clients missed there sessions. That was working great, and I've got some new
> requirements now for the report. They needed to store the number of days the
> client is suppose to attend, so I created a field called ClientSchedule that
> stores a value 1-7 (days as week). The thing that has got me now is that they
> also want the report to represent this new field. So the report needs to not
> include the client in the report if they have attended for there scheduled
> days for that week. But the kicker is that the report is based on a date
> range and that range is variable, it could be set to one week, two weeks, 1
> 1/2 weeks, ect.. I have a query that partially works, but I could not figure
> out how to work out the missed days within a week with a variable date range.
> Somehow it needs to compare the number of days the client is scheduled
> (ClientSchedule) with the number of days attended for a week, if days
> attended is lower than ClientSchedule for that week, include the missed days
> in the report(including all weeks that may be part of the Date range the user
> selected).
> The base query I have is as follows:
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.CounselorId int,
> @.FromDate smalldatetime,
> @.ToDate smalldatetime
> AS
> SET NOCOUNT ON
> DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
> Delete from RangeTest
> if @.PatientId = -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate
> Print 'First Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> Print 'Second Set'
> end
> end
> if @.PatientId > -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate and PatientId = @.PatientId
> Print 'Third Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> and PatientId = @.PatientId
> Print 'Last Set'
> end
> end
>
> Delete from Range
> if @.PatientId = -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON 1
> = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.PatientId > -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
> Patientid = @.PatientId) Test ON 1 = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.CounselorId = -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID => dbo.nf_GetLastDayVisited.PatientId
> where PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> if @.CounselorId <> -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID => dbo.nf_GetLastDayVisited.PatientId
> Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> GO
> I have worked on it for several hours and not sure where to take it from
> here. Do I have to start to use a curser, or is there a way to do this with
> just a modification to the query I have so far. Thanks for any suggestions.
> Michael
>

Help with addition to Missing date query

Hi Everyone,
I hope someone can help out some. I have a report that displays the days our
clients missed there sessions. That was working great, and I've got some new
requirements now for the report. They needed to store the number of days the
client is suppose to attend, so I created a field called ClientSchedule that
stores a value 1-7 (days as week). The thing that has got me now is that the
y
also want the report to represent this new field. So the report needs to not
include the client in the report if they have attended for there scheduled
days for that week. But the kicker is that the report is based on a date
range and that range is variable, it could be set to one week, two weeks, 1
1/2 weeks, ect.. I have a query that partially works, but I could not figure
out how to work out the missed days within a week with a variable date range
.
Somehow it needs to compare the number of days the client is scheduled
(ClientSchedule) with the number of days attended for a week, if days
attended is lower than ClientSchedule for that week, include the missed days
in the report(including all weeks that may be part of the Date range the use
r
selected).
The base query I have is as follows:
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.CounselorId int,
@.FromDate smalldatetime,
@.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
Delete from RangeTest
if @.PatientId = -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate
Print 'First Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
Print 'Second Set'
end
end
if @.PatientId > -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate and PatientId = @.PatientId
Print 'Third Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
and PatientId = @.PatientId
Print 'Last Set'
end
end
Delete from Range
if @.PatientId = -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10
+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON 1
= 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.PatientId > -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10
+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
Patientid = @.PatientId) Test ON 1 = 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.CounselorId = -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID =
dbo.nf_GetLastDayVisited.PatientId
where PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
if @.CounselorId <> -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID =
dbo.nf_GetLastDayVisited.PatientId
Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
GO
I have worked on it for several hours and not sure where to take it from
here. Do I have to start to use a curser, or is there a way to do this with
just a modification to the query I have so far. Thanks for any suggestions.
MichaelHi Michael
Have you thought of using a calendar table for this ?
http://sqlserver2000.databases.aspf...ndar-table.html
John
"Michael" wrote:

> Hi Everyone,
> I hope someone can help out some. I have a report that displays the days o
ur
> clients missed there sessions. That was working great, and I've got some n
ew
> requirements now for the report. They needed to store the number of days t
he
> client is suppose to attend, so I created a field called ClientSchedule th
at
> stores a value 1-7 (days as week). The thing that has got me now is that t
hey
> also want the report to represent this new field. So the report needs to n
ot
> include the client in the report if they have attended for there scheduled
> days for that week. But the kicker is that the report is based on a date
> range and that range is variable, it could be set to one week, two weeks,
1
> 1/2 weeks, ect.. I have a query that partially works, but I could not figu
re
> out how to work out the missed days within a week with a variable date ran
ge.
> Somehow it needs to compare the number of days the client is scheduled
> (ClientSchedule) with the number of days attended for a week, if days
> attended is lower than ClientSchedule for that week, include the missed da
ys
> in the report(including all weeks that may be part of the Date range the u
ser
> selected).
> The base query I have is as follows:
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.CounselorId int,
> @.FromDate smalldatetime,
> @.ToDate smalldatetime
> AS
> SET NOCOUNT ON
> DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
> Delete from RangeTest
> if @.PatientId = -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate
> Print 'First Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> Print 'Second Set'
> end
> end
> if @.PatientId > -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate and PatientId = @.PatientId
> Print 'Third Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> and PatientId = @.PatientId
> Print 'Last Set'
> end
> end
>
> Delete from Range
> if @.PatientId = -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10
+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON
1
> = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.PatientId > -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10
+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
> Patientid = @.PatientId) Test ON 1 = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.CounselorId = -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstNam
e,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId
)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID =
> dbo.nf_GetLastDayVisited.PatientId
> where PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> if @.CounselorId <> -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstNam
e,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId
)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID =
> dbo.nf_GetLastDayVisited.PatientId
> Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> GO
> I have worked on it for several hours and not sure where to take it from
> here. Do I have to start to use a curser, or is there a way to do this wit
h
> just a modification to the query I have so far. Thanks for any suggestions
.
> Michael
>sql

help with a restore problem please

I have a problem and I believe is very simple but is something missing in my knoledgments.
I migrate an sql server database to db2.
I start working with db2 for about 2-3 months and I am very new in administering problems.
I installed another server in another location. IN the old location I have my database which I intented to move in the new location (the new server).
I made a backup.
Now I want to restore using this backup in the new location (and the new server). I don't know how.
In SQL Server if I have a backup I can restore everywhere I want. Generally i create a new empty database and restore the backup into the new empty database. Another possibility is to restore the database and the database will be restored with the original name.
I try the same in Control Center but I don't know what is my mistake. If I try to to use create database from backup the "database being restored" give me the possibility to choose among the existing databases. But all I have is a full backup.
Can you tell me please what to do?
Thanks in advanced.have you tried copying the database between the two servers using DTS? it's easy to do. simply back up the database you want to copy, right click on the database and select export. change the exporting database to the one you just backed up, and set the export to database as the one you want to populate on the other box. follow the rest of the instructions and all should go well.

the other method is to copy the backup of the original database, put it on the new box and do a restore from file (simply put the link to the backup copy).

hope this helps.sql

Help w/ SqlClientPermission

Hello!

I'm currently working on a project consisting in rewriting VB 6.0 applications into VS 2005 applications. I already have the form designed in VS '05 w/ the controls, and I'm connected to a SQL server 2000 database. I've also created the different data adapters and datasets, which in VS 2005, have also created BindingSources and BindingNavigators. But, when I run the project, I am getting the following error, and the form doesn't appear:

Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

I have been trying to find ways to get around it, but I'm not that successful. I have gone online on the Microsoft website and found a host of comments from other people, but I can't relate theirs to my problem. I understand that my program needs to be granted this permission for it to run, but I can't seem to know what and where to begin resolving it. Here's also what I've found online about how to resolve it:

To grant the required permissions to a custom assembly, follow these steps:

1. Assert permissions in your custom assembly.

2. Make the custom assembly available to your project.

3. Reference the custom assembly in your project.

4. Modify the code access security (CAS) settings for your custom assembly.

Can anyone who has encountered this kind of error tell me if these are the steps I need to take to solve it?

Also, is teh custom assembly the AssemblyInfo.vb file or is it obtained by right-clicking the project in the Solution Explorer and pointing at the properties?

Thanks very much for any help I can help.

Have a wonderful day.

This is a CAS policy which could be set at domain level and enherited by your computer. The SQLClient assembly is not rated as trusted in the domain/Area you want to run the application (either locally or over the network which is in common not allowed / trusted). YOu can see the configured permissions using the .Net Framework configuration tool.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Help w/ SQL Newbie Student

Hi,

I am working on a school project ...

I have the following schema:
<b>
DIVISION (dvname, manager)
DEPT (dname, parent-dname/parent-dvname, manager, floor#)
EMP (ename, salary, dname/dvname)
ITEM (iname, color, price, type)
SELL (dname, iname)
SUPPLY (sname, iname, dname)
</b>
Each of the first fields is the PKey.

I have to figure out the SQL for the following statement:

<b>List the items supplied by all companies that supply all items of type A.</b>

I have gotten this far, but do not understand division in SQL well enough .. I have a relational algebra solution that works ... but am having a hell of a time with a SQL solution ... Please help,

This is what I have in SQL:
SELECT SE.iname FROM SUPPLY SE WHERE NOT EXISTS
(SELECT I.iname FROM ITEM I WHERE I.type='A' AND NOT EXISTS
(SELECT S.iname FROM SUPPLY S WHERE S.iname=I.iname AND S.dname=SE.dname))

I have not work with SQL much so please help out ... I am using SQL Server if that matters ... Thanks

... I have done the frist 49 of the queries ... and they all made sense ... but this one is just bugging me ... PLEASE HELPDon't feel bad. This is not a simple task.

Some questions:
Does this have to be done in a single SELECT statement, or can it be done using a stored procedure or user-defined function that contains multiple SQL statements?

You need to list items supplied by companies, but all I see in your structure are departments and divisions. Please clarify.

Your DIVISION and DEPT tables are kind of a goofy schema, but I suppose you have no control over that?

What is the purpose of the SELL table vs the SUPPLY table? Can we ignore the SELL table?

Is a company considered to be a supplier of all type "A" items if its departments and divisions collectively supply all the items, or does it have to supply the items itself directly?

Can we ignore the EMP table? It appears to have nothing to do with the problem.

blindman|||Originally posted by blindman
Don't feel bad. This is not a simple task.

Some questions:
Does this have to be done in a single SELECT statement, or can it be done using a stored procedure or user-defined function that contains multiple SQL statements?

You need to list items supplied by companies, but all I see in your structure are departments and divisions. Please clarify.

Your DIVISION and DEPT tables are kind of a goofy schema, but I suppose you have no control over that?

What is the purpose of the SELL table vs the SUPPLY table? Can we ignore the SELL table?

Is a company considered to be a supplier of all type "A" items if its departments and divisions collectively supply all the items, or does it have to supply the items itself directly?

Can we ignore the EMP table? It appears to have nothing to do with the problem.

blindman

The assignment had 50 queries ... of all different kinds.

We have to do it in a single SELECT statement (with subqueries of course)

I do not think that an table except the SUPPLY and ITEM are needed but I dont know exactly how they interact ...|||If your answer must be a single select statement, then that eliminates any possibility of searching the departmental hierarchy, and so actually makes the problem easier.

Break your problem into parts.

If you had a list of companies (departments?) then it would be trivial to list all the products they sell, right?

So your task is to create a subquery that lists all the departments that sell all type "A" products. How do you determine if a department sells all of the type "A" products?

The most direct method would be to find Departments where the number of distinct type "A" products they sell equals the number of type "A" products that exist.

The more subtle method (which your code seems to be attempting) is to create a list of departments that DON'T sell all the type "A" items, and then find all the departments that AREN'T on that list. This is certainly a more confusing method than the product count technique, but it may have some efficient advantages for extremely large tables. I don't know. But I do know you can get a list of departments that don't sell a full line of type "A" products by using a FULL OUTER JOIN between DEPARTMENT and a list of type "A" suppliers.

blindman

Help switching to SQL Authentication

At the moment my asp.net app is working ok and I can connect to the database using windows authentication, however i'm trying to use sql authentication on my local computer.

i'm using sql server studio at the moment to manage the database. And tried numerious combinations of things to try and get it working and allow me to connect to the database using SQL authentication but still no luck :(

can anyone give me some rough step by step instructions to setting up the sql username and password for forms authentication and activating it for a certain database?

thanksWhen you say forms authentication, I assume you mean that in your web.config file you have something like this:

<authentication mode="Forms">
<forms loginUrl="Default.aspx" protection="Validation" timeout="300"/>
</authentication
If so, then a connection string that works connecting to sqlserver is this:

<add name="PolarIntegrationConnectionString2" connectionString="Data Source=INSP8600;Initial Catalog=PolarIntegration;Integrated Security=True"
providerName="System.Data.SqlClient" /
Let me know if that doesn't help.

Help Stored procedure working but not doing anything

Help Stored procedure working but not doing anything New Post
Quote Reply
Please i need some help.

I am calling a stored procedure from asp.net and there is acursor in the stored procedure that does some processing on servaltables.

if i run the stored procedure on Query Analyzer it works and does whatit is suppose to do but if i run it from my asp.net/module control itgoes. acts likes it worked but it does not do what is suppose to do.
i believe the cursor in the stroed procedure does not run where iscalled programmatically from the asp.net/module control page.plus itdoes not throw any errors

This is the code from my control
System.Data.SqlClient.SqlParameter [] param={newSystem.Data.SqlClient.SqlParameter("@.periodStart",Convert.ToDateTime(startDate)),newSystem.Data.SqlClient.SqlParameter("@.periodStart",Convert.ToDateTime(endDate)),newSystem.Data.SqlClient.SqlParameter("@.addedby",UserInfo.FullName+ ""+UserInfo.Username)};
stringstr=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];
System.Data.SqlClient.SqlConnection cn=newSystem.Data.SqlClient.SqlConnection(str);
cn.Open();
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();

SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);

--------THis is the code for my storedprocedure----

CREATE PROCEDURE [dbo].[generatePaylistTuned]
@.periodStart datetime,
@.periodEnd datetime,
@.addedby varchar(40)

AS

begin transaction generatePayList

DECLARE @.pensioner_id int, @.dateadded datetime,
@.amountpaid float,
@.currentMonthlypension float,@.actionType varchar(50),
@.isAlive bit,@.isActive bit,@.message varchar(80),@.NoOfLoadedPensioners int,
@.NoOfDeadPensioners int,@.NoOfEnrolledPensioners int,@.DeactivatedPensioners int,
@.reportSummary varchar(500)

set @.NoOfLoadedPensioners =0

set @.NoOfDeadPensioners=0
set @.NoOfEnrolledPensioners=0
set @.DeactivatedPensioners=0
set @.actionType ="PayList Generation"

DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR

select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension
from pensioner p left outer join pensionpaypoint py on p.pensionerid=py.pensionerId

where p.isActive = 1

OPEN paylist_cursor

FETCH NEXT FROM paylist_cursor
INTO @.pensioner_id,@.isAlive,@.isActive,@.currentMonthlypension

WHILE @.@.FETCH_STATUS = 0
BEGIN

set @.NoOfLoadedPensioners=@.NoOfLoadedPensioners+1
if(@.isAlive=0)
begin
update Pensioner
set isActive=0
where pensionerid=@.pensioner_id
set @.DeactivatedPensioners =@.@.ROWCOUNT+@.DeactivatedPensioners
set @.NoOfDeadPensioners =@.@.ROWCOUNT+@.NoOfDeadPensioners
end
else
begin
insert into pensionpaylist(pensionerId,dateAdded,addedBy,
periodStart,periodEnd,amountPaid)
values(@.pensioner_id,getDate(),@.addedby, @.periodStart, @.periodEnd,@.currentMonthlypension)
set @.NoOfEnrolledPensioners =@.@.ROWCOUNT+ @.NoOfEnrolledPensioners
end

-- Get the next author.
FETCH NEXT FROM paylist_cursor
INTO @.pensioner_id,@.isAlive,@.isActive,@.currentMonthlypension
END

CLOSE paylist_cursor
DEALLOCATE paylist_cursor

set @.reportSummary ="The No. of Pensioners Loaded:"+Convert(varchar,@.NoOfLoadedPensioners)+"<BR>"+"The No. OfDeactivated Pensioners:"+Convert(varchar,@.DeactivatedPensioners)+"<BR>"+"The No. ofEnrolled Pensioners:"+Convert(varchar,@.NoOfEnrolledPensioners)+"<BR>"+"No Of DeadPensioner from Pensioners Loaded: "+Convert(varchar,@.NoOfDeadPensioners)
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @.periodStart, @.periodEnd,@.reportSummary,'Pay List Generation')

if (@.@.ERROR <> 0)
BEGIN

insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @.periodStart,@.periodEnd,@.reportSummary,'Pay List Generation')

ROLLBACK TRANSACTION generatePayList

END

commit Transaction generatePayList
GOYou may need try to use"ExecuteNonQuery() ", not "ExecuteScalar()".|||I used ExecuteNonQuery and it still gave me the same result.it did nothing.
i really need ur help.thanks|||

CREATE PROCEDURE [dbo].[generatePaylistTuned]
@.periodStart datetime,
@.periodEnd datetime,
@.addedby varchar(40)

AS

BEGIN TRANSACTION

DECLARE @.NoOfLoadedPensioners int,
@.NoOfDeadPensioners int,@.NoOfEnrolledPensioners int,@.DeactivatedPensioners int,
@.reportSummary varchar(500)

UPDATE Pensioner
SET isActive=0
WHERE isAlive=0 AND isActive=1

IF (@.@.ERROR=0)
BEGIN

SET @.DeactivatedPensioners=@.@.ROWCOUNT
SET @.NoOfDeadPensioners=@.@.ROWCOUNT

INSERT INTO pensionpaylist(pensionerId,dateAdded,addedBy,periodStart,periodEnd,amountPaid)
SELECT p.pensionerId,getDate(),@.addedby,@.periodStart,@.periodEnd,py.currentMonthlypension
FROM pensioner p
LEFT JOIN pensionpaypoint py on p.pensionerid=py.pensionerId
WHERE p.isActive = 1

IF (@.@.ERROR=0)
BEGIN

SET @.NoOfEnrolledPensioners=@.@.ROWCOUNT
SET @.NoOfLoadedPensioners=@.NoOfEnrolledPensioners+@.DeactivatedPensioners

SET @.reportSummary ="The No. of Pensioners Loaded: "+Convert(varchar,@.NoOfLoadedPensioners)+"<BR>"+"The No. Of Deactivated Pensioners: "+Convert(varchar,@.DeactivatedPensioners)+"<BR>"+"The No. of Enrolled Pensioners: "+Convert(varchar,@.NoOfEnrolledPensioners)+"<BR>"+"No Of Dead Pensioner from Pensioners Loaded: "+Convert(varchar,@.NoOfDeadPensioners)

END
END

IF (@.@.ERROR<>0)
BEGIN
INSERT INTO reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @.periodStart, @.periodEnd,@.reportSummary,'Pay List Generation')
ROLLBACK TRANSACTION
END
ELSE
BEGIN
INSERT INTO reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @.periodStart,@.periodEnd,@.reportSummary,'Pay List Generation')
COMMIT TRANSACTION
END
GO

Should do the same thing as what you had, although run much, much quicker depending on how many Pensioneers you process (The more you process, the faster this will run in comparision). Although, I question the error handling/checking/reporting that you have. I've also changed that part so that if there is an error, only 1 record is inserted into the reportSummary (Your code would have indicated no errors, then another record indicating there was an error, if that is what you really wanted, you'll have to change it)

In any case, I don't see anything in the stored procedure that would cause it to behave differently depending on which environment you are running it in. You've abstracted the sql guts into you sqlhelper class, so we really can't see what you are calling anyhow. I would recommend that you run a sql trace with the sql profiler and see what sql you are generating. Then copy and paste the code into query analyzer/management studio and see if it acts the same there, then play with it until it works. My guess is the problem lies within the SqlHelper class, and it's not generating sql that you would expect from the code you've shown.

|||well thanks but i have run the stored procedure many times in query analyzer and it works fine does what it suppose to do.
but the problem is running it from the application using SqlHelper.executeNonquery that is y i am so confused.

I just don't know what the problem is.
It works in query analyzer but does not do anything when called from the application.
thanks|||I found out the problem.
Iused profiler totrace the events when i call the stored procedure and it gave a sp:cachemiss
so i fixed its working now.
thanks forur help

help sql 2005 express

I have a computer_1 with sql 2005 express with app working fine, from another computer I want to connect to sql 2005 express in computer_1 using tcp/ip or named pipes, in computer_1 is enabled tcp/ip and named pipes, but when I connect using microsoft sql server management studio express I get this error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

another thing is when I put... osql -L in computer_1 .... (local)\sqlexpress not is listen

You need to enable SQL Browser and open the Firewall on your computer so that remote connections can talk to SQL Express.

Check out http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx for information on doing this.

Mike

Help regarding Parameters selections in reporting services 2005.

Hi Experts,
I am working on Reporting services 2005 and i am new for this
software.
I have some reports, and some reports contains more than one
parameters.
Now my problem starts...
My client want reports with more and more parameters, and
at the same time he does not want to select all of them
He is saying that he may or may not select all the parameters.
and if not select all the parameters then still reports should be
generated.
for his selected parameters only.
Suppose i have one report with 4 parameters and first parameter is
Country
second one is states, third one is city, fourth one coustmers name
etc.
How i can implement this.
Now he is saying that if he select only one parameters in country and
leave others,
then report should be generated for all the coustemers for that
country.
Please help me...
Regards
DineshCheck this thread:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/43cddb294583ab2c/dc7146eb95e741bb?lnk=gst&q=all+union+parameter&rnum=3#dc7146eb95e741bb
Hope it helps.
On Mar 30, 1:44 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> Hi Experts,
> I am working on Reporting services 2005 and i am new for this
> software.
> I have some reports, and some reports contains more than one
> parameters.
> Now my problem starts...
> My client want reports with more and more parameters, and
> at the same time he does not want to select all of them
> He is saying that he may or may not select all the parameters.
> and if not select all the parameters then still reports should be
> generated.
> for his selected parameters only.
> Suppose i have one report with 4 parameters and first parameter is
> Country
> second one is states, third one is city, fourth one coustmers name
> etc.
> How i can implement this.
> Now he is saying that if he select only one parameters in country and
> leave others,
> then report should be generated for all the coustemers for that
> country.
> Please help me...
> Regards
> Dinesh|||On Mar 30, 5:12 am, "Alphonse" <amphysv...@.gmail.com> wrote:
> Check this thread:http://groups.google.com/group/microsoft.public.sqlserver.reportingsv...
> Hope it helps.
> On Mar 30, 1:44 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> > Hi Experts,
> > I am working on Reporting services 2005 and i am new for this
> > software.
> > I have some reports, and some reports contains more than one
> > parameters.
> > Now my problem starts...
> > My client want reports with more and more parameters, and
> > at the same time he does not want to select all of them
> > He is saying that he may or may not select all the parameters.
> > and if not select all the parameters then still reports should be
> > generated.
> > for his selected parameters only.
> > Suppose i have one report with 4 parameters and first parameter is
> > Country
> > second one is states, third one is city, fourth one coustmers name
> > etc.
> > How i can implement this.
> > Now he is saying that if he select only one parameters in country and
> > leave others,
> > then report should be generated for all the coustemers for that
> > country.
> > Please help me...
> > Regards
> > Dinesh
Also, I would suggest setting up the report parameters w/a default
value of 'none selected' as part of the dataset that populates the
parameters (most likely w/a union statement in the dataset query).
That way the user can select only what is wanted and the other
parameters automatically use 'none selected.' Then design the stored
procedure/query that populates the report to handle the 'none
selected' accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant