Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Need a Query on group by condition Expand / Collapse
Author
Message
Posted Monday, October 14, 2013 2:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 7,040, Visits: 12,966
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
Post #1504541
Posted Tuesday, October 15, 2013 7:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:55 AM
Points: 17, Visits: 157

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




Post #1504751
Posted Wednesday, October 16, 2013 11:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:27 AM
Points: 5, Visits: 31
--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
Post #1505490
Posted Monday, October 21, 2013 5:30 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
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

Post #1506633
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse