Count Of Conditonal Formatting

Jul 2, 2007

Hi,



I have a report where conditional formatting is applied based on being compared to a fixed value in a second dataset. There are several columns where this is applied, and I would like to be able to count all where the condition is true (ie >= corresponding column in dataset2, background green) and all that are false (ie < dataset2, background red) and use that data in the same report and create a percentage.



Not sure whether the reporting layer is the place to do this, or whether to create a separate table and populate that with 1's and 0's beforehand and then use that data additionally in the report?

Many thanks

View 10 Replies


ADVERTISEMENT

Running Count &&amp; Date Formatting

Dec 28, 2007

Hi All,

I am wondering how to achieve a running count on the rows being displayed in a table list. Not sure how to get it to show 1 for first row, 2 for second row, 3 for third row and so on. Example

No | Name
1 | John
2 | Jane
3 | Jim

I am also wondering on the date format in SSRS. When I try to format the date 10/31/2007 in a DD/MM/YYYY format it does not come out so good. Basically I go to the cell property and choose custom formatting, input the formatting string d/mm/yyyy but it only shows 31/00/2007. Can you guys tell me what I am doing wrong?

Thanks for the help.

Regards,
Fadzli

View 14 Replies View Related

Conditonal WHERE Clause

Aug 28, 2007

Hi,

I have a sproc, called spGetJobs, which is querying a table called Jobs. Jobs are either filled or not filled. If filled, the DateJobFilled field will have a date value. If not filled, that field is null. The sproc takes a parameter to indicate either take all jobs or only unfilled jobs. I tried to solve this with a CASE statement in the WHERE clause, as in the following:

ALTER PROCEDURE dbo.spGetJobs
(
@UnfilledJobs bit, -- if 1, get only unfilled jobs, else all jobs
@StartDate smalldatetime
)
AS
select j.JobID, c.ClientID, j.JobStart, j.JobEnd
from Jobs j
join Clients c on j.ClientID = c.ClientID
where j.JobStart >= @StartDate
and j.Role = 'client'
and (case when @UnfilledJobs = 1 then j.JobFilledDate is not null
else 1 = 1 end)

However, VS complains of a syntax error when I try to save this.

I suppose I could construct the SELECT statement as a string and then execute it, but would rather not have to do that. Any suggestions as how to make a conditional where clause?


Thanks.

View 4 Replies View Related

Conditonal SUM Function, Or Similar Conditional Aggregates

Jul 23, 2005

Are there any conditional aggregate functions, such as SUM()?An example would probably be the best way to describe what I'mtrying to do...I have the following table, named Orders, with the following records:ItemNo qty_ord paid------ ----------- ------T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 0.00T101B 1 0.00T101B 1 0.00T101B 1 0.00Z200L 1 50.00Z200L 2 100.00I want to produce the following result set:ItemNo qty_gross qty_net------ ---------- -------T101B 11 7Z200L 3 3The "qty_gross" column in the result set is the sum oftotal items ordered within the ItemNo grouping.Easy enough. However, I also want a column "qty_net" thatis the sum of qty_ord but ONLY IF the amount in the"paid" column is > 0.I tried using the HAVING clause, but that produces acatch 22 situation. If I say "HAVING paid > 0" thenthe qty_gross column is wrong because it leaves out rowsthat contain records with paid = 0 values. If I leaveout the HAVING clause, then the "qty_net" is wrong.Any ideas?select ItemNo, Sum(qty_ord) as qty_gross, Sum(qty_ord) as qty_netfrom Ordersgroup by qty_ord, paid, ItemNohaving paid > 0 ?????Thanks,Robbie

View 1 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....

Select Distinct
sum(isnull(cast([Total Count] as float),0))

from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'

View 9 Replies View Related

In SQL 2000 Can I Use Count() To Count A Column?

Nov 26, 2007

I use SQL 2000
I have a Column named Bool , the value in this Column is  0ã€?0ã€?1ã€?1ã€?1
I no I can use Count() to count this column ,the result would be "5"
but what I need is  "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is  2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.  
thank you very much
 

View 5 Replies View Related

Table Row Count + Index Row Count

Jul 23, 2005

SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.

View 5 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:


--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'


select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc


Thanks. Dan

View 1 Replies View Related

Inserted Rows Count From SSIS Not Like Table Rows Count

Jun 25, 2007

Hi all



i using lookup error output to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................



any ideas

View 6 Replies View Related

Formatting

Aug 18, 2005

Hello all,I have a strange problem that i need some advice on. I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly:SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCIt returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051.SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCDoes anyone have an idea how to solve this? Thanks.Richard M.

View 1 Replies View Related

SQL Formatting.

Feb 28, 2001

I'm working as a consultant for a project and I have been having constant issues with the in house "database guru" about stored procedure formating.

He insists I use a format similar to this:

create procedure getUser @UserId int = NULL
as
select
UserName
, UserPassword
, UserRole
from
Users
join
UserRole
on
User.ID = UserRole.ID
where
User.ID = @UserID
and
User.Active = 1

where as I prefer this format:

create procedure getUser
(
@UserId int = NULL
)
as
select UserName,
UserPassword,
UserRole
from Users
join UserRole on User.ID = UserRole.ID
where User.ID = @UserID
and User.Active = 1

Now normally I don't argue about style issues but I find his format confusing and it bothers me that it senselesly wastes lines make the stored procedure longer than necessary.

I'd love to get some comments on this issue or reference to any discussions on issues such as this.

Thanks

View 2 Replies View Related

SQL Formatting

Apr 9, 2008

Hello -
Does anyone know why SQL Server Reporting Services formatts SQL weird? -- If I type a SQL statement in notepad or notepad++ and then paste it in Reporting Services it will change the whole structure and "expand" it out to three or four times its size.
Thanks
Adam

View 3 Replies View Related

Formatting In Sql Query

Dec 12, 2006

Hello All,
Is it possible to format an integer to a float value with 2 decimals in an sql query.
If yes, Please help?

View 2 Replies View Related

SQL Formatting A SUM As Currency

Jan 24, 2007

I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible?
SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
 I tried:
SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
But this does not format it as currency. Any input would be helpful.

View 6 Replies View Related

Formatting Dates

Apr 25, 2007

I imported data from a text file, where dates are currently in the format:   050729 to represent July 29, 2005.
 How could I write a sql statment to put make this show up as 07/29/05?
 
Thanks

View 5 Replies View Related

Text Formatting In SQL

Jan 31, 2008

I am trying to output data from SQL Server to an email body message with a little formatting (just to make it legible)
Using this SQL statement:
select top 1 'Name', app_first_name+' '+app_last_name [name] ,'Submission Date', submission_date ,'Address', app_address+', '+app_city+', '+app_state+'  '+app_zip from vw_bpa_export where app_email = dave@email.com order by submission_date desc
the resulting output displays this in the email body
"Name","Brian Vogler","Submission Date","2008-01-30 07:45:00","Address","6285 Harrow Trace, Norcross, GA  30092     "
but what I want is the text to look more like this:
Name                        Brian VoglerSubmission Date        2008-01-30 07:45:00Address                     6285 Harrow Trace, Norcross, GA  30092
 Is there a way to do this in the SQL  statement  (without HTML tags)?
 

View 3 Replies View Related

Formatting A Filter

Jun 9, 2008

In a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"'
 Isn't this a standard VB function and why does it produce an error. How do I fix?
Thanks

View 2 Replies View Related

Formatting Dates

Dec 13, 2000

Hi,

In MS Access I can do the following:
Format([SW_Dttm],"Short Date")

What is the equivalent in SQL Server?

Thanks,

Bill

View 1 Replies View Related

Formatting Values

Jun 19, 2000

I have values in my database that I'd like to display as $xxx,xxx.xx. Some of these values use the money datatype and others use float.

I'm building a string in a stored procedure that is eventually passed to Visual Basic. I want to format these values in the above format in the string that is passed. I've searched all through MSN and I couldn't find anything related to VB's FORMAT function in SQL Server.

Does Transact-SQL have any functions that could format these values for me?

View 1 Replies View Related

Data Formatting

Mar 13, 2001

Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as:
10001
10003
10009
54321
89043
etc
Let's say I have a couple of hundreds of them. I need to be able to see them as -

'10001','10003','10009','54321','89043' etc

Appreciate very much your time.

Ivan

View 3 Replies View Related

SQL 6.5 Query Formatting Help

Feb 1, 1999

I am trying to run a query for the company that I work for and I am getting the right data but more than needed. the query is like this

select distinct(companyname),max(calldate) from tablename

what I want to see is the latest call made to a customer for each company
but what I get back is the company listed several times with the dates of each call. I only want the latest. Any help?

View 1 Replies View Related

Formatting Data Help!!

Apr 12, 1999

Hello Everyone,

I have been requested to take a number of columns and concatenate them into one column with a specifc format/layout. For example I need to take this, (noting all columns are of different datatypes:

col_1 col_2 col_3 col_4 col_5 col_6 col_7
PK #1 John Doe 123 Elm Street Anywhere NM 55660
#2
#3
etc..

...and create this on a different table (noting col_2 is of datatype "text":
col_1 col_2
PK #1 John Doe
123 Elm Street
Anywhere NM 55660
#2
#3
etc..

Can this type of formatting be done?
I know how to concatenate data and use the convert function, but how do you add in CR-LF to get the proper formatting??

Any help would be greatly appreciated.
Troy

View 1 Replies View Related

Xp_sendmail - Formatting

Jun 12, 2001

Requirement:
On Insert, Trigger xp_sendmail to complete pre-formatted email with specific content and send to given email address.

Question:
What tools/methods are available to develop a template that SQL Mail's (SQL Server) xp_sendmail function can utilise without resorting to attachments.

e.g. - how do Amazon.com format and send their automated shipping confirmaion emails ??

View 1 Replies View Related

Date Formatting

Oct 10, 2001

I'm currently getting a date in this format 2001-10-08 10:35:45
(yyyy-mm-dd hh:mm:ss) how would I convert the date to this format
2001-10-08/10:35:45 (yyyy-mm-dd/hh:mm:ss)? The only thing added was the / between the date and time.

Thanks in advance,

Ed

View 2 Replies View Related

Date Formatting

Aug 6, 2004

Hi,

I need to know how i can format the date so that the query results will come out as the format mm/dd/yy hh:mm:ss?

Thanks alot.

View 1 Replies View Related

Getting Formatting Into Data

Sep 8, 2006

I am new to php/MySQL and am working on an online manual. The data is divided into Title and Content. The title is easy as it is one line but the content is meant to be in a number of paragraphs.

The Content field is currently set as "blob" and the text comes out as just one paragraph. Wha can I do to get paragraphs etc into the text?

Thanks.

View 1 Replies View Related

Formatting Output

Oct 7, 2004

Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----------
SET NOCOUNT ON
DECLARE @errorCount INT
SELECT @errorCount = COUNT(*) FROM error_report WHERE id != - 2 ANDid != - 5
IF @errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
-------------------------
The Results in the bottom pane looks like this below
------------------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
-------------------
But, when I do a select, they are like this below
----

NODETABLE0There are 2 duplicate subclass name: Specification
Propertytable0at Parentid = 2000000859 and propertyid = 721
Propertytable0at Parentid = 10122 and propertyid = 9

View 5 Replies View Related

Formatting Numbers

Jan 18, 2005

I am using a MS Access ADP connected to SQL Server Data

In a view I have the following formula:

dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

this calculates cost + markup

I cannot get it to format in Currency

Ex:

Cost is $1.75
Markup is 2.00 (2%)
Total shows - 1.785000

I want $1.78

Also - I am using this formula to calculate the Quoted price for the Qty Entered

dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

Using a Qty of 2 for above, I get 3.535000

I want $3.53


Any help is appreciated - AB

View 2 Replies View Related

BCP -- Formatting Output

Jun 13, 2007

Hi,

I wrote the below code and procedure that exports two tables contents into 2 separate Excel files. Is there a way to export contents of two tables via BCP utility into 1 Excel file but 2 different Worksheets of this file?

DECLARE @FileName varchar(50),
@FileName1 varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'E:GPPD_db_stats.XLS'
SET @FileName1 = 'E:GPPD_file_stats.XLS'
print @FileName
SET @bcpCommand = 'bcp "master.dbo.spdbdesc" OUT ' + @FileName + ' -Samex-srv-gppdb -T -c'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand

SET @bcpCommand = 'bcp "master.dbo.spfiledesc" OUT ' + @FileName1 + ' -Samex-srv-gppdb -T -c'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
exec master.dbo.xp_stopmail
set @bcpCommand = ' ' + @FileName + '; ' + @FileName1 + ''
DECLARE @body VARCHAR(1024)
SET @body = 'Please find enclosed files with the database status reports as of '+
CONVERT(VARCHAR, GETDATE()) + '. Please DO NOT respond to this email or the ones coming in the future ' +
'with data files as this email address is not monitored for incoming emails. However, if you have any ' +
'questions/concerns please contact ....'


EXEC master..xp_sendmail
@recipients='alla.levit@amex.com',
@message = @body,
@subject = 'Database Weekly Statistics Report',
@attachments = @bcpCommand

Thanks in advance!
-Alla

View 1 Replies View Related

Formatting Dates

Jan 20, 2004

Hi, I was wondering if there is a way to output a special format for dates in SELECT statements. Currently, my date field is returning my date in this format:
2004-01-19 00:00:00.000
but I want it to be like this:
1/19/04 or 1/19/2004
Is there a way to do this?
Thanks.

View 9 Replies View Related

Formatting Question

Mar 13, 2004

I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:

CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))

Piece of cake....however I am struggling with this in MSSQL.

Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).

I have gotten this far....

select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
+ '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
From tPrices

I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)

I looked through a lot of the T-SQL docs but have come up dry.

Anyway HELP!!!!!!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved