Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need a Query on group by condition


Need a Query on group by condition

Author
Message
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
Here's a cte based solution.

But, as already mentioned, this query is everything but efficient and clean code.

From my point of view there are three options:
a) get the MONTHY column into a datetime format, as already recommended or
b) at least add a computed persisted column that'll do the same while keeping the "nasty column"
c) live with what you have and be prepared for date conversion errors (e.g. MONTHY =152013) or at the very least invalid date values.

I'd go with option (a).

declare @tbl table 
(
emp int ,
monthyear int,
amount money
)

INSERT INTO @tbl
SELECT 1, 102013 ,1000
UNION ALL SELECT 2, 102013, 1000
UNION ALL SELECT 1, 112013 ,1000
UNION ALL SELECT 1, 112013, 1000
UNION ALL SELECT 2, 122013 ,1000
UNION ALL SELECT 2 ,122013,1000
UNION ALL SELECT 1, 12014 ,1000;


WITH cte AS
(
SELECT
CAST(RIGHT(monthyear,4) + LEFT(1000000 + monthyear,2) + '01' AS DATE) AS YYYYMMDD,
emp,
amount
FROM @tbl
)
SELECT emp, SUM(amount) AS TotalAmount
FROM cte
WHERE YYYYMMDD >='20131101' AND YYYYMMDD <'20141001'
GROUP BY emp





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
MARCO VERBURG
MARCO VERBURG
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 173
Try this: (not tested)

select emp
, SUM(amount)
, '(THIS AMOUNT INCLUDES THE MONTHS FROM ' + cast(min(monthyear) as char(6)) + ' TO ' + cast(max(monthyear) as char(6)) + ')'
from @tbl
where monthyear in(112013, 122013, 12014, etc, etc..... )
group by emp
chaitutheprince
chaitutheprince
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 33
--create a new local table and add start date column and separating the month and year in the monthyyear column
Declare @loc_table table (emp int, dates int, year int, amount money,startdate smalldatetime)
Insert into @loc_table
Select emp
,(case when len(monthyyear) = 6 then substring(monthyyear,1,2)
when len(monthyyear) = 5 then substring(monthyyear,1,1)
else ''
end) as dates
,(case when len(monthyyear) = 6 then substring(monthyyear,3,4)
when len(monthyyear) = 5 then substring(monthyyear,2,4)
else ''
end)as year
,amount
,null
from tablename

Update @loc_table
SET startdate = dateadd(month,dates-1,dateadd(yy,year-1900,0))
From @loc_table

--input variables
--need a Query from the month 112013 to 122014
Declare @startdate smalldatetime
,@enddate smalldatetime
,@month1 int
,@year1 int
,@month2 int
,@year2 int
,@montyear1 varchar(10)
,@monthyear2 varchar(10)

SET @montyear1 = 112013
SET @monthyear2 = 122014

--get the start date

SET @month1 = (case when len(@montyear1) = 6 then substring(@montyear1,1,2)
when len(@montyear1) = 5 then substring(@montyear1,1,1)
else ''
end)

SET @year1 = (case when len(@montyear1) = 6 then substring(@montyear1,3,4)
when len(@montyear1) = 5 then substring(@montyear1,2,4)
else ''
end)

SET @startdate = dateadd(month,@month1-1,dateadd(yy,@year1-1900,0))

--get the end date
SET @month2 = (case when len(@monthyear2) = 6 then substring(@monthyear2,1,2)
when len(@monthyear2) = 5 then substring(@monthyear2,1,1)
else ''
end)

SET @year2 = (case when len(@monthyear2) = 6 then substring(@monthyear2,3,4)
when len(@monthyear2) = 5 then substring(@monthyear2,2,4)
else ''
end)

SET @enddate = dateadd(day,-1,dateadd(month,@month2,dateadd(yy,@year2-1900,0)))




select emp, sum(amount) as Amount from @loc_table
where Startdate between @startdate and @enddate
group by emp
parulprabu
parulprabu
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1454 Visits: 136
Hi try with this


WITH cte AS
(
SELECT
CAST(RIGHT(monthyear,4) + CASE WHEN LEN(monthyear)=6 THEN LEFT(monthyear,2) WHEN LEN(monthyear)=5
THEN '0'+LEFT(monthyear,1) END + '01' AS DATE) AS YYYYMMDD,
emp,
amount
FROM @tbl
)
SELECT emp, SUM(amount) AS TotalAmount ,'THIS AMOUNT INCLUDES THE MONTHS FROM ' + CONVERT(VARCHAR(10), MIN(YYYYMMDD)) + ' TO ' +CONVERT(VARCHAR(10), MAX(YYYYMMDD))
FROM cte
WHERE YYYYMMDD >='20130101' AND YYYYMMDD <'20140201'
GROUP BY emp


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search