Hello, this is my first post to these forums so... Hi.
I'm currently doing a select statement and I cannot figure out how to get it to do what I want it to.
I'm trying get the statement to show how much activity was done in each year in a separate field and I don't have any idea where to start to get this accomplished.
Here's the code:
select distinct client.Number as [ClientNumber], client.FirstName, client.LastName,
client.Address, client.City, client.State, client.Zip,
count(distinct [transaction].date) as VisitsWithinDateRange, client.LastVisit
from client
inner join [transaction] on client.number = [transaction].client
where
[transaction].date >= '01/01/1980'
and
[transaction].date <= '01/01/2008'
and
client.status like '%a%'
and
client.status not like '%n%'
and
client.status not like '%i%'
and
client.clienttype <> 'C'
group by client.number, client.Number, client.FirstName, client.LastName,
client.Address, client.City, client.State, client.Zip, client.LastVisit
Code Block Create table #UserList(userid nvarchar(20), stcod smallint) Insert INTO #UserList VALUES('State. : 1',0); Insert INTO #UserList VALUES('Jack12',0); Insert INTO #UserList VALUES('Tommy22',0); Insert INTO #UserList VALUES('State. 2',0); Insert INTO #UserList VALUES('Kathy25',0); Insert INTO #UserList VALUES('State 4',0); Insert INTO #UserList VALUES('Tim',0); Insert INTO #UserList VALUES('State.: 6',0); Insert INTO #UserList VALUES('Jimmy',0); Insert INTO #UserList VALUES('Boopla1',0);
The final data looks like this :
Code Blockuserid stcod State. : 1 0 Jack12 0 Tommy22 0 State. 2 0 Kathy25 0 State 4 0 Tim 0 State.: 6 0 Jimmy 0 Boopla1 0
1. I want to first update the stcod. Select rows which start with 'State' in userid and pick the numeric value in that. Eg: In 1st row, the numericvalue is 1 and update the stcode with that value till u enounter another row starting with 'state'
2. After updating all rows, remove the rows which start with 'State'
Expected Output :
Code Blockuserid stcod Jack12 1 Tommy22 1 Kathy25 2 Tim 4 Jimmy 6 Boopla1 6
Plz help me with sql query for this. Thanks a lot.
Folks, I am sort of new to SSIS.. I am doing the following
- Have a package that does some data transformation - The transformed data is written to a csv file - The csv file is copied to multiple destinations by a script task
All the above actions are performed in one package.
The SSiS log is not very helpful it just mentions a generic error code that is of no use.
Q1) If I need to separate the above actions in the Sql Server job scheduler as separate tasks do I have to split the above tasks into separate dtsx files ? If so how can we have the first dtsx kickoff the second one and can the second dtsx file share the variables ?
Q2) does anyone have any suggestions or examples on how log each step of your dts package to a log file
I will like to seperate the contents of sp_dbfixedrolepermissioninto groups by the fixedrole (With the LUXURY of a heading).Is therea any short way or straight forward command to do that?TIATY
I'm just getting into using sql server 2008 and the report builder 3.0 addon after a long time away from sql and I was wondering if the following was possible.I have a number of records with a start and end date along with a volume i.e.
Essentially this means for each day between the two delivery dates the stated volume is delivered, what i need to try and do is to find the volume for each day between two user entered dates, giving a result along the lines of
Start Date Entered = 01/06/12 End Date Entered = 03/06/12
Date; Total Volume 01/06/12; 80,000 02/06/12; 80,000 03/06/12; 30,000
Hi, I am having chart which has positive and negative numbers.I need dark line(gridline) at 0.00th location.So that it differentiates between positive and negative levels.So please help me out in solving this asap.
I have a form that's filtering search results and porting to a gridview. The drop down is selecting from the date column of SQL, but i want it the default value to show all dates in the gridview. In a normal string field, you can just use "%", but a datetime field won't take this. What can i use to show all dates?
This is the best suited forum I found that fits my problem. If there's a better one, please direct me to it.
I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables). My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device). I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates. That way I can only compare relevant data and not the whole database.
For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007. Another example: Filter out rows that their Address column was modified in the last two weeks.
I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables. But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.
Is there a chance this is implemented inside Access (or SQL server, for that matter)? The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.
Any help will be greatly appreciated!
Thanks.
P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.
P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)
I have a table with several million rows of data. There is a date field defined as a char(8) with some bad rows. i tried to locate them with below
select date_stopped from patient_medication where isdate(convert(datetime,date_stopped)) = 1
This won't work, I get the Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
(This was previously posted @ http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1935938&SiteID=1&mode=1, I transferred it here because it's more relevant)
I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables). I use C++ non-.NET ADO in order to access that database (msado15.dll). My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device). I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates. That way I can only compare relevant data and not the whole database.
For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007. Another example: Filter out rows that their Address column was modified in the last two weeks.
I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables. But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.
Is there a chance this is implemented inside Access (or SQL server, for that matter)? The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.
Any help will be greatly appreciated!
Thanks.
P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.
P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)
I am doing a search on a column of type varchar, the columns all contain dates in the future. When i perform a query
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
,[CIFExpDate]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE CAST([CIFExpDate] AS smalldatetime) between '12/01/2000' and '01/30/2015'
I get error:
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
I can use this same query on an exactly same other field that contains dates in the past. Is there some kind of limitation on the dates as far as them being in the future or past?
I am using Sql Server 2012. I have a table which has a field as Datetime (it is a table in Dynamics CRM 2011 so I have no control of the data type). Say this field is called BisStartDate. If I run this query in management studio.
select BisStartDate, BisStartDateutc from myTable where _bisnumber=10375
I will get:
BisStartDate BisStartDateutc
2014-07-29 00:00:00.000 2014-07-29 05:00:00.000
*in CRM, datetime is saved in 2 fields, one is the current time, the other one is the utc time.
You can see the offset between the datetime and utc is 5 hours.
However when the same statement was running inside a SSIS package on the server, the result returned is:
I will get -5 if I run it in ManagementStudio and -6 is running on server package(running inside VisualStudio will be -5, same as running a query in ManagementStudio).
I think when the record was saved, “date” is 5 hours offset to UTC time but now the system use the current utc offset which is 6 hours. I just want to use the BisStartDate as it is. How do I let the SSIS turn off the conversion.
The same datatime is saved in another system then we compare them to check the data entry. Now because of this one hour difference, sometime the Day will be different.
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID Quarterly Monthly Days Period Year BARmRev AARmRev PYRmRev
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 3462.14 5107.65
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 NULL NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed.
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie. Code Description Brand ABC1 BLANK DVD SONY ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
