Create Stored Procedure To Get Total Last 7 Day Sales For Each Day

Mar 13, 2015

Table name: ONIV
Columns:
Date: DocDate
Sales: DocTotal

I want to populate a line graph which would show the 7 days of the week on X-Axis and the sale on the Y-Axis.

View 2 Replies


ADVERTISEMENT

Report That Shows Customer And Total Sales Who Had Max Sales By Year?

Dec 9, 2014

I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.

Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200

Report:

Cust ID Year Sales
C1 2000 250
C3 2001 200

View 1 Replies View Related

Total Sales By Customer Vs Total Sales

Nov 26, 2007



I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?

Thanks.

View 3 Replies View Related

Stored Procedure For Monthly Sales With Discount

Mar 6, 2014

How to create a stored procedure, or many stored procedures for generating a monthly sales report in our company.

We have two tables:

ITEM_SALES which consists of:
Item_ID
Name
Store
Sales_Date
Sales_Price
Quantity

And then

ITEM_DISCOUNT which consists of:
Item_ID
Name
Store
Sales_Price
Date_From
Date_To

Explanation: After each month, our different stores will send us a report on which discounts they had. These discounts can vary from one, to many days and therefor we have the Date_From, Date_to.

To make this sales report, i need a procedure that first fetches all sales in ITEM_SALES, then checks all the discounts in ITEM_DISCOUNT and overwrites the ones that have similar Item_ID, Name, and Store for a selected period.

Example: So if a item originally had a sales_price on 99,- and then had a discount sales_price to 79,- for 2014-01-02 to 2014-01-10 it has to be overwritten for that period so the report shows the right numbers.

View 6 Replies View Related

Monthly Sales - Three Discounts In Stored Procedure

Jun 25, 2014

I am making a stored procedure for monthly sales. In the stored procedure we have a Discount. This discount can be fetched from three different tables. If the discount is not in id.rabatt, it should fetch from dp.rabatt, if its not there, it should fetch from ds.rabatt. So the first two ones can be empty, while the last one always has a discount..

Im having big trouble designing the WHEN part of the procedure.

CASE (
when
Isnull(id.rabatt, Isnull(u.rabatt, id.rabatt)) then..
when
Isnull(dp.rabatt, Isnull(x.rabatt, id.rabatt)) then..
when
Isnull(ds.rabatt, Isnull(y.rabatt, id.rabatt)) then..
end)
AS 'Discount',

The reason i have to use Isnull is that inside each Discount table, i also have two different discounts, one that lasts forever(2999) and one that have a selected period. Like i show here:

LEFT OUTER JOIN discount AS id
ON id.identifiers = isa.identifiers
AND id.store = BV.name
AND id.from_date <= isa.sales_date
AND id.to_date >= isa.sales_date
AND id.to_date < '2999-01-01'
LEFT OUTER JOIN discount AS u
ON u.identifiers = isa.identifiers
AND u.to_date = '2999-01-01'

The two others tables are designed in similar ways

View 1 Replies View Related

How To Write Such A Stored Procedure To Return Monthly Sales?

Mar 8, 2005

I have a Orders and OrderDetails table having the columns listed below:

Orders (OrderID, OrderDate, CustomerID, ...)
OrderDetails (OrderID, ProductID, UnitPrice, Quantity, ...)

Now, I want to obtain monthly sales from the data in the two tables by passing in a Year parameter. How to develop such a stored procedure? I have no idea where to get started. I was thinking to call a SELECT statement on each month. But, things trouble me are:

1. how to loop through each month to make a SELECT query for each month for a given Year? Use a cursor or what?
2. how to determine month boundary for a given year and construct the where clause on OrderDate using the month boundary for the SELECT query ? What happens if it is a leap year?
3. how to stop processing for the rest of the year when last order month is done?

Any suggestion would be appreciated. Thanks.

View 1 Replies View Related

How To Write A Stored Procedure To Report Weekly Sales For The Last 5 Weeks

Feb 3, 2006

Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks

View 7 Replies View Related

Help: Query To Get Total Last 7 Day Sales For Each Day

Aug 5, 2004

Let say I have this table in MS SQL server
table transaction(date,sales)

how to query to get result like this (date,sales,sum(sales last 7 day))
I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.

note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day

thanks

View 2 Replies View Related

T-SQL (SS2K8) :: How To Show Total Sales

Mar 10, 2014

My Table struct

create table cust
(
cust_id int ,
city varchar(20),
pincode int,
sales int,
latitude float,
longitude float

[code]....

like i wanna display each pincodes how to make a code?

View 6 Replies View Related

Output Every Hour Sales Total By Different Shop

Sep 1, 2014

I desire output by each hour of sales by different shop total with sales_datetime format is 20140831 22:30:xxx.

for example now is 23:05 then desire result is 23:00-23:05 of sale.
-----------------------------------------------------------------
select shopid,qty,amount from sales where sales_datetime>='hh:mm' and sales_datetime<='hh:mm' group by shop

View 4 Replies View Related

Conditional Total In Stored Procedure

Aug 11, 2006

Is there a simple way to do conditional totaling?

I have a table that has incremental values, so the maximum value is the current total, however on occassion the value is reset and the increment begins anew. What I need to do is get the total the maximum value each time it is reset.

ie.

Value Date
----- -----
12 1-1
14 1-2
100 1-8
35 1-10
50 1-12
5 1-15

the total would be 155 (100 + 50 + 5)

View 4 Replies View Related

Stored Procedure + Return Total Row Affected

Jul 7, 2004

Hi..

I have a stored procedure which is used to remove record from database.
Is there anyway to get the total row deleted for each table?


CREATE PROCEDURE dbo.sp_company_delete
(
@Company_Id int,
@Sched int Output,
@Users int Output,
@Asset int Output,
@Fleet int Output

)
AS
SET NOCOUNT OFF;
Begin Transaction delete_company
Select @Sched = COUNT(*) from Scheduler Where Company_id=@Company_Id
Delete From Scheduler Where Company_id=@Company_Id

Select @Users = COUNT(*) From Users Where Company_Id=@Company_Id
Delete From Users Where Company_Id=@Company_Id

Select @Asset = COUNT(*) From Asset Where Company_Id=@Company_Id
Delete From Asset Where Company_Id=@Company_Id
Delete From Fleet Where Company_Id=@Company_Id
Delete From Company Where Company_Id=@Company_Id
Commit Transaction delete_company
GO


Basically I want to get the total row deleted for each table and put it inside output parameters.

I am doing a double job here, first I get the count, put into output parameter, then only I delete the record.

Is there any other way which is more efficient to do this?

I tried using
SET @Sched = Delete From Scheduler Where Company_id=@Company_Id
SET @Users = Delete From Users Where Company_Id=@Company_Id
But doesn't work.

Any suggestion is welcomed.
Thank you in advanced.

View 2 Replies View Related

Running Total Count In Stored Procedure

Jul 20, 2005

in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US

View 1 Replies View Related

Transact SQL :: Stored Procedure For Total Count For New Users

Jul 14, 2015

I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."

But now we have changed the concept to get the ISID (name of the user)  and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .

After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.

if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.

Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).

Here is my sample  code:

I need to sum it up to get the total user's from both the table but I should get 1 instead of 2 

SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like  '%invited%'
and Created_By= 'sam'
+
(select distinct count (*)  from dbo.test1
where invited_by ='sam'

View 8 Replies View Related

SQL Server 2012 :: Sales Over Years - Retrieve Values In Single Query For Multiple Years And Grand Total?

Apr 24, 2015

I need to list customers in a table that represents sales over the years.

I have tables:

Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...

I am using this SQL but it only gets one year:

SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC

I need something like this:

customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$

Is it possible to retrieve these values in a single SQL query for multiple years and grand total?

View 6 Replies View Related

Transact SQL :: Establishing Unique Sales ID To Future Sales IDs From Original Sale

May 19, 2015

i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123',  how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?

Parent:Sale ID #123
Children: Sale ID # 456,
Sale ID #789,
Sale ID #.....

how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?

View 4 Replies View Related

How Can I Create Stored Procedure?

Feb 13, 2008

 forumid    questid   answerid   answer     replyby   replyon    1            1            1            xxxx        aaa        01/01/08(mm/dd/yy)    1            1            2            yyyy        bbb        01/02/08    2            1            1            zzzz        ccc        01/02/08    1            1            3            hhhh         bbb        01/04/08    2            1            2            uuuu         vvv         01/04/08    1            2            1            tttt            ooo        01/05/08suppose i give forumid value=1 i want following answer recent reply and no.of replies  forumid    questid   answerid  no.of.reply  answer     replyby   replyon            1            1            3            3               hhhh        bbb       01/04/08      1            2            1            1               ttt            ooo        01/05/08 

View 14 Replies View Related

Create Stored Procedure

Apr 21, 2004

Hi!

I have a problem. I would like to create a stored procedure from a script file. I must use inparameters as well. I'm using ms Access 2000.

Please help me!

Mike.

View 5 Replies View Related

How To Create Rdl Using Stored Procedure Which Use

Apr 3, 2008

Hi

I am sql server 2005.

how to create rdl using stored procedure which uses parameter


Thank you.

View 1 Replies View Related

Create Stored Procedure

May 6, 2008

Is there a way to create a stored procedure that accepts a column name and returns the name of the tables that have that column name?

View 2 Replies View Related

Create A Stored Procedure

May 7, 2008

I have to create a stored procedure that accepts a column name and returns the name of the tables that have that column name. I'm not sure how to get it to accept the column name. Here's what I have so far:

create proc spTheNameOfTheProcedure
as
select table_name from information_schema.columns
where column_name = XXXXXX

no sure what to put where I have XXXXX

View 2 Replies View Related

Create Stored Procedure

Dec 22, 2007

Say i had a system table in that table i had dbo.user then format (dd,mm,yyyy) after that i had dbo.user_backup_ 10_12_2007,dbo.user_backup_ 10_13_2007,dbo.user_backup_ 10_14_2007,dbo.user_backup_ 10_15_2007,dbo.user_backup_ 10_16_2007

If i use the SQL command
SELECT * FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
AND name not in( SELECT Top 3 name FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
ORDER BY Name DESC)


results would be 10_12_2007,10_13_2007.

So i want to put those results in a backup file and keep top 3 which is 10_14,10_15,10_16. I want to create a store procedure that i can execute and do that for all my tables. this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3

View 1 Replies View Related

How To Create A PDF From A Stored Procedure

Sep 18, 2007

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name whichthat has all these data is called dbo.TestTable
I spent so much time in google and I have not found one simple good example. Can you help me please


Thanks in advance for your help

View 5 Replies View Related

Is It Possible To Create A View From A Stored Procedure

Jan 3, 2008

Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.

View 9 Replies View Related

Need To Create Table With Stored Procedure

Feb 10, 2005

I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure.
This is what I have so far, but it does not allow me to run it.

alter procedure dbo.createNewBUtable
(
@BU as varchar(50)
)
as
set nocount on;
create table @BU
(
BUid varchar(50) primary key,
BUinfo varchar(50)
)

View 4 Replies View Related

Stuck Trying To Create A Stored Procedure

May 3, 2005

Hi

Let me start by saying that SQL is
not my strong suit so please bear with me.

Here's my situation
I'm building a property portfolio system with db structure as follows

tblProperty (The master table - linked 1 to many via propertyID)
tblValuations (columns valuationID, propertyID, currentValue, valuationDate)

For each property in tblProperty the valuations table can hold multiple valuation entries.

I need to have a summary that shows the number of properties a user has
listed and crucially I need to be able to find the latest
valuation for each property & add them together to retrieve the
total value of a users portfolio

I'm totally stumped with this so any help/advice would be gratefully received

Lee

 

View 1 Replies View Related

How To Create An Array In A Stored Procedure

Aug 8, 2000

I just want to Know how to create an Array in a stored procedure. Please can you give the syntax and any Example.

View 1 Replies View Related

Grant &#34;create Stored Procedure&#34;

Feb 24, 2000

Hi!
How do I grant rights to an user for creating stored procedures (using T-SQL)?
Thanks,
Fabio

View 1 Replies View Related

VB5 Create Temporary Stored Procedure

Sep 3, 1998

When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed.
These stored procedures are deleted only when the connection is closed.
My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.

My questions:
Why it occur ?
Wich have created this stored procedure ?
How to avoid it occur ?

The versions are:
SQL Server 6.5
Visual Basic 5.0
SQL Server ODBC Driver 2.65.0240

View 4 Replies View Related

Create Stored Procedure Without Admin Right

Mar 12, 2001

I have no right to connect to my hosting company using MS SQL2000 client.
And I have to use stored procedure, can any body tell me is it possible to
create stored procedure ( I need is complex one with loops of if loops).
If possible please list the syntax.

View 2 Replies View Related

Unable To Create Stored Procedure

Jun 4, 2004

I tried to create a stored procedure but instead of opening up to a new stored procedure it displays an exist stored procedure. I erased the code and typed in my code now i received this error message.

MS SQL-DMO (ODBC SQLState:42000)
Error 2729: Procedure spUpdate_date_time group number 1 already exists
in the database. Choose another procedure name


Does anyone know how I can fix this?

Your help is appreciated

View 2 Replies View Related

How Do You Create A Database Within A Stored Procedure

Apr 20, 2004

I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'

select @iStatus = 0
select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)


create database @dbname
on
( name = @dbname + 'data',
filename = 'F:MSSQLData' + @dbname + 'data.mdf',
size = 10mb,
filegrowth = 10% )
log on
( name = @dbname + 'log',
filename = 'F:MSSQLData' + @dbname + 'log.ldf',
size = 5mb,
filegrowth = 10% )

select @iStatus = @@error
if @iStatus = 0
print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
else
print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)

end

View 3 Replies View Related

T-SQL (SS2K8) :: How To Create Stored Procedure

Oct 16, 2014

yesterday i was trying to create Stored procedure but it fails i don't know why

CREATE proc GetBooksbyBorrowerID @Borrower_id INT
AS
BEGIN
SELECT A.BORROWER_ID ,a.ISBN, b.book_Title,b.LANGUAGE, CONVERT(VARCHAR,a.borrowed_from_date,103)"Borrowed On(dd/mm/yyyy)" FROM borrower_details a, book_mst b
WHERE a.borrower_id=@Borrower_id
AND a.ISBN = b.ISBN
END
GO
EXEC SP_Task1 10001

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved