When Then To Create Mult Columns
Apr 3, 2008I have a colm day and colm sales, I would like to have a column for each day of week i think i can use when then but not sure how
View 3 Replies
ADVERTISEMENT
Mult Parameter Help
Mar 27, 2008I am working on a report for a client and in the parameter field they would like to be able to use different date ranges if needed. The fields they would like to have parameters for would be Effective Date, Tracking Date, ReCred Letter Sent Date, ReCred Complete Date. Instead of making 4 different reports, I was looking for something I can put in my statement so she can pull either fields.
This is what I thought would work. I am using Microsoft Visual studio. If you think it is better for me to put it in my actual SQL I can do that too.
Here is what I was doing below. The when I try and run the report, it tells me that I need to have a value in each one of my parameters. Can this type of formula work or do I need to have 4 separate reports.
([Tracking Thru Date] >= @trackfrom) AND ([Tracking Thru Date] <= @trackthru) or
([Effective Date] >= @efffrom) AND ([Effective Date] <= @effthru)or
([ReCred Letter Sent Date] >= @sentfrom) AND ([ReCred Letter Sent Date] <= @sentthru) or
([ReCred Complete Date] >= @compfrom) AND ([ReCred Complete Date] <= @thru)
Thanks!
UPDLOCK On A Mult-table Join
Jul 23, 2005Hi all,Is this legal ?SELECT a.col1,b.col2,c.col3FROM tab1 a WITH (UPDLOCK) , tab2 b, tab3 cWHERE a.col1 = b.col1and b.col2 = c.col1WIll the above cause a UPDLOCK on tab1 and not tab2 and tab3?COmments,thoughts,criticisms?I have a problem with a query that performs a multi-table jointo get column values and then one of the tables in the join isbeing updated. I am getting DEADLOCKs and was wondering if I couldtry and reduce that.Also how can I find out what locks are in effect for a certainquery..Is it syslocks or are there any other tables..?DrD
View 2 Replies View RelatedRisks Of Single MSSQL Domain Account For Mult Servers?
Jul 20, 2005Greetings:I am trying to conceive what risks might be created by runningmultiple SQL servers within a domain under a single domain account, asopposed to 1) running under the local service account or 2) multipledomain service accounts.In this case, all the SQL servers are SQL2000 running on Win2003. Theservice account is assigned only to the "Domain Users" group.We do use linked server calls, and I have played and suceeded gettingKereberos up to avoid double hop issues when using Windows Auth. Infact, this is one of the reasons that sparked the question in my mind-- in all the MS Kerebos SQL<->SQL examples, the SQL servers run undera unique service account.As an aside, most of the servers are "line of business" servers, butHR runs under a unique server with more sensitive information. I don'treally think that merits a seperate service account, but again, Icould well be missing something.I mostly looking for food for thought, but concrete examples ofgotchas would be appreciated.Thanks all.d.
View 2 Replies View RelatedUsing Row Data To Create New Columns
Jun 1, 2008i have been trying to create new columns in a tabinvoice details using row data from another table and additional input from another form. Problem is i get no sql errors m database conection is ok usually works but doesn't write any new columns or values to the tblinvoicedetails here is the code i am using i hope someone ca tell me where i am going wrong.
<table width="100%" cellspacing="0" cellpadding="2" border="0">
<form name="form2" method="post" action="index.php" onsubmit="return v.exec()">
<?
$cid = mysql_real_escape_string($_GET['cid']);
$query = "select * from tblcustomerdetails where intCustomerID = '$cid'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$cid = $row["intCustomerID"];
?>
<tr>
<td><?print_r( $row['vcCCompanyName'])?></td><td align="left">Tel: <?print_r( $row['vcCDPhone'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCStreetNo'])?> <?print_r( $row['vcCStreet'])?></td><td align="left">Fax: <?print_r( $row['vcCFaxNo'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCCity'])?></td><td align="left">Email: <?print_r( $row['vcCEmail'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCState'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCPostalCode'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCCountry'])?></td><td> </td>
</tr>
</table>
<br />
<table width="100%" cellspacing="0" cellpadding="2" border="0">
<tr>
<td> </td><td><b>Code</b></td><td><b>Name</b></td><td><b>Description</b></td><td><b>Price</b></td><td colspan="2" align="center"><b>Action</b></td>
</tr>
<?
$accountsql="select * from tblpricedetails where intCustomerID=$cid order by intProductID asc";
$tempdata=$DB_site->query($accountsql);
$count =$DB_site->num_rows($tempdata);
while($rowdata=$DB_site->fetch_array($tempdata))
{
$intPriceID = $row["intPriceID"];
$intProductID = $row["intProductID"];
$vcProductCode = $row["vcProductCode"];
$vcProductName = $row["vcProductName"];
$vcProductDescription = $row["vcProductDescription"];
?>
<tr>
<td class="accountsumb">
<?=$rowdata["intProductID"]?>
</td>
<td class="accountsume">
<?=$rowdata["vcProductCode"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductName"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductDescription"]?>
</td>
<td class="accountsumc">
<?=number_format($rowdata["ftAmount"],2,".",",");?>
</td>
<td class="accountsumc" align="right">
<input type="text" size="10" name="Quantity" />
</td>
<td class="accountsumc" align="center">
<input type="text" size="10" name="total" />
</td>
</tr>
<?
}
?>
<? if($count==0)
{
?>
<tr bgcolor="#EFEFEF" class="smallblack">
<td colspan="7" align="center" bgcolor="#EFEFEF" class="redtext">This user has no product data. </td>
</tr>
<?
$insertdate = mysql_query("INSERT INTO tblinvoicedetails (intProductID,vcProductCode,vcProductName,vcProductDescription,ftAmount) VALUES(".$row['intProductID'].",".$row['vcProductCode'].",".$row['vcProductName'].",".$row['vcProductDescription'].",".$row['ftAmount'].")") or die(mysql_error());
}
?>
<tr>
<td colspan="7" align="center"><br />
<input name="submit" type="submit" class="button" value="Submit" />
</td>
</tr>
<tr>
<td colspan="7" align="center">
<input class="button" type="reset" name="reset" value="Reset" />
</td>
</tr>
</table>
</form>
Create Array Of Columns
Feb 18, 2008Absolute beginner to SQL here...
I'm try to create an array of columns and then refer to these columns at the asp.net level.
Can't seem to find this in the SQL literature...so I'm probably looking at this all wrong.
Thanks in advance.
Larry
Could Not Create A Foreign Key On 2 Unique Columns
Feb 12, 2006Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
CREATE TABLE T_FK (ID1 INT, ID2 INT)
And I want to do:
ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)
I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.
Thank you.
Have a nice day.
How To Create Dynamic Columns In Sql Database
May 3, 2008hi,
in the front end i have two fields one is dropdown list another is checboxlist say dropdownlist consist of title name and checkbox list consists of sub title name.on selecting title name from dropdownlist i will be getting respective title name in checkboxlist. here when i select checkboxlist(suppose if there are 6 items in checkboxlist n if i select only 3)then my databse column should generate one dropdownlist selected item and 3 checkboxlist selected item.
Eg: if dropdownlist contains( title1,title2,title3) and checkboxlist contains(chk1,chk2,chk3,chk4,chk5,chk6). if i select title1 from dropdownlist i will be geeting 6 item in checkbox list) if i select only 3 items of checkbox list. then i should be able to create column name by title1,chk1,chk2,chk3
help is appreciated
Create A View That Counts In One Of The Columns?
Nov 5, 2014I need to create a view that counts in one of the columns.
The tricky part for me is that it should only count to three, then start on 1 again and count to three again.
Example
1 - car
2 - bike
3 - Motorbike
1 - Boat
2 - Airplane
3 - Motorboat
1 - Bicycle
Add New Columns To Create Script Of A Database
Dec 22, 2014Am a fresher and I got a scenario like i have to take create script of some tables of a database in sql server and I generated script to them and i have to add four new columns to the script and have to delete 2 columns from existing script for multiple tables at a time. It will take more time to do them individually.Is there any way to do this at a time and with in short period of time????
View 3 Replies View RelatedCreate View With Computed Columns?
Apr 1, 2015I have a table that I cannot allow a computed field to exist on (due to a 3rd party software), so I am thinking I could create a view with a computed field that is persistent, is that possible?
the syntax below will not work, I am not even sure if this is possible, but if it can work, that would be great.
I am wanting to get the sum of jetfoot1, 2 & 3 and have the total added up as "total"
create view ViewSumReport as
select JETFOOT1,JETFOOT2,JETFOOT3,(JETFOOT1+JETFOOT2+JETFOOT3)as [total] persisted
from dbo.fielddata
GO
How To Create Rule Which Update Other Columns
Jul 20, 2005Hi,I have a table with the following columns:ID INTEGEDR,Name VARCHAR(32),Surname VARCHAR(32),GroupID INTEGER,SubGroupOneID INTEGER,SubGroupTwoID INTEGERHow can I create a rule/default/check which update SubGroupOneID &SubGroupTwoID columns when GroupID for example is equal 15 onMSSQL2000.It is imposible to make changes on client, so I need to checkinserted/updated value of GroupID column and automaticly updateSubGroupOneID & SubGroupTwoID columns.Sincerely,Rustam Bogubaev
View 4 Replies View RelatedHow To Dynamically Create Columns For A Table
Apr 29, 2008Hi,
how to dynamically create columns for a table
Is There A Way To Create Columns From A Row Value In A Select Statement?
Jan 30, 2008
Hi!
I have a select question that look like this:
DECLARE @ID uniqueidentifier;
SET @ID = '40bd3052-60f4-414a-99df-ca882c128493';
SELECT
rp.ID AS ReportPackId
,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier
,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier
FROM
ReportPack rp
INNER JOIN ReportPackAdditionalParty rpap
ON rpap.fk_ReportPack = rp.ID
WHERE rp.ID = @ID
The result a get when running the select question is:
ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId2
My problem is that the result I want is the following:
ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
AdditionalPartyIdentifier
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
addPartyId2
I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?
Avoid Typing To Create Columns
Nov 19, 2007I need to create table in sql server 2005 database, I have plenty of columns. Well I know how to create them manually. but does any body know or tried before to create columns automatically, or through some batch process. I have column names already typed in TXT file (comma delimited).
all I am trying to avoid that typing work.
any advice.
Thanks
Create Unique Index On Multiple Columns
Jan 19, 2008Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
How To Create A Query To Pull That Specific Row And All 50 Columns
Feb 7, 2012I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
View 3 Replies View RelatedIs It Possible To Dynamically Create Columns In A Table In SSRS
Nov 19, 2007
Hi,
I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.
So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..
any help is appreciated..
Regards
Karen
How To Create A Unique Constraint On Composite Columns
May 5, 2008I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.
Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.
Thanks in advance
John.
Create Table With Veriable Number Of Columns
Apr 12, 2008
Hi everyone,
I need to create temporary table in one of the SP.The problem is that number of columns in table will vary depanding on input in SP.
How can i create table with variable number of columns?
Thanks,
Alex
How To Create Dynamic Columns In A Temporary Table
Sep 5, 2007Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.
my actual table is like
Key
Value
X1
x
X3
x
X5
x
Y1
y
Y2
y
when user select x, the input variable passed to stored proc and the result is shown like
column names
X1 X3 X5 as column headers.
the select query is from temporary table.
these out put is based on the user selection. so the temporary table created with columns dynamically.
please help me out.
please let me know if you didn't understand.
thanks
Praveen.
Reporting Services :: Create Alternate Color Columns (RDL)
Jun 3, 2009I found something related to row coloring at :: [URL] ....
I went through all the properties for column but cannot achieve my goal. SO in my case .. the no of rows are fixed .. but columns vary .. so i want the alternate columns to be of different color (say white and silver)
T-SQL (SS2K8) :: Create Periods Transaction Dates And Make Them Columns
Sep 4, 2014I 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
[Code] ....
T-SQL (SS2K8) :: Identify Columns Which Will Create Unique Record In A Table
Sep 15, 2014I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies View RelatedSQL Server 2012 :: Create A New Column By Dividing 2 Columns In The Query?
Mar 12, 2015I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market
How can I do this?
How Create Multiple Driildowns For All Columns In Table Using Ssrs Report
Dec 13, 2007
hai, iam new to ssrs please help me,
my report having 10 columns, i can explain my need with example that is, assume like this customer id is first column of my table customer id is 101 it having 3 departments a1,b1,c1,perticular department that is a1 having emp1,b1 having emp2,c1 having emp 3.
i want output like this when clicking + customer id driildwon it display 3 departments taht is a1,b1,c1, when +a1 drill down clicking i need to dispaly emp1, corresponding b1 to emp2 , c1 to emp 3.
above explanation is only one column of the table, like that iam also displaying this driiling procedure for remaining different columns.
and i need to display customer information by weekly,daily,monthly,yearly at bottom of the report
please give which logic used in creating format like above drilldown report which having multiple drilldowns for all columns in a table
if any body give procedure for creating fromat for drilldown report which having multiple drilldowns for all columns in a table is appriciate.
thanks
jacks
When To Create Columns And Metadata For Custom Asynchronous Component Output
Apr 17, 2006I'm having a tad bit of trouble getting output from an asynchronous component that I've written and am looking for some insight.
This component takes in a name string passed from upstream and parses the name components into standardized output fields. I'm using an asynchronous component because if the name string contains two names ("Fred & Wilma Flintstone") I'm outputting one row for Fred and one for Wilma. I've gotten it to run and with debugging have observed what appeared to me to be proper execution, but zero rows are flowing out of it.
In my ProvideComponentProperties method, I add the three fields and there associated metadata to the OutputColumnCollection. Is this method where this should occur? It's before the PrimeOutput method, so I didn't know if I should be creating the output columns in ProcessInput (i.e., after the output buffer is provided by PrimeOutput.)
In ProcessInput, I'm using AddRow for each input row and another if it contains a second name, setting the value for each index using the buffer's SetString method, to no avail. I can observe it to this point, but then don't know what's in that output buffer (if I'm using the wrong buffer index value, etc)
Thanks.
Create Output Columns Based On Input In Custom Component
Aug 28, 2007
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?
Transact SQL :: Create Columns For Year / Month And Day From Epoch Column
Jul 21, 2015I'm wanting to create reports in SSDT 2012 which is connected to a 2008R2 database. I want to have parameters on for my reports where a user is able to select a year such as 2014. Unfortunate my table containing the data has two columns with a date value. the first is of the int type and contains an epoch formatted date. The second is a varchar type and shows the date as 2015-07-01 08:00:00. I would like to be able to write a query to return the year, monthnumber and daynumber from either of these columns.
View 5 Replies View RelatedSQL Server 2012 :: Dynamically Create A Script Only Selecting Columns Where There Is Data?
Dec 2, 2013I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
SQL Server Admin 2014 :: Create Dynamic Columns In Temp Table?
Jun 9, 2014I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below
CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]
for i want to generate table for the above feilds with datatype
RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page
Dec 13, 2007Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns
Jul 24, 2012Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...