November 20, 2006 at 1:13 pm
I am attempting to create stored procedures for my sql calls. I have this sql call
SELECT
Level2ID, Level2Name, sum(TOTAL_SUBS) as TOTAL_SUBS, sum(TOTAL_COMPANIES) as TOTAL_COMPANIES FROM (SELECT distinct Level2ID, Level2Name, count(distinct Level3ID)as TOTAL_SUBS, '' as TOTAL_COMPANIES FROM dbo.tbl_MF_Prod_Active WHERE Level2ID is not null AND Level1ID = '12 00 00' group by Level2ID, Level2Name union SELECT DISTINCT Level2ID, Level2Name, '' as TOTAL_SUBS, count distinct CompanyID) as TOTAL_COMPANIES FROM dbo.tbl_MF_Prod_Active p right outer join tbl_Listings l on p.MF_ID = l.MF_ID WHERE p.MF_ID in (Select Distinct MF_ID From dbo.tbl_Listings) AND p.Level2ID is not null AND p.Level1ID = '12 00 00' AND l.ProcessedYN = 'Y' group by p.Level2ID, p.Level2Name) TBL group by level2ID, Level2Name ORDER BY Level2Name ASC
This returns two fields the total subs and total companies fields. I need to compare these two values. Basically if total subs is greater than 0 I need to create a link in code for the level2 id, if it = 0 then I need to check total companies for a value greater than 0 and again create a link with level2. If they are both 0 I do not want to do anything. On the UI tier of this I only want to display a datalist that is bound to a table. I am wondering if I need to create the logic I need in the business tier or data tier (within the sp). What I want either of these tier to do is compare the totals and then be able to let the UI know which literal it needs to display (x subcategories of x companies). I would like to put this in the sp to display a table that had the level2id, level2name, count, and a true/false that says true - subcategory, false - total companies. so that the datatable that is return in the business tier only will show levels that have further data and the UI tier can just use a datalist.
November 20, 2006 at 1:16 pm
also, I want to create a sp that will allow me to plug in optional parameters. These levels can traverse down to level 5. So each subsequent call to the sp will have an additional level to drill down to. Taken the above sql call, subsequent calls will ask for level 3 id and level 3 name where level3id is not null and level2id = previous id and level1id = previous id (from querystrings).
November 20, 2006 at 3:30 pm
A CASE statement MIGHT work:
SELECT CASE WHEN drvT.Total_Sub > 0
THEN LEVEL2ID
WHEN drvT.Total_Sub = 0 AND
drvT.Total_Companies = 0
THEN LEVEL2NAME
ELSE 0 END
FROM (SELECT sum(TOTAL_SUBS) as TOTAL_SUBS, sum(TOTAL_COMPANIES) as TOTAL_COMPANIES FROM (SELECT distinct Level2ID, Level2Name, count(distinct Level3ID)as TOTAL_SUBS, '' as TOTAL_COMPANIES FROM dbo.tbl_MF_Prod_Active WHERE Level2ID is not null AND Level1ID = '12 00 00' group by Level2ID, Level2Name union SELECT DISTINCT Level2ID, Level2Name, '' as TOTAL_SUBS, count distinct CompanyID) as TOTAL_COMPANIES FROM dbo.tbl_MF_Prod_Active p right outer join tbl_Listings l on p.MF_ID = l.MF_ID WHERE p.MF_ID in (Select Distinct MF_ID From dbo.tbl_Listings) AND p.Level2ID is not null AND p.Level1ID = '12 00 00' AND l.ProcessedYN = 'Y' group by p.Level2ID, p.Level2Name) TBL group by level2ID, Level2Name) drvT
Play with that and see if you can get what you need.
-SQLBill
November 21, 2006 at 7:38 am
SQLBill,
Thanks that was a great starting point and exactly what I needed. I did change it up a bit. I still need the first four column values but I wanted an indicator to tell me which one had a value greater than 0 in sql. Of course the subs value being first is more important than the company. So now when I bind this to the datalist on the UI Tier I can check the value of type and the display the correct count.
SELECT
Level2ID, Level2Name, TOTAL_SUBS, TOTAL_COMPANIES, CASE WHEN drvT.TOTAL_SUBS > 0
THEN 1
WHEN
drvT.TOTAL_COMPANIES > 0
THEN
2
ELSE
0 END as TYPE
FROM
(SELECT Level2ID, Level2Name, sum(TOTAL_SUBS) as TOTAL_SUBS, sum(TOTAL_COMPANIES) as TOTAL_COMPANIES FROM (SELECT distinct Level2ID, Level2Name, count(distinct Level3ID)as TOTAL_SUBS, '' as TOTAL_COMPANIES FROM dbo.tbl_MF_Prod_Active WHERE Level2ID is not null AND Level1ID = '12 00 00' group by Level2ID, Level2Name union SELECT DISTINCT Level2ID, Level2Name, '' as TOTAL_SUBS, count (distinct CompanyID) as TOTAL_COMPANIES FROM dbo.tbl_MF_Prod_Active p right outer join tbl_Listings l on p.MF_ID = l.MF_ID WHERE p.MF_ID in (Select Distinct MF_ID From dbo.tbl_Listings) AND p.Level2ID is not null AND p.Level1ID = '12 00 00' AND l.ProcessedYN = 'Y' group by p.Level2ID, p.Level2Name) TBL group by level2ID, Level2Name) drvT
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply