Combine Multiple views and invoke it using Stored Procedure

1

Hello Techie, may anyone please share your expertise .

i have a table contain customerid which is getting truncated each time before loading other set of data.

i have multiple view created for each customer.

i am trying to create a SP which can combine all the view, and execute the view based on the customerID available in the table.

DDL AND views:

Table CREATE TABLE CUSTOMER (

[CUSTOMERID] [int] NOT NULL
)

INSERT CUSTOMER

SELECT 100 UNION
SELECT 105 UNION
SELECT 108

---- view 1

CREATE VIEW Customer1
AS
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY myid ORDER BY mydate) as RN
FROM OUTPUT_table m WHERE CUSTOMERID = '100'
)

SELECT c.*,
ISNULL(DATEADD(second,-1,c2.mydate),'12/31/9999 12:00:00 AM') as TO_DATE

FROM cte c
LEFT JOIN cte c2
ON c2.RN = c.RN + 1 AND c.myid = c2.myid;

GO

------ view 2

CREATE VIEW customer2 AS
(
SELECT
*,
CASE WHEN MYCurrent = 1 THEN '9999-12-31' ELSE TO_DATE END AS TO_DATE,
FROM OUTPUT_TABLE mt

OUTER APPLY ( SELECT MAX(DATEADD(second, -1, mydate)) TO_DATE
FROM OUTPUT_TABLE mt2
WHERE mt2.myid = mt.myid
AND mt2.mydate > mt.mydate
) oa
WHERE mt.CUSTOMERID = '105'
)
GO

How can i call 1st view when table have value 100, and similarly for other
Thanks a lot for your help.

2

CREATE PROC someproc (@customerid int)
AS

IF @customerid = 100 SELECT ... FROM view1...
IF @customerid = 105 SELECT ... FROM view2...

3

Couple of suggestions:

Don't use SELECT * - list all the columns you want included in the view by name, even if that is ALL the columns.

Don't use bogus dates, such as "9999-12-31" as a "flag value". Either use NULL, or have a separate column that indicates the attribute /property that you need

Both of these will come back to bite you in the future, otherwise.

SELECT MAX(DATEADD(second, -1, mydate)) TO_DATE 
FROM OUTPUT_TABLE mt2

is not SARGable - so will not use any suitable index, and thus will perform badly. Try to avoid using Functions on Columns in SELECT / WHERE clauses. I would use this:

SELECT DATEADD(second, -1, MAX(mydate)) TO_DATE 
FROM OUTPUT_TABLE mt2

although I'm curious what your reason for subtracting one second is?

Is it intended that the customer2 View return ALL Rows from OUTPUT_TABLE for CUSTOMERID = '105' - each one with the [self-referenced] MAX mydate from OUTPUT_TABLE?

I'm not sure that an OUTER APPLY in your second view is the most efficient way of getting "MAX Date from Child Table", but maybe the optimiser is smart enough to correlate it efficiently. Either way, an INDEX on OUTPUT_TABLE(myid, mydate) would most likely help (provided the query is SARGable ...). I would try a JOIN instead:

LEFT OUTER JOIN
(
    SELECT myid,
           DATEADD(second, -1, MAX(mydate)) AS TO_DATE 
    FROM OUTPUT_TABLE AS mt2 
    GROUP BY myid
) AS MT2
    ON mt2.myid = mt.myid 
    AND mt2.mydate > mt.mydate

I'm curious why your views are restricted to a single CUSTOMERID? If you included CUSTOMERID in the PARTITION BY of the first view then you could include CUSTOMERID in your WHERE clause when you use the VIEW - so it would work for any customer. Similarly for your second view

4

Thanks Kristen. for shorten the question, i have used *. i will remove the bogus date.. will it be possible to slightly modify the SP. instead of hard code like (IF @customerid = 100) will it be possible to look into the
customer table for execution of specific view.

something like when column value of table and were clause of view matched. than the specific view run.

each view restricted to single customer id because each customer has different logic to be apply.

Please suggest.

5

Thanks TaraKizer

6

If you have separate reports for each Customer then I suppose that is OK, but its a maintenance nightmare - a new Customer means that DEV / DBA staff have to make new VIEWs ...

Only alternative I can think of is that you have some sort of "Config Table" which contains the "Rules" for each Customer, and then apply those Rules in the VIEW

7

Hi Kristen,

you are absolutely right I have separate reports for each Customer. Please help me on structure and value
of config table with rules for each Customer, and then how i can apply those Rules in the VIEW.

Please share your expertise.

Thanks a lot

8

Customer 1 wants a 1,2,3,... RowNumber on each row (in MyDate order) and a "flag" value if MyDate is NULL

Customer 2 wants a flag value if MyCurrent=1 (Why can't they use MyCurrent for that?) and the MAX MyDate (ever) showing on every row

Is that it?

1 Like