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 statement

thanks

View 7 Replies


ADVERTISEMENT

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

Transaction Report By Weekly----Plz Help To Write Query

Nov 30, 2007



Hi Guys,
I am generating Transaction Activity report,which should get data by weekly.Report shold look like this.






W1
W2
W3
W4
W5
W6

OKC
79
38
50
76
35
47

NFL
0
0
45
43
33
28

LA

5

12

10

0

0

10

Total
79
38
95
119
68
75


Iam passing 3 parameters @startdate,@enddate,@Market. when i select one Market(OKC/NFL/LA), report generating properly, but when i passing 3 markets values(OKC,NFL,LA), iam getting wrong report,report format is not correct.I used Cross tab for generating this report.Result lam getting like this.













W37
W38
W39
W40
W41
W42
W43
W44
W45

OKC
80









OKC

38








OKC


95







OKC



119







OKC




68





OKC





75




OKC






74



OKC







70


OKC








59

OKC










OKC










LA










And i wrote query like this,

select m.Market_name as Market,'W'+datename(ww,ut.creation_date) as Week,count(ut.transaction_id) as Count
from POS.DSC_TRANSACTION_STATUS_VL ts inner join POS.DSC_USER_TRANSACTION ut
on ts.transaction_status=ut.transaction_status inner join POS.RETAIL_LOCATION rl
on ut.rl_number=rl.rl_number inner join POS.BILLING_MARKETS bm
on rl.bm_code=bm.bm_code inner join dbo.Market m
on bm.market_id=m.market_id
where (ut.creation_date between @startdate and @enddate) and m.market_name IN(@Market)
group by m.Market_name,ut.creation_date
order by m.Market_name desc

Could you please some one help me to get this correctly.



Thanks in advance


Thanks
San

View 2 Replies View Related

SQL Server 2008 :: MDX Query Filtering - Return Sales For Each Customer For Past 6 Weeks

Feb 4, 2015

I'm having problems filtering in my MDX query.

My requirements are to return the sales for each customer for each store, for the past 6 weeks.

The catch is that I only want those customers which have had sales over 10,000 within the last week.

This is my query:

WITH SET [CustomerList] AS
FILTER(
([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]),
[Measures].[Sales] >= 10000
AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]

[Code] ....

The dates are hard-coded for testing purposes.

View 0 Replies View Related

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

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 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 Display Data At Bottom Of Report By Daily, Weekly, Monthly, SYTD Using Ssrs Report

Dec 14, 2007

hai iam new to ssrs, please help me.


i have student billbale information assume what ever data it. i need to to dispaly total amount for the student at

Bottom Of Report By Daily, Weekly, Monthly, SYTD . take any example, i want to know formula.

thanks to advanced

Jacks v

View 1 Replies View Related

Need To Write A Stored Procedure

May 10, 2007

Hi,I need to write a stored procedure to check if the string = "web_version" exists in another string called FromCode.The stored procedure accepts 2 parameters like this:Create proc [dbo].[spGetPeerFromCode]( @FromCode VARCHAR(50)) (@WebVersionString VARCHAR(50))as   please assist. 

View 1 Replies View Related

How To Write Stored Procedure?

Jan 28, 2008

 
Hi all,
   I want to write a stored procedure for the following,
  The table have the following field,
  CategoryID, CategoryName,                    Parent_categoryID
  123             Kids Dresses                        0
  321             Kids Boys                            123
  322             Kids Baby                            123
  401             Kids Boys school Uniform     321
  501            Kids Boys Formals                321
  541            Kids Baby school Uniform      322
  542            Kids Baby Formals                322
  601            Household Textile                  0
  602           Bathrobe                               601
  603           Carpet                                  601
  604           Table Cloth                           601
 
 From the above example the categoryID acts as a parent_categoryID for some products,
 when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID,
 subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID.
 For example when i pass categoryID as 123 it should return the following subcategoryIDs
 321,322,401,501,541,542 because these are all subcategory of categoryID 123.
 How to write stored procedure for this?
 
Thanks!
 

View 2 Replies View Related

How To Write Stored Procedure

Mar 21, 2007

Hai Friends,
I heard that stored procedures can be written using two servers
how to write stored procedures by using two servers
CAN ANYONE GIVE INFORMATION ABT THIS

Malathi Rao

View 4 Replies View Related

Where Do You Write A Stored Procedure?

Mar 21, 2008

Hi, where do you write an SQL stored procedure?

View 1 Replies View Related

Generating Records Weekly / Bi Weekly Based On The Received Date Field

Feb 18, 2014

I have a query that will generate records monthly based on the number of months that i calculate between two date feilds for a given requestid. How can i use the same query to generate records for weekly and bi weekly based on the receiveddate field that i use in the subtraction for calculating the number of months.

Also when inserting i have been adding a month for every record as i was generating monthly and now i would have to add week and 2 weeks to the receiveddate

SET NOCOUNT ON
GO
declare @num_of_times int
declare @count int
declare @frequency varchar(10)
declare @num_of_times1 int

[Code] ....

View 6 Replies View Related

How To Write Stored Procedure With Two Parameters

Aug 23, 2007

 How to write stored procedure with two parametershow to check those variables containing  values or  empty in SP
 if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one       
Select * from orders  plz write this SP for me thanks  

View 3 Replies View Related

How To Write A Stored Procedure In SQL Server2000?

Jan 28, 2008

 
Hi all,
 
  I want to write a stored procedure for the following,
  The table have the following field,
  CategoryID, CategoryName,                    Parent_categoryID
  123             Kids Dresses                        0
  321             Kids Boys                            123
  322             Kids Baby                            123
  401             Kids Boys school Uniform     321
  501            Kids Boys Formals                321
  541            Kids Baby school Uniform      322
  542            Kids Baby Formals                322
  601            Household Textile                  0
  602           Bathrobe                               601
  603           Carpet                                  601
  604           Table Cloth                           601
 
 From the above example the categoryID acts as a parent_categoryID for some products,
 when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID,
 subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID.
 For example when i pass categoryID as 123 it should return the following subcategoryIDs
 321,322,401,501,541,542 because these are all subcategory of categoryID 123.
 How to write stored procedure for this?
 
Thanks!

View 6 Replies View Related

Can Anyone Help Me To Write A Stored Procedure For The Following Scenario...

Feb 7, 2008

Hai friends, 
The project i'm working on is an asp.net project with SQL Server 2000 as the database tool.
I've a listbox (multiline selection) in my form, whose selected values will be concatenated and sent to the server as comma separated numbers (Fro ex: 34,67,23,60).Also, the column in the table at the back end contains comma separated numbers (For ex: 1,3,7,34,23).
What i need to do is -
1. Select the rows in the table where the latter example has atleast one value of the former example (In the above ex: 34 and 23).
2. Check the text value of these numbers in another table (master table) and populate the text value of these numbers in a comma separated format in a grid in the front end.
 I've programmed a procedure for this. but it takes more than 2 minutes to return the result. Also the table has over 20,000 rows and performance should be kept in mind.
Suggessions on using the front-end (asp.net 2.0) concepts would also be a good help.
 Anybody's helping thought would be greatly appreciated...
Thanks lot...

View 2 Replies View Related

Read / Write Into .doc From SQL Stored Procedure

Jun 25, 2007

I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?



Thank you.

View 1 Replies View Related

Hi I Want To Know How To Write Stored Procedure ..then I Have To Include (IF Condition ) With SP ..

Sep 20, 2007

hi i want to know how to write  stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????

View 5 Replies View Related

Could You Write A Special Stored Procedure For Me In SQL 2005?

Apr 6, 2006

Could you write a special stored procedure for me in SQL 2005?
When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as TagName= SportTagName= NewTagName= Health
I hope there is a stored procedure which can join all TagName into a single string and return,it means when I pass @IDTopic to the stored procedure, it return "Sport,New,Health"
How can write the stored procedure?

View 2 Replies View Related

Stored Procedure To Read And Write Between XML And SQLServer

Mar 28, 2004

Hi All,
I need some help from you experts.

I need to develop something that reads from xml files and writes in to sqlserver, also it should read from SQLServer and writes to xml.

I should be able to give this as a job to exectue daily ,etc.

Can we do this using stored procedures in SQLServer.
Pls paste some sample code, if any or direct me to any url with better info.

Thanks in advance

View 11 Replies View Related

Write To A Text File From A Stored Procedure

Oct 19, 2011

I have a sp which saves the necessary information regarding the status of action(whether success or failure, rows affected etc) to a log table say( StatusLog )After this, I was sending a database mail with information taken from the log table via sp_send_dbmail. Now I would like to write the status information to a 'txt' file instead of sending via mail.How can I write to a text file from a stored procedure in ms sql server 2005?

View 6 Replies View Related

Write An Xml File With A Stored Procedure RESOLVED

Sep 22, 2006

Can you, and if yes, how do you, write an xml file using a stored procedure. For example the sp below writes this new record to a sql table. How would I change it to write it to an xml file ?

CREATE Procedure [spOB_AddtblOB_Properties]

@strPropertyRef varchar (100),
@strRouteNo numeric,
@strAdd1 nvarchar(1000),
@strPostcode nvarchar(10)

as

Insert into tblOB_Properties (
PR_PropertyRef,
PR_RouteNo,
PR_Add1,
PR_Postcode)

values(
@strPropertyRef,
@strRouteNo,
@strAdd1,
@strPostcode)
GO

View 1 Replies View Related

Is It Possible To Write Clr Stored Procedure With Out Using Adomd Server.

Dec 5, 2006

hi,

I need to write a clr stored prodeure.i have to call that stored procedure from my prediction query.My Application is going to make use of that query to get the prediction output.

Is it possible to write clr stored procedure with out using adomd server.How?

For example:

In My Assembly i am having a function PredictPerformance(),Which is having my DMX Query.I need to execute that function from my analysis service by calling like this,

select aly. PredictPerformance() FROM [Model].

how to do this?

View 1 Replies View Related

Could You Write A Special Stored Procedure For Me In SQL 2005?

Aug 19, 2007



When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as
TagName= http://www.hothelpdesk.com
TagName= http://www.hellocw.com
TagName= http://www.supercoolbookmark.com


I hope there is a stored procedure which can join all TagName into a single string and return,
it means when I pass @IDTopic to the stored procedure, it return "http://www.hothelpdesk.com, http://www.hellocw.com, http://www.supercoolbookmark.com"

How can write the stored procedure?

View 1 Replies View Related

Reg: Weekly - Monthly Report

Apr 5, 2008

Hi All,

I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.

Input values:

Start Date: 2/17/2008
End Date : 5/8/2008

Output Format:

2/17/08 - 2/29/08 (Partial Month) 12
3/1/08 - 3/31/08 (Full month) 0
4/1/08 - 4/30/08 (Full month) 22
5/1/08 - 5/8/08 (Full month) 10

I want the above format of the monthly report. I really could use some help on this. thanks.

~ Maran

Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028

View 3 Replies View Related

How Should I Write Stored Procedure And Pass It Into Sql Server Express?

Feb 28, 2008

 Hi i m using vwd2005 express and sql express.1. I want to write stored procedure in sql express using sql server management studio express. I m not sure where to write and how to execute the stored procedure in sql server management studio express. Can any one explain the steps required to achieve this? 2.And what is the difference between creating database using:-       a. right click the project in solution explorer-> add new item->SQl database(creating .mdf file)and b.clicking a database explorer->data connection->Add connection ....       thanks.  jack.  

View 4 Replies View Related

How To Write Stored Procedure To Deal With This Kind Of Question?

Sep 13, 2005

I can get following data information from table1Name                        DescriptionA                              AviationA01                           Aviation GeneralCA01                        CapitalCA01-006                 NEW CTB SignageA02                           CapitalCA05-005                  East End RoadwayCA05-006                 Modify RoadHow do I write stored procedure to get following results based on the data from table1Code                        Name                        Description                        Level  1                               A                              Aviation                                    1  1.01                         A01                           Aviation General                        2  1.01.01                    CA01                        Capital                                       3  1.01.01.006            CA01-006                 NEW CTB Signage                    4  1.02                          A02                           Capital                                      2  1.02.01                    CA05-005                  East End Roadway                    3  1.02.01.006             CA05-006                 Modify Road                              4Thank a lot!

View 1 Replies View Related

T-SQL (SS2K8) :: Write A Stored Procedure That Will Be Used Registered Server?

Mar 14, 2014

Is it possible to write a stored procedure that will be used registered server?

For example I have registered server named 'Test Servers' and I want to use it in stored procedure

View 3 Replies View Related

SQL 2012 :: How To Write Insert Into With Select And Where In Stored Procedure

Sep 25, 2015

I am trying to write a stored procedure that is an INSERT INTO <sql table> FROM <other sql tables> WHERE <where clause>.

I need to insert 3 columns from multiples SQL tables into one SQL table. Here is my code snippet:

ALTER PROCEDURE [dbo].[sp_insert_test]
@MID INT OUTPUT,
@CIDINT OUTPUT,
@MemberNVARCHAR OUTPUT

[Code] ....

i cannot pass any of the values to the tblVotings table and not sure how to enter the @MID, @CID and @Member to the INSERT INTO statement.

View 7 Replies View Related

How To Write One Stored Procedure For A (same Column )present In 20 Tables

Mar 8, 2006

hi,i am a learner of ms -sql server 2000, i had a doubt in storedproceduressuppose i have a data base having 20 tables, all the tables have acolumn named--DATEcan we write a store procdure to find out the data ---i mean i wantthe data enteredbetween two days ---- if i call the stored procedure in any one of thetable i need to get the answerpls help me how to write the stored proceduresatishkumar.g

View 2 Replies View Related

Write A Stored Procedure Based On Recursive Data.

Feb 25, 2008

Hello, I am hoping someone can help me in this. I am looking to write a stored procedure that will return the heirarchy of an organization. I will display how the heirarchy might look and then list the tables involved.

John Smith

- Jacob Jones
- Lisa Thompson
- Samuel Barber

- Paul Smith
- John Jackson

Ok, so Jacob, Lisa, an Samuel report up to John Smith. Paul and John Jackson report up to Samuel Barber.

Here are the tables:

Users holds the user_id, first_name, last_name, and reports_to_user_id.
User_Roles holds the user_id, role_type_id
Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example. In addition, Role_Types also has ranking which must be taken into consideration as well. 1 being the top rank and 9 being the lowest.

Thanks very much in advance,
Saied

View 12 Replies View Related

Transact SQL :: Write Parameter Stored Procedure In Server?

Aug 4, 2015

I am having table on which i want to fire a query on.

table structure is as follows :

Table1 

Table1ID    bigint PK
City  nvarchar(10)
FirstName  nvarchar(10)
LastName  nvarchar(10)
MiddleName  nvarchar(10)
State  nvarchar(10)
FirstName  nvarchar(10)
LastName  nvarchar(10)
LLDCode     nvarchar(20)
MMDCode    nvarchar(20)
LastModified    datetime
CreatedDateTime       datetime
LastUpdatedDateTime       datetime
SSN#  nvarchar(20)

I have to write a parameterised stored procedure where multiple values will be pass as input to SP.

E:g:  1) SSN# if no record found by SSN# then 2) Find by LLDCode OR MMDCode

This logic can be implemented using UNION to join output of 2 queries and selecting by LastUpdatedDateTime this query will return the Table1ID.

Now we will fire the query on table2 where this Table1ID as FK in Table2 and we will return the filed SSXML from Table2.How to do this ?

Table2 structure.

Table2ID PK
Table1ID FK
Name
Department
SSXML

View 9 Replies View Related







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