Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2014-03-20 : 08:01:38
|
HI All I need some help Aim -Count how many ([FDMSAccountNo]) have been added in a quarterTo calculate this I was going to use the open date.Column :Open_DateDate type : datetimeFormat :2014-03-01 00:00:00.000I need a fixed quarter Jan – March Q1 April – June Q2July – Sept Q3Oct – Dec Q4My query so far is SELECT 'Total Number Of Merchants Added Q Singles',COUNT ([FDMSAccountNo]) as OutletsFROM [FDMS].[dbo].[Dim_Merchant_Log_All]where [Account_Status] = 16and [FDMSAccountNo] <>[Agent_Chain_No]And [FDMSAccountNo] <> [Corp_Chain_No] And [FDMSAccountNo] <> [Chain_Chain_No] |
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2014-03-20 : 08:29:35
|
try thisSELECT 'Total Number Of Merchants Added Q Singles',COUNT ([FDMSAccountNo]) as Outlets,case DATENAME(Quarter, CAST(CONVERT(VARCHAR(8), open_date) AS DATETIME))when 1 then 'Q1'when 2 then 'Q2'when 3 then 'Q3'when 4 then 'Q4' end as quarternameFROM [FDMS].[dbo].[Dim_Merchant_Log_All]where [Account_Status] = 16and [FDMSAccountNo] <>[Agent_Chain_No]And [FDMSAccountNo] <> [Corp_Chain_No] And [FDMSAccountNo] <> [Chain_Chain_No] group by DATENAME(Quarter, CAST(CONVERT(VARCHAR(8), open_date) AS DATETIME))Javeed Ahmed |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2014-03-20 : 08:30:25
|
DATEPART(QUARTER, Open_date) as DatePartQuater |
|
|
MuralikrishnaVeera
Posting Yak Master
129 Posts |
Posted - 2014-03-21 : 01:41:55
|
Run the below Snippet ...Hope it will clarifies your doubt......CREATE TABLE [Dim_Merchant_Log_All]([FDMSAccountNo] INT IDENTITY ,[Open_Date] DATETIME )INSERT INTO [Dim_Merchant_Log_All] VALUES ('2014-03-01 00:00:00.000'),('2014-03-01 00:00:00.000'),('2014-05-01 00:00:00.000') ,('2014-05-01 00:00:00.000'),('2014-06-01 00:00:00.000'),('2014-06-01 00:00:00.000') ,('2014-07-01 00:00:00.000'),('2014-11-01 00:00:00.000'),('2014-11-01 00:00:00.000') ,('2014-11-01 00:00:00.000'),('2014-11-01 00:00:00.000')SELECT 'First quater Count IS....'+CAST(COUNT([FDMSAccountNo]) AS VARCHAR(50)) AS Outlets FROM [Dim_Merchant_Log_All] WHERE DATEPART(QUARTER,[Open_Date]) = 1UNION ALLSELECT 'Second quater Count IS....'+CAST(COUNT([FDMSAccountNo]) AS VARCHAR(50)) FROM [Dim_Merchant_Log_All] WHERE DATEPART(QUARTER,[Open_Date]) = 2UNION ALLSELECT 'Third quater Count IS....'+CAST(COUNT([FDMSAccountNo]) AS VARCHAR(50)) FROM [Dim_Merchant_Log_All] WHERE DATEPART(QUARTER,[Open_Date]) = 3UNION ALLSELECT 'Fourth quater Count IS....'+CAST(COUNT([FDMSAccountNo]) AS VARCHAR(50)) FROM [Dim_Merchant_Log_All] WHERE DATEPART(QUARTER,[Open_Date]) = 4Murali Krishna |
|
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2014-03-21 : 03:18:45
|
create table the_table ( [DateKey] INT,)insert into the_tablevalues(20120101),(20120201),(20120301),(20120401),(20120501),(20120601),(20120701),(20120801),(20120901),(20121001),(20121101),(20121201);WITH myDateCTE(DateKey, Date) as ( SELECT DateKey ,[Date] = CONVERT(DATETIME, CONVERT(CHAR(8),DateKey),112) FROM the_table ),CTE(datekey,date,quarterstring) as (SELECT t.[DateKey] , m.[Date] , [QuarterString] = 'Q' + CONVERT(VARCHAR(20),Datepart(qq,Date)) FROM the_table t inner join myDateCTE m on t.DateKey = m.DateKey ) SELECT DATENAME(MONTH,MIN(date)) AS QuarterStart , DATENAME(MONTH,MAX(date)) AS QuarterEnd , QuarterString FROM cte GROUP BY DATEPART(qq,date),QuarterStringVeera |
|
|
|
|
|
|
|