URGENT PLEASE..qty display based on dATE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

URGENT PLEASE..qty display based on dATE

Author

Topic

challa_praveena
Starting Member

7 Posts

Posted - 2004-05-12 : 01:02:50

Hi guys,

this is very urgent -any help is gr8tly appreciated.

i Have an sql statement as follows
----------------------------------------------

SELECT
mrp.Week_Begin_Date,
MRP.Qty,
FROM
FCST.dbo.Planning_Out_MRP_vw as MRP
inner join DM_Fin_1.dbo.Time_Day_Long as TDL on TDL.Day_Date = mrp.week_begin_date
WHERE
(TDL.Day_Date between dateadd("d",-117, getDate()) and dateadd("d", +133 , getdate())) and
Prod_Part.Part_No=MRP.Prod and
MRP.scenario in ('SupplyCommit') and
year(mrp.week_begin_date) = year(getdate())

my condition is something like this
--------------------------------------------
when MRP.version ='CURRENT' and mrp.week_begin_date > CURRENTWEEK then
mrp.week_begin_date - 1 WEEK

i HAVE 2 CONDITIONS TO SATISY AND BASED ON THAT I NEED TO DISPLAY MY QUANTITY DATA
I HAVE VERSIONS IN MY TABLE
HOW TO PUT THESE CONDITIONS IN A SELECT CASE STATEMENT ????????????????
--------------------------------------------------------------------------------------------------------------------------------

1) VERSION="CURRENT"

(WHEN VERSION IS CURRENT AND MY WEEK_BEGIN_dATE > CURRENTWEEK) THEN

I SHOULD NOT DISPLAY MY 1 WEEK PREVIOUS -RELATED QUANTITY.

2) VERSION="PRIOR"

(WHEN VERSION IS "PRIOR" AND MY WEEK_BEGIN_dATE > CURRENTWEEK) THEN

I SHOULD NOT DISPLAY MY 2 WEEKS PREVIOUS RELATED QUANTITY.

3) VERSION="VER20040428"

(WHEN A VERSION IS HAVING DATE IN IT -THEN I SHOULD PICK THAT DATE AND COMPARE WITH
WEEK_BEGIN_dATE > 2004/04/28 THEN

I SHOULD NOT DISPLAY QUANTITIES OF 1 WEEK PRIOR TO THIS DATE.

4) LIKE THIS I COULD HAVE MULTIPLE VERSION WITH DATES AS ABOVE -WHAT WOULD I NEED TO DO THEN?

CAN ANYONE HELP ME.

ITS URGENT PLEASE HELP

THANK U
PIVI

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-12 : 12:55:31

If you provide DDL for your tables and DML for sample data, we could help you better.

Tara

challa_praveena
Starting Member

7 Posts

Posted - 2004-05-12 : 14:07:15

hi,

THIS DATA STRUCTURE IS AS SUCH

TABLE - Planning_Out_MRP_vw as MRP
------------------------------------
FIELDS
-------
PROD (THIS IS THE PRODUCT ID)
WEEK_BEGIN_DATE (THIS IS A WEEK BEGINDATE)
SCENARIO ('SUPPLYCOMMIT' , 'SUPPLYREQUEST')
VERSION (HOLDS VERSION DATA)
QTY (QUANTITY DATA)

SAMPLE DATA
-----------
prod - week_begin_Date - scenario - version - QTY
---------------------------------------------------------------
I3341A0KPB 2004-05-16 SupplyCommit Current .0000
I3341A0KPB 2004-05-16 SupplyCommit PRIOR .0000
I3341A0KPB 2004-05-16 SupplyCommit V040229 .0000
I3341A0KPB 2004-05-16 SupplyCommit V040307 .0000
I3341A0KPB 2004-05-16 SupplyCommit V040314 .0000
I3341A0KPB 2004-05-16 SupplyCommit V040321 .0000
I3341A0KPB 2004-05-16 SupplyCommit V040328 .0000
I3341A0KPB 2004-05-16 SupplyCommit V040404 .0000
I3341A0KPB 2004-05-16 SupplyCommit V040411 .0000
I3341A0KPB 2004-05-16 SupplyCommit V040418 .0000
I3341A0KPB 2004-05-16 SupplyCommit V040425 .0000
I3341A0KPB 2004-05-16 SupplyReq Current 25000.0000
I3341A0KPB 2004-05-16 SupplyReq PRIOR 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040222 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040229 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040307 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040314 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040321 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040328 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040404 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040411 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040418 25000.0000
I3341A0KPB 2004-05-16 SupplyReq V040425 25000.0000
I3341A0KPB 2004-05-23 SupplyCommit Current 78000.0000
I3341A0KPB 2004-05-23 SupplyCommit PRIOR 78000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040215 89000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040222 89000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040229 89000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040307 89000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040314 89000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040321 89000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040328 89000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040404 89000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040411 89000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040418 78000.0000
I3341A0KPB 2004-05-23 SupplyCommit V040425 78000.0000
I3341A0KPB 2004-05-23 SupplyReq Current 25000.0000
I3341A0KPB 2004-05-23 SupplyReq PRIOR 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040222 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040229 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040307 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040314 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040321 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040328 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040404 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040411 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040418 25000.0000
I3341A0KPB 2004-05-23 SupplyReq V040425 25000.0000
I3341A0KPB 2004-05-30 SupplyCommit Current .0000
I3341A0KPB 2004-05-30 SupplyCommit PRIOR .0000
I3341A0KPB 2004-05-30 SupplyCommit V040229 .0000
I3341A0KPB 2004-05-30 SupplyCommit V040307 .0000
I3341A0KPB 2004-05-30 SupplyCommit V040314 .0000
I3341A0KPB 2004-05-30 SupplyCommit V040321 .0000
I3341A0KPB 2004-05-30 SupplyCommit V040328 .0000
I3341A0KPB 2004-05-30 SupplyCommit V040404 .0000
I3341A0KPB 2004-05-30 SupplyCommit V040411 .0000
I3341A0KPB 2004-05-30 SupplyCommit V040418 .0000
I3341A0KPB 2004-05-30 SupplyCommit V040425 .0000
I3341A0KPB 2004-05-30 SupplyReq Current 12500.0000
I3341A0KPB 2004-05-30 SupplyReq PRIOR 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040222 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040229 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040307 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040314 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040321 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040328 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040404 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040411 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040418 12500.0000
I3341A0KPB 2004-05-30 SupplyReq V040425 12500.0000
I3341A0KPB 2004-06-06 SupplyCommit Current .0000
I3341A0KPB 2004-06-06 SupplyCommit PRIOR .0000
I3341A0KPB 2004-06-06 SupplyCommit V040229 .0000
I3341A0KPB 2004-06-06 SupplyCommit V040307 .0000
I3341A0KPB 2004-06-06 SupplyCommit V040314 .0000
I3341A0KPB 2004-06-06 SupplyCommit V040321 .0000
I3341A0KPB 2004-06-06 SupplyCommit V040328 .0000
I3341A0KPB 2004-06-06 SupplyReq Current 12500.0000
I3341A0KPB 2004-06-06 SupplyReq PRIOR 12500.0000
I3341A0KPB 2004-06-06 SupplyReq V040222 12500.0000
I3341A0KPB 2004-06-06 SupplyReq V040229 12500.0000
I3341A0KPB 2004-06-06 SupplyReq V040307 12500.0000
I3341A0KPB 2004-06-06 SupplyReq V040314 12500.0000
I3341A0KPB 2004-06-06 SupplyReq V040321 12500.0000
I3341A0KPB 2004-06-06 SupplyReq V040328 12500.0000
I3341A0KPB 2004-06-06 SupplyReq V040404 12500.0000
I3341A0KPB 2004-06-13 SupplyCommit Current .0000
I3341A0KPB 2004-06-13 SupplyCommit PRIOR .0000
I3341A0KPB 2004-06-13 SupplyCommit V040229 .0000
I3341A0KPB 2004-06-13 SupplyCommit V040307 .0000
I3341A0KPB 2004-06-13 SupplyCommit V040314 .0000
I3341A0KPB 2004-06-13 SupplyCommit V040321 .0000
I3341A0KPB 2004-06-13 SupplyCommit V040328 .0000
I3341A0KPB 2004-06-13 SupplyCommit V040404 .0000
I3341A0KPB 2004-06-13 SupplyCommit V040411 .0000
I3341A0KPB 2004-06-13 SupplyCommit V040418 .0000
I3341A0KPB 2004-06-13 SupplyCommit V040425 .0000
I3341A0KPB 2004-06-13 SupplyReq Current 12500.0000
I3341A0KPB 2004-06-13 SupplyReq PRIOR 12500.0000
I3341A0KPB 2004-06-13 SupplyReq V040222 12500.0000
I3341A0KPB 2004-06-13 SupplyReq V040229 12500.0000
I3341A0KPB 2004-06-13 SupplyReq V040307 12500.0000
I3341A0KPB 2004-06-13 SupplyReq V040314 12500.0000
I3341A0KPB 2004-06-13 SupplyReq V040321 12500.0000
I3341A0KPB 2004-06-13 SupplyReq V040328 12500.0000
I3341A0KPB 2004-07-04 SupplyCommit Current .0000
I3341A0KPB 2004-07-04 SupplyCommit PRIOR .0000
I3341A0KPB 2004-07-04 SupplyCommit V040229 .0000
I3341A0KPB 2004-07-04 SupplyCommit V040307 .0000
I3341A0KPB 2004-07-04 SupplyCommit V040314 .0000
I3341A0KPB 2004-07-04 SupplyCommit V040321 .0000
I3341A0KPB 2004-07-04 SupplyCommit V040328 .0000
I3341A0KPB 2004-07-04 SupplyCommit V040404 .0000
I3341A0KPB 2004-07-04 SupplyCommit V040411 .0000
I3341A0KPB 2004-07-04 SupplyCommit V040418 .0000
I3341A0KPB 2004-07-04 SupplyCommit V040425 .0000
I3341A0KPB 2004-07-04 SupplyReq Current 22892.0259
I3341A0KPB 2004-07-04 SupplyReq PRIOR 22892.0259
I3341A0KPB 2004-07-04 SupplyReq V040222 5000.0000
I3341A0KPB 2004-07-04 SupplyReq V040229 5000.0000
I3341A0KPB 2004-07-04 SupplyReq V040307 5000.0000
I3341A0KPB 2004-07-04 SupplyReq V040314 5000.0000
I3341A0KPB 2004-07-04 SupplyReq V040321 5000.0000
I3341A0KPB 2004-07-04 SupplyReq V040328 5000.0000
I3341A0KPB 2004-07-04 SupplyReq V040404 5000.0000
I3341A0KPB 2004-07-04 SupplyReq V040411 18000.0000
I3341A0KPB 2004-07-04 SupplyReq V040418 22892.0259
I3341A0KPB 2004-07-04 SupplyReq V040425 22892.0259
I3341A0KPB 2004-07-11 SupplyCommit Current .0000
I3341A0KPB 2004-07-11 SupplyCommit PRIOR .0000
I3341A0KPB 2004-07-11 SupplyCommit V040229 .0000
I3341A0KPB 2004-07-11 SupplyCommit V040307 .0000
I3341A0KPB 2004-07-11 SupplyReq Current 22892.0259
I3341A0KPB 2004-07-11 SupplyReq PRIOR 22892.0259
I3341A0KPB 2004-07-11 SupplyReq V040222 5000.0000
I3341A0KPB 2004-07-11 SupplyReq V040229 5000.0000
I3341A0KPB 2004-07-11 SupplyReq V040307 5000.0000
I3341A0KPB 2004-07-11 SupplyReq V040314 5000.0000
I3341A0KPB 2004-07-11 SupplyReq V040321 5000.0000
I3341A0KPB 2004-07-11 SupplyReq V040328 5000.0000
I3341A0KPB 2004-07-11 SupplyReq V040404 5000.0000
I3341A0KPB 2004-07-11 SupplyReq V040411 18000.0000
I3341A0KPB 2004-07-11 SupplyReq V040418 22892.0259
I3341A0KPB 2004-07-11 SupplyReq V040425 22892.0259
I3341A0KPB 2004-07-18 SupplyCommit Current 66750.0000
I3341A0KPB 2004-07-18 SupplyCommit PRIOR 66750.0000
I3341A0KPB 2004-07-18 SupplyCommit V040321 .0000
I3341A0KPB 2004-07-18 SupplyCommit V040328 .0000
I3341A0KPB 2004-07-18 SupplyCommit V040404 .0000
I3341A0KPB 2004-07-18 SupplyCommit V040411 .0000
I3341A0KPB 2004-07-18 SupplyCommit V040418 66750.0000

now the situation is like this -
--------------------------------
this data I am supposed to display in an excel sheet and
if the weekbegindate is equal to currentdate's week then I should not display the quantity data of currentdate week - 1 week
and for this the version ='CURRENT'

for version='PRIOR'
if the weekbegindate is equal to currentdate's week then I should not display the quantity data of currentdate week - 2 weeks

for version='V040418'
this represents date in the version.
in this case I have to retrieve the date from this version and
compare that date with weekbegindate and
should not display the QUANTITES 1 WEEK PREVIOUS TO THIS DATE.

THANK YOU - I HOPE THIS CAN HELP
PLEASE LET ME KNOW IF U HAVE ANY DOUBTS

THANK YOU FOR ALL YOUR TIME AND HELP.

PIVI

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-12 : 14:09:29

Please provide in the form of CREATE TABLE and INSERT INTO. Also provide the expected result set using your sample data.

Tara

challa_praveena
Starting Member

7 Posts

Posted - 2004-05-12 : 14:27:19

THE current RELATED DATA HAS TO BE DISPLAYED ALWAYS - ITS ONLY THE 'prior' RELATED QUATITY SHOULD NOT APPEAR

IS THERE A WAY I COULD SEND U A EXCEL SHEET SAMPLE - COS ITS A BIT CONFUSING TO EXPLAIN.

THANK YOU

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 14:40:54

Are you wanting to display each of the 3 at once or each at separate times?

Daniel
SQL Server DBA

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 14:51:24

Take a look over these two queries. You will need to modify them to work. I'm not sure what else I can do without having your database and not fully understanding what you need to do.

Hope this helps:

-- Only display one set (current, prior, or date specific) at a time.
IF @VERSION = 'CURRENT'
begin
select or insert or update or delete stuff here
end

IF @VERSION = 'PRIOR'
begin
select or insert or update or delete stuff here
end

IF @VERSION like '%[0-9]%'
begin
select or insert or update or delete stuff here
end

-- Display all three sets together
Select mrp.Week_Begin_Date, MRP.Qty, FROM FCST.dbo.Planning_Out_MRP_vw as MRP
where MRP.version ='CURRENT' and mrp.week_begin_date > CURRENTWEEK
UNION
Select mrp.Week_Begin_Date, MRP.Qty, FROM FCST.dbo.Planning_Out_MRP_vw as MRP
where MRP.version ='PRIOR' and mrp.week_begin_date > CURRENTWEEK
UNION
Select mrp.Week_Begin_Date, MRP.Qty, FROM FCST.dbo.Planning_Out_MRP_vw as MRP
where MRP.version like '%[0-9]%' and mrp.week_begin_date > cast(replace(MRP.version, 'ver', '') as datetime)

Daniel
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources