Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
I have a query in sql server 2005 to write a SP , pls help me to solve the same , Thanks
 

 
1. Select all the market codes from TBL_MARKET table
2. For each market code, select the Order Number and Date from Order Master table for all the Orders where date is same as parameter date
3. If there are no Order for a particular marketcode, select the next market from the market master table and start again from step 1. Otherwise, insert the Date and market code in the Tax Register Master table as per below mentioned logic
 
I want to create a procedure (SP) based on the follwoing parameter on the tables as , pls help me in this
 
There is OrderTable and Orderdetail and OrderRegsiter Tables and OrderCheck
 
OrderCheck table has follwowing column
Order_code | Order_name | region_code | VAT_NO | Order_active | userId
 
OrderTable is the master table having following coloumn
 
Order_number | Order_code | Order_number | Order_date | Order_status |
 

This procedure will populate the OrderRegsiter tables ( OrderRegsiter and Orderdetail) for all the Orders , which are generated on the particular date
 
parameter as
 
1. Order Code: Market for which the Order register is generated. The default value for the parameter will be -1, which means that the register will be generated for all the markets.
2. Date: Date for which the Order will be selected for the OrderRegister table
 

steps will be to ceate the stored proc as
 
1. Select all the Order codes(Order_code) from OrderCheck table
2. For each Order code, select the Order number and Date from Order Master table (Ordertable)for all the Order where date is same as parameter date
3. If there are no Order for a particular code , select the next code from the OrderCheck table and start again from step 1. Otherwise, insert the Date and order code in the OrderRegister Master table as per below mentioned logic
 

 
OrderRegsiter table column
a)- Order_id (PK) Surrogate key (running number)
b)- Order_code
c)- ORDER_DATE Date of generation of Tax Register. Available as parameter in the stored procedure
d)- Order_from Select last (max) REPORT_DATE for the market and add one day to it. If there is no last date available for the market then insert 1st Jan as the REPORT_PERIOD_FROM date
e)- Order_to This will be same as ORDER_DATE
Posted 23-Oct-12 21:01pm
lalitkr1.5K
Edited 24-Oct-12 2:08am
v2
Comments
OriginalGriff at 24-Oct-12 3:04am
   
This has a strong homework feel about it.
What have you tried?
Where are you stuck?
lalitkr at 24-Oct-12 10:53am
   
Hi,
 
Check my solution
_Amy at 29-Oct-12 1:37am
   
Where?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,569
1 Sergey Alexandrovich Kryukov 6,168
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 24 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100