quote:

---

Originally posted by visakh16

wow...view with name as current date..thats interesting
Can i ask reason for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

.....
WHERE
   DateColumn = '20130225'
   AND VendorIDColumn = 245

quote:

---

Originally posted by James K

I am not familiar with Coldfusion, so this may or may not be applicable. In SQL server, the recommended way would be to query the table (or create a view and query it) that has two additional columns - 1. Date, 2. Vendor ID. Once you have that, you would query against that view/table using a where clause such as this:

.....
WHERE
   DateColumn = '20130225'
   AND VendorIDColumn = 245

Creating a view for each vendor and each date is not scalable and not a recommended practice, especially so for the dates.

---

DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8),GETDATE(),112)) + '
as SELECT col1, col2 FROM YourTable';
EXEC (@sql);

quote:

---

Originally posted by taunt

Well I was trying to do a sale report for the buyer that would list it's by time between date received and last sold date. The issue was in Coldfusion it has issues with the coding that I was using (in sql it would add fine in cf it would be off). So I thought to do a create list view in Coldfusion, but I need it to create a view named with the vender number and date made. Then it wouldn't have an issue if multiple ones were made in a day. For instance it would make it to look like view would be titled 092252013 for one vendor and another one would be 2452252013.

quote:

---

Originally posted by visakh16

wow...view with name as current date..thats interesting
Can i ask reason for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

---

GO
DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8),GETDATE(),112)) + '
as SELECT  DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) / 365 AS Years, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) % 365 / 30 AS Months, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) 
            % 365 % 30 AS Days, Items.UPC, Items.buyqty, Items.Attribute1, Items.ProductName, Items.Attribute2, Items.LastOrdDate, Items.ReceiptDate, Items.LAST_SOLD, Items.VendorID, 
            Items.SKU AS Expr1, Items.StockQty, Items.UsedQty, RTotals.NSTTL
FROM    Products INNER JOIN
            RTotals ON Items.PID = RTotals.PID
WHERE  (Items.VendorID = '245') AND (Items.buyqty > 0);
EXEC (@sql);
go

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '245'.
Msg 105, Level 15, State 1, Line 8
Unclosed quotation mark after the character string ') AND (Products.buyqty > 0);
EXEC (@sql);
go
'.

quote:

---

Originally posted by James K

You can do that, like this:

DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8),GETDATE(),112)) + '
as SELECT col1, col2 FROM YourTable';
EXEC (@sql);

---

DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8), GETDATE(), 112)) + 
    '
as SELECT  DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) / 365 AS Years, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) % 365 / 30 AS Months, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) 
            % 365 % 30 AS Days, Items.UPC, Items.buyqty, Items.Attribute1, Items.ProductName, Items.Attribute2, Items.LastOrdDate, Items.ReceiptDate, Items.LAST_SOLD, Items.VendorID, 
            Items.SKU AS Expr1, Items.StockQty, Items.UsedQty, RTotals.NSTTL
FROM    Products INNER JOIN
            RTotals ON Items.PID = RTotals.PID
WHERE  (Items.VendorID = ''245'') AND (Items.buyqty > 0);'
EXEC (@sql);
GO

DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8), GETDATE(), 112)) + 
    '
as SELECT  DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) / 365 AS Years, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) % 365 / 30 AS Months, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) 
            % 365 % 30 AS Days, Items.UPC, Items.buyqty, Items.Attribute1, Items.ProductName, Items.Attribute2, Items.LastOrdDate, Items.ReceiptDate, Items.LAST_SOLD, Items.VendorID, 
            Items.SKU AS Expr1, Items.StockQty, Items.UsedQty, RTotals.NSTTL
FROM    Products INNER JOIN
            RTotals ON Items.PID = RTotals.PID
WHERE  (Items.VendorID = ''245'') AND (Items.buyqty > 0);'
EXEC (@sql);
GO

quote:

---

Originally posted by James K

DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8), GETDATE(), 112)) + 
    '
as SELECT  DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) / 365 AS Years, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) % 365 / 30 AS Months, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) 
            % 365 % 30 AS Days, Items.UPC, Items.buyqty, Items.Attribute1, Items.ProductName, Items.Attribute2, Items.LastOrdDate, Items.ReceiptDate, Items.LAST_SOLD, Items.VendorID, 
            Items.SKU AS Expr1, Items.StockQty, Items.UsedQty, RTotals.NSTTL
FROM    Products INNER JOIN
            RTotals ON Items.PID = RTotals.PID
WHERE  (Items.VendorID = ''245'') AND (Items.buyqty > 0);'
EXEC (@sql);
GO

---