How to create sql for retrieving 10 records per time in .NET

I want to create sql for retrieving 10 records per time but need to know the total result matches.
And I have previous and next button for retrieving previous or next 10 records.
ThanksAs a great man once said: Huh?

I'm sure that your posting made sense to you, but I don't even have a clue what you meant. Could you try to repost and fill in a few of the details that I'm missing? I'd like to help you if I could, but at the moment I don't understand enough of what you want to have a chance!

-PatP|||Hi,

as Pat said, this isn't very clear and you really should repost. I just want to comment on what you may need to consider mentioning.

Almost all databases are used by multiple users at the same time. Selecting the 10 first records in a particular order makes perfect sense, as does selecting record 11 to 20 of that same recordset. But selecting 10 and then (at a later time) selecting "the ten next" doesn't have any clear meaning since the data may well have changed in the meantime. So perhaps it's a good idea to clarify how you want your system to deal with the different change scenarios: records may have been added, removed or updated, or any mix of the three between the two queries.

One way to "page in the database":
Initialize:
- establish a single orderby column for your query (if you have several columns, combine them into one column yielding equivalent sort order)
- select the top 10 records
- persist the value of the orderby column for the last record. In ASP.NET, use ViewState for this.

Each round trip:
- use the value of the last record in the previous result set to select the next ten:
SELECT TOP 10 ... WHERE ... AND ordCol > @.lastValue ORDER BY ordCol
- persist the last value again

Of course, if you require to page the other way (previous page) you must also persist the value of the first record. Furthermore, since there's no BOTTOM keyword to mirror TOP, you must use reverse sort order (otherwise, if you did SELECT TOP 10 ... WHERE ordCol < @.firstVal, the first page would be returned even if you tried to get the previous one from, say, page 5). Since you still want to display in-order, fix this by selecting into a temp table and then select in-order from that one:

SELECT TOP 10 INTO #page ... WHERE ... AND ordCol < @.firstVal ORDER BY ordCol DESC
SELECT * FROM #page ORDER BY ordCol

I hope this helps!

Dag|||Or you could just set-up paging in a datagrid...|||Huh?

Just a guess...

You want to do paging

http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx

Make sure to say hi to Jeff for me
__________________|||I meant that you could use a .NET DataGrid control that allows you to view a recordset one page at a time. You can define how many records you want displayed on each page. Of course this deals with paging at the Application level - don't know if that's what he wants but just thought I'd mention it.

I don't have a clue about paging at the SQL Server level...:-)|||Sorry for unclear question. Yes, what i want to do is paging.
Brett: The URL http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx is unavailable
mmcdonald: Thanks for your suggestion but the user don't want the result be shown in datagrid :(

How to create Parameter Field with embedded SQL

I want to create an expression field on my report that performs an sql statement that I specify to bring back a total instead of using the built-n functions for SSRS, how can I do this? Basically just like in Crystal where you can create a formula field that runs a sql statement then brings back a value from your sql statement. This value is not related to the other data in the report...so yes in theory you may be thinking subreport...is tha the only way? or can I create some sort of textbox that shows the total I want below?

DECLARE @.TotalDaysInMonth int,

@.today datetime,

@.TotalWeekendDays int,

@.TotalHolidaysThisMonth int,

@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH

SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN

31

ELSE

DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))

END

SELECT @.TotalDaysInMonth

-- TOTAL HOLIDAYS THIS MONTH

SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM Apex_ReportingServer.dbo.Holidays

WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))

AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

SELECT @.TotalHolidaysThisMonth

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME

SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END

SELECT @.TotalWeekendDays

SELECT @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)

SELECT @.TotalPostingDays

You could simply add the SQL Statement above to a new/different Dataset and reference that Dataset in the given field on your report...does that make sense, or am I missing something?

|||exactly thanks! I actually tried that and it worked before you posted...thanks you were right on.

How to create Parameter Field with embedded SQL

I want to create an expression field on my report that performs an sql statement that I specify to bring back a total instead of using the built-n functions for SSRS, how can I do this? Basically just like in Crystal where you can create a formula field that runs a sql statement then brings back a value from your sql statement. This value is not related to the other data in the report...so yes in theory you may be thinking subreport...is tha the only way? or can I create some sort of textbox that shows the total I want below?

DECLARE @.TotalDaysInMonth int,

@.today datetime,

@.TotalWeekendDays int,

@.TotalHolidaysThisMonth int,

@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH

SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN

31

ELSE

DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))

END

SELECT @.TotalDaysInMonth

-- TOTAL HOLIDAYS THIS MONTH

SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM Apex_ReportingServer.dbo.Holidays

WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))

AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

SELECT @.TotalHolidaysThisMonth

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME

SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END

SELECT @.TotalWeekendDays

SELECT @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)

SELECT @.TotalPostingDays

You could simply add the SQL Statement above to a new/different Dataset and reference that Dataset in the given field on your report...does that make sense, or am I missing something?

|||exactly thanks! I actually tried that and it worked before you posted...thanks you were right on.sql

how to create index (Case when)

How to create index when there is a SQL statement like
Select count(1) as [Total],
sum(Case when Field1 < Field2 then 1 else 0 End) as [Selected]
from Table1
Thx in Adv
XLDBUse one index on each column ( field 1 field 2 ) ...use nonclustered for each

however, if table is small ( say less than 1000 row s ) SQL probably wont use indexes..it will scan whole table.|||the table has 2 million records|||actually, there are 2 similar tables that i use the query on...

the table with 2 million records is taking 57 seconds
the table with 40,000 records is taking 1 second

there is only 1 index on "Field1" in "Case When" statement

How to create Calculations that do not change when dimensions are added.

I would like to calculate the total business done for a week irrespective of the dimension used and I have created a member ([Measures].ForAWeekOfYear) for the same. The Percent1 member calculates the percentage of business done by a company.

with member [Measures].ForAWeekOfYear as
sum( [Dimdate].[Week Of Year] , [Measures].[TotalBusiness] )
member Measures.Percent1 as [Measures].[TotalBusiness]/[Measures].ForAWeekOfYear
select { [Measures].ForAWeekOfYear , ( Measures.Percent1 ) } on 0,
{ [Dim Customer].[Company].members * [Dimdate].[Week Of Year].Members} on 1
from [Account]

The problem I have if I use the above MDX expression is that the calculation gets split based on the Customer dimension and I assume that this is how the query should work. I assume I have to use the SCOPE statement, but not sure how to use it.

Please note that I am trying to execute this in Management console and not within a cube.

If you don't want your calculation to depend on Customer dimension, you should include Root(Customer). If you want to abstract from all of the dimensions in the cube - use Root().

HTH,

Mosha (http://www.mosha.com/msolap)

How to create and combine 4 reports, one being a total off fields from the other 3 ?

If I have one rdl.. and I need 4 reports in it, one has a table that is a summary off certain fields from the other 3. I have some questions then

1) How do I add 4 reports to the same report so that I end up with 4 Excel tabs?

2) How can I relate a 4th report to be a summary report of all 3 other reports in the same rdl?

My Excel file in the end will have 4 sheets. 3 of them will be reports, and the final will be a summary table that sums up certain fields based off the other 3 reports. I am not sure the approach to both create the 4 reports together, then ensure pageination is correct in that I end up wtih 4 sheets in an excel workbook when doing an export in the end. You gave a great explanation but now I have more questions :)

Please also reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=233219&SiteID=1&mode=1

I responded on the original thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=233219&SiteID=1&mode=1

how to count the total rows for a query with group by?

Hi,
I have a query like
select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY from tbl1, tbl2 where
tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
for example, I can get the results like
f1 f2 YY
--
x1 y1 2
x2 y2 5
x3 y3 1
As you can see, total rows for the query is 3 (rows). How can I get
the "3" in the one select query like:
f1 f2 YY Total_Rows
--
x1 y1 2 3
x2 y2 5 3
x3 y3 1 3
or write another query to count the total rows (to get the "3") only?
Thanks a lot.
Ouyanghi
just see this:
select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY ( select count(*) from tbl1,
tbl2 where
tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2 ) as total_rows from tbl1, tbl2
where
tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
probably this solves your problem
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"zxo102" wrote:

> Hi,
> I have a query like
> select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY from tbl1, tbl2 where
> tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
> for example, I can get the results like
>
> f1 f2 YY
> --
> x1 y1 2
> x2 y2 5
> x3 y3 1
>
> As you can see, total rows for the query is 3 (rows). How can I get
> the "3" in the one select query like:
> f1 f2 YY Total_Rows
> --
> x1 y1 2 3
> x2 y2 5 3
> x3 y3 1 3
> or write another query to count the total rows (to get the "3") only?
>
> Thanks a lot.
> Ouyang
>|||You may also want to summarise the data in a report style query using
COMPUTE, the link below will take you to an example on how to implement
this:
http://www.mssql.com.au/kb/html/gmg... />
&@.sa_id=58
"zxo102" <zxo102@.gmail.com> wrote in message
news:1122690598.655507.63480@.g47g2000cwa.googlegroups.com...
> Hi,
> I have a query like
> select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY from tbl1, tbl2 where
> tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
> for example, I can get the results like
>
> f1 f2 YY
> --
> x1 y1 2
> x2 y2 5
> x3 y3 1
>
> As you can see, total rows for the query is 3 (rows). How can I get
> the "3" in the one select query like:
> f1 f2 YY Total_Rows
> --
> x1 y1 2 3
> x2 y2 5 3
> x3 y3 1 3
> or write another query to count the total rows (to get the "3") only?
>
> Thanks a lot.
> Ouyang
>|||Hi Chandra,
this sql query:
"select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2 group by
tbl1.f1, tbl2.f2"
gives group count instead of total count from the query with "group
by".
Any ideas?
Thanks.
Ouyang|||hi
can you please send the ddl so that i can help u out
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"zxo102" wrote:

> Hi Chandra,
> this sql query:
> "select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2 group by
> tbl1.f1, tbl2.f2"
> gives group count instead of total count from the query with "group
> by".
> Any ideas?
> Thanks.
> Ouyang
>|||hi
i am sorry, you can try this:
select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"zxo102" wrote:

> Hi Chandra,
> this sql query:
> "select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2 group by
> tbl1.f1, tbl2.f2"
> gives group count instead of total count from the query with "group
> by".
> Any ideas?
> Thanks.
> Ouyang
>|||Hi Guillaume,
Thanks for your information. I got the total row using "compute".
But the returned results from SQL server are in two parts. The value
of the total row is in the second part.
Since I call this query from python application which can not grab the
value from the second part, do you know how to get the total row in a
query which just return the total row only?
Thanks a lot.
Ouyang|||Hi Chandra,
My ddl is as follows:
select t_all_status.enter_diag AS r_ICDCODE,
count(t_all_status.enter_diag) AS r_SUM2,
t_icd.chinese_diag AS r_CHINESE,
t_dept.dept_name as r_DEPT_NAME
from t_all_status, t_icd,t_dept
where (t_all_status.enter_dept=t_dept.dept_code)
AND (t_all_status.enter_diag not like 'M%')
AND (t_all_status.enter_diag = t_icd.icdcode)
group by t_dept.dept_name,t_all_status.enter_diag, t_icd.chinese_diag
order by r_SUM2 DESC
Thanks
Ouyang|||Put the row count in an output variable. Maybe something like this:
select *
from ...
set @.finalRowCount = @.@.rowcount
@.finalRowCount must be declared as an in/out parameter of your procedure.
ML|||Hi ML,
I don't know very much about the stuff you mentioned, can you give
me an exmaple for that using my DDL as follows:
select t_all_status.enter_diag AS r_ICDCODE,
count(t_all_status.enter_diag) AS r_SUM2,
t_icd.chinese_diag AS r_CHINESE,
t_dept.dept_name as r_DEPT_NAME
from t_all_status, t_icd,t_dept
where (t_all_status.enter_dept=3Dt_dep=ADt.dept_code)
AND (t_all_status.enter_diag not like 'M%')
AND (t_all_status.enter_diag =3D t_icd.icdcode)
group by t_dept.dept_name,t_all_status.=ADenter_diag,
t_icd.chinese_diag
order by r_SUM2 DESC=20
I really appraciate your help.
Ouyang