Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Group By Positive Or Negative Number In Table


Sorry if this question has been answered before--i tried a search but nothing came close to what i needed.

I have a report I am creating where I need to group on whether a number is positive or negative.  The number will never be zero.  This number is a decimal (currency), if that makes a difference.

I have tried creating a formula field with the following expression:





Code Snippet

IIf(Fields!Num.Value > 0, 1, 0)

I tried creating a group using this field (essentially, positive = 1 and negative = 0), but it gave me no results at all.

If you need any more information, please let me know and I will provide what I can.  Thanks for your answers!


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How Do You Convert A Number From Negative To Positive?
 I was just wondering if there is a way of converting a number from negative to positive within Reporting Services, ie remove the "-" sign from the front of the number?  One of the columns within my report contains a calculation which results in an integer, but it is always a negative number, even though it should really be positive (it would take too long to describe the exact reason for this, but it's to do with the underlying database, which I don't have access to anyway). When I right-click on the relevant cell within the table in Layout view in the Report Designer and go to Expression, there is a list of Conversion functions under the "Common Functions" heading but I can't find anything there which would convert a number from negative to positive.  Does anyone know how this can be achieved?

View Replies !   View Related
Creating A Blank Row In A Table Between Positive && Negative Values
I have a table that displays a bunch of data based on their percentage changes.

There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%.

I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this?

Thanks,
Steven

View Replies !   View Related
QA Returning Negative #s As Positive
Hey guys,

I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.

The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.

Is there a known bug in QA that would manifest itself as this? What is the cause for this?

Thanks in advance.

View Replies !   View Related
Positive Negative Values In SUM
 



Hi,


I have table with a column which has both negative and positive values.

I want to calculate the sum of positve values and the sum of negative values from that column separately. Is it possible.

Plase help me.


Ex: My  Input Values are
 
   column1
-186.5499257
-141.8985222
-95.3846883
-71.1359768
-69.39071172
30.44750844
40.33666032
53.81527872
98.2967252
112.948667
 
OutPut goes like This
 
  Column1                    Column2
-186.5499257               null
-141.8985222               null
-95.3846883                 null
-71.1359768                 null
-69.39071172            -564.36  
30.44750844               null
40.33666032               null
53.81527872               null
98.2967252                 null
112.948667               335.84
 
 

View Replies !   View Related
Negative Numbers Become Positive When Transferring From 400
Help! I have a DTS package that does a data transfer between an AS400 and SQL 7. There is a field in the 400 table that contains negative numbers. After the transfer, the same field in the SQL table has lost the negative sign and is now positive. Also, the data in the original table has two decimal places. After the transfer, there are fifteen decimal places and it appears that it has been "unrounded", i.e. instead of 98.80, it's now 98.799999999999997.

Prior to upgrading to 7.0, we were doing the same thing with 6.5 using HDR to do the transfer. It worked perfectly then.

Anyone have any ideas??? Your help is MUCH appreciated.

Chris W.

View Replies !   View Related
How Do I Delibrately Make A Positive Value A Negative Value
 

 I want to convert a positive value to a negative value.

How do I do it...

View Replies !   View Related
Treat Data Differently For Positive Vs Negative #
Ok, I have a table with IP addresses stored in decimal format using both positive and negative numbers.
The way that they are stored is:
Positve
1 thru 2147483647 = 0.0.0.1 - 127.255.255.255
Negative
-2147483648 thru -1 = 128.0.0.0 - 255.255.255.255
Conversion
positive
x/2^24 . (x/2^24)/2^16 . etc . etc
negative
(x+2^32)/2^24 . ((x+2^32)/2^24)/2^16 . etc . etc

I have a script which works by using UNION and the WHERE statements are x>0 x<0

My problem is I need to use a 3rd party app to run the script (McAfee ePO). McAfee does not recognize the UNION. My question is, can I acheive the same results as the script below, without using UNION.

SELECT ReportFullPathNode.FullPathName,
cast(cast(IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
IPSubnetMask.IP_Start
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start>0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID
UNION ALL
SELECT ReportFullPathNode.FullPathName,
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
IPSubnetMask.IP_Start+4294967296
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start<0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID

View Replies !   View Related
Stacked Column Bar With Negative And Positive Value. Cannot Cross At Zero
 

I've a stacked column chart (vertical) both with positive and negative value.
 
I want an horizontal line only for the value = 0.
 
I tried to add a serie and to plot it as line, but I see it in the legend and I don't want to see it.
 
Can I show the gridlines only for the value 0?
Simona

View Replies !   View Related
Query Analyzer Shows Negative Numbers As Positive Numbers
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View Replies !   View Related
Inserting Negative Number?
Hi

I'm a newbie when it comes to using SS7. When I try to insert a negative number (-1) into a integer column, the value is changed to 65535. How can overcome this problem?

TIA

JDJ
jdj@gvd.dk

View Replies !   View Related
Meaning ??? Negative Number And Out Of Lock
Help

Why is the unsed space is negative number? Whould that affect the database? How do you fix the negative number?

If 'out of lock' happen, how do you check at what level the object was/is locked and how many locks is needed to configure?

Thank you ahead of time

View Replies !   View Related
DATEDIFF Returns Negative Number
I am trying to output the difference in minutes between 2 times. The sql statement works for the most part but if I compare a time before midnight and a time after midnight, the query returns a negative number.

Here is my SQL statement:

SELECT
dbo.fdc_trips.enrdate,dbo.FDC_Trips.enrtime, dbo.fdc_trips.atsdate,dbo.FDC_Trips.atstime, DATEDIFF(n, dbo.FDC_Trips.enrtime, dbo.FDC_Trips.atstime)
AS TimeDiff
FROM dbo.FDC_Trips

For example: if enrtime = 23:57:00 and atstime = 00:04:00, the DATEDIFF formula returns -1433, and really it should be 7.

How can I get this query to work with times before and after midnight?

View Replies !   View Related
Negative Number Parenthesis Alignment
I am  representing negative number within parenthesis , but as I am working on reporting service, it is possible to make the exact alignment of the number and parenthesis.
 
here is one example ....
when i format number with #,##,0;(#,##,0) i am getting output in a format  in subsequent rows like
 

  123,345
(145,567)
 
How can I make this alignment like
 
 123,345
(145,567)
 
where the parenthesis will be outside the number space keeping in mind the alignment is in right .
 
any help will be apriciated.
 
thanks
Aru

View Replies !   View Related
Sp_spaceused Report Negative Number For Unused
Could some one help me to explain what does it means when I ran sp_spaceused on the database it reported ex. -15430kb for unused. Does this mean that the index ran out off space. Do I need to increase space for the database. I will try to run the dbcc check hope it will fix the problem.

Thanks.

View Replies !   View Related
Convert Negative Number Stored As Nvarchar
I'm working with a horrible database!  The field I'm having issues with is a negative number stored as a nvarchar, but it's not stored in a consistent format.  I need to convert the field to a decimal (9,2) but I can't because of the negative sign.
 
Example:
 
Balance
00000000
0000-413
0000-913
00-10913
00009526
 
I don't even know where to start to convert this.  Any help is appreciated!
 
Thanks.
 

View Replies !   View Related
Row Number Of Group Footer
How would I get the rownumber() for a Group Footer in report designer not in my query?

I put the syntax rownumber(nothing), but it is giving me the row numbers for the detail section.

Any help would be appreciated.

View Replies !   View Related
How To Get Number Of Pages For A Group?
Like many others, I have a large report that groups on invoices, and does a page break between each invoice.  From reading other posts in this forum, I was able to reset the page number on the grouping, however, there are some things that I need to display only on the last page of each invoice, but I don't see how I will be able to determine when I'm on the last page for a particular invoice.  Can I somehow determine up front the number of pages for a group so I know when I'm at the last page?

Any help is greatly appreciated!

View Replies !   View Related
How To Insert Sequence Number With Group
SOURCE TABLE
ID________COMMENT
123_______I am joe
123_______I am programmer
124_______I am Wang
124_______I am programmer
124_______I like cricket

DESTINATION TABLE

ID_____SEQ______COMMENT
123_____1_______I am joe
123_____2_______I am programmer
124_____1_______I am wang
124_____2_______I am programmer
124_____3_______I like cricket
can somebody please advise the easiest way to do this in sql 2000?

View Replies !   View Related
How To Select Top N Number Per Date Group?
I have 1 table named BookMe,

with fields

$Date, $Div, $Name

I want to select the top N number of Div's (ie highest Div value) per distinct Date.

i know how to get the distinct date like this:

SELECT DISTINCT $Date FROM BookMe ORDER BY $Date

 

How do I combine a TOP keyword to get the TOP n rows per distinct date???

 

Thx!

View Replies !   View Related
Any Way To Show A Group Detail Header Row Once For Each Group In A Table?
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group.  The table will not have a real header or footer.  Thanks for the help.

View Replies !   View Related
How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time
Hi

I want to enter rows into a table having more number of columns
 
For example : I have one employee table having columns (name ,address,salary etc )
                     then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View Replies !   View Related
Maximum Number Of Group-by Expressions Exceeded
Is there any way of configuring this parameter?

A work around?

Or have I reached the limits of scalablilty on this issue?

Please assist me

Thanks

View Replies !   View Related
How To Extract A Sequence Number From The Group By Count?
Hi,

I have a situation where I have to write a SQL to generate a sequence number from the count of the group by.

Example:

I have src data :

---------------------------------------------------------------------------------------------------------------

SUBSCRID         col1     col2     col 3     col4

1001                  val1        val2      val3    val4

1002                 vala         valb      valc     valc

1001                 ................

1002                 ..........

1001                  ...........

1003                 ................

---------------------------------------------------------------------------------------------------------------

So my result should be :

---------------------------------------------------------------------------------------------------------------

SUBSCRID      SUBSCRCNT       col1          col2       col3       col4   

1001                1                        val1        val2       val3       val4

1001              2                       ...................

1001              3                       .....................

1002             1                       vala          valb       valc       valc

1002              2                     ................

1003              1                    .................

---------------------------------------------------------------------------------------------------------------

Any help on how to write a query will be appreciated?

Thanks,

Venkat

View Replies !   View Related
Reset Total Page Number In A Group
I know how to reset the page numbers with each group, but how do you reset the total page number within each group.

EX.  Code for page of total pages

="Page " & Globals.PageNumber & " of  " & Globals.TotalPages

 

EX. Code to reset within a group
Custom Code:
Shared offset as Integer
Shared currentgroup as object
 
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If not (group = currentgroup)
offset = pagenumber - 1
currentgroup= group
end if
return pagenumber - offset
end function
 
=Code.GetGroupPageNumber(ReportItems!Category.Value(grouping),Globals!PageNumber)
 
What I need is code for a combination of the two...to display code for page of total pages that resets within a group.
 
Any help is greatly appreciated.
Thanks!

 

View Replies !   View Related
Resetting Page Number On Group Break
 

Hi,
I have a report where i do a page break for each supplier. I want to reset my page number to '1' when there is a group break.When i googled for this functionality i found the following chris Hay's blog which provides the solution.
http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx
But if i follow the same, I get #Error instead of page number during the run time.How do i resolve the error?
Also, I want to display the "page 1 of totalpages in group" like that. Is that possible?
 
Thanks in advance

View Replies !   View Related
SELECT TOP With Additional Requirement: (limited Number Per Group)
This is a little bit tricky:

2 tables:

1) tblAlpha:
Name Alpha
ABC 0.111
DEF 0.222
....

2) tblSector:
Name Sector
ABC Industry
DEF Technology
.....

I want to select top X Names from tblAlpha, ordered by Alpha, but I want to
limit the number of names for any Sector (Q).
E.g. X = 10, but Q = 10*50%= 5, that means I want the top 10 Alpha from
tblAlpha, BUT any Sector can not have more than 5 to be selected. So if all
Top 10 Alphas are Technology, I can only select top 5 of them and search
down the list further.

This should involve JOIN and TOP, maybe create some temp table, What should
I do to make it work?

Thanks a lot.

View Replies !   View Related
How Can I Show A Row Inside A Table Group Just Once After The Group Row?
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group.  The table will not have a real header or footer.  Thanks for the help.

View Replies !   View Related
Conditional Number Format On Specific Matrix Column Group
I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
 
For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125
 
By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
                     Revenue         Cost           Total
Customer1          -100             60             -40
Customer2          -200            125            -75
 
But the users need the report to look like this, with all positives (why, oh why?!  ):
                     Revenue         Cost           Total
Customer1          100             60              40
Customer2          200            125             75
 

I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
 
=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
 
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
 
=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
 
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
 
How else can I do this? I must be missing something here. Thanks.

View Replies !   View Related
Positive Value
 
I have the following query
 
OutPut:
 
ChildName     BillPayerName     Cost
Mary                Fred                          £50  
 
Total Balance                                 Blank
 
The problem is when I check for a value > 00.1 in the having clause the total balance column disappears.
But its ok if I use any other comparison operator.
I.e < 00.1.
 
 
Any ideas.
 

create procedure Rpt_pos1 --(@cmb1 as varchar(50)) with ENCRYPTION as

--declare @startdate smalldatetime

AS

set dateformat ymd

set datefirst 7
 

--loop through weeks

create table #getTable (thesite integer null,thechild integer null,cost money null)

insert #getTable select s.siteid,c.childid,0

from site s,child c

where --s.siteName=@cmb1

c.siteID=s.siteID


and c.active=1

and c.potential=0
 

insert #getTable

select s.siteid,c.childid,isnull(sum(round(bt.total,2,2)),0)*-1

from site s,child c,billingTotal bt

where --s.siteName=@cmb1

s.siteID=bt.siteid


and c.siteID=s.siteID

and bt.siteID=s.siteID

and bt.childid=c.childid

and c.active=1

and c.potential=0

group by s.siteID,c.childid
 

insert #getTable select s.siteid,c.childid,isnull(sum(round(p.amount,2,2)),0)

from site s,child c,payment p

where --s.siteName=@cmb1

p.siteID=s.siteid


and c.siteID=s.siteID

and p.childID=c.childID

and c.active=1

and c.potential=0

group by s.siteID,c.childid
 

create table #results (ChildName varchar(200) null,BillPayerName varchar(200) null, Cost varchar(20) null)

insert

#results

select c.forename + ' ' + c.surname

,case b.telhome when '' then b.Forename + ' ' + b.Surname

else b.Forename + ' ' + b.Surname + ' (' + b.telhome + ')' end

,case when sum(round(g.cost,2,2)) < 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) > 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) = 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15)) end

from

#gettable g

,child c

,billpayer b

,currency cu

,site s

where

cu.currencyid=s.currency


and s.siteID=c.siteID

and c.siteID=g.thesite

and c.childID=g.thechild

and b.billpayerID=c.billPayerID

group by

c.forename,

c.surname,

b.telhome,

b.surname,

b.forename,

c.siteID,

cu.symbol


having sum(round(g.cost,2,2)) > 00.1

order by

c.surname asc

insert #results

select '','',''

insert #results

select

'Total Balance'

,''

,c.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

from

#gettable g

,site s

,currency c

where

g.thesite=s.siteid

and c.currencyID=s.currency



group by

c.symbol


having sum(round(g.cost,2,2)) > 00.1

drop table #gettable

select * from #results

drop table #results

go

 

View Replies !   View Related
Check For Positive Value
I need to be able to check & display people who have a positive value in the bills.
i.e > 1p.
On the billingtotal table i need to sum > 1p.
 
 

CREATE PROCEDURE rpt_PositiveAccountBalances

(@cmb1 as varchar(100)) WITH ENCRYPTION

AS

BEGIN

SELECT DISTINCT




Site.siteName,

Child.surname + ', ' + Child.forename AS ChildsName,

billPayer.surname + ', ' + billPayer.forename AS BillPayerName,

BillingTotal.total

FROM

Site

INNER JOIN


Child

ON


Site.siteID = Child.siteID

INNER JOIN


BillingTotal

ON


Site.siteID = BillingTotal.siteID

CROSS JOIN


billPayer

WHERE


site.sitename=@cmb1

END

 

View Replies !   View Related
Nagative To Positive, Format
one value should be always positive in report

I just replace "-" to " " using  Replace(Fields!sumamount.Value,"-","") and format is currency like $ 1234.12

but after replace I get 1234.1200 in report

 

format of cell is currency like $ 1234.12 and if I don't use replace - it's ok

 

how to remove two last zero and add "$" ? or may be other ideas how to show alway positive value ?

 

View Replies !   View Related
Showing The + Sign As Prefix For Positive Numbers
Hi,

I would like to know how to Format a textbox to show the positive sign (+) in the even that the number more than zero.

I would prefer to do this without writing an specific IIf function into the expression if possible.

The common formats provided did not specify anything on handling prefix positive negative signs.

Thanks.

Kenny

View Replies !   View Related
Selecting ONLY Records From One Table Having ALL Data Contained In Other Table (GROUP BY?)
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz

View Replies !   View Related
How To Archiv Table To Another Table With Unique Number For All Rows Once + Date
need help
how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute)
i need whan i insert to the another table add 2 more fields (unique number      ,         date_time )
 
this is the table 1 i select from
ID      fname      new_date      val_holiday
----------------------------------------------------

111   aaaa         15/03/2008       1
111   aaaa         16/03/2008       1
111   aaaa         18/03/2008       1
111   aaaa         19/03/2008       1
111   aaaa         20/03/2008       1
111   aaaa         21/03/2008       1
 
222  bbb            02/05/2008       3
222  bbb            03/05/2008       3
222  bbb            04/05/2008       3
222  bbb            05/05/2008       3
222  bbb            06/05/2008       3
222  bbb            07/05/2008       3
222  bbb            08/05/2008       3
222  bbb            09/05/2008       3
 
333  ccc            03/04/2008       4
333  ccc            04/04/2008       4
 
this is the table 2 i insert into
----------------------------------
ID      fname      new_date      val_holiday     unique number               date_time
--------------------------------------------------------------------------------------------------------------------

111   aaaa         15/03/2008       1                     666                           15/04/2008 17:03
111   aaaa         16/03/2008       1                    666                             15/04/2008 17:03
111   aaaa         18/03/2008       1
111   aaaa         19/03/2008       1                    666                             15/04/2008 17:03
111   aaaa         20/03/2008       1                    666                             15/04/2008 17:03
111   aaaa         21/03/2008       1                    666                            15/04/2008 17:03
 
222  bbb            02/05/2008       3                    666                             15/04/2008 17:03
222  bbb            03/05/2008       3
222  bbb            04/05/2008       3                   666                             15/04/2008 17:03
222  bbb            05/05/2008       3                   666                              15/04/2008 17:03
222  bbb            06/05/2008       3                  666                              15/04/2008 17:03
222  bbb            07/05/2008       3                  666                               15/04/2008 17:03
222  bbb            08/05/2008       3                  666                              15/04/2008 17:03
222  bbb            09/05/2008       3                  666                            15/04/2008 17:03
 
333  ccc            03/04/2008       4                666                               15/04/2008 17:03
333  ccc            04/04/2008       4               666                              15/04/2008 17:03
 
for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)
 
next insert ......
ID      fname      new_date      val_holiday     unique number               date_time
--------------------------------------------------------------------------------------------------------------------

111   aaaa         15/03/2008       1                     667                           15/04/2008 17:15
111   aaaa         16/03/2008       1                    667                            15/04/2008 17:15
111   aaaa         18/03/2008       1
111   aaaa         19/03/2008       1                    667                            15/04/2008 17:15

.........................
.....................................................................667                            15/04/2008 17:15                 

 
next insert ......
ID      fname      new_date      val_holiday     unique number               date_time
--------------------------------------------------------------------------------------------------------------------

111   aaaa         15/03/2008       1                     668                           15/04/2008 08:15
111   aaaa         16/03/2008       1                    668                            15/04/2008 08:15
111   aaaa         18/03/2008       1
111   aaaa         19/03/2008       1                    668                            15/04/2008 08:15

.........................
.....................................................................668                            15/04/2008 08:15       

 
 
TNX

View Replies !   View Related
&"Column Name Or Number Of Supplied Values Does Not Match Table Definition&" When Trying To Populate Temp Table
Hello,

I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp

INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?

The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.

Thanks,
Greg.

View Replies !   View Related
How To Group This Table??
 

Hi guys

I need to aggregate a table to three different levels but I need the results in a single table. Here is the sample data












IndicatorName
DHBName
PHOName
Practice
PracticeName
Numerator
Denominator

ABC
SAM
a
PracticeA
QW
22500
22.5

BNN
SAM
b
PracticeB
SSS
22500
22.5

dddd
JONES
c
PracticeC
FFFF
22500
45

ssss
Alter
d
PracticeZ
QW
22500
22.5

rrrr
Sam
a
PracticeA
FFFF
52500
60

ABC
GINI
b
PracticeA
ASDFF
45000
45

BNN
Hoe
c
PracticeD
Tahunanui Medical Centre
45000
15

Now I need to group this table first on the dhb level:

Query used
 SELECT     IndicatorName, DHBName,sum( Num),sum( Den)
FROM         DHBLevel
GROUP BY IndicatorName, DHBName

 

Then group on PHO Level

SELECT     IndicatorName, DHBName, phoname,SUM(Num) AS Expr1, SUM(Den) AS Expr2
FROM         DHBLevel
GROUP BY IndicatorName, DHBName,phoname

 

Then on Practice Level

SELECT     IndicatorName, DHBName, phoname,practicename,SUM(Num) AS Expr1, SUM(Den) AS Expr2
FROM         DHBLevel
GROUP BY IndicatorName, DHBName,phoname,practicename.

Now I need to see the aggregates in 1 single table only.

How shall i do this??

Here is the create table script

USE [PhoTest]

CREATE TABLE [dbo].[performanceOctober](



[IndicatorName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,



[DHBName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,



[PHOName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,



[PracticeName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,



[Numerator] [float] NULL,

[Denominator] [float] NULL

) ON [PRIMARY]

View Replies !   View Related
Row Number Of A Table
Hi

Table name.. Emp
col- Name Salary
a 500
b 700
c 800
there must the the row no which is maintain by sql server.
So I want to retrive the row number of a given name.
How can i do this..

thanks

ASM

View Replies !   View Related
Total Of A Group In Table
 

I have a report that looks like below. It's grouped by Product then by Year. I want to include within the Product grouping and item called "All products". If I swapped order of Grouping to Year then Product, I could simply add a SUM in the group header, but the users don't want it displayed that way round.
Any suggestions for "All products" ?
Thanks
Richard
 
Revenue
Product 1
2007
2008
2009
Product 2
2007
2008
2009
etc
 
I want to add
All products
2007
2008
2009
 
 

View Replies !   View Related
Table Group Product
Week1





Task1
58.12%



Task2
98.34%



Task3
74.45%



Task4
66.54%



Overall
28.31%


Week2





Task1
74.35%



Task2
56.68%



Task3
66.88%



Overall
28.18%


For the table above, I need to calculate the Overall Percentage

Overall = Task1*Task2*Task3*Task4 (Maximum 4 Tasks in one week)

I Used one Week Group and another Task group

 

If I place the "overall" in the Week group Footer, the value is coming as Zero.

 

How do I calculate the Overall value?I wrote custom code but didn't help. Also the overall value has to be one per week.

 

Thanks for u'r help.

View Replies !   View Related
Calculate Inner Group In Table
I'm trying to grab the Total for each "brand" in my table group (this is not in a matrix).

                                               Period 1                    Period 2               Period 3
Sensible Choice          2007     3843                            4020                      4746
                          2008     1830                            3352                      1025
                          Total:   1830


Maxi                           2007       124388                      132248                  162429
    2008        127729                      194458                   13732
    Total:       127729



I want to make the total the same amount as the 2008 total per period. (everything in yellow is the group 1 (outer group and the red is the inner group. When i tried to get the value for the year 2008 and put it in the total field, i put Last(Fields!PeriodTotal.value), but it doesnt work, because sometimes it gives me the 2007 total, and sometimes it gives me the 2008 total. I dont know why. Any suggestions on how i can get the 2008 value everytime.

View Replies !   View Related
Subreport In Group Of Table
 

Hi all,
 
I am creating a report based on a table in SQL Server 2005 Reporting Sevices. I need to display a subreport within the groupheader of a group I inserted to the table.
When I try to preview the report I'll get the following error: "An error occured during local report processing. An internal error occurred on the report server."
 
When I place the subreport outside of the table the report runs.
I tested the scenario in an emty table. When I place the report in the details row the subreport is displayed succesfully. If I insert a group to the table and place the subreport in the groupheader the same error occurs.
 
Is it impossible to use a subreport in a groupheader? If not, does anyone have an idea how to sove this issue?
 
Thanks!
Dirk

View Replies !   View Related
Negative Zero
Query: Select -1*0.00
Result: -0.00

Any comments?

Maja

View Replies !   View Related
Number Of Coloums In Table
is there a limit in number of coloumns in a table?

is there a size limit of a row?

View Replies !   View Related
Number Of Fields In A Table?
My users login from the tbl_users table.

I would like to store all information on them in this table but that amounts to quite a lot of fields (about 50) and some of these will store a lot of HTML text in them.

Is it best to split the table up for performance reasons or will it make little difference?

Thanks,
JB

View Replies !   View Related
Number Of Columns In Table
How can I determine the number of columns in a table?

View Replies !   View Related
Number Of Fields In A Table
I'm new to SQL Server 7. Is there a limit to the number of fields that a table can have?

View Replies !   View Related
Number Of Rows In A Table
HI!
1.select count(*) from sales
2.select rows from sysindexes where id=object_id('sales') and indid<2

both queries return number of rows.
can anyone tell me which one is better?

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved