Select CASE - Add Up Amounts Based On Transaction Type

Dec 18, 2014

Getting Incorrect Syntax near the keyword 'and'

This table returns multiple records for an Invoice.

Based on the transactiontype_desc the Amount_Paid_DC is a different value. Trying to add up the amounts based on the transactiontype.

select DebtorNumber, InvoiceNumber, Sum(Amount_Invoiced_DC) AS InvAmt,
case transactiontype_desc when 'Sales Invoice' then sum(Amount_Paid_DC) else 0 end as AmtPaid,
case transactiontype_desc when 'Discount/Surcharge' and Amount_Paid_DC < 0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt
FROM BI50_BankTransactions_AR_InvcDt_H
group by debtornumber, Invoicenumber

View 6 Replies


ADVERTISEMENT

Select Query Results In Multiple Columns Based On Type From Another Table

Apr 6, 2008

Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:

Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3


Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road

I'd like the results to be like this:

Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30

Anyone? Thanks in advance!

View 3 Replies View Related

Case Statement Based On Criteria

Sep 23, 2013

I have to produce a report, but not sure of the best way to get the required results

Aim - to count how many [FDMSAccountNo] there are per given [Month_end_date], and then do a case when on the[Retail_amount]

E.g.

10 Fdmsaccountno in Jan

Those 10 Fdmsaccountno vary in [Retail_amount]

I have 5 fdmsaccountno which are between %0 & £5
2fdmaccounno beterrn £6& £10
3 fdmsaccountno £10>

My query is

SELECT [FDMSAccountNo]
,[Month_end_date]
,[Retail_amount]
FROM [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] = '32r'
and Month_end_date between '2013-01-01' and '2013-12-01'

View 5 Replies View Related

Why Can't I Use Columns Based On Case Down In My Where Clause

Sep 13, 2007



I am writing a fairly simple sql, and I would like to write something like




Code Snippet
select

firstname as firstname,
case

when firstname = 'Peter' then 'yes'
else

'no'
end as whatever

from

MyTable
where

whatever = 'yes'



And this should then select out the rows where column number 2 is 'yes'.

It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.

But why?


View 4 Replies View Related

T-SQL (SS2K8) :: Case For Field Type

Jun 7, 2014

Is there an easy way to send back to my C# program the pascal case of a field type? If you get the parameters or columns for a table or procedure the type is in all lowercase (varchar instead of VarChar). But in C#, it is in an enum (SqlDbType) that has the variables as mixed case so you can't compare them and just pass the type. You need to figure out what the case should be - varchar should be VarChar and bigint should be BigInt.

View 1 Replies View Related

Dynamic CASE Statement Based On List Of Dates

Oct 5, 2007

I have the following table of data.  I need to take a date from a large table and do the following case:CASEWhen date < date(0)     Then '0'When date between date(0) and date(1)      Then '1'When date between date(1) and date(2)     Then '2'When date >= date(3)      Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense.  Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date.  Any ideas how to manipulate these dates into a case statement?  Don't worry about the second table below.  I just wanted you to see why I need to return an int from the Case function.thanksMilton



Dates Table

Date

4/1/2003

1/1/2006

4/2/2007

Fee Table



Date
Period
Class
Fee

1
Daily
True
329

1
Half Day
True
178

1
OT
True
49

1
Hourly
True
41

1
Daily
False
156

1
Half Day
False
86

1
OT
False
27

1
Hourly
False
19

2
Daily
True
355

2
Half Day
True
192

2
OT
True
50

2
Hourly
True
44

2
Daily
False
171

2
Half Day
False
92

2
OT
False
28

2
Hourly
False
21

3
Daily
True
364

3
Half Day
True
197

3
OT
True
51

3
Hourly
True
45

3
Daily
False
175

3
Half Day
False
94

3
OT
False
29

3
Hourly
False
21

View 3 Replies View Related

Calculated Column Based On Case Statement Result

Dec 10, 2013

I'm new to SQL Server and would like to add a calculated column to this query from the report writer in our ERP system based on the NextFreq case statement result.

Basically, I want to create a column called service with result as follows:

If IV.meter > NextFreq then the result should be 'OVERDUE'
If (NextFreq - IV.meter) <50 then the result should be 'DUE SOON'
Otherwise the result should be 'NOT DUE'

This is the code from the current report writer query:

Select IV.item, IV.meter, isnull(wt.name,0)as name, case when whh.meterstop is null then 0 end meterstop, whh.rejected, Case when cast(meterstop as int) > 0 then cast(meterstop as int) when meterstop is null then isnull(IV.meter,0) else isnull(IV.meter,0) end EndMeter, ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) as LastFreq,
case when whh.rejected = 1 then ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 0 then 100 when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 100

[Code] ....

View 4 Replies View Related

Need CASE Statement(s) To Populate 5 Fields Based On Values Of 2

Mar 10, 2008

I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.

What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX



If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL.



After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.



CASE HB.PLAN_TYPE

WHEN '10' THEN 'Y'

ELSE 'N' END AS 1A,

CASE HB.PLAN_TYPE

WHEN '10' THEN 'N'

ELSE ' ' END AS 1B,

' ' AS 1C,

CASE HB.PLAN_TYPE

WHEN '10' THEN HB.BENEFIT_PLAN

ELSE ' ' END AS 1D,

CASE HB.PLAN_TYPE

WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'

WHEN HB.COVRG_CD ='2' THEN 'YY'

WHEN HB.COVRG_CD ='3' THEN 'ZZ'

ELSE 'WW' END)

ELSE ' ' END AS 1E,



It works to an extent. While the desires/required output it above, the output this produces (same scenario) is:



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

Y N __ MD XX N __ __ __ __ N __ __ __ __ N __ __ __ __



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

N __ __ __ __ Y N __ MD XX N __ __ __ __ N __ __ __ __



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX



While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL.
Any assistance in this matter would be greatly appreciated.

View 5 Replies View Related

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related

How To: Create A SELECT To Select Records From A Table Based On The First Letter.......

Aug 16, 2007

Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
 
Thanks Ross

View 3 Replies View Related

Transact SQL :: Field Type Float - Case And IsNull

Oct 27, 2015

I have a field called PPH and this field type float. so, this field could be null, 0.0, or values. I am using case statement:
   
[PPH] = case when h.HoursFloat = 0 then 0 else
round(s.Pledges / h.HoursFloat,2) end,

I am trying to add where if h.HourFloat is null then 0 but, it does not work. I have tried different ways such as

[PPH] = CASE isnull (h.HoursFloat,0) 
when 0 then 0 else ....

How to add another condition to check on null then 0.

View 9 Replies View Related

SqlDataSource.Select Error: Unable To Cast Object Of Type 'System.Data.DataView' To Type 'System.String'.

Oct 19, 2006

I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx    Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!

View 3 Replies View Related

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

Sep 2, 2015

Below is the SQL Query i am currently having

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

[Code] ....

And here is the result dataset

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

[Code] .....

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

View 13 Replies View Related

Time Based Data Type

Aug 4, 2005

Right now i'm developing a time attendance system.
So in that case i'll be playing with time where that time represents what time an employee come to office and go home.

I'm a little bit confuse which data type i'll be used in this system.
Because there will be a lot of calculation according time data inside the database, such as: how many times an employee comes before 08.00, how much hours that he/she spend in the office that they, etc.

Can somebody help me with this problem, what data type most suitable with this kind of system and how can I do the calculation more easily?

thanx before,

View 3 Replies View Related

SQL Statement Based On Data Type

Nov 22, 2005

Hi there,

I have a situation in which I need to do one thing if the values of a field ("ServiceCode") fall within the range of 100-299, and do something else if the value is outside this range. The problem is that ServiceCode field has a datatype of char(6), and there are some non-numeric values in that field.

So the logic needs to be:

If ServiceCode is a number datatype (int or decimal) AND ServiceCode is between 100 and 299, then <Do Something>
Else NULL


Additionally, this statement needs to be within a larger Select statement.

I.E.

SELECT
FIELD1,
FIELD2,
<LOGIC FROM ABOVE>,
FIELD4
FROM TABLE_X
WHERE <BLAH>


Any suggestions?



Thanks,
NWChowd

View 5 Replies View Related

Case Statement - Calculate No Of Days Based On User Input Start And End Date

Jan 5, 2015

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.

View 4 Replies View Related

SQL 2012 :: Restore DB Without LDF File - Special Case DB Shutdown In Mid Transaction

Nov 14, 2014

Got this situation, trying to do Use SignleMode to recover my handing db, after that lost ldf (and physically too). Tried all things thru SSMS and scripts (below) that I know with no result, is there anything else I can try to recover it, I don't need log file.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not open new database 'MyLostDB'. CREATE DATABASE is aborted.

File activation failure. The physical file name "C:xxxMyLostDB.ldf" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. (Microsoft SQL Server, Error: 1813)

EXEC sp_attach_single_file_db @dbname='Commissions',
@physname=N'C:SQLDataMyLostDB.mdf'
GO
CREATE DATABASE Commissions ON
(FILENAME = N'C:SQLDataMyLostDB.mdf')
FOR ATTACH_REBUILD_LOG
GO

View 6 Replies View Related

Conditional Fields Based On Export Type

Aug 27, 2007

I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example).
I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.

Any help would be appreciated.

Jerry

View 2 Replies View Related

SQL Server TimeStamp Data Type Problem In Pst.setNULL() In Case Of Paramatrized Que

May 25, 2004

I would like to get information related to timestamp data type in SQL Server (WANT TO SET NULL IN TIMESTAMP COLUMN )I have Following case

try {
try {
stmt.execute("drop table timestampTable");
}
catch (SQLException ex1) {
}
stmt.execute(
"Create table timestampTable(c1 int Primary Key, c2 Timestamp)");

PreparedStatement pst = connection.prepareStatement(
"insert into timestampTable values(?,?)");
pst.setInt(1, 2);
pst.setNull(2, Types.TIMESTAMP);
pst.execute();
}
catch (SQLException ex) {
ex.printStackTrace();
}


TRACE IS GIVEN BELOW
====================


java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Disallowed implicit conversion from data type datetime to data type timestamp, table 'ClientDB.dbo.timestampTable', column 'c2'. Use the CONVERT function to run this query.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeInter nal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec ute(Unknown Source)
at JDBC.TestSQLServer.testTIMETAMPDataTypes(TestSQLSe rver.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.daffodilwoods.tools.testworker.TestRunner.runM ethod(TestRunner.java:159)
at com.daffodilwoods.tools.testworker.TestRunner.runI nSequence(TestRunner.java:83)
at com.daffodilwoods.tools.testworker.TestRunner.runT estCase(TestRunner.java:4


PLEASE REPLY ME AS SOON AS POSSIBLE

THANKS

SUBE SINGH

View 1 Replies View Related

How To Performance Case-insensitive Search On XML Data Type In SQLServer 2005?

Apr 25, 2006

Does anyone know how to how to performance case-insensitive search onXML data type in SQLServer 2005? Or I have to convert all the xml datato lower case before I store it?Thanks in advance.John

View 2 Replies View Related

Transact SQL :: Sum Quantities For Given Transaction Type

Nov 4, 2015

My data is in 4 columns and multiple rows, like this

PartNo  Quantity TransactionType         TransactionDate
aaa       25         Incoming                    2015-03-01
aaa       25         Incoming                    2015-03-01
aaa       50         Transfer                     2015-03-02
bbb       30         Incoming                   2015-03-03
bbb       30         Transfer                     2015-03-03
ccc        50         Incoming                   2015-05-15
ccc        75         Incoming                   2015-05-20
ccc        50         Transfer                    2015-05-18
ccc        75         Transfer                    2015-05-21

What I need to achieve is sum the quantities for a given transaction type, group it by Part Number and add an additional column where the Transaction Date for Transfer Type rows would become the Transfer Date. Each part would have one row. The resulting data would look like this.

PartNo   Quantity   IncomingDate   TransferDate
aaa        50           2015-03-01      2015-03-02
bbb        30           2015-03-03      2015-03-03
ccc         125         2015-05-15      2015-05-21

How to achieve this goal.

View 5 Replies View Related

Transact SQL :: Add Position Of Record Based On Certain Date Of Transaction

Aug 4, 2015

I built the following query to add the position of a record based on a certain date of the transaction of a customer:

select
CustomerID
, Product
, purchasedate

[code]....

View 6 Replies View Related

Transact SQL :: Find Last Transaction Of A Particular Type For Each Customer

Nov 12, 2015

I am working with SQL 2012 Express and I have a table with all transactions of invoices and payments for each customer. I am looking to find the last transaction detail of either two particular types of transactions (but not both) for each customer. So far I have tried various combinations around

SELECT        MAX([sbt::dte]) OVER (PARTITION BY [sbt::code]) AS LastPayment, [sbt::folio], [sbt::typ], [sbt::net] 
FROM dbo.tblSalbatTxn
WHERE      ([sbt::typ] = 13 OR [sbt::typ] = 17) 

Then there are some cases where customers have made more than the one of the same transaction type on that same last day which I would then like to sum the net value for that day of that transaction type.

I have also worked around this but it filtered the transaction type after it found the last transaction for each customer irrespective of it's transaction type.

SELECT        TOP (100) PERCENT a.[cl::code], a.[cl::alpha], b.[sbt::folio], b.[sbt::typ]
FROM            dbo.tblSalAccounts AS a INNER JOIN
                         dbo.tblSalbatTxn AS b ON a.[cl::code] = b.[sbt::code]
WHERE        (b.[sbt::dte] =
                             (SELECT        MAX([sbt::dte]) AS Expr1
                               FROM            dbo.tblSalbatTxn
                               WHERE        (b.[sbt::typ] = 11 OR
                                                         b.[sbt::typ] = 17) AND ([sbt::code] = b.[sbt::code]))) 

View 8 Replies View Related

Transact SQL :: Commit And Rollback Transaction Based Upon Results Of A Calculation

Oct 5, 2015

In t-sql 2012, I have the following sql that I would like the following to occur:

1. commit or rollback a transaction based upon the results of a calculation listed below,
2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way to be able to tell from messages if a rollback occurred or a commit happened.

DECLARE @TransactionName varchar(20) = 'Transaction1';
        @STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0
DECLARE @lockmap TABLE (lockID  int NOT NULL PRIMARY KEY,
                       schoolID  int NOT NULL,                    
                       UNIQUE(schoolID,lockID)
 )

[Code] ....

Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?

View 5 Replies View Related

T-SQL (SS2K8) :: Find Matching Phone Of Person Based On Relation Type - Duplicates

Aug 11, 2014

I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.

Below is the schema structure:

Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names
Patient_Info: PatientID, table contains patient ID and other information
Patient_PersonRelation: Person_ID, patientID, RelationType
Address: Contains address of all person and patient (key PersonID)
Phone: Contains phone # of everyone (key is personID)

The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).

View 9 Replies View Related

Select Case - A Better Way?

Jul 7, 2005

Hi, i have just written this:
select distinct department = case department
when 'ict' then 'Partnership Office'
when 'finance' then 'Partnership Office'
when 'Employee Development' then 'Partnership Office'
when 'Contract Compliance & MIS' then 'Partnership Office'
when 'Quality, Standards & Effective' then 'Partnership Office'
when 'Partnership administration Exe' then 'Partnership Office'
when 'Partnership Executive' then 'Partnership Office'
when 'Personnel' then 'Partnership Office'
when 'Operations' then 'Partnership Office'
when 'Quality Standards and Effectiv' then 'Partnership Office'
when 'Marketing and Communications' then 'Partnership Office'
when 'Marketing and Information' then 'Partnership Office'
else department end from personnel_records

is there some way to do this on one line, like
...
when in ('ict','finance','personnel', etc etc) then 'Partnership Office'
else
... ?

TIA

View 2 Replies View Related

Is This Possible To Use Select Case

Feb 8, 2007

hi, this is my procedure.

CREATE procedure yard_retrieve
(
@yard varchar(50),@vessel varchar(50),@customer varchar(50)
)
AS
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@yard and category_code=(select category_code from categoryMaster where category_description='Yard') )
select 'found' as result
else
select 'not found' as reault
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@vessel and category_code=(select category_code from categoryMaster where category_description='vessel'))
select 'found' as reault1
else
select 'not found' as reault1
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@customer and category_code=(select category_code from categoryMaster where category_description='customer'))
select 'found' as reault2
else
select 'not found' as reault2
end
GO

if i run this procedure i need the result as following

result result1 result2
found notfound found

is this posible to do using select case ? please help me to get my need please

View 4 Replies View Related

Excel File Manipulation - Repeat First 7 Columns Based On Number Of Lines In Transaction

Jul 10, 2015

I have a task where I need to process roughly 60000 excel spreadsheets and bring them into a SQL Server 2014 database. All excel files have the same format and same number of identical columns. I have set up an SSIS package that is using Foreach Loop Container to look into a folder and process these files one at a time and load them to a table. The mappings are straight-forward, no problems there.

I am attaching a sample spreadsheet with two tabs - current structure and desired structure.

Basically what I need to do is to repeat the first 7 columns based on the number of lines in a transaction.

The number of lines is variable per patient.

View 6 Replies View Related

Open Transaction With Sleeping Status And Wait Type As AWAITING COMMAND

Sep 4, 2015

During our DR drill we found that the same code which used to run perfectly fine on our Primary Data Centre is running very slow on Disaster Recovery DB Server and there are Lot's of open transaction with sleeping status and waittype as 'AWAITING COMMAND'.CPU, Memory and disk utilization are good. The ping reply between the app server and the DB server is well within the limit's even blocking is not their, also nothing is reported in the error logs. We are using SQL server 2014 STD 64 BIT on Windows Server 2012.

View 6 Replies View Related

Select Case Issue

Sep 19, 2007

I need to do the following so that I can convert a Null value to 0 in my db... however I am getting errors "Incorrect Syntax near '='.
Facilities.PortfolioID AS PortfolioID =
CaseWhen Facilities.PortfolioID = Null then Facilities.PortfolioID = 0
End,

View 5 Replies View Related

CASE In Select Statement

Jan 17, 2008

Hi, i've got this stored procedure:
 @LidFederatieNummer varchar(20),
@ClubID int,
@LidClubNummer varchar(50),
@PersoonNaam varchar(100),
@ClubStamnummer varchar(10),
@ClubNaam varchar(50),
@SeizoenID int,
@ShowAllJN bit,
@LidFederatieJN int,
@CategorieID int
AS
BEGIN

IF @CategorieID IS NOT NULL BEGIN
DECLARE @LicentieVerplicht int
SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
END

IF (@ShowAllJN = 0)
BEGIN
SET RowCount 100
END

CASE @LicentieVerplicht
WHEN '1' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 1
Order By Persoon
END

WHEN '2' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN)
ORDER BY Persoon
END

WHEN '3' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 0
Order By Persoon
END
 According the value of my @LicentieVerplicht variable:
DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
My where clausule should change... how can  i achieve this cause my case is not working because it is not inside the select statement

View 4 Replies View Related

Case When In A Select Statement

Apr 13, 2006

Hi everyone,
I have the following problem withj this query:

SELECT DocNumber,
       Title,FirstName,LastName,Paper,
       ANNO,Location,Keywords,
       URL= case WHEN URL IS NULL THEN
'|<a
href=''https://illiad.library.nova.edu/illiad/FNN/illiad.dll?ILLiadRedirect=OpenURL&genre=thesis&isbn='+CONVERT(varchar,DocNumber)+'&title='+CONVERT(varchar,Title)+'&rft.pub='+CONVERT(varchar,Program)+'&rft.edition='+
CONVERT(varchar,Paper) +'&date='+ CONVERT(varchar,ANNO)+
'&aulast=' + CONVERT(varchar,Lastname) + '&aufirst=' +
CONVERT(varchar,Firstname)+
'&url_ver=Z39.88-2004&rfr_id=info:sid/MARPS:Practicum''target=''blank'')>
Order through ILLiad</a>' ELSE URL END
FROM DocTable1 WHERE FREETEXT....

When URL is null, I don't get the string showing. It works properly only in the query analyzer.

Any ideas?

Christian

View 3 Replies View Related

Search Or Select CASE????

Nov 21, 2001

I'm using a 'searched case' however I don't care which method I use.

I'm open to using anything....

I just want to correct a problem in my pivot table where I need to create a ficticous eventdate for 'S' records, those that do not have an event date in the past 7 days.

-- eventdate is a datetime column
select User
,res =
case res --event result
when 'S' then '0' --S generic non-event
when 'SUC' then '1' --successfull event
end
,eventdate =
case eventdate
when enventdate < getdate()-8 then getdate()-8
end
from eventlog_1 --my view of events

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '<'.

TIA

JeffP...

View 2 Replies View Related







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