Analysis :: Counting Days From A Subcube

Jul 1, 2015

The logic of MDX eludes me oftentimes... Why the subcube query below will not return the expected result.  I'm using the SSAS browser to test a calculated member, and found the query it is generating uses several layers of sub-queries. I've managed to replicate the issue in a simpler query from 2008 Adventure works:

with member x as
   count(existing ([Date].[Date].[Date]))
select non empty { x, [Measures].[Order Count] } on 0
from (select ([Date].[Date].&[20080601] : [Date].[Date].&[20080602]) on columns from [Adventure Works] )

Order Count returns the right number, 287 for the 2 days that are in the subcube.  x however, returns 2191 instead of 2.

I've tried a lot of variations using filter and descendants, have also tried different levels of the dimension such as [Date].[Date].  I get some different numbers, but never 2.

If I include the Date hierarchy on axis 1 (rows), I get the right answer of 1 day for each row, and there are only 2 rows.  But if I use any other dimension on rows, it repeats the value 287 for order count, and whatever strange number there is for x.

How do I get this count of days to return 2 ?

View 4 Replies


Hosting DBO Nightmare Going Live In 3 Days And Counting

Jan 25, 2005

My hosting company will give me a user with db_ddlAdmin how do I change my current database which has all the tables,store procedures and views owned by dbo.

I've used a few sql scripts to change the owner using

Exec sp_changeobjectowner 'object', 'user'

and I've used

Grant Execute on object to user

These have changed ownership but the site still doesn't work

Does anyone know what to do to achieve this.

Any help greatly appreciated

Many Thanks


View 2 Replies View Related

Analysis :: Count Working Days MDX

Nov 23, 2015

 I would like to count the working days of a spesific time range and then find the Average Daily Dispatches.Currently the time range is at WHERE statement. how to count the dates in a month range. I prefer not to add a new calc Member.

WITH MEMBER [Measures].[Working Days] AS
COUNT(Date.[Working Date].&[1])--Doesn't work
MEMBER [Measures].[Average Daily Dispatches] AS
[Measures].[Total Dispatches]/[Measures].[Working Days]
SELECT [Measures].[Average Daily Dispatches] ON 0
FROM [cube]

View 4 Replies View Related

Analysis :: How To Get Cumulative Count Of Business Days

Jul 9, 2015

I am trying to get a cumulative count of business days. I have a column in my date dimension BusinessDayInd which is 'Y' or 'N'. I have been trying to create a calculated column in the cube but have not been successful yet. I think I'm close with this:

   COUNTROWS(FILTER(DATESMTD(PostingDate[Posting Date]), PostingDate[BusinessDayInd] = "Y"))

However, this is giving me a count of calendar days for all business days and null for non-business days. How do I apply the filter to DATESMTD? I've tried many iterations of this using CALCULATETABLE, CALCULATE, and FILTER. All are giving me the same result.

What am I doing wrong?

View 7 Replies View Related

Analysis :: Range Function - How To Get Last 60 Days Before A Month Starts

Apr 20, 2015

I am trying to get the last 60 days before a month starts. I have a set that is returned from the query below :

   non empty
   [Measures].[TRANSACTIONS Count] on 0,
   non empty ([TRANSACTIONS].[Days].[Days],                                                          
                                     [TRANSACTIONS].[Transaction Month].[Transaction Month])  on 1 from [cube]   

I can get the cummulative count of last 60 days before month 2 by hardcoding the day of transaction of start of month like below :

 [Measures].[Cumm Account Count]
    AGGREGATE( [TRANSACTIONS].[Days].CurrentMember:NULL ,[Measures].[TRANSACTIONS Count])

   non empty [Measures].[Cumm Account Count] on 0,
   non empty [TRANSACTIONS].[Days].&[3]:[TRANSACTIONS].[Days].&[3].lead(60) on 1 from  [cube];
and for subsequent months by using the dates that the following month starts. How can I achieve the above result without having to use the day numbers, I tried to use the tail function (to get the months and star date) but it wont work because the range function  accepts members only...

View 4 Replies View Related

Analysis :: To Include Only Working Days In SSAS Calculations

Jul 28, 2015

I am trying to compute average of Sales amount for 10 days and I am having an issue when using LAG function. Since weekends fall in between working days my LAG (10) function is also including weekends and hence I am not able to get the correct average.

For ex: D1 - 10 , D2 - 20, D3 - 30,  D4 - 50, D5 - 10, D6 - W, D7 - W, D8-10, D9 - 20, D10 -30, D11 - 10, D12 - 40, D13 - W, D14 - W

I am using a sample script

SUM([Time].[Hierarchy].CurrentMEMBER : Time.[Hierarchy].Currentmember.Lag(9), [Measures].[Sales Amount])/10

When I use this and When my current member is D12 Then I am getting the value as 200/10 = 20 (from D12 to D3).

However ideally it should be 230/10  =23 (from D12 to D1) and lag should exclude D6 and D7 as it is a weekend.

I also have a working day flag in my time dimensions which says 0 for non working days and 1 for working days.

View 2 Replies View Related

Analysis :: How To Restrict Cube Users To See Only Last 2 Days Of Data

Nov 9, 2015

in cube we have one role defined. users in that cube should be allowed to see only last 2 days of data. I mean they should be able to browse only last 2 days of data based on current date. How to implement?

View 5 Replies View Related

Analysis :: SSAS 2012 - MDX To Find Last 7 Days Data In Where Clause?

Jul 6, 2015

I have a MDX query , where I have a date Range in where clause.

I want to replace it with Cuurent Date and Last 7 days date.

I tried multiple ways using NOW function , but could not get it correct .

modifying the Query so that I can fetch DATA for last 7 days 

FROM ( SELECT ( [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[3].&[7].&[20150706] : [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[2].&[6].&[20150629] ) ON COLUMNS

I want to replace Date Hard code value , I have used Calendar Hierarchy of date dimension. to find Last 7 days Data.

View 2 Replies View Related

Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates

Jan 7, 2014

I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:

ce.enquiry_time represents when the enquiry was logged

(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))

It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.

View 1 Replies View Related

SQL Server - Select Records Added Today, Last 3 Days, 7 Days...

Oct 25, 2006

Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly).   -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out?  Thanks so much in advance.

View 1 Replies View Related

Analysis :: Power BI Analysis Services Connector - Remote Server Returned Error

Mar 5, 2015

I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.

We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.

View 10 Replies View Related

Analysis :: Cube Needs To Be Deployed From VS After SSIS Analysis Services Processing Task Completes?

May 13, 2014

I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS.  In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task.  The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube.  This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.

I would expect that once development is done, and it is processed and deployed, that is it.  My thinking is that the SSIS task should just update the already deployed cube,

View 2 Replies View Related

Analysis :: How To Right Choose Key Column In Mining Structure For Microsoft Analysis Services

Jun 12, 2015

How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:

"Incoming goods"

Create table Income (         
ID int not null identity(1, 1)            
[Date] datetime not null,             
GoodID int not null,               
PriceDeliver decimal(18, 2) not null,               
PriceSalse decimal(18, 2) not null,               
CONSTRAINT FK_IncomeGood foreign key (GoodID)  references dbo.Goods ( ID )            

I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?

View 2 Replies View Related

Analysis :: Create Analysis Services Project In Visual Studio 2012 Data Tools?

Feb 18, 2013

It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?

View 5 Replies View Related

Analysis :: Excel 2013 Pivot-table / Grouping On Date That Comes From Analysis Service 2008?

Nov 24, 2015

I am using

SSAS: version 2008
Excel: version 2013

I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:

    - Type: System.Integer
    - Value: yyyyMMdd
    - Type: System.DateTime
    - Type: System.String
    - Value: dd/MM/yyyy (note: I am not using US culture)
    - Example: 01/11/2015  
    - Type: System.String
    - Value: %d/%M/yyyy (note: I am not using US culture) 
    - Example: 1/11/2015  

Filtering on date is working fine:

Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting  DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:

I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:

But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.

For date values of my data coming from cube, excel consider them as 'General'

    2.1. But value here is same as it was in simple test.

    2.2. 'Date Filter' works just fine.

    2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell. 

    2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.

    2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.

So, I don't know, what is the puzzle piece that I am missing.

1. Date filter works, group does not work

2. Excel consider it as 'General' string.

3. SSAS does not allow to change 'NameColumn' to Date.

View 2 Replies View Related

Analysis :: Running Analysis Service In Terms Of Port Usage?

May 30, 2015

I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.

View 2 Replies View Related

Analysis :: Creating Roles In Analysis Service Without AD Account

Aug 6, 2015

I want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.

View 3 Replies View Related

IDC And Counting

Jun 26, 1998

When I have made a query with IDC, how can I make a counter in the .htx-file, which tells me if there was no query-results ?

View 2 Replies View Related

A Q About Counting

Jan 28, 2007

Hi,New to SQL. Got some questions about it.Suppose I have two tables. Each of them has a single column, named asc1. For table T1, I have:11133579For table T2, I have:1234513The exercise I want to do is to select the number of occurence in T1for those elements in T2. For above tables, I want to show:1 3 ( i.e. "1" is in T2 and shows 3 times in T1)2 0 (i.e. "2" is in T2 but doesn't show in T1)3 2 (i.e. "3" is in T2 and show 2 times in T1)It seems I can't figure out a good way to do this. Any help will beappreciated.Thanks

View 2 Replies View Related

Counting In SQL

Oct 24, 2007

I am working on writing a query to use with Reporting Services where I am pulling grades from a school grade book trying to see what students qualify for Honor Roll.
I have one Honor Roll done but am still working on the other. The qualifications I am trying to get is that a student has a gpa >3.0 and can have one C grade. I've got the 3.0 and the C's down but how to get the query to pull anyone having at the most one C. It doesn't matter whether it is C+, C, or C- but the student can't have more than a count of one.

Here is what I have

SELECT DISTINCT s.lastname+','+' '+s.firstname AS Student, s.studentnumber,e.grade, AS Term,

gs.score, AS Course, se.teacherdisplay

FROM GradingScore gs

INNER JOIN student s ON s.personid = gs.personid

INNER JOIN v_TermGpa tg ON tg.personid = s.personid

INNER JOIN enrollment e ON e.enrollmentid = s.enrollmentid AND e.calendarid = gs.calendarid

INNER JOIN scheduleStructure ss ON ss.calendarid = gs.calendarid

INNER JOIN Termschedule ts ON ts.structureid = ss.structureid

INNER JOIN Term t ON t.termscheduleid = ts.termscheduleid

INNER JOIN section se ON se.sectionid = gs.sectionid

INNER JOIN course c ON c.courseid = se.courseid

WHERE gs.calendarID = 20 AND ='1st 6wk' AND tg.term1gpa >=3.0 AND s.enddate IS NULL

AND gs.score <='C-'

AND (select gs1.personid, gs1.score

from gradingscore gs1

inner join enrollment e1 on e1.personid = gs1.personid

Inner join schedulestructure ss1 on ss1.calendarid = e1.calendarid

Inner join termschedule ts1 on ts1.structureid = ss1.structureid

Inner join term t1 on t1.termscheduleid = ts1.termscheduleid

where gs1.score IN('C','C+','C-') and (count(score)= 0 or count(score) < 2))

ORDER BY s.[student]

I can get it to work all the way up to where the And (count(score) starts then there is something about the way the count is in there that I haven't figured out yet.

Thanks in Advance for any help or advice

View 9 Replies View Related

Counting The Sum Of Each Row?

Jan 26, 2008


How do I count the total number of each row in a table? The table Company has a field called Group. How do I count the total amount of members in each group?

Thanks in advance!

View 23 Replies View Related

Auto Counting In SQL

Oct 5, 2007

Hi all,
I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it.
So if I for example select all Dates older than today's date, I would want something like this:





Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually".
Is this possible? And if yes, how ? :)
Thanks in advance

View 6 Replies View Related

I'm Stuck With COUNTING

Jan 31, 2008

Sorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection") My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str) str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing

View 2 Replies View Related

Counting Problem

Mar 15, 2004

Hi everyone,

another problem:

I'm trying to count the number of rows but it's not working. Here's my code:

SELECT 'TOTAL number of rows', count(*) --This counts 4! The total number of rows in [Activites]
FROM [Activities]
WHERE [Person ID] IN
SELECT DISTINCT [Person ID] --This brings back 2 rows (two specific people)
FROM [Activites]

As my comments say, I'm wanting to count the two rows but it's counting every row. Obviously I'm doing something wrong but I can't work it out.

Any help?

View 3 Replies View Related

Counting Items

Jun 22, 2004


I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?


View 6 Replies View Related

Counting Rows

Oct 19, 2005

Is there a way to get a SQL Stored Procedure to count the number of rows returned and then store that total in a variable?

View 1 Replies View Related

Aggregate Counting

Aug 17, 2006


How can I aggregate a top 5 count across two satellite tables?

e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.

Any help or pointers would be a great help!


View 3 Replies View Related

Counting Rows

Aug 29, 2006

hi all

quick question

is there any way to set up a column that has the row count in it? i need this for a program i am developing and this would make it much easier to deal with. I know i can get a total count but when i run a count within a select statement i just get '1' for every row. thanks


View 1 Replies View Related

Counting Problem...

Nov 29, 2006

Hi. I have a small problem that i just can't seem to figure out. I'm trying to generate a report for a case management system. The problem I am having is trying to exclude some of these results. Here is my query:

SELECT COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey=DefendantEventPros.VBKey
WHERE DefendantCase.StatusID=17 AND DefendantCase.ProsAtty=55
AND DefendantEventPros.EventDate BETWEEN DATEADD(DAY,-60,GETDATE()) AND GETDATE() AND DefendantEventPros.EventID=9

This query is trying to find the total amount of cases where the statusid=17, the prosatty=55, the date is between today and 60 days ago, and there is an eventid=9.

now, i'm not getting errors in the query itself; it's just that it's inflating the total number. If a case has more than one eventid=9, it will include that extra in the results. I do not want to include those in the results. Does anyone have any suggestions? Thanks!

View 3 Replies View Related

Counting Rows

Mar 10, 2004


I have a temp table that I use to calculate prices from and I need to know how many of each row with the same serialnumber.

I figured I could add a column that contains that number. But how will I get it there?

UPDATE [_temp] T1

Doesn't work. Any ideas?

View 5 Replies View Related

Counting A Pyramid Db

Mar 27, 2004

hiya all,
I got a table that is represented as a B-Tree, it has a one to
many relation with its self
| UserID |

UserID = FatherID
it looks like this
/// \
ooo ooo
//\ ///\
oo ooo ooo
I want to get all the nodes that has 256 children and grand children and each node is limited to have 6 nodes under it.
any ideas ? the DB isn't filled with data yet so we can add more fields
to the table if it helps.

I could write a trigger that increments a counter in each node but it will be a recursive trigger so it can go from the child to its father and the father to its father upating them and so on.


View 7 Replies View Related

Counting Bit Types

Apr 23, 2008

I have a table that I want to use for reports that holds these datatypes

safe: Integer
unsafe: Integer
made_safe: Bit
unobserved: Bit

Which gets populated.

I want to count the values in each field but Bit fields seem to be awkward. This is my attempt

SUM(safe)as TotalSafe,
SUM(unsafe)as TotalUnsafe,
COUNT(Case WHEN unobserved = 1 THEN 1 ELSE NULL END)as NotSeen,
COUNT(Case when made_safe = 1 THEN 1 ELSE NULL END) as TotalMadeSafe

FROM myTable

Any ideas please

View 3 Replies View Related


Jun 4, 2008

Hi all,
I am trying to count the total amount of times the primary key is used within each month of the year "JAN", "FEB" "MAR"... Unfortunately I have no idea how to A) Search by month and B) Count the total amount of times data within the new table that is created AFTER the outer joins are done. I hope you can help.


tableA ta,
tableB tb,
tableC tc
WHERE tc.updated_date >= TO_DATE ('01/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS')
AND tc.updated_date <= TO_DATE ('31/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS')

AND tc.primaryKey = tb.primaryKey (+)
AND tc.primaryKey = ta.primaryKey (+)
AND count(tc.priarykey) -- This is what I want to count

View 2 Replies View Related

Copyrights 2005-15, All rights reserved