USING 'CASE' TO GROUP DATA

Aug 22, 2006

Hi,

Can anyone please help me with SQL syntax to create a second variable
based on the value of another (both numeric)?

My effort is below but I get a syntax error.

SELECT

charA,

CASE charA
WHEN < -199 THEN 2
WHEN < 31 THEN 3
WHEN < 82 THEN 4
WHEN < 100 THEN 5
WHEN < 105 THEN 6
WHEN < 111 THEN 7
WHEN < 143 THEN 8
WHEN < 165 THEN 9
WHEN < 233 THEN 10
WHEN >= 233 THEN 11
END AS Bin_charA

FROM X


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

View 3 Replies


ADVERTISEMENT

Group With A Case?

Mar 26, 2002

I have a SQL statement below which has a case clause which works fine, until I want to group it. Is it possible to group on a clause that is defined as case?

declare @sdate datetime
set @sdate = '02/01/2002'
select distinct count(tbm.otheridnumber), instatus
= case inmate_status
when 'inactive' then 'Out of system'
else 'Active'
end,inm.df_institutionname
from tb_medication tbm
inner join inmateid inm
on tbm.inmate_cntr_id = inm.inmate_cntr_id
where
tbm.chdpedt is null
and DATEDIFF(day,chdpstdt , @sdate) > 90
group by instatus,
inm.df_institutionname
order by inm.df_institutionname

View 1 Replies View Related

Need Some Assistance With A Group By Case

Oct 11, 2006

Code:


Select Signal_id, Test =
CASE
WHEN signal_date > dateadd(hour,-24,getdate()) THEN 'Today'
WHEN signal_date > dateadd(hour,-48,getdate()) and signal_date < dateadd(hour,-24,getdate()) THEN 'Today-1'
WHEN signal_date > dateadd(hour,-72,getdate()) and signal_date < dateadd(hour,-48,getdate()) THEN 'Today-2'
WHEN signal_date > dateadd(hour,-96,getdate()) and signal_date < dateadd(hour,-72,getdate()) THEN 'Today-3'
WHEN signal_date > dateadd(hour,-120,getdate()) and signal_date < dateadd(hour,-96,getdate()) THEN 'Today-4'
WHEN signal_date > dateadd(hour,-144,getdate()) and signal_date < dateadd(hour,-120,getdate()) THEN 'Today-5'
WHEN signal_date > dateadd(hour,-168,getdate()) and signal_date < dateadd(hour,-144,getdate()) THEN 'Today-6'
WHEN signal_date > dateadd(hour,-192,getdate()) and signal_date < dateadd(hour,-168,getdate()) THEN 'Today-7'
WHEN signal_date > dateadd(hour,-216,getdate()) and signal_date < dateadd(hour,-192,getdate()) THEN 'Today-8'
WHEN signal_date > dateadd(hour,-240,getdate()) and signal_date < dateadd(hour,-216,getdate()) THEN 'Today-9'
ELSE 'Other'
END
, Total = count(*)
From abmsignal WHERE Signal_id = 'fail test' AND Signal_date > dateadd(hour,-240,getdate())
Group by Signal_id, Test
COMPUTE AVG(SUM(Total))




That's what I would like, Daily (or rather 24 hour periods) listed, and then averaged out. I will fix the dateadds so that they are for previous 10 days rather than previous 240 hours, but first I need to get it to work as is.

View 2 Replies View Related

Question Regarding CASE And GROUP BY

Jan 21, 2004

I have the below query and I am not sure if this will return an accurate aggregate, I know I cannot just group by my alias GLG_DELEGATE_ID, is this the way to handle aggregates when you have a CASE in the SELECT statement?

SELECT CASE
WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID
END AS GLG_DELEGATE_ID
,COUNT(P.CONSULTATION_ID) ACTIVITY_AMOUNT
FROM
dbo.CONSULTATION C
GROUP BY
C.GLG_DELEGATE_ID
, C.GLG_ID

View 1 Replies View Related

Group By Case Statement

May 13, 2007

Hi guys,
I am having a little diffulty displaying two columns next to each other in a case/group by statement as code shown below.
I was wondering if i could have the results displayed such that the gst_amount and total_amount are in two separate columns (as they currently are) however the results of the rows are in the same row not in separate rows (as they are currently). I dont think i can do this in the current case statement that i have (i.e: the two case statements).
Any feedback would be appreciated


SELECT distinct
PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
case when temp_111.[name] like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as GST_AMOUNT,
case when temp_111.[name] not like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as Total_Amount
FROM temp_111 INNER JOIN PERIOD
ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ
WHERE
(NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL)
AND temp_111.PERIODSEQ in ('111')
group by PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
temp_111.[name]


Here is the current result displayed:


Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000NelNULL 83470.5608000000
2006-11-01 00:00:00.000NelNULL 161408.5264874810
2006-11-01 00:00:00.000NelNULL 677568.2683000000
2006-11-01 00:00:00.000NelNULL 2645478.1215092400
2006-11-01 00:00:00.000Nel8347.0560800000 NULL
2006-11-01 00:00:00.000Nel16140.8526488160NULL
2006-11-01 00:00:00.000Nel67756.8268300000NULL
2006-11-01 00:00:00.000Nel264547.8121507070NULL



Instead I want the result to show something like this:


Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000Nel8347.0560800000 83470.5608000000
2006-11-01 00:00:00.000Nel16140.8526488160 161408.5264874810
2006-11-01 00:00:00.000Nel67756.8268300000 677568.2683000000
2006-11-01 00:00:00.000Nel264547.8121507070 2645478.1215092400

View 2 Replies View Related

Group Case Sensitive

Jul 25, 2006

It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?

I am running SQL Server 2000 and the database that I am querying is not case sensitive.

View 9 Replies View Related

GROUP BY, CASE (easy) Problem

Nov 16, 2006

I am not to experienced with SQL and I guess the following is a piece of cake for most of you...
I want to get the sales amount for each shop, for each of three different products, and for the sum of the three products. The problem is that I only get for each shop for each of the product, but not the sum of the three products OR for each shop the total of the three products, but not for each product.

My query looks like this:

SELECT

Shop_name,

case

when prod in (1,2,3) then ‘milk_bread_butter’

when prod = 1 then ‘milk’

when prod = 2 then ‘bread’

when prod=3 then ‘butter’

else ‘none’

end as product,

sum(sales_amount) as sales

FROM

Dbo.sales

GROUP BY

Shop_name,

case

when prod in (1,2,3) then ‘milk_bread_butter’

when prod = 1 then ‘milk’

when prod = 2 then ‘bread’

when prod=3 then ‘butter’

else ‘none’

end



The problem is that I only get the lin 'milk_bread_butter' per shop. If I switch the order around in the case statement;

case

when prod = 1 then ‘milk’

when prod = 2 then ‘bread’

when prod=3 then ‘butter’

when prod in (1,2,3) then ‘milk_bread_butter’

else ‘none’



I get the sales amounts per shop for each of the products, but not for the milk_bread_butter total.



I would be very greatful for any guidance as how to solve this.

Thank you.

Best, Mal

View 3 Replies View Related

Transact-SQL Help - CASE Statement And Group By

Jul 20, 2005

I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad

View 4 Replies View Related

Using A Field Alias For A CASE Statement In A GROUP BY Clause

May 20, 2004

When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:

ie,

SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3

etc.


The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".

Regards,

Dave

View 5 Replies View Related

Problem With Group By When Using Case Statements In The Select List.

Oct 11, 2007



I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :

Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.



Below is the my sql statement:

SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,

ItemRef =

Case

when RH.Quantity < 0 then Sum(RH.Quantity)

when RH.Quantity >= 0 then Sum(0)

end

FROM dbo.RHISTORY AS RH INNER JOIN
dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID

WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007')
AND (RH.TransactionCode IN (13, 14, 15, 16))

Group by dbo.DateOnly(TH.TransactionDate), RH.Item

The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day.

Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.

View 12 Replies View Related

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 Replies View Related

Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.

Sep 6, 2007

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.

View 2 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName'  values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC)  must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName                 Clm ABC-X

X

Page2:

GroupName                 Clm ABC-Y

Y

Page3:

GroupName                 Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

Collapsing/Expanding Group Data In Table And Matrix Data Regions

May 30, 2007

Hi,



Is it possible to create Expand/Collapse functionality for the grouped data in Table and Matrix data regions? Essentially, the idea is for the user to be able to see the group/subgroup data if she wishes to by clicking on (+/-) symbols, as is usually the case in Tree View style data grid control in web apps. Any ideas how to accomplish the same in reporting services?



Thanks.

View 1 Replies View Related

Inserting Different Case Data

Nov 10, 2005

Currently am migrating my data from oracle to sqlserver. Here is a senario where am struck.

CREATE TABLE TEST (COL1 VARCHAR(90), CONSTRAINT TEST_PK PRIMARY KEY (COL1) );
INSERT INTO TEST VALUES ('test');
INSERT INTO TEST VALUES ('TEST'); -- UPPER CASE data

The above is acceptable in oracle but in sqlserver in case insesitive database the second value is not accepted as it voilates the PK constraint. I searched the forums and found below link

http://www.dbforums.com/t905653.html

But on doing it resulted in below error:

Server: Msg 5074, Level 16, State 8, Line 1
The object 'TEST_PK' is dependent on column 'COL1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN COL1 failed because one or more objects access this column.

View 4 Replies View Related

A Curious Case Of Data Corruption

Jul 23, 2005

Dear group,if someone could give me an idea what is going on in one of ourdatabases, this would really really be helpful.We have two tables with around 2 / 3 million rows. These tables have nokey and no ID. (This major design flaw will be overcome in some laterversion of the application-software working on this DB but right now ihave to live with this).Now for the funny bit1) I open one window in the Query-Analyzer and write some code likeBegin transaction INSERT INTO TABLE COMMIT2) in another window i write "SELECT COUNT(*) from TABLE"If I perform the insert then afterwards select count(*) the row-countis incremented by two whereas the Insert-Statement said "1 row(s)modified.DBCC gives no errors.DBCC gives amount of rows 2 million rowsSelect count(*) on the same table gives 3 million rowsExporting the data, truncating the table re-importing data gives noresult, right now the DTS-status is 203 and the machine is "thinking".Is there any possibility to check the "integrity" of the table?This problem is on the production machine, but right now i am workingon a copy so it was propagated with backup / restore-mechanism.Any hint would be very helpfulThanks and GreetingsUli

View 5 Replies View Related

Converting Data Like A Case Statement

Oct 10, 2006

Hello.

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?

Thanks









View 10 Replies View Related

Data In Sqlserver Is Not Case Sensitive

Aug 28, 2006

I am facing problems as the data in sqlserver is not case sensitive. The data in parent key may be capital/lower case and the same data in the child table may be lower/capital case. While migrating the data from sqlserver database to other databases(like oracle) its giving error as the data not found in parent key, though the data found in parent table . This is just because the case sensitive in oracle. But according to my knowledge its better if the sqlserver also supports data case sensitive.

Take a small example
Need a table to store all the alphabets in a table
the table structure is

CREATE TABLE [dbo].[ALPHABET] (
[Alphabet] [varchar] (1) NOT NULL ,
[Description] [varchar] (50) NOT NULL
)
GO

The data is








Alphabet
Description

a
Small a

b
Small b

C
Capital C

A
Capital A

sqlserver wont allow to insert data 'A' and gives error "voilation of primary key", though a & A are different according to this table.

I tried with NVARCHAR datatype also. The same problem here also. Sqlserver atleast should support data case sensitivity for NVARCHAR datatype as this can store different languages. May be in other languages the entire meaning may be differ with case differences. Even in english language some words meaning will differ with case differences. For reference can refer english dictionary

View 1 Replies View Related

Fetching Case Sensitive Data From The Database

Apr 7, 2008

S/W Technologies : C#.Net 2005, ASP.Net 2005, SQL Server 2005
Greetings everyone,
       Heres my code for login verification, which is written in the login button click.
SqlCommand cmd = new SqlCommand("Select uid,pass from UserRegistration where uid='" + txtuname.Text + "' and pass='" + txtpass.Text + "'", con);
con.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows) {  <code......> }
     Now, my problem is, the SQL Server 2005 is fetching rows without checking case. For e.g. if I enter a password as "MYSTERY" or "mystery" which is stored as "Mystery" in the database. The datareader shows positive in HasRows property. So, Can someone suggest me how to fetch case sensitive data from SQL SERVER 2005. Thanks.

View 3 Replies View Related

Finding Lower Case Data From Table

Jun 17, 2003

Hi,

My database is case insensitive. However, the application is case sensitive for the data in the table.

I need to find out the data from the table where the data is stored in lower case.

Here is the sample data:
Table: inv_manager
column: sku varchar(5)
Data: 1134X
1135x
1123a
b145Y

I just need query to return row 2, 3, 4.

Here is my query:

select sku from inv_manager where sku like lower('%[a-z]%')

The problem is it returns all the rows from the table including first one.

How can I return just the data with lower case ?

Thanks

View 2 Replies View Related

Coverting Mixed Case Data To UPPERCASE

Jan 28, 2004

I am trying to convert all my client first and last names in my table to uppercase. They are currently listed as mixed case. Also I wanted to know what is the best way to force the data to UPPERCASE hwen a end user tries to insert or update the clients name. I am thinking about trying a trigger, but I am unsure how to set it up. Thanks for all the help.

View 4 Replies View Related

How To Make Sql Server Data Case - Sensitive?

Feb 25, 2008

Hii



I transferred data from Oracle to sql server 2005. Now what i want is to make data in the tables case-sensitive .(it has to be data inside the tables only and not table and column names).
what i tried is :


alter database test collate Latin1_General_CS_AS

But to my horror it made the tables name case-sensitive .
Plz help me out asap.


Thanx in advance
Supriya

View 18 Replies View Related

SQL Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?

Aug 31, 2015

How can I change my T-SQL text editor from text sensitive to text insensitive?

View 2 Replies View Related

Case Insensitivity Is On Server Wide: Tables Render Case Sensative...

Jan 6, 2005

Hello:

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?

View 4 Replies View Related

Case Insensitive Searching In Sql Server 2000 When It's Case Sensitive

May 4, 2007

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.

View 3 Replies View Related

Select Case Columns - Inconsistent Data Types

May 8, 2012

When I try to do the following SQL statement to select a different column it gives the error below as one is a date and one a number

[I]ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Select
CASE WHEN 2=1 THEN DATE+TP_PERIOD/48 ELSE TP_PERIOD END DATETIME,

Is there a way to use TOCHAR or something so I can get either result in one column?

View 3 Replies View Related

SQL 2012 :: SSIS Data Flow With Case Statements

Oct 29, 2014

I would like to know how I can add the following sample code to my Source data on Data Flow on SSIS, or what other options there are. The main issue is time as we have talking about 100's of millions of rows

select Sample,
CASE
WHEN Sample IS NULL
THEN NULL
WHEN SUBSTRING(Sample, 1, 6) IS NULL
THEN ' '
ELSE RTRIM(SUBSTRING(Sample, 1, 6))
END AS [Sample_1_6]
from TestTable

what I have done at this stage is just to Create a SQL task with a Insert into

INSERT INTO [dbo].[TestTable1]
([Sample]
,[Sample_1_6])
select Sample,
CASE WHEN Sample IS NULL =THEN NULL
WHEN SUBSTRING(Sample, 1, 6) IS NULL THEN ' '
ELSE RTRIM(SUBSTRING(Sample, 1, 6))
END AS [Sample_1_6]
from TestTable

If there is a way adding this to a dataflow so I van use fast load that would really be the best solution. I know there are derived columns, but would this really be faster than the straight insert into in a SQL Task? If this is the way to go what is the code I would use in the derived column or any other option.

View 7 Replies View Related

Simple IF Or CASE Statement In Data Flow Transformation

May 11, 2007

Using SSIS to import from Excel to SQL Server.

In Excel they are showing Sale as -ve quantity and purchase as +ve quantity.

The database has quantity always as +ve figure and a separate column "isPurchase" set to true or false depending on whether purchase or sale.



So I need Derived Column to return a bolean (or int) depending whether quantity is positive or negative. I tried each of the following in the Expression but all of them were invalid expressions.

CASE WHEN [TotalQuantity] > 0 THEN 0 ELSE 1 END

If [TotalQuantity] > 0 THEN 1 ELSE 0 END

IIF ([TotalQuantity] > 0, 1,0)



Can anyone help me with correct syntax, or correct Data Flow Transformation if Derived column is wrong.



Thanks



Richard

View 3 Replies View Related

HELP! Case Insensitive Database On Case Sensitive Server

Aug 17, 2005

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 Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

I 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

View 5 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

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 Related

Group Data By Weeks

Apr 22, 2008

hi, I am trying to group my data into weeks in a month.
I am using :
GROUP BY DateAdd(day, -1 * datepart(dw, convert(char(10),date1,23)),
convert(char(10),date1,23))

Which works ok, but my first day is the monday of each week.
Do you know how I can group by weeks,
where dates run from monday to sunday ?
So IN march I would have

week begin 3/3
week begin 10/3
week begin 17/3
week begin 24/3
week begin 31/3

But for the last day I only want to count the 31st, not the entire week.
Same goes for the beginning of the month,
eg for april, I want it to show as week beginning 31st march but I only count aprils data.

I hope that makes sense.

can you help ?

View 3 Replies View Related

How To Group Certain Rows And Get The Data.

Jul 2, 2007

Hi Folks,

I am stuck in forming a query.

My age wise employee count sample data (department wise) is as shown below.

Sample Data
-----------
Department [>55] [50-55] [<50] [<40] [<30]
---------- ----- ------- ----- ----- -----
Marketing 0 1 5 10 20
Op's Support 0 3 6 5 25
Op's Tech 0 0 0 3 10
Product Tech 0 0 2 4 12
Product Support 0 0 1 3 7

I would require the data (sum of employee count age wise) to be categorized at a boarder level. Each category comprising of one or more departments.

Operations [Op's Support + Op's Tech], Product [Product Tech + Product Support], Others [Marketing]
The expected result would be.

Category [>55] [50-55] [<50] [<40] [<30] [Total]
--------- ----- ------- ----- ----- ----- -------
Operations 0 3 6 8 35 52
Product 0 0 3 7 19 29
Others 0 1 5 10 20 36

Thanking you in anticipation.

Jabez.

View 4 Replies View Related







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