Here's my query - Since I'm grouping by the partnerid
select distinct make, count(leadid) as TotalCount, case
when PartnerID = 1 then 'retail'
else 'wholesale' end
as disposition
from leads_sent (nolock)where datein between '2007-09-01' and '2007-09-30'
group by make, partnerid
order by make
Here's a sample my current output -
Acura
1 wholesale Acura
2 wholesale Acura
4 wholesale Acura
5 wholesale Acura
21 wholesale Acura
34 wholesale Acura
37 wholesale Acura
56 wholesale Acura
57 wholesale Acura
72 wholesale Acura
510 retail Audi
1 wholesale Audi
3 wholesale Audi
7 wholesale Audi
12 wholesale Audi
16 wholesale Audi
18 wholesale Audi
23 wholesale
I have to count the number of Ideas and Markets here.
CASE WHEN Team IN ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team') THEN 'Ideas' ELSE 'Markets' END
I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.
select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages
Basically now, it returns the "OutBoundMessages" column
I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )
I determine if the column is "checked" or "unChecked" by a column in tblMessage
SELECT COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors], COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors] FROM content_hits_tbl WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
=======================
How do I add up both COUNT/CASE columns? Would it be: SUM([New Visitors] + [Returning Visitors]) AS Total
I tried this and it doesn't work. I get invalid column names error for both.
I have even tried: SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total
You would think that there would be some gui functionality in VS08 that would do this...
I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.
Right now I am achieving this by the following case statement
SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class' WHEN Stcount>=10 and StCount<=50THEN 'Medium Class' WHEN Stcount>50 THEN 'Big Class' END FROM( select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl
I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!
For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:
Code: CASE WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00] WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00] ... WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]
Is there an easier way to do this, maybe with a loop?
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
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?
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
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.
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.
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
Hi Everyone, I am trying to do a query where I need to use as little C# as possible to build my gridview. Basically I have a column called statusID. There are about 15 options for this column but I only want to count certain ones. I want to count when statusID = 3 and output that into a column called "fullUnitsUsed" but when the value is 4 or > 13 I want it to count and put the result into a column called "halfUnitsUsed". I also want it to count based on the month. To accomplish this I have used CASE and GROUP BY. This has worked to some extent. Currently if I COUNT for one month I get the correct number of fullUnitsUsed and halfUnitsUsed used for January. Unfortunately the query returns 2 records for the month. The first one has a value for fullUnitsUsed and halfUnitsUsed is NULL, the second record has fullUnitsUsed as NULL and halfUnitsUsed has the correct value. I was hoping to output one record where both fullUnitsUsed and halfUnitsUsed have data. My other problem is that if I test for the entire year (which is what this query is supposed to do) there are 5 records returned for each month, 3 of the records have fullUnitsUsed and halfUnitsUsed both as NULL and for the other 2, one has fullUnitsUsed with data and the other has halfUnitsUsed with data and the other column in both records is NULL. The values for fullUnitsUsed and halfUnitsUsed are counted for the entire year as well, which I only want it to count based on each month. Below is my query, any suggestions about how to approach this will be greatly appreciated. If any clarification is needed please let me know. Again if I could get this to work completely with SQL and not need to use any more C# than I have to it would be preferable. SELECT People.lastName + ', ' + People.firstName AS fullName, Property.Name, NYSDDSORegion.Description, OpenDays.[month], OpenDays.maxOpenDays,CASE Attend.statusID WHEN 3 THEN COUNT(Attend.statusID) END AS fullUnitsUsed,CASE Attend.statusID WHEN 4 THEN COUNT(Attend.statusID) WHEN 14 THEN COUNT(Attend.statusID) WHEN 15 THEN COUNT(Attend.statusID) WHEN 16 THEN COUNT(Attend.statusID) WHEN 17 THEN COUNT(Attend.statusID) WHEN 18 THEN COUNT(Attend.statusID) WHEN 19 THEN COUNT(Attend.statusID) WHEN 20 THEN COUNT(Attend.statusID) END AS halfUnitsUsed FROM Attend INNER JOIN People ON Attend.personID = People.personID INNER JOIN Property ON Attend.propertyID = Property.propertyID INNER JOIN NYSDDSORegion ON Property.RegionID = NYSDDSORegion.RegionID CROSS JOIN OpenDays WHERE (Attend.attendDate BETWEEN '1/1/2007' AND '12/31/2007') GROUP BY Property.Name, People.lastName, NYSDDSORegion.Description, People.firstName, OpenDays.monthID, OpenDays.[month], OpenDays.maxOpenDays, Attend.statusID ORDER BY Property.Name, fullName, NYSDDSORegion.Description
It doesn't seem possibly, but maybe? Is there a way to have an expression be used, but also benefit from using distinct on a column?
I'm looking for something like: sum(case when dtEntered > '1-1-2006' then 1 else 0 end) but also encorporating somehow a distinct count on UserName. So a username showing twice would only count once, and this would only be counted if the record's dtEntered date was greater than Jan 1, 2006.
The reason I'm writing the statement that way is because there are 5 columns which aggregate data by different time periods.
If it's not possible, I will just end up joining to the table multiple times, putting the date filter in the where clause.
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!
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.
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.
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!
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
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
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
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
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 )
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
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
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
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?
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
