August 20, 2014 at 7:38 am
SELECT COALESCE(sum(A.AmountGiven),0) AS AmountGiven , A.YearToDate, A.EndDate
FROM (SELECT (SELECT SUM(AmountGiven) FROM [Production].[dbo].[DB1]
WHERE Year(date) = Year(Startdate) and EmployeeID = rf.EmployeeID) AS 'AmountGiven',
(SELECT MAX(Date) FROM [Production].[dbo].[DB1] ) AS 'EndDate', Year(Startdate) as YearToDate, EmployeeID
FROM [Staging].[dbo].[DB2] rf WHERE Year(Startdate) IN ('2011','2012','2013','2014') AND EmployeeID IN ('300163315')
GROUP BY Year(Startdate) , EmployeeID ) A GROUP BY A.YearToDate, A.EndDate
I get result below it does not have 2014 because user is not in DB2 with a 2014 date.
AmountGivenYearToDateEndDate
020112014-07-31 00:00:00.000
250420122014-07-31 00:00:00.000
020132014-07-31 00:00:00.000
How can I get it to return a line item for all dates in the where clause.
I want the results to be
AmountGivenYearToDateEndDate
020112014-07-31 00:00:00.000
250420122014-07-31 00:00:00.000
020132014-07-31 00:00:00.000
0 2014 2014-01-01 00:00:00.000
August 20, 2014 at 7:55 am
Let's start by formatting this sql so we can read it.
SELECT COALESCE(sum(A.AmountGiven), 0) AS AmountGiven
,A.YearToDate
,A.EndDate
FROM (
SELECT (
SELECT SUM(AmountGiven)
FROM [Production].[dbo].[DB1]
WHERE Year(DATE) = Year(Startdate)
AND EmployeeID = rf.EmployeeID
) AS 'AmountGiven'
,(
SELECT MAX(DATE)
FROM [Production].[dbo].[DB1]
) AS 'EndDate'
,Year(Startdate) AS YearToDate
,EmployeeID
FROM [Staging].[dbo].[DB2] rf
WHERE Year(Startdate) IN (
'2011'
,'2012'
,'2013'
,'2014'
)
AND EmployeeID IN ('300163315')
GROUP BY Year(Startdate)
,EmployeeID
) A
GROUP BY A.YearToDate
,A.EndDate
This looks way over complicated to me. You have 4 select statements to get three columns. You have multiple aggregates which seems like we could do this a bit simpler.
What I don't quite understand is what you are looking for. You said that you don't get data for 2014 because there is no row for that. That means you need to use another table as your driving table for this query. A table that has the dates you want, then you can left join to retrieve the other information.
If you need more detailed help we need some more information. Please take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2014 at 8:07 am
If you provide table DDL and some sample data as mentioned in the first link in my signature it would make much easier to provide accurate and tested solutions.
Whenever you need to fill in data you need some type of table to join to provide the data. In your case you need a calendar table that has years so you can provide a row for years when an employee has no rows. Something like this:
DECLARE
@StartDate DATE = '2011-01-01',
@EndDate DATE = '2014-01-01';
/* Create virtual numbers table based on Itzik Ben-Gan's article,
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers, so we can
create a virtual years table */
WITH L0
AS (
SELECT
1 AS c
UNION ALL
SELECT
1
),
L1
AS (
SELECT
1 AS c
FROM
L0 AS A
CROSS JOIN L0 AS B
),
L2
AS (
SELECT
1 AS c
FROM
L1 AS A
CROSS JOIN L1 AS B
),
L3
AS (
SELECT
1 AS c
FROM
L2 AS A
CROSS JOIN L2 AS B
),
L4
AS (
SELECT
1 AS c
FROM
L3 AS A
CROSS JOIN L3 AS B
),
L5
AS (
SELECT
1 AS c
FROM
L4 AS A
CROSS JOIN L4 AS B
),
Nums
AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (
SELECT
NULL
)) AS n
FROM
L5
),
years
AS (
SELECT TOP (DATEDIFF(YEAR, @StartDate,
DATEADD(YEAR, 1, @EndDate)))
YEAR(DATEADD(YEAR, Nums.n - 1, @StartDate)) AS TheYear,
DATEADD(YEAR, Nums.n - 1, @StartDate) AS StartDate,
DATEADD(YEAR, Nums.n, @StartDate) AS EndDate
FROM
Nums
)
SELECT
years.TheYear,
COALESCE(SUM(One.AmountGiven), 0) AS AmountGiven,
MAX(Two.StartDate) AS EndDate
FROM
years
LEFT JOIN [Production].[dbo].[DB1] AS One
JOIN [Staging].[dbo].[DB2] AS Two
ON One.EmployeeID = Two.EmployeeID
ON Two.StartDate >= years.StartDate AND
Two.StartDate < years.EndDate AND
EmployeeID IN ('300163315'
GROUP BY
TheYear;
Without DDL and test data I can't guarantee that this is right, but I think it points you in the right direction. As Sean said your original query is a bit complex for the problem it looks like you are trying to solve.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2014 at 6:46 pm
I figured out a way to do it. Thank you
August 21, 2014 at 2:21 am
jrusso (8/20/2014)
I figured out a way to do it. Thank you
Does it look something like this?
SELECT
[AmountGiven] = ISNULL(SUM(a.AmountGiven), 0),
a.YearToDate,
a.EndDate
FROM ( -- a
SELECT
[AmountGiven] = MAX(x.[AmountGiven]),
[EndDate] = MAX(y.[EndDate]),
YearToDate = d.StartYear,
rf.EmployeeID
FROM (VALUES (2011),(2012),(2013),(2014)) d (StartYear)
LEFT JOIN [Staging].[dbo].[DB2] rf
ON YEAR(rf.Startdate) = d.StartYear
AND rf.EmployeeID = '300163315'
OUTER APPLY (
SELECT [AmountGiven] = SUM(d1.AmountGiven)
FROM [Production].[dbo].[DB1] d1
WHERE YEAR(d1.[Date]) = YEAR(d1.Startdate) -- Where is Startdate? d1 or rf?
AND d1.EmployeeID = rf.EmployeeID
) x
CROSS APPLY (SELECT [EndDate] = MAX([Date]) FROM [Production].[dbo].[DB1]) y
GROUP BY d.StartYear, rf.EmployeeID
) a
GROUP BY a.YearToDate, a.EndDate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply