Case Statement 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

Case Statement Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-05-09 : 03:51:02

Hey Guys

Sorry to pester you so early in the morning but i really need some help

Aim – To identify fdmsaccountno
Who haven’t got a last post date since 1st Jan 2013 – Present
Last post date = “datatype” = smalldatetime

Any help would be appreciated

My query so far is

SELECT
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo,
stg_FDMS_Merchant_Control_Data.[Account Status],
stg_FDMS_Merchant_Membership_Data.[Open Date],
stg_FDMS_Merchant_Membership_Data.[First Post Date],
stg_FDMS_Merchant_Membership_Data.[Last Post Date],
stg_FDMS_Merchant_Membership_Data.LastStatusChg,
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo,
sum(Fact_Financial_History.hst_sales_amt) as Sales,
sum(Fact_Financial_History.hst_sales_tran) as Trans
FROM Fact_Financial_History INNER JOIN Dim_Outlet
INNER JOIN
stg_FDMS_Merchant_Membership_Data INNER JOIN
stg_FDMS_Merchant_Control_Data ON
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo = stg_FDMS_Merchant_Control_Data.FDMSAccountNo ON
Dim_Outlet.FDMSAccountNo = stg_FDMS_Merchant_Membership_Data.FDMSAccountNo ON
Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where YEAR(hst_date_processed) = year(DATEADD(YEAR,-1, GETDATE()))
group by stg_FDMS_Merchant_Membership_Data.FDMSAccountNo, [Account Status],[First Post Date],[Last Post Date],LastStatusChg,[Open Date]

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-05-09 : 04:18:10

Hi,
Please try the below query

declare @currentyearfirstdate datetime
select @currentyearfirstdate=DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
SELECT
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo,
stg_FDMS_Merchant_Control_Data.[Account Status],
stg_FDMS_Merchant_Membership_Data.[Open Date],
stg_FDMS_Merchant_Membership_Data.[First Post Date],
stg_FDMS_Merchant_Membership_Data.[Last Post Date],
stg_FDMS_Merchant_Membership_Data.LastStatusChg,
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo,
sum(Fact_Financial_History.hst_sales_amt) as Sales,
sum(Fact_Financial_History.hst_sales_tran) as Trans
FROM Fact_Financial_History INNER JOIN Dim_Outlet
INNER JOIN
stg_FDMS_Merchant_Membership_Data INNER JOIN
stg_FDMS_Merchant_Control_Data ON
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo = stg_FDMS_Merchant_Control_Data.FDMSAccountNo ON
Dim_Outlet.FDMSAccountNo = stg_FDMS_Merchant_Membership_Data.FDMSAccountNo ON
Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where YEAR(hst_date_processed) = year(DATEADD(YEAR,-1, GETDATE()))
and [Last Post Date] between GETDATE() and @currentyearfirstdate
group by stg_FDMS_Merchant_Membership_Data.FDMSAccountNo, [Account Status],[First Post Date],[Last Post Date],LastStatusChg,[Open Date]

mohammad.javeed.ahmed@gmail.com

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-05-09 : 04:27:27

hi ahmeds08

Thank you for your post, the query you provided me returns zero results
i built the query as follows

SELECT
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo,
stg_FDMS_Merchant_Control_Data.[Account Status],
stg_FDMS_Merchant_Membership_Data.[Open Date],
stg_FDMS_Merchant_Membership_Data.[First Post Date],
stg_FDMS_Merchant_Membership_Data.[Last Post Date],
stg_FDMS_Merchant_Membership_Data.LastStatusChg,
sum(Fact_Financial_History.hst_sales_amt) as Sales,
sum(Fact_Financial_History.hst_sales_tran) as Trans,

CASE WHEN [Last Post Date] BETWEEN '2013-01-01' and '2013-12-31' THEN 'Posted In 2013' ELSE 'Not Posted in 2013' END [Test]

FROM Fact_Financial_History
INNER JOIN Dim_Outlet
INNER JOIN stg_FDMS_Merchant_Membership_Data
INNER JOIN stg_FDMS_Merchant_Control_Data ON
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo = stg_FDMS_Merchant_Control_Data.FDMSAccountNo ON
Dim_Outlet.FDMSAccountNo = stg_FDMS_Merchant_Membership_Data.FDMSAccountNo ON
Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where YEAR(hst_date_processed) = year(DATEADD(YEAR,-1, GETDATE()))
--and [Last Post Date] < '2013-01-01'
group by stg_FDMS_Merchant_Membership_Data.FDMSAccountNo, [Account Status],[First Post Date],[Last Post Date],LastStatusChg,[Open Date]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-09 : 04:50:12

sounds like this to me with the limited info you provided


SELECT *
FROM
(
SELECT 
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo,
stg_FDMS_Merchant_Control_Data.[Account Status], 
stg_FDMS_Merchant_Membership_Data.[Open Date], 
stg_FDMS_Merchant_Membership_Data.[First Post Date],
stg_FDMS_Merchant_Membership_Data.[Last Post Date], 
MAX(stg_FDMS_Merchant_Membership_Data.[Last Post Date]) OVER (PARTITION BY stg_FDMS_Merchant_Membership_Data.FDMSAccountNo) AS MaxLastDate,
stg_FDMS_Merchant_Membership_Data.LastStatusChg,
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo, 
sum(Fact_Financial_History.hst_sales_amt) as Sales, 
sum(Fact_Financial_History.hst_sales_tran) as Trans
FROM Fact_Financial_History INNER JOIN Dim_Outlet 
INNER JOIN
stg_FDMS_Merchant_Membership_Data INNER JOIN
stg_FDMS_Merchant_Control_Data ON 
stg_FDMS_Merchant_Membership_Data.FDMSAccountNo = stg_FDMS_Merchant_Control_Data.FDMSAccountNo ON 
Dim_Outlet.FDMSAccountNo = stg_FDMS_Merchant_Membership_Data.FDMSAccountNo ON 
Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where YEAR(hst_date_processed) = year(DATEADD(YEAR,-1, GETDATE()))
group by stg_FDMS_Merchant_Membership_Data.FDMSAccountNo, [Account Status],[First Post Date],[Last Post Date],LastStatusChg,[Open Date]
)t
WHERE MaxLastDate < DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources