View That Conditionally Counts Rows With Reset
May 22, 2015
I have a table sorted by vendor, then item, then Status date with a QC Pass date and Fail date.
I need a SQL 2008 view that counts how many consecutive times a Vendor/Item has passed QC.
When it fails, the count resets to zero, then begins incrementing again.
I need to know how to generate the last column (Count).
I have tried using a ROW_Number() OVER(Partition BY, Order By...) command in the view, but I cannot seem to make it work right.
VendorItemStatusDatePassDate Faildate Count
10056322010-05-092010-05-091
10056322012-12-152012-12-152
10056322013-05-252013-05-253
10056322014-11-172014-11-174
[Code] ....
View 5 Replies
ADVERTISEMENT
Mar 19, 2007
Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file
I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.
Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?
Any suggestions will ge greately appreciated
IgorB
View 6 Replies
View Related
Jul 26, 2007
I just spent about 30mins searching through the forums for this and saw several posts, but I didn't find a straight answer that seems like it would work for my report. How can I add only 2 data rows to a group (to hide them via the group's visible properties) and keep the rest of the rows outside of the group, but still in the same column (vertical area), as shown:
This is the view of all rows:
Cat1
Cat2
Product A
Data 1
#
#
Data 2
#
#
Data 3
#
#
Data 4
#
#
Data 5
#
#
Data 6
#
#
Product B
Data 1
#
#
Data 2
#
#
Data 3
#
#
Data 4
#
#
Data 5
#
#
Data 6
#
#
And this is the view I'm seeking in some cases:
Cat1
Cat2
Product A
Data 1
#
#
Data 4
#
#
Data 5
#
#
Data 6
#
#
Product B
Data 1
#
#
Data 4
#
#
Data 5
#
#
Data 6
#
#
View 4 Replies
View Related
Aug 26, 2007
I am building a Table Report where I need to "count" the number of cells in a column conditionally.
I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:
iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer
IF Cell.Value = "Orange" THEN
iCountOfOranges = iCountOfOranges + 1
ELSE IF Cell.Value = "Apple" THEN
iCountOfApples = iCountOfApples + 1
ELSE IF Cell.Value = "Banana" THEN
iCountOfBananas = iCountOfBananas + 1
The 3 count values would then be displayed in 3 footer rows at the bottom of the table.
Thanks
View 4 Replies
View Related
Nov 2, 2004
Having one invoice table (ord) and one 'person' table (actor) I would like to include the name of the person who is responsible for an invoice: SELECT Ord.OrdNo, Ord.Selbuy, AC.Nm AS 'Responsible', AC.EmpNoFROM OrdLEFT OUTER JOIN Actor AS AC ON Ord.Selbuy = AC.EmpNoWHERE ord.ordno = 23505 This works perfectly fine if Ord.Selbuy has a corresponding value in Actor:|26914 |21|Yvonne| 21| or if there is no corresponding value in Actor:|26914 |21|NULL| NULL| But what if Ord.Selbuy=0? Then I end up with 3285 rows from Actor! This happens because Actor.EmpNo=0 is allowed. Persons which have never been employed or used to be employed gets Actor.EmpNo=0.Can I create a SELECT statement which only returns data from the INVOICE row if Ord.Selbuy=0?|26914 |21|NULL| NULL| p.s. I'm not able to change table structure/behavior of update procedures, because the tables/code belong to a "bought from the shelf"-business system.
View 2 Replies
View Related
Aug 8, 2012
This should be a simple solution, but it has been a long time since I've done any query writing (mostly in Oracle) and I am stumped, so here goes:
We are in the process of converting Access database to MSSQL with web form front ends.
I have a table, all columns are nullable, and want users to be able to query from a form, which has a field for each column and defaults to a % wild card for the entered value.
I want the users to be able to put any string in any field, and have it return each row that matches that, including rows with null values in the other columns, but not the column with the entered criteria.
Here is a sample of the data:
Code:
SQL> select * from test;
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too
7 rows selected.
Now, if I have this SQL run, it will return only rows that have no nulls in any columns:
Code:
select
col1,
col2,
col3,
col4from test
where
col1 like'th%'
and col2 like '%'
and col3 like '%'
and col4 like '%';
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is test too
Now, if I use an OR clause for each column, this mostly works, but the trouble is it will also return rows with null values for the field that has criteria entered in it:
Code:
select
col1,
col2,
col3,
col4from test
where
(col1 like'th%' OR col1 is null)
and (col2 like '%' OR col2 is null)
and (col3 like '%' OR col3 is null)
and (col4 like '%' OR col4 is null);
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too
The idea is to only select the first 4 rows in the above example.
I was playing with ISNULL in the select clause, but all it does is substitute a string for a null, and I think CASE will do the same thing.
Is there a way I can write this query so it will return rows with NULL values in any column, except the one(column) that has user entered criteria in it?
View 9 Replies
View Related
Nov 5, 2014
I need to create a view that counts in one of the columns.
The tricky part for me is that it should only count to three, then start on 1 again and count to three again.
Example
1 - car
2 - bike
3 - Motorbike
1 - Boat
2 - Airplane
3 - Motorboat
1 - Bicycle
View 4 Replies
View Related
May 26, 2015
Script for finding row counts of two specific tables in each database of SQL in PIVOT format . e.g
Database || TableName1| Tablename2|| RowsCounts
View 2 Replies
View Related
Oct 26, 2007
Hi,
I have recently had to optimise a site. The DMVs are useful in establihing which queries are taking the longest to run but the data is skewed as it takes into account all queries than have run ever. This is not as useful as profiling a site which in fact I used.
Is there a way to clear all the data returned by the DMVs. This would be useful as all the data could be cleared and then I could just check the DMV every week.
Thanks in advance
James
View 1 Replies
View Related
Jul 12, 2012
I'm trying to create a trigger that will clear the "checked" field when all items with the same classification have been checked.
The purpose of this is to do cycle counting of inventory, which is when you count the stock levels of a proportion of stock that have the same classification.
The checked field will be used to record the date it was lasted checked and will cleared once all stock have counted, putting back into the pool of stock to be counted.
Therefore, the trigger is suppose to count the number of rows that share the same classification as the amended row, and compare that with the number of rows in that classification, that have a date in the checked date field. When the two values are the same, reset all the checked dates to blank.
But when I tested it, nothing happened ...
ALTER TRIGGER [dbo].[UDEF_Last_Checked_Reset] ON [dbo].[ASC_PMA_TBL]
AFTER INSERT
AS
BEGIN
DECLARE @PartOnly varchar(16) -- Part Number
DECLARE @Rev varchar(4) -- Part Revision
DECLARE @PartCode as varchar(2) -- Part Classification
DECLARE @Last_Checked as datetime -- Checked Date
[Code]...
View 1 Replies
View Related
Feb 15, 2015
How can I reset the number of Row_Number() if I exceed the number of rows specified.
Example:
Code:
Col1 RowNum
----------------------
ID1 1
ID1 2
ID1 3
ID1 4
ID1 5
ID1 1
ID1 2
ID1 3
ID1 4
In this example, the Row_Number will reset for every 5 items.
View 1 Replies
View Related
Jul 21, 2004
Hi All,
I can do this in Access, with VB, but I'm pretty new to SQL Server.
Say you have the following table, call it TblStudents:
Grade Name
8 John
8 Mike
8 Ed
9 Tom
9 Greg
10 Jack
10 Tony
And you wanted a view that would give you:
Grade Name
8 John, Mike, Ed
9 Tom, Greg
10 Jack, Tony
How would you do this in SQL Server?
Thanks.
Henry
View 1 Replies
View Related
Apr 29, 2004
Hi Im having trouble with this it seems simple enough but its not!
I have a source Table called Access_table example
Name Role1 Role2 Role3 Role4 Role5
a 1 0 0 0 0
a 0 0 1 0 0
b 1 0 0 0 0
c 0 1 0 0 0
d 0 0 0 0 1
e 0 0 1 0 0
e 0 1 0 0 0
f 1 0 0 0 0
g 0 0 1 0 0
I need to create a view that basically finds all the names with double Roles and merge the results into 1 row example.
Name Role1 Role2 Role3 Role4 Role5
a 1 0 1 0 0
e 0 1 1 0 0
I cannot change the information in the source table and the results need to be in a view as the roles will change. Every time I try and do this I duplicate the row again. Can anybody suggest a solution.
Thanks in advance.
View 2 Replies
View Related
Dec 11, 2007
Hi, i have a Stored Procedure that is calling a view. I need to pass on all the records of this view so i made another view that returns the rows count and then a for loop from 0 to count-1. But then how can i access the rows and columns of the view one by one? Any idea?
And if someone has a better logic that gives the same result please let me know. Thanks...
View 10 Replies
View Related
Jul 15, 2007
I have two tables that have a column called "subscriberid". I imported rows into the main table, and it's sister table.
There are 90,000 rows in the main table and 94,021 in the other table.
It appears that roughly 4,000 rows have disappeared in the main table, but not in the other table, and I don't understand why?
I'm new to SQL Server 2005 Express and I'd appreciate any help you can give me.
Thanks,
Bill
View 4 Replies
View Related
Mar 11, 2008
Hi, my name's John and this is my first post here at SQL team. I've learnt a lot from the forums here, never needed to post though. I'm pretty good at sql, but not that good and I think for the first time in couple of years I now have a query that I'm completely unable to create.
Let me explain the setup.
I've got three tables e.g.
Products (productid, Name)
100 Computer
200 Printer
300 PDA
ProductActions (ID, Name)
6 changed casing
8 Changed motherboard
7 replaced cd-drive
ProductPerformedActions (productid, actionid)
100,7
100,9
200,8
This basically shows a simple setup showing that product (computer) has been done the action "repaired" and product (printer) has been done the action "changed motherboard". The query i'd like to have is where I can select all products that have not been performed on cerrtain actions. e.g. a list of products that have not been performed on all actions, such as
UnPerformedActionsList(product,actionid)
PDA,6
PDA,7
PDA,8
Printer,7
Printer,6
Computer,8
I know its foundation is something like this:
select productid from products where productid not in
(select productid from productperformedactions....
which elimiantes products that have been performed an action on, but it also removes it for ALL actions,
which i'm trying to avoid. All i want is kind of a cross join, listing all actions, then inverting it, to show
all products that have NOT had it performed on them.
I hope this makes sense.
Thanks
John
View 5 Replies
View Related
Jul 30, 2000
Help, is something wrong with my SL Server? I am unable to return any rows from all tables in all databases (user and system)on My SQL 7.0 SP2 machine. Whne i right click on the table in E.M and select design or open table i get no results. Does anyone know why this is happening? It did not always happen either.
Thanks
View 1 Replies
View Related
May 24, 2007
I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for
I have a table is MSSQL 2000 that looks as follows:
Code:
id custnum b1_email b2_email b1_sub b2_sub
------------------------------------------------------------------------
1 123456 b1@host1.com b2@host1.com 0 0
2 654321 b1@host2.com b2@host2.com 1 0
3 321654 b1@host3.com b2@host3.com 0 1
Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows:
Code:
custnum email sub
----------------------------------
123456 b1@host1.com 0
123456 b2@host1.com 0
654321 b1@host2.com 1
654321 b2@host2.com 0
321654 b1@host3.com 0
321654 b2@host3.com 1
Any help would be great! I imagine some sort of join command can be constructed using a single table?
View 2 Replies
View Related
Dec 27, 2007
Hello,
Is it possible to insert rows into a view, update rows in a view and delete rows in a view?
If so, how does T-SQL handle it when a VIEW is the result of following query?
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 ON table_name1.col = table_name2.col
WHERE condition
Thanks in advance,
Erke.
View 4 Replies
View Related
Aug 26, 2015
I have one view which is based on couple of tables. Here is the definition of view. Which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.
CREATE VIEW [dbo].[V_Reqs]
WITH SCHEMABINDING
AS
SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime,
purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne,
purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,
[code]....
View 3 Replies
View Related
Jul 19, 2007
Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300
View 1 Replies
View Related
Apr 28, 2014
I have a table like below:
ItemIdAmountTax1Tax2SrvType
111 100 10 20 1
112 200 10 2
113 300 10 30 3
Now I want to create View that will have an exploded resultset based on SrvType.
For SrvType 1 and 2 there will be 2 lines per Itemid - One for 'Amount' anod another for 'Tax1+Tax2'. But for SrvType 3 there will be 3 lines per 'ItemId' - one for 'Amount', one for Tax1 and another for 'Tax2'.
I have a few hundred source records like this. Now sure how to achieve the exploded resultset with a View.
View 4 Replies
View Related
Jan 19, 2015
I have a simple query that joins a largeish fact table (3 million rows) to a view that returns 120 rows. The SKEY in the view is returned via a scalar function. The view returns instantly if queried on it's own however when joined to the fact table in the simple query below results in a query execution plan that runs forever. Interestingly if I change the INNER JOIN to a LEFT OUTER JOIN the query returns the matched results almost instantly.
Select
Dimension.Age_Band.[10_Year_Age_Band],
Count(*)
From
Fact.APC_Episodes
Inner Join Dimension.Age_Band ON
Fact.APC_Episodes.AGE_BAND_SKEY = Age_Band.AGE_BAND_SKEY
Group By
Dimension.Age_Band.[10_Year_Age_Band]
I know joining to a view using a column generated by a scalar function is not a good recipe for performance. I also know that I could fix this by populating a physical table with the view first as I have already tested this though I hoping not to have to go down that route.
Why a LEFT OUTER JOIN works and not an INNER JOIN or anyway I can get the query optimizer to generate an execution plan that works?
View 9 Replies
View Related
Jun 8, 2015
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList]
as
select m.id as Max4SaleId,
mt.[Description] as [TypeDescription],
CAST(m.[type] as tinyint) as [Type],
m.start_time as [StartTime],
m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
View 4 Replies
View Related
Jul 20, 2005
This is probably a stupid question, but here goes: I am trying toupdate a table differently based on what is in one of the columnswithin the table. For example, I want to set Fld1 to be "On" if Fld2is "A", and I want to set Fld1 to be "Off" if Fld2 is "B". This iseasy to do in two SQL statements, but I was wondering if there's someway to do it in a single SQL statement. Thus far, I have had no luckin figuring out how to do this. Any ideas would be appreciated!!
View 1 Replies
View Related
Mar 7, 2008
I need some help aggregating values in a single table, where neither a simple Sum() nor a simple First() function will do... Would like to do Sum(First()) but that's not allowed!
Sample dataset (select * from cs_view):
Gender | Program | Student | Class_Section | Heads | Credits
------ | ------- | ------- | ------------- | ----- | -------
Female | English | Elena | Phys 101-b | 1 | 4
Female | English | Elena | Hist 101-c | 1 | 4
Female | English | Elena | Engl 101-a | 1 | 4
Female | English | Elena | Engl 105-b | 1 | 4
Male | History | Rich | Phys 105-a | 1 | 4
Male | History | Rich | Engl 101-c | 1 | 4
Male | History | Rich | Hist 101-b | 1 | 4
Male | History | Jacob | Phys 101-a | 1 | 4
Male | History | Jacob | Hist 101-b | 1 | 4
Male | History | Jacob | Engl 101-c | 1 | 4
Male | History | Jacob | Phys L-101-a | 1 | 0
Dataset has one row per student enrollment in class section. No trouble summing credits by student or by program (or gender). HOWEVER, aggregate head-count should add each student only once.
Desired table:
Gender Program Heads Credits
------ ------- ----- -------
Female English 1 16
Male History 2 24 - --- 3 40If I add a third grouping level, that is, add a student-level grouping to the desired table, First(Fields!Heads.Value) will return the correct student-level head count; however, I don't know how to sum up the student-level group header rows ('subtotal' rows), to aggregate head count by gender or by program.
Thanks for taking the time...
tom
View 1 Replies
View Related
Feb 27, 2007
Hi folks
I think this may be an increadibly simple one, but it's been driving me up the wall. I have a dataset that contains some summary information about a group of companies which i'd like to represent in a report. The dataset looks like this:
Company Lead_Type Count
Company1 Internal 2
Company2 Internal 3
Company3 Internal 5
Company2 External 10
Company1 External 7
Company3 External 3
etc...
I want to show this in a report as follows:
Internal External
Company1 2 7
Company2 3 10
Company3 5 3
I can't figure out how to select a specific field from the dataset (e.g. Company = Company1 and Lead_Type = Internal). This sort of thing must be done all the time, I'm sure I'm missing something fairly obvious. Any posts back gratefully received.
Thanks
Guy.
View 1 Replies
View Related
Oct 31, 2006
I'm pararmeterizing a SQLDataSource, but I've encountered a problem. The users want all records returned if no ID is provided. So when they first hit the page, they will see all records, then they can filter down to one ID if needed. However, if I add a selectparameter of ID, I have to default it to a value, which won't give the users all records. Suggestions?
View 1 Replies
View Related
Sep 7, 2007
Hallo
Is there a way to conditionally execute a task?
I got a task €śMail Send€ś and I would like to execute it, just if the variable X (my message source) has a value.
I try in expressions €śDisable€? = len(@[Benutzer::Msg] ) > 5 ? false : true, but it does not work.
View 3 Replies
View Related
Jan 4, 2007
I'm inside a "dataflow" and
i have a conditional spliter
one of the condition say "ok"
and the the other says "not ok"
when my flow goes to the "not ok" condition
I want to Abort the entire package execution
I want to make it sure that the package is terminated
and could not triggers the next control in the control flow.
i want to kill/ abort the execution of all my SSIS processes
for the entire package within the current dataflow
View 3 Replies
View Related
Oct 30, 2007
Is it possible with SSRS to conditionally display parameters.
Example, we have dimension concepts that we would like to use in reporting on an Either/Or basis
1. Choose Country
then choose either
2. Geography - Country>Region>Market>Submarket
3. Division - Country>Division>Submarket
The users would like a choice of reporting by Geography or Division
So, choosing Geography will then lead to cascading parameters of Region>Market
Choosing Division would lead to a Division paramter only
Is it possible to do this with SSRS?
View 3 Replies
View Related
Oct 9, 2006
Hi,
I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.
View 2 Replies
View Related
Jan 29, 2008
I have a sql server table column that can contain the following possible values. 1. 766/IT 2. 777/HR3. 890/EG4. 012/AS5. Trainee6. Contractor 7. Others I want to write an SQL query grouped by this column, Trainee should be one group, Contractor should be another different group, Others should be another different group and then every thing else ( 766/IT ,777/HR ,890/EG, 012/AS) should be grouped together as one group. Think of it in terms of a pie chart with those groups.
View 4 Replies
View Related
