SQL Server 2012 :: Sales Over Years - Retrieve Values In Single Query For Multiple Years And Grand Total?

Apr 24, 2015

I need to list customers in a table that represents sales over the years.

I have tables:

Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...

I am using this SQL but it only gets one year:

SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC

I need something like this:

customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$

Is it possible to retrieve these values in a single SQL query for multiple years and grand total?

View 6 Replies


ADVERTISEMENT

Counting The Years In Multiple Datetime Values

Jul 19, 2007

am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))
but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))
P.S i want to be able to use it in a where clause :
select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp)
thanks in advance

View 8 Replies View Related

Power Pivot :: LY Sales - Function Throws Error When Selecting Periods In Different Years

May 21, 2015

I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification) .

The DAX code for calculating LY Sales is:

=
CALCULATE (
    [Sales $],
    FILTER (
        ALL ( dCalendar ),
        dCalendar[IRIYearNumber]
            = VALUES ( dCalendar[IRIYearNumber] ) - 1

[Code] ....

The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report. 

The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;

ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected. 

View 5 Replies View Related

SQL Server 2012 :: Get Previous Years Holiday Date?

Jan 6, 2015

Given this table;

DECLARE @table TABLE (HolidayDate DATE, HolidayName NVARCHAR(50))
INSERT INTO @table
( HolidayDate, HolidayName )
VALUES ('2012-01-01','New Years Day'),
('2012-01-16', 'MLK Day'),
('2012-02-20', 'Presidents Day'),

[code]....

How to get a result set that shows a new column called PreviousHolidayDate with the corresponding holidays last years date?

HolidayDateHolidayNamePreviousHolidayDate
1/1/2012New Years DayNULL
1/16/2012MLK DayNULL
2/20/2012Presidents DayNULL
4/6/2012Good FridayNULL
5/28/2012Memorial DayNULL

[code]....

View 4 Replies View Related

SQL Server 2012 :: Case When Statement - N Number Of Years Without Modifications

Feb 4, 2014

Below is the scenario which I have currently in my query. I need to write this query without any hardcode values , so that it will work til n number of years without modifications.

Startdate =
CASE WHEN
Trandate between '06-04-2013' and '05-04-2014' then '06-04-2013'
Trandate between '06-04-2012' and '05-04-2013' then '06-04-2012'
Trandate between '06-04-2011' and '05-04-2012' then '06-04-2011'
Trandate between '06-04-2010' and '05-04-2011' then '06-04-2010'
Trandate between '06-04-2009' and '05-04-2010' then '06-04-2009'
Trandate between '06-04-2008' and '05-04-2019' then '06-04-2008'
END

View 5 Replies View Related

Getting Density Of Values Across Years And Months

Aug 29, 2007

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 5 Replies View Related

Analysis :: MDX Multiple Years On Columns

Nov 5, 2015

Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically.  My question is how do I get the previous two years dynamically as well.  I've tried a few different ways with no luck.  As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.

SELECT
{
[Time].[Time].[Year].&[2013],
[Time].[Time].[Year].&[2014],
TAIL([Time].[Time].[Year].MEMBERS,1)
}ON COLUMNS,
NON EMPTY
{[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS
FROM SALES

View 9 Replies View Related

Analysis :: Calculate Percentage Difference Of Two Values From Selected Years

Aug 31, 2015

Developing a measure which displays the difference of two values from the selected years.

An example : Show the difference of the sales amount from 2013 and 2015.

Since i am not really into mdx or calculated members.

View 6 Replies View Related

SQL Server 2012 :: How To Replace Multiple Values In Single Select Statement

Aug 18, 2015

how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.

DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))

[code]....

View 7 Replies View Related

Distinct Count With Null Values (grand Total)

May 9, 2007

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of pulications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
--------------
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 1 Replies View Related

Distinct Count With Null Values (grand Total)

May 10, 2007

Hello,

I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of publications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 2 Replies View Related

SQL Server 2014 :: Partition A Big Table Into Years?

Jun 4, 2015

I'm looking to partition a big table into years.

I have a big table with data going back to 2001.

I need to partition this into file groups each for the last 5 years, and the rest into its own file group

I was going to convert the inherent date time field into the format int(yyyymmdd) and partition on that field.

Is this an acceptable way to achieve this? Is there a better way to partition on date? [not time]

View 4 Replies View Related

SQL Server 2008 :: Find Patients With Age 6 Months To 5 Years

Mar 3, 2015

formula to return patients ages 6 months to 5 years?

create table dbo.TEST
(
MRN varchar(10),

[Code]....

View 6 Replies View Related

Get The Grand Total Of This Query

Dec 12, 2003

I need to get the Grand Total of the results of this query.
The query pulls the total customer quotes for each community in a management company and loads my DataGrid:

This query works fine for individual community totals"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _
" Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _
" (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _
"(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"

At first glance you would think that the following query would return the Grand Total for all communities in the management company:

Current Grand Total Query"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE
cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session
("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "

But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.

Thanks in advance.

View 2 Replies View Related

Transact SQL :: Grand Total At Button Of Result Set Of Query

Jul 16, 2015

I have a script that produce a result set that is almost complete. I have a new requirement come up to put a Total  at the button of the result set of my Query.

I'm using SQL Server 2008
;WITH CTE AS
(
SELECT
Case WHen left(MONTHDate,3)='Jan' Then '01'
WHen left(MONTHDate,3)='Feb' Then '02'
WHen left(MONTHDate,3)='Mar' Then '03'
WHen left(MONTHDate,3)='Apr' Then '04'
WHen left(MONTHDate,3)='May' Then '05'

[code]....

View 4 Replies View Related

Help: Query To Get Total Last 7 Day Sales For Each Day

Aug 5, 2004

Let say I have this table in MS SQL server
table transaction(date,sales)

how to query to get result like this (date,sales,sum(sales last 7 day))
I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.

note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day

thanks

View 2 Replies View Related

How Many Years

Sep 28, 2005

Of database experience do you have?

And what are they?

View 14 Replies View Related

Happy New Years

Dec 30, 2004

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 2 Replies View Related

Calculating Age In Years

Dec 19, 2001

--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 20 Replies View Related

Add 28 Years Aagin

Jul 20, 2005

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 4 Replies View Related

Report That Shows Customer And Total Sales Who Had Max Sales By Year?

Dec 9, 2014

I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.

Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200

Report:

Cust ID Year Sales
C1 2000 250
C3 2001 200

View 1 Replies View Related

Happy New Years Dbforums

Dec 30, 2005

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

View 4 Replies View Related

Measuring Consecutive Years

Oct 24, 2006

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 9 Replies View Related

Need To Get Just Last 2 Years Worth Of Data

Jun 23, 2008

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 3 Replies View Related

How To Get 20 Years From Current Year

Sep 30, 2014

I want an sql query that wil give me listing of 20 years from current year.

View 2 Replies View Related

How To Get Date Of Birth And Age In Years?

Mar 27, 2007

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 10 Replies View Related

Compare The Same Period Of Different Years

May 21, 2008

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 1 Replies View Related

Group By Concurrent Years

Nov 8, 2007



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 9 Replies View Related

Dates In Years Prior To 1900?

Jan 13, 2000

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 1 Replies View Related

Dynamic Sql To Loop Over Fiscal Years

Jan 6, 2004

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 4 Replies View Related

T-SQL (SS2K8) :: DATEADD And Leap Years

May 29, 2012

DECLARE @MyDate datetime
SET @MyDate = '2/28/2009'

SELECT DATEADD(year,-1,@MyDate)

Desired result '2/29/2008'

This below seems to work. (Subtract a year and then find the last day of the month for that date, set to midnight)

SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(year,-1,@MyDate))+1,0))), 0)

Is this a reasonable approach? I thought the SQL Server data and time functions were aware of leap year?

View 9 Replies View Related

Get Date Of Tuesday Of Past X Years From Now

Feb 4, 2015

I am trying to get the date of Tuesday from now of the past x Years

The below Query works fine for weeks

DECLARE @x INT
SET x= 53
SELECT CONVERT(VARCHAR, DATEADD(WW,-@x,DATEADD(DD, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)

The issue is with below query for any passed Number of Years

SET x = 7
CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)

So need to get past years Tuesday Date and also verify if week also works for any passed week numbers ? The x parameter can be changed based on the situation

View 9 Replies View Related

Subquery In DTS No Longer Works After Years

Oct 13, 2006

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 1 Replies View Related







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