MS SQL - Loop Query

Sure, here is the basic idea, adjust the outer select as needed.

select *
from table1
where month in (select unique month from table1)
and otherfield = 'othervalue'
;

Select allOpen in new window

---

»bp

I assume this is just the SQL logic. How would you incorporate the iteration for multiple months?

Can you share the SQL statement you want execute "multiple" times?

---

»bp

maybe something like this

select *
  from (select 1 as n union all select 2 union all select 3  union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) m
  left join myTable t on t.month=m.n 

Select allOpen in new window

As you can see below, I have to query each month but I would like a looped iteration (if possible) for every month in one run for a year.

select

b.store_Num,
a.store_UIN,
b.store_Name,
COUNT((RO_NO)) as [Car Count],
FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP -DISL - DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') AS [ARO]

from HRO as a
inner join StoreInfo as b
	on a.store_UIN = b.store_UIN

where
(PAY_DATE >= '06/01/2017') and (PAY_DATE <= '06/30/2017') and [Status] IN ('C','Q') 

GROUP BY b.store_Num, a.Store_UIN, b.Store_Name
ORDER BY B.store_Num ASC

Select allOpen in new window

So do you need 12 separate queries, or could you just group by the 12 months at the highest level?

---

»bp

Grouped at the highest level?

Yes, conceptually extract the month from the PAY_DATE, and then use that in front of Store_Num in the GROUP BY.

---

»bp

I guess I'm not following you here...

sorry

just use one query

what do you want to get? last 12 months or this year data?

if you use

where
(PAY_DATE >= '06/01/2017') and (PAY_DATE <= '06/30/2017') and [Status] IN ('C','Q')

Select allOpen in new window

>>>

where [Status] IN ('C','Q')

Select allOpen in new window

then you get all data :)

to get current year data:

where PAY_DATE >= cast(cast(year(getdate()) as varchar) + '0101' as date) and  [Status] IN ('C','Q')

Select allOpen in new window

to get last 12 month

where PAY_DATE >= cast(cast(dateadd(month, -12, dateadd(day, 1-day(getdate()) , getdate())) as date) as datetime) and  [Status] IN ('C','Q')

Select allOpen in new window

Adjust the month calcs in the WHERE clause as needed to get the specific range of dates you need.

--select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) --just to show the values the current date calcs yield

select

b.store_Num,
a.store_UIN,
b.store_Name,
CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, a.PAY_DATE), 0) AS date) AS Pay_Month,
COUNT((RO_NO)) as [Car Count],
FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP -DISL - DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') AS [ARO]

from HRO as a
inner join StoreInfo as b
      on a.store_UIN = b.store_UIN

where
(a.PAYDATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) AND
 a.PAYDATE < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) and /*Note: less than NOT less than or equal to*/
[Status] IN ('C','Q')

GROUP BY b.store_Num, a.Store_UIN, b.Store_Name, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, a.PAY_DATE), 0) AS date)
ORDER BY B.store_Num ASC

This solution did what I needed it to do. All others, thank you for contributing. However, Bill provided the solution I needed.

Thanks, Bill!

Welcome, glad that was helpful.

---

»bp