Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
I would like to reopen this discussion. I need to create this tableusing SQL that will return this dataset.BRAID RATEDEFID MIN(BRADATE) MAX(BRADATE RATECODE PRODNAME614 14 12/1/2002 12/4/2004 Rack BeachTower Terrace618 14 12/8/2002 12/11/2004 Rack BeachTower Terrace622 14 12/15/2002 12/19/2004 Rack BeachTower TerraceI have provided the test data below and the SP I have been working with.The SP works fine as long as there are 4 seqential dates. I getunpredictable results when the count <> 4. I am also concerned on whathappens at the end of the month. For example, 1/31/2004, 2/1/2004.Does this affect the SP? I appreciate your continued support.MichaelCREATE TABLE [dbo].[BASERATEAVAIL_Tmp] ([BRAID] [decimal](18, 0) NOT NULL ,[RATEDEFID] [decimal](18, 0) NOT NULL ,[BRADATE] [datetime] NOT NULL ,[RATECODE] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,[PRODNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL) ON [PRIMARY]GOINSERT INTO dbo.BASERATEAVAIL_Tmp(BRAID, RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (614, 14, '12/1/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (615, 14, '12/2/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (616, 14, '12/3/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (617, 14, '12/4/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (618, 14, '12/8/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (619, 14, '12/9/2002 ', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (620, 14, '12/10/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (621, 14, '12/11/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (622, 14, '12/15/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (623, 14, '12/16/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (624, 14, '12/17/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (625, 14, '12/18/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (626, 14, '12/19/2002 ', ' Rack ', ' Beach Tower Terrace ');CREATE PROCEDURE usp @n INTASSET ANSI_WARNINGS OFFSELECT MIN( braid) AS "braid",ratedefid, ratecode, prodname,MIN( CASE n WHEN 1 THEN bradate END ) AS "min_date",MAX( CASE n WHEN 0 THEN bradate END ) AS "max_date"FROM ( SELECT t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate,COUNT( * ) % @n AS "n",( COUNT( * ) - 1 ) / @n AS "r"FROM BASERATEAVAIL_Tmp t1INNER JOIN BASERATEAVAIL_Tmp t2ON t2.bradate <= t1.bradateGROUP BY t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate) DGROUP BY r, ratedefid, ratecode, prodnameHAVING MAX( CASE n WHEN 0 THEN bradate END ) IS NOT NULL ;Usage:EXEC usp @n = 4--Anith*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hello,I'm somewhat of a newbie and I need to create a view with a columnthat is not derived from any other tables. What I want is for thisfield to be an auto-increment field or some kind of UID. Can anyonequickly tell me how to do this.Here is my dilemma. I need to pull data from a unique data set from atable which does not have a primary key, and none exists in its data.Please tell me how to put this data in a view(or another table) sothat I can pull it out with unique records. It doesnt matter what theunique field consists of.Thanks
Not sure that's what I need to do but here goes: I need to create a temp table with data for a crosstab. (columns grouped by month)
The crosstab's not gonna show a missing month (example, date range is from January to June but there's no data for the month of January, so the 1rst column in the crosstab is Feb) Is there a way to create 'dummy' dates in the select statement and insert them in my result set so missing months will show whether theres data or not?
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
To the experts in the field: There is probably a very simple solution that is avoiding my grasp.
I have a For Loop which I want to execute as long as a variable called BeforeRunDt = CurrentDate. Both are DateTime data types and I am using the following expression:
@BeforeRunDt==@CurrenDate
I get an error stating "Cannot convert expression value to propeerty type"
I understand that the result of the expression should be a boolean value but am just struggling on how to create it.
Hi,I'm a newbie to sql server and this may be a really dumb question forsome you. I'm trying to find some examples of sql server triggers thatwill set columns (e.g. the created and modified date columns) if the rowis being inserted and set a column (e.g. just the modified date column)if the row is being updated.I know how to do this in oracle plsql. I would define it as a beforeinsert or update trigger and reference old and new instances of therecord. Does sql server have an equivalent? Is there a better way to dothis in sql server?Thanksericthis is what i do in oracle that i'm trying to do in sqlserver...CREATE OR REPLACE TRIGGER tr_temp_biubefore insert or updateon tempreferencing old as old new as newfor each rowbeginif inserting then:new.created_date := sysdate;end if;:new.modified_date := sysdate;end tr_temp_biu;
Hi all, i have a table which has date and count as column Can you please...help me write a query for it.
date | type | count 10/07 | A | 70 11/07 | A | 50 12/07 | A | 50 11/07 | B | 60 12/07 | B | 50 13/07 | B | 40 11/07 | C | 50 12/07 | C | 60 13/07 | C |60 14/07 | A | 50 i need to write a sp such that..if user sends date and @ndays(no. of days) as parameter say 11/07 and 3
then
out put
type 11/07 12/07 13/07 A 50 50 B 60 50 40 C 50 60 60
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select Test_Name AS 'Test_Name', Table_Bird.Animal AS 'Birds', Table_Mammal.Animal AS 'Mammal', Table_Reptile.Animal AS 'Reptile, Table_Fish.Animal AS 'Fish' From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.
For example a report that would run on today's MM, yy (5/2007) would look something like this:
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.
I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!
select gl_account.id as GLAcct, gl_account.descr as GLDesc, sum(gl_ledger.amount_n) as Net from gl_account
I have created a sample Database for the school project,
After executing the query below, the Date column is supposed to have the dates I have entered before,
However the dates shown are 1900.
Any idea why is this happening?
I appreciate your help.
Thank you. Query:
Drop table AccountReceivable GO --BEGIN TRANSACTION Create table AccountReceivable ( AccountRecID int identity (1,1) not null, PatientID int not null, PresentCharges int default 0 not null, PaymentMade money default 0 not null, PreviousBalance money default 0 not null, BalanceDue money default 0 not null, LastPaymentDate datetime not null, PresentDate datetime default GetDate() not null ) GO ALTER TABLE AccountReceivable ADD CONSTRAINT PK_AccountRecID Primary Key (AccountRecID) GO
ALTER TABLE AccountReceivable ADD CONSTRAINT FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID) GO --COMMIT --query to find delinquent accounts --DATEDIFF (d, LastPaymentDate, PresentDate)
--Populate the Accounts Table DELETE AccountReceivable GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate ) VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (918035,109,109,0,0,3/6/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (914235,279,89,0,190,5/9/2005,5/9/2005) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (914235,0,90,190,100,5/9/2005,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT) GO
Hi im trying to create a view that creates another column for a table and then adds the value but i cant get it could sum1 help
Create View BONUS_PAYOUTS As
Alter Table Employee Add EMP_BONUS Decimal(6,2)
Update Employee Set EMP_BONUS = EMP_PCT / 500000
Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, Employee.EMP_BONUS From Employee Inner Join Position On Employee.POS_ID = Position.POS_ID
I need to split a string, based on comma delimetrs, into columns of a table.
I am using SQL Server 2005. The plan is to use webmethods (Integration Software) to receive a flat file, loop through the records in that flat file and during each iteration of the loop call a SQL stored procedure that will split the record, based on comma delimetrs, into columns of a table.
A typical record will look like this: AP05-07,ACTUAL,ZA01,......
I have looked at some of the past solutions to this type of post, but am battling to understand....
So if its possible, is there a simple way to create a stored procedure that will do this?
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
Please note that the number columns are different in each table. I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table. And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
I have a table like the followingField1 Field2 Field3------ ------- ------x1 y1 z1x1 y2 z2x1 y3 z3x1 y4 z4x2 y1 z5x2 y2 z6x2 y3 z7x2 y4 z8x3 y1 z9............and so onI want to create a view with x1, x2, x3.. as uniquerecords; y1, y2, y3.... as fields; and z1, z2, z3.... as the valuesWhen I doCREATE VIEW xyz (y1, y2, y3, y4) ASSELECT field1 ,( SELECT field3 FROM table WHERE field2 = 'y1'),( SELECT field3 .....FROM tableI get the error that the sql query creates duplicate values. I think Imay have to do a join using distinct values of field1. I was lookingfor some guidance with the join.Thanks for your help in advance(using SQLSERVER 2000)
I have one table which holds all the information for the forms and i have another table which holds list of all the form fields. Now these two tables are mapped and that table give me list of all the fields in any forms.
To get the information about the users who filled different forms first i need to query the tblFormsToFormFields and get the list of all the fields for that form and then use that fields list in the select query to get the result.
In my report i need to provide the drop down with the list of all the forms and then display the information for all the customers for that particular form with the list of the form fields.
Is there in SSRS 2005 i can use C# or any programming language to create Columns in the report dynamically as there will be different fields for each form.
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I think I need to only create few if an index is covering all columns then I do not need to create more indexes for separate columns or should I create separate index as suggested?
Similarly:
CREATE INDEX [NCIX_20187_20186_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([SerialNo],[StationaryStatus]) GO CREATE INDEX [NCIX_20189_20188_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([StationaryStatus]) INCLUDE ([SerialNo]) GO
[Code] ....
Should I create all indexes above or use minimum number of indexes which covers all columns as mentioned in above create index statements?
I would like my transformation to automatically create an output column for each input column. Any tips? I can't seem to determine which event to listen to or method to override.
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
We are building an application with a database that contains Jobs. These Jobs have properties like Name, Code etc. and some custom properties, definable by the application admin. For bulk import of Jobs, we want to allow the import of an Excel sheet with the columns Name, Code and a variable amount of columns. If the header names of these columns in the Excel sheet match the name of a custom property in the system we want to add the value of that cell into the database as property value.
In our Data Flow of our Import Package in SSIS we added an Excel Source that points to a test excel sheet with the Name and Code columns and €“ for this example - 3 custom property columns (Area, Department, Job Family). When we configure the Excel Source in the Excel Source Editor, we have the option to select the Columns from the Available External Columns table. But here lays the problem, we do not know at design time, what custom property columns to expect. We DO expect the Name and Code columns, but the rest is uncertain at design-time.
That raises the question: Is there some way to select all of any incoming columns (something like a SELECT * in T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is being generated at design-time would need to be updated at run-time to reflect the variability of the columns that might be encountered while reading the excel sheet.
Then, we thought, we could add a Script Component to our data flow that passes some kind of DataSet (or DataReader) in which we can walk through the columns ourselves? But then still, we miss the option to include ANY of the columns while reading an Excel sheet (or any other datasource by the looks of it)
We are aware of the option of optional columns in combination with the RaggedRight option, but it seems that we would have to put all of the columns of a row in just one column and then extract all the columns later with Derived Columns. But then, since the source import file is being prepared by an application admin, we want don€™t want to burden him with this horrendous task of putting everything in one column.
We would like to have some way of iterating through all the columns, either in a Script Component or maybe with a Pivot/Unpivot mechanism.
Does anyone have any suggestions? Are there other options we should have considered?
I am trying to find a way to add into a table a flattened (comma seperated list) of email addresses based on the multiple columns of nformation in another table (joined by customer_full_name and postcode.
This is to highlight duplicate email addresses for people under the same customer_full_name and Postcode.
I have done this using a loop which loops through concatenating the email addresses but it takes 1minute to do 1000. The table is 19,000 so this isn't really acceptable. I have tried temp tables, table variables and none of this seems to make any difference. I think that it is becuase i am joining on text columns?
