Change results

I have created query As shown:

SELECT distinct [Table] FROM [Database]

The results i get back are A, B & C. What i am trying to do is show a result as a different word.

i.e instead of displaying A is shows in drop down list as Apple, B shows Banana. etc.

Can anyone help please.

Thanks

Duncan:

It sounds to me like what you want to do is to join to a translation table to do a simple lookup; something like?

declare @.source table ( sourceKey varchar(8))
insert into @.source values ('A')
insert into @.source values ('B')
insert into @.source values ('C')

declare @.translate table ( sourceKey varchar(8), xlate varchar (10))
insert into @.translate values ('A', 'Apple')
insert into @.translate values ('B', 'Banana')
insert into @.translate values ('C', 'Cherry')

select xlate as Target
from @.source p
inner join @.translate q
on p.sourceKey = q.sourceKey

-- Target
-- -
-- Apple
-- Banana
-- Cherry

|||

The syntax of your query is not correct. Normally in SQL Server you would use the following:

SELECT [Columns] FROM [Table]

It is good practice to create a lookup table that contains a set of unique shorthand codes or IDs, such as 'A' and 'B' in your example, and an alphanumerical field to contain a description of the code/ID - 'Apple' or 'Banana' in your example. You would then join the fact table to the lookup table and return the appropriate columns from each table.

Could you post your table definitions and the query that you are using to return the data?

Chris

|||Thanks for that. I can see what you've done but the fields i need are carries in a field in a table. there can be only 3 results a b or c. there are alot of records. i don;t see how i can use this to select distinct from field in table but display as something else.|||I must agree with Chris; I don't know where I am going and I'm afraid that I need a roadmap.|||

Query

SELECT distinct [COLUMN] FROM [TABLE]

Returns

A
B
C

What i would like is for it to show

Apple
Banana
Cranberry

It is to populate a drop down list with SSRS. So that the users see what each entry means and not just ABC.

|||

In that case then the answers previously given to your original post will work, however if in the short term you want to make this really simple then you could use this:

SELECT 'Apple' AS [LookupValue], 'A' AS LookupCode UNION
SELECT 'Banana', 'B' UNION
SELECT 'Cranberry', 'C'
ORDER BY 1

The LookupValue is the value that you would display in your drop-down list, the LookupCode is the value that you feed back into the report's main query.

I wouldn't recommend the above approach as you'd need to modify the SELECT statement if you make changes to the reference data - you'd be far better off by using a lookup table as originally suggested, I've provided the example below to show how this can be done.

The choice is yours.

Chris

CREATE TABLE dbo.FruitType
(
FruitTypeCode CHAR(1) NOT NULL PRIMARY KEY,
FruitTypeValue VARCHAR(100) NOT NULL,
)
GO

--Ideally you would declare a Foreign Key constraint between the FruitTypeCode columns in your fact table and the following lookup table.
INSERT INTO dbo.FruitType(FruitTypeCode, FruitTypeValue)
SELECT 'A', 'Apple' UNION
SELECT 'B', 'Banana' UNION
SELECT 'C', 'Cranberry'
GO

--Use this to populate your combo - display FruitTypeValue and feed FruitTypeCode back into the report's main query.
SELECT FruitTypeCode, FruitTypeValue
FROM dbo.FruitType
ORDER BY FruitTypeValue
GO

|||

Ok got it working.

select distinct

case [COLUMN NAME]

when 'A' then 'Apple'

when 'B' then 'Banana'

else 'Unknown'end

from [TABLE NAME]

where [COLUMN NAME] in ('A', 'B', 'C')

Change query issues

I am trying to take this statement:

Select Total = SUM(gt)
FROM
(Select Distinct CustomerName,
CustomerNumber,
FeeGoal_AZ AS FG,
FeeSchedule,
(cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt
from DCR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FeeGoal_AZ,
FeeSchedule
) as dTable

and change up (cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt

I need that part to do this instead there: (PostedAmount + Sum of all PDC where the PDC in this month + Sum all CC where CC in this month) / FeeSchedule) * 100 as gt

Here is my failed attempt:

DECLARE @.today DATETIME,
@.datefirst datetime

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @.datefirst = @.@.DATEFIRST

BEGIN
Select Total = SUM(gt)
FROM
(Select Distinct CustomerName,
CustomerNumber,
FeeGoal_AZ AS FG,
FeeSchedule,
PostedAmount
+ SUM(select PCD from dcr where EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dc.DateEntered IS NOT NULL)
+ SUM(select CC from dcr where EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dc.DateEntered IS NOT NULL)
* FeeSchedule) * 100 AS gt
from DCR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FeeGoal_AZ,
FeeSchedule
) as dTable

Once again your problem is that you cannot do a subquery inside a select list, ever. You can use a subquery as a derived table, so long as that subquery is a single sql statement, and then join to that. I am not fully sure what you are trying to do here. First of all, the above query can be simplified somewhat, as I was not reading the SQL thoroughly. The top statement can be simplified to:

Select SUM((cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 )
FROM DCR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FeeGoal_AZ,
FeeSchedule

As for the rest of the query, you need to explain exactly you are trying to do here. You can probably join your subqueries into the overall query, and sum the PDC column by joining to the DCR column in the from clause, and adding the where conditions to the end of the query. Note that you can join to mulpiple copies of the same table by using different aliases. Ypu may also be able to use the MONTH function to avoid all that complex date calculation logic. Check out this link and see if there is an easier way to do the same thing:

http://msdn2.microsoft.com/en-us/library/ms186724.aspx

Anyway, should be something like:

DECLARE @.today DATETIME,
@.datefirst datetime

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @.datefirst = @.@.DATEFIRST

Select Distinct dcr.CustomerName,
dcr.CustomerNumber,
dcr.FeeGoal_AZ AS FG,
dcr.FeeSchedule,
(dcr.PostedAmount + SUM(dcr2.CC) + SUM(dcr3.CC)) / dcr.FeeSchedule) * 100
from DCR as dcr
join DCR as dcr2
on dcr2.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)
AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND dcr2.DateEntered IS NOT NULL
-- enter your own code in this line -- AND dcr2.PrimparyKeyField(s) = dcr.PrimparyKeyField(s)
join DCR as dcr3
on dcr3.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)
AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND dc3.DateEntered IS NOT NULL)
WHERE dcr.branch='00002'
group by
dcr.CustomerName,
dcr.CustomerNumber,
dcr.FeeGoal_AZ,
dcr.FeeSchedule

You will probably have to rewrite that slightly as I am guessing at your database schema. You also don't need to have the same condition in each join as they are all joining to the same table. So for example having dcr.DateEntered IS NOT NULL once is sufficient. You will also have to join the three copies of the DCR table together on their primary keys, as I do not know what they are.

HTH

Remember, as with the CASE statement problem a few days ago, you cannot have a subquery within the column list of a select statement. You can only have a single subquery as a derived table in the from clause where you join to it.

|||

I'm lost, here is what I put

Select Distinct dcr.CustomerName,

dcr.CustomerNumber,

dcr.FeeGoal_AZ AS FG,

dcr.FeeSchedule,

((dcr.PostedAmount + SUM(dcr2.CC) + SUM(dcr3.CC)) / dcr.FeeSchedule) * 100

from DCR as dcr

join DCR as dcr2 on dcr2.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)

AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND dcr2.DateEntered IS NOT NULL

AND dcr2.CustoemrNumber = dcr.CustomerNumber

join DCR as dcr3 on dcr3.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)

AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND dc3.DateEntered IS NOT NULL)

WHERE dcr.branch='00002'

group by dcr.CustomerName,

dcr.CustomerNumber,

dcr.FeeGoal_AZ,

dcr.FeeSchedule

|||

So what is the problem with that code? You didn't say. You haven't joined on the primary key of the table, which you will need to do otherwise you will get a cartesian product. So you will need a

join dcr2 on dcr.dcrID = dcr2.drcid

or something like that. You may also need to have outer joins.

What subsets are you trying to get from DCR? I suggest you define clearly what they are, write three separate queries to get them, test them and then combine them into one statement. You may not need three copies of one table either, you may be able to do this with one copy. If you want three different calculations performing on the same subset of data, then one table will suffice. However, if you want to intersect three different subsets of the same data, then three table copies will be required.

Please add more explanation, I often have trouble determining what exactly your problems are, I need more information to solve.

|||

I don't know, that's why I need some hand holding here ! You probably have difficulty because this isn't usual requests for an IT programmer do code this kind of crap.

OK, let me try to explain from scratch.

I have a table called DCR

I am basically having to recreate this complicated Excel doc into SQL Server and Reporting Services to automate it.

Right now, I am not able to create the expression I need in my Reporting Services 2005 footer field which simply sums up that group column so I have to resort to doign the sum in SQL then bring in the sum using a dataset in my Report....which to me is totally stupid, overdone.

Anyway, For this total (subtotal of my column), I have to calculate the grand total of the following equation for each customer record. This is a transaction table therefore each customer record will have several entries.

First, let me give you some business formulas.

In-House1 = PostedAmount + Sum of PDC where EnteredDate between '2/1/2006' and '2/28/2006' + Sum of CC where EnteredDate between '2/1/2006' and '2/28/2006' + Sum of PDC where EnteredDate not between '2/1/2006' and '2/28/2006' + Sum of CC where EnteredDate not between '2/1/2006' and '2/28/2006'

In-House2 = InHouse1 * FeeSchedule

Now, my table is like this

DCR Table fields:

TransID (not needed in my query, just for fyi)

CustomerNumber

PDC (number)

CC (number)

Entered Date

FeeSchedule (int)

FeeGoal (bigint) - is updated from user entry using an asp.net form, i then update each customer trans record in dcr with the same fee goal that the user entered

Example data

--

TransID CustomerNumber PDC CC EnteredDate FeeGoal FeeSchedule

1 200 100 250 '2/2/2006' 2233 50

2 200 300 200 '2/5/2006' 2233 50

3 400 233 444 '2/6/2006' 1000 30

4 400 3333 332 '2/6/2006' 1000 30

5 400 2233 300 '2/18/2006' 1000 30

So now, if you figure out InHouse1's forumula using the fields from this table

then I want to take InHouse1 for each company and perform InHouse2's formula

this is as best as I can describe it...once again, their trying to shove an excel into a dtabase which I don't believe is normal, at least not in my 8 years of reporting, we wouldn't even touch that and have once and know what pains you have to go through...that's why they make Excel!

|||

Thank you, that makes things a lot clearer.

After a quick look at this, it would appear this problem needs a different approach. I will describe it now, I don;t have time to write it for you. Please try doing it this way and let me know if
it works, I will be online later to check. OK, instead of doing one big query, use a union, with a different query for each subtotal. As you are doing a UNION ALL, you will need to ensure
that each column returned from each unioned query has the same number of columns, with the same data type and precision. The names can differ. Now place all of that UNION ALL into a derived table
(just surround it all in parentheses and refer to the whole thing as if it were a table. The column names out the UNIONEd columns will assume whatever name you specified in the first of your UNIONED query.
Do you follow so far. Then in the outer query, SUM up the subtotal column as one column, and perform your second in house formula in the second statement. To to this, you will have to return the FeeSchedule column
in the UNION as well, and then repeat the formula from the previous column, multiplying it by that amount. It will look something like

SELECT SUM(sub.a), SUM(sub.a * sub.FeeSchedule)
FROM
(SELECT xyz....
FROM DCR
UNION ALL
SELECT xyz....
FROM DCR
SELECT xyz....
FROM DCR) as sub

You will have to fill in the details. Let me know how you get on....

|||

Ok, try this:

select SUM(subTotal) * FeeSchedule
from
(
select SUM(PDC) as subTotal,
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate between '2/1/2006' and '2/28/2006')
or (EnteredDate not between '2/1/2006' and '2/28/2006'))
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
and DateEntered IS NOT NULL)
union all
select SUM(CC),
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')
or (not between '2/1/2006' and '2/28/2006'))
and DateEntered IS NOT NULL)
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
)
group by FeeSchedule

You may want to change the date formulas, I just hard coded the values based on your description. Do you need these totals separately, or do you just want the end number per customer? If you want to know the values individually before summing them up, then you will have to resort to using multiple copies of the table, joining by customer number and some other field, to make sure like is joining like, and you are getting a semi cartesian join. I am confused by your date ranges. They are mutually exclusive. If you just want the grandtotal of both, don't bother to add the date filter clauses, adding the value for all the days in february to the value for all the days not in february is the same as saying add the value for all days in the table together! Do you see my point. It is like saying add 3, 4 and 5. Now add all the other numbers, 1 to 10 that are not 3,4 and 5. It is the same as saying add all the numbers from 1 to 10, yet the latter is a lot simpler to perform.

Is the FeeSchedule the same value for each customer? If it is, the above query will work. If it differs for the same customer, then I would assume you would want the total of all FeeSchedules for each customer as well, before multiplying by the SUM(PDC) + SUM(CC). If that is the case, remove FeeSchedule from the group by's in the UNION expression, and add a SUM in front of the colum to get the SUM of FeeSchedules instead in the UNION sub query\derived table (both terms are interchangeable I think).

I hope I explained that well, it is quite a complex query, I am not surprised it is causing some issues. Let me know if this solved the issue or not,

Simon

|||

Oops, slight mistake in above query. I am only human after all.

select SUM(subTotal) * FeeSchedule, CustomerNumber
from
(
select SUM(PDC) as subTotal,
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate between '2/1/2006' and '2/28/2006')
or (EnteredDate not between '2/1/2006' and '2/28/2006'))
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
and DateEntered IS NOT NULL)
union all
select SUM(CC),
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')
or (not between '2/1/2006' and '2/28/2006'))
and DateEntered IS NOT NULL)
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(CC)
)
group by FeeSchedule, CustomerNumber

I forgot you needed the grand total by customer.

HTH

|||Shughes , I really appreciate your support. I have never used unions before, never had the need until now. I'll give it a try, thanks for really taking the time to show me this! You are saving my life here, I'm still trying to figure this thing out after an entire day!|||

I am getting:

Incorrect syntax near the keyword 'where'.

Msg 156, Level 15, State 1, Procedure SSRS_Get_Tot_InHouse2, Line 29

Incorrect syntax near the keyword 'where'.

let me see if I can fix it...

|||My bad, I added an extra closing parenthesis at the end of the where clause (after the NOT NULL). Sorry, but unable to test my end as do not have database. Try this:

select SUM(subTotal) * FeeSchedule, CustomerNumber
from
(
select SUM(PDC) as subTotal,
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate between '2/1/2006' and '2/28/2006')
or (EnteredDate not between '2/1/2006' and '2/28/2006'))
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
and DateEntered IS NOT NULL
union all
select SUM(CC),
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')
or (not between '2/1/2006' and '2/28/2006'))
and DateEntered IS NOT NULL
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(CC)
)
group by FeeSchedule, CustomerNumber

That should do it!
|||

the where clause needs to come before the group by:

select SUM(subTotal) * FeeSchedule, CustomerNumber

from

(

selectSUM(PDC) as subTotal,

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate between '2/1/2006' and '2/28/2006')

or (EnteredDate not between '2/1/2006' and '2/28/2006'))

and DateEntered IS NOT NULL

group by CustomerNumber, FeeSchedule

union all

selectSUM(CC),

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')

or (not between '2/1/2006' and '2/28/2006'))

and DateEnteredIS NOT NULL

group by CustomerNumber, FeeSchedule

)

group by FeeSchedule, CustomerNumber

Now I'm getting one error but can't see it:

Msg 156, Level 15, State 1, Line 18

Incorrect syntax near the keyword 'where'.

|||

in the end I should end up with only one sum, one result. It looks like your query will end up with

Customer tot

Customer tot

...

but that/s now what I want...anyway, still trying to fix the last error.

|||Thar should work. Are you still getting an error?|||

select SUM(subTotal) * FeeSchedule, CustomerNumber

from

(

select SUM(PDC) as subTotal,

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate between '2/1/2006' and '2/28/2006')

or (EnteredDate not between '2/1/2006' and '2/28/2006'))

and DateEntered IS NOT NULL

group byCustomerNumber,

FeeSchedule

union all

select SUM(CC),

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')

or (not between '2/1/2006' and '2/28/2006'))

and DateEnteredIS NOT NULL

group byCustomerNumber,

FeeSchedule

)

group byFeeSchedule,

CustomerNumber

same error but I don't see anything wrong:

Msg 156, Level 15, State 1, Line 18

Incorrect syntax near the keyword 'where'.