How Do I Display Records For Every 30Th Count.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi,
I have an o/p like as per my query in sql server 2008.

Days Cnt Value
0	 2	  10.40
1	 4	  77.95
2	 0	  15.00
3	 7	  74.00
4	 1	  16.21
5	 0	  6.03
6	 1	  6.00
7	 2	  69.29
8	 1	  96.00
9	 2	  90.58
10	 3	  15.60

I need to display as (this example is just for a reference purpose)

TotDays TotCnt TotValue
0-30	  36    8552.19
31-60	  39    8552.19 
61-90	  96    8552.19

Is it possible to display like above shown in sql server 2008 ?
Please Help .
-- Thanks in Advance :)

Posted 16-Dec-15 1:33am

pn46

Updated 17-Dec-15 1:13am

v6

Add a Solution

Comments

OriginalGriff 16-Dec-15 7:42am

No, not really.
If only because the TotCnt for "0" is 72, and the TotCnt for 0-30 is 36 - which means that it's not a summary of the number of days TotCnts, but something you haven't tried to explain.
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
You need to explain exactly what you are trying to achieve, and provide sample data that actually works to support that.
Use the "Improve question" widget to edit your question and provide better information.

CHill60 16-Dec-15 7:58am

Well you've updated your question to tell us the data is just for a reference purpose but you haven't really clarified what you are trying to do

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-12-16T02:06:00

Solution 2

Try:

SQL

SELECT  CONVERT(NVARCHAR, Min(Totdays)) + '-' + CONVERT(NVARCHAR, Max(TotDays)) AS TotDays, 
        SUM(TotCnt) AS TotCnt, 
		SUM(TotValue) AS TotValue FROM MyTable
GROUP BY TotDays/31

Posted 16-Dec-15 2:06am

OriginalGriff

CHill60 · Answer 2 · 2015-12-16T02:24:00

An alternative

select cast(1 + (30 * ((TotDays - 1)/ 30)) as varchar) + ' to ' + cast(30 + (30 * ((TotDays - 1)/ 30)) as varchar) as TotDays
, SUM(TotCnt) as TotCnt, SUM(TotValue) as TotValue
from pn46
GROUP BY ((TotDays - 1)/ 30)

Which I tested with the following

SQL

Create table pn46
(
	TotDays int identity(1,1),
	TotCnt int,
	TotValue float
)

go
insert into pn46 values(1,10.0)
go 100

And it correctly returned

1 to 30	        30	300
31 to 60	30	300
61 to 90	30	300
91 to 120	10	100

etc

venkataramana k · Answer 3 · 2015-12-16T02:03:00

Solution 1

Select ID, LastName, FirstName FROM Users WHERE ID%30 = 0 ORDER BY ID

instead of id u have to use totdays ,may be it will helpful.

Posted 16-Dec-15 2:03am

venkataramana k