Group By Case Statement
May 13, 2007
Hi guys,
I am having a little diffulty displaying two columns next to each other in a case/group by statement as code shown below.
I was wondering if i could have the results displayed such that the gst_amount and total_amount are in two separate columns (as they currently are) however the results of the rows are in the same row not in separate rows (as they are currently). I dont think i can do this in the current case statement that i have (i.e: the two case statements).
Any feedback would be appreciated
SELECT distinct
PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
case when temp_111.[name] like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as GST_AMOUNT,
case when temp_111.[name] not like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as Total_Amount
FROM temp_111 INNER JOIN PERIOD
ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ
WHERE
(NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL)
AND temp_111.PERIODSEQ in ('111')
group by PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
temp_111.[name]
Here is the current result displayed:
Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000NelNULL 83470.5608000000
2006-11-01 00:00:00.000NelNULL 161408.5264874810
2006-11-01 00:00:00.000NelNULL 677568.2683000000
2006-11-01 00:00:00.000NelNULL 2645478.1215092400
2006-11-01 00:00:00.000Nel8347.0560800000 NULL
2006-11-01 00:00:00.000Nel16140.8526488160NULL
2006-11-01 00:00:00.000Nel67756.8268300000NULL
2006-11-01 00:00:00.000Nel264547.8121507070NULL
Instead I want the result to show something like this:
Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000Nel8347.0560800000 83470.5608000000
2006-11-01 00:00:00.000Nel16140.8526488160 161408.5264874810
2006-11-01 00:00:00.000Nel67756.8268300000 677568.2683000000
2006-11-01 00:00:00.000Nel264547.8121507070 2645478.1215092400
View 2 Replies
ADVERTISEMENT
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
May 20, 2004
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:
ie,
SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3
etc.
The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".
Regards,
Dave
View 5 Replies
View Related
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
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
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
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
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
May 26, 2006
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View 10 Replies
View Related
Feb 20, 2008
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows
as per data in MS Sql
my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --
2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no
3)Other_project
fields are -- other_proj_id,other_custname,po
for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20
Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222
material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output
plz send a sql query
View 8 Replies
View Related
Mar 26, 2002
I have a SQL statement below which has a case clause which works fine, until I want to group it. Is it possible to group on a clause that is defined as case?
declare @sdate datetime
set @sdate = '02/01/2002'
select distinct count(tbm.otheridnumber), instatus
= case inmate_status
when 'inactive' then 'Out of system'
else 'Active'
end,inm.df_institutionname
from tb_medication tbm
inner join inmateid inm
on tbm.inmate_cntr_id = inm.inmate_cntr_id
where
tbm.chdpedt is null
and DATEDIFF(day,chdpstdt , @sdate) > 90
group by instatus,
inm.df_institutionname
order by inm.df_institutionname
View 1 Replies
View Related
Oct 11, 2006
Code:
Select Signal_id, Test =
CASE
WHEN signal_date > dateadd(hour,-24,getdate()) THEN 'Today'
WHEN signal_date > dateadd(hour,-48,getdate()) and signal_date < dateadd(hour,-24,getdate()) THEN 'Today-1'
WHEN signal_date > dateadd(hour,-72,getdate()) and signal_date < dateadd(hour,-48,getdate()) THEN 'Today-2'
WHEN signal_date > dateadd(hour,-96,getdate()) and signal_date < dateadd(hour,-72,getdate()) THEN 'Today-3'
WHEN signal_date > dateadd(hour,-120,getdate()) and signal_date < dateadd(hour,-96,getdate()) THEN 'Today-4'
WHEN signal_date > dateadd(hour,-144,getdate()) and signal_date < dateadd(hour,-120,getdate()) THEN 'Today-5'
WHEN signal_date > dateadd(hour,-168,getdate()) and signal_date < dateadd(hour,-144,getdate()) THEN 'Today-6'
WHEN signal_date > dateadd(hour,-192,getdate()) and signal_date < dateadd(hour,-168,getdate()) THEN 'Today-7'
WHEN signal_date > dateadd(hour,-216,getdate()) and signal_date < dateadd(hour,-192,getdate()) THEN 'Today-8'
WHEN signal_date > dateadd(hour,-240,getdate()) and signal_date < dateadd(hour,-216,getdate()) THEN 'Today-9'
ELSE 'Other'
END
, Total = count(*)
From abmsignal WHERE Signal_id = 'fail test' AND Signal_date > dateadd(hour,-240,getdate())
Group by Signal_id, Test
COMPUTE AVG(SUM(Total))
That's what I would like, Daily (or rather 24 hour periods) listed, and then averaged out. I will fix the dateadds so that they are for previous 10 days rather than previous 240 hours, but first I need to get it to work as is.
View 2 Replies
View Related
Jan 21, 2004
I have the below query and I am not sure if this will return an accurate aggregate, I know I cannot just group by my alias GLG_DELEGATE_ID, is this the way to handle aggregates when you have a CASE in the SELECT statement?
SELECT CASE
WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID
END AS GLG_DELEGATE_ID
,COUNT(P.CONSULTATION_ID) ACTIVITY_AMOUNT
FROM
dbo.CONSULTATION C
GROUP BY
C.GLG_DELEGATE_ID
, C.GLG_ID
View 1 Replies
View Related
Aug 22, 2006
Hi,Can anyone please help me with SQL syntax to create a second variablebased on the value of another (both numeric)?My effort is below but I get a syntax error.SELECTcharA,CASE charAWHEN < -199 THEN 2WHEN < 31 THEN 3WHEN < 82 THEN 4WHEN < 100 THEN 5WHEN < 105 THEN 6WHEN < 111 THEN 7WHEN < 143 THEN 8WHEN < 165 THEN 9WHEN < 233 THEN 10WHEN >= 233 THEN 11END AS Bin_charAFROM XServer: Msg 170, Level 15, State 1, Line 6Line 6: Incorrect syntax near '<'.
View 3 Replies
View Related
Jul 25, 2006
It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?
I am running SQL Server 2000 and the database that I am querying is not case sensitive.
View 9 Replies
View Related
Nov 16, 2006
I am not to experienced with SQL and I guess the following is a piece of cake for most of you...
I want to get the sales amount for each shop, for each of three different products, and for the sum of the three products. The problem is that I only get for each shop for each of the product, but not the sum of the three products OR for each shop the total of the three products, but not for each product.
My query looks like this:
SELECT
Shop_name,
case
when prod in (1,2,3) then ‘milk_bread_butter’
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
else ‘none’
end as product,
sum(sales_amount) as sales
FROM
Dbo.sales
GROUP BY
Shop_name,
case
when prod in (1,2,3) then ‘milk_bread_butter’
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
else ‘none’
end
The problem is that I only get the lin 'milk_bread_butter' per shop. If I switch the order around in the case statement;
case
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
when prod in (1,2,3) then ‘milk_bread_butter’
else ‘none’
I get the sales amounts per shop for each of the products, but not for the milk_bread_butter total.
I would be very greatful for any guidance as how to solve this.
Thank you.
Best, Mal
View 3 Replies
View Related
Oct 11, 2007
I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :
Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Below is the my sql statement:
SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,
ItemRef =
Case
when RH.Quantity < 0 then Sum(RH.Quantity)
when RH.Quantity >= 0 then Sum(0)
end
FROM dbo.RHISTORY AS RH INNER JOIN
dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID
WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007')
AND (RH.TransactionCode IN (13, 14, 15, 16))
Group by dbo.DateOnly(TH.TransactionDate), RH.Item
The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day.
Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.
View 12 Replies
View Related
Jan 18, 2008
I am trying to use a case statement in one of my stored proc but I am stuck a little bit.Here is a example, something like:declare @id int set @id =1case @id When 1 then select * from contactsend case but this keeps on giving me error: Incorrect syntax near the keyword 'case'. Any help is appreciated!
View 7 Replies
View Related
Apr 22, 2008
Hi I have some question regarding the sql case statment.Can i use the case statement with the where clause.Example: SELECT FirstName, IDFROM myTablewhere case when ID= '123' then id = '123' and id='124' endorder by idBut the above code does not work.
View 9 Replies
View Related
Jun 2, 2005
Hi all,
I was wondering if there is any way in an sql statement to check whether the data your trying to get out of the DB is of a particular type, ie. Int, char etc. I was thinking about a case statement such as
<code>
CASE WHEN (MyNum <> INT) then 0 end AS MyNum
</code>
This has to be included in the sql statement cause I need this field to get other data.
Any thoughts on how to achieve this would be greatly appreciated.
If I’m in the wrong thread section please advise of best one to get help in.
View 1 Replies
View Related
Sep 17, 2005
Hi !!!i hope one of the sql specialists answer me about the best and most effeceint way to acheive what i am looking for Scenario:-------------i have a 3 tables related to each other Addresses, Groups and GroupAddressthe relation is for both addresses and groups is one to many in the GroupAddress.the behaviour in the application : user can add addresses to his address list and from the address list a user can add an address to many groups like if you have Group name "Freinds" and you add me in it and you have Football team group and you add me to it like that !!!not i have another function called "copy group"in the GroupAddress i have this data as example GroupID AddressID1 41 61 21 441 72 82 62 93 133 73 10and the group ID called "Freinds"i want to copy the group so i can have another group that has the same addresses by one click rather than collectiong them again one by one ...by the way the new copy will have a new group name ( as this is thebusiness logic so user can not have dupicate group name )so what is the best SQL statement that i need to copy the group ???i hope that clear enough!
View 2 Replies
View Related
Jan 23, 2002
I am trying determine if I can do something like the code below. I have done a left join on a table. In the select statement there are three possible values. Yes, No, or NULL. I could like to use a Case statement to determine if there is Null. If so, then output N/A in place of the Null. So then my possible valus are Yes, No, and N/A.
Any clues?
Thanks,
John
SELECT TOP 100
OfferDressRoomYN.yesno as OfferDressRoom
= CASE
WHEN offerDressRoomYN.yesno IS NULL THEN 'N/A'
END,
FROM dataquestionnaire dq
LEFT OUTER JOIN yesno OfferDressRoomYN ON dq.c3_1 = OfferDressRoomYN.yesnoid
View 2 Replies
View Related
Aug 27, 2001
Hi
Can anybody tell me how to execute store procedure in the case statement.
Thanks
View 1 Replies
View Related
Sep 23, 2002
How do l use the case statement to cater for these updates.
BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = ('AB')
WHERE AB_CLIENT = 1
COMMIT
GO
BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + ' | SB'
WHERE SB_CLIENT = 1
COMMIT
GO
BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'SB'
WHERE SB_CLIENT = 1 And entity is null
COMMIT
go
BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + (' | CI')
WHERE CI_CLIENT = 1
COMMIT
GO
BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'CI'
WHERE CI_CLIENT = 1 And entity is null
COMMIT
GO
BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + (' | GEMS')
WHERE GEMS_CLIENT = 1
COMMIT
GO
BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'GEMS'
WHERE GEMS_CLIENT = 1 And entity is null
COMMIT
GO
View 1 Replies
View Related
Feb 14, 2006
In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row.
I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated.
Please help
Thanks
select
isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname
, case ProductID
WHEN 22 then count(S.Product)
Else 0
END AS Builders
, case ProductID
WHEN 23 then count(S.Product)
Else 0
END AS Associates
, case ProductID
WHEN 24 then count(S.Product)
Else 0
END AS Affiliates
FROM vwpersons p with (nolock)
join vwSubscriptions S with (nolock)
on S.RecipientID = P.ID
where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
group by P.Lastname, S.ProductID
having count(P.LastName)>=1
order by 1
Result set :
Firstname Builders AssociatesAffiliates
Abarca 010
Abascal200
Abelar 100
Abeyta100
Abeyta010
Abreu 100
Abreu 010
Acevedo100
Acevedo050
View 2 Replies
View Related
Aug 20, 2007
I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement and only get one calltype.
I want to do a CASE statement to get all average scores for all calltypes.
Select Site, avg(totalscore) as [Avg Score]
FROM DB
WHERE calltype = 'A'
GROUP BY Site
Results
Site Avg Score (for A)
1 85
2 75.5
3 85.33
SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore
ELSE 0 END) AS [Avg Score For A]
FROM DB
GROUP BY Site
Results
Site Avg Score For A
1 i get 8.5
2 i get 37.75
3 i get 36.57
Why am I getting a difference?
Any help is greatly appreciated - thank you
View 1 Replies
View Related
Aug 18, 2004
Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y)
Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help?
SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage
FROM [Company/Year]
GROUP BY CCode, BS_TOTAL_ASSETS
Thanks
View 3 Replies
View Related
Apr 11, 2008
i ahve one fucniton:
create function fntotalcountcustclas
( @campaign varchar(50), @startdate datetime, @enddate datetime)
RETURNS TABLE
AS
RETURN
( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount
from
(
select
vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas
from vwcustnmbr vc
join vwitemnbmr vi
on vi.sopnumbe=vc.sopnumbe
Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)
group by vi.itemnmbr,vc.custclas, vc.custclasdescription
) as t
)
when i m executing it:
select * from fntotalcountcustclas('copd','1/1/2008','4/11/2008')
order by totalcount desc
i m getting results like:
itemnmbr,custclas,custclasdescription,totalcount
------------------------------------------------
06-5841 STANDARD Standard(web) 31
06-5840 STANDARD Standard(web) 30
kr-014 STANDARD Standard(web) 72
06-5841 INDPATIENT Patient 12
06-5840 INDPATIENT Patient 9
06-5845 INDPATIENT Patient 6
06-5841 PROGRAM Program 6
06-5841 INST-HOSPITAL Hospital 11
...................
Basically, i ahve to use one condition to get corrrect output related to inputs:
like - i have to input @category varchar(50), @category_value varchar(50)
and if category = 'campaign' then category_value = ''
then output should be itemnmbr sum(totalcount) [whatever should be custclas or custclasdesscription]
itemnmbr sumcount
-----------------
06-5840 52
06-541 101
06-452 26
kr-045 252
and if categroy = 'item' then category_value = any itemnmbrs(06-5840,06-5845,06-5841 etc..)
then output should be
itemnmbr custclas custclasdescription totalcount
-----------------------------------------------------
06-5840 STANDARD Standard(web) 31
06-5840 INDPATIENT Patient 9
06-5840 PROGRAM Program 6
06-5840 INS-HOSPITAL Hospital 17
like that..
can anyone help me to write case statement.
thanks a lot!!
create function fntotalcountcustclas
( @campaign varchar(50), @startdate datetime, @enddate datetime,
@category varchar(50), @category_value varchar(50))
RETURNS TABLE
AS
RETURN
( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount,
case when category
from
(
select
vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas
from vwcustnmbr vc
join vwitemnbmr vi
on vi.sopnumbe=vc.sopnumbe
Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)
group by vi.itemnmbr,vc.custclas, vc.custclasdescription
) as t
)
View 10 Replies
View Related
May 5, 2008
Im running the following sql statement but I dont see the
expected output. There are few differences between acc & cl1, mcc & cl2 , ncr & cl3 but I dont see either 'ONE' or 'TWO' or 'THREE'.
There is even a case where cl3 is null but the sql is not filling in either one or two or three. Query simply returns id & rest as null values.
SELECT P1.id,
CASE
WHEN p1.acc!= p1.cl1 then 'ONE'
WHEN p1.mcc!= p1.cl2 then 'TWO'
when p1.ncr!= p1.cl3 then 'THREE'
Else NULL END
As NOnMatchingColumn
from
(select id, acc, cl1,mcc,cl2,ncr,cl3 from dbo.ml)P1
View 5 Replies
View Related
Jun 6, 2008
I'm not sure if I am doing this the right way. In my table I have project ids. Each project id has several activities associated with it. Each project has a starting balance. Each activity posts an expense to the total balance of the project. If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". If there isn't enough money, I want to loop through the activities, check to see if there is enough of a balance to "post" the first one, If there is, then I want to re-adjust the balance and check the second activity. I want to scroll through each project/activity to see what can be "posted". Here is what I have so far, but I cannot work out how to change the total balance amount. Hopefully what I am trying to do makes sense!
declare @testId nchar(6)
declare @RowNum int
declare @newBalance int
select top 1 @testId=projID from #ProjIds
set @RowNum = 0
WHILE @RowNum <= (Select Max(temp_id) from #ProjIds)
BEGIN
set @RowNum = @RowNum + 1
set @newBalance = (select top 1 Bal_2300
from #RevRecData
where @testId=projId
order by projID, activity)
select projId, activity, postCr, Bal_2300,
'New_Status' = Case when (postCr <= Bal_2300) then 'Can Clear' else 'Still Check' END,
'New_Balance' = Case when (postCr <= @newBalance) then (@newBalance - postCr) else @newBalance End
from #RevRecData
where @testId=projId
order by projID, activity
select top 1 @testId=projId from #ProjIds
where projId > @testID
END
View 12 Replies
View Related
Jun 7, 2008
I've these following table
SEL
Date_Taken | Main_ID | Time | Daily_Rainfall
---------------------------------------------------
...
...
...
4/3/2005 | 202 | 1015 | 2
4/3/2005 | 202 | 1045 | 2
4/3/2005 | 202 | 1215 | 7
4/3/2005 | 203 | 715 | 2
4/3/2005 | 203 | 1345 | 2
4/3/2005 | 203 | 1530 | 7
...
...
...
5/29/2005 | 203 | 1100 | 56
5/29/2005 | 203 | 1130 | 156
5/29/2005 | 203 | 1145 | 256
...
...
...
Station_Info
State | Main_ID
--------------------
SEL | 202
SEL | 203
SEL | 204
SEL | 205
SEL | 209
Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf
------------------------------------------------------------------------------
202| 4/30/2005 | 525.8 | 683.11 | 356.33 | 754.33
203| 4/30/2005 | 435.5 | 400.9 | 290.34 | 234.34
204| 4/30/2005 | 265.53 | 453.21 | 543.66 | 753.24
205| 4/30/2005 | 251.38 | 754.33 | 478.34 | 785.22
209| 4/30/2005 | 259.5 | 356.34 | 894.33 | 354.78
202| 5/30/2005 | 565.8 | 383.11 | 756.33 | 254.33
203| 5/30/2005 | 485.5 | 444.9 | 744.34 | 755.34
This query only display Max(Daily_Rainfall) per day from SEL
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL,
CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3
THEN t3.[3MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=6
THEN t3.[6MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=9
THEN t3.[9MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=12
THEN t3.[12MthCumRf]
END AS CumRf
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND
t1.RowNo=1 INNER JOIN dbo.LT t3 ON t3.STATION_ID =t2.STATION_ID
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
ANDt1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL'
ORDER BY MAIN_ID,DATE_TAKEN,TIME
In my query, i still can query if last3month using CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3 THEN t3.[3MthCumRf]
How to adjust my CASE statement
if Main_ID=203, Date_Taken=5/5/2005,
i should pickup following rows
Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf
-------------------------------------------------------------------
203| 5/30/2005 | 485.5 | 444.9 | 744.34 | 755.34
if Main_ID=203, Date_Taken=4/5/2005,
i should pickup following rows
Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf
203| 4/30/2005 | 435.5 | 400.9 | 290.34 | 234.34
Need someone help... plz
View 20 Replies
View Related
Aug 22, 2013
In my temptable I'm still trying to replace NULL values with 0 (zero).I tried ISNULL and that didn't work.I tried ISNULL and it didn't work. I was told by a senior developer I work with that I should do something like a case when. whatever that is.
INSERT INTO #TEMPLEASEDOLLARSSUBMITTED (SalesName, UserID, LeaseDollarsSubmitted)
(
SELECT (FirstName + '' + LastName)AS [Sales Team Member], Usr.UserID,
SUM (ISNULL(MerAppEqL.MonthlyPayment,0))AS [Leases $ Submitted]
FROM MerchantAppEquipmentLease MerAppEqL WITH(NOLOCK)
INNER JOIN MerchantAppEquipment MerAppEqt ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber
[code]...
Or should I have put the ISNULL when I updated #TEMPDASHBOARD because that is where the NULL values show up. They don't show up in #TEMPLEASEDOLLARSSUBMITTED table?
View 4 Replies
View Related
Jan 3, 2006
Hello,
I have a sql statement that uses case statement to calculate a variable, I would like to use that variable in the same sql statement to calculate a different variable.
In my code Wed_Var is calculated, but since I dont know a way to use the Wed_Var in Wed_Adj, I copy the whole formula again.
Any help is appreciated.
Thanks
SELECT TOP 100 PERCENT dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal,
Wed_Var = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon
+ dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
/ dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN .75 WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
/ dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN 1.25 ELSE (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
/ dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date END,
Wed_Adj = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed
/ 4) WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
/ dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN .75 * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4)
WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
/ dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN 1.25 * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4)
ELSE (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * ((dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
/ dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date) END,
Week_To_Date = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed
/ 4)
* dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
/ dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed
/ 4) * .75 WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
/ dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed
/ 4) * 1.25 ELSE dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4)
* ((dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
/ dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date) END
FROM dbo.VIEW_ManPlan_Dock_Prev_4WK INNER JOIN
dbo.VIEW_ManPlan_Dock_Prev_Current ON
dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Current.Terminal INNER JOIN
dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday ON
dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Terminal
ORDER BY dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal
View 2 Replies
View Related
