Dynamic Sets? (i.e. Where X In (@set))
Jul 20, 2005
Hi,
I've tried without success to create a function where one of the
parameters determines the final line of a select statement.
The final line includes a 'where referencenumber in (@set)' line.. and
@set is the parameter I wish to use which is a string.
If the set is just one value, it works fine, however with anything more
than that I just get no records returned.. If I output the parameter,
and copy and paste that into query analyser I do get the correct result,
so it's nothing to do with string delimiters etc..
Any ideas to fix this problem? Or am I going to have to dynamic sql the
whole thing?
Cheers,
Chris
View 2 Replies
ADVERTISEMENT
Sep 3, 2007
Hi,
I'm using a matrix report where in i want to use two datasets in the same report. How can i make the dataset dynamic for a single report.
Regards
View 1 Replies
View Related
May 21, 2008
Hello,
I'm new to MDX and was hoping someone could answer this question. I'm working on an MDX query, a simplified version is below.
What I'm trying to do is to pull 2 sets, based on a date range. Based on those, I'd like to see the total "cost" measure for each set, and divide one by another.
What I'm finding is that when I run the query below, the cost is broken out by month. So, my select statement gives me the cost for January 2008 and February 2008 in the CurrentPeriod set, and for January, February, etc. 2007 in the PriorPeriodData set. What I want instead is to get the total "cost" measure for all the months in each set. I believe this is what I'd get if I put the date range in a subcube or a where clause - but in that case, I wouldn't be working with 2 distinct sets.
Is there some other way to write this, or to combine all the months in the set before the measures?
WITH
SET [CurrentPeriod] AS
'{[Activity Date].[Date].&[2008-01-01T00:00:00]:[Activity Date].[Date].&[2008-02-01T00:00:00]}'
SET [PriorPeriod] AS
'{[Activity Date].[Date].&[2007-01-01T00:00:00]:[Activity Date].[Date].&[2007-12-01T00:00:00]}'
MEMBER [Measures].[Cost Variance] AS
'([CurrentPeriod],[Measures].[Cost]) /
([PriorPeriodData],[Measures].[Cost])'
SELECT NON EMPTY {
([CurrentPeriod],[Measures].[Cost]),
([PriorPeriodData],[Measures].[Cost]),
([Measures].[Cost Variance])
} ON COLUMNS
FROM [Cube]
Thanks for your help!
View 3 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 23, 2007
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
View 3 Replies
View Related
Apr 21, 2003
PLEASE SEE my update (post #3) for a better simpler explanation!!!!!
Hi,
I need a query which is basically
(query 1)
Except
(query 2)
SQL server supports
(query 1)Union (query 2)
but I can't get the Except to work.
Alternatively,
I also tried to implement it by using the "NOT IN" but didnt work.
Background: 3 tables, accnts, Opty, Opty_postn
I want
the accounts who have 1 or more opty's at 100% (sold) before 1/1/2002 but zero opty's at 100% after 12/31/2001.
accnts has fields ID & name, ROW_ID
opty has fields acct_id, prob, ROW_ID
opty_postn has fields opty_id, close_dt
My query:
SELECT accnt.name
FROM accnt
WHERE
accnt.ROW_ID IN
(
SELECT acct_id
FROM opty
where prob = 100
AND opty.ROW_ID IN
(SELECT opty_id
FROM opty_postn
WHERE close_dt<1/1/2002)
)
AND accnt.ROW_ID NOT IN
(
SELECT acct_id
FROM opty
where prob = 100
AND opty.ROW_ID IN
(SELECT opty_id
FROM opty_postn
WHERE close_dt >12/31/2001)
)
AND accnt.ROW_ID = opty.accnt_ID
my intent was
select account.names
where id
is in set(prob = 100 & sale before 1/1/02)
but not in ( prob = 100 & sale after 21/31/01)
SO ,the accounts which have sales both before and after get counted. But I want only those accoutns which have sales only before 1/1/02.
I hope i explained this right.
Thanks in advance for your help.
Ash.
View 14 Replies
View Related
Dec 18, 2004
Hi,
Can someone briefly explain which this feature of Analysis services is and how canit be of use.
Also, besides BOL, I'll appreciate if someone can recommend a good reading on this.
Thanks.
View 2 Replies
View Related
Jan 31, 2005
Hello.
i have dimensions: month, country, customer, item.
measures: amount, price.
i would like to get such result :
for each customer from USA: sum (amount of item in June * price for the same
item in January).
is it possible?
thank you for your help, Mike.
View 2 Replies
View Related
Apr 24, 2007
I have the following table structure
CREATE TABLE [dbo].[QDisc](
[Id] [int] NOT NULL,
[MinVal] [int] NOT NULL,
[MaxVal] [int] NOT NULL,
[PerVal] [int] NOT NULL,
CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I need to be able to select unique overlapping sets of data based on the minval and maxval.
Simple Example
minval maxval
5 15
16 25
10 20
query would produce
minval maxval
5 10
11 15
16 20
21 25
More Complex example
minval maxval
5 15
16 25
10 20
7 7
1 100
query would produce
minval maxval
1 5
6 6
7 7
8 10
11 15
16 20
21 25
26 100
Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.
View 9 Replies
View Related
Aug 17, 2006
I need to manipulate some sets, doing joins based on intersection, and looking for subsets. Is there any way to store an array in a single data field? And then do operations based on those array lists? e.g. -
{1,2,3} intersect {2,3,4} ==> {2,4} (would like to then be able to do joins based on intersection, so that I could say, return all cases where two sets have all but one member from each in common...)
View 3 Replies
View Related
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Mar 20, 2008
Hi,
I'm currently trying to retrieve results from a large dataset, there are over 45000 records and I need to use them all to peform counts etc. I have set up views, but my page is still being returned slowly, is there anything I can do to speed this up?
Thanks
Gemma
View 2 Replies
View Related
May 7, 2008
I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT
SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE)
SELECT NAME,
POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$,
LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLE INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME
WHERE POST_PD = @LASTPD
AND TABLE2.NUM= 539
GROUP BY NAME) AS LASTMONTH
INTO #TEMP_SAFROM TABLE
INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLEWHERE TABLE2.NUM = 539
GROUP BY NAME, POST_PDORDER BY NAME, POST_PD
SELECT NAME,
LAST_MONTH,
CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$
FROM #TEMP_SA
GROUP BY NAME
ORDER BY NAME
View 13 Replies
View Related
Jul 31, 2000
I need to upgrade a 6.5 box to 7.0 but also change the character sets. Let me know how this sounds.
-Have a 2nd machine w/ 6.5 on it and same char sets.
-backup/restore data from prod box to 2nd machine.
-upgrade 2nd machine to 7.0 using the same char sets.
-uninstall prod machine and install 7.0 w/ new char set.
-use dts to move data from 2nd machine to new machine. (changing char sets from old to new)
View 4 Replies
View Related
Oct 28, 1999
We're constructing a three-tier application. We want the middle tier to
extract a recordset from the database, disconnect from the database,
then pass the recordset to the front tier. After changes have been made
by the front tier, it will pass the recordset back to the middle tier,
which will reconnect, and update the database.
The problem:
Using stored procedures, the recordset is no longer available
once the connection has been closed. Is there any way, using stored
procedures, to be able to keep the recordset available after the
connection has been closed, either by preserving it, copying it, or
otherwise?
View 1 Replies
View Related
Dec 11, 2007
Hi:
How can I combine 2 sets of group by together?
same criteria on group by Region, county
setA with where sum(timeSec) <= 30
while setB with where sum(timeSec) > 30
they are not necessary mutual exclusive. thus, union is not good.
However, when I use derived table method, I will have 'dups' on column of 'Region' and 'county', 2 total are fine.
thanks
David
View 2 Replies
View Related
Jun 19, 2013
I have been tasked with creating a report that shows sales of our products grouped into buckets of 5 each, DESC. I have a table that has the itemNo and revenue. The final report would be something like:
Top 5 Spreads$695,066
Next 5 Spreads$467,845
Next 5 Spreads$416,946
Next 5 Spreads$361,946
Next 5 Spreads$305,607
Next 5 Spreads$270,567
Bottom Spreads$15,954
My initial thinking was to use row_number() and partition to label the rows in groups of 5 like:
item 130,0001
item 229,0001
item 328,0001
item 427,0001
item 526,0001
item 625,0002
item 724,0002
item 823,0002
item 922,0002
item 1021,0002
item 1120,0003
item 1219,0003
item 1318,0003
And then Sum the revenue, grouping by this row_number. But I haven't been able to get it working right.
View 13 Replies
View Related
Apr 8, 2008
Dear friend, the following is my query.
1.SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'
2.SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'
i need to join this two resultsets. please give me sample query to join this two result sets
View 10 Replies
View Related
Jun 3, 2014
i have these 2 queries with the included results sets...What commands could I use to take the TotalBlueCircle Column from the 2nd Results Set and have it included next to the TotalRugby column in the 1st results set??Do i need to do a UNION or use Sub Queries or something else?
View 5 Replies
View Related
Feb 25, 2008
I have a maintenance plan setup to expire backup sets after 4 days, but its keep backup copies for an infinite amount of time. I might be misunderstanding whats suppose to happen, but I would expect the files to be removed after 4 days.
View 3 Replies
View Related
Mar 12, 2008
Hi All,
I would like to match two sets of data. I have setup a view of data that contains a group of customers and their details. I want to view this data, but also find these customers in another table based on matching their surname and date of birth, then retreive the information stored on these customers from the second table.
Does anyone have any suggestions how i would go about doing this?
Thanks in advance
Humate
quote:Originally posted by Michael Valentine Jones
It takes real skill to produce something good out of a giant mess.
View 2 Replies
View Related
Jul 23, 2005
I have the following situation. One set of data has 274 rows (set2)and anther has 264 (set1). Both data sets are similar in structure aswell as values for both of them were extracts from the same parenttable. Hope the info would substitute DDL. I need to find the "gap"rows between these two sets.Attempted to run a query likeselect count(*)from set2where not exists(select *from set1)did not yield what I desired. What else to try?TIA.
View 12 Replies
View Related
Jul 23, 2005
Hello!My client has a need to be able to store Japanese characters in theirPeopleSoft database. So we need to change the character set from fromLatin1_General (1252) to Japanese character set (932) on SQL Server2000 Enterprise. I have 2 questions:1) I would like to know if in SQL Server, the character set is machinespecific or is it defined at the database instance level?2) Can multiple installs of SQL Server co-exist on a single server withdifferent default character sets?ThanksVishal
View 1 Replies
View Related
Jul 20, 2005
Hi!Another silly question:If a stored procedure returns multiple result sets, how do I choose the oneI want to insert into a table?For example sp_spaceused returns two result sets if object name is ommited.I only need the first result set. How do I do that?Tnx!Darko
View 2 Replies
View Related
Feb 29, 2008
Hi, I'm trying to return 2 different result sets using the below query mapped to 2 different variables in my execute sql task. I've tried this with one task and 2 seperate tasks but can't get it to work. Is this possible using only one task? It keeps giving me a result set error.
SELECT COUNT(*) AS DeceasedCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 53
SELECT COUNT(*) AS LostCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 469
View 5 Replies
View Related
Jul 12, 2015
I was working on what I was told was SQL 2012 and it turns out it is SQL 2005. I wrote two procs that I need to convert to 2005. Here is the code:
SELECT
era_provider_name AS Provider,
RIGHT([era_upi], 5) AS 'ERA Upi',
[era_fy] AS 'ERA FY',
ProcGrp,
COUNT(DISTINCT UCI) AS 'Client Count',
[Code]....
I'm not finding an efficient way to do this. I cannot use GROUPING SETS with 2005. Here is the code for the second proc:
SELECT
CASE
WHEN GROUPING(era_provider_name) = 1 THEN 'TOTALS'
ELSE era_provider_name
END AS era_provider_name,
CASE WHEN GROUPING(era_fy) = 1 THEN 'TOTALFY'
[Code] ...
The results as in SQL 2012 are exactly as I would like them. I want to mimic those results in 2005.
View 6 Replies
View Related
Mar 13, 2007
Hi
I have a matrix whos colunm group is filed by Dataset1,
now i want to add naother colunm group,but using the Dataset2
can I use two different dataset for a matrix,
for differnt colunm group
please help me in this regards
thanks
View 2 Replies
View Related
Oct 11, 2007
I popped into the Transact-SQL forum to get some help and created the dbo.selectaudit1 table, which can be queried succesfully.
Code Blockcreate table dbo.selectaudit1 (startkey varchar(10) not null,
endvalue varchar(10) null,
endkey as coalesce(endvalue, startkey))
insert into dbo.selectaudit1 (startkey)
select '042'
union all select '140'
union all select '2089'
union all select '2031'
union all select '2051'
union all select '2100'
union all select '2299'
union all select '2300'
union all select '2349'
union all select '2350'
union all select '2389'
union all select '2390'
union all select '2399'
union all select '2732'
union all select '2733'
union all select '2849'
union all select '2850'
union all select '2883'
union all select '2898'
union all select 'V073'
union all select 'V078'
union all select 'V100'
union all select 'V109'
union all select 'V580'
union all select 'V581'
union all select 'V661'
union all select 'V662'
union all select 'V671'
union all select 'V672'
union all select 'V711'
union all select 'V760'
union all select 'V769'
insert into dbo.selectaudit1 (startkey, endvalue)
select '1400' ,'2089'
union all
select '2100', '2299'
union all
select '2300' ,'2349'
union all
select '2350' ,'2389'
union all
select '2390' ,'2399'
union all
select 'V100' ,'V109'
union all
select 'V760' ,'V769'
Then in reporting services, I created a report with the following query in my dataset. This query works fine in a BIDS query windows, however, when I refresh my dataset and attempt to execute the query I am getting the following error:
Cannot find the object "selectaudit1" because it does not exist or you do not have permissions.
(Microsoft SQL Server, Error: 1088)
Code Block
Select
p.MRN
, p.PatientName
, CONVERT(CHAR(20),p.AdmitDate,101) AS AdmitDate
, CONVERT(CHAR(20),p.DischDate,101) AS DischDate
, p.VisitTypeCode
, d.VisitTypeName
, p.AnyDx
, p.PrinDxCode
, p.PrinDxDesc
, p.SecDx1Code
, p.SecDx2Code
, p.SecDx3Code
, p.SecDx4Code
, p.SecDx5Code
, p.SecDx6Code
, p.SecDx7Code
, p.SecDx8Code
, p.SecDx9Code
, p.SecDx10Code
, p.SecDx11Code
, p.SecDx12Code
, p.SecDx13Code
, p.SecDx14Code
, p.SecDx15Code
From dbo.PtMstr p inner join
ampfm.dct_VisitType d on d.VisitTypeCode=p.VisitTypeCode
INNER JOIN dbo.selectaudit1 sel
ON (PrinDxCode between sel.startkey and sel.endkey
AND DischDate between '2006-11-01' and '2007-09-30')
OR (SecDx1Code between sel.startkey and sel.endkey
AND DischDate between '2006-11-01' and '2007-09-30')
What am I missing?
View 4 Replies
View Related
Jul 31, 2007
Hi,
I am aware that SSRS does not support stored procedures with multiple data sets, but what if the same stored procedure may only return one set based on a parameter?
For example, lets say if parameter @TableID = 0, the stored procedure returns 2 result sets. But if @TableID = 1, or @TableID = 2, the SP only brings back 1 result set. Is this also not supported?
When I try to do this it still only brings back the fields from the first table. Am I doing something wrong?
Thanks for any insight.
View 2 Replies
View Related
Aug 28, 2006
What is the recommended mechanism for selecting paged results from SQL.
Presently I pass various params including the request Max Items Per Page and the requested page.
The I execute the query as a count with the search params.
Then comes the paging logic, which validates the page number against the request page and number of hits etc.
Then a temp table and record variables are created for the results.
Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).
Then I return the temp table & some additional return params with the Total Hits etc.
The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.
Thanks for any input,
Martin.
View 11 Replies
View Related
Mar 9, 2015
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
View 1 Replies
View Related
Jun 12, 2008
My stored procedure displays two result sets. How can i use that result sets in my 3-tier application. I want to bind first resultset to repeater control and second to label control. I am using SqlDataReader...
View 6 Replies
View Related
Apr 15, 1999
Supposing I have a sort order/char set installed and some databases working under these circumstances.Is there any way to change order and sets without losing my old databases? If I try to rebuild databases, I lose them,and when i try to restore them, there's a conflict between the new SQL Server 6.5 configuration and the Databases previously created in old sort order/char sets?Is there any solution to this,or once I have chosen the database configurations,I'll have to either recreate the data or die with them??? Neede answers ASAP....
Thanks for your attention, Luciano
View 1 Replies
View Related
