CODEWITHSUNDEEP
c#asp.netsqlsql-server
Adams
Adams

Reputation: 195

how to show past 20 days in this SQL Query

I want to change the criteria at the end. to the past 20 days. is this ? (DateTime.Now.Year ) - 20 ?

  sqlT1 = "SELECT ROW_NUMBER() OVER(ORDER BY ID_KEY DESC) AS RN,* From(Select distinct f.FACILITY_NAME, ID_KEY, [BATCH] AS column1, [IMPORTDATE], [DATEBILLED], [RX], [DATEDISPENSED], [DAYSUPPLY], [PAYTYPE], [NPI], [PHYSICIAN], [COST], [QUANTITY], [MEDICATION], A.[NDC], " +
                        " case when (COST > 0 AND DAYSUPPLY > 0) then (COST / DAYSUPPLY) * 30 else 0 end [30DayCost] , [PATIENTNAME], [ROUTEOFADMIN], [INVOICECAT], [COPAY], [BRAND], [TIER], [SKILLLEVEL], [STAT] STATUS, [LASTTASKDATE],SEQNO, 'please bring to the attention of the administrator.' SUBST_INSTRUCTIONS  , f.FACILITY_ID " +
                        " FROM [LMI].[T_CHARGES] A Left Outer Join PBM.FACILITY f on A.FACILITYNPI = f.FACILITY_NPI  " +
                        " Where COST > 500 " +
                        " AND [TIER] = 'T1' and month(A.DATEDISPENSED) = " + DateTime.Now.Month + " and year(A.DATEDISPENSED) = " + DateTime.Now.Year + "" +
                        sqlWhere + " AND f.FACILITY_ID IN (" + selected + ")";

Upvotes: 0

Views: 98

Answers (2)

Metaphor
Metaphor

Reputation: 6415

To get the date 20 days ago, you can use the sql function DATEADD:

WHERE A.DATEDISPENSED >= DATEADD(dd,-20,GetDate())

Upvotes: 0

RadioSpace
RadioSpace

Reputation: 952

well I don't know about changing your code for you but generally in SQL you use getdate() to get today's date and you use dateadd(d,-20,getdate()) to get 20 days ago so use

(t-sql):

 testingdate between dateadd(d,-20,getdate()) and getdate()

in your where clause

Upvotes: 1

Related Questions