Showing posts with label query. Show all posts
Showing posts with label query. Show all posts

Thursday, March 29, 2012

Filter in stored procedure

I am trying to edit this query i already have that is fully functional for another report in reporting services. The report has visits sales reps made to stores, broken down by period, week and then date. Now, i need to filter it out by Visits. They want the Sales Reps who had less then 6 visits a day. How would i code that out in my stored procedure? When i have Stores and Account Status( which makes it pretty broken down). Any suggestions. Thanks..heres the code.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

END

SET NOCOUNT OFF

If dbo.Qry_Visits.Visits stores the cumulative count of the visits made then you can add 'Qry_Visits.Visits < 6' to the where clause. To make it more generic instead of hard coding values, you may want to have to edit the signature of the proc to have an input parameter that represents the number of visits also.

|||

try:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.Visits >= @.visit_req

END

SET NOCOUNT OFF

|||it does not show the cumulative. It shows one visit per sales person, per date. So its really broken down. So if i put <6 it will show everything, because each record has one visit. I only want the ones with less then 6 visits by a Sales Rep. Which means the sum of visits per Sales Rep, per day. How would i get that?|||

I don't really understand the data structure, but is possible to just do an aggregate similar to:

Code Snippet

Pseudo Code:

SELECT YourColumns, SUM(Visits) AS Visits
FROM AllThoseTables
GROUP BY YourColumns
HAVING SUM(Visits) > 5

|||

Then do:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.[SR Code] NOT IN(select [SR Code] from dbo.Qry_Visits

group by [SR Code]

having count(Visits) < @.visit_req)

END

SET NOCOUNT OFF

filter expression using measures

Hi guys,

I want to filter an MDX query based on a specific measure values, however, the MDX query designer allows you to filter only based on a dimension.

Is there any soulution to resolve this problem.

Sincerely,

--Amde

In the data tab for reporting services, select your dataset you want to filter by in the drop down list. Now, click the "..." to the right. In the filters tab, you can filter the dataset by an expression you provide.

For example, if you wanted to filter the results so that only customers with over 100 purchases were displayed, you could do this:

Expression: =Fields!NumberOfPurchases.value
Operator: >
Value: 100

You should be good to go. Good luck!!

|||thx!

Filter Expression

Hi,

I'm trying to create a query where the results are restricted by the date selected. When the user selects two different dates, the query works. However, if the user wanted to see receivals for only one day, the query returns nulls even though there are receivals for that day. Below is the code:

SELECT {{[Measures].[Tonnes]}} on 0,
NonEmptyCrossJoin({{Filter([Date].[Day].members, [Date].currentmember.name = "2006-10-16")}}, {{[Transaction].[Closing CarryOver].[IN].[Receival tonnes]}}, {{[Measures].[Tonnes]}}, 2) DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

The "2006-10-16" is the date selected by the user. Any ideas why the filter doesn't like just returned one date, would be extremely helpful! Thanks.

First, you should lose NonEmptyCrossJoin from the query, which is hurting here, and also may produce wrong results if you have more than one measure group.

The simplest query to do what you want is

SELECT {[Measures].[Tonnes]} on 0,
NON EMPTY {([Date].[Day].[2006-10-16], [Transaction].[Closing CarryOver].[IN].[Receival tonnes])} DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

Filter double results from query

We are working on an application which aggregates news articles from various sources. The problem is, when querying the database, is that there are a lot of double results because some of the sources get their news from the same press agency. To clarify things a bit here's a link to an example:

http://84.244.146.27/Zoekresultaten.aspx?SearchQuery=Amsterdam

As you can see, there are a lot of the same results. What we want is to filter out the double results irrespective of the source. In other words; when the query encounters the first result, display that and leave out the other same results.

I've tried filtering them out by using DISTINCT on the 'news header' / results, but that does not seem to work. Any ideas on how to get this done?

Any help is greatly appreciated!

Providing the aggregator select is an infrequent action, you can use a CURSOR within it to select just the first record to a buffer table which the page will read.

|||

Thanks Tatworth for your answer! I'm afraid that it sounds a bit like Chinese to me though...

The aggregator is run every couple of minutes and of course it would be best to filter the results in that step, then filtering on querying results. There's a slight problem though with the aggregator script. It's in PHP :( and PHP and MSSQL don't play along that nicely with eachother as far as I have experienced.

Is there a way to filter within the select query?

|||

Yes, as an example:

14-11-2007

10:52

Marokkaanse vaders in actie in Amsterdam-West

BNdestem BNdestem

|||

If your answer is 2005 or Express, and you want the earliest record, this syntax will do it for you:

SELECT *

FROM (

SELECT {the fields from your original query},row_number() OVER (PARTITION BY {the list of key fields} ORDER BY {Your datetime field} ASC) AS TheRank

{the rest of your original query}

) t1

WHERE TheRank=1

Example:

SELECT *FROM (SELECT NewsDate,NewsTitle,NewsLink,row_number()OVER (PARTITIONBY NewsTitleORDER BY NewsDateASC)AS TheRankFROM NewsTable) t1WHERE TheRank=1
|||

Thanks very much Motley! It works like a charm!

Filter by uniqueidentifier in DPW's don't work, and yet sometimes do?

According to Microsoft, when executing a query against a linked server, any
filtering (where condition) against a uniqueidentifier or bit columns cannot
be applied on the remote server. Instead, all rows in the remote server's
table will be brought across the wire, and the filter condition will be
applied locally.
This can be demonstrated by declaring a view
CREATE VIEW ActionItems AS SELECT * FROM
RemoteServer.dbo.Database.ActionItems
that queries the table
CREATE TABLE [ActionItems1] (
[ActionItemID] [uniqueidentifier] NOT NULL CONSTRAINT
[DF_ActionItems1_ActionItemID] DEFAULT (newid()),
[AssociatedEmployeeID] [uniqueidentifier] NULL ,
[AssociatedActionItemID] [uniqueidentifier] NOT NULL , --clustered
[ParentActionItemID] [uniqueidentifier] NULL ,
CONSTRAINT [PK_ActionItems1_1] PRIMARY KEY NONCLUSTERED
(
[ActionItemID]
)
)
Now, i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE ActionItemID = '28E5D33A-8946-48A6-BEFC-A0C07914D440'
StmtText
------
|--Filter(WHERE[Jango].[CasinoHR].[dbo].[ActionItems1].[ActionItemID]=28E5D33A-8946-48A6-BEFC-A0C07914D440))
|--Remote Query(SOURCEJango), QUERYSELECT Tbl1001."ActionItemID"
Col1003 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001))
And as expected and documented, all rows are returned, then a filter is
applied to get the one row.
But wait! Now i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedEmployeeID = '4C5CD857-8DD4-4279-B017-7CF1C49B23D9'
StmtText
--------
|--Remote Query(SOURCEJango), QUERYSELECT Tbl1001."ActionItemID"
Col1004 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001 WHERE
Tbl1001."AssociatedEmployeeID"=?))
And not as expected, and opposite as documented, only the matching rows are
returned - and the filter is applied on the remote server.
But wait! Now i will try to filter by a uniqueidentifer:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedEmployeeID is not null
StmtText
-------
|--Filter(WHERE[Jango].[CasinoHR].[dbo].[ActionItems1].[AssociatedEmployeeID]<>NULL))
|--Remote Query(SOURCEJango), QUERYSELECT
Tbl1001."AssociatedEmployeeID" Col1005,Tbl1001."ActionItemID" Col1003 FROM
"CasinoHR"."dbo"."ActionItems1" Tbl1001))
And as expected and documented, all rows are returned, then a filter is
applied to get the one row.
But wait! Now i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedActionItemID = 'C8A32A8F-2AF8-4B4A-9954-27274F4F6B01'
StmtText
-------
|--Remote Query(SOURCEJango), QUERYSELECT Tbl1001."ActionItemID"
Col1004 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001 WHERE
Tbl1001."AssociatedActionItemID"=?))
And not as expected, and opposite as documented, only the matching rows are
returned - and the filter is applied on the remote server.
So what's up? Either SQL Server can pass a filter condition on a
uniqueidentifier field to the remote server, or it cannot. Which is it? The
documentation says it cannot. The documentation does not say that it cannot
except for sometimes - who's logic isn't apparent or logical.
Oops, DPW should be DPV
Distributed Partitioned Views
sql

Filter by uniqueidentifier in DPW's don't work, and yet sometimes do?

According to Microsoft, when executing a query against a linked server, any
filtering (where condition) against a uniqueidentifier or bit columns cannot
be applied on the remote server. Instead, all rows in the remote server's
table will be brought across the wire, and the filter condition will be
applied locally.
This can be demonstrated by declaring a view
CREATE VIEW ActionItems AS SELECT * FROM
RemoteServer.dbo.Database.ActionItems
that queries the table
CREATE TABLE [ActionItems1] (
[ActionItemID] [uniqueidentifier] NOT NULL CONSTRAINT
[DF_ActionItems1_ActionItemID] DEFAULT (newid()),
[AssociatedEmployeeID] [uniqueidentifier] NULL ,
[AssociatedActionItemID] [uniqueidentifier] NOT NULL , --clustered
[ParentActionItemID] [uniqueidentifier] NULL ,
CONSTRAINT [PK_ActionItems1_1] PRIMARY KEY NONCLUSTERED
(
[ActionItemID]
)
)
Now, i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE ActionItemID = '28E5D33A-8946-48A6-BEFC-A0C07914D440'
StmtText
----
----
|--Filter(WHERE[Jango].[CasinoHR].[dbo].[ActionItems1].
1;ActionItemID]=28E5D33A-8946-48A6-BEFC-A0C07914D440))
|--Remote Query(SOURCEJango), QUERYSELECT Tbl1001."ActionItemID"
Col1003 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001))
And as expected and documented, all rows are returned, then a filter is
applied to get the one row.
But wait! Now i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedEmployeeID = '4C5CD857-8DD4-4279-B017-7CF1C49B23D9'
StmtText
----
----
--
|--Remote Query(SOURCEJango), QUERYSELECT Tbl1001."ActionItemID"
Col1004 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001 WHERE
Tbl1001."AssociatedEmployeeID"=?))
And not as expected, and opposite as documented, only the matching rows are
returned - and the filter is applied on the remote server.
But wait! Now i will try to filter by a uniqueidentifer:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedEmployeeID is not null
StmtText
----
----
--
|--Filter(WHERE[Jango].[CasinoHR].[dbo].[ActionItems1].
1;AssociatedEmployeeID]<>NULL))
|--Remote Query(SOURCEJango), QUERYSELECT
Tbl1001."AssociatedEmployeeID" Col1005,Tbl1001."ActionItemID" Col1003 FROM
"CasinoHR"."dbo"."ActionItems1" Tbl1001))
And as expected and documented, all rows are returned, then a filter is
applied to get the one row.
But wait! Now i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedActionItemID = 'C8A32A8F-2AF8-4B4A-9954-27274F4F6B01'
StmtText
----
----
--
|--Remote Query(SOURCEJango), QUERYSELECT Tbl1001."ActionItemID"
Col1004 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001 WHERE
Tbl1001."AssociatedActionItemID"=?))
And not as expected, and opposite as documented, only the matching rows are
returned - and the filter is applied on the remote server.
So what's up? Either SQL Server can pass a filter condition on a
uniqueidentifier field to the remote server, or it cannot. Which is it? The
documentation says it cannot. The documentation does not say that it cannot
except for sometimes - who's logic isn't apparent or logical.Oops, DPW should be DPV
Distributed Partitioned Views

Filter by uniqueidentifier in DPW's don't work, and yet sometimes do?

According to Microsoft, when executing a query against a linked server, any
filtering (where condition) against a uniqueidentifier or bit columns cannot
be applied on the remote server. Instead, all rows in the remote server's
table will be brought across the wire, and the filter condition will be
applied locally.
This can be demonstrated by declaring a view
CREATE VIEW ActionItems AS SELECT * FROM
RemoteServer.dbo.Database.ActionItems
that queries the table
CREATE TABLE [ActionItems1] (
[ActionItemID] [uniqueidentifier] NOT NULL CONSTRAINT
[DF_ActionItems1_ActionItemID] DEFAULT (newid()),
[AssociatedEmployeeID] [uniqueidentifier] NULL ,
[AssociatedActionItemID] [uniqueidentifier] NOT NULL , --clustered
[ParentActionItemID] [uniqueidentifier] NULL ,
CONSTRAINT [PK_ActionItems1_1] PRIMARY KEY NONCLUSTERED
(
[ActionItemID]
)
)
Now, i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE ActionItemID = '28E5D33A-8946-48A6-BEFC-A0C07914D440'
StmtText
------
|--Filter(WHERE:([Jango].[CasinoHR].[dbo].[ActionItems1].[ActionItemID]=28E5D33A-8946-48A6-BEFC-A0C07914D440))
|--Remote Query(SOURCE:(Jango), QUERY:(SELECT Tbl1001."ActionItemID"
Col1003 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001))
And as expected and documented, all rows are returned, then a filter is
applied to get the one row.
But wait! Now i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedEmployeeID = '4C5CD857-8DD4-4279-B017-7CF1C49B23D9'
StmtText
--------
|--Remote Query(SOURCE:(Jango), QUERY:(SELECT Tbl1001."ActionItemID"
Col1004 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001 WHERE
Tbl1001."AssociatedEmployeeID"=?))
And not as expected, and opposite as documented, only the matching rows are
returned - and the filter is applied on the remote server.
But wait! Now i will try to filter by a uniqueidentifer:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedEmployeeID is not null
StmtText
-------
|--Filter(WHERE:([Jango].[CasinoHR].[dbo].[ActionItems1].[AssociatedEmployeeID]<>NULL))
|--Remote Query(SOURCE:(Jango), QUERY:(SELECT
Tbl1001."AssociatedEmployeeID" Col1005,Tbl1001."ActionItemID" Col1003 FROM
"CasinoHR"."dbo"."ActionItems1" Tbl1001))
And as expected and documented, all rows are returned, then a filter is
applied to get the one row.
But wait! Now i will try to filter by a uniqueidentifier:
SELECT ActionItemID FROM ActionItems
WHERE AssociatedActionItemID = 'C8A32A8F-2AF8-4B4A-9954-27274F4F6B01'
StmtText
-------
|--Remote Query(SOURCE:(Jango), QUERY:(SELECT Tbl1001."ActionItemID"
Col1004 FROM "CasinoHR"."dbo"."ActionItems1" Tbl1001 WHERE
Tbl1001."AssociatedActionItemID"=?))
And not as expected, and opposite as documented, only the matching rows are
returned - and the filter is applied on the remote server.
So what's up? Either SQL Server can pass a filter condition on a
uniqueidentifier field to the remote server, or it cannot. Which is it? The
documentation says it cannot. The documentation does not say that it cannot
except for sometimes - who's logic isn't apparent or logical.Oops, DPW should be DPV
Distributed Partitioned Views

Tuesday, March 27, 2012

Filter by months & days without years

I have an application that holds BirthDates.
Is there a way to query for all birthdates between two dates without
considering the year ?
Like: Between "April 1" and Dec 10". I know who to query between two dates,
but it considers the year.
CASystemsUse
Select *
From <TableName>
Where DateValue Between
Stuff(convert(VarChar(8), DateColumn, 112), 5, 4, '0401') And
Stuff(convert(VarChar(8), DateColumn, 112), 5, 4, '1210')
"CASystems" wrote:

> I have an application that holds BirthDates.
> Is there a way to query for all birthdates between two dates without
> considering the year ?
> Like: Between "April 1" and Dec 10". I know who to query between two dates
,
> but it considers the year.
> --
> CASystemssql

Filter by concatenated field

How can I filter a query using a cocatenated field? For instance, I have a
query (for instance):
Select id,
ClientCity + ' ' + ClientState + ' ' + ClientZip as ClientInfo
from tblClients
I want to be able to say "where ClientInfo like '% ' + @.Parameter + '%'"
so that I can query by "Chicago" or "60001" as parameters
Thanks for your help.The where clause is parsed before the select list, so you can't do that
because the optimizer won't know what you're talking about (or in rare
cases, you will be filtering on the wrong column, e.g. if you actually had a
clientInfo column with different information). A couple of options:
SELECT
id,
ClientInfo = ClientCity + ' ' + ClientState + ' ' + ClientZip
FROM
tblClients
WHERE
ClientCity + ' ' + ClientState + ' ' + ClientZip LIKE '%'+@.Parameter+'%'
or
SELECT
id,
ClientInfo
FROM
(
SELECT id,
ClientInfo = ClientCity + ' ' + ClientState + ' ' + ClientZip
FROM
tblClients
) subquery
WHERE
ClientInfo LIKE '%'+@.Parameter+'%'
"et" <eagletender2001@.yahoo.com> wrote in message
news:e7MI72JEGHA.524@.TK2MSFTNGP09.phx.gbl...
> How can I filter a query using a cocatenated field? For instance, I have
> a query (for instance):
> Select id,
> ClientCity + ' ' + ClientState + ' ' + ClientZip as ClientInfo
> from tblClients
> I want to be able to say "where ClientInfo like '% ' + @.Parameter + '%'"
> so that I can query by "Chicago" or "60001" as parameters
> Thanks for your help.
>
>

Filter by a concatenated column?

I am using a query in a stored procedure, where the user can dictate which
field they want to sort by. That I can do. What I also want to do is
filter out the null values for the selected field, and one of the fields is
a concatenated result. This is the query:
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
so the stored procedure would be something like this:
CREATE PROCEDURE [dbo].ClientSearch]
@.SearchField as varchar(50)='' AS
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
where @.SearchField is not null order by @.SearchField
If the user is selecting FullName, I get an error saying the @.SearchField is
an invalid column name -- however this works if other field names are
selected, and it also works if I only want to order by FullName, but not
filter.
Thanks for your help.I hope you are using exec since its a dyamic query.
fullname is a concatenationo of two fields LName + ', ' + FName
therefore LName + ', ' + FName is not null does not work.
make sure that LName is not null and FNAME is not NULL
HTH
Rajesh Peddireddy.
"news.microsoft.com" wrote:

> I am using a query in a stored procedure, where the user can dictate which
> field they want to sort by. That I can do. What I also want to do is
> filter out the null values for the selected field, and one of the fields i
s
> a concatenated result. This is the query:
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> so the stored procedure would be something like this:
> CREATE PROCEDURE [dbo].ClientSearch]
> @.SearchField as varchar(50)='' AS
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> where @.SearchField is not null order by @.SearchField
> If the user is selecting FullName, I get an error saying the @.SearchField
is
> an invalid column name -- however this works if other field names are
> selected, and it also works if I only want to order by FullName, but not
> filter.
> Thanks for your help.
>
>

Filter before export

Hi,
I'm in the process of automating the export of a report to a PDF file.
The number of files is enormous and to prevent the query from executing over
and over again I was wondering whether the following scenario is possible.
- Get all records for a given report
- Filter by a specific value and export, repeat process for all necessary
values of that field.
Thanks for any input
Kind regardsYou could definitely do it programmatically from code that calls the RS Web
service...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> Hi,
> I'm in the process of automating the export of a report to a PDF file.
> The number of files is enormous and to prevent the query from executing
over
> and over again I was wondering whether the following scenario is possible.
> - Get all records for a given report
> - Filter by a specific value and export, repeat process for all necessary
> values of that field.
> Thanks for any input
> Kind regards
>|||Hi,
Thanks for the answer.
Do you have any idea in which Namespace I can find the Filter property?
Thanks
"Wayne Snyder" schreef:
> You could definitely do it programmatically from code that calls the RS Web
> service...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
> news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> > Hi,
> >
> > I'm in the process of automating the export of a report to a PDF file.
> > The number of files is enormous and to prevent the query from executing
> over
> > and over again I was wondering whether the following scenario is possible.
> >
> > - Get all records for a given report
> > - Filter by a specific value and export, repeat process for all necessary
> > values of that field.
> >
> > Thanks for any input
> >
> > Kind regards
> >
> >
>
>|||I would use a parameter, passed in via URL. THen use the parameter to do the
grouping...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> Hi,
> I'm in the process of automating the export of a report to a PDF file.
> The number of files is enormous and to prevent the query from executing
over
> and over again I was wondering whether the following scenario is possible.
> - Get all records for a given report
> - Filter by a specific value and export, repeat process for all necessary
> values of that field.
> Thanks for any input
> Kind regards
>|||Hi,
Wouldn't this trigger the query every time?
I'll try to give some more information.
We have about 1500 offices which all need a bunch of pre generated PDF
reports.
I want a report that returns only a couple rows per office to come back as 1
big Dataset that can be filtered clientside and then exported. So every
'filter' operation extracts the information for a specific office and then
exports it to a PDF. I do not want these queries to execute 1500 times
creating all the connection overhead.
eg.
Report returns 10 records per office
Returns 15000 records in the RS Dataset
The C# program (using Reporting Services WebService) filters out the 10
records and exports these to a PDF for every office
Kind regards,
Wesley
"Wayne Snyder" wrote:
> I would use a parameter, passed in via URL. THen use the parameter to do the
> grouping...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
> news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> > Hi,
> >
> > I'm in the process of automating the export of a report to a PDF file.
> > The number of files is enormous and to prevent the query from executing
> over
> > and over again I was wondering whether the following scenario is possible.
> >
> > - Get all records for a given report
> > - Filter by a specific value and export, repeat process for all necessary
> > values of that field.
> >
> > Thanks for any input
> >
> > Kind regards
> >
> >
>
>

Filter & Parameter pane missing in MDX Query Designer in SQL Reporting Services

I've posted this in the SQL Reporting Services forum as well, but thought I'd try here as some of you may be using Analysis server with SRS also. I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?

Additional information on this thread. The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?

Filter

I need a query that returns every n record from the database and the last record.

For every n-th is easy, but I need a

WHERE Index % n = 0

but I need a last one too.

Any Help.

How about a UNION statement? The new code you want is in green.

SELECT Col1, idx

FROM MyTable

WHERE idx % 12 = 0

UNION

SELECT Col1, idx

FROM MyTable

WHERE idx = (SELECT MAX(idx) FROM MyTable)

|||

In 2005, you can do something like:

select *
from (

select row_number() over (order by object_id) as rowNum,
object_id
from sys.objects

) as objects
where rowNum % 5 = 0
or rowNum = (select count(*) from sys.objects)

There is a (more messy) way to do it in 2000 also. The key is that tables don't have order, so you will need to define an order in order to calculate N.

Filling up ListControl from a SQL query

Hello,

I use MFC ListControl. Is there any way to fill it up using SQL query without adding one row at a time?

All I could find was m_List.InsertItem(...) and m_List.SteItemText(...)

Are there any other ways?

Thanks

One possible way to do this is using virtual listctrl:

Fill(insert) the list items with bound data addresses first, then do SQL fetch for the result.

|||

Thanks a lot for the reply.

Could you give me an example of how to do that or point me to where I can read about it. Thanks again

|||

Following link maybe helpful

http://www.codeproject.com/listctrl/virtuallist.asp

Filling out gaps in a date based select statement

Hi,
I have a query which simply returns a count of a particular field by w
number. My query as it works fine however for ws where there are no data
it obviously doesn't return a row. My problem is that is I wish for the
statement to return a zero for those ws with no data - I'm just not sure
how to do this.
I realise that I can achieve it by creating a table listing w numbers and
joining my query on that table. However I'd rather not create a table like
that. Is there another approach that will let me achieve the same thing?
Currently my query is:
SELECT datepart(wk, datein) AS Wno , COUNT(movementno) AS ContainersIn
STOCK stock
WHERE year(datein) = '2005'
GROUP BY datepart(wk, datein)
which returns:
W ContainersIn
29 3
31 1
34 5
35 4
36 8
37 6
38 3
39 1
40 12
Thanks,
Chris.See if this helps you
http://weblogs.sqlteam.com/jeffs/ar...09/12/7755.aspx
Madhivanan|||Why don't you want to create a table for this? I would recommend you
keep an auxiliary numbers table (a table integers from 0 to some
arbitrarily large number) for exactly such an application. A Calendar
table is also very useful to have around.
A SELECT statement can't create data out of nothing. Some other options
are: use a derived table in your query with a UNION of the numbers 1 -
52 ; create a view containing those numbers ; create a table-valued
function that returns a parameterized range of numbers ; extract the
numbers from system table(s) (not recommended). Creating the permanent
table would be my favourite - it's fast, easy, portable and much more
concise and transparent than any of the others.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128332658.694445.44410@.g47g2000cwa.googlegroups.com...
> Why don't you want to create a table for this? I would recommend you
> keep an auxiliary numbers table (a table integers from 0 to some
> arbitrarily large number) for exactly such an application. A Calendar
> table is also very useful to have around.
> A SELECT statement can't create data out of nothing. Some other options
> are: use a derived table in your query with a UNION of the numbers 1 -
> 52 ; create a view containing those numbers ; create a table-valued
> function that returns a parameterized range of numbers ; extract the
> numbers from system table(s) (not recommended). Creating the permanent
> table would be my favourite - it's fast, easy, portable and much more
> concise and transparent than any of the others.
> --
> David Portas
> SQL Server MVP
> --
>
Thank your for your help David - most appreciated. I guess I find the idea
of creating a new table "messy". No idea why - just something that has
always struck me as something to avoid. A habit I will have to get out of!
thanks again
Chris.

Friday, March 23, 2012

Fill DataSet takes forever, query db 7 sec

Hi,

I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!

What's going on?

Any thoughts highly appreciated.

t.i.a.,
ratjetoes.

What is the query..?|||

Hi ratjetoes,

To troubleshoot this problem, we can do the following:

1. Use Profiler to track every activity of the query, including opening the connection. Check in the trace log to see if it is the query that takes 7 sec.
2. If it is the query that takes long time running, please put it in Query Analyzer to see which part is blocking to whole query.

HTH. If anything is unclear, please feel free to mark the post as Unanswered and post your reply. Thanks.

Friday, March 9, 2012

File->Open->File always opens new Connection/window

In Query Analyzer, a File->Open would replace the current query with a new query (after appropriate warning if necessary)

In Management Studio, File->Open->File always creates a new connection in a new query window. This is a pain a majority of the time as I want to replace current query with another. It seems like the File->Open->File with New Connection would be used for creating a new connection. I do see the difference between the two option, just that the first is too close to the second. It is annoying as I have to go find and then close my prior query window.

I've searched but cannot find a way around this. Any help would be most appreciated

Thanks

Carol

Download Service Pack 2:

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

|||

Good thought but same behavior. I am running Microsoft SQL Server Management Studio 9.00.3042.00

But it sounds like this works differently for you than for me. Can you verify this and if so, can you think of anything you may have done to alter the default behavior?

Thanks
Carol

|||

I didn't read you question correctly. Sorry.

Before SP2 you had to connect. log in, for each file you opened. After SP2 you can open without loging in each time.

Each file opens in a new window.

|||

This doesn't feel like a very popular scenario to me. Most MDI interfaces these days open new windows instead of replacing the content of the current window. I'd suggest that if this is an important scenario to you that you report it on Microsoft Connect and have others vote on it.

Microsoft Connect:
http://connect.microsoft.com/SQLServer/Feedback/

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Paul,

Thanks for the response. I found an existing Microsoft Connect thread (127103) where the request stated "In SQL Server 2000 - Query Analyzer when you open a saved SQL script the script will open over the top of the current window. If the current window has an unsaved or modified script then it will prompt you to save before overwriting the contents of the window with the opened script. This has two advanatages to the user:
i) there are not multiple screens opened
ii) the script opens in the current database"

You posted a reponse on March 31, 2006 stating
"This has been addressed for SP2. Our fix should make it into CTP1 for Service Pack 2. If you could, please install the CTP1 when it is available and verify that your specific issue with Open File has been resolved. With this fix, we've also improved areas such as DDE, Drag and Drop and sqlwb.exe command line behavior."

There is a follow-up response by Microsoft on July 24, 2006 that goes into specifics. Reading this, I believe that SP2 provided improvements, but did not address the original request, which is to open a script over top of the current window. So while having it not always ask for connection info is definitely an improvement, we still would like it to not open a new window.

ID 127103 is closed but had a 4.56 rating. Would you recommend I open a new one?

Thanks
Carol