How To Display Result Of Different Year In Different Column Instead Of Different Row?

How To Display / Generate All Days In A Year?
Hi,
I'm wanting to generate a list of all the days that fall between a certain period of time (month, year etc.), but can't find any documentation on how to do this, does anybody know the sql query to do so?
TIA!

View Replies !   View Related

---

Display ONLY Month, Year From SQL2k
Tried this: SELECT CONVERT(varchar,fieldMonthYear,107) 'Month in Question' FROM .....That returns: Apr 01, 2006What I need is this: April 2006 or even Apr 2006. But no date for the day.Is there a way I can trim the center 4 characters of this now converted varchar? This is in a datalist, btw. Thanks!bs. 

View Replies !   View Related

---

To Display Month Name And Year Instaed Of Complete Date...in SSRS Reports
Hi All,
           I have a sample data like this..I have added the cases for the particular worker in the table..My question is that when displaying the cases for the particular month...the date should not be displayed..instead of date 2008-04-30 I have to display...like April 2008...if the date is 2008-10-20...it should display as October 2008...can anyone please help me with this...
312   KRISTI  WHITE                 865400           2008-04-30   2
312   KRISTI  WHITE                 1000264311    2008-04-30   3
312   KRISTI  WHITE                 1000430815    2008-04-30   1
312   KRISTI  WHITE                 1000660614    2008-04-30   1
312   KRISTI  WHITE                 1002371318    2008-04-30   2
312   KRISTI  WHITE                 2003722520    2008-04-30   4
 
 
Thanks
Chaitanya.
 

View Replies !   View Related

---

Is There A Way To Display This Query In 1 Result Set?
I am trying to count the number of Part that is repaired and those that is not repaired, is there a way to combine the following into one result set instead of returning 2? The bold line is the only condition that's different between this 2 query.
I want to display these fields: date_complete, part_categoryid, part_model, repaired, not_repaired
/* parts being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as repaired from  tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 0 and part_flag_ntf = 0 and part_flag_beyondrepair = 0)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid,part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)
/* parts completed but not being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as not_repaired from  tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 1 or part_flag_ntf = 1 or part_flag_beyondrepair = 1)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid, part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)

View Replies !   View Related

---

Display Of A Result Set Without Order By
Hi all

If we don't specify any Order by clause, what is the default order of displaying the data .

Will SQL Server show the data in a consistent order?

Thanks.

View Replies !   View Related

---

Display Query Result Vertically
Hi,

I have a query to run, but the data in the tables are stored horizontally. I want the query to output the result vertically.

e.g. if row 1 contains the following data:
custA,3-april2008,mango's,123,456,78,10

Then i want it to output as follows:
custA,3-april2008,mango's,123
custA,3-april2008,mango's,456
custA,3-april2008,mango's,78
custA,3-april2008,mango's,10

hope I'm clear, and would appreciate if someone could help me.

Thanks

View Replies !   View Related

---

Split Fields And Display Query Result
Hi,
I'm having a problem in spliting the fields
I need to ru the following query to join two tables and getting the output as shown.

Query:
select cusl.user_name,
pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'

Output:
user_name bills_ref_info payee_acid cust_acid txn_amt
SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318
SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327
SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031
SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918


Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query.
Can this be done? Please guide me on this...

View Replies !   View Related

---

How To Use A Function To Format And Display Result From Data Reader
Hi guys n gals !
I am having a few problems manipulating the results of my data reader,To gather the data I need my code is:
        // database connection        SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]);
        // sql statement to select latest news item and get the posters name        SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon);                // open connection        dbcon.Open();
        // execute        SqlDataReader dr = rs.ExecuteReader();
        // send the data to the repeater        repeater_LatestNews.DataSource = dr;        repeater_LatestNews.DataBind();
Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is:
    // prevent html    public string StripHtml(string data)    {        // grab the data        string theData = data;
        // replace < with &alt;        theData = Regex.Replace(theData, "<", "&lt;");                // return result        return theData;        }
But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???

View Replies !   View Related

---

Display A Group In A Report That Has No Data Rows In The Result Set
Hello
 
I have a report that retrieves its data from Analysis Services. The data includes a count and dollar value of projects against their current status:  It looks something similar to

 

(group1) status1    10    $200,000

(detail)     p1      1      $5,000

             p2      1     $10,000

              .

              .

            p10      1     $20,000

 

(group1) status3     5     $90,000

(detail)      .

              .

 

(group1) status4    15    $150,000

(detail)      .

              .

 

In the report I hide the detail rows.  I have a fixed/known number of statuses (in this case 4) and need to show all 4 in the report. eg

 

(group1) status1    10    $200,000

(detail)     p1      1      $5,000

             p2      1     $10,000

              .

              . 

            p10      1     $20,000

 

(group1)status2    0         $0

 

(group1) status3     5     $90,000

(detail)      .

              .

 

(group1) status4    15    $150,000

(detail)      .

              .

ie in this case I need to show status 2 (that doesn't exist in the data set) with zero totals.

 

Does anyone know if this is possible to get SSRS to display each of the status groups (in a known fixed list) and then match them to the records in the dataset.
 
As an alternative, if I were using SQL Server I could add rows to the dataset using a union statement.  Is there similar functionality using mdx?  My mdx skills are very basic.

 

Thanks 

 
Stewart

View Replies !   View Related

---

Connect To MSSQL2005 Using ASP2 And Display Query Result
 Question: I have searched here and on Microsofts site already but it seems that all solutions require already either some ASP2 knowledge or MSSQL knowledge .. I am quite new in both but need to realise this for a project.

I have installed a MSSQL 2005 server running MSSQL2005 Standard in mixed authentication mode. Services running using a domain account created for this purpose.
I have then created a simple database called test with a table called testtable

All I need to achieve now is a simple ASP2 page, which is located on the DC IIS, which connects to the SQL server (in same domain but different server) using WINDOWS authentication and returns the result of

SELECT * FROM TESTTABLE

I try now for a couple of days so I hope someone could help me with this .. I really need a step by step guide what I need to do on the SQL server side (so a specific user can connect to this particular database) and on the IIS side ..

I know it is always painful to help someone with little knowledge but I am getting desperate.

Thanks a lot guys

View Replies !   View Related

---

Do Not Display The Result Of My Long Text String, Approx About 400 Characters
I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??

SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE

WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
.......

View Replies !   View Related

---

How To Display The Search Result Of A BookStore With Pictures Of Books, In A Proper Format.
Hi all,
I am using ASP.NET 2003 with SQL Server as database. 
I have a database of a book store with BookPicture, Author, Title, and Description of the Book.
Now when the user searchers for a  book with a keyword, how can I display the results which should show:
1. The picture of the book, 2. The at it right, Title of Book, 3. The author,4. The descritionThen the image of "Add to cart"
Each search result must be separated by a box like the cell of a table. 
Is it possible to be done?
Thanking you in advance
Tomy
 

View Replies !   View Related

---

How To Access Data From Different Database And Display Result Set In Managed Stored Procedure
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Text
Imports System.Collections

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _
                                          ByVal strCompany As String, _
                                          ByVal decSerial As Decimal, _
                                          ByVal strSerialCode As String, _
                                          ByVal strSerialScan As String, _
                                          ByVal decMasterSerialNumber As Decimal, _
                                          ByVal strCustomerPart As String, _
                                          ByVal strTakataPart As String, _
                                          ByVal strCustomerRanNo As String, _
                                          ByVal strCustomerAbv As String, _
                                          ByVal strDestinationAbv As String, _
                                          ByVal decQty As Decimal, _
                                          ByVal strCreatDate As String, _
                                          ByVal decVoidSerialNo As Decimal, _
                                          ByVal strProductionLineNo As String, _
                                          ByVal strProcType As String)

        Dim sp As SqlPipe = SqlContext.Pipe
        Dim strResult As Integer = 0
        Dim strErrorText As String = String.Empty
        Dim dsData As New DataSet
        Dim parameter(15) As OleDbParameter
        If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then
            ' Populate parameter collection

            parameter(0) = (CreateParameter("PARM1", OleDbType.Char, 20, ParameterDirection.InputOutput, strAS400ServerName))
            parameter(1) = (CreateParameter("PARM2", OleDbType.Char, 2, ParameterDirection.InputOutput, strCompany))
            parameter(2) = (CreateParameter("PARM3", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decSerial))
            parameter(3) = (CreateParameter("PARM4", OleDbType.Char, 2, ParameterDirection.InputOutput, strSerialCode))
            parameter(4) = (CreateParameter("PARM5", OleDbType.Char, 25, ParameterDirection.InputOutput, strSerialScan))
            parameter(5) = (CreateParameter("PARM6", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decMasterSerialNumber))
            parameter(6) = (CreateParameter("PARM7", OleDbType.Char, 30, ParameterDirection.InputOutput, strCustomerPart))
            parameter(7) = (CreateParameter("PARM8", OleDbType.Char, 15, ParameterDirection.InputOutput, strTakataPart))
            parameter(8) = (CreateParameter("PARM9", OleDbType.Char, 15, ParameterDirection.InputOutput, strCustomerRanNo))
            parameter(9) = (CreateParameter("PARM10", OleDbType.Char, 6, ParameterDirection.InputOutput, strCustomerAbv))
            parameter(10) = (CreateParameter("PARM11", OleDbType.Char, 6, ParameterDirection.InputOutput, strDestinationAbv))
            parameter(11) = (CreateParameter("PARM12", OleDbType.Decimal, 9, ParameterDirection.InputOutput, decQty))
            parameter(12) = (CreateParameter("PARM13", OleDbType.Char, 10, ParameterDirection.InputOutput, strCreatDate))
            parameter(13) = (CreateParameter("PARM14", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decVoidSerialNo))
            parameter(14) = (CreateParameter("PARM15", OleDbType.Char, 3, ParameterDirection.InputOutput, strProductionLineNo))
            parameter(15) = (CreateParameter("PARM16", OleDbType.Char, 2, ParameterDirection.InputOutput, strProcType))

            RunDB2Sp("FABLE.MAP", parameter, dsData)

            If dsData.Tables.Count > 0 Then
                dsData.Tables(0).TableName = "Supreeth"
                Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString()
                Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString()

                ' I am not sure here
                SqlContext.Pipe.Send(bitresult)
                SqlContext.Pipe.Send("No errors")


            End If

        Else
            Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid")
        End If

    End Sub

Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet)
        '*********************************************
        ' Declare Variables
        '*********************************************
        Dim daAdaptor As OleDbDataAdapter
        Dim cmdAS400 As OleDbCommand
        'Dim dstestMe As New DataSet
        Try
            cmdAS400 = CreateCommand(strProcedure, parms)
            daAdaptor = New OleDbDataAdapter(cmdAS400)

            ' Fill the Data Set
            daAdaptor.Fill(dsData)
        Catch expError As OleDbException
            daAdaptor = Nothing
        Finally
            daAdaptor = Nothing
            cmdAS400.Dispose()
            'Me.Close()

        End Try

    End Sub
    Public Shared Function CreateParameter(ByVal name As String, _
                                                   ByVal type As OleDbType, _
                                                   ByVal size As Integer, _
                                                   ByVal direction As ParameterDirection, _
                                                   ByVal paramValue As Object) As OleDbParameter
        Dim param As OleDbParameter = New OleDbParameter
        param.ParameterName = name
        param.OleDbType = type
        param.Size = size
        param.Direction = direction
        param.Value = paramValue
        Return param
    End Function

    Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand
        Dim CmdSAS400 As OleDbCommand
        Dim parameter As OleDbParameter
        Dim connAS400 As OleDbConnection
        connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;")
        connAS400.Open()

        CmdSAS400 = connAS400.CreateCommand()
        CmdSAS400.CommandText = strProcedure
        CmdSAS400.CommandType = CommandType.StoredProcedure
        CmdSAS400.Parameters.Clear()
        'CmdAS400.CommandTimeout = intTimeOut
        If (prams Is Nothing) Then
        Else
            For Each parameter In prams
                CmdSAS400.Parameters.Add(parameter)
            Next

        End If

        Return CmdSAS400

    End Function

I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset.
My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs

Thanks

View Replies !   View Related

---

How Can I Send Row Data To A SQL Database Table Via VB 2008, And Permanently Save And Display The Result?
 This is related to:
How can I make some graphics drawings stick while others disappear?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1
 
 
Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called €œHexMap€? that contains some columns that I am ready to insert some row data into.  Here is what my program should do:
 
As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor.  If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database.  Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:
 
http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg
 -sorry, I didn't capture the mouse cursor inside the red hexagon
 
I think I am missing something since I appear to be able to connect successfully to the database table.  Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer).  I am hoping someone will review my code snippet (below) and tell me what I am missing.  What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.           
 



Code Snippet
'======================================================================================
Dim CN As New SqlClient.SqlConnection()
Dim da As New SqlClient.SqlDataAdapter
 
'Consider using Me._adapter that is used already
 
CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
CN.Open()
 
'Use the following code to verify that a connection to the database has achieved
If CN.State = ConnectionState.Open Then


 MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")
End If
 
Try
 
Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)
End Try
 
'use the Connection object to execute statements
'against the database and then close the connection
da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)
 
If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================
 
Dim rows As Integer
 
rows = 0
 
Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)
 
CN.Open()
 
rows = CMD.ExecuteNonQuery
 
If rows = 1 Then
                MsgBox("Table HexMap updated successfully")
            Else
                MsgBox("Failed to update the HexMap table")
End If
 

If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================


 
Thanks for reviewing my code.
 
Technozoide 
 

View Replies !   View Related

---

How Can I Add A Fiscal Year Column To A View
I have a view that shows payment amount, payment date. I need to add a column to the view that shows what fiscal year the payment belongs to.
How can this be done?
I do have a table that has the fiscal start and end in it, tblGlobal with fields FiscalYearStart and FiscalYearEnd.
 

View Replies !   View Related

---

Year Function In Derived Column Gives Error
Hi ,
Year function in derived column gives error if the incoming date is less than 1/1/1753. Is this Issue or required behaviour

Thanks
Dharmbir

View Replies !   View Related

---

Extract Month & Year From Date Column
hi

i have column in database as account open date

format as:Jan 27,2004 12:00:00:AM

How do i extract month& Year from this column..
all i have to do a calculation

if accountopendate is prior to dec 31 1994 then jan 1995..

and if the account open date is after 2100 then ist jan 2011.

how do i write the calculation

Thanks guys
phani

View Replies !   View Related

---

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View Replies !   View Related

---

Compare Given Period In Current Year / Previous Year
HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan

View Replies !   View Related

---

Select Month/year When Range Spans Year
I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.

The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.

I've tried evaluating the parts of the episode date seperately like:


where
(epimonth>=10 and epiyear=2003)
or
(epimonth<=2 and epiyear=2004)


and that works, but what happens when someone wants to see from 10/2002 to 2/2004?

Any suggestions on the best way to do this?

View Replies !   View Related

---

Display ROW As A COLUMN
I am JOINing a Table with a View, and the View can have multiple rows relating to the join condition(i.e. My view as 4 columns, and can look like the following:
A  B C D
A E F G
B H I J
I want this to look like -
A B C D E F G
B H I J
I want one row, but not one column, I need to maintain each value in its own column, withinin a single row. 
Doesn't matter to me if that transition happens in my View or as the outcome of my Join.
I've been looking at the PIVOT command, but that appears to only work with an aggrgrate function. I don't want to calculate/aggreagte anything, just combine my multiple rows in one.  
This is my VIEW stmt -
SELECT     [PKG ID] AS PKG_ID, [APPR GRP NAME], [USRID OF APPR], [APPR STATUS]FROM         dbo.pkgapproversWHERE     ([APPR STATUS] = 'approved') AND ([APPR GRP TYPE] = 'E')
This is my Join stmt - set @sql = N'SELECT DISTINCT [PKG ID] AS PKG_ID, [ELM ACT] AS Action, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment, [USRID OF APPR]
FROM [PkgAction]
FULL JOIN vw_PkgApprovals
ON PkgAction.[PKG ID] = vw_PkgApprovals.[PKG_ID]
WHERE 1=1'
 
Any thoughts....Thanks
 

View Replies !   View Related

---

Re-display Result Set Without Re-running Query In Query Analyzer?
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.

View Replies !   View Related

---

How *NOT* To Display Header Column
Hi,

1. This is what SET NOCOUNT ON does:

-- SET NOCOUNT to ON and no longer display the count message.
SET NOCOUNT ON
GO
SELECT au_lname
FROM authors
GO


2. Now, is it possible and what is the statement to use if I don't want to display
the header column? au_lname. Thanks.

View Replies !   View Related

---

How To Display Column Descriptions?
Hello!

I am wracking my brain, trying to figure out how to return & display a column's description.

I work in Cold Fusion with SQL 2000 database.

My table name is "clients" and the column in question is "client_firstname"

The description of this column, as entered in Ent. Mgr, is "First Name"

If anyone can please help me, it would be very greatly appreciated!

Thanks!

Now for the details...

I am trying this:

----------------------------------------------------------------
<cfquery name="get_ext2" datasource="#application.dsn#">
SELECT
objname As 'Column'
, value As 'Description'
FROM
::fn_listextendedproperty
('MS_Description', 'user', 'dbo', 'table','client', 'client_firstname', default)
</cfquery>

<cfoutput>
"#get_ext2.Column#"
"#get_ext2.Description#"
</cfoutput>
----------------------------------------------------------------

... and it is returning this:

"" ""

In my debug output, the query result appears as such:

----------------------------------------------------------------
get_ext2 (Records=0, Time=16ms)
SQL =
SELECT
objname As 'Column'
, value As 'Description'
FROM
::fn_listextendedproperty
('MS_Description', 'user', 'dbo', 'table','client', 'client_firstname', default)
----------------------------------------------------------------

Haaalp!

Thanks....

Richard

View Replies !   View Related

---

Combine 2 Column Into 1 In Display
I want to know how to combine 2 column in a table into 1 column to display it in a datagrid.
For example i have firstname and lastname in the table. I need to display as Name in datagrid where it have to combine firstname and lastname. How can i do that?

And i also want to know how to change the value to display it in datagrid. In my table the value is only '1' or '0'. Where i need to display as Active when it is '1', Unactive when it is '0'. Is this do in programming way?

Thanks in advance.

View Replies !   View Related

---

Filter On One Column But Display Another
I have a date column and a value column. I want to display the value column based on the last date. MAX (Date)

I tried using MAX for the date column but that displays the last date.

Any Ideas?

View Replies !   View Related

---

Display Column Description
I have this easy sql for a drop down.

select code_value, desc1
from dbo.market_area_code
where code_value in ('1', '2', '4')

How can I add something to display something like

if code_value = '1' then display 'Blue'
if code_value = '2' then display 'Orange'
if code_value = '4' then display 'Red'

View Replies !   View Related

---

Conditional Display Of A Column
Would like to know if it is possiblefor any of you to suggest how to conditionally diaplay a Column based on the values of the other columns.
Here's my query
SELECT
[ProductionDay]
,DATEPART(wk,StartTime) AS WeekCount
,(AVG([RNTDuration])) AS AvgRNT
,MIN([RNTDuration]) As LeastRNT
,MAX([RNTDuration]) As WorstRNT
,dbo.fn_MinimumRNT_sp([ProgramNo],[Machine],[ProductionDay]) AS BestRNT
,dbo.fn_MinimumRNT(SubAssemblyNo,[Machine]) AS BestRNT_txt
,SUM([LoadingDuration]) AS LoadingTime
,[ProgramNo] AS ProgramNo
,COUNT(RNTID) AS PartsCount
,[SubAssemblyNo]
,[Sheetsize]
FROM [RPMS].[dbo].[List_MachineShopRNT]
where Machine = @iMachine and dateadd(month, datediff(month, 0, [StartTime]),0) = @dtMonthStartDate
Group by DATEPART(wk,StartTime), [ProductionDay],[Machine],[ProgramNo],[SubAssemblyNo],[Sheetsize]

Here I would like to Run the function dbo.fn_MinimumRNT_sp or dbo.fn_MinimumRNT if the ProgramNo is 100

Thank you

View Replies !   View Related

---

How To Add The Total Balances Of Previous Year Into The Next Year?
I'm working with a table called SALFLDGAMS where it has two columns that I need to work with.  One of the columns is Period which has years from 2000 to 2005 and the other column is called amount which has the balance for that year.  Let me explain in a little more detail.  There are account codes associated with the years as well so there will be many places where for example the year 2000 will show up with a given value.  What I'm trying to do is to create a view which has a BeginBalance column which adds lets say all the values for 2000 and sticks them to 2001.  So what I'm saying is all the values from the previous year I want them in the current year.  All in one column.  Thanks for the help guys.

View Replies !   View Related

---

Parameter Year And Previous Year (Beginner)
I have one matrix that shows the CrashCount (measure) by month. Looks like this:

 

Jan    Feb   Mar    Apr     May   Jun     Jul    Aug    Sep   Oct   Dec 

25       90     100     55     52        55     22     55       22     35   65

 

The user selects a Year as a parameter. I want to put another matrix in that displays the previous year, just as the first year is displayed. How do i edit the second matrix? Do i put the parameter as

=(Parameters!CrashStatisticalYear.Value)-1 or is there some other way this can be done. Without having the user put 2 years. I just want them to pick one. And the previous year shows up in the matrix below this one. Can anyone help me with this...what should i do?

View Replies !   View Related

---

Sales For Current Year To Previous Year
Guys,
 
         I wanted to find the ratio: (sales made for current year 2007 - sales made for previous year 2006)/sales made for previous year 2006.
 
so, the result should be something like this:
 
Year:         Sales:       %change in sales:
2005          100              10%
2006          200              20%
2007          300              30%
 
How do I write a query for this...??? so that i can plot this in a chart in SSRS.
 
somebody help me.
 
  

View Replies !   View Related

---

Need Help On Getting Result From Row To Column
Hi
I need help to build the below data to my requirement
i have table having the following data's








NoofLive
Action
Sold
Ratio
EffDt

10-49
43
0
0
1/1/2008

50-99          
62
0
0
1/1/2008

100-199        
73
0
0
1/1/2008

200-299        
17
1
0.059
1/1/2008

300-499        
25
0
0
1/1/2008

500-999        
21
0
0
1/1/2008

1000+          
45
0
0
1/1/2008

100-199        
11
1
0.091
2/1/2008

1000+          
1
0
0
2/1/2008

100-199        
17
0
0
3/1/2008

500-999        
2
0
0
3/1/2008 
EffDt = (Jan-08,Feb-08,Mar-08)
I want creat result as follows from the above data's
















Jan-08
Feb-08
Mar-08

Eligible Lives
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio

10 - 49
43
0
0
0
0
0
0
0
0

50 - 99
62
0
0
0
0
0
0
0
0

100 - 199
73
0
0
11
0
0
0
0
0

200 - 299
17
1
0.059
0
0
0
17
0
0

300 - 499
25
0
0
0
0
0
0
0
0

500 - 999
21
0
0
0
0
0
0
0
0

1000+
45
0
0
1
0
0
2
0
0
Please anybody can help build a query to generate the above result.
Thanks in advance
sthanika
 
 
 
 

View Replies !   View Related

---

Error: Column Name &"X&" Appears More Than Once In The Result Column List.
Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below.  Any clues on how to fix this?  Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: }
Line 1445: try {
Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();
Line 1447: return returnValue;
Line 1448: }

View Replies !   View Related

---

One Table, Two Condition, Display Result As One Table
I got one table with 3 columns = Column1, Column2, Column3

Sample Table

Column1 | Column2 | Column3
------------------------------------
A | 12 | 0
A | 13 | 2
B | 12 | 5
C | 5 | 0

Select Column1, Column2, Column3 as New1
Where Column1 = A AND Column2 = 12 AND Column3 = 0

Select Column1, Column2, Column3 as New2
Where Column1 = A AND Column2 = 12 AND Column3 >0

The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table.
So how do i display the result in one table where the new Output will be in this manner

Column1 | Column2 | New1 | New2|

Thanks

View Replies !   View Related

---

Display Multiple Columns Into One Column
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 Replies !   View Related

---

Must I Display A Column In DataGrid To Reference It?
Hi,I have a DataGrid whose DataSource is an SQLDataSource. This SQLDataSource references a SQL Server table with an Identity column, named Id. The SQLDataSource has generated an Update statement for me, but this only works if the Id column is selected for display in the DataGrid, and has Visible = True. If not, then the @original_Id parameter to the generated Update statement has a value of Nothing, and the Update has no effect. So is there a way to enable the Update statement to work, without the user having to see the Id column in the DataGrid? Many thanks,Keith.

View Replies !   View Related

---

Change Results In Column To Display Different
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.

Here goes..

SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC

View Replies !   View Related

---

How To Display Column From Inserted Table
Hi all,

I have created a trigger in SQLExpress which looking for any insertion to a specific table in a database:

Create Trigger Tg_Insert on [tablename]
FOR Insert
AS 
Select * FROM Inserted
---------------------------------------------------------
Assume there is nothing wrong with this trigger as I have already checked the output with Query tool.
My question is how can I display the newly inserted data to the client? In my case, Console application.
I want to do something like Console.WriteLine("data from inserted table here");
How can I achieve this? FYI, I'm using VS2005 with sqlexpress 2005.
Thanks in advance.

Regards,
SHAH

View Replies !   View Related

---

Multi Column Display In A Report??
 

I have a table with a data cell which is printing top-to-down
 
1
2
3
4
5
6
7
8
 
I want it to print like
 
1      2      3     4      5      6     7      
 
How to do this??
 
The cell is in a group detials sections. and I am using Reporting Services 2000 with VS.NET 2003
 
Thanks in Advance

View Replies !   View Related

---

How To Display 2 Columns Of Data In 1 Column?
 
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 Replies !   View Related

---

Column Name As The Result Of A Query?
Simple example would look like that in MS SQL

SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)

So that the display is

English
a

as we assume that

SELECT language_name FROM language WHERE language_id = 1

returns only English

I have tried to that with a variable but it does not work

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that

LOL

I just tried another way as i was going to post

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"

and it worked!

Posting anyway so people might answer with a better solution with no variable

Thanks a lot

Mordan

View Replies !   View Related

---

Add Apostrophe To Column In Result Set
I have a brain teaser for you all.

The end result: I need one of the columns in my result set (col2) to have a preceeding apostrophe (i.e., '001234 ).

The problem: I am building my query as a string and then using the EXEC() function to execute it. For example:
SET @strSQL = 'SELECT col1, col2 FROM mytable'
EXEC(@strSQL)

Just to tame any answers that might not be directed to the specific question, the query Must be built as a string and then executed.

I have been unable to obtain the solution and I am wondering if it is even possible?

TIA

View Replies !   View Related

---

Aggregate (SUM) A Column And Then Use The Result?
Hi,

 

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

 

Thanks

View Replies !   View Related

---

Display Database Column In Alphabetical Order
Hello
I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From  Name_CatEWhere Education = 'yes'Order ByLName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
 SELECT LName FROM Name_CatEORDER BY LName ASC
Thanks
Lynn

View Replies !   View Related

---

How To Display The Column Description In Query Analyser
Hello All.

I have created a table with short name as the file name but have entered the description in the column box (when creating a new table - see attached). I would like to show this description information in query analyser. Can this be done? I used sp_help <table name> but it doesn't show the description.

Please advise. Thank you.


Best regards

View Replies !   View Related

---

DISPLAY COLUMN GRANDTOTALS IN A MATRIX REPORT
Hi there...

I have a matrix report within a list so it functions as a report with dynamic columns.

Matrix a:

          a   b   c   d   e

x        1   2   4   9   12  <NEED SUBTOTAL across Columns for row x>

y        6   2   4   5    9   <<NEED SUBTOTAL across Columns for row y>

TOTAL <need totals going down rows and need a grand total>

 

Remember the number of rows and columns are dynamic so I have NO CLUE how to add a column and display it if and only all the columns have been displayed ...sort of like a COLUMN and ROW footer for a matrix report.

 

Any help will be appreciated

thank you

Prashanth Prasanna

View Replies !   View Related

---