Hi All,
I have to fetch FromDate and Todate values from the table like this.Suppose Fromdate value is 02-Feb-2007 and Todate Value is 04-Feb-2007,then my need is to get the date value like this......Feb 2-4,2007or 2-4 Feb,2007.Can anybody know the syntax or code?.I am using sql Server and fromdate and todate values are stored in two different feilds in table.
Thanks and Regards
Hi All,
I have to fetch FromDate and Todate values from the table like this.Suppose Fromdate value is 02-Feb-2007 and Todate Value is 04-Feb-2007,then my need is to get the date value like this......Feb 2-4,2007or 2-4 Feb,2007.Can anybody know the syntax or code?.I am using sql Server.
Thanks and Regards
I need procedure query for two date between fromdate and todate
alter procdure date
declare @Fromdate varchar(10)
declare @todate varchar(10)
as begin
select * from master
where convert(varchar(10),@Fromdate,121) between CONVERT(varchar(10),@Todate,121) and CONVERT(varchar(10),rr.Original_Date,121)
end
I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query:
--------
select distinct
'On-Demand' as 'Business Line',
o.OrderID as 'Order #',
isnull(d.DisplayCode,'UNK') as Hub,
isnull(rz.RouteID,'UNK') as 'Default Route',
'On-Demand' as 'Assigned Route',
[Code] ....
I need formulating a view through which I can create a an output like below image -
Monthly Table
NUM STATUS ACTIVITYCODE
HAX603 Completed 0x45845a
HAX317 Completed 0x112z44
HAX465 Completed 0x1155x4
HAX523 Completed 0x124c69
Season Table
NUM STARTMONTH STARTDAY ENDMONTH EMDDAY
HAX603 JULY 1 OCTOBER 31
HAX317 DECEMBER 1 DECEMBER 31
HAX317 MARCH 1 MARCH 31
HAX317 July 1 July 28
[Code] ...
Final Output
NUM STATUS ACTIVITYCODE <SEASONS>
HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER
HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July
HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August
HAX523 Completed 0x124c69 1 November - 30 November
I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.
select num, (CAST(startday AS VARCHAR(3)) + ' ' + startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + endmonth)AS Season from seasons;
Hi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2
We have a piece of software and database for student registers.
One of the biggest problems is the database has been difficult to work with, as I have had to work out the date using dateadd functions and combine fields from different tables.
I've now got the date correctly, but I want to add the time to the time part of a datetime field.
The time field is already stored in a datetime field, but the date in this field is always 1899-12-30.
This is the function I am using to get the date:
Code:
DATEADD(dd, e.day_of_week, DATEADD(ww, sm.week, '01/08/2011'))
And this is the function I use to get the time (from a datetime field):
Code:
SUBSTRING(CAST(e.start_time AS varchar(20)), 13, 5)
With the DATEADD function it appears you can only add individual elements each time hh, mi, ss.
Would I have to add a DATEADD function for each time element in order to add the time to the date?
I have one table with text column(varchar50) and 2 value columns(INT)
it looks like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4
What i want to do is to combine c3 on row 3 and 4 so the output would look like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text3 ext4
I have tried to use CASE with no luck.
edit:
output like this is also fine
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4
5, null, text3 ext4
Hello to All,
I needs help to combine these together but how would I eliminate necessarily zero in front of "PropertyHouseNumber".
Table: DirectHome
Column fields.......
PropertyHouseNumber, PropertyStreetDirection, PropertyStreetName, PropertyMODE
0000001091 , W , 000026TH , RD
Thank you
RV
Hello I am new to SQL Server 2005 and am designing my first database.
In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.
Why would you not put it in a separate table with state/province or a separate table all togeather?
Thanks for your help.
I have a report that I am running in visual studio 2010, that gets its data from a few different stored procedures in a SQL 2012 Database. The variables are Date and Office Code. We currently have 6 different office codes. One of the Stored Procedures gets call information for each office. With the report we can select any single office or any combination of offices to compile data.
The problem I have is two of these share phone information, so when you select either one or both of them the same data gets returned. So for example we have office codes of AAAAA, BBBBB, CCCCC, DDDDD, EEEEE, and FFFFF. Now AAAAA, and BBBBB share phone information so if you select office code AAAAA, the phone info that is returned is for AAAAA, and BBBBB, and visa versa. So I am not sure how to accomplish that either in the report or in the stored procedure.
Here's a portion of the current statement.
UPDATE EngagementAuditAreas
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = 0,
bControlRiskIsAffirmed = 0,
bCombinedRiskIsAffirmed = 0,
bApproachTypeIsAffirmed = 0,
bCommentsIsAffirmed = 0
FROM EngagementAuditAreas WITH(NOLOCK) ...
And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).
UPDATE EngagementAuditAreas
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),
bControlRiskIsAffirmed = (numControlRiskID IS NULL),
bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),
bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),
bCommentsIsAffirmed = (parent.txtComments IS NULL)
FROM EngagementAuditAreas WITH(NOLOCK)
Thanks.
I want to combine two of my fields somewhat like when you combine to char fields and concatenate them. But these two fields are int. How do i do that? Here's my query right now:
Code Block
SELECT AutoID,
CONVERT(Varchar(Mars_Calender_Year )+ CONVERT(Varchar(Mars_Calender_Period_Code) as MarsId
FROM NavisionReplication.dbo.Tbl_Mars_Calender
ORDER BY Mars_Calender_Year DESC
what am i doing wrong?
Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909 T8888
And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?
Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.
View 2 Replies View RelatedGood morning.
I am importing an XLS file into one of my tables. The fields are:
Date Id Time IO
12/22/2006
2
12:48:45 PM
9
12/22/2006
16
5:40:55 AM
1
12/22/2006
16
12:03:59 PM
2
When I do the import, I get the following:
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
Here are my doubts:
1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?
Any comments or suggestions will be very much welcomed.
Cheers mates.
How do I specify a time period from first of this month to yesterday?
As a example I wanna see all loans disbursed from 1st of Jan 2008 to yesterday.
There is a field called Loan.DisbursedDate in loan table.
Thanks
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
I need to count and to list events for the current month todate and to count and list event for the rest or balance of the month.
Could someone assist me in preparing these separate queries? Thank you in advance for your assistance.
Thisis my query for list all events for the current month:
SELECT EventTimeBegin AS [Closing Date - Time], EventName AS [File No], AttyLName AS [Atty], Borrower1LName AS [Borrower], Borrower1FName AS [First], ProcessorLName AS [Processor], LOLName AS [LO], ClosingLocation AS [Location], MtgeBroker AS [Broker], EventID AS [ID], ModuleID AS [MID], CreatedDate AS [Order Date]
FROM RECalendar
WHERE
(
RECalendar.FileType IN ('CONSTR','ConstrPerm','Constr Refi Table Fund','Conv Refi','FHA Refi','HELOC','Purchase 0 Loan','Purchase Loan','Purchase Cash','Witness')
AND
RECalendar.ModuleID IN ('581','582','583','584','585','586','587','588','589','590','591','592','593','594','595','596','59 7','603','733','747','750','751','752','753','754','755','756','757','758','759','761','762','763',' 764','765','766','767','768','769','771','772','773','774','775','776','777','778','779','780','781' ,'782','783','784','785','786','814','815','816','817','818','819','820','821','822','823','824','82 5','826','827','828','829','830','831','832','833','834','887','888','889','890','891','892','893',' 894','895','896','897','898','899','900','901','902','903','904','905','906','907','908','909','910' ,'911','912','914','915')
)
AND
(
month(EventTimeBegin)=month(getdate())
AND
year(EventTimeBegin)=year(getdate())
)
ORDER BY EventTimeBegin ASC
I have added a sqldatasource to my form. I want to programmatically get field values directly from this control without adding a databound control such as gridview and then get values from the gridview.
Is this perphaps not the best way to use sqldatasource? Maybe it is meant to be used together with a databound control?
In that case what and how should I code it? Example please
/regards J
hello all,
is there any way to set a sql field to accept more than 255 characters... much like other dbs that have a memo field???
I have a ten field table (sql 6.5) and one of the fields has to hold a product description that is pretty long. Any way to do it?
thanks,
ak
I have a table "abc" with there fields of same data type 'x','y','z'.
'x' is the primary key for the table 'abc', what I supposed to do is to copy the values under field 'y' to field 'z' irrespective of the values already with 'z'.
look forward for your help
San
Hi,In oracle I have a LAG function using which I could get the previousvalue of a field.Do we have anything similar to that in SQL Server or access?ThanksDevi
View 4 Replies View RelatedI can not modify DDL of the table, so creating a trigger etc is not an option. This is my syntax that I am using, and each #Testing.abcd has different possible options.
For example, when #Testing.abcd = Test1 then #Data.field2
okay values are 'Yes', 'No', 'Maybe', but for #Testing.abcd = Test3 then #Data.field2 okay values are 'Follow-Up', 'Contact Requested'
How can I set-up "acceptable values" for one field, but different based off abcd in my syntax?
Declare abcd varchar(100), @sql varchar(1000)
Create Table #Testing (abcd varchar(100), efgh varchar(500)
Insert Into #Testing Values
('Test1', 'zzz'),
('Test2', 'xxx'),
[Code] ....
Hi,
In Microsft Access you can use the value of fields in calculations eg, txtbox_demand_value * qty - is this possible in Reporting Services????
I am trying to replace field values in SSIS. For exapmle, I have 'unkown' and 'N/A', and a few other values that mean the same thing, and I want to give all these fields the same value. I have not been able to find a tool in the toolbox to make this type of change. Is there one? And, if there is, do you know a link to a page explaining its use?
The closest I have come is the Derived column String Transformation:Replace. But, I have not been able to get this to do what I want. If you know of a good reference explaining this tool's use I'd appreciate it.
thanks much,
b2
i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date. i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this? what i currently have doesn't blow up, but neither does it actually insert anything. here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)"> <InsertParameters> <asp:Parameter Name="Major" Type="Int32" /> <asp:Parameter Name="Minor" Type="Int32" /> <asp:Parameter Name="Sub" Type="Int32" /> <asp:Parameter Name="Rev" Type="Int32" /> <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" /> </InsertParameters></asp:SqlDataSource>
View 7 Replies View RelatedHello Experts,
Been struggling with this issue all morning and beginning to get a headache. Essentialy my problem is this. I have a gridview control that ive bound to datasource where the select statement is using an inner join to retrieve textual info from one table in refernce to an object_id contained within both. i.e
SelectCommand="SELECT 'multiple fields from both tables'FROM ZCRMTAB_ACTIVITY INNER JOIN ZCRMTAB_ACT_LOG ON ZCRMTAB_ACT_LOG.OBJECT_ID = ZCRMTAB_ACTIVITY.OBJECT_ID WHERE (ZCRMTAB_ACTIVITY.REF_CUST_NO = @REF_CUST_NO) ORDER BY ZCRMTAB_ACTIVITY.CREATED_AT DESC">
<SelectParameters><asp:QueryStringParameter Name="fieldName" QueryStringField="value" Type="String" />
My problem is that this returns individual row results and are displayed on individual lines. Where the object_id is the same i want all the textual info to be displayed within the same row.
Does anyone know how i can accomplish this?
Thanks in advance
Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')
please teach me how to
xxx
I need to do a SQL query based on the two biggest values of a field. PLS , what s the SQL syntax : select * from Table where PRICE ......Ex: if the values of the field PRICE are : 50, 40, 100, 30 and 150.The request should select the rows that have Price equal 100 or 120Thanks a lot for help
View 2 Replies View RelatedTo All,
I have a table [multiple entries over the web] where the identity field values do not appear in order using a simple SELECT [no ORDER BY]. Why?
Can some one please tell me how to update a field in a table with multiple
values for each of the values in the other fields?
Thanks in advance.
I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?
View 2 Replies View Related