Group Counter In Condition
Oct 16, 2007
Hi All,
I have a tricky issue and plesae help:
My dataset likes this:
Level people
1 A1
1 A2
1 A1
2 A3
2 A3
2 A4
2 A4
2 A4
I want to create a summary report and get the results like:
Records People
Level 1 3 2
Level 2 5 3
I only two levels: Level 1 and level 2 and don't need consider the lable of level.
How can I implement this in the Table or Metrix?
TIA
Micror
View 1 Replies
ADVERTISEMENT
Jul 18, 2007
How can I create a counter (line#) by group were it resets = 1 whenever a new group starts.
Customer Line#
A 1
A 2
A 3
B 1
C 1
C 2
View 3 Replies
View Related
May 1, 2006
I need to check groups of records for a condition that doesn't exist in any record in the group.
Example:
Accountno code
1 A
1 C
1 M
1 P
1 Z
If the group does not contain a code 'Z', I want to select the accountno of the group. In the above example, the accountno should not be selected. If none of the records had a code 'Z', the accountno should be selected.
I can't simply say 'where code <> 'Z'. When the system looks at the first record in the above example, it sees that A is not equal to Z and the accountno would be selected. But this is wrong. As there is a 'Z' in the group, the accountno should not be selected.
I've tried using 'top 1' and count. Nothing has worked.
Any ideas?
View 5 Replies
View Related
Aug 20, 2007
Hi,
I Have the following "select statement" with "group by condition", I want the outgoing result for the query to be vno with (scamt <> sdamt) the example is as following:-
select vno,vtype,tdate,sum(camt) as scamt,sum(damt) as sdamt
from transact
where year = 2007
group by vno,vtype,tdate
HAVING (scamt <> sdamt)
order by vno
but i have the following error
Invalid column name 'sdamt'.
View 2 Replies
View Related
Jul 20, 2005
Data:PROJ PLAN TOTTIME UNITA P1 10 DAYA P2 10 HOURA P3 1 MONTHWHEN I'M DOING GROUP BY ON PROJAND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTHI WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.10 DAY=10 DAYS10 HOUR=1.25 DAYS1 MONTH=20 DAYSTHE RESULT SHOULD BE LIKE THIS:PROJ PLAN TOTTIME (IN DAYS)-------------------------------A ALL 31.25-------------------------------THANKS IN ADV.T.S.NEGIJoin Bytes!
View 1 Replies
View Related
Jun 14, 2014
tblScore contains score for each problem
id problemID score
------------------------
1 1 10
2 2 30
tblSubmission contains problem submissions for each user
id user problemID accepted
-----------------------------------------------
1 UserA 1 0
2 UserA 1 0
3 UserA 1 1
4 UserA 2 1
5 UserB 1 0
6 UserB 1 1
7 UserB 2 1
For UserA :
- For problemID 1
-> submitted three times
-> rejected for first two submission and accepted on third submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
For UserB :
- For problemID 1
-> submitted two times
-> rejected for first submission and accepted on second submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
Now I would like to process the table and want to get the following result :
user Score
--------------------------
UserA 36 (6 + 30)
UserB 38 (8 + 30)
Explanation :
- For each rejected submission, a -2 point penalty.
- UserA have submitted probelmID 1
- > score of problemID 1 is 10.
- > first two times rejectd
- > third time accepted.
-> score = 10 - 4 = 6
- UserA have submitted problemID 2
- > score of problemID 2 is 30
- > first time accepted. No penalty will be counted
- > score = 30
so final score for UserA = 30 + 6 = 36
Similar for UserB.
View 2 Replies
View Related
Oct 11, 2013
I have one table which contines 3 columns they are
1.emp
2.monthyear
3.amount
in that table i am having 6 rows like below.
emp monthyear amount
1 102013 1000
2 102013 1000
1 112013 1000
1 112013 1000
2 122013 1000
2 122013 0000
i want a total on group by condition on each employee. which will have the data from NOV to dec 2013 only.
out put should be like this
1 2000
2 1000
View 9 Replies
View Related
Jul 16, 2015
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
View 15 Replies
View Related
Oct 1, 2015
I have a query that returns the data about test cases. Each test case can have multiple bugs associated to it. I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
TestCaseID TestCaseDescription BugID BugStatus
1 TestCase1 1 Closed
2 TestCase1 2 Open
3 TestCase2 11 Closed
4 TestCase2 12 Closed
5 TestCase2 13 Closed
How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.
View 3 Replies
View Related
Dec 3, 2013
I have data as below: select ID,code,go,back from tableA
ID code go back
2 C US UK
2 A UK US
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
I only want to pull out whole ID group which code Z exist. I tried as below:
select ID,code,go,back from tableA A
where exists
( select ID,go,back
from tableA B with (nolock)
where A.ID = B.ID
and A.code='Z')
But it only appear:
ID code go back
1 Z UK US
How can I appear with the group as well?
ID code go back
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
View 3 Replies
View Related
Feb 9, 2006
For example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
View 1 Replies
View Related
Apr 19, 2007
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
View 4 Replies
View Related
Jun 22, 2015
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS", otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST", otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"),
IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
View 6 Replies
View Related
Oct 17, 2015
I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :
create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc
[Code] .....
View 3 Replies
View Related
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
Jul 5, 2006
Hi
I'm using a gridview to show a list of high scores in a c#.net page. The position of the score is not stored in the database, just the score, name and id.
In the gridview however I would like to indicate the position of the scores, the SQL statement sorts the records by ascending scores so the are already in the correct order.
Does anybody know how to include a counter in the SQL statement that I can refer to vai the gridview so that it is able to display it as the position?
Thanks in advance for any help!
View 2 Replies
View Related
Jun 21, 2004
SQL Databased Multipage Hit Counter
# 1 - You want a Multipage Hit Counter to keep track of the number of Hits each individual webpage gets in your website.
# 2 - You also want to store the Webpage Hit Count Values into a SQL Database, using Stored Procedures.
Using 2 Webpages as an example:
--------------------------------------------------------------------------------
Inside webpage #1 put insert the following:
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SQLClient" %>
<script runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim objCon As New SQLConnection("server=yourServerName;User id=idName;password=yourPassword;database=HitsCounter")
Dim cmd As SQLCommand = New SQLCommand("EXEC dbo.webcounter1", objCon)
objCon.Open()
Dim r as SQLDataReader
r = cmd.ExecuteReader()
r.read()
strcounter3.text = "Hits : " & r.item(0)
end sub
</script>
Also insert the following inside the body tags of webpage #1
<asp:Label id="strcounter3" font-size="X-Large" font-bold="True" bordercolor="Silver" width="300px" borderstyle="Inset" forecolor="Lime" visible="True" runat="server"></asp:Label>
Marked in Red webcounter1 is the Stored Procedures Name.
ALso marked in<B> Red counter3 is the name of the Database.
This is where you would make your changes inside the Webpages.
--------------------------------------------------------------------------------
Use the following Stored Procedure with Webpage #1
CREATE PROCEDURE dbo.webcounter1
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
DECLARE @hits INT
SELECT Hit FROM counter3
WHERE
ID = 1
update counter3 set hit = hit + 1
EXEC sp_recompile counter3
END
GO
--------------------------------------------------------------------------------
The Database Table has 2 columns which consists of an ---ID & Hit Column -- The Table Name is (counter3)
The best way to explain is by showing so posted below are Webpages 1 & 2 along with there Stored Procedures.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Inside webpage #2 put insert the following:
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SQLClient" %>
<script runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim objCon As New SQLConnection("server=yourServerName;User id=idName;password=yourPassword;database=HitsCounter")
Dim cmd As SQLCommand = New SQLCommand("EXEC dbo.webcounter2", objCon)
objCon.Open()
Dim r as SQLDataReader
r = cmd.ExecuteReader()
r.read()
strcounter3.text = "Hits : " & r.item(0)
end sub
</script>
Also insert the following inside the body tags of webpage #2
<asp:Label id="strcounter3" font-size="X-Large" font-bold="True" bordercolor="Silver" width="300px" borderstyle="Inset" forecolor="Lime" visible="True" runat="server"></asp:Label>
--------------------------------------------------------------------------------
Use the following Stored Procedure with Webpage #2
CREATE PROCEDURE dbo.webcounter2
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
DECLARE @hits INT
SELECT Hit FROM counter3
WHERE
ID = 2
update counter3 set hit = hit + 1
EXEC sp_recompile counter3
END
GO
--------------------------------------------------------------------------------
Now this type of Hit Counter works fine to a point.
Visiting Webpage #1 accumilates 1 Hit & Visiting Webpage #2 Accumilates 2 Hits.
And each time there 's a revisit to these pages they acquire 2 Hits each.
I'm Guessing the problem is in the Stored Procedure storing there values in Memory.
If Someone can Help Figure Out how to fix this. This could be a nice Website Multipage Hit Counter. I can use some help Guys feel free to advise.
View 3 Replies
View Related
Nov 7, 2000
I'm trying to write a select that returns a result set with a counter added on. I want to do it without using an identity column or cursor. Something along the lines of select col1, max(col2)+1 from tab1.
I get (as expected)
col1 col2
------- ------
a 25
b 25
c 25
I want
col1 col2
------- ------
a 25
b 26
c 27
Thanks,
Jim
View 2 Replies
View Related
Oct 23, 2003
Hi Everyone,
The sqlserv.exe takes too much CPU utilization on my SQL 2000 on W2K production machine. I am tring to use System Monitor to monitor the Thread/%process time with all Sqlservr instances, and then match the sqlservr instance number to the KPID in sysprocesses table to find out which user is causing the problem. but I can only see the instance number from Sqlservr0 to sqlservr99. From the table sysprocesses table, the KPID is all 3 or 4 digits number. Any one has any idea about this?
Thanks in Advance.
Jason
View 2 Replies
View Related
Oct 11, 2006
I need to add a ClaimCount column to a table. I am having a bit of trouble with the SQL to make sure it populates correctly.
Current Table
claim#namePaid
123George Washington50
123 George W Washingon50
124Sam Adams100
126George Washington75
128Rich Cheney69
128Dick Cheney69
128Richard CHeney69
Want to add a column (ClaimCount) as follows
Claim#namePaidClaimCount
123George Washington501
123 George W Washingon502
124Sam Adams1001
126George Washington751
128Rich Cheney691
128Dick Cheney692
128Richard CHeney693
Rules for ClaimCount.
1) there is no order by, It doesn't matter which record with the same Claim# is 1 or 2 or 3...
Appreciate the help.
Ray
View 10 Replies
View Related
Nov 16, 2007
I am looking to create a incremental value based on the resulting insert that I am using. There already is another field being used as an identity field. I have a beginning value that I just want to add the row number to for the insert.
insert into lineitem select substring(group_id,4,len(ltrim(rtrim(group_id)))-3) as co_code,
0,0,(case when enddate < cast(month(getdate()) as varchar(10))+cast(day(getdate()) as varchar(10))
then 'Prior' else 'Current' end ),
left(acct_type,2) as bene_type, convert(smalldatetime,left(ltrim(rtrim(eff_date)), 8)),
0,trans_amt,0,0,convert(smalldatetime,left(ltrim(r trim(sett_date)),8)),
ltrim(rtrim(b.fname)) + ' ' + ltrim(rtrim(b.lname)) as payee,0,0,a.ssn,'Y',999+count(*),
(case when isnull(b.location,'') = '' then '' else b.location end) as location
from mbi_tran_temp a
left join enrollees b on a.ssn = b.ssn and
ltrim(rtrim(a.group_id)) = ltrim(rtrim(b.mbicode))
The '999+count(*)' is where I would like to have the incremental value.
View 13 Replies
View Related
Mar 9, 2004
It sound like a simple task to perform but I just can't seem to get it. I've built a search function on the site and after each search request, a log is kept of the search word and the date, so the table looks a little like this:
ID SearchTerm DateSearched
============================
1 home 2004/03/09
2 fred 2004/03/08
3 cup 2004/03/08
4 home 2004/03/08
5 home 2004/03/08
6 fred 2004/03/07
I want to pull out each of the search terms as well as how many times they've been searched on so I could display it in the format like so:
Word Qty
============================
home 3
fred 2
cup 1
Any help would be appreciated. Thanks.
Goran (GoMo)
View 4 Replies
View Related
Aug 18, 2015
My client wants IN_PUNCH to be the first in punch of the day and the OUT_PUNCH to be the last out punch of the day based on the TransactionDate as it is showing in the desired results table for 07/29 and 07/30 (Changes are highlighted in Bold).
I tried using MIN and MAX at different areas but to no avail.Also, is there a way to add a counter at the end of each row so in case if there were two shifts in a day then it be a '1' for row 1 and a '2' for row 2?
WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS,PAYCODE,SHIFT_LABEL,DOW) AS
(
SELECT 1234,'07/27/2015','07/27/2015','07/27/2015', '12:00','12:00','8', 'Hol', 'NULL', 'Monday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '08:00','','4.00', 'Absent', 'Batax 1st','Tuesday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '12:15','14:00','3.75', 'Regular', 'Batax 1st','Tuesday' UNION ALL
[code]...
View 2 Replies
View Related
Dec 13, 2006
Hello,
I need to count the days (24 hours)from the (GETDATE()),returning an integer in a table column, automatically, all the time , under the scene until after a specified number of days (usually 20 for example), the relevant table row is be automatically deleted, replaced by another insert (row) to start again and the process repeated many times.
I have a database which works automatically. The data need not to be queried or manipulated until the row is deleted automatically.
I use SQL Server 2000.
Any ideas of doing this in a simple way ?
Regards and thanks in advance.Yves.
View 12 Replies
View Related
Nov 5, 2007
I'm looking for a query that will look at an Id field and if it occurs more than once then returns the count of the times it occurs. For Example,
ID Code GetSequence
4 239 1
4 241 2
4 3243 3
View 5 Replies
View Related
Nov 9, 2007
Hello everyone:
this procedure resets the business day to one for every month. It works fine except for the month of October. any idea or suggestions?
DECLARE @i INT
SET @i = 1
DECLARE @DateID INT,
@DtTimeD DATETIME,
@LASTDAY DATETIME
DECLARE c CURSOR
FOR
--
-- LASTDAY is the last day of the month
-- the counter will reset to 1 on the first of each month
--
SELECT DateID, DtTimeD, DATEADD(dd, -DAY(DATEADD(m,1,DtTimeD)), DATEADD(m,1,DtTimeD)) 'LASTDAY'
FROM D_DATE
WHERE WkDayIn = 'Yes' AND HolidIn = 'No'
OPEN c
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
WHILE @@FETCH_STATUS = 0
--
-- update the business day in D_DATE
--
BEGIN
UPDATE D_DATE
SET BusDay = @i
WHERE DateID = @DateID
--
-- reset the counter to 1 if it's the last day of the month
--
IF @DtTimeD = @LASTDAY
SET @i = 1
ELSE
SET @i = @i + 1
--
IF @@ROWCOUNT = 500
COMMIT
--
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
END
CLOSE c
DEALLOCATE c
GO
View 10 Replies
View Related
Feb 15, 2006
I have a form that I turned off the nav buttons. I then create my own.The reason I do is that I cna then move the nav buttons any where I needto on the form.The only thing I can not figure out is on the default ones, it shows thetotal records. I can not figure out how to do this. The form is based ona table and when I add a record or delete a record I want to make ssurethe total displayed is correct.How can I display the total records, for the table, on the form on thefly?Does that make sense?Michael Charneym charney at dunlap hospital dot org*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Aug 8, 2006
While inserting data into a target table, I'm trying to populate a primary key ID field sequentially. For each record, the value of the primary key field needs to be incremented by one (a counter).
I've tried to use the RowCount transformation to store the values in a variable. I'm able to successfully do that; however, I don't know how to read or update the variable incrementally.
If someone knows how to perform this task, please let me know. I would greatly appreciate it.
View 3 Replies
View Related
Apr 8, 2006
when a new record is inserted in to table, i want to increment the variable called counter by 1
how can i implement this?
what is the IDENTITY field in a table? is it something like a counter variable
pls let me know
View 4 Replies
View Related
Aug 6, 2004
Hi All,
I need some comments regarding my Hit Counter that I have created for my sites. Here is my case:
I have developed several websites which I host along with MS SQL server. I created a class that calls a stored procedure to add a count to the database field. The way that I am using it is that every page in the page_load event will call the class indicating which counter to use (i.e. LoginPage, HomePage, ProductPage, etc.). The class in turn will perform the database route to increment the counter. In the Store procedure before incrementing the counter, I first check to see if a record for the current date has been created, if not I add the record with a date stamp with all the different counter values as zero. I then will add 1 to the desired counter for the date.
Is this a good solution?
What I now want to do is create a page that allows the different website owners to access a page with the relevant page counters.
Any ideas will be greatly appreciated.
Thanks
View 2 Replies
View Related
Jul 22, 2005
I'm a local admin on a SQL Server with dba permissions. I can view that SQL Server in System Monitor and I can create a performance log counter. When I try to start the counter, I get an error: ---------------------------
Performance Logs and Alerts
---------------------------
The SQLSever log or alert has not started. Refresh the log or alert list to view current status, or see the application event log for any errors. Some logs and alerts might require a few minutes to start, especially if they include many counters.
---------------------------
OK
---------------------------
Does anyone have ideas why this isn't working?
View 14 Replies
View Related
Aug 28, 2006
I want to delete a counter using an SQL query (alter, drop...)
What could be the SQL query
View 3 Replies
View Related
Jun 13, 2008
CREATE TABLE#Info
(
DbName SYSNAME,
ROUTINE_TYPE SYSNAME,
ROUTINE_NAME SYSNAME,
Lines INT
)
EXECsp_msforeachdb'
INSERT#Info
(
DbName,
ROUTINE_TYPE,
ROUTINE_NAME,
Lines
)
SELECT''?'',
d.ROUTINE_TYPE,
d.ROUTINE_NAME,
SUM(CASE WHEN d.c10 < d.c13 THEN d.c13 ELSE d.c10 END)
FROM(
SELECTROUTINE_TYPE,
ROUTINE_NAME,
1 + LEN(ROUTINE_DEFINITION) - LEN(REPLACE(ROUTINE_DEFINITION, CHAR(13), '''')) AS c13,
1 + LEN(ROUTINE_DEFINITION) - LEN(REPLACE(ROUTINE_DEFINITION, CHAR(10), '''')) AS c10
FROM.INFORMATION_SCHEMA.ROUTINES
) AS d
GROUP BYd.ROUTINE_TYPE,
d.ROUTINE_NAME
'
SELECTDbName,
ROUTINE_TYPE,
ROUTINE_NAME,
Lines
FROM#Info
ORDER BYDbName,
ROUTINE_TYPE,
ROUTINE_NAME
DROP TABLE#Info
E 12°55'05.25"
N 56°04'39.16"
View 15 Replies
View Related
