Transact SQL :: Grouping With Case Statement

Aug 9, 2015

In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.

So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.

I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?

select 
case when @include_browser =
1 then browser_name end as browser_name, 
case when @include_browser_version
= 1 then browser_version end
as browser_version,

[Code] ....

View 4 Replies


ADVERTISEMENT

Grouping With Case Statement?

Apr 2, 2008

I am attempting to get one line with all the price info for three fields.
The end result looks like this
PT_UD_KeyPatternPrice CastingPrice MachiningPrice
FG-020006-000 0.00 1840.00 0.00
FG-020006-000 750.00 0.00 0.00
FG-020006-000 0.00 0.00 3945.00

I would like it to look like this

PT_UD_KeyPatternPrice CastingPrice MachiningPrice
FG-020006-000 750.00 1840.00 3945.00

What am I doing wrong?
Here is my query

SELECT PT_UD_KEY,
'PatternPrice' =
CASE
WHEN PT_UD_NUMBER = 1 THEN TOTAL
ELSE 0.00
END,
'CastingPrice' =
CASE
WHEN PT_UD_NUMBER = 15 THEN TOTAL
ELSE 0.00
END,
'MachiningPrice' =
CASE
WHEN PT_UD_NUMBER = 3 THEN TOTAL
ELSE 0.00
END
FROM CAL.dbo.EXT00103
WHERE PT_WINDOW_ID='ITEM PRICE' AND LEFT(PT_UD_KEY,2)='FG' AND PT_UD_NUMBER IN (1,3,15) AND TOTAL >0.00
ORDER BY PT_UD_KEY

View 3 Replies View Related

Transact SQL :: How To Add Subtotal / Grandtotal Using Case And Grouping Sets

Jun 15, 2015

How do you incorporate a case statement so that you can add "sub total" and grand total" to each grouping set section? Trying to see how to incorporate case.

[URL] ....

SELECT
CustomerID,
SalesPersonID,
YEAR(OrderDate) AS 'OrderYear',
SUM(TotalDue) AS 'TotalDue'
FROM Sales.SalesOrderHeader

[Code] .....

View 4 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

Transact SQL :: CASE Statement For A WHERE

Sep 25, 2015

I've been beating my head against this for a bit and haven't been able to figure this out I want to pull 1 set of values between a date time range if @Report = '1' but if @Report = 2 or 3 I want it to drop 3 of the parameters so it will pull all items. by dropping the where parameters for @BeginRangeDate and @LookOutDate and L.COLineStatus. My report works great if I go in and run Report 1 with the date parameters in the where statement and also works great for Reports 2, 3 if I REM out those 3 items in the Where statement. Here is what I currently have

CASE@Report
WHEN'1'THEN
WHEREL.RequestedShipDate>=@BeginRangeDate
ANDL.RequestedShipDate<=@LookoutDate
ANDDO.ForecastDate>=@ForecastDate

[Code] .....

View 7 Replies View Related

Transact-SQL Help - CASE Statement And Group By

Jul 20, 2005

I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad

View 4 Replies View Related

Transact SQL :: Order By Used With Case Statement

Sep 24, 2015

I use Order By Used with Case Statement and may be this is the reason for which i am getting error

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Here is my complete sql where i use Row_Number function for pagination with sorting.

DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1

[Code] ....

View 7 Replies View Related

Transact SQL :: Case Statement With Different Columns

Oct 20, 2015

Is it possible to combine a CASE statement with two different columns to retrieve data into one result column? In one column it has multiple JobCode, but this needs to be divided.  The only way I can see to do this is take the data from another column to get the results.Ex. JobCode - has one code for several job descriptions (there are about 30), but all within this code cannot have the same level of access.  So I need to divide them out, and put them in one column for AccessLevel. 

 JobTitle - has one code for one job, (but there are over 100).I want to pull from both columns to get the results I need to assign appropriate access level in one column.

Case JobCode     (they all have same job code, but everyone in this job code should not have same access)
When '45' Then '1' (Principal, Asst. Prin, or any Administrator, Counselors)
When '25' Then '2' (this could be teacher, etc. )      
Case JobTitle   (this is how access should be)
        When '12345' Then '1' (this is Administration only) 

[code]....

View 12 Replies View Related

Transact SQL :: Update Statement With Case

Nov 11, 2015

I am trying to run the below but I get an error of 'Incorrect syntax ')''  --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.

UPDATE abcdefg
SET [Date] = GETDate(),
[readytogo] =
(
CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view])
THEN 'Yes'
ELSE
'Needs Verification'

[code]....

View 5 Replies View Related

Transact SQL :: Case Statement In Update Clause

Jun 4, 2015

I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type  = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
 
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC'  THEN  (c.volume)
 WHEN c.TYPE = 'DEF'  THEN  (c.volume)
 WHEN c.Type = 'GHI'  THEN  (c.volume)
 Else 0
 END),
 CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'
 WHEN c.Type = 'ABC' THEN '123.1'
 WHEN c.Type = 'DEF' THEN '234.2'
WHEN c.Type = 'GHI' THEN '567.1'
END
 from table1 a join table2 b
 on a.Cust = b.Customer
 join table3 c
 on b.account = c.account and b.channel =c.channel

Why its not working properly? But if i use Select statement instead of update query its working properly.

View 18 Replies View Related

Transact SQL :: How To Add A Letter In Front Of Value Using Case Statement

Jul 28, 2015

I am trying to add the letters 'MS' in front of value while using a case statement. If Dispo = 2 I want it to pull back 'Inactive', else I want it to pull back the Value with MS in front (eg. "MS14"). The data in the value column are numbers. Would I use a CONCAT? If so where does that need to go?

Case when dispo = 2 then 'Inactive' else cast(Value as varchar(11)) end ,

View 27 Replies View Related

Transact SQL :: How To Use Case Statement In Select Condition

Nov 6, 2015

below is my original query

select Value = count(*) from dbo.test 

I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.

View 5 Replies View Related

Transact SQL :: How To Use A Between In Case Statement For Joining 2 Tables

Jul 27, 2015

below is the sql query i am trying to write and i receive an error saying Incorrect syntax near the keyword 'Between'.

Update dbo.Claims_Primary_Adjuster_Test_Sujith
SET
Claims_Primary_Adjuster_Test_Sujith.Supervisor = Claims_Internal_Transfer.Supervisor,
Claims_Primary_Adjuster_Test_Sujith.Office = Claims_Internal_Transfer.Office,
Claims_Primary_Adjuster_Test_Sujith.Specialty = Claims_Internal_Transfer.Specialty
from dbo.Claims_Primary_Adjuster_Test_Sujith
Left Outer Join Stg.HS_DW_RV_ClaimsON Claims_Primary_Adjuster_Test_Sujith.Claim_Number = HS_DW_RV_Claims.ClaimNumber

[code]....

how to modify my code such that it works for my condition?

View 10 Replies View Related

Transact SQL :: Nested Select Case Statement

May 18, 2015

I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.

Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else
WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'

In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.

View 2 Replies View Related

Transact SQL :: Using Case Statement With Update Query

Jun 22, 2015

I have a table A  and lookup table B.

table A:
| ID | FRUIT | VEGETABLE | GOOD |
--------------------------------------------
|  1  | orange | cabbage     |  no   |
|  1  | apple  | lettuce        |  yes   |
|  1  | kiwi     | broccoli      |  no   |
|  1  | pear    | kale           |  yes   |

table B:
| ID | FRUIT | VEGETABLE |
-------------------------------
| 1  | apple  |  lettuce       |
| 2  | pear    |   kale         |

If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.

This is what I have so far.

update tableA
set GOOD =
(case when tableA.id = C.id then 'yes'
else 'no'
end
)
from
(select tableA.id as id
from tableA A
left join tableB B on B.fruit = A.fruit
and B.vegetable = A.vegetable) C

View 6 Replies View Related

Transact SQL :: Unable To Get Required Output Using Case Statement

May 17, 2015

Table Structure
EID    COLA    COLB
1    name    A
1    age    23
1    city    hyd
1    email    abc@live.in
1    mobile    45126
2    name    B
2    age    43

[code]....

how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null

View 9 Replies View Related

Transact SQL :: Case Statement In Where Clause - Select First / Default Value If Two Different Value Exists

Nov 16, 2015

I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.

Store     Type    Sales
11           A        1000
23       A      1980
23       B       50
5         B      560

I want to filter the store in "where clause"  where

1)- if the store has type A and  B, then assign only A
2)- if the store has  type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.

Select Store, sum(sales), Type
from table1
where (TYPE]=  (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]

The above statement is not working for when store has only Type B associated with it.

View 7 Replies View Related

Transact SQL :: Find Exact Word In String Within Case Statement

Aug 6, 2015

I am required to find or check that a specific word exists in string or not.

Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.

1. 'St is valid;'    -> true
2. 'DOB is valid;ST is invalid;'    -> true
3. 'DOB is valid; ST is invalid;'    -> true
4. 'DOB is valid;invalid ST;'    -> true
5. 'DOB is valid; invalid ST;'    -> true
6. 'DOB is valid; invalid STate;'    -> false

Means the exact ST should be search. It is  not free text search.

T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.

View 10 Replies View Related

Transact SQL :: Case Statement For A New Column Based On Date And Username Columns

Sep 2, 2015

Below is the SQL Query i am currently having

SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate

[Code] ....

And here is the result dataset

ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL

[Code] .....

So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.

View 13 Replies View Related

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 Replies View Related

Grouping (Case)

Nov 27, 2006

I have a table of Stores and the profit generated per mile.

Store|Miles|Profit
Store1|1|20,000
Store1|2|22,000
Store1|3|11,000
Store1|4|20,000
Store1|5|22,000
Store1|6|15,000
Store1|7|20,000
Store1|8|32,000
Store1|9|15,000
Store2|1|20,000
Store2|2|22,000
Store2|3|11,000
Store2|4|20,000
Store2|5|22,000
Store2|6|15,000
Store2|7|20,000
Store2|8|32,000
Store2|9|15,000

I want to group the stores by miles and sum the profit

Store1|Miles 1-5| 95,000
Store1|Miles 6-9| 82,000
Store2|Miles 1-5| 95,000
Store2|Miles 6-9| 82,000

Im guessing its a case stmnt. But i can only figure out how to make the colomn headings labled 1-5 and 6-9.


Thanks,
Dynasty

View 2 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?

View 7 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:




Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...






This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:



Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,


shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...


This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

Joining On And Grouping By CASE Function Column Alias (URGENT)

Apr 14, 2004

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
CASE
WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
END,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC Ross

View 5 Replies View Related

Transact SQL :: How To Use GROUPING SETS

Jul 12, 2015

I was working on what I was told was SQL 2012 and it turns out it is SQL 2005.  I wrote two procs that I need to convert to 2005.  Here is the code:

SELECT
era_provider_name AS Provider,
RIGHT([era_upi], 5) AS 'ERA Upi',
[era_fy] AS 'ERA FY',
ProcGrp,
COUNT(DISTINCT UCI) AS 'Client Count',

[Code]....

I'm not finding an efficient way to do this.  I cannot use GROUPING SETS with 2005.  Here is the code for the second proc:

SELECT
CASE
WHEN GROUPING(era_provider_name) = 1 THEN 'TOTALS'
ELSE era_provider_name
END AS era_provider_name,
CASE WHEN GROUPING(era_fy) = 1 THEN 'TOTALFY'

[Code] ...

The results as in SQL 2012 are exactly as I would like them.  I want to mimic those results in 2005.

View 6 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Sum Amount By Grouping Date

Oct 3, 2015

I have a table that have customer name and their bill amount

a) tblBilling

which shows records like below (Billing Dates are shown in Year-Month-Day format)

Invoice   Customer             BillingDate       Amount            Tax
--------------------------------------------------------------------------
1             ABC                       20015-10-2           1000.00            500.00
2             DEF                        20015-10-2           2000.00        1000.00
3             GHI                        20015-10-2           1000.00            500.00
4             JKL                         20015-10-3           5000.00          2500.00
5             MNO                     20015-10-3           1500.00            750.00
6             PQR                       20015-10-4            500.00            250.00
7             STU                        20015-10-4           1000.00           500.00
8             VWX                      20015-10-4            2500.00         1250.00

I want to perform a query that should SUM Amount and Tax Colums by date basis, so we could get the following result

 BillingDate       Amount            Tax
--------------------------------------------------------------------------
20015-10-2           4000.00            2000.00
20015-10-3           6500.00            3250.00
20015-10-4           4000.00            2000.00

View 3 Replies View Related

Transact SQL :: How To Get Sum Of Results From GROUPING SETS

Jul 8, 2015

I needed to add in the Fiscal Year (FY) to group my results by FY.  I changed my code to the following:

/*
EXEC ADAMHsp_BSS_GetNonMedicaidReportTotals
@pstrProviderName = 'FAM SER-WOOD'
*/

ALTER PROCEDURE [dbo].[ADAMHsp_BSS_GetNonMedicaidReportTotals]
@pstrProviderNameVARCHAR(100)
AS
BEGIN

[Code] ....

See my result set in the picture below.  The rows with NULL in the 'ProcGrp' column have the totals of the groupings by FY that I am looking for - that's great.  What I want to do now is have another row that contains the sums of the values from any row where 'ProcGrp' is null so that I have a totals row.

View 3 Replies View Related

Transact SQL :: Parent Child Grouping

Nov 4, 2015

I have a table as below

ItemID ParentID
6 NULL
7 NULL
8 7
11 7
12 8

Here ParentID null means it does not have the parent its the master. I want result in below format.

ItemID ParentID
6 NULL
--No parent no child
7 NULL
--No parent but has child
8 7
--7 is the parent of 8
12 8
--8 is the parent
11 7
--7 is the parent

Basically it should be in parent id then child id after that child's childID , In a recursive way.

View 5 Replies View Related

Transact SQL :: Grouping Data Within A DB Table?

Jul 9, 2015

I have a table with couple of hundred thousand records, i have added a new column to the table named MD_Group.

Now i need this column populated, so that every 10,000 row set in that table gets a numeric (incremental number) assigned starting from 1.

So the first 10,000 rows will have MD_Group = 1, next 10,000 rows will have MD_Group = 2 , next 10K MD_Group = 3 etc.

For testing purpose here is a results table with total of 11 rows where we want data grouped every 3 rows.

MD_Group
CustomerNumber
AmountBilled
1
12
15243
1
1231234
15243

[code]....

-- Create Sample Table

Declare @GroupRelation_Test TABLE (
MD_Group [varchar](20),
CustomerNumber [varchar](20) ,
AmountBilled [varchar](20) ,

[code]....

-- Test data

INSERT INTO @GroupRelation_Test
( CustomerNumber, AmountBilled,MinAmounttBilled)
SELECT'12','15243','' UNION ALL
SELECT'1231234','15243','' UNION ALL
SELECT'463','15243','' UNION ALL
SELECT'442','15243','' UNION ALL

[code]....

View 22 Replies View Related

Transact SQL :: Summing With A Function In Grouping

Jun 23, 2015

I have a table with duration values for different machine states. I 'm trying have a sum of the duration value of each state ( the duration sum , was an earlier question).

declare
@tblDurations
TABLE
(StateName
nvarchar(30),Duration
nvarchar(30))

[code]...

I want the output to have sum of the duration ( from my function ), grouped by the state.So output should be :

Breaks 00:02:03:40
Meetings 00:00:01:50
Running  15:21:07:16

I think this can be done with windows functions, but how I don't know.

View 10 Replies View Related

Transact SQL :: Sum Values By Grouping Name From Two Tables

Nov 6, 2015

I have two tbles that have ItemName and their bill amount

a) tblLunch

which shows records like below

Invoice   Item                          Amount  
--------------------------------------------
1             COFFEE                       1000.00 
2             TEA                          2000.00
3             ICE CREAM                1000.00

b) tblDinner

which shows records like below

Invoice   Item                      Amount  
------------------------------------------------------------
1             COFFEE                  1000.00 
2             TEA                        2000.00
3             PASTA         1000.00

I want to perform a query that should SUM Amount Columns by Grouping the Item from both the tables, so we could get the following result

Item                      Amount  
------------------------------
COFFEE                  2000.00 
TEA                        4000.00
ICE CREAM           1000.00
PASTA                    1000.00

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved