Company is starting to use the Reporting Solution feature. The model has been created however when adding filters we receive a yellow exclamation point when trying to add certain entities as filters. Can someone point me in the right direction on why this might occurr.
Thanks
Hello,
i have a report with approximately 20 reportparameters from queries. This is really slowing down the system, although the dataload (rows sent back) is not that huge. I read on the internet that there are basicly three approaches.
1, Query parameter with alot of diffrent datasources
2, Table filtering
3, Stored procedures
I wonder which approach is the best and why it takes such a long time to get the report with the report parameters (before generating the report itself).
Thank you for your help!
Basically I'm tried of messing with the stupid thing. It is a waste of time and money. MS should be ashamed of themselves. I have reinstalled the server twice and it still won't configure correctly.
Has any found anything else that works like it is supposed to?
Thanks
Hi,
I have being trying this for quiet a while now and I just cant find a solution. Basically I have a field called Total Active. The total active indicates for example..... A project is open or closed. If the project is open, the value 1 is displayed in the database and if closed the value 0 is displayed in the database.
So I have my field in reporting services and now I just want to count all the open projects. I thought you could do this:
= Count(Fields!Active_flag.value = 1)
This does not seem to work.....or my syntax is wrong or something.
Then I tried to pass all the values in Fields!Active_flag as a object using VB custom code. Example:
=code.getTotalActive(Fields!Active_flag.value)
then receive the Object and loop through it to count all the 1's in the Object (Active_flag.value) and pass the total back. However I was not successful with this because I couldn€™t get the length of the Object....which I needed to iterate through all the values.
My next attempt which I thought was a sure winner was using a group and filtering out all the 0's which left only 1's that I could count. But this brought about another problem.....the filter only looks for 1's and then counts the 1's and displays then.... But when it doesn€™t find any 1's then the entire Field does not display on the report. This is very frustrating because I would rather have is say "no projects active" instead of it not displaying.
Any help will be much appreciated. I have boggled my mind for the solution.
thanks
Ziyaad
hi,
I have a trouble in filtering with SQL reporting services 2005 (SSRS). Please help me in clearing this issue.
I have a textbox and an dropdownbox with all the column names of a table in it. I will be entering some text in the textbox and will select a column name in the dropdownbox. I should get a result by checking the value of the textbox in the selected colum from the dropdownbox.
I give the query in where condition as: "(@dropdownvalue LIKE @Textboxname) ", which is not working.
Thanks in advance,
John.
I am creating custom reports from TFS data in Report Builder 3.0. What I am trying to do is make it possible to run the report and then filter on something and only display those results. For example.. I have created a report with Project Name, Deployment Dates.
The report returns all the project names with a particular type of work item with a planned release date. I would like to be able to drop down and select a particular project name and only display results from that project. By default, I do want all the projects to display but want the possibility to select a specific project.
Do I go to dataset properties of the project name field and add an expression under the filters section?Do I also need a parameter?
I'm building a report that has a column in the table called countries of origin, that column can contain data like "USA","China" ( normal countries) but some rows can also contain "USA, China, Australia" ( multiple counties in one row).
I'm trying to build a filter to filter my results :
-I have a data set created with a list of all world countries
-I've Build a Parameter containing a drop down list with all countries
--- My problem is when I want to create the filter in my main data set , I can so it , but when I search for example for "USA" , I don't get the lines where there are multiple countries and "USA" is one of them...
There must be an expression in the filter that will be able to search through all the values in the string and return me my correct results.
Is there a way in Reporting Services to filter based on a parameter, but get it to use distinct values?
Currently I have a dataset that is grouped on a Branch value and a Team value.
The end-user wants to be able to filter out data based on the branch values, however when I set it up to that field it comes back with:
Branch 1
Branch 1
Branch 1
Branch 2
Branch 2
Branch 3
Branch 3
and the same goes with Team, which is a sub-group of branch. Is there any way I could set this up so that the parameter drop-down will only display distinct values? Also, is it possible to do the same with another parameter based upon the branch parameter.
Ex.
Branch 1 contains Team 01, Team 02, and Team 03
Branch 2 contains Team 04, Team 05
User selects to filter by Branch 1, the Team dropdown should only contain options for further filtering on Team 01, Team 02, and Team 03 not all of them.
Any help is much appreciated.
I am trying to generate a report that is basically a class list. It lists classes for a specific semester and lists them by day of the week and then by time.
If I am pulling back a dataset of all classes, how do I filter my report so as to show each set of classes appropriately (by day and by time within the day)? I tried using the Filter function on the table level, but I guess I don't get it.
My table would need to look like this:
MONDAY:
8:30 - 9:30
9:30- 11:30
11:30-1:30
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
TUESDAY:
8:30 - 9:30
9:30- 11:30
11:30-1:30
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.
The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks,
Barry G. Sumpter
Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express
Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.
Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.
Thanks
Using SQL 7 (of course), is there a way to give a user access to create and modify Stored procedures without actually giving them DBO rights?
View 1 Replies View RelatedHi folks, guidance required!
Clients have an MSDE installed and they need all of their data to be dowloaded for the first time from our website. 30 tables r involved.
Can i automate this? I suspect BCP allows only one table to be exported into a text file. Since it's the first time process i don't want to setup replication.
Howdy!
Hello All,
I have a transaction table that holds nearly a million records and my master with 60000 records,I want to fetch details based on two dates.
I use Union operator to query the info from a view,is this right or fetch the result into a temporary table and fetch the result from temporary table using the same Union operator.
Which is the better way to fetch details faster.
Nirene
My SP is pasted below just advice me to execute this more efficiently,cos this SP is called thru a Web application.
CREATE PROCEDURE GL @SDt Varchar(10),@EDt Varchar(10),@Loccode Char(5),@OP Char(4)
AS
Declare @OpBalQry NVarchar(1000),@TrnQry NVarchar(2000),@MainSQry NVarchar(500),@MainEQry NVarchar(500)
Declare @Cocode Char(5)
Declare @CashGL Char(6),@Gldesc Varchar(50)
IF EXISTS (Select 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='#Gltmp')
Begin
Drop Table #Gltmp
End
Select @Cocode=Cocode from Location Where Loccode=@Loccode
Select @CashGL=Glcode,@Gldesc=Gldesc from Glmast Where Cocode=@Cocode and Subgroupcode='CASH'
Set @OpBalQry='Select A.Glcode,'''' as Trtype,''' + @SDt + ''' as Refdt,'' Opening Balance'' as Refno,
Max(B.Gldesc) as Narration,Case When (Sum(A.Deb)-Sum(A.Cre))<0 Then (-1*(Sum(A.Deb)-Sum(A.Cre))) Else 0 End as Dr,
Case When (Sum(A.Deb)-Sum(A.Cre))>=0 Then (Sum(A.Deb)-Sum(A.Cre)) Else 0 End as Cr from
(
Select Glcode,
(Case When Drcrflag=''D'' Then Opbal Else 0 End) as Deb,
(Case When Drcrflag=''C'' Then Opbal Else 0 End) as Cre from Obdata
Union
Select Glcode,
(Case When Drcrflag=''D'' Then Tramt Else 0 End) as Deb,
(Case When Drcrflag=''C'' Then Tramt Else 0 End) as Cre from Trans_Journal
Where Refdt <Convert(Datetime,''' + @SDt + ''',103)
) A,Glmast B Where B.Cocode=''' + @Cocode + ''' and A.Glcode=B.Glcode Group By A.Glcode
Union
'
Set @TrnQry='SELECT T.Glcode,T.Trtype,Convert(Varchar,Max(T.Refdt),103) as Refdt , '''' AS Refno,
(Rtrim(Ltrim(Max(G.Gldesc))) + '' -- From Sub-Ledger'') AS Narration,
SUM(CASE WHEN T.Drcrflag = ''D'' THEN T.tramt ELSE 0 END) AS Dr,
SUM(CASE WHEN T.Drcrflag = ''C'' THEN T.tramt ELSE 0 END) AS Cr
FROM Trans_Journal T,Glmast G
WHERE T.Glcode=G.Glcode and G.Cocode=''' + @Cocode + ''' and (T.Slcode IS NOT NULL AND Len(Rtrim(Ltrim(T.Slcode)))>0) and
(T.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and T.Refdt <=Convert(Datetime,''' + @EDt + ''',103)
and T.Loccode= ''' + @Loccode + ''' and (G.Subgroupcode<>''BANK'' or G.Subgroupcode<>''CASH''))
GROUP BY T.Glcode,T.Trtype
UNION
SELECT A.Glcode,'''' as Trtype,Convert(Varchar,A.Refdt,103) as Refdt, A.Refno, B.Gldesc AS Narration,
CASE WHEN A.Drcrflag = ''D'' THEN A.Tramt ELSE 0 END AS Dr,
CASE WHEN A.Drcrflag = ''C'' THEN A.Tramt ELSE 0 END AS Cr
FROM Trans_Journal, Glmast B
WHERE (A.Glcode = B.Glcode and B.Cocode=''' + @Cocode + ''') and
(A.Slcode IS NULL OR Len(Rtrim(Ltrim(A.Slcode)))=0) and
(A.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and A.Refdt<=Convert(Datetime,''' + @EDt + ''',103))
and A.Loccode=''' + @Loccode + ''' and (B.Subgroupcode<>''BANK'' or B.Subgroupcode<>''CASH'')
Union
Select Glcode,Trtype,Convert(Varchar,Max(Refdt),103) as Refdt,'' '' as Refno,Max(Narration) as Narration,Sum(Dr) as Dr,Sum(Cr) as Cr from
(Select A.Glcode,A.Trtype,A.Refdt,B.Gldesc as Narration,
CASE WHEN A.Drcrflag =''D'' THEN A.Tramt ELSE 0 END AS Dr, CASE WHEN A.Drcrflag =''C'' THEN A.Tramt ELSE 0 END AS Cr
from Trans_Journal,Glmast B
WHERE
(A.Glcode=B.Glcode and B.Cocode=''' + @Cocode + ''' and A.Refdt>=Convert(Datetime,''' + @SDt + ''',103)
and A.Refdt<=Convert(Datetime,''' + @EDt + ''' ,103)
and A.Loccode=''' + @Loccode + ''') and (B.Subgroupcode=''BANK'' or B.Subgroupcode=''CASH'')
Union
Select ''' + @CashGL + ''' as Glcode,Trtype,Refdt,''' + @Gldesc + ''' as Narration,
CASE WHEN Drcrflag =''C'' THEN Tramt ELSE 0 END AS Dr, CASE WHEN Drcrflag =''D'' THEN Tramt ELSE 0 END AS Cr
from Trans_Transnarr
WHERE (Trtype=''CPM'' or Trtype=''CRT'') and Loccode=''' + @Loccode + ''' and
Refdt>=Convert(Datetime,''' + @SDt + ''' ,103) and Refdt<=Convert(Datetime,''' + @EDt + ''',103)) TT
Group By Glcode,Trtype) X'
Set @MainSQry='Select Glcode,Trtype,Refdt,Refno,Narration,Dr,Cr Into #Gltmp from ('
Declare @Fullqry NVarchar(4000)
If @OP='WOB'
Begin
Set @TrnQry = @OpBalQry+@TrnQry
End
Set @FullQry = @MainSQry+@TrnQry
Exec sp_executesql @FullQry
Select Glcode,Trtype,Refdt,Refno,Narration,Ltrim(Rtrim(Str(Dr,14,2))) as Dr,Ltrim(Rtrim(Str(Cr,14,2))) as Cr from #Gltmp SQL2XML
Where Dr+Cr<>0 Order By Glcode,Trtype,Refdt,Refno for XML AUTO
Drop Table #Gltmp
GO
hi,
i want to get following output:
id_order | type | number
-------------------------
1234 | A | 1
1235 |A | 0
1235 |B | 0
1236 |B | 1
1237 |C | 0
1237 |D | 0
create table tbl_order
(
id_order int
,type nvarchar(40)
)
insert into tbl_order (id_order, type) values (1234, 'A' )
insert into tbl_order (id_order, type) values (1235, 'A' )
insert into tbl_order (id_order, type) values (1235, 'B' )
insert into tbl_order (id_order, type) values (1236, 'B' )
insert into tbl_order (id_order, type) values (1237, 'C' )
insert into tbl_order (id_order, type) values (1237, 'D' )
insert into tbl_order (id_order, type) values (1238, 'A' )
insert into tbl_order (id_order, type) values (1239, 'D' )
insert into tbl_order (id_order, type) values (1239, 'B' )
insert into tbl_order (id_order, type) values (1239, 'A' )
select
id_order
,type
--,isnull(orderX,'') as number
,case when orderX > 1 then 1 else 0 end as number2
from tbl_order
left join (select t2.id_order as orderX
from tbl_order as t2
where
(select count(t1.id_order)
from tbl_order as t1
where
t1.id_order = t2.id_order) = 1
)as x
on tbl_order.id_order = x.orderX
Is there any better/faster select sentance to do this? i'm using sql2000.
thank you
Hi, i am not sure if i can solve the problem with SSIS. I wanted do do it hardcoded with C# or so.
So, for my project I need to download zip-files on a daily-base. In these zip-files are xml-Files. And in these files is information stored for inserting or updating tables on a MS-SQL-Server.
So, my question, is it possible to solve that with SSIS? And if, is it easy to understand and to learn within a few days?
I haven't found good sources for information on how to get the data out of XML-Files.
I've got a .dts which load data to several Sql2k servers by Copy SQL Server Objects Task. I'd like to add another task for a Sql25k but I can't because of it's requesting SMO.
It seems that such task need DMO. I know that there are other ways for do such stuff in that DTS but I wonder if there is any trick or shortcut for to avoid this barrier.
Dear All expertise please help me
The following is the table structure of Holiday
EmployeeID(Integer)
HolidayFrom(date)
HolidayTo(date)
The following record exist in the table
EmployeeId HolidayFrfom Holidayto
-----------------------------------------------------------------------------------------------
5 25/12/2007 28/12/2007
1 02/01/2008 10/01/2008
2 03/01/2008 03/01/2008
3 25/01/2008 27/01/2008
I want a report to get the employee who did take the leave from the given date range. For example I am giving the date range as follows
Criteria for Report
----------------------------------
date from 03/01/2008
Date to 04/01/2008
When I given the date range as above two record have to be displayed . Because Employee 1 and 2 will be on leave on that date.
EmployeeId HolidayFrfom Holidayto
-----------------------------------------------------------------------------------------------
1 02/01/2008 10/01/2008
2 03/01/2008 03/01/2008
So please help me to find out an SQL to display the records from the given criteria of two date range
Hi all,
I think i have a quite specific problem that we have to resolve with replication. however, i don't know which type of replication to use and how to configure it for this:
To simplify the case a bit:
We have one central server which has a table Sales(id, shopid, article).
Also our 50 shops have the same table Sales(id, shopid, article).
spec 1:
When a shop sells an article, the row of the Sales table at the local shop has to be replicated to the central server. This way, the central server its Sales table has all sold articles from all shops.
spec2:
But now we have also an internet shop. All e-orders arrive immediately in the table Sales at the Central Serve with a specific shopid where the article will arrive. Now i have to find a way to replicate the new rows from the Central Server to the specific Shop.... Remark: the other shops should not have rows of other shops.
which type of replication would i use the best and how?
Thanks !
The encrypted value for the "LogonCred" configuration setting cannot be decrypted. (rsFailedToDecryptConfigInformation) (Microsoft.ReportingServices.Diagnostics)
Please: a step by step solution.
I need help with filtering a specific set of numbers. I have a Sql database that is connected to my sql report I have created a tsql statement that pulls a clients name, PO, and invoice number. The prblem I am having is I have 2 different types of invoice numbers one number looks like 123456-1234-T the other looks like 123455-1234-L I need to beable to pull only the invoices with T on one report and L on another report can some on show me how I can sort these in a tsql script
View 8 Replies View RelatedI have table with the following columns.
ID, DearlershipLocation, VehicalMake, VColor, VType, VYear
1, London, Buick, Red, Sedan, 2000
2,
2006, Windsor, Ford, Blue, Jeep, 2002
My question is, how do I write a query to filter fron all Dealership location a speciif car like Ford with a red color and a sedan type?
Please help.
Thanks.
Juvan
Hello,
This may be simple, but I can't figure a way to do this. I have the following data returned to a table and need to sum only the items where HDMethod=0 in the table footer. For some reason, something like:
=Sum(Iif(Fields!HDMethod.Value=0, Fields!BDExtended.Value, Nothing)) returns all the rows.
There is a LEFT OUTER JOIN: dbo.[Billing Detail].Item = dbo.[History Detail].Item between the tables touched in the query if that helps.
Thanks for any help you can offer.
:
BDBilling BDExtended HDMethod BDDate
----------- --------------------- -------- -----------
14965 30.00 0 2008-03-24
14965 25.00 NULL 2008-03-24
14965 28.00 NULL 2008-03-24
14965 45.00 NULL 2008-03-24
14966 30.00 0 2008-03-24
14966 50.00 NULL 2008-03-24
14966 20.00 NULL 2008-03-24
14966 45.00 NULL 2008-03-24
14966 42.00 NULL 2008-03-24
14966 60.00 NULL 2008-03-24
14967 30.00 0 2008-03-24
14967 25.00 NULL 2008-03-24
14967 28.00 NULL 2008-03-24
14967 45.00 NULL 2008-03-24
14968 30.00 0 2008-03-24
14968 25.00 NULL 2008-03-24
14968 28.00 NULL 2008-03-24
14968 45.00 NULL 2008-03-24
14969 30.00 0 2008-03-24
14969 25.00 NULL 2008-03-24
14969 28.00 NULL 2008-03-24
14969 45.00 NULL 2008-03-24
14969 42.00 NULL 2008-03-24
14969 60.00 NULL 2008-03-24
14970 30.00 0 2008-03-24
14970 25.00 0 2008-03-24
14970 28.00 0 2008-03-24
14970 45.00 0 2008-03-24
14970 60.00 0 2008-03-24
I have a table with the following columns
ID, Dealershiplocation, VehicalMake, VColor, Vtype and VYear.
1, London, Buick, Red, sedan, 2001
------
20, Windsor, Ford, Blue, pickup, 2004
My question is how do I write a query so I can filter from all dealership location a specific vehical like
Ford with a red color and Sedan type?
Please help.
Thanks
Juvan
I'm running VS 2005 Enterprise, and SQL Server 2005 Developer / workgroup edition.I'm trying to follow a few online examples to play around, and some of these examples require that you add a SQL DB to your solution by going:-right click on website-Add new Item-Add SQL DataBase.WHen I do that, I get the error that SQL Express isn't installed. Now the question is, why would I want SQL Express installed when I have SQL server 2005 already installed? My Machine can support it, but it's sort of a waste when I need to run SQL Server 2000, SQL Server 2005 and then SQL Server express all at the same time.Any advice?
View 3 Replies View RelatedDear All,
I'm new in SQL Server 2000. Anyone has a good materials or sites to recommend which talk about Multidimensional Expression? Especially MDX work in "Analysis Manager".
You information are highly appreaciate. Thanking you in advance.
can anyone please point me to any good links for scalable solutions in sql server..
thanks
Thought I'd got my head round using a set-based approach but my brain's gone dead on this one
CREATE TABLE #mytable (SWID INT , T INT , DateA DATETIME , DateB DATETIME)
INSERT #mytable (swid , t , DateA , DateB)
SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 , CAST('31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 1 ,CAST('31-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 1 ,CAST( '31-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 0 , CAST('15-Mar-2006' AS DATETIME), CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST( '01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) ,CAST( '01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME), CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 , CAST('31-Dec-2005'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '31-Dec-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST('30-Nov-2005'AS DATETIME) , CAST( '01-Aug-2006' AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '30-Nov-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME)
select * from #mytable order by SWID desc, DateA desc
The Columns where T values are 1 and 0 are OK having already been derived. I need to UPDATE the remaining rows from the Default T Value of 9999
to Decrementing values (starting at -1) commencing at the highest remaining (ie non 9999 T Value) DateA value and working 'backwards'
'grouping' on SWID
The DateB value is irrelevant for this purpose
The desired output is below with derived T values
SWIDTDateADateB
6396712006-03-31 00:00:00.0002006-08-01 00:00:00.000
6396712006-03-31 00:00:00.0002007-02-01 00:00:00.000
6396702006-03-15 00:00:00.0002006-08-01 00:00:00.000
6396702006-03-15 00:00:00.0002007-02-01 00:00:00.000
63967-12006-02-28 00:00:00.0002006-08-01 00:00:00.000
63967-12006-02-28 00:00:00.0002007-02-01 00:00:00.000
63967-22006-01-31 00:00:00.0002006-08-01 00:00:00.000
63967-22006-01-31 00:00:00.0002007-02-01 00:00:00.000
1005112006-03-31 00:00:00.0002006-08-01 00:00:00.000
1005112006-03-31 00:00:00.0002007-02-01 00:00:00.000
1005102006-03-15 00:00:00.0002006-08-01 00:00:00.000
1005102006-03-15 00:00:00.0002007-02-01 00:00:00.000
10051-12006-02-28 00:00:00.0002006-08-01 00:00:00.000
10051-12006-02-28 00:00:00.0002007-02-01 00:00:00.000
10051-22006-01-31 00:00:00.0002006-08-01 00:00:00.000
10051-22006-01-31 00:00:00.0002007-02-01 00:00:00.000
10051-32005-12-31 00:00:00.0002006-08-01 00:00:00.000
10051-32005-12-31 00:00:00.0002007-02-01 00:00:00.000
10051-42005-11-30 00:00:00.0002006-08-01 00:00:00.000
10051-42005-11-30 00:00:00.0002007-02-01 00:00:00.000
Thanks in advance
Hi there,
Looking for a bit of help with my problem:
Say i have 3 tables-
tblClients
clientID (primary key identity/autonumber)
clientName (varchar 50)
tblCities
cityID (primary key identity/autonumber)
cityName (varchar 50)
tblClientsCities
ID (primary key identity/autonumber)
clientID (int)
cityID(int)
A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.
I thought is would be easier to make up a unique key for each client with a script eg
client name: PJ Computers
Unique key Generated: PJCOMP58784
Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.
So my question is what's the best way of accomplishing this?
Any help would be much appreciated, thanks :)
Hi ,
What is the best Backup plan that can be give for a sql databse that is on a very high usage.
Will a six hour backup will decrease the performance of the SQL server...
Sipin
CREATE TABLE [Cube_fact_table] (
[ISIN_ID] [bigint] NOT NULL ,
[CLIMSTID] [bigint] NOT NULL ,
[HOLDID] [bigint] NOT NULL ,
[Quantity] [bigint] NULL ,
[Holding] [numeric](15, 0) NOT NULL ,
[Cost] [numeric](18, 3) NOT NULL ,
[Close_Price] [numeric](18, 3) NOT NULL ,
[Tran_Value] [bigint] NULL ,
[Date] [datetime] NULL ,
[UCOA] [bigint] NULL ,
[COA] [bigint] NULL
) ON [PRIMARY]
GO
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('1', '1', '1', '2000', 2000, 170.100, 170.100, '340200', '11/28/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('1', '1', '3', '-1000', 1000, 185.500, 185.500, '-185500', '12/12/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '5', '48600', 48600, 225.000, 225.000, '10935000', '11/27/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '8', '-48575', 25, 243.000, 243.000, '-11803725', '12/4/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '9', '12575', 12600, 254.000, 254.000, '3194050', '12/5/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '10', '-12120', 480, 232.000, 232.000, '-2811840', '12/5/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '11', '12120', 12600, 219.000, 219.000, '2654280', '12/6/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '12', '-12120', 480, 265.000, 265.000, '-3211800', '12/6/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '13', '4261', 4741, 224.000, 224.000, '954464', '12/8/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '14', '9059', 13800, 223.000, 223.000, '2020157', '12/11/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '15', '10200', 24000, 233.000, 233.000, '2376600', '12/12/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '16', '-12188', 11812, 243.000, 243.000, '-2961684', '12/12/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '17', '12188', 24000, 234.000, 234.000, '2851992', '12/13/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '18', '-12188', 11812, 234.000, 234.000, '-2851992', '12/13/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '19', '788', 12600, 254.000, 254.000, '200152', '12/16/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '20', '8000', 20600, 223.000, 223.000, '1784000', '12/17/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '21', '-13754', 6846, 231.000, 231.000, '-3177174', '12/17/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '22', '10554', 17400, 245.000, 245.000, '2585730', '12/19/2003 12:00:00 AM', NULL, NULL)
Now what i want to do is this ...
UCOA is to be updated with the average of all previous transactions where the climstid and isin_id are the same.
example
take case where holdid(transaction no) in the sample data is 14.
What i need is a query which will sum up the data in the tran_value column upto holdid 5.
Again I need a set based solution. Gurus .. please help
SQL Gurus,
What is the best backup solution?
Product:MS SQL 2000 Enterprise Edition
Environment:webserver
Goal:Off-site backup of database without onsite tech intervention
Obstacles:
* Webhost does not want SQL Server Agent running for fear of viruses
* Database is 3.4GB, LDF is 3.7GB, and a Full Backup is 3.1GB.
Connection / Access:Enterprise Manager, RDP and FTP all work to access data and backups.
I have looked into doing a full backup then differential backups, doing transaction replication, and log shipping. The client doesn't really need replication, he just wants to know that if his server with the production database goes up in flames he has a way of getting back in business without losing more than one day of data.
I am hoping there is a method of "restoring" on his off-site server the data that is "backed up" on the production server. Is that possible if we're doing differential backups?
Is that the best solution or is there a better way?
Thanks for any recommendations or advice,
Alec Sherman
These two queries when executed seperately give results in under 10 secs A union between these two does not give results even after 20 minutes ... Any idea why this is happening SELECT T001W.NAME1, t25a5.bezek SKU, QTY = SUM(CASE MSEG.BWART WHEN '101' THEN MSEG.ERFMG WHEN '102' THEN (-1)*MSEG.ERFMG END), YPLNT.VKGRP FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a5 WHERE MSEG.MANDT = MKPF.MANDT AND MKPF.MANDT = MARA.MANDT and mkpf.mandt =yplnt.mandt and mkpf.mandt = t25a5.mandt AND MKPF.MBLNR = MSEG.MBLNR AND MARA.MATNR = MSEG.MATNR AND YPLNT.PPLNT= MSEG.WERKS AND MSEG.WERKS = T001W.WERKS and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3) AND MARA.PRDHA <> '' AND MKPF.VGART IN ('WR','WF') AND MKPF.MJAHR=YEAR(@BUDAT1) AND MSEG.AUFNR IS NOT NULL AND MSEG.BWART IN ('101','102') GROUP BY t25a5.bezek,T001W.NAME1,YPLNT.VKGRP SELECT T001W.NAME1, t25a2.bezek SKU, QTY = SUM(CASE MSEG.BWART WHEN '101' THEN MSEG.ERFMG WHEN '102' THEN (-1)*MSEG.ERFMG END), YPLNT.VKGRP FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a2 WHERE MSEG.MANDT = MKPF.MANDT AND MKPF.MANDT = MARA.MANDT and mkpf.mandt =yplnt.mandt and mkpf.mandt = t25a2.mandt AND MKPF.MBLNR = MSEG.MBLNR AND MARA.MATNR = MSEG.MATNR AND YPLNT.PPLNT=MSEG.WERKS AND MSEG.WERKS = T001W.WERKS and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3) AND MARA.PRDHA <> '' AND MKPF.MJAHR=YEAR(@BUDAT1) AND MKPF.VGART IN ('WR','WF') AND MSEG.AUFNR IS NOT NULL AND MSEG.BWART IN ('101','102') GROUP BY t25a2.bezek,T001W.NAME1,YPLNT.VKGRP
View 14 Replies View Related