I WANT TO BE ABLE TO CREATE A VIEW TABLE THAT AM ABLE VIEW DEPT SALES PER BATCH AS WHEN THEY ARE PRINTED ON THE ZREPORT
I WANT A TABLE OR ABLE TO EXPORT AS A CSV FILE
TO SHOW SALES AMOUNT THAT ARE LINKED TO BATCHES
A SQL COMMAND THAT WILL HELP ME
*This post is locked for comments
SQL Command: View created from above query supplied by Archelle Marcito
Create View [YourViewNameHere] AS
SELECT b.BatchNumber ,
d.Name AS Department ,
SUM(te.Price * te.Quantity) AS TotalSales
FROM dbo.[Transaction] t
LEFT JOIN dbo.TransactionEntry te ON te.TransactionNumber = t.TransactionNumber
INNER JOIN dbo.Batch b ON b.BatchNumber = t.BatchNumber
INNER JOIN Item i ON i.ID = te.ItemID
LEFT JOIN dbo.Department d ON d.ID = i.DepartmentID
GROUP BY b.BatchNumber ,
d.Name
Had to leave out where clause for time, but you could probably add this to the view if required. Also the order by clause is not used or needed in a view. You would use the order by when querying the view.
Is it possible to create as a view table so I don't have exec the command every time like it create the last of depart along the the batch number attach to it
Hello here's a simple stored procedure i created for u that displays BatchNumber, department and its equivalent sales.
Execute this first:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE DepartmentSalesPerBatch
@fromdate AS DATETIME ,
@todate AS DATETIME
AS
BEGIN
SET NOCOUNT ON
SET @fromdate = CAST(@fromdate AS DATETIME)
SET @todate = DATEADD(hour, 24, @todate)
SET @todate = DATEADD(SECOND, -1, @todate)
SELECT b.BatchNumber ,
d.Name AS Department ,
SUM(te.Price * te.Quantity) AS TotalSales
FROM dbo.[Transaction] t
LEFT JOIN dbo.TransactionEntry te ON te.TransactionNumber = t.TransactionNumber
INNER JOIN dbo.Batch b ON b.BatchNumber = t.BatchNumber
INNER JOIN Item i ON i.ID = te.ItemID
LEFT JOIN dbo.Department d ON d.ID = i.DepartmentID
WHERE t.Time > = @fromdate
AND t.[Time] <= @todate
GROUP BY b.BatchNumber ,
d.Name
ORDER BY b.BatchNumber ,
d.Name
END
GO
RUN THE ABOVE QUERY and USE THE FOLLOWING SYNTAX:
exec DepartmentSalesPerBatch
@fromdate = 'key in ur date from here'
@todate= 'key in ur to date here'
HOPE THIS HELPS. THANKS.
STOREDB
store db
So u are looking for an sqlcommand to view department sales per batch? Is this command to run in HQ database or store db instead?
email me at edsrmsanswers@gmail.com. We can discuss getting this create for you.
please don't type in all CAPS. Its considered yelling.
#1
André Arnaud de Cal...
291,431
Super User 2024 Season 2
#2
Martin Dráb
230,503
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
