I have a query where I need to see in 2 seperate columns, the Actual date of deliveries and collections, and the Invoice Week number of the same. The problem is that in our ERP system delivery date (di.ddate) is a date field, and the collection date is nvarchar. This is my query and sample results below, as you can see I'm getting nulls for the collection lines. How can I do this efficiently
select di.dticket, di.item,
case di.item when 'DEL' then di.ddate when 'COL' then CONVERT(datetime,di.text2,103) end ActDate,
Case when di.item = 'DEL' then
(select YearWeek from MCSReports.RptRevenueGuideWeekLookup
where di.ddate between FromDate and ToDate) end InvWeek
from deltickitem di
where item in ('DEL','COL')
Sample results below:
dticket item ActDate InvWeek
-------------------- -------------------------------------------------- ----------------------- --------
01-000002 DEL 2011-07-05 15:53:00.000 2011-27
01-000002 COL NULL NULL
01-000004 DEL 2011-07-05 16:04:00.000 2011-27
01-000004 COL NULL NULL
01-000005 DEL 2011-07-05 16:08:00.000 2011-27
01-000005 COL NULL NULL
01-000006 DEL 2011-07-05 16:19:00.000 2011-27
01-000006 COL NULL NULL
01-000007 DEL 2011-07-05 16:24:00.000 2011-27
01-000007 COL NULL NULL
01-000008 DEL 2011-07-05 16:29:00.000 2011-27
01-000021 DEL 2011-07-05 08:48:00.000 2011-27
I have a case statement which I need to alter in my query. The field [sr.spec_value] contains either 1 or 0. When it is 1 I want the case expression to return 'YES' when 0 then 'NO'. I've tried a couple of options from the forum but am not getting it right. Here is the original case statement from my query:
case when sr.spec_value = 1 then 'YES' when 0 then 'NO' end [Fixed].
I have data in a SSIS package that I need to alter to something else.
The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".
The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.
I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.
Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?
I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1 WHEN PracticeCode IN (.......) THEN 1 WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1 ELSE 0 END
I need to put these conditions in config table and generate dynamic sql. What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.
I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1 WHEN PracticeCode IN (.......) THEN 1 WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1 ELSE 0 END
I need to put these conditions in config table and generate dynamic sql.
What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.
I am trying to use a date comparison in a statement using the year statement as well. Here is what I have:
Case [LastHireDate] When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12' When Month([LastHireDate]) = '1' then '12' When Month([LastHireDate]) = '2' then '11' When Month([LastHireDate]) = '3' then '10' When Month([LastHireDate]) = '4' then '9'
[Code] ....
When I am looking at it [LastHireDate] is showing that red line underneath. The < symbol has a red line and @EndYearlyDate has a red line. I can not seem to get them to clear and am, wondering what I am missing. When I execute the error comes up that it does not like the < sign in there.
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.
I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.
say for eg: in the tables I have emp name user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days Total days 53 Days
For this :
I calculated datediff + 1 and got sub jobs days BUT
say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec
so on paper I calulated as :
1-31 Dec 2014 PhaniMarketing NULL (Do not fall in Req Dt) PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec) PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days) Total days 31
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
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?
Is there a method for converting the first character of a account name to uppercase and the the remaining characters to lower case? I've used the substring procedure but for a name like 'MY NEW COMPANY', how could I convert it to 'My New Company' ? Thanks, Terry
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.
Hello, we've an Oracle transition in the pipeline and want to convertall our database objects to upper case. Any one got a script ortechnique (other than manual) to do it?Many thanks, Kevin.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :)
Just a couple of typical examples of how the data should appear ~
------------------------------------------------------------------ Character Set = 2, cp850 Code Page 850 (Multilingual) character set. Sort Order = 50, bin_cp850 Binary ordering, for use with Code Page 850 (cp850). Characters, in Order
SELECT h1.ftraccode, CASE WHEN FTRAADDRED='A' then h1.ftrascode end as 'From Sec', CASE WHEN FTRAADDRED='r' then h1.ftrascode end as 'To Sec', case when ftraaddred ='A' then h1.ftradesc end as 'From Description', case when ftraaddred ='r' then h1.ftradesc end as 'to Description' from bHISfile h1 where h1.ftradesc like 'sw%' order by 1 ---------------------------------------------------------------- clintcode |from_sec | to_sec| from_desc | to_desc --------------------------------------------------------------- ABADJ16421 |NULL | MMTEI |NULL |SWITCH TO OAPIF ABADJ16421 |OAPIF | NULL |SWITCH FROM MMTEI |NULL
2(row)
Expected output like this
---------------------------------------------------------------- clintcode |from_sec | to_sec| from_desc | to_desc --------------------------------------------------------------- ABADJ16421 |OAPIF | MMTEI |SWITCH FROM MMTEI |SWITCH TO OAPIF
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, I have julian date in the format of 2003182 and I need to convert into our regular calendar date. Anybody have any SQL statement for this. Thanks, Ravi
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
The dB dates are store in db as 20140303 when I bring this to my application to pick a date, the apps shows the date as 20140303 how can I change this to show in the format 03/03/2014 in my application?
I am trying to convert a decimal date value to a date value that I can use in formulas but am having a lot of trouble.My date value currently shows in decimal format YYYYMMDD. I want to convert this to a date so I can then find the number of days between two dates.I have tried convert (datetime, convert(varchar(8),left(qhstdt,8))) with qhstdt as my decimal date field but I receive the error message below:
Error: SQL0204 - CONVERT in *LIBL type *N not found.
I have also tried converting it using (year(QHSTDT)*10000+100*month(QHSTDT)+ day(QHSTDT))) but when I convert the dates using this formula, I can get an incorrect number of days when I try to subtract one from the other.What formula can I use to convert my YYYYMMDD field to a format that will allow me to compare number of days between two dates?
WHEN 'DAYS' THEN DATEDIFF(dd, CHECKOUT_TS, GETDATE())
ELSE DELIVER_ID END
FROM REQUEST
If @sortcolumn has 'DATE' or 'DAYS' it runs fine....any other value, I get an exception "Conversion failed when converting datetime from character string". All columns except CHECKOUT_TS are of type varchar.
I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).What I want is to do the following:Say you have these records:person | date-time-------+---------------------------jim | 06/02/2004 00:05:52jim | 06/02/2004 05:06:21jim | 06/02/2004 05:46:21jim | 06/15/2004 11:26:21jim | 06/15/2004 11:35:21dave | 06/04/2004 09:35:21dave | 06/04/2004 11:05:21dave | 06/06/2004 10:34:21dave | 06/08/2004 11:37:21I'd like the results to count how many days and returnperson | days-------+-------jim | 2dave | 3How would I do this?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]
A table has a column containing values "Y", "N", and some blanks.
I'm trying to write a single SELECT statement applied for that column that displays the word "Yes" instead of "Y" (in the table itself) and the word "No" instead of "N".
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.
