.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Get the row position of the group? merging query?

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
 
Hello! I am using SQLS2005. I have two tables: Unit: UnitId int PK Title varchar UnitOption: UnitOptionId int PK UnitId int FK Title varchar Quote: QuoteId int PK UnitOptionId int FK Title varchar I want to create a scalar UDF that takes a QuoteId param and returns a varchar that contains the following description (pseudu): Quote.Title + '-' + Unit.Title + '-' + Unit.UnitId + /* Here is where my question is: If there are more than 1 UnitOption under this Unit, then return '-' + the UnitOption number under this Unit (i.e.) if under this Unit, there are 3 UnitOption with IDs 13, 17, 55 under the unit, and the current Quote.UnitOptionId is the 17 one, it should return 2. Which means I want to retrieve an ID of this row in the group. Else return ' */


View Complete Post


More Related Resource Links

Need Help in LINQ query for group By with chunks of record

  

I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
{
p.CON_ENTRY_POINT,
p.APPTType,
p.CON_CONTAINER_ISSUE_DATE
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
{
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&
h.CON_CONTAINER_ISSUE_DATE == objUnique.CON_CONTAINER_ISSUE_DATE)))
{
obj.Shipper

Content Query with more thatn on group

  

I want to group the result of the content query web part in two levels but this web part only have one Grouping field. How can I do grouping with more than one field?

<site> and DocumentSubtype (custom property)

example

  • Site 1
  •   Document Sub type 1
  •        File 1
  •        File 2
  •   Document Sub type 2
  • Site 2
  • Document Sub type 1

Group Query

  
Hi All. Greetings. I have 3 TablsTable =PersonID Person ---------- ----------- 1 ali2 abuTable = DetailID Person_ID Detail---------- ----------- --------------1 1 Good2 1 Excellent 3 2 Normal Table = ReferenceID Detail_ID description---------- ----------- ------------1 1 Has son2 2 Drop3 2 DiedI want result Like this.Ali----------GoodExcellentHas sonAbu----------NormalDropDiedAbove are 3 tables. I want to Show Person Name and then Its sub link or subcategory. If i use only 2 tables then i canshow required result but i have database where i have to fetch the record from 3rd and 4th table and bring under sub category.Thanks

T-SQL 2005 query for Group BY and not GROUP in same query for SQL reporting service use purpose?

  
Hi, I have SQL 2005 table like bellow @OrderTable I want to display all row data and GROUP BY data as well for SQL Reporting Service Matrix purpose...   declare @OrderTable TABLE (OrderID varchar(10),OrderType varchar(20),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06','O1',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06','O2',105.48,'2010-06-12') INSERT INTO @OrderTable VALUES('P07','O3',555.00,'2010-06-09') INSERT INTO @OrderTable VALUES('P08','O1',10.22,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O1',55.66,'2010-03-17') INSERT INTO @OrderTable VALUES('P06','O1',45.44,'2010-03-17') INSERT INTO @OrderTable VALUES('P07','O3',477.81,'2010-03-18') INSERT INTO @OrderTable VALUES('P07','O3',78.85,'2010-03-18') INSERT INTO @OrderTable VALUES('P06','O1',78.08,'2010-04-09') INSERT INTO @OrderTable VALUES('P07','O2',899.90,'2010-04-22') INSERT INTO @OrderTable VALUES('P08','O3',25.33,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',859.01,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',7433.89,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O1',1005.41,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O2',455.20,'2010-06-09') INSERT INTO @OrderTable VALUES('P07','O3',85.30,'2010-06-12') INSERT INTO @OrderTable VALUES

Trying to build query using DISTINCT or GROUP BY...beginner here

  
Hi all, I have a table with the following format: instanceID    timeStamp  stepID 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:33.807 2 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:34.807 3 ... EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:26.773 2 so I need to build a query which will return 1 instanceID and all its stepIDs in one row. So the results would have to be something like this: instanceID    timeStamp  StepIDs 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1,2,3 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1,2 and if possible I would like to specify something like...bring me the data where 'timeStamp' > 2010-09-07 20:35 ps: I tried using DISCTINCT and GROUP BY but could not reach the desired results. Thank you!JCD

Create SharePoint Security Group populated by AD query

  
Is there any non-code way to create a SharePoint Security Group that is populated by an AD query? The "standard" way of getting the same "effect" is to create a group that contains an AD group but that does not allow members of a particular site to see who else is also a member of the site Any thoughts?

Group query

  
Hi All, Greetings.From Select statment Group query I am showing bellow recordAliHadi  | 2   | 3Jon    |  4 |  5HadiRobi | 2  | 3 Ali    |  4 |  5RobiHadi  | 2 | 3 Ali     |  4 |  5I want to show bellow result by Group query so what i wrote so it skip one category but show me in subcategory.Ali Hadi  | 2   | 3 Jon    |  4 |  5Robi Hadi  | 2 | 3 Ali     |  4 |  5Thanks

How to do a <> Select Query, and assign results to a Group 'Other'

  

How can I use this in a Select Query?
<> "*" & "Internet" & "*" Or <> "*" & "Old Customer" & "*" Or <> "*" & "Reference" & "*" Or <> "*" & "Saw Trucks" & "*" Or <> "*" & "Y/P" & "*" I want to group all the results (named count) and call the result 'Other'

Here’s my SQL now:

SELECT DATABASE.[LEAD FROM], Count(DATABASE.[LEAD FROM]) AS [Count of Leads], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-7,Date()) & "#") AS [Last 7-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-30,Date()) & "#") AS [Last 30-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-365,Date()) & "#") AS [Last 365-Days]

FROM [DATABASE]

GROUP BY DA

Query on solution which needs help with joins or group by

  

Wonder if anybody can help me with a solution for this query.

Have been asked to flag entries on a table as deleted where there are matching policy premiums. I have created 2 temp tables called #AMPneg (which contains RowID, Premium Value, Absolute value of premium and policy id for all premiums < 0) and #AMPpos which is the same apart from being for premiums > 0.

I have tried running the following sql:

SELECT      AccountsModulePaymentKeyNeg,

        PaymentAmountOriginalCurrencyNeg ,

        AbsolutePaymentNeg ,


SPView query to find if the current user belongs to a Group

  

HI All,

I´m trying to write a query for a list view that should return all items if the current user belong to a group. If the user doesn´t belongs to this group, no items should be returned.

So far, I could find many equal examples about using the CAML Membership element in a comparison with an AssignetTo field. But not making a comparison between the current user and a specific Sharepoint Group.

Any suggestions, please?

Thanks



slq query with sum and group by

  

Hi i have a table CALLED 'REPORT' with 3 column of time T1,T2,T3 AND ONE COLUMN WITH MONTH WITH VALUE 1,2,3,4 ETC..I NEED TO FIND THE SUM OF T1,T2,T3 FOR EVERY MONTH

T1 T2 T3   MONTH

2  3  6       1

2 1  3        1

1  4  8       1

2  2 3        2

3 3  3        2

SO I WANT THE QUERY THAT GIVE  2 COLUMN AS TIME AND MONTH

TIME   MONTH

20        1

18        2

MEANS I WANT FOR EVERY MONTH SUM OF ALL 3 COLUMN T1,T2,T3

HOW TO ACHIEVE THIS

PLEASE HELP ME

 

 

 


Using Group by and Order By in a Single query

  

Hi Guys,

I have a requirement to use Group By and Order By in a single query and I used it but the results are not as estimated so I need your help guys

I am writing a query like this

select id, opendate, lastactiondate, (opendate-lastactiondate) as responsetime, ownergroup from ticket group by ownergroup, responsetime, opendate, lastactiondate, id order by opendate

What I am expecting is it will group the results and then order the results in that groups based on the open date but whats happening is opposite it is ordering by opendate first and then grouping if possible

So I see Order By is overriding Group By, is there a waywe can group first and then Order by open date inside that groups

For your Informtion: I am using DB2 Database

Help is appreciated


Group by Clause in Union Query

  

Table 1

Qty BatchNo MaterialCode

54 211 127

50 AR0347 165

252 054 190

180 022 191

50 HP10004 194

20 005010 196

 

Table 2

Qty BatchNo MaterialCode

6 211 127

28 054 190

20 023 191

 

Select Sum(iQty) as iQty ,vBatchNo,vMaterialCode

from

(

Select 

 a.iQty as Qty,      

 a.vBatchNo   as  BatchNo,      

 a.vMaterialCode &

Group by Query

  

MDX Query runs endless if the User is not a member of the Domain Admin Group

  

Please, before you say "its not possible" read my post. Myself, i couldnt believe it, until i saw the behaviour with my own eyes.
Environment: SSAS 2005, tested with a MDX Query executed in the SQL Server Management Studio.

We deliver a CUBE (SSAS 2005) that we have build for our Billing System (SQL 2005). Nothing tricky on the cube definition. The cube is installed on several customer systems, runs without a problem. On our in-house test server it runs with no problem. I can execute the same MDX Query connected as a User who is a Member of the Domain Admins and with a User who is a "simple" Domain User. The same query performance, the same Resultset, experienced in-house and by many of our customer.

But on the system of one of our customer i have a really strange behaviour. If i connect on the SQL Management Studio with a User that is in the Domain Admin Role (let's call it user "A") the same MDX Query that we use in-house runs without a problem. But on the customer system i cant execute the MDX Query with a User that is not a Member of the Domain Admin Role (let's call this user "B").
The simple Domain User "B" starts the SQL Management Studio and connects to the Analysis Services Instance. No error. In the SQL Management Studio, on the left pane it has the same metadata, dimensions and members

CAML query to check if user is member of a specific group

  

I've got a nested query caml which basically should check 3 things:

  1. Check if a task has been assigned directly to the ucrrent user
  2. Check if a task has been assigned to a group the user is in
  3. Check if the user is member of a specific

The first to bullet points work like a charm, but I can't figure out how to get the last one to work. Here is what I thought should work:

query += "<Where>";
query += "<Eq><Membership Type=\"CurrentUserGroups\">";
query += "<Value Type=\"String\">Name of my specific group</Value>";
query += "</Membership></Eq>";
query += "</Where>";

Somehow it doesn't seem to be the correct approach, is there somebody who can help me out on this? Thanks


caml query - group by column and get count of records

  

Hi,

I want to group by on column name and get the count of records returned.

 

 


RowNum Ref_No Date
1 1011 1/1/2010
2 1011 2/1/2011
3 1011 1/8/2009
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend