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 - 2013-12-09 : 03:46:07
|
Hi All I spent all weekend trying to resolve this, but to no avail Aim – Count How many FDMSAccountNo, with the account status 1 &16, when the [LAST-STATUS-CHG] was previous month.My query so far isSELECT 'Closing balance of previous month', count(FDMSAccountNo) FROM [FDMS].[dbo].[stg_LMPAB501]where [ACCOUNT-STATUS] in ('01','16')[LAST-STATUS-CHG] Column Name [LAST-STATUS-CHG]DataType :nvarchar(6)The data is represented like this “020312” Would appreciate any help available, as this is really bugging me |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-12-09 : 04:08:32
|
"020312" is it YYMMDD or MMDDYY or DDMMYY or what is it? Too old to Rock'n'Roll too young to die. |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-12-09 : 04:26:09
|
HI webfredDateformat is mmddyy |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-12-09 : 04:44:06
|
where [ACCOUNT-STATUS] in ('01','16')and left([LAST-STATUS-CHG],2) = month(dateadd(month,-1,getdate())) Too old to Rock'n'Roll too young to die. |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-12-09 : 05:38:41
|
HI Webfred i lied its actually DDMMYY |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-12-09 : 05:44:57
|
where [ACCOUNT-STATUS] in ('01','16')and substring([LAST-STATUS-CHG],3,2) = month(dateadd(month,-1,getdate())) Too old to Rock'n'Roll too young to die. |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-12-09 : 05:52:48
|
Hi Webfredi have done the following " right([LAST-STATUS-CHG],2) as year" to isolate the yearif i want to get the current year would it be right([LAST-STATUS-CHG],2) =year(GETDATE()) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-09 : 06:13:19
|
quote: Originally posted by masond Hi Webfredi have done the following " right([LAST-STATUS-CHG],2) as year" to isolate the yearif i want to get the current year would it be right([LAST-STATUS-CHG],2) =year(GETDATE())
ideally it should be[code]...CASE WHEN right([LAST-STATUS-CHG],2) >= 50 THEN '19' ELSE '20' END + right([LAST-STATUS-CHG],2) =year(GETDATE())..[/code------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|