I want to write a query that will give me a dynamic amount of columns back.
What I want to do, I want to create a calendar application, in which for
each employee, I want to show if he is in the office or not.
this should look like:
ID, Name, 1,2,3,4,5,6,7,8
88,Leo,0,0,1,1,1,0,0,1
The amount of columns is dynamic, and is a period of time, with a column for
each day.
Any suggestions what the best approach to this could be?
Thanks
Leo
Hi All,
I am using Micosoft Visual Studio Report Desinger. with MS SQL 2005.
I have a table transac table fields are likely,
location,date,amount values,
USA,01/07/2006,3000
SG,01/07/2006,2500
USA,02/07/2006,6000
SG,02/07/2006,3500
USA,03/07/2006,1000
SG,03/07/2006,6700
USA,04/07/2006,500
SG,04/07/2006,200
Am writing query for date = 04/07/2006
select location,date,amount from transac where date = 04/07/2006
I wanted to add two more column in the query which is
a.two days before what is the amount
b. From 01/07/2006 to 04/07/2006 what is the amount
The result I want to be
Location,date,amount,2daysbefore,uptodate
USA,04/07/2006,500,6000,10500
SG,04/07/2006,200,3500,12900
How to write a query ?.
I am writing this query from DataSet for Report Desinger.
Is there any way to include this two column.
Please Advise,
Regrads Saleem
I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales
01-Jan-14,100,120,300
02-Jan-14,80,150,300
03-Jan-14,100,20,180
Turned to this
01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180
Or even just the date and a SUM
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
I just joined a bank related IT department where the existing IT team is already working on creating a new application since a year now and designed database on SQL Server 2012, the amount (currency related) column's datatype found different in tables some where it is decimal and somewhere found different. I want to suggest them Money datatype to choose where we are dealing with currency related columns. My question is, is that correct to choose Money datatype or should I ignore this?
View 3 Replies View RelatedIf I have the total amount of 673000 that is passed as a parameter in my stored proc then, I need to do this:
Declare @TotalAmount money
@TotalAmount = 673000
Col1 Col2 Col3
Test1 45 672955 --(I want to subtract 673000 from Col2 data) 673000-45
Test2 30 672925 --(I want to subtract 30 from the remaining amount of col3)
Test3 100 672825 --(I want to subtract 100 from the remaining amount of col3)
ID A B C AVG
------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column....
I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?
Here's the script:
declare @tableHTML nvarchar(max) ;
set @tableHTML =
N'<h1>Revenue Report</h1>' +
N'<table border="1">' +
N'<tr><th>Amount</th><th>Index</th><th>CompObj</th><th>Rev Type</th><th>Program</th>'+
CAST ((SELECT td=SUM(dbo.tblAllocations.Amount),'',
[code]....
Generally, on any screen, we design filter screen by allowing user to identify range or one value to search.
But sometime in some screen, It will be more convenient for user if user can identify No matter how value to search.
For example
On screen which have information of people in any province.
So user would like to search it by identify no matter how value to search.
There are check box of any province on filter part which enable users choose it.
Hence, if sometime user choose (by clicking on checkbox) 3 provinces : LA, Michigan, WachingtonDC to see description only 3 chosen province.
and sometime user choose (by clicking on checkbox) 2 provinces : LA, Michigan to see description only 2 chosen provinces.
Please give me any idea for create Stored Procedure or any tecnique to complete my idea....
Help me Pleaseeeee
Hi every one,
I am facing problem in printing the reports from browser and also when i export it to pdf,the problem i am facing is blank pages are coming when report column getting the large amount of text around 2500 characters into column value.
can any one help me in this issue?. if the report is getting acceptable amout of data it is printing in proper way i.e no balnk pages at all.i maintained all properties like margins+body size < page size.
Not nice but a work around: is there a way to get the amount of fields or rows from a table or query within MS SQL, to pick out the lastfield-4 for example ?
SELECT LASTFIELD-4
FROM CUSTOMERS
Thanks
I have a query that returns a result that looks like this:
amount count
steve 122000 12
jim 145213 13
paul 62325 7
I need to add 2 columns to this query that rank the employees by count and amount with a number, so for example:
amount amount_rank count count_rank
steve 122000 2 12 2
jim 145213 1 13 1
paul 62325 3 7 3
I was going to paste the entire query in but I thought I'd try this first to see if I could get by with a hint. So essentially I need to evaluate with an order by or something and then insert a number as amount_rank and number as count_rank.
Any ideas?
Hi all - struggling a bit with this one... I have made an accounts application and need to make a report called "Aged Debtors". It lists all of the clients with outstanding invoices and breaks them down into periods:
current (this month)
period 1 (last month)
period 2 (2 months ago)
period 4 (invoices raised 3 months ago or more)
My tables are as follows (simplified)
Clients
clientid int
clientname varchar(100)
invoices
invoiceid int
clientid int
invoicedate datetime
cinvoiceamount decimal(19,2)
paid int (1 = paid, 0 = unpaid)
paidamount decimal(19,2) (how much of this invoice has been paid)
I need the following columns in the report
clientname invoiceid current period1 period2 period3+
test client 1 £100 - - -
2 £50 - - -
3 - £100 - -
test client2
I can format all the report using a matrix in SSRS so just need the stored proc to get me going - any help greatly appreciated!
Thanks guys,
Stephen.
I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.
I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:
SELECT
t.gl7accountsid,
a.accountnumber,
a.description,
a.category,
t.POSTDATE,
t.poststatus,
t.TRANSACTIONTYPE,
t.AMOUNT,
case
when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,
t.ENCUMBRANCESTATUS,
t.gl7fiscalperiodsid
FROM
UrsinusCollege.dbo.gl7accounts a
join
ursinuscollege.dbo.gl7transactions t on
a.gl7accountsid=t.gl7accountsid
where
(t.gl7fiscalperiodsid >= 97
And
t.gl7fiscalperiodsid<=@FiscalPeriod_identifier)
And poststatus in (2,3)
and left(a.accountnumber,5) between '2-110' and '2-999'
And right(a.accountnumber,4) > 7149
And not(right(a.accountnumber,4)) in ('7171','7897')
order by a.accountnumber
Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.
Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.
Thanks very much.
Sue
I have a sales tables which looks as below.
DEPARTMENT
Barnd_Name
Item_Group
S_DATE
S_AMOUNT
Administration
IBM
[code]....
Now i need Month Wise Running Totals.but i should check the following group as show below i that order
1) DEPARTMENT
1) Brand
3) Item Group
4) Month
Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.
Hi all,
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
I kindly request for help.
Thanx in advance.
Ronnie
Hello All,
I'm trying to us a parameter as column name like:
Select * From SM_Clients
where @Selection like @SelectionChar+'%'
This dos not work.
does someone have a solution :(
tks stroek
Hello All,
I'm trying to us a parameter as column name like:
Select * From SM_Clients
where @Selection like @SelectionChar+'%'
This dos not work.
does someone have a solution
tks stroek
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
View 2 Replies View RelatedHi All
I want to rename the column name by using the dynamic value as like
declare @name as varchar(12) set @name='ss'
sp_rename 'dbo.employees.id3',@name, 'COLUMN';
go
but it shows the error
Incorrect syntax near 'sp_rename'.
any idea?
Hi is possible to create dynamic column name
example
Declare
@StartDate as dateTime
Select @StartDate = '2004-06-05'
select
SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)],
SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)]
from
table1
thanx for the help in advance,
i have a table with Monday, Tuesday, Wednesday. .... Sunday
i get the weekday by
select @stat = datename(dw, getdate()) which in this case is Wednesday
but when i do something like
select top 2 * from myTable
where 'myTable.'+@stat = 1
which is as same as
select top 2 * from myTable
where myTable.wednesday = 1
i receive a syntax error. how can i dynamically select myTable.Wednesday ?
i also tried using
select top 2 * from Intercon
where '@stat' = '1'
but then this doesn't return anything where it should.
any help?
Dear All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
please can anybody solve this issue.
Thanks,
Syed
Hi everyone,
So I have to export from a SQL 2005 table to a dBaseIV table using SSIS. Easy enough, however the catch is that the tables being exported will vary. I can send variables to the the package calling the sql table name and creating the DBF file with the same columns on the fly.
The problem is mapping the columns. From what I've been reading there is no way to alter the mapping in a package at runtime. I was hoping that there would be some sort of auto mapping setting that would match on the field names but I guess not. Anybody run in to this issue and have a work around? Thanks in advance....
Scott
Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.
Mike
"oh, that monkey is going to pay"
Hi guys 'n gals,
I am trying to achieve something a bit complex (or at least appears to be for me).
I have the following table structure:
UID, GroupID, ColumnName, ColumnValue
here is some example data:
UID, GroupId, ColumnName, ColumnValue
1, 1, MAC Address, 8a7sd87sad
2, 1, IP Address, 192.168.1.100
3, 1, Name, John
4, 2, MAC Address, 09a8sd098as
5, 2, Name, Steven
and here is what I would like the example to come out like:
GroupID, MAC Address, IP Address, Name
1, 8a7sd87sad, 192.168.1.100, John
2, 09a8sd098as, NULL, Steven
It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...
I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:
Code Snippet
DECLARE @ColumnName varchar(128)
DECLARE @Sql varchar(255)
SELECT @ColumnName = [ColumnName] FROM tblSourceData
SELECT @Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@ColumnName+'] FROM tblSourceData'
EXEC (@Sql)
Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?
Regards,
Justin
Hi
In SSIS i have an execute SQL task where i get the job_id as the output which i set it to a package variable.
Now what I need to do is i have to map this value to one of the columns of the OLEDB destination.
How can I do it? Please help me ASAP.
Thanks
Sai
Hi All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
please can anybody solve this issue.
Thanks,
Syed
Hi,
I have to choose a particular column from a table whenever a condition met. That means i have to use the column in the select query whenever the condition met otherwise i have to create a dummy column with the same column name.
I tried to use the following sample query.
Eg:
Create table temp
(test_id int,
test varchar(100))
Declare @var varchar(100)
Set @Var='Hi'
Select
case when @Var='HI' then "None" else Test_new End as Test
from Temp
But it throw error as "Invalid column 'Test_new' "
As per my situation the Test_new column will be available in temp table whenever the condition is not met.So when @var<>'HI' i have to get the details of the test_new column otherwise i have to create a dummy column with the same name.
Hey, I have a search form with a selectbox. This selectbox contains the columnnames.I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.
If you see I have put in the querystring as columnname @sescolumn which I have initialised as asp:sessionparameter.But it gives no results. When I put @sescolumn between [] like normal columnnames are it doesn't work also.
Can someon put my on the right path?
<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @sescolumn like @seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @sesdatefrom and [Eindeverhuur] <= @sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>
How can I make this stored procedure concept work:
-----------------------------------------------
Create Procedure MyProc @MyValue varchar(5)
As
Declare @ColumnName as varchar(11)
Set @ColumnName = 'Price_' + @MyValue
Select Sum(Price) As @ColumnName --????
From MyTable where Item = @MyValue
Return
-----------------------------------------------
Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
Bjorn
I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names.
Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to.