MS SQL Server :: Trying To Split My Columns Into Years (col1 Must Have Vals For 2005, Col2 Vals For 2006)

Insert Vals Into SQL From Access That Don't Exist
I've got an access table with about 2 million rows. I'm using this to update a table in SQL that holds pretty much the exact same data, only with an added Identity column.

From week to week, the access table grows. For example, next week it may have 2.1 millions rows, the week after 2.2 million, etc.

The goal of the DTS is to keep the SQL table up to date w/ the access one. In the past, this has been done by deleting everything from the SQL Table and then importing the ENTIRE access table. This not only takes more time then need be, since the majority of the records *already* existed, but it also threw referential integrity out the door - other tables should be referencing the Identity in the SQL Table. IDEALLY, the only rows that would be transferred from the access file are ones that don't already exist in the SQL table.

I don't want to re-invent the wheel, and have to confess being a little under-schooled on all that SSIS has to offer. Is there a Data Flow Transformation that would solve this?? Any other advice? If all else fails, I'd probably just dump the entire access table to a temp table and then insert vals into the production table that don't exist, but even this would require more temp hard drive space then I'd like.

Thanks!

View Replies !

Stored Proc: Query Vals To Local Variables
I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.

Thats the dream.

The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.

Here is what I have as of this moment for my sp.

ALTER PROCEDURE usp_CalculatePunchcard
AS
DECLARE @dtPP DateTime
SET @dtPP = (SELECT thursday_in1
FROM punchcards
WHERE (punchcard_id = 1))
/*
Also tried....
SELECT @dtPP=thursday_in1
FROM punchcards
WHERE (punchcard_id = 1)
*/

PRINT @dtPP

RETURN
/*
for some reason i can't use GO ... even though every
document i've read on stored procedures has used GO
and none use RETURN
*/

The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '

Any help would be greatly appreciated as I am about to kick someone/something.

Thanks

View Replies !

Peculiar Behavior In Stored Procedure (outputs Are Returning Proper Vals For Uniqueidentifiers And Ints, Not Nvarchars)
Rather than the real code, here's a sample we came up with.

Here's the C# Code:
public class sptest : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
private DataSet dtsData;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string strSP = "sp_testOutput";
SqlParameter[] Params = new SqlParameter[2];
Params[0] = new SqlParameter("@Input", "Pudding");
Params[1] = new SqlParameter("@Error_Text", "");
Params[1].Direction = ParameterDirection.Output;
try
{
this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);
Label1.Text = Params[0].Value.ToString() + "--Returned Val is" + Params[1].Value.ToString();
}
//catch (System.Data.SqlClient.SqlException ex)
catch (Exception ex)
{
Label1.Text = ex.ToString();

}
}

Here is the stored procedure:

CREATE PROCEDURE [user1122500].[sp_testOutput](@Input nvarchar(76),@Error_Text nvarchar(10) OUTPUT)AS
SET @Error_Text = 'Test'GO
When I run this, it prints up the input variable, but not the output variable.

View Replies !

Concat All Col2 Values For Each Col1, And Add Sum(col3) (was &"query Help&")
Hi,
Can anybody help me to create a single query? I have this problem.

CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)

INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)

INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)

INSERT INTO t1 VALUES('A003','Fred',50)

I want a resultset like this ...
i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)

(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)

A001 Tom Rick Harry 80 --sum(col3)
A002 Peter Sam NULL 100
A003 Fred NULL NULL 50

Any help would be greatly appreciated !!

View Replies !

Display The Variance Between Two Years In Row With Months In Columns?
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 Replies !

Format Date From 2/25/2006 To 02/25/2006
in footer of my report i show a current date by this

=Format(Today(), "d")

as a result i gave those

3/21/2007

but wont date like this 03/21/2007

How ?

View Replies !

Split A Column Into 2 Columns
Hi everyoneI guess this should be a simple question for the gurusI have a Data in a column which is to be places in 2 columns instead ofone. How do i go about doing it in MS SQL server? Could someone pleasehelp me. I could do it in access with an update query but things are alittle different in SQL server so I am a little lost.Eg.NameJohn?Doeto be split intoName LastNameJohn DoeThanks in advance.Prit

View Replies !

Split Address Value Into Two Columns
I am trying to get an address field into 2 colums. I need the number value in one column and street name in another column.

The data is stored:
876 blue ct
9987 red dr
23 windyknoll

This is what I haveelect
substring(Address,0,charindex('',Address)) as number
,substring(Address, (charindex('',Address)+1)
,len(Address)) as address
from contact

View Replies !

Split The String Into Columns

I have a table called products with the values like

ProductId ProductName
10            A
20           D,E,F,G
30           B,C
40           H,I,J

I need to display each productid's with

ProductId ProductName
10           A

20           D
20           E
20           F
20           G
30           B
30          C
40          H
40          I
40          J

I will be appreciated if you can send me the code.

Thanks,
Mears

View Replies !

Split The Data Into Columns
I have a table called products with the values like

ProductId ProductName
10            A
20           D,E,F,G
30           B,C
40           H,I,J

I need to display each productid's with

ProductId ProductName
10           A

20           D
20           E
20           F
20           G
30           B
30          C
40          H
40          I
40          J

I will be appreciated if you can send me the code.

Thanks,
Mears

View Replies !

Split One Column Into Multiple Columns
Hi all,
I have a requirement like this ,
I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469
I have to split this into 3 more columns like(Address1,name,phoneno)--
Means i have 4 columns including Address Column.(Address,Address1,name,phoneno)

Example:
Address:Rajkot
Address1:Univercity Road
Name:Mr. K KK Tank Guntur Jal Bhavan
PhoneNO:9843563469

How can i acheive this one with out data lose in Address Column.
Thanks in advance.

View Replies !

Query Split Column In 2 Columns In SQL
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that.
I have a table like this:
Select Name from Cars;
Result:
Col1
BMWMercedesFordAudi
But i like to make a query so it is displayed like this:
Col1 Col2
BMW FordMercedes Audi
So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.

View Replies !

Split Row's Columns Across Multiple Tables
Hello,

Hoping someone here can help. Perhaps I'm missing something obvious, but I'm surprised not to see a data flow task in SSIS for splitting *columns* to different destinations. I see the Conditional Split task can be used to route a *row* one way or another, but what about columns of a single row?

As a simple and somewhat contrived example, let's say I have a row with twelve fields and I'm importing the row into a normalized data structure. There are three target tables with a 1-to-1 relationship (that is, logically they are one table, but physically they are three tables, with one of them considered the "primary" table), and the twelve input fields can be mapped to four columns in each of the three tables.

How do I "split" the columns? The best way I can see is to Multicast the row to three different OLE-DB Destinations, each of which inserts to one of the three target tables, only grabbing the four fields needed from the input row.

Or should I feed the row through three successive OLE-DB Command tasks, each one inserting into the appropriate table? This would offer the advantage, theoretically, of allowing me to grab the identity-based surrogate primary key from the first of the three inserts in order to enable the two subsequent inserts.

Thoughts?

Thanks in advance,
Dan

View Replies !

How To Split Columns Into Multiple Rows
SOURCE TABLE
ID DESCRIPTION
1 I am a programmer
2 I am a doctor

Destination Table

ID LINE DESCRIPTION(Varchar10)
1 1 I am a pro
1 2 grammer
2 1 i am a doc
2 2 tor

Please someone help me on this.

View Replies !

Filter Out 2005 And 2006 Ferrari's Out Of A Result
i have a table full of cars, from many makes and years (along with other info).

but I DONT want 2005 Ferrari's, or 2006 Ferrari's.

tried making a SQL statement, but it seems to filter out ALL ferrari's.

Here is the SQL i've tried:

SQL Code:

Original
- SQL Code

--SQL Stm #1
select COUNT(*)
from items
where account_id in (1667) and
items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND
( (items.Make != 'Ferrari' AND items.Year != '2005')
OR (items.Make != 'Ferrari' AND items.Year != '2006') )

--SQL Stm #1
SELECT COUNT(*) from items
where account_id=1667 AND
items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND
(items.make!='ferrari' and (items.year!=2005 OR items.year!=2006))

--SQL Stm #1SELECT COUNT(*) FROM items WHERE account_id IN (1667) AND items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND ( (items.Make != 'Ferrari' AND items.Year != '2005') OR (items.Make != 'Ferrari' AND items.Year != '2006') ) --SQL Stm #1SELECT COUNT(*) FROM items WHERE account_id=1667 AND items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND(items.make!='ferrari' AND (items.year!=2005 OR items.year!=2006))

both takes out all ferrari's ?

View Replies !

Split A Single Column Data In To 2 Columns
Hi
This is probably a very basic question for most people in this group.
How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure.
Here is an example of what i want to acheive

FName
John?Doe

FName LName
John Doe

thanks for the help
prit

View Replies !

MS Access Detail Split Into Columns On Report
I would appreciate any help on this project. I have created an Access database that contains one vehicle. I have also included all options on that vehicle, which are in one column. Therefore the main criteria for the vehicle is listed each time for each different option. On my report I am grouping by the vin and placing the main criteria in the group header area of the report. The options are going into the detail section. How do I get the options to print in two columns within the detail section? I am unable to find any help on this subject, so I am asking you for help.

Thank you all! Timpy

View Replies !

Split Comma Separated Values Into Columns

Hi,
I have data like this in my table:

AppId   Gender
1         x
2         y

3         x, y
4         x, y, z

I need to transform like this:
AppID          Gender
1                  x
2                  y
3                  x
3                  y
4                  x
4                  y
4                  z

How to do this?

Thanks in advance

View Replies !

Which Edition And Build Of SQL Server 2005 To Use For Biztalk 2006?
Hi all -

We are planning to use a 2-node SQL Server 2005 cluster as part of a new Biztalk 2006 project, and I would like some advice regarding SQL Server service packs and editions.

Testing has already been running for some time without problems, but the test environment (a) is not clustered, (b) is using SQL Server 2005 Standard Edition rather than Enterprise and (c) has neither of the SQL Server service packs installed.

As this is a major project for us, my instinct would be to use Enterprise Edition (maximise performance and scalability)
and to apply SP2 (because I thought it was best practice to be up-to-date). I would have liked to do this before we go Live so we don't run the risk of having to change afterwards if we hit a problem.

Understandably, the project guys' perspective is this: testing so far has not been affected by SQL Server bugs, and we should go Live with the environment we have tested in, albeit with the planned 2-node cluster which is seen as a 'must have'.

I have trawled Books Online, Technet and other resources without success, so any thoughts or info on required, recommended or preferred combinations of Biztalk 2006 with SQL Server 2005 editions and service packs in a clustered
environment would be greatly appreciated - thanks!

Michael

View Replies !

SQL Server Version 2005/2006 Und ESQL/C Programme
Hallo!

Wie kann ich herausfinden, ob die neueste SQL Server Version 2006 noch ESQL/C Programme unterstÃ¼tzt?

Wir haben eine alte C-Bibliothek, die mit nsqlprep 6.5 Ã¼bersetzt wurde. Diese mÃ¶chte ich auch mit der SQL Server Version 2006 nutzen.

Danke im voraus

View Replies !

How To Select Distinct Row By Col1 ?
HI

Please help I didn't get right solution for this problem. So I am posting again with full details.
Scenario

col1---col2------col3----col4-----col5------col5
123-----AB--------WE-----Name------Add------Prod1
123-----AB--------DC-----Name------Add------Pro512
123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

How do I select Distinct row by col1 from the above scenario. Expected result will be

123-----AB--------WE-----Name------Add------Prod1
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

OR

123-----AB--------DC-----Name------Add------Pro512
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

OR

123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

Please Help.

View Replies !

[Microsoft][ODBC Text Driver] In The File Specification '...', The Col1 Option Is Invalid.
I'm trying to connect to a tab-delimited text file using the ODBC data access. I set it up using Administrative Tools - Data Sources (ODBC) from the start menu, and clicked the 'Guess' button under Define Format.

The schema it created looks like this:

[cims_output_data.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
CharacterSet=OEM
Col1=SIMNAME
Col2=TIMESTAMP
Col3=SIMTYPE
Col4=REGION
Col5=SECTOR
....etc.

It looks OK, but if I go back to Define in Administrative Tools and try to review it, I get a similar error message:

Ini File (or Registry) C:Program FilesCIMSschema.ini is corrupt.
Section: cims_output_data.txt, Key: Col1.

Has anyone seen this before? There is nothing on MSDN as far as I could find. I read on another forum that if you replace the label 'Col1' with 'Col' the problem is solved but it still reports that the schema is corrupt when I try to open it in Administrative Tools.

Bill.

View Replies !

Split Function - Sql Server 2005
In my table, column1 having a comma separated values.I want to display in rowwise.
for example:
column1
aa,ss,ff

output should be
aa
ss
ff

Pls. help me..I want to do thr query.

View Replies !

Split Number From Nvarchar Fiels Sql Server 2005
Hi
i want to split the numbers from the varchar field(accc0001).
i want 0001 only. at the same time select max of that number+1.

friends can u help me on this .

View Replies !

How Many Years
Of database experience do you have?

And what are they?

View Replies !

Add 28 Years Aagin
HI all,This has puzzled me all morning. I have a int field which reads19691124 (UK dates) this is actually a date 24 November 1969 I need toadd 28 years to this making it 19971124 but I’m stumped!Any ideas anyone?CheersSean*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

Happy New Years
I just wanted to wish everyone a Happy New Years and I wanted to thank all submitters to this forum for breaking up my code monkey workday and cutting into my productivity and I have found many of these discussions helpful in my preperation for my long procrastinated MCDBA exams.

Get hammered and do not drive.

View Replies !

SQL Date Is Off By 20 Years!
Hello Swynkers,

Hopefully this is a simple one:

One of our SQL7 boxes crashed - and we found out later that upon startup the system date was somehow automatically set to 7/20/2019. (It does this mysteriously after crashing). SQL of course took this date to be its own, and now all the Database creation dates, and "last backup date"s are screwy. The SQL Jobs, though scheduled, won't run (for another 20 years!) -- and when run manually, they do NOT reset the last Backup date.

Any suggestions on how I can manually change these dates? Has anyone else run into this date-flip error?

Thanks for any help,

Fenderson
miguel@cubik.com

View Replies !

Calculating Age In Years
--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:

DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

--Notes:
--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.

View Replies !

Happy New Years Dbforums
remember if you drink too much, get a taxi and charge it to your company.

View Replies !

Measuring Consecutive Years
Hi there.

I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.

I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.

CREATE TABLE mygifts06 (Donor_id varchar (10), Gift_yr nvarchar (4), Tot_pledges numeric (16,2))

INSERT INTO mygifts06 (Id,Gift_yr,Pledges)
SELECT 155758,2005,15.00 UNION ALL
SELECT 155759,2004,25.00 UNION ALL
SELECT 155758,2004,40.00 UNION ALL
SELECT 155757,2005,100.00 UNION ALL
SELECT 155758,2002,30.00 UNION ALL
SELECT 155758,2001,120.00 UNION ALL
SELECT 155755,2003,15.00 UNION ALL
SELECT 155758,2006,80.00 UNION ALL
SELECT 155757,2003,65.00 UNION ALL
SELECT 155759,2005,400.00

For the above dataset, I am trying to create the following output

Donor_id 2_consec_gifts 3_consec_gifts 4 consec_gifts
--------- -------------- -------------- --------------
155755000
155757000
155758110
155759100

Do I need to use a cursor for this task? I lack experienced in using cursors is there an alternative method someone could suggest?

Thanks in advance.

View Replies !

Compare Between Two Years (from MDX Query)
Hi
In my cube, I have account datas for 2005, 2006, 2007
I want to compare amounts between 2005 and 2006 in a SSRS report
To do that, I'm using this member :
MEMBER [Measures].[Last Year Account] AS '([Measures].[Account],
PARALLELPERIOD([Time].[Hierarchy].[Year],1))'
My cube measure is [Account]

I need a lot of comparisons and my query is very slow (i can't use
Non_Empty_Behavoir, otherwise it returns false results).
Is there a way to do that directly in the report? Because results of 2005, 2006 and 2007 are
yet returned by my original query (without [Last Year Account] Member)

Any advice will be helpfull

View Replies !

Compare The Same Period Of Different Years
Hello to everybody, I'm quite new to the Analysis Services world and would like to create a BI Solution for one of my customers with the following problem: I have sales data from the ERP from different years (2005 and on). My customer wants a report that shows the sales of the current year up to the month he wants to specify (e.g. january - march 2008) compared with the sales of the same period of the previous year (e.g. january - march 2007), divided into product category.

Is there a way to build a cube and then a matrix report in Reporting Services that lets the user specify the period for the current year and shows the same period of the previous year. Do I achieve this behaviour with a calculated member in the cube (e.g. ParallelPeriod)

Any hint (or web like example) would be very appreciated.

Best regards

Alessandro

View Replies !

Group By Concurrent Years

Hi and thank you in advance to whomever takes the time to read this entry. I will be as detailed as possible, so I apologize for the length. The information here relates to the automotive industry, but I don't believe that is a very important detail

The overall purpose of this query is to create a compressed set of the data that exists in the database. The de-normalized information is structured like the following (including made-up data):

Code Block
PartNumber           Make           Model        Year
-------------------------------------------------------
835100               ACURA         INTEGRA         2004
835100               ACURA         INTEGRA       2003
835100               ACURA         INTEGRA         2001
835100               ACURA         INTEGRA         2000
835100               FORD           FOCUS          2002

There is any number of part numbers, makes, etc in this mix. The query that I currently use is utilizing MAX and MIN functions to determine year range, but as in the sample above, this does not take into account the fact that there could be a year skipped in the middle. I could probably use a cursor to do this, but would really like to stick to set logic if at all possible.

My existing query is:

Code Block
SELECT DISTINCT
    cwi.PartNumber
    ,RTRIM(lv.Make)     AS Make
    ,MIN(lv.Year)       AS StartYear
    ,MAX(lv.Year)       AS EndYear
FROM
    CWIParts AS cwi
        INNER JOIN PartTypes AS pt
            ON cwi.PartTypeID = pt.PartTypeID
        INNER JOIN PartDetail AS pd
            ON cwi.PartNumber = pd.PartNumber
        INNER JOIN Status AS s
            ON pd.StatusCode = s.StatusCode
        INNER JOIN LegacyVehicle AS lv
            ON cwi.LegacyVehicleID = lv.LegacyVehicleID
WHERE
    cwi.PartTypeID = 10
   AND s.Status = 'Active'
GROUP BY
    lv.Make
ORDER BY
    cwi.PartNumber ASC
    ,cwi.Make ASC

In hopes to end up with a result set that has 1 row for each unique part-number, make, and consecutive year range relevant to the part-number and make. I will also need to add the functionality to add other attributes (such as Model and Liters), but those are equality based and can be passed in easily.

I had done most of this programatically in vb.net, but I would much rather push this logic back to SQL Server. Thank you all for any help that you can provide on this topic.

- Jay Soares

View Replies !

How To Get Date Of Birth And Age In Years?
I have a table name employee and datetime column named dateofbirth, how can i write a select statement to show their, date of birth and age in years?

This is how i did it, i couldn't finish it, Any feedback would be very thankful.

select name, datebirth, year(dateofbirth)
from employee

View Replies !

Need To Get Just Last 2 Years Worth Of Data
SELECT * FROM TEST NOLOCK
WHERE FY_CD = DATEPART(YEAR, GETDATE())

This gives me last years - but they want last years 2007 and 2008

Any suggestions

View Replies !

2006-07-26 00:00:00
This is the result I get in Word as I handle a document that merges datafrom the database. The field «Date_Fin_Membre» returns me this resulteven if the query stipulatesWHERE (dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),dbo.Membre.Date_Fin_Membre, 101)). I have also tried other types ofconversion without any success.thanks

View Replies !

Subquery In DTS No Longer Works After Years
Hi folks,A DTS package we have run for years now no longer works. The specificpart that is not working is a subquery in the SOURCE object of atransformation. The source is based on a Microsoft Data Link to aSybase database (DSN changed a couple months ago but the connectionstring was updated successfully for the new 12.51 version of ASE) andthe destination is a link to a local SQL Server 2000 database.The transformation has always worked and when I remove the subqueryeverything works OK. The problem is that I need the subquery!Does anyone have a clue what is going on?Here is the full query.select TableKey = RVSN_TYPE_ID,TableCode = RVSN_TYPE,RevisionDate = RVSN_DATE,RevisionReasonCode = RSN_CODE,RevisionGroup = RVSN_GRP_ID,RevisedField = (select L.FieldIDfrom tempdb.guest.lkpRevisedField Lwhere L.TableID = R.RVSN_TYPEand L.FieldName = R.CHNG_FLD),RevisedValue = OLD_FLD_VAL,RevisionTimestamp = RVSN_TIMESTAMPfrom RVSN R,tempdb.guest.MaxTimeStamp TSwhere R.RVSN_TIMESTAMP TS.Rtimestampand R.RVSN_TIMESTAMP is NOT NULLJohn H.

View Replies !

Dynamic Sql To Loop Over Fiscal Years
thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECTcount(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHEREstart_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECTcount(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHEREstart_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...

expected output....

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
.... ....

View Replies !

Dates In Years Prior To 1900?
I've set up a SQL7 database with MSAccess97 as a front end. I'm trying to enter a person with a birthdate prior to 1900, get an ODBC call error, "Datetime field overflow". How to enter dates prior to year 1900?
Thanks.

View Replies !

Retrieving Data Using Fiscal Years
Hi, I would like to select data from my source system based on fiscal years. I don't want to hard code anything, e.g. select * from person where startdate > 01042005. I want to try and use a store proc or function. The main requirement is to always retrieve data from source tables for the last two fiscal years (based on todays date - getdate). Our fiscal year starts the 1st April. Therefore, If todays date is 14 October 2007, I would like to select all persons whose start date was greater than 01 April 2005. Is there an easy way to do this in sql server 2005 without hard coding dates?

Gurj

View Replies !

Getting Density Of Values Across Years And Months
Hi All,

I have the following table "Project"

-------------------------------------------------------------------------------------------
ID                Name                Start Date         End Date
--------------------------------------------------------------------------------------------
001           Project 1               2-2-2003          2-3-2007
002           Project 2               1-24-2003        2-6-2007
003           Project 3               4-10-2005        2-10-2008
004           Project 4               5-20-2006        6-6-2008
...
015           Project 15             2-20-2006        3-3-2009
----------------------------------------------------------------------------------------------
What I want is the the following output.

Output 1:

------------------------------------------------------------------------------------------------------------------------------------------------
Year                            Projects                 No. Of Projects                       Starting Months
------------------------------------------------------------------------------------------------------------------------------------------------
2003                     Project 1 , Project 2                 2                                February, January
2005                     Project 3                                 1                                April
2006                     Project 4, Project 15                 2                               May , Feb
------------------------------------------------------------------------------------------------------------------------------------------------

(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...)
and also the following

Output 2: (this is optional view...)
-----------------------------------------------------------------------------------------
Year            Start Date                     Project Name
-----------------------------------------------------------------------------------------
2003

February 2                          Project 1
January 24                         Project 2
2005
                    April 4                               Project 3
2006
                   May 5                                Project 4
                   February 20                         Project 15
-------------------------------------------------------------------------------------------

I am very much in need of Output 1. Could someone help me,

View Replies !