Setting up a view with parameters

Question

Post reply

Setting up a view with parameters

Tourless

SSC Enthusiast

Points: 175

October 6, 2023 at 6:42 pm

#4302928

Hi Folks,

I'm not very well versed in SQL so I come here to the experts for advise.

I have a nice little query that is currently using hard coded values for customer name, start and end dates. I need to be able to pass those values as parameters which I don't know how to do. To complicate things, the source data stores date values as Epoch so I need to be able to pick my dates and convert those values before passing them as parameters. Here's what I have so far. Any help is greatly appreciated. - Thank you.

WITH CTE(Name, Date, Weight, Type) AS 
(SELECT lmreceive.CustName, lmreceive.LdryTargRecvDate, SUM(lmrecvcart.LdryNettWght) AS Expr1, 'S' AS Type 

FROM linenmaster118.lmreceive AS lmreceive INNER JOIN linenmaster118.lmrecvcart AS lmrecvcart ON lmreceive.RcID = lmrecvcart.RecvRcID

WHERE (lmreceive.LdryCode = 'FDRNY') AND (lmreceive.InvtAcctCode = 'RENT HOSP') AND (lmreceive.CustName = 'CHS - Good Samaritan Hospital') AND  (lmreceive.LdryTargRecvDate BETWEEN '1693569600' AND '1696075200') GROUP BY lmreceive.CustName, lmreceive.LdryTargRecvDate

UNION ALL

SELECT lmdelivery.CustName, lmdelivery.LdryTargDelvDate, SUM(lmdelvcart.LdryNettWght) AS Expr1, 'C' AS Type

FROM linenmaster118.lmdelivery AS lmdelivery INNER JOIN linenmaster118.lmdelvcart AS lmdelvcart ON lmdelivery.RcID = lmdelvcart.DelvRcID

WHERE (lmdelivery.LdryCode = 'FDRNY') AND (lmdelivery.InvtAcctCode = 'RENT HOSP') AND (lmdelivery.CustName = 'CHS - Good Samaritan Hospital') AND 
(lmdelivery.LdryTargDelvDate BETWEEN '1693569600' AND '1696075200')

GROUP BY lmdelivery.CustName, lmdelivery.LdryTargDelvDate)

SELECT Name, Date, SUM(CASE WHEN Type = 'S' THEN Weight END) AS SoilWeight, SUM(CASE WHEN Type = 'C' THEN Weight END) AS CleanWeight

FROM CTE AS CTE_1

GROUP BY Name, Date

October 7, 2023 at 8:27 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

A table-valued function might do what you need. Have a look at the following link and see whether it gets you on the way:

https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/

As you've no doubt already discovered, parameterised views are not possible in SQL Server.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tourless SSC Enthusiast Points: 175 More actions · Answer 2

Thanks for chiming in here Phil and yes, I do know about parameterized views not being possible. My first thought was to change my view to a stored procedure since it can be passed parameters. I'll take a hard read on the table value functions and see if I can implement. -thank you.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

Here is a TVF:

CREATE FUNCTION myFunctioname
(
    @LdryCode as varchar(100),
    @InvtAcctCode as varchar(100),
    @CustName as varchar(100),
    @LdryTargDelvDateStart as varchar(100),
    @LdryTargDelvDateEnd as varchar(100)
)
RETURNS TABLE 
AS
RETURN 
WITH CTE(Name, Date, Weight, Type) AS 
(
    SELECT lmreceive.CustName, 
           lmreceive.LdryTargRecvDate, 
           SUM(lmrecvcart.LdryNettWght) AS Expr1, 
           'S' AS Type 
      FROM linenmaster118.lmreceive AS lmreceive 
     INNER JOIN linenmaster118.lmrecvcart AS lmrecvcart 
             ON lmreceive.RcID = lmrecvcart.RecvRcID
     WHERE lmreceive.LdryCode = @LdryCode
       AND lmreceive.InvtAcctCode = @InvtAcctCode
       AND lmreceive.CustName = @CustName
       AND lmreceive.LdryTargDelvDate BETWEEN @LdryTargDelvDateStart AND @LdryTargDelvDateEnd
     GROUP BY lmreceive.CustName, lmreceive.LdryTargRecvDate
     UNION ALL
    SELECT lmdelivery.CustName, 
           lmdelivery.LdryTargDelvDate, 
           SUM(lmdelvcart.LdryNettWght) AS Expr1, 
           'C' AS Type
      FROM linenmaster118.lmdelivery AS lmdelivery
     INNER JOIN linenmaster118.lmdelvcart AS lmdelvcart 
             ON lmdelivery.RcID = lmdelvcart.DelvRcID
     WHERE lmdelivery.LdryCode = @LdryCode
       AND lmdelivery.InvtAcctCode = @InvtAcctCode
       AND lmdelivery.CustName = @CustName
       AND lmdelivery.LdryTargDelvDate BETWEEN @LdryTargDelvDateStart AND @LdryTargDelvDateEnd
     GROUP BY lmdelivery.CustName, lmdelivery.LdryTargDelvDate
)
SELECT Name, 
       Date, 
       SUM(CASE WHEN Type = 'S' THEN Weight END) AS SoilWeight, 
       SUM(CASE WHEN Type = 'C' THEN Weight END) AS CleanWeight
  FROM CTE AS CTE_1
 GROUP BY Name, Date
;
GO

You have a couple of typos in the SQL in your question, I think I've corrected them.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

I am going to take a little different approach - the CTE should define the detail data and then we can summarize that detail data. There really isn't any need to SUM the SUM's here:

 CREATE FUNCTION myFunctioname (
        @LdryCode   varchar(100)
      , @AcctCode   varchar(100)
      , @CustName   varchar(100)
      , @StartDate  varchar(100)
      , @EndDate    varchar(100)
        )
RETURNS TABLE 
     AS
 RETURN 

   WITH Details (CustName, TargetDate, LdryCode, AcctCode, Weight, Type) 
     AS (
 SELECT lmr.CustName
      , lmr.LdryTargRecvDate
      , lmr.LdryCode
      , lmr.InvtAcctCode
      , lmr.CustName
      , lmrc.LdryNettWght
      , 'S'
   FROM linenmaster118.lmreceiv             lmr
  INNER JOIN linenmaster118.lmrecvcart      lmrc ON lmrc.RecvRcID = lmr.RcID
  UNION ALL
 SELECT lmd.CustName
      , lmd.LdryTargDelvDate, 
      , lmd.LdryCode
      , lmd.InvtAcctCode
      , lmd.CustName
      , lmdd.LdryNettWght
      , 'C'
   FROM linenmaster118.lmdelivery           lmd
  INNER JOIN linenmaster118.lmdelvcart      lmdd ON lmdd.DelvRcID = lmd.RcID
        )
 SELECT det.CustName
      , det.TargetDate
      , SoilWeight   = SUM(CASE WHEN Type = 'S' THEN det.Weight END)
      , CleanWeight  = SUM(CASE WHEN Type = 'C' THEN det.Weight END)
   FROM Details                             det                                 
  WHERE det.CustName = @CustName
    AND det.LdryCode = @LdryCode
    AND det.AcctCode = @AcctCode
    AND det.TargetDate BETWEEN @StartDate AND @EndDate
  GROUP BY 
        det.CustName
      , det.TargetDate;
     GO

We can then push the criteria to the outer query - and a single GROUP BY on the total details.

One thing to note - the parameters have all been defined as VARCHAR(100). These need to be defined as the actual data types for each column. I suspect the target date columns are actually a numeric value and not a string - so those should be defined appropriately.

If you have a calculation that is used to convert from a date to internal value - you could modify this to perform that calculation and set the function up to accept datetime instead. If those are old style UNIX Epoch:

      , @StartDate     datetime
      , @EndDate       datetime
...
    AND det.TargetDate BETWEEN DATEDIFF(second, '1970', @StartDate) AND DATEDIFF(second, '1970', @EndDate)

If they are new style UNIX timestamps - then see this article: https://www.sqlservercentral.com/articles/convert-unix-timestamps-to-date-times-in-sql-server-part1

This reply was modified 1 year, 1 month ago by Jeffrey Williams.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Tourless SSC Enthusiast Points: 175 More actions · Answer 5

Thank you all. This is great information. However, we have run into a problem... Taking the original code we modified it as follows and at first it worked returning a single customer in a test. But now it returns a boatload of customer and data outside the date range.

We created this...

CREATE FUNCTION getMonthlyCleanLinen (
      @CustName   varchar(128)
     , @StartDate  int
     , @EndDate    int
       )

 

RETURNS TABLE AS

 

RETURN
  WITH Details (CustName, TargetDate, InvtAcctCode, Weight, Type)
    AS (

 

SELECT lmr.CustName
     , lmr.LdryTargRecvDate
     , lmr.InvtAcctCode
     , lmrc.LdryNettWght
     , 'S'
  FROM linenmaster118.lmreceive             lmr
INNER JOIN linenmaster118.lmrecvcart      lmrc ON lmrc.RecvRcID = lmr.RcID

 

UNION ALL

 

SELECT lmd.CustName
     , lmd.LdryTargDelvDate
     , lmd.InvtAcctCode
     , lmdd.LdryNettWght
     , 'C'
  FROM linenmaster118.lmdelivery           lmd
INNER JOIN linenmaster118.lmdelvcart      lmdd ON lmdd.DelvRcID = lmd.RcID
       )

 

SELECT det.CustName
     , det.TargetDate
     , SoilWeight   = SUM(CASE WHEN Type = 'S' THEN det.Weight END)
     , CleanWeight  = SUM(CASE WHEN Type = 'C' THEN det.Weight END)
FROM Details                            det                              

 

WHERE det.CustName = @CustName
   AND det.InvtAcctCode = 'RENT HOSP' OR det.InvtAcctCode = 'RENT NH'
   AND det.TargetDate BETWEEN @StartDate AND @EndDate

 

GROUP BY
      det.CustName
     , det.TargetDate;

 

GO

and run this...

SELECT *FROM getMonthlyCleanLinen('Brinton Manor Nursing & Rehab Center', '1291183201', '1672552801')

and get this...

2023-10-11_12-59-16

not sure where the disconnect is. anyone have any ideas? -thanks.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

You have OR in there. Needs to be inside parentheses.

WHERE det.CustName = @CustName
   AND ( det.InvtAcctCode = 'RENT HOSP' OR det.InvtAcctCode = 'RENT NH' )
   AND det.TargetDate BETWEEN @StartDate AND @EndDate

This reply was modified 1 year, 1 month ago by Jeffrey Williams. Reason: Added code sample

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs