July 10, 2012 at 10:31 am
Hey guys,
I need some help with this query I am trying to write...
Here are the specs:
Get sum of number for previous year and current year by group...So there are 4 groups, and if the DateA is previous year, sum for each group and it will go in previous yr column, if Date A is in current year, sum for each group and it will go in current yr column...and needs to be where DateB is between current month...
Result Set should look like (numbers are made up):
Group Previous Yr Current Yr
GroupA 800 925
GroupB 500 1245
GroupC 1000 1450
GroupD 750 600
Here is my query:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = dbo.CONVERT_TO_BOD(dbo.GET_FIRST_DAY_OF_CURRENT_MONTH())
SET @EndDate = dbo.CONVERT_TO_EOD(dbo.GET_LAST_DAY_OF_CURRENT_MONTH())
SELECT CASE GRP_NO
WHEN 1 THEN 'GroupA'
WHEN 2 THEN 'GroupB'
WHEN 3 THEN 'GroupC'
WHEN 4 THEN 'GroupD'
END AS 'GROUP',
CASE
WHEN DATEPART(yy,DateA) = DATEPART(yy, dbo.GET_FIRST_DAY_OF_PREVIOUS_YEAR()) THEN SUM(CONVERT(DECIMAL (18,2),Number))
END AS 'PREVIOUS YEAR',
CASE
WHEN DATEPART(yy,DateA) = DATEPART(yy, dbo.GET_FIRST_DAY_OF_CURRENT_YEAR()) THEN SUM(CONVERT(DECIMAL (18,2),Number))
END AS 'CURRENT YEAR'
FROM TableA
WHERE GRP_NO IN ('1','2','3','4')
AND DateB BETWEEN @StartDate AND @EndDate
GROUP BY GRP_NO, DateA
But here are is my result set:
GROUP PREVIOUS YR CURRENT YR
GroupA 500 600
GroupB 700 1200
GroupC 800 900
GroupA 600 700
GroupB 400 800
GroupD 600 500
GroupD 350 600
GroupC 600 500
It is taking each record and summing the number....I only want the 4 records in the result set for each group and their sum...
Can anyone help me with this? If you have other questions, let me know
July 10, 2012 at 10:40 am
Take a look at the first link in my signature about best practices. You need to post ddl, sample data and desired output.
_______________________________________________________________
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/
July 10, 2012 at 10:46 am
Not sure what else you want...
I gave you my expected result set, the query I was using, and the result set it gave back...
Does it look like what is exactly in your best practices, No? But I mean its still readable...
I am not trying to offend you but I supplied what you asked for...
July 10, 2012 at 10:55 am
asm1212 (7/10/2012)
Not sure what else you want...I gave you my expected result set, the query I was using, and the result set it gave back...
Does it look like what is exactly in your best practices, No? But I mean its still readable...
I am not trying to offend you but I supplied what you asked for...
OK but I can't run that query. That means I can't actually test anything I write to see if it actually works.
You have tables and data I can't see. You have scalar functions all through this. Scalar functions btw are one of the worst performing pieces in t-sql. I couldn't begin to offer a suggestion without some details.
If you can provide these details you will get lots of help from lots of people. Not asking you to recreate your entire system, just enough so that the volunteers around here can run queries against a consistent table and data to get your results.
_______________________________________________________________
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/
July 10, 2012 at 11:04 am
I understand your point of view...Give me a lil bit and I will revisit this!
July 10, 2012 at 11:07 am
Without your table structure and some sample data, we're left guessing on parts of this, and left doing more work than would be necessary if you provided that. We're volunteers here, so saving us time makes us more likely to help you, and able to help you better.
All that aside, here's my best approximation of your data, and a query that would work on that. You'll have to modify the query to work with your actual table(s), but the basic idea, two derived tables joined by the Group column, should get you started.
DECLARE @StartDate DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
@EndDate DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) ;
IF OBJECT_ID(N'tempd..#T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T
(GRP_NO INT,
Number FLOAT,
DateA DATE,
DateB DATE) ;
INSERT INTO #T
(DateA, DateB, GRP_NO, Number)
VALUES ('7/10/12', '7/10/12', 1, 1),
('7/10/11', '7/10/11', 1, 2),
('7/10/12', '7/10/12', 1, 3),
('7/10/11', '7/10/11', 1, 4),
('7/10/12', '7/10/12', 2, 10),
('7/10/11', '7/10/11', 2, 20),
('7/10/12', '7/10/12', 2, 30),
('7/10/11', '7/10/11', 2, 40),
('7/10/12', '7/10/12', 3, 100),
('7/10/11', '7/10/11', 3, 200),
('7/10/12', '7/10/12', 3, 300),
('7/10/11', '7/10/11', 3, 400),
('7/10/12', '7/10/12', 4, 1000),
('7/10/11', '7/10/11', 4, 2000),
('7/10/12', '7/10/12', 4, 3000),
('7/10/11', '7/10/11', 4, 4000) ;
SELECT COALESCE(A.[Group], B.[Group]) AS [Group],
CurrentYr,
PreviousYr
FROM (SELECT CASE GRP_NO
WHEN 1 THEN 'GroupA'
WHEN 2 THEN 'GroupB'
WHEN 3 THEN 'GroupC'
WHEN 4 THEN 'GroupD'
END AS [Group],
SUM(Number) AS CurrentYr
FROM #T
WHERE DateA >= @StartDate
AND DateA < @EndDate
AND GRP_NO IN (1, 2, 3, 4)
GROUP BY GRP_NO) AS A
FULL OUTER JOIN (SELECT CASE GRP_NO
WHEN 1 THEN 'GroupA'
WHEN 2 THEN 'GroupB'
WHEN 3 THEN 'GroupC'
WHEN 4 THEN 'GroupD'
END AS [Group],
SUM(Number) AS PreviousYr
FROM #T
WHERE DateA >= DATEADD(year, -1, @StartDate)
AND DateA < DATEADD(YEAR, -1, @EndDate)
AND GRP_NO IN (1, 2, 3, 4)
GROUP BY GRP_NO) AS B
ON A.[Group] = B.[Group]
ORDER BY A.[Group] ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2012 at 11:29 am
As they say, it's not clear to see exactly what's going wrong. But I believe that there's an extra column in your GROUP BY clause (or maybe there's another reason to include DateA in it).
If you don't need it and you want to group only by GRP_NO, you should only specify that column on the GROUP BY clause.
July 10, 2012 at 1:36 pm
I apologize for the way I presented my question...I do realize you all are just volunteers and I appreciate everyones work on the website...I have been helped many times from the forums on this site! It is my go to site when I am having issues!
Next time I will present my question in a proper manner!
July 10, 2012 at 1:40 pm
asm1212 (7/10/2012)
I apologize for the way I presented my question...I do realize you all are just volunteers and I appreciate everyones work on the website...I have been helped many times from the forums on this site! It is my go to site when I am having issues!Next time I will present my question in a proper manner!
I assume then that you were able to resolve your issue?
Have no fear, we don't bite and we certainly don't hold any grudges. It just helps when you see it from the other perspective. Glad that we could be of help and please don't hesitate in the future. 😀
_______________________________________________________________
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/
July 10, 2012 at 2:17 pm
Yes GSquared showed how I can use the derived tables to get what I was needing! Didnt even think to use those...
Thank you all for your input and like I said before next time I have a question, I will try to go about it the correct way!
July 12, 2012 at 6:17 am
Glad we could help.
And don't worry, we don't apply the death penalty until the third time you post a question without fulfilling all possible requirements on it. :w00t:
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply