Click here to Skip to main content
11,715,160 members (65,831 online)
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 20:01pm
Edited 24-Oct-12 1: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 Sergey Alexandrovich Kryukov 495
1 Richard MacCutchan 275
2 Maciej Los 259
3 ManasKumarM 155
4 Frankie-C 125
0 Sergey Alexandrovich Kryukov 775
1 CHill60 415
2 Richard MacCutchan 405
3 Maciej Los 389
4 OriginalGriff 320

Advertise | Privacy | Mobile
Web04 | 2.8.150901.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