If I have the raw data dumped into a big table as following:
Date P R M E Date P R M E Date P R M E Date P R M E
1/1/90 1 2 3 4 1/1/90 2 3 4 5 1/1/90 3 4 5 6 1/1/90 4 5 6 7
...
1/1/05 1 2 3 4 1/1/05 2 3 4 5 1/1/05 3 4 5 6 1/1/05 4 5 6 7
And this table has a repeating block [D, P, R, M,E] 300 times. Is it possible to write a loop query/stored procedures/triggers (or whatever it is) to read each repeating block and stack them on top of each other to insert into another table which has the same structure as following?
Look like this?
Date P R M E
1/1/90 1 2 3 4
...
1/1/05 1 2 3 4
1/1/90 2 3 4 5
...
1/1/05 2 3 4 5
1/1/90 3 4 5 6
...
1/1/05 3 4 5 6
If there is a solution would you please elaborate, example?
Thank you for the help.
shiparsons
Hello all,Thinking about building a new database in the enterprise addition ofsql server and using some horizontal parititioning techniques in orderto accomaodat what will eventually be a monster huge database.Can you share some hard earned experience, gotchas, etc...with me? Wewill be setting up this server on a SAN array that will be made up ofjust one or two huge virtual RAID10 volumes and I am also wonderingabout the wisdom of this? Its simple and should work, but is thatconfig relevant in any way specifically to our plan to partition?
I am testing horizontal partitions to see whether it is a feasible option for a project. IF I have a composite Primary Key and the constraint column (a part of the Primary Key) that helps the partitioned view is defined with DateTime Data Type, select on a restricted set of data through a partitioned view still tries to access all the tables instead of just one table that contains the data. Is this the case or am I missing something ?
CREATE TABLE [dbo].[tst01] ( [Dt] datetime NOT NULL , [TID] int NOT NULL , [Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[tst02] ( [Dt] datetime NOT NULL , [TID] int NOT NULL, [Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tst01] ADD CONSTRAINT [PK_tst01] PRIMARY KEY CLUSTERED ( [Dt], [TID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tst02] ADD CONSTRAINT [PK_tst02] PRIMARY KEY CLUSTERED ( [Dt], [TID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[tst01] ADD CONSTRAINT [CK_tst01] CHECK (Dt between '11/1/2002' and '11/30/2002') GO
ALTER TABLE [dbo].[tst02] ADD CONSTRAINT [CK_tst02] CHECK (Dt between '12/1/2002' and '12/31/2002') GO
insert into tst01 values('11/1/2002', 1, 'SS') insert into tst01 values('11/2/2002', 2, 'KK') insert into tst01 values('11/3/2002', 3, 'DD') Go
insert into tst02 values('12/1/2002', 1, 'LL') insert into tst02 values('12/2/2002', 2, 'MM') insert into tst02 values('12/3/2002', 3, 'GG') Go
CREATE VIEW vtst AS SELECT * FROM tst01 UNION ALL SELECT * FROM tst02 Go
I'm running SQL Server 2008 Standard.I need to create a query that has data from multiple columns (Columns 1-6 with coresponding Date started and Date Completed data) displayed vertically, but also has the column name the preeceeding column to identify it...along with other data (Record number, status).
I'm considering using horizontal partitions to separate my data by year. For example, SomeTable_2004, SomeTable_2003, etc. This works well for backups, maintenance, etc. because I'm working with 150+ GB of data. I'll be a partitioned view for queries.
However, I'm new at this and have a few questions. I would also like to do partitioned updates or inserts. But I need to make sure that the tables don't use similar primary keys. Does that make sense? I need to make sure that the primary keys from the first table are not used again in the second table.
SomeTable_2003 primary keys: 1,5,8,9,15
SomeTable_2004 primary keys: 2,3,4,10
I don't really care what keys are used on what table, as long as they are different. I have apps that already use this data, and I don't want to change the application logic.
factory2 goods 1kg 5.50 factory2 goods 2kg 6.20 and so on for all factories.
I tried with UNPIVOT but it does not allow it (I'm using Navicat 8), saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UNPIVOT...".
I have 2 installations:1. W2k with MS SQL 2000 sp2.2. W2003 with MS SQL sp3.There are two databases in all instalations: "maindb" and "userdb". Thetable with data is in maindb.In the 1'st installation I restict user access to all data in table in thisway:- create user's login and user in databases maindb and userdb.- create view in userdb with "where" clause as dbo (dbo is owner of thisview). This clause restict access to data. (create view userdb.dbo.table asselect * from maindb.dbo.table where ...)- add user to group "Public" in maindb.- add role "data reader" to user in userdb.- effect: user can access data only by view, can not access any data inmaindb.I do the same in secound installation:- effect: user can not access data by view - message like this: "userhave not permission to select on maindb.dbo.table"Is this bug in sp3 or in sp2 ?Is there another way to horizontal restrict access data in tables?In Sybase ASE this method (restict by view) works ok. And there is newproperty of ASE 12.5.1 - administrator can define context of login - the"where" clause will be added automatically to any select.Please help me. Thank You for any advice.
I recently came across a database where the data are horizonally partitionedinto 4 tables. I'm not sure if this was a poor design choice, or if it wasdone for valid performance reasons. The schema of the tables are essentiallythe same, it's just that they are named differenly and the columns are nameddifferenlty to differentiate the data from a business usage perspective. Thetables could easily be combined inot one by adding a new colum to theclustered index that would be used to differentiate the business usage. I amtrying to evaluate whether combining the tables would improve performance orif it would be better to leave them the way they are. Many queries that runagainst these tables do not request records from more than one of thetables, which is good. However, there are a number of processes that queryagainst all of the tables on the identical clustered index range. I am notsure exactly how many rows are in the tables but I'm fairly certain theentire database is < 50 GB.
Anyone know why the horizontal scroll bar doesnt show up when the data is too large to fit into the given window? The vertical scroll bar will show up but never the horizontal.
HI, I'm testing out partitioning for an upcoming project and I'm a little confused over the query plan im generating.
I have a 13 million row table I want to break in to date ranges: tables are calljrnl,calljrnl_1999 and calljrnl_2000. The calljrnl table contains data for the last six months. one view, calljrnl_view is a union all of these three tables.
alter table calljrnl_1999 add constraint dt_constraint_1999 check (cj_created_dt <='1999-12-31 23:59:59')
alter table calljrnl_2000 add constraint dt_constraint_2000 check (cj_created_dt >='2000-01-01 00:00:000')
alter table calljrnl_2000 add constraint dt_constraint_2000_2 check (cj_created_dt <='2000-09-30 23:59:59')
The problem I'm having is that when I query on any date range outside the constraints set on the calljrnl_2000 table, It still wants to scan the calljrnl_2000 table. I'm assuming this has something to do with how the constraints are structured, but I am not able to find the right combination.
I have a table with 52 million rows which resides on Primary file group in my database. Because of huge number of rows the performance has gone very down and I would like to break the table into parts.
Can anyone suggest me the steps for doing the same and the number of parts that should be made. It is named as Account_Transactions and contains information of Policies in an insurance database.
I have a table as follows opendate (datetime) callnumber (int) closed (bit)
I want to find how many calls were opened today and of those how many are closed
I have come up with the code below but again am looking for 1. a more elegant solution 2. a way to generalise this to show the same information for x number of days
create table #holdit1 (opencount int)
create table #holdit2 (closedcount int)
insert into #holdit1 SELECT count(*) as opencount FROM [dbo].[problog] WHERE datediff(dd, opendate, getdate()) = 0 AND closed = 0 group by closed
insert into #holdit2 SELECT count(*) as closedcount FROM [dbo].[problog] WHERE datediff(dd, opendate, getdate()) = 0 AND closed = 1 group by closed
select #holdit1.opencount AS CallsOpen, #holdit2.closedcount AS CallsClosed, #holdit1.opencount + #holdit2.closedcount AS AllCalls from #holdit1 cross join #holdit2 #holdit2
DROP TABLE #holdit1 DROP TABLE #holdit2
this gives me CallsOpen CallsClosed AllCalls ----------- ----------- ----------- 1 3 4
I have a client with a fairly simple table as illustrated in my sample code. Their interesting requirement is that the records would be listed horizontally, end-to-end as I hope you can visualize based on what I'm providing here. Two other characteristics/specs of note, 1) there could be as few as two records end-to-end in a row or as many as 100 and 2) of course the column headings would have to be unique.
create table #tmpEndToEnd ( ID int identity, ClientID char(10), Agency varchar(20), Clinician varchar(20), Goal varchar(50)
We are generating RDL file on the fly using a XSLT. We are trying to implement an auto width to all the columns dislpayed in the Report. Is Automatic horizontal width for column supported in RDL? If so how? If not is there is any workaround for it?
We tried using CanGrow and CanShrink tags, but those are for only vertical.
I would like to display a couple of records horizontally via a table or list, but don't think this is possible. Am I right? The given recordset should only have two records and the formatting would be much better if they were displayed left to right rather than one on top of the other. Is this just not possible with SSRS? Thanks,
I'll admit I am fairly new to the reporting services, but I managed to figure most things out so far except this one. I have a matrix report where I have column groupings of:
Sales person Region
Basically, the report shows sales by sales person and region, and I added subtotals to each so each salesman/region combination gets subtotals, as does each salesman entry. Now, the one piece missing is the 'total of the subtotals' so to speak. So if I have something like:
Salesman Region Sales Jan Sales Feb Sales Mar 001 A $100 $175 $50
B $100 $200 $100 C $200 $100 $50 Total $400 $475 $200 002 A $100 $175 $50
B $100 $200 $100 C $200 $100 $50 Total $400 $475 $200 Grand Total $800 $950 $400
What I want to do is sum up the totals and add another column like so:
Salesman Region Sales Jan Sales Feb Sales Mar 001 A $100 $175 $50
B $100 $200 $100 C $200 $100 $50 Total $400 $475 $200 $1075 002 A $100 $175 $50
B $100 $200 $100 C $200 $100 $50 Total $400 $475 $200 $1075 Grand Total $800 $950 $400 $2150
And that is where I am stuck. In Report Designer, with the existing matrix report as above, how do I get these horizontal totals of the subtotals?
Is there a report viewer property that needs to be set to enable the horizontal scroll bar. My reports are being cut off at the end of the iframe. I have several other reports that do show the horizontal scroll bar, but for some reason all my reports in the newly created project do not allow horizontal scrolling. Vertical scrolling is still available.
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
To the following Cust______1______3______4______6______9______11 __________________________________________________ __________ 1_______Bike___________Blue__No ENsur_________ 2_______car____silver_________________Ens____Yes"
I have a query that calculates sales by sales person, but it displays horizontally across my query window. Is their a way in SQL Server to have the data display vertically down the window instead?
This is my current query
Code:
Select count(case when salesman Like 'Geo%' then id else null end) As [George] ,count(case when salesman Like 'Li%' then id else null end) As [Lisa] ,count(case when salesman Like 'Jor%' then id else null end) As [Jorge] ,count(case when salesman Like 'Ri%' then id else null end) As [Richard] ,count(case when salesman Like 'Geo%' then id else null end)+count(case when salesman Like 'Li%' then id else null end) As [Team 1 Sales] ,count(case when salesman Like 'Jor%' then id else null end)+count(case when salesman Like 'Ri%' then id else null end) As [Team 2 Sales] from sales.southeastregion
Which of course shows the results as such
George --- Lisa --- Jorge --- Richard --- Team 1 --- Team 2 100 50 10 90 150 100
And I want the data to be displayed like
George - 100 Lista - 50 Jorge - 10 Richard - 90 Team 1 - 150 Team 2 - 100
Above is the query of the result I want to show this resultset horizontaly for that I have to use the cursor such that @M1=Where Milestone=1 @M2=Where Milestone=2 @M3=Where Milestone=3 and putting this into a temp table with its projectid and taskid. Plz guide me that how to achiev this exactly Swati