Click here to Skip to main content
11,920,156 members (58,279 online)
Rate this:
Please Sign up or sign in to vote.
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
Edited 24-Oct-12 2:08am
OriginalGriff 24-Oct-12 3:04am
This has a strong homework feel about it.
What have you tried?
Where are you stuck?
lalitkr 24-Oct-12 10:53am

Check my solution
_Amy 29-Oct-12 1:37am

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

  Print Answers RSS
0 OriginalGriff 455
1 phil.o 170
2 CPallini 150
4 F-ES Sitecore 130
0 OriginalGriff 6,778
1 KrunalRohit 4,495
2 Sergey Alexandrovich Kryukov 3,153
3 George Jonsson 2,810
4 Suvendu Shekhar Giri 2,181

Advertise | Privacy | Mobile
Web01 | 2.8.151120.1 | Last Updated 24 Oct 2012
Copyright © CodeProject, 1999-2015
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