How can I combine two report parameters into a string?

Sorry, I am new to the SQL reporting. I am not sure whether I can combine
two report parameters in a string and then use it in my querystring or not.
For example, I have a report paramter called "Month" and the other one
called "Year". Obviously I want to make them as a start date for my report
and them pass this into my querystring. Can I achieve this without writing a
stored procedure for this purpose?
ThanksUse the command type of text.
exec sp_my_stored_procedure @.Month + @.Year, @.Param3, @.Param4
or
select * from table where date = @.Month + @.Year
--
Harolds
"Laipond" wrote:
> Sorry, I am new to the SQL reporting. I am not sure whether I can combine
> two report parameters in a string and then use it in my querystring or not.
> For example, I have a report paramter called "Month" and the other one
> called "Year". Obviously I want to make them as a start date for my report
> and them pass this into my querystring. Can I achieve this without writing a
> stored procedure for this purpose?
> Thanks|||Harolds,
Thanks for the reply. I have tried the way you mentioned below. Following
is what I did and the result I got:
Case 1. In "Define Query Parameter" dialog box, I type 3/1/2005 for @.month,
and leave @.year blank. The reporting service can successfully pull the data.
Case 2. In "Define Query Parameter" dialog box. I type 3/1/ for @.month and
2005 for @.year. An error happen: "Application uses a value of the wrong type
for the current operation."
Is there any other function I can apply for this query string to make it work?
Thanks
Laipond
"Harolds" wrote:
> Use the command type of text.
> exec sp_my_stored_procedure @.Month + @.Year, @.Param3, @.Param4
> or
> select * from table where date = @.Month + @.Year
> --
> Harolds
>
> "Laipond" wrote:
> > Sorry, I am new to the SQL reporting. I am not sure whether I can combine
> > two report parameters in a string and then use it in my querystring or not.
> >
> > For example, I have a report paramter called "Month" and the other one
> > called "Year". Obviously I want to make them as a start date for my report
> > and them pass this into my querystring. Can I achieve this without writing a
> > stored procedure for this purpose?
> >
> > Thanks|||You are getting that error because you have @.Month and/or @.Year set to a
datetime data type instead of string.
--
Harolds
"Laipond" wrote:
> Harolds,
> Thanks for the reply. I have tried the way you mentioned below. Following
> is what I did and the result I got:
> Case 1. In "Define Query Parameter" dialog box, I type 3/1/2005 for @.month,
> and leave @.year blank. The reporting service can successfully pull the data.
> Case 2. In "Define Query Parameter" dialog box. I type 3/1/ for @.month and
> 2005 for @.year. An error happen: "Application uses a value of the wrong type
> for the current operation."
> Is there any other function I can apply for this query string to make it work?
> Thanks
> Laipond
> "Harolds" wrote:
> > Use the command type of text.
> > exec sp_my_stored_procedure @.Month + @.Year, @.Param3, @.Param4
> > or
> > select * from table where date = @.Month + @.Year
> > --
> > Harolds
> >
> >
> > "Laipond" wrote:
> >
> > > Sorry, I am new to the SQL reporting. I am not sure whether I can combine
> > > two report parameters in a string and then use it in my querystring or not.
> > >
> > > For example, I have a report paramter called "Month" and the other one
> > > called "Year". Obviously I want to make them as a start date for my report
> > > and them pass this into my querystring. Can I achieve this without writing a
> > > stored procedure for this purpose?
> > >
> > > Thanks

How can I combine three fields together of dif datatypes

I want to combine three fields together as a description in a select statement. When I try using the & or + I'm told that the datatypes are incompatable. How can I join them?

(Item_Description is Nvarchar, Item_Cost is Money, Is_Active is bit)

Select Item_Description&' '& Item_Cost&' '& Is_ActiveAsDescriptionFROM tblItemList

I tried Casting this but same incompatable message.

Select Item_Description&' '& CAST(Item_Cost AS NVARCHAR) &' '& CAST(Is_Active AS NVARCHAR) AsDescriptionFROM tblItemList

What I'm hoping to end up with is:

Brake Pedal, $36.00, True

Did you try using the + operator after casting?

Select
Item_Description + ' ' + CAST(Item_Cost AS NVARCHAR) +' '+ CAST(Is_Active AS NVARCHAR) As Description
FROM
tblItemList

What are the datatypes of the above fields?

|||

Ok, Scott, I tried the + again and it did work, however, can you point me in the direction of how to format the money to $36.00 and the bit to True or False. I would like to end up with:

Brake Pedal, $36.00, True

|||

SELECT
Item_Description+', $'+Cast(Item_Costasnvarchar)+', '+ dbo.ufnGetBoolean(Is_Active)
FROM
tblItemList

The ufnGetBoolean function is:

CREATEFUNCTION [dbo].[ufnGetBoolean](@.BitValue [bit])
RETURNSvarchar(5)
AS
-- Returns a string value based on the bit value passed
BEGIN
DECLARE @.retvarchar(5);
SET @.ret='false'

IF(@.BitValue= 1)
SET @.ret='true'

RETURN @.ret

END;

Let me know if that works.

How can I combine different rows?

Hi!

I have a table looking like

(username) (account number) (start date) (end date) (product)

wich I can have up to 4 lines for the same client.

I wist to transfert those lines into a new table looking like

(username) (account number) (start date 1) (end date 1) (product 1)
(start date 2) (end date 2) ... (product 4)

How (in SQL) I could do it?>I have a table looking like ..

Please post DDL instead of your personal narrative. If you had done
the talbe properly, i mgiht look like this:

CREATE TABLE AccountHistory
(acct_nbr INTEGER NOT NULL,
product_nbr INTEGER NOT NULL,
product_cnt INTEGER DEFAULT 1 NOT NULL
CHECK(product_cnt BETWEEEN ! AND 4),
PRIMARY KEY (acct_nbr, product_nbr, product_cnt),
user_name VARCHAR(25) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));

I left out the REFERENCES clause you would need and some other
things.

Quote:

Originally Posted by

Quote:

Originally Posted by

>which I can have up to 4 lines [sic] for the same client. <<

Lines appear on a paper form or an input screen; a table has rows.
You need a constraint to enforce this rule.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I wish to transfer those lines into a new table looking like .. <<

You also failed to give any rules for sorting the repeating groups.
But th real question is why are you doing this at all?? That would
violate First Normal Form (1NF). This is not a good way to write
SQL.

How can I combine 2 SELECT statements into 1 ?

Please consider the following SELECT statements; is it possible to combine
them into a sole SELECT statement returning 4 columns ? Thank you.
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
, MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
, ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0) AS ItvDuree
FROM dbo.InterventionDI
WHERE (CodEtatIntervention = 'F')
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
--
Gilbert.HI
Use UNION or UNION ALL
Cheers
vinu
"Gilbert" <gilbert@.nospam.nospam> wrote in message
news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
> Please consider the following SELECT statements; is it possible to combine
> them into a sole SELECT statement returning 4 columns ? Thank you.
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> --
> Gilbert.|||Sorry but, as far as I know, with UNION I will get more lines with always 3
columns. This is not why I need. I need the same number of lines
(approximatively) with 4 columns.
--
Gilbert.
"vinu" wrote:

> HI
> Use UNION or UNION ALL
> Cheers
> vinu
>
> "Gilbert" <gilbert@.nospam.nospam> wrote in message
> news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
>
>|||Please post DDL, sample data and expected results.
You might also consider joining the two queries as derived tables on common
columns (e.g. ItvNumAnee and ItvNumMois), but don't let this suggestion fool
you into thinking I have any idea what you really want. Stop us guessing and
explain your goals.
ML
http://milambda.blogspot.com/|||I thought that giving the SELECT statements was enough details; sorry. I
intend to do monthly statistics on a table storing user requests. The first
query gives the number of requests per month. The second query gives the
average delay of the answer per month, for all requests that are closed (thi
s
is why there is a WHERE clause). If I execute 2 independent SELECT statement
s
I receive 2 tables with 3 columns, each line displaying the value for a
specific month. What I would like to do is to execute only 1 SELECT, giving
me 1 table of 4 columns (year, month, number of requests, and average delay)
,
each line displaying the values for a specific month. Thank you for your hel
p.
--
Gilbert.
"ML" wrote:

> Please post DDL, sample data and expected results.
> You might also consider joining the two queries as derived tables on commo
n
> columns (e.g. ItvNumAnee and ItvNumMois), but don't let this suggestion fo
ol
> you into thinking I have any idea what you really want. Stop us guessing a
nd
> explain your goals.
>
> ML
> --
> http://milambda.blogspot.com/|||Ok, I see. But we still need to understand how the data in the two queries
can be joined to form one valid result-set.
That's why we need to see the DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||OK. Here is the DDL (simplified by hand to remove unuseful columns and
constraints) :
CREATE TABLE [InterventionDI] (
[NumIntervention] [int] IDENTITY (1, 1) NOT NULL ,
[DhEnregistrementIntervention] [datetime] NOT NULL CONSTRAINT
[DF_InterventionDI_DhEnregistrementInter
vention] DEFAULT (getdate()),
[DhClotureIntervention] [datetime] NULL ,
[CodEtatIntervention] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL,
CONSTRAINT [PK_InterventionDI] PRIMARY KEY NONCLUSTERED
(
[NumIntervention]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO
Here is a set on 10 records (INSERT created by hand) :
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (18, '13/09/2002 09:52:18', '20/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (19, '13/09/2002 09:53:18', '21/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (20, '14/09/2002 09:54:18', '20/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (21, '14/09/2002 09:55:18', '15/09/2002 16:53:57', 'O')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (22, '14/09/2002 09:56:18', '15/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (23, '14/09/2002 09:57:18', '18/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (24, '13/10/2002 09:58:18', '20/09/2003 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (25, '13/10/2002 09:59:18', '20/09/2004 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (26, '13/01/2003 09:12:18', '20/09/2005 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (27, '13/01/2003 09:22:18', '20/09/2006 16:53:57', 'F')
Thank you.
--
Gilbert.
"ML" wrote:

> Ok, I see. But we still need to understand how the data in the two queries
> can be joined to form one valid result-set.
> That's why we need to see the DDL and sample data:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML
> --
> http://milambda.blogspot.com/|||Based on what the two queries have in common this might give the correct
result:
select Demande.Anee
,Demande.Mois
,Demande.ItvQteDemande
,Duree.ItvDuree
from (
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
) Demande (Anee, Mois, ItvQteDemande)
inner join (
SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
, MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
, ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0) AS ItvDuree
FROM dbo.InterventionDI
WHERE (CodEtatIntervention = 'F')
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
) Duree (Anee, Mois, ItvDuree)
on (Duree.Anee = Demande.Anee)
and (Duree.Mois = Demande.Mois)
order by Demande.Anee
,Demande.Mois
Please, validate this against a sufficient amount of cases before using it
in production.
ML
http://milambda.blogspot.com/|||UNTESTED
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
, (
case when CodEtatIntervention = 'F' then
ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0)
ELSE NULL END
)AS ItvDuree
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
"Gilbert" <gilbert@.nospam.nospam> wrote in message
news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
> Please consider the following SELECT statements; is it possible to combine
> them into a sole SELECT statement returning 4 columns ? Thank you.
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> --
> Gilbert.|||Wonderful ! Thank you for having teached me something new.
--
Gilbert.
"ML" wrote:

> Based on what the two queries have in common this might give the correct
> result:
> select Demande.Anee
> ,Demande.Mois
> ,Demande.ItvQteDemande
> ,Duree.ItvDuree
> from (
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ) Demande (Anee, Mois, ItvQteDemande)
> inner join (
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ) Duree (Anee, Mois, ItvDuree)
> on (Duree.Anee = Demande.Anee)
> and (Duree.Mois = Demande.Mois)
> order by Demande.Anee
> ,Demande.Mois
> Please, validate this against a sufficient amount of cases before using it
> in production.
>
> ML
> --
> http://milambda.blogspot.com/sql