Date function help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date function help

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 quarter
To calculate this I was going to use the open date.

Column :Open_Date
Date type : datetime
Format :2014-03-01 00:00:00.000

I need a fixed quarter
Jan – March Q1
April – June Q2
July – Sept Q3
Oct – Dec Q4

My query so far is

SELECT 'Total Number Of Merchants Added Q Singles',
COUNT ([FDMSAccountNo]) as Outlets
FROM [FDMS].[dbo].[Dim_Merchant_Log_All]
where [Account_Status] = 16
and [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 this
SELECT '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 quartername
FROM [FDMS].[dbo].[Dim_Merchant_Log_All]
where [Account_Status] = 16
and [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]) = 1
UNION ALL
SELECT 'Second quater Count IS....'+CAST(COUNT([FDMSAccountNo]) AS VARCHAR(50)) FROM [Dim_Merchant_Log_All] WHERE DATEPART(QUARTER,[Open_Date]) = 2
UNION ALL
SELECT 'Third quater Count IS....'+CAST(COUNT([FDMSAccountNo]) AS VARCHAR(50)) FROM [Dim_Merchant_Log_All] WHERE DATEPART(QUARTER,[Open_Date]) = 3
UNION ALL
SELECT 'Fourth quater Count IS....'+CAST(COUNT([FDMSAccountNo]) AS VARCHAR(50)) FROM [Dim_Merchant_Log_All] WHERE DATEPART(QUARTER,[Open_Date]) = 4

Murali Krishna

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-21 : 03:18:45

create table the_table
(
[DateKey] INT,
)

insert into the_table
values
(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),QuarterString

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources