SP To Display Selected Dates As Columns

Jul 31, 2007

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


Thanks..

Regards,
sradez

View 3 Replies


ADVERTISEMENT

Reporting Services :: How To Display (All Selected) When Parameter (Select All) Is Selected In SSRS

May 6, 2015

Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.

Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.

View 3 Replies View Related

How To Display The Selected Multi-value Report Parameter/s In A Text Box?

Mar 19, 2008



Hi All,

I have a report parameter which i multivalue. I want to display the selected value in a text box. I have written the following code :


=Switch(Parameters!ServiceAttribute.Count = 1,Parameters!ServiceAttribute.Value(0),

Parameters!ServiceAttribute.Count = 2,(Parameters!ServiceAttribute.Value(0) & ", " & Parameters!ServiceAttribute.Value(1)))



Which suggests that if only one of the two multivalue parameter id is selected display the same (Parameters!ServiceAttribute.Value(0)).

This works fine when i select both the attributes but throws an exception "Index was outside the bounds of the array" when i select only one of the parameters. Can anyone help me with that?

Also i want this report parameter to allow null i.e. if a user does not select anything he should still be able to view the report.
In case of regular dropdowns i have added a <Null> value to the existing values and set the default to null. But in case of multi-value, it does not give an option of adding <Null>

View 5 Replies View Related

Change/Add Report Fields To Display Based On The Parameter Selected

Dec 18, 2007



I have a report parameter named "Schools" which display a list of schools. For example, Alo elementary school, Balo middle school and Calo high school.

When "Alo elementary" is selected the report only display students from that school along with other assessments data fields. same goes for other schools too. But I want to display different data fields for "Calo high school" when it is selected. It is not currently possilbe becasue I am using the same template for all types of schools. There some fields only should be displayed for "Calo high school" but not for any other type of schools.

I can accomplish this by creating two separate report, one for "Calo high school" and the other for other schools. But I want to accomplish this just by creating one report. So when "Alo elementary school" is selected it displays report with certain fields and when "Calo high school" is selected then it displays same fields as "Alo elementary school" but as well as some other fields too in the report. Is this possible? Need help.

View 4 Replies View Related

Reporting Services :: Display Columns When There Is No Data To Display

Apr 30, 2015

I would like to display a portion of report where there is data or no data

There is data subreport  display   

     Product Name Latex Gloves  
     Product ID      
xxxx5678

 There NO data in the subReport
 
  Product Name                          
   Product ID    

View 3 Replies View Related

Power Pivot :: Selected Report Filter Values Display In A Cell

Oct 28, 2015

I have a power pivot with 2 multi valued report filters   student_branch & blood_group. These report filters are used to fetch the data set that contain below result set

student_branch               blood_group           count

Everything works fine. But, what i am looking for , is there any way to show the what are all the report filters that are selected currently by , separated in a separate cell ? below is the image for output reference.

View 2 Replies View Related

DISPLAY DAtes

Feb 28, 2007

Hello

I have this as a calculated field for displaying dates like 1/1/2007

=DATEADD("d", Fields!TimeIncInteger.Value , DATEADD("n", Parameters!GMTOffSet.Value,Parameters!BDateTime.Value))

This format increments the day vaues fine but how can I get it to increment month vaues too.

i.e presently it gives me day numbers perfect 1/2/2007 ..2nd january

but for 5th february it gives date as 1/5/2007. month value remains 1.

any help appreciated.

thanks

View 6 Replies View Related

SELECT DISTINCT - But Just One Of The Selected Columns

Sep 22, 2005

Hi,

I have a table with 3 columns - userID, score, dateCompleted

From this table I wish to select the highest score for each userID and the date for that score. If there is more than one row with the same userID and score (i.e. the user got the same score twice), I want to select the earliest date.

To clarify, there are multiple rows which have the same userID, and may have the same score too, but I want to pull out just 1 score for each userID.

I could do this quite easily if the DISTINCT operator worked on a per column basis, but it works on the entire row instead. Is there a way to SELECT DISTINCT column, but still output other columns in those rows?

I hope this is clear, but please let me know if I've confused you.

View 3 Replies View Related

Change Selected Rows To Columns

Nov 19, 2007

Hello,

I start with this table,










Tagname
Timestamp
Value

A
12/12/2007 12:00:01
1

A
12/12/2007 12:00:02
1,1

A
12/12/2007 12:00:03
30

B
12/12/2007 12:00:01
12

B
12/12/2007 12:00:02
12

B
12/12/2007 12:00:03
12

C
12/12/2007 12:00:01
13,4

C
12/12/2007 12:00:02
14

C
12/12/2007 12:00:03
14,5

I would like the table to be as following










Timestamp
A
B
C

12/12/2007 12:00:01
1
12
13,4

12/12/2007 12:00:02
1,1
12
14

12/12/2007 12:00:03
30
12
14,5

Does anyone knows how to do this within a query?

Thx,
Nic

View 9 Replies View Related

T-SQL (SS2K8) :: Display Dates For A Given Value?

Mar 4, 2015

I'm looking to identify the people who visited an office more than once and the dates that they visited. For example I have a table that looks like the following:

Name |Office Visit Date
-----------------------------
John| 2002-01-23
Mary| 2003-02-04
Fred| 2002-11-02
Jane| 2012-06-05
John| 2003-07-12
Mary| 2004-10-12
Fred| 2011-03-20
John| 2009-02-14

I want to display the following:

Name |Office Visit 1 |Office Visit 2 |Office Visit 3
----------------------------------------------------------------------
John|2002-01-23| 2003-07-12| 2009-02-14
Mary| 2003-02-04| 2004-10-12|
Fred| 2002-11-02| 2011-03-20

Note that Jane does not show up because she has only visited the office once and everybody else has visited the office at least two times.

View 2 Replies View Related

Count Of Distinct Rows Of Only Selected Columns?

Aug 9, 2012

I am trying to just get a count of the number of distinct rows are in a table --- not looking at the entire row of fields, but only selecting a few.

i don't want to see the distinct rows, i just want a count of how many are in the table.

View 4 Replies View Related

SELECT With NULL Values In One Or More Of Selected Columns

Apr 10, 2008

Hello everyone,

I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?


SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex

FROM Person LEFT OUTER JOIN

Person2Role ON Person.personID = Person2Role.personID

WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))

AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))

AND (Person.sex = ISNULL(@sex, Person.sex))

This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters


I am working with SQL Server 2005 Express Version

Thank you,

Alex

View 10 Replies View Related

SQL Server Admin 2014 :: Selected Columns On View

Mar 18, 2015

I have created row level security on two views and adding these two views to particular role.Today I have got an requirement that, middle level managers shouldn't see the all the columns. So I have created another role for Middle level managers and assign securables as those two views with selected columns by grant, and map all the middle level managers to this role. I thought my job is done. But these managers uses this view on SSAS(tabular model) and Excel, In those applications they are not able to load the data.

Later I come to know we can't use -- select * from ViewA ( in viewA I have restristced few columns in the role level) Work around is creating another view and assigning to the role. But how can we achieve column level security to implement this in either SSAS/SSRS/EXCEL?

View 6 Replies View Related

SQL Server 2008 :: Display Dates Between A Range

Mar 30, 2015

How to show the dates in between a range. Its hard for me to explain so I have ddl with the original results and then ddl of how I would like the desired outcome.

On the side I have a visit ID I need to show each day logged for each ID. Sometime the Start and End are a single day and sometimes they are a range. I need a row for each date.

CREATE TABLE #Results (VisitID INT, DateFrom DATE, DateTo DATE)
INSERT INTO #Results VALUES (361, '2015-03-07', '2015-03-07'), (361, '2015-03-08', '2015-03-10')
,(48, '2015-03-18', '2015-03-18'),(48, '2015-03-19', '2015-03-23')

SELECT *
FROM #Results
DROP TABLE #Results

[Code] .....

View 5 Replies View Related

T-SQL (SS2K8) :: Calculate And Display Week Between Two Dates

Nov 4, 2015

I want to display week between two dates as below.

requirement is as:

suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016

Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

Say for example the column date is coming as 10/23/2015 then it will fall in week-1

Same way if 11/01/2015 falls in week2.

View 3 Replies View Related

T-SQL (SS2K8) :: Aliases In OPENQUERY - Get Selected Columns By Their Ordering Number

Apr 9, 2014

I have the following query

SELECT [KPI].*
FROM
OPENQUERY(LINKED_OLAP,'SELECT
HEAD(TAIL(DESCENDANTS
(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,
([Game].[Game Code].&[1] ,

[Code] ...

The last three columns are dynamically generated because they change during time. Next month they will be different.

I like to introduce aliases for them and to have them in the select as 'TWO_MONTHS_AGO','ONE_MONTH_AGO', 'CURRENT_MONTH'

I wonder if exists something like [KPI].(0), [KPI].(1), and etc.. of the OPENQUERY to get the selected columns by their ordering number...

View 1 Replies View Related

SQL 2012 :: Number Of Variables Declared In INTO List Must Match That Of Selected Columns

Apr 28, 2015

I am getting error [[Msg 16924, Level 16, State 1, Line 13

Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.]] when i execute below script.

Declare @mSql1 Nvarchar(MAX)
declare @dropuser int
declare @dbname Nvarchar(max)
declare @username Nvarchar(max)

DECLARE Dropuser_Cursor CURSOR FOR

[Code] ....

View 9 Replies View Related

How To Update Selected Columns Of A Table In SQL Server Db Using Data From A Excel File?

Apr 4, 2007

Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!

View 4 Replies View Related

SQL 2012 :: Selected Subscriber Does Not Satisfy Minimum Version Compatibility Level Of Selected Publication

Feb 21, 2014

I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"

The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...

View 0 Replies View Related

Row Values Display In Columns

Jun 5, 2015

One Table have 2 Columns.

1.Order_number
2.PSON NUmber

In Table Contains 6 rows with 2 order_numbers and different PSON Number.I want Order Number,PSON1,PSON2,PSON3,PSON4 and its Values

Ex:--Order_number    PSON NUmber
             1                    ONT10
             1                    ONT11
              1                   ONT12
              2                   ONT13
               2                   ONT14

I want   data :-      Order_number    PSON1          PSON2        PSON3
                                  1                     ONT10      ONT11       ONT12
                                   2                      ONT13     ONT14       Null

View 3 Replies View Related

Creating Dates From Table Columns

May 23, 2006

Hi, i have a table with 3 ints that are used to store dates. Thedatetime data type is not used because this data comes from an oldAS400 server.I need to be able to use those 3 columns to build dates within a queryand be able to use them to compare themselves to other datesLet's say the table has the following values:myday mymonth myyear23 5 2006and suppose i want to do a query that displays all rows with dategreater than '20060520'Here is the query i have tried:selectcast(myday as varchar(2))+'/'+cast(mymonth asvarchar(2))+'/'+cast(myyear as varchar(4))from mytableThat query returns the string '23/5/2006' yet i can't use it to compareit with '20060520'Is there a way i can do this in a simple query?This is on sql server 2000

View 6 Replies View Related

Display Multiple Columns Into One Column

Feb 5, 2008

My first ASP.NET/SQL project. I'm creating an Asset Management DB. I wish to view the Asset number, the full name of the user it's assigned to and the Make and Model of each record in the DB. However assets are split into different categories e.g. monitors, PCs, Preinters etc. and they are all in different tables. The SQL below displays the asset number, Name of person assigned and the model of the asset.
SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model, PCModel.Model AS Expr1, PrinterModel.Model AS Expr2
FROM Hardware INNER JOIN
[User] ON Hardware.UserID = [User].UserID INNER JOIN
Model ON Hardware.ModelID = Model.ModelID LEFT OUTER JOIN
MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN
PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN
PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID
This outputs:-
Asset number     FullName     Model     Expr1     Expr2
00000                User Name   Model     NULL      NULL
00001                User Name   NULL      Model     NULL
00002                User Name   NULL      NULL      Model
However what i hope to acheive is output Model, Expr1, Expr2 into one column like so:-
Asset number     FullName     Model
00000                User Name   Model
Can i do this in the SQL or do i have to do it in my ASP.NET (VB) Page?
Using VSWD 2005 Ex Edition and SQL Server 2005 Ex Edition
Thank you for your replies

View 4 Replies View Related

How To Display The Values From Two Different Columns (was RS Question)

Jan 3, 2008

Does somebody know how do you get a column in Reporting Services to display the values from two different columns? I don't mean a concat, but the result of the two columns.

This has been a headache for 2 days now.

Thanks!!

View 3 Replies View Related

Don't Want To Display Sensitive Data In Columns

Oct 21, 2014

I have a table. I have some sensitive data that I don't want to display that sensitive data. The columns I want to abstract are

Firstname varchar(50) not null
Latname varchar(50) not null
dob datetime not null
addressline1 varchar(50) null
adressline2 varchar(50) null
city varchar(50) null
postalcode varchar(20) null

I want to display null values as nulls empty as empty not null as numbers.

View 4 Replies View Related

How To Display All Columns Of Distinct Rows

Apr 17, 2014

I have the below working query

select distinct OrderNum from invoiceHistory where orderNum in (56387,57930,57933,57935)

I need to get all columns of the distinct ordernum as resultset. I am working to get the resultset of below.

select invoiceID,distinct orderNum,invoiceDate from invoiceHistory where orderNum in (56387,57930,57933,57935)

View 1 Replies View Related

MS SQL Compare Columns To Generate Display Name

Sep 17, 2007

Hello, I have the following table with 4 columns....firstname, lastname1, lastname2, EMAILTable has user names and email, I would like to generate a 5th columncalled DisplayName.The email Id is sometimes firstname.lastname1.lastname2@ and othersjust firstname.lastname1@I would like to generate the display name exactly like the email egfirstname.lastname1.lastname2@ displayName = firstname lastname1lastname2......so for james.smith display name = James Smith and forjames.earl.smith displayName = James Earl Smith etc etcIs there a way that I can check/compare email Id (before the @ part)with firstname, lastname1 and lastname2 and generate a display namebased on what was used for the email address?I hope I've explained this well :-)Many thanks in advance for any help/adviseYas

View 5 Replies View Related

Find The Greatest Of Three Columns Per Each Row And Display

Nov 15, 2006

Hi,

i have a problem where in i have to display the greatest marks scored by each student.

How can i do this?? Is there any built in TSQL function.

Rgds..,

Aazad

View 4 Replies View Related

How To Display 2 Columns Of Data In 1 Column?

Aug 16, 2007


How to display 2 columns of Data in 1 column?

If I use like this

Select Names, 'myData' from emp

The output is:
Name1 myData
Name2 myData
Name3 myData

But, I need to display like this

Name1
myData
Name2
myData
Name3
myData

please advise

View 5 Replies View Related

SQL Server 2012 :: List Dates In Columns

Nov 11, 2014

I have a table (Event_Table) like:

EmployeeID, CustomerID, Date
1, 11, 2014-11-11
2, 13, 2014-12-10
1, 11, 2014-12-21
2, 13, 2015-01-11
1, 11, 2015-03-02

And now I would like to have a summary with a unique Employee/Customer combination and 3 Date columns like:

EmployeeID, CustomerID, Date1, Date2, Date3
1, 11, 2014-11-11, 2014-12-21, 2015-03-02
2, 13, 2014-12-10, 2015-01-11

Dates should be arranged with the first date in Date1, the next in Date2 and the third in Date3 (if there are forth and more dates I don´t care)

View 2 Replies View Related

Dynamic Dates In Columns On Matrix Report?

May 25, 2007

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:



MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08



(Col 1 - Col 12 separated by commas)



How do I do this in a matrix report column?

View 1 Replies View Related

SQL Server 2012 :: Display Columns With No Data

Dec 12, 2013

Trying to get the PSI Outcome, Expected, and PSIIndex every month whether it has data or not. Created a CTE and left outer joined with PSI table, but it's still not pulling every month for every PSIKey.

Table schematics

di.DivisionRegion,int
P.PSIKey,int
P.PSIOutcome,int
P.PSIExpected,int

[Code] .....

View 2 Replies View Related

Display The Variance Between Two Years In Row With Months In Columns?

Mar 17, 2008

I already read a lot about the inscope-function and how it is used to display variances over time periods. But I don't know where to start, as there is no tutorial how to setup this functionality. What I want to display within the report is the following:





Code Snippet

Months
ProductGroup Article Year 1 2 3 4 5 ....
Bicycles 1020 2007 1500 2000 etc.
2008 3000 3000
Var. abs. 1500 1000
Var. % 100% 50%
1025 2007 0 1000
2008 500 1200
Var. abs. 500 200
Var. % 500% 20%
Motorcycles etc.

View 14 Replies View Related

SQL Server 2012 :: Compare Dates Between 2 Different Rows And Columns?

Feb 18, 2015

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.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved