Click here to Skip to main content
Rate this: bad
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
Edited 24-Oct-12 2:08am
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
Check my solution
_Amy at 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 Dnyaneshwar@Pune 719
1 CHill60 315
2 Sergey Alexandrovich Kryukov 240
3 TheRealSteveJudge 190
4 Peter Leow 160
0 Sergey Alexandrovich Kryukov 9,193
1 OriginalGriff 6,786
2 Peter Leow 4,332
3 Zoltán Zörgő 3,809
4 Richard MacCutchan 2,672

Advertise | Privacy | Mobile
Web01 | 2.8.150128.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