How can I go through a table in a stored procedure?
This question arose out of this .
I have two tables that I need to query and collect some estimated amounts; I want a result set based on Units - one row for each Units, with the computed data for them added to that row.
The two tables contain the following matching elements:
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 module (which has many rows in both tables, one for each MemberNo, but contains one row in the result set), I need to fill in four columns: New, Intended, Existing, and Organic. These values are the sums of all the members for Units, based on the values associated with the corresponding Subcategory field. The BeginDate / EndDate tables of the CustomerCategoryLog table are used to determine which subcategory is the Unit / Member during the month / year being assessed.
Thus, the 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 is something like:
Given the month and year specified by the user as parameters (for example, "1" for the month (January) and "2016" for the year), find out for each department how much $ was in MonthlySales for each subcategory within that month (where BeginDate was is less than or equal to the date of the month / year provided by the user, and EndDate was greater than or equal to the date of the month / year).
So it seems to me that I would need to create Date from the Year / Month parameters to compare against the BeginDate and EndDate values in the CustomerCategoryLog table (another option is to change the CustomerCategoryLog table to have BeginDateMonth, BeginDateYear, EndDateMonth and EndDateYear, but I believe that there should be a somewhat easy way to create a date from the provided Year / Month parameters).
My problem is how to actually build this in TSQL. I am not SQLhead and my best (pseudosql) stroke is in it:
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 entirely correct, and perhaps not even remotely, but hopefully this is clear enough for a SQL / jr expert. to understand what I need to do / try to do.
UPDATE
Here is some data from the two tables requested:
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
source to share
It looks like a long way to turn.
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;
source to share