Date Query 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 Query Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-21 : 11:51:40

Hey Guys

I hope you are well

Aim –To produce a unique line by month for the FDMSAccountNo
I have created the following query (see below) which produces the following result set

FDMSAccountNo AvgVal Card_Type Pence_Percent fee_wholesale_date BegMonthDate
878000001882 0.012900000 Credit Percent 2009-07-08 2009-07-01 00:00:00.000
878000001882 0.015700000 Credit Percent 2011-02-24 2011-02-01 00:00:00.000
878000001882 0.014700000 Credit Percent
2011-02-25 2011-02-01 00:00:00.000

SELECT
[FDMSAccountNo],
AVG([fee_retail_amt]*1.0) AS AvgVal,
Card_Type,
[Pence_Percent],
[fee_wholesale_date],
DATEADD(mm,DATEDIFF(mm,0,[fee_wholesale_date]),0) AS BegMonthDate
FROM
(
SELECT
[FDMSAccountNo]
,[fee_sequence]

,[fee_retail_amt]
,[fee_wholesale_date]
,Dim_Fee_Codes.Card_Type
,[Pence_Percent]
,ROW_NUMBER() OVER (PARTITION BY FDMSaccountno,fee_sequence ORDER BY Fee_Wholesale_date DESC) AS Seq
FROM [FDMS].[dbo].[Audit_FDMS_Billing_Fees_Hist]
INNER JOIN Dim_Fee_Codes ON Audit_FDMS_Billing_Fees_Hist.fee_sequence = Dim_Fee_Codes.Fee_Code
where fee_retail_amt <> '0.00000000'
and Card_Type <> 'N/A'
and Card_Type = 'Credit'
and Pence_Percent = 'Percent'
and FDMSAccountNo = '878000001882'
)t
WHERE Seq=1
GROUP BY
[FDMSAccountNo]
,[fee_wholesale_date]
,Card_Type
,[Pence_Percent]

Desired results
FDMSAccountNo AvgVal Card_Type Pence_Percent fee_wholesale_date
878000001882 0.012900000 Credit Percent 2009-07-08
878000001882 0.012900000 Credit Percent 2009-08-08
878000001882 0.012900000 Credit Percent 2009-09-08
878000001882 0.012900000 Credit Percent 2009-10-08
878000001882 0.012900000 Credit Percent 2009-11-08
878000001882 0.012900000 Credit Percent 2009-12-08
878000001882 0.012900000 Credit Percent 2010-01-08
878000001882 0.012900000 Credit Percent 2010-02-08
Etc until the new fee_wholesale_date is found
878000001882 0.015700000 Credit Percent 2011-02-24
878000001882 0.014700000 Credit Percent 2011-02-25

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 14:50:51

[code]
;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY FDMSAccountNo ORDER BY fee_wholesale_date) AS Seq,*
FROM YourTable
)

SELECT c1.FDMSAccountNo,
c1.AvgVal,
c1.Card_Type,
c1.Pence_Percent,
DATEADD(mm,v.number,c1.fee_wholesale_date) AS fee_wholesale_date
FROM CTE c1
INNER JOIN CTE c2
ON c1.FDMSAccountNo = c2.FDMSAccountNo
AND c1.Seq = c2.Seq -1
CROSS JOIN master..spt_values v
WHERE v.type = 'p'
AND DATEADD(mm,v.number,c1.fee_wholesale_date) < c2.fee_wholesale_date
[/code]

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