Devam Ediyor

MS SQL SERVER - STORED PROCEDURE

[url removed, login to view] Procedure: getOutStanding

TABLENAME : MESSACCOUNTS,CUSTOMERS

INPUT : CUSTOMERID

RETURN VALUE : AMOUNT (CREDIT - DEBIT)

[url removed, login to view] Procedure : CreditPurchase Report

TABLE : CREDITPURCHASE,LABELS

Refer the Excel sheet attached

[url removed, login to view] TripSheet

TABLES : MEALLOG,DRIVERS,CUSTOMERS

------------------------------------------------------------------------------------------------

INPUT : TRIPSHEET-DATE, DRIVERID,

STEP1: SELECT THE DISTINCT COMPANY NAME FROM CUSTOMERS TABLE

STEP2: LOOP OVER THE DISTINCT COMPANY ID AND SELECT FROM MEALLOG TABLE

AND COUNT THE EACH different MEALTYPE( MOTTA,MV,BARIK,BV,CHAPATI,BVC,MVC AND OTHER)

WITH FOLLOWING CONDITIONS :

[url removed, login to view] DAY SHOULD NOT BE FRIDAY - DON'T EXECUTE THIS QUERY AND RETURN NULL

[url removed, login to view] ONLY RECORDS IN MEALLOG TABLE WHERE ACTIVE SHOULD = 1

[url removed, login to view] THE MEALLOG DAYS = 5 THEN CURRENT DAY SHOULD NOT BE IN WEEKEND1 OR WEEKEND2 FIELD

[url removed, login to view] THE MEALLOG DAYS = 6 THEN CURRENT DAY SHOULD NOT BE IN WEEKEND1

5 IF THE MEALLOG DAYS = 7 THEN IGNORE THE WEEK-END

[url removed, login to view] TRIPSHEET DATE SHOULD BE WITHIN THE START-DATE AND END-DATE OF THE MEALLOG TABLE

[url removed, login to view] ACTIVE FIELD SHOULD BE EQUAL TO 1

[url removed, login to view] FIELD CONTRACTTYPE SHOULD CONTAIN THE LETTER 'L'

--------------------------------------------------------------------------------------------------

INSERT THE RESULT INTO A NEW TABLE CALL IT AS LUNCH-TRIPSHEET WITH FOLLOWING FIELDS

--------------------------------------------------------------------------------------------------

SHEET-ID - AUTONUMBER (AUTOINCREMENT)

TRIPSHEETDATE - DATETIME- INPUT VALUE OF TRIPSHEET-DATE

DRIVERNAME - VARCHAR(50) GET THE DRIVERNAME FROM DRIVERS TABLE WITH DRIVERID INPUT

COMPANYNAME - VARCHAR(70) COMPANYNAME

MOTTA - INTEGER

MV - INTEGER

BARIK - INTEGER

BV - INTEGER

CHAPATI - INTEGER

BVC - INTEGER

MVC - INTEGER

OTHER - INTEGER

REFER THE ATTACHED TRIPSHEET HTML FOR REFERENCE

1=SUN,2=MON,3=TUE,4=WED,5=THU,6=FRI,7=SAT

--------------------------------------------------------------------------------------------------

[url removed, login to view] Tripsheet

TABLES : MEALLOG,DRIVERS,CUSTOMERS

--------------------------------------------------------------------------------------------------

INPUT : TRIPSHEET-DATE, DRIVERID,

STEP1: SELECT THE DISTINCT COMPANY NAME FROM CUSTOMERS TABLE

STEP2: LOOP OVER THE DISTINCT COMPANY ID AND SELECT FROM MEALLOG TABLE

AND COUNT THE EACH different FMID(FRIDAYMEALID FROM LOOKUP TABLE FRIDAYMEAL)

WITH FOLLOWING CONDITIONS :

[url removed, login to view] DAY SHOULD FRIDAY - DON'T EXECUTE THIS QUERY FOR OTHER DAYS RETURN NOTHING

[url removed, login to view] ONLY RECORDS IN MEALLOG TABLE WHERE ACTIVE SHOULD = 1

[url removed, login to view] THE MEALLOG DAYS = 5 THEN (WEEKEND1 OR WEEKEND2) SHOULD NOT BE FRIDAY

[url removed, login to view] THE MEALLOG DAYS = 6 THEN WEEKEND1 SHOULD NOT BE FRIDAY

5 IF THE MEALLOG DAYS = 7 THEN NEED TO SELECT IT -I MEAN COUNT IT

[url removed, login to view] TRIPSHEET DATE SHOULD BE WITHIN THE START-DATE AND END-DATE OF THE MEALLOG TABLE

[url removed, login to view] ACTIVE FIELD SHOULD BE EQUAL TO 1

INSERT THE RESULT INTO A NEW TABLE CALL IT AS FRIDAYMEAL-TRIPSHEET WITH FOLLOWING FIELDS

--------------------------------------------------------------------------------------------------

SHEET-ID - AUTONUMBER (AUTOINCREMENT)

TRIPSHEETDATE - DATETIME- INPUT VALUE OF TRIPSHEET-DATE

DRIVERNAME - VARCHAR(50) GET THE DRIVERNAME FROM DRIVERS TABLE WITH DRIVERID INPUT

COMPANYNAME - VARCHAR(70) COMPANYNAME

MUTTONBRIYANI - INTEGER

CHICKENBRIYANI - INTEGER

FISHBRIYANI - INTEGER

VEGBRIYANI - INTEGER

--------------------------------------------------------------------------------------------------

Beceriler: SQL

Daha fazlasını görün: sql server stored procedure field loop, tue-sat, sql get date, ms lookup, drivers select, sql stored procedure loop query, t-sql with, t-sql in, c sat, a&t labels, t-sql;, stored, sql l, SAT, report sql, null, mvc sql, mvc c sql, mv, motta, insert date, excel with sql server, excel to sql, sql query loop, mvc lookup

İşveren Hakkında:
( 5 değerlendirme ) Abudhabi, United Arab Emirates

Proje NO: #713848

Seçilen:

ITStone

Hi, As we discussed. Thanks

1 gün içinde 30$ USD
(2 Değerlendirme)
1.2

6 freelancer bu iş için ortalamada 51$ teklif veriyor

JohnNuman

Kindly review PM for details. Thanks.

1 gün içinde 68$ USD
(10 Değerlendirme)
6.9
hcy0212

Please see PM.

in 2 gün içinde50$ USD
(4 Değerlendirme)
3.3
BSoftTech

I'm an expert in stored procedures. I can create these procedures. Thanks, John

in 2 gün içinde100$ USD
(0 Değerlendirme)
0.0
JJKewer

can start right now

1 gün içinde 30$ USD
(0 Değerlendirme)
0.0
manojdotnet

Hi, I am interested to work on this project . I message you more details, Please check your PMB. Thanks, Manoj.

1 gün içinde 30$ USD
(1 Değerlendirme)
0.0