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
SELECT 100 UNION
SELECT 105 UNION
---- view 1
CREATE VIEW Customer1
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY myid ORDER BY mydate) as RN
FROM OUTPUT_table m WHERE CUSTOMERID = '100'
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;
------ view 2
CREATE VIEW customer2 AS
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
WHERE mt.CUSTOMERID = '105'
How can i call 1st view when table have value 100, and similarly for other
Thanks a lot for your help.