MS SQL Server :: Sequence No Group Wise

Having The Issue Regarding The Group Wise SUM

Apr 25, 2008

I have a group and a subgroup in a report. in the subgroup there are repetitive rows. But i want to display them at only once within the subgroup. So I hide them. Now I want to have the sum of a field viz. "quantity" group wise, having the only single value of the subgroup. But problem is in the sum the values which are hidden also included. But i don't want them to include in the sum of the group.
Can anybody give the suggestions how to resolve the problem..
I am fighting with it from a long back but i am not able to solve the issue.. plz help me out..

location invoice no. date quantity item
delhi inv/del/1 1/1/2008 3 item-1
item-2
item-3
inv/del/2 2/1/2008 2 item-4
item-5
Location Total 5

This is what i want... for invoice inv/del/1 there are 3 items, i hide rest of two rows using "Hide Duplicates", But the group sum i.e. Location Total in the report is coming out as '13' as it includes the quantities of hidden rows also. But in actual I want '5'.

thanx you..

View 3 Replies View Related

How To Get Group Wise Distinct Count

May 9, 2008

Hi,

I need to get the groupwise distinct count .
For eg. Lets assume we have group as Category and under this there are two childs Category1 and Category2.
I need the distinct count under Category1 and Category2 separately with the count resetting after Category1 and Category2.
Any help will be greatly appreciated.

Regards,
Ravi

View 4 Replies View Related

SQL Server 2008 :: Rows Holding Group-wise Maximum Of Certain Column?

May 13, 2015

is there any more efficient way for example to implement the next query?

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
WHERE dealer = 'dealer sample'

What indexes I should create for this query?

View 2 Replies View Related

Add Sequence Number Within Group?

Oct 13, 2013

I have the following data:

POL# POL_EFF_DATE
123 1-1-2012
123 1-1-2012
123 1-1-2012
123 1-1-2013
123 1-1-2013
456 1-1-2012
456 1-1-2012
456 1-1-2013
456 1-1-2013

I want to add sequence number to each group with same Pol# and Effective Date like this:

POL# POL_EFF_DATE Seq
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2013 2
123 1-1-2013 2
456 1-1-2012 1
456 1-1-2012 1
456 1-1-2013 2
456 1-1-2013 2

is there a set based function to accomplish this in SQL 2008?

View 2 Replies View Related

How To Insert Sequence Number With Group

Jun 11, 2008

SOURCE TABLE
ID________COMMENT
123_______I am joe
123_______I am programmer
124_______I am Wang
124_______I am programmer
124_______I like cricket

DESTINATION TABLE

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

View 3 Replies View Related

Reporting Services :: Insert Row Number For Zone Wise (group By Zone Column) In SSRS Report

Jul 19, 2015

How to insert a row number for a zone wise(ie group by zone column) in ssrs report in zone column i should get zone1 only once (should not get Zone1,zone1, zone1 -3 times)

sl.no Zone District no.of.region

1 hyd 24
2 ZONE1 chn 12
3 bang 2
1 raj 4
2 ZONE2 vizag 3
3 bbb 34

View 4 Replies View Related

How To Extract A Sequence Number From The Group By Count?

Nov 17, 2006

Hi,

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

Example:

I have src data :

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

SUBSCRID col1 col2 col 3 col4

1001 val1 val2 val3 val4

1002 vala valb valc valc

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

1002 ..........

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

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

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

So my result should be :

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

SUBSCRID SUBSCRCNT col1 col2 col3 col4

1001 1 val1 val2 val3 val4

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

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

1002 1 vala valb valc valc

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

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

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

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

Thanks,

Venkat

View 5 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

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

Wise Job Scheduling

Feb 22, 2007

Guys,

I've been asked to properly schedule some jobs on our system for a database following a full recovery model. I realized that the order for some of the jobs might matter and came up with the solution, which is described below.

Please let me know if this ordering makes sense and/or if you have any general comments/suggestions:

1. Backup all dbs [in case defragmentation messes something up]
2. Defragmentation [this will grow the transaction log]
3. Backup transaction log [backs up trans log and truncates inactive portion of it]
4. Shrink log file

Also, I've been previously suggested not to shrink the log file, unless absolutely necessary, as it is resource intensive. In my experience, however, this operation does not seem to be resource intensive at all. It took me 1 second to shrink the transaction log file from 25GB to 2GB.

Is it regrowing of the log file that will affect performance?

Also, if I do shrink the transaction log file, is there a suggested value to shrink it to - possibly a function of the database file size?

Thanks a lot

View 6 Replies View Related

Max Value Row Wise Query

Jan 25, 2015

I have a table having 3 coulmns.

amnt1 amnt2 amnt3
100 200 230
200 170 120
290 188 299
800 170 120

i need to get max amount row wise like

amnt
230
200
299
800

View 4 Replies View Related

BIT-Wise Aggregation

Oct 29, 2006

Hi,

I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)

In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.

I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.

SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name

Thanks,
Loonysan

View 6 Replies View Related

Data In Column Wise

Apr 18, 2008

My Table(collection) Structure consist of below:
TypeDate ItemAmount
REVENUE2007-04-01serv002***
REVENUE2007-04-01Serv004***
REVENUE2007-04-01serv005***
REVENUE2007-04-01AMC-***
ROYALTY2007-04-01serv002***
ROYALTY2007-04-01Serv004***
ROYALTY2007-04-01serv005***
ROYALTY2007-04-01AMC***

O/p should come as:
====================
Revenue:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

Royalty:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

View 6 Replies View Related

Need Weekly Wise Data

Apr 2, 2007

Hi,

Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday.
Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.

Help!!!

Regards,
Navneet

View 11 Replies View Related

Is There Any Difference Performance Wise

Dec 4, 2007

Dear All,
is there any difference performance wise using
select * from my_table
and
select mycol1,mycol2....mycoln from my_table

actually i've read from one article the there is big difference....
please clear my doubt...
thanks in advance

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 8 Replies View Related

Is There Anyway To Generate Day Wise Scripts

Feb 12, 2008

Dear All,
is there any way to generate daywise scripts in database? i mean all the transactions wether insert update or delete....
i need all the changes on that particular date(with in 24 hours)....
is there any procedure or function for this?

i've almost 1200 tables in that database.

note : due to replication is not possible for my scenario, i'm thinking in different ways to achieve my purpose.

this is my requirement
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96959

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 1 Replies View Related

How To Use Order By Clause Row Wise

Sep 9, 2015

i want to know how to use order by clause in row wise

for example, suppose the rows is
10 20 30 40

so the output is should be
10
20
30
40

View 3 Replies View Related

Get Table Rows Priority Wise

Aug 24, 2007

I have a table.
Highlight-------------
Id
Name
Detail
StartDate
EndDate
Priority

I want to make a query which returns 1 Highlight in the current date.
But remember I have already set the Hightlight Priority 1 to 5. And I want that Hight Priority rows select more times than Low Priority Rows.

View 23 Replies View Related

Display Of Data Category Wise

Dec 12, 2007

Hi,
I have a requirement where I need to display the items category wise
I have 3 tables,
one stores category_id, parent_id and category_name
second table stored item_id,item_name and proce
3rd table stores category_id and item_id
I need to display the values like this
Category name, Item name
with first parent category ans ites items should be displayed and next sub category and its item
By category table with contain 2 levels of sub category
i.e Category1
       Category11
        Category111
any help with the query would be much appreciated, I wrote the query something like this....
select c.category_name,parent_id,item_name from category c,items i,Category_item ci where
ci.item_id=i.item_id and c.category_id=ci.category_id order by parent_id, category_nameBut it will display all parent category items then the sub category items I want to display like thiscategory1 items1category11 item11category1 item12category11 item111category11 item112 category2 item2
Thanks

View 2 Replies View Related

Row Wise Operation In MSSQL Server

Jun 14, 2004

How can I create a row wise trigger in MSSQL Server .

CREATE TRIGGER trigName ON tableName for
INSERT , UPDATE , DELETE
AS ...

For a multiple delete , I got only one trigger invocation .
But I need individual trigger calls for each row ...
How can I do this in t-sql ?
Is there any usage like FOR EACH ROW in Oracle ?
Is it possible through INSTEAD OF TRIGGER ?
Please help !!!!!!!!

View 2 Replies View Related

Count Record Gruop Wise

Apr 3, 2008

Hi all,
i am using sql server 2005.
let me explain the scenario.
i am writing a function that return no of call a sales execituve done.

an executive can present n no of product to one customer but that call will be only one

my table record look like

empid custid product date and other field
em111 iimran aaaa 01-04-2008 ........
em111 iimran bbbb 01-04-2008 ........
em111 iimran cccc 01-04-2008 ........
em111 xxxxxx aaaa 01-04-2008 ........
em111 xxxxxx 2222 01-04-2008 ........
em111 iimran aaaa 02-04-2008 ........
em111 iimran bbbb 02-04-2008 ........
em111 iimran cccc 02-04-2008 ........
em111 xxxxxx aaaa 02-04-2008 ........
em111 xxxxxx 2222 02-04-2008 ........

Now if i select total no of call it should give me four call
also i am getting this through this query

select dcr_date , cust_id from vwdcrdtl where emp_id = @empcode and dcr_date between @stdate and @enddate group by dcr_date , dcr_cust_id

this query correctly return 4 rowcount(which i am gerring though @@rowcount) but i want this rowcount to return from function

i assinged this to rowcount to int veriable but this giving me error .

i would like to know how can i get count of these records

Please Help

View 1 Replies View Related

Department Wise Max And Min Salaried Employees

Sep 14, 2014

I have the employees and department tables(structure below).

Write a sql to get the department name,employee earning maximum salary,employee earning minimum salary for each department

result set:

dname |max_salaried_employee|min_salaried_employee
------------------------------------------------------
Accounts | Blakes | Miller
HR | King | James

Structure :

create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)

[Code] .....

View 1 Replies View Related

Month Wise Employee Count

Apr 26, 2007

Hi Folks,

Please help me in writing a query. I need to get current strength of employee's, monthwise (till each month).

Table Structure
----------------
Employee_Master

ED_EmpId ED_DOJ ED_Name
-------- ------- ---------
2006-01-02 SRINIVASA
2006-01-02 KAVITA
2006-01-02 VIVEK
2006-02-20 CHANDRA
2006-02-25 PARIMAL
2006-02-30 SATISH
........
2007-01-10SANJEEV SHARMA
2007-01-14 JITENDRA PRATAP SINGH
2007-03-15 SANDEEP
2007-03-02 SUNIL KUMAR SHARMA

I require the data in the below format..

For the year '2007-2008'

Month Count
----- -----
April(07) 10
May . 10
June . 10
July . 10
August . 10
Sept 10
Oct 10
Nov 10
Dec 10
Jan (08) 10
Feb . 10
March . 10

Thanks.

View 2 Replies View Related

T-SQL (SS2K8) :: User Wise Query Tracking

Apr 30, 2015

In my production server i created sql server user for limited permission. Now I want details of what are all the Query executing in user wise .

View 1 Replies View Related

Change Multiple Rows Into Column Wise

May 25, 2014

I have multiple number of rows i want to change into column wise. My data

PondCropDOCABWTargetABW
01PA01-18700.21
01PA01-18150.590.77
01PA01-18221.241.5
01PA01-18280.922.6
01PA01-18351.823.7

[Code] ....

I want the data should be like these

01PA01-18 01PA02-18 01PA03-18...206B15-01 Target ABW
Doc ABW Doc ABW Doc ABW Doc ABW
7 0 7 0 7 0 11 0.42 0.21
15 0.59 15 0.59 15 0.59 18 0.77 0.77
22 1.24 22 1.24 22 1.14 25 1.22 1.5
28 0.92 28 0.87 28 0.91 32 2.7 2.6
35 1.82 . . . . . . .
42 2.6 . . . . . . .
49 3.62 . . . . . . .
56 4.64
63 5.54
66 6.24
73 7.25

View 4 Replies View Related

Month Wise Date On Column Address

Nov 20, 2007

could u help me out to get the month wise date on column address

this is the query

SELECT[ID],
SUM(CASE WHEN week_no = 1 THEN Qty ELSE 0 END) AS week1,
SUM(CASE WHEN week_no = 2 THEN Qty ELSE 0 END) AS week2,
SUM(CASE WHEN week_no = 3 THEN Qty ELSE 0 END) AS week3,
SUM(CASE WHEN week_no = 4 THEN Qty ELSE 0 END) AS week4,
SUM(CASE WHEN week_no = 5 THEN Qty ELSE 0 END) AS week5
FROM
(
SELECT[ID],week_no = DATEPART(week, [Date]) - DATEPART(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)) + 1,Qty
FROM@sample
WHERE[Date]>= DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)
AND[Date]< DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date) + 1, 0)
) d
GROUP BY [ID]

for this query the output will be

/*
ID week1 week2 week3 week4 week5
----- ----------- ----------- ----------- ----------- -----------
st001 114 38 0 0 0
st002 110 110 0 0 0

but i need date wise split up to be displayed for particular month

ID week1 week2 week3 week4 week5
(1-7) (8-14) (15-21) (22-28) (29-31)
----- ----------- ----------- ----------- ----------- -----------
st001 114 38 0 0 0
st002 110 110 0 0 0

pls help me out.

Shiney

View 6 Replies View Related

Can A Managed Udf Contain A Static Dictionary&&<&&>? Is This A Wise Idea?

May 17, 2007

I currently have a udf written in T-SQL that's getting way too logically complicated!

It��s typically accessed like this:

SELECT PartNumber,dbo.PartPrice(Manufacturer, Model, AssemblageInfo, Version, CustomerDiscountLevel) FROM WorkOrders where OrderNumber=123456

The udf does some complicated manipulations on the parameters and eventually does a SELECT on a lookup table and returns the result.

If I make this a managed code udf, the logic gets much simpler to write (great!).

But, my question is:

Can I take the lookup table and embed it in the udf--so the udf doesn't have to go to the database to do the lookup?

Would I do that in a STATIC dictionary<>?

Is it wise to keep the info statically?

The lookup table consists of 3600(+/-) elements and changes exactly once a month.

The SELECT statement using the udf typically returns several thousand rows.

The SELECT is done often.

--Mark

View 4 Replies View Related

Transact SQL :: Time Column With Order Wise

Nov 17, 2015

I have a table like below

CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')

I need result like below screen shot

here 'I' stands for In,and 'O' for Out, in this example first In time not available

View 7 Replies View Related

Transact SQL :: Count Of Records By Month Wise

Oct 7, 2015

I am trying to get count of records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...

SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)

Output

Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139

View 4 Replies View Related

How To Show Single Row Wise Percentage In Matrix

Feb 11, 2008

Hi,

I'm using Reporting Services 2005. In my report I'm binding data in a matrix by grouping 3 row fields (nested within each other) and 1 column field. Now I get the subtotals alright but I also want to get percentage at row1 only rather than grouping it with either in row or in column. I've tried so many ways but am not able to do it. Can anybody help me on this or is there a way in SSRS 2005 to get over this.

Also please suggest something instantly, as I'm terribly in the need
Thanks

View 1 Replies View Related

How To Get The Folder Wise Security Info Of All The Users?

Dec 21, 2006

Hi,
The Report Manager portal has many folders. For each folder there are specific users with different roles.
I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.

Use ReportServer

SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r

WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID
The above query fetches all the users and their roles.
The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query.
TIA

View 1 Replies View Related

Is It Wise To Decrypt Stored Procedures In SQL Server?

May 5, 2008

Hi All,

I'm new to SQL Server 2005 encryption security. I developed a simple login form using .NET 2 framework. I'm encrypting the user's password on the execution of the INSERT statement using SQL Server EncryptByCert(Cert_ID('abc'),'password').

My only concern is that of decrypting the password on the execution of the SELECT dtatement in a stored procedure using the DecryptByCert(Cert_ID('abc'),val,N' certificate password'). Anyone who has rights on executing that particular stored procedure in SQL Server can easily return the user's password.

Can I still use the SQL encryption, whilst the login application handles the decryption (meaning I embed the DecryptByCert in the application ).

If anyone has other ways of implementing this please forward them? (I preferable would like to store all the certificates, private keys, etc on the database side )

Thanks,

Matt

View 1 Replies View Related

Transact SQL :: Passing Value In Where Condition Loop Wise

Jun 1, 2015

I want to calculate year to month data for month wise . Ihave start range and end range table like below

Start range and end range table

Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015

[Code] ....

Then I want to pass this value one by one in below table for get value month wise

Main table

Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404

[Code] ....

The below query is for november month: (Apr to Nov)

select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)

So I want to pass second row

select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'

Like wise I want to get month wise data of YTM data.

My expected output is

201411
201412
201501
201502
201503
201504
201505

[Code] ....

View 10 Replies View Related