How can I loop through a table within a stored procedure?

Question

This question evolved from this one.

I have two tables that I need to query and glean some calculated sums from; I need a result set based on units -- one row for each Unit, with calculated data for them folded into that row.

The two tables contain the following pertinent members:

CustomerCategoryLog:

Unit        varchar(25)
MemberNo    varchar(10)
Category    varchar(50)
Subcategory varchar(50)
BeginDate   Datetime
EndDate     Datetime

ReportingMonthlySales:

Unit (VarChar)
MemberNo (VarChar)
MonthlySales (Money)
CYear (Int)
Cmonth (Int)

For each Unit (which has many rows in both tables, one for each MemberNo, but comprises a single row in the result set), I need to populate four columns: New, Assumed, Existing, and Organic. These values are the sums of all Members for the Unit based on those belonging to the corresponding "Subcategory" field. The CustomerCategoryLog table's BeginDate/EndDate values are used for determining what Subcategory the Unit/Member was a part of during the month/year being evaluated.

So a simplified form of the result set looks like this:

Unit    New  Assumed        Existing    Organic     Total
----    ---  -------        --------    -------     -----
Abuelos $22  $44            $33         $11         $110
Gramps  $12  $23            $1          $34         $70
. . .

To put the problem in English, it's something like:

Given the month and year provided as parameters by the user (such as "1" for Month (January) and "2016" for year), find out for each Unit how much $ there was in MonthlySales for each Subcategory within that month (where the BeginDate was less than or equal to the Month/Year date supplied by the user AND the EndDate was greater than or equal to the Month/Year date supplied).

So it seems that I will need to create a Date from the Year/Month parameters for comparison with the BeginDate and EndDate values in the CustomerCategoryLog table (The other option would be to alter the CustomerCategoryLog table, so that it has BeginDateMonth, BeginDateYear, EndDateMonth, and EndDateYear ints; but I reckon there must be a somewhat straightforward way to create a date from the Year/Month parameters supplied).

My problem is how to actually construct that in TSQL. I'm not a SQLhead, and my best (pseudosql) stab at it is:

DECLARE @Unit varchar(30);
DECLARE @Year Int;
DECLARE @Month Int;
. . .
DECLARE @PARAMDATE DATETIME = (Year + Month + 01).ToDateTime();

SELECT DISTINCT UNIT INTO #UNITS U FROM ReportingMonthlySales
WHILE NOT U.EOF DO

Unit = U.Unit

SELECT Unit, MonthlySales as 'NewSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left join     
CustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND RMS.CYear = @Year and RMS.CMonth = @Month     
AND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'New'),

MonthlySales as 'AssumedSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left join     
CustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND  RMS.CYear = @Year and RMS.CMonth = @Month     
AND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'Assumed'),

MonthlySales as 'ExistingSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left join     
CustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND  RMS.CYear = @Year and RMS.CMonth = @Month     
AND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'Existing'),

MonthlySales as 'OrganicSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left join     
CustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND  RMS.CYear = @Year and RMS.CMonth = @Month     
AND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'Organic')
FROM ReportingMonthlySales RMS2
ORDER BY RMS2.Unit

END WHILENOTEOF

I know this is not quite right, and probably not even remotely right, but hopefully it's clear enough for a SQL expert/jr. mind reader to understand what it is I need to do/am trying to do.

UPDATE

Here's some sample data from the two tables that are queried:

CustomerCategoryLog table:

MemberNo = 007
Unit = AMC THEATERS
Subcategory = New
BeginDate = 1/1/2016
EndDate = 12/31/2016

MemberNo = 029
Unit = FOODBUY HMS
Subcategory = Existing
BeginDate = 1/1/2015
EndDate = 12/31/2015

ReportingMonthlySales table:

Unit = AMC THEATERS
MemberNo = 007
MonthlySales  = $988.82
CYear = 2016
Cmonth = 1

Unit = FOODBUY HMS
MemberNo = 029
MonthlySales  = $61,479.28
CYear = 2017
Cmonth = 3

What would really help is some details about your tables. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ That query you posted is a wall of text and makes my eyes bleed. — Sean Lange, Mar 27 '17 at 21:23
Did you look at the link I posted? Things like tables names, column names, datatypes, sample data. You know...the kind of details that would make this answerable. — Sean Lange, Mar 27 '17 at 21:25
"where the BeginDate was greater than or equal to the Month/Year date AND the EndDate was less than or equal to the Month/Year date)." Is this backwards or am I not reading it correctly? — SqlZim, Mar 27 '17 at 21:26
@SeanLange: Aren't all those things above - the two table names and their columns? Okay, I'll add some sample data... — B. Clay Shannon-B. Crow Raven, Mar 27 '17 at 21:27
@SqlZim: Possibly, but I don't think so. If BeginDate is 4/1/2016 and EndDate is 7/1/16, and the month the user wants to query is June of 2016, that month that interests them (6/2016) is greater than or equal to the BeginDate (4/2016) and less than or equal to the EndDate (7/2016). — B. Clay Shannon-B. Crow Raven, Mar 27 '17 at 21:29
@B.ClayShannon but it says "EndDate was less than or equal to the Month/Year date" not "Month/Year date less than or equal to EndDate" — SqlZim, Mar 27 '17 at 21:31
Use example data to demonstrate the intended behaviour and any known corner cases — MatBailie, Mar 27 '17 at 21:36
Aside from dates, what is the relationship between ReportingMonthlySales and CustomerCategoryLog, is it Unit or Unit and MemberNo? Is it possible for more than one row in CustomerCategoryLog to exist per related ReportingMonthlySales row, and if so do you count the MonthlySales for each instance per category or do you want MonthlySales to only show up once per subcategory per unit per, uh, ModelNo? — Bill Hall, Mar 27 '17 at 21:38

SqlZim · Accepted Answer · 2017-03-27T22:00:32.100

4

This just looks like the long way to pivot.

How about something like this?

declare @Unit varchar(30);
declare @Year int;
declare @Month int;
declare @paramdate datetime = datefromparts(@year, @month, 1);
/* --prior to sql server 2012
declare @paramdate datetime;
set @paramdate = convert(datetime,convert(char(4),@Year)
                +right('0'+convert(varchar(2),@month),2)
                +'01') 
*/

select distinct unit
into #Units
from ReportingMonthlySales;

select 
    u.Unit
  , New      = sum(case when ccl.Subcategory = 'New'      then rms.MonthlySales else 0 end)
  , Assumed  = sum(case when ccl.Subcategory = 'Assumed'  then rms.MonthlySales else 0 end)
  , Existing = sum(case when ccl.Subcategory = 'Existing' then rms.MonthlySales else 0 end)
  , Organic  = sum(case when ccl.Subcategory = 'Organic'  then rms.MonthlySales else 0 end)
from #Units u
  left join CustomerCategoryLog ccl 
    on u.Unit = ccl.Unit
   and @paramdate >= ccl.begindate
   and @paramdate <= ccl.enddate
  left join ReportingMonthlySales rms
    on u.Unit = rms.Unit
   and rms.cyear  = @year
   and rms.cmonth = @month
group by u.unit;

edited Mar 27 '17 at 22:00

answered Mar 27 '17 at 21:46

SqlZim

37,248
6
41
59

Thanks; this looks very promising, but I'm getting "Error 102: Incorrect syntax near 'U'." here: into #Units U – B. Clay Shannon-B. Crow Raven Mar 27 '17 at 21:58
@B.ClayShannon remove the `U` after `into #Units`. I missed that when I was reshaping your psuedo-code. – SqlZim Mar 27 '17 at 22:01
Cool; it seems to work; it returns no data, but I think that's a data problem...I'll try some other args/param vals. – B. Clay Shannon-B. Crow Raven Mar 27 '17 at 22:03
The last record added to CustomerCategoryLog always has EndDate unset (null), so I had to change the second date check to this: and @paramdate <= ccl.enddate OR ccl.enddate is null – B. Clay Shannon-B. Crow Raven Mar 27 '17 at 22:39
1

@B.ClayShannon ahh that makes sense. Glad you got it working! – SqlZim Mar 28 '17 at 01:36
It's still generating some humongous values in some cases; I will have to delve deeper into it to see whether they are accurate, or there is a data problem, or what's going on...I may update later, or post a new question. – B. Clay Shannon-B. Crow Raven Mar 28 '17 at 15:22
@B.ClayShannon You might check to make sure you don't have overlapping date ranges in `CustomerCategoryLog` – SqlZim Mar 28 '17 at 15:26
It's all test data for now, and they all have the same BeginDate of 1/1/2017, and all but a few have an EndDate of null (the handful of exceptions have enddate of 3/22/2017) – B. Clay Shannon-B. Crow Raven Mar 28 '17 at 15:34
@B.ClayShannon Thank you very much for the additional bounty, I'm not sure this answer truly deserved it though. – SqlZim Apr 06 '17 at 17:29

How can I loop through a table within a stored procedure?

UPDATE

1 Answers1

Linked