Hello i wrote this case statement to choose the password for a certain user but seems not to work. I have to get custom1 from another table.
select cast(custom1 as varchar(5)) from dbo.newhiresraw where id =userid) when custom1 'C%' then password = abcd4321 when not 'C%' then password = [postalcode] end [postalcode].
Not sure if custom1 = C then password =abcd4321
If custom1 not = C then it will be a zipcode.
not sure on the syntax any suggestions
I use the following statement:
SELECT OH.OHONR, CASE WHEN LV.LVTYP='VIL' THEN LV.LVT30 ELSE NULL END AS LE, CASE WHEN LV.LVTYP='SÄL' THEN LV.LVT30 ELSE NULL END AS LS INNER JOIN LV ON OHONR=LVONR
The result gives one row for each hit on EITHER of the CASE statements. (Two rows if both case stantements are true)
OHONR LE
OHONR LV
I want to have one row with the two fields after each other.
OHONR LE LV
I've also tried to use ' ' instead of NULL
How can I change the statement to achieve this!
/matss
Hi,
I require some help with a SP. I have written the following CASE
block but it is giving me an syntax error with Case statement.
Here @loc_id, @id_val and @inid are input parameters to the SP.
Basically what I am doing is I am selecting the column from table based on
the input parameter @loc_id I want to use Case and avoid IF statements as Case will reduce the execution time.
If any of you have faced such problem and have a sloution then do let me know.
-- SP section starts
CASE @loc_id
WHEN 1 THEN select @id_val = val1 from id_val where name = @inid
WHEN 2 THEN select @id_val = val2 from id_val where name = @inid
WHEN 3 THEN select @id_val = val3 from id_val where name = @inid
WHEN 4 THEN select @id_val = val4 from id_val where name = @inid
WHEN 5 THEN select @id_val = val5 from id_val where name = @inid
WHEN 6 THEN select @id_val = val6 from id_val where name = @inid
WHEN 7 THEN select @id_val = val7 from id_val where name = @inid
WHEN 8 THEN select @id_val = val8 from id_val where name = @inid
WHEN 9 THEN select @id_val = val9 from id_val where name = @inid
END
-- SP section ends
Thanks & Regards
____________________
Mukund Joshi
J.Mukund@zensar.com
Can someone tell me what is wrong with my syntax? I am getting several errors:
Incorrect syntax near keyword 'CASE'
Incorrect syntax near keyword 'WHEN'
Incorrect syntax near '@Tablename'
CREATE PROCEDURE al_readcampsignup
@User_ID int,
@Pagenumber smallint,
@Tablename varchar(10)
AS
CASE @Mypagenumber WHEN 1 THEN SELECT Blah FROM tempcampsignup WHERE Camp_ID = @User_ID WHEN 2 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID WHEN 3 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID END
Thanks
Im looking to strip all non numeric values out of a string at the SQL level in order to compare it to similar user entered data.
Something like
SELECT REReplace(PhoneNumber,"[^0-9]","","ALL") AS NewPN
FROM Numbers
WHERE NewPN = 'UserEnteredNumber'
Hi,
I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..
I have my SPROC configured as below, can anyone help me out as to why its not working?
btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)
IF @genderID > 2
..
ELSE
........WHERE genderID = @genderID
thanks very much once again!
mike123
CREATE PROCEDURE [dbo].[select_123]
(
@genderID tinyint
)
AS SET NOCOUNT ON
SELECT
CASE @genderID
WHEN 1 THEN
SELECT TOP 40 *
FROM tbl
WHERE .......
WHEN 2 THEN
SELECT TOP 40 *
FROM tbl
WHERE .......
ELSE
SELECT TOP 40 *
FROM tbl
WHERE .......
END
I am attempting to mask a user's SS# based on Users.DisplaySSN. I have gone over the code time and time again and can't find where the error is.
Error I'm getting:
Incorrect syntax near the keyword 'AS'.
Here's where the error lies:
CASE
(SELECT DisplaySSN FROM Users WHERE Users.UserID = @UserID)
WHEN False THEN
'xxx-xx-xxxx' + right(SocialSecurityNumber,0) AS SocialSecurityNumber
ELSE
vwEmp.SocialSecurityNumber
END
Hello all,I belive, my problem is probably very easy to solve, but still, Icannot find solution:declare @iintdeclare @zintcreate table bubusilala ([bubu] [int] NOT NULL ,[gogo] [int] NOT NULL ,[lala] [varchar] (3) NOT NULL )insert into bubusilala (bubu,gogo,lala) values (1,2,'ala')insert into bubusilala (bubu,gogo,lala) values (10,20,'aca')insert into bubusilala (bubu,gogo,lala) values (100,200,'bbb')insert into bubusilala (bubu,gogo,lala) values (11,21,'ccc')insert into bubusilala (bubu,gogo,lala) values (12,22,'abc')insert into bubusilala (bubu,gogo,lala) values (13,23,'cbd')set @i = 10set @z = 2select * from bubusilalawhere bubu in (case when @i > @z then (1,2)when @i < @z then (10,13) end)and gogo like '%a%'I get error, that statement is wrong in case near ','.I supose, it is not possible, to get from case a group of values.But why then, this works:select * from bubusilalawhere bubu in (case when @i > @z then (1)when @i < @z then (select gogo from bubusilala) end)and gogo like '%a%'This data are totaly simplified.agrh ... any ideas??Thank You in advance,Mateusz
View 3 Replies View RelatedHello experts!
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime)
>= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR
datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
Any help is appreciated.
I'm getting an error as "Incorrect syntax near the keyword 'CASE' ". When trying to run this query.
CREATE PROCEDURE dbo.SP_CDB_EA2
@RiskRef varchar(100)
--drop table #Tmp
AS
BEGIN
[code]....
I cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE
ses.BK_MS_SESSION <= '2015-03'
AND vis.CAT_DRAW_STATUS =
(CASE ses.BK_MS_SESSION
WHEN '2015-03' THEN vis.CAT_DRAW_STATUS
ELSE
CASE stat.BK_MS_VISIT_STATUS
WHEN 'T' THEN 'X'
ELSE vis.CAT_DRAW_STATUS
END
END
) IN ('D','R')
Hi everyone,
I am a newbie to SSIS and looking for answer to a simple search/replace style question.
I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.
A bit like a case statement:
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S1' THEN 'Other sale items'
ELSE 'Not for sale'
END,
The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.
Thanks for all your help,
regards,
Chris
What will be the best way to write this code? I have the following code:
BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal
[Code] ...
then if Signal Sent.... do the following query:
Select * from package.....
if signal not sent don't do anything
How to evaluate the first query with the 2 options and based on that options write the next query.
Hi guys,
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
thanks for any assistance you can provide.
regards,
Chris
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies View RelatedHello:
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Any suggestions?
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies View RelatedI am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
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 RelatedWhy does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
View 2 Replies View RelatedI 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
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
View 2 Replies View RelatedThis is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
I just can't get the case to work for me in this view.
CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusThe error I get is:
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
ALTER VIEW [dbo].[AffiliationPayments]ASSELECT dbo.Affiliations.AffiliationType, dbo.Affiliations.AffiliationDescription, dbo.Affiliations.AffiliationEnd, dbo.Payments.PaymentDate, dbo.Payments.AmountPaid, dbo.Affiliations.Client_ID,CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusFROM dbo.Affiliations LEFT OUTER JOIN dbo.Payments ON dbo.Affiliations.Client_ID = dbo.Payments.Client_ID
I have a deadline that is set ahead of time and will not change. I need to insert this deadline for all new users, so I want to set in the DB and I have tried to insert the deadline into the db using a case(below)
Error -2147217900
Error -2147217900
Incorrect syntax near the keyword 'CASE'.
ALTER PROCEDURE prcStartWeeks @UserID VARCHAR(50) ASSET NOCOUNT ONDECLARE @Result INTDECLARE @ID INTDECLARE @Weeks INTDECLARE @DeadLine VARCHAR(8)SET @ID = 0SET @Weeks = 1SELECT @ID = ISNULL((SELECT peID from tblUserInfo where UserID = @UserID),0)IF @ID = 0 BEGIN SELECT @Result = -1 ENDELSE BEGIN WHILE @Weeks < 18 BEGIN CASE @Weeks WHEN '1' THEN @DeadLine = '5/9/2006' WHEN '2' THEN @DeadLine = '9/15/2006' WHEN '3' THEN @DeadLine = '9/22/2006' WHEN '4' THEN @DeadLine = '9/29/2006' WHEN '5' THEN @DeadLine = '10/6/2006' WHEN '6' THEN @DeadLine = '10/13/2006' WHEN '7' THEN @DeadLine = '10/20/2006' WHEN '8' THEN @DeadLine = '10/27/2006' WHEN '9' THEN @DeadLine = '11/3/2006' WHEN '10' THEN @DeadLine = '11/10/2006' WHEN '11' THEN @DeadLine = '11/17/2006' WHEN '12' THEN @DeadLine = '11/21/2006' WHEN '13' THEN @DeadLine = '11/28/2006' WHEN '14' THEN @DeadLine = '12/5/2006' WHEN '15' THEN @DeadLine = '12/12/2006' WHEN '16' THEN @DeadLine = '12/19/2006' WHEN '17' THEN @DeadLine = '12/28/2006' END INSERT INTO tblUserWeekly(PEID,WeekID,Points,DeadLine) VALUES(@ID,@Weeks,0,@DeadLine) SET @Weeks = @Weeks + 1 END SELECT @Result = 0 END
I want to use case in my date range to get last weeks range. I have a similar proc that uses variables, but my application UI will not allow this in production.
Error incorrect syntax near '=' on line 15
-- begin
select
Company
,Carrier
,Client
,DatePaid
,DateBilled
,cast(PremiumReceived as money)
,cast(PolicyAmount as money)
from view_billing
where
cast(datebilled as datetime) between
(cast(datebilled as datetime) =
case
when datepart(dw,getdate()) = 2 then getdate()-8
when datepart(dw,getdate()) = 3 then getdate()-9
when datepart(dw,getdate()) = 4 then getdate()-10
when datepart(dw,getdate()) = 5 then getdate()-11
when datepart(dw,getdate()) = 6 then getdate()-12
when datepart(dw,getdate()) = 7 then getdate()-13
end)
and
cast(datebilled as datetime) =
case
when datepart(dw,getdate()) = 2 then getdate()-1
when datepart(dw,getdate()) = 3 then getdate()-2
when datepart(dw,getdate()) = 4 then getdate()-3
when datepart(dw,getdate()) = 5 then getdate()-4
when datepart(dw,getdate()) = 6 then getdate()-5
when datepart(dw,getdate()) = 7 then getdate()-6
end
and and company like '%Comp%'
-- end