Fetching previous Datas by using another table value

Question

1.00/5 (1 vote)

See more:

Hi,
i am going to fetch data in one table it contains the current_month values as 2013.007. In another table hold the timeid for this currentmonth. now i want to choose the time id for 2013.007 to before 6 value that means(2012.001 t0 2013.007). In mbrVersion table only contains the one curretn month. in Time table only contain the id for 7data how can i get this id?

here i have the code for only 2013.007 its example value only. i want to bind the value?

SQL

SELECT SUM(SIGNEDDATA)
FROM FACPLAN
WHERE TIMEID IN
(SELECT TIMEID FROM Time
WHERE ID IN
(SELECT CURRENT_MONTH FROM mbrVERSION WHERE CURRENT_MONTH!=''))

Posted 12-Mar-14 20:05pm

vinodhini sekar

Add a Solution

Comments

Raja Sekhar S 13-Mar-14 2:25am

Provide table Structure with some sample data..

vinodhini sekar 13-Mar-14 2:40am

In version table
versionid currentmonth
A.1012 -
B.2013 2013.007

In Time table
ID timeid period

2013.001 5476587 001
2013.002 7856786 002
2013.003 7856786 003
2013.004 7856786 004
2013.005 78546786 005
2013.006 78546786 006
2013.007 78456786 007

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

vinodhini sekar · Accepted Answer · 2014-03-12T23:25:00

SQL

DECLARE @CURRENT_MONTH VARCHAR(20)
  DECLARE @YEAR CHAR(4)
  DECLARE @PERIOD NUMERIC(3)

SET @CURRENT_MONTH= (SELECT CURRENT_MOTH FROM dbo.VERSION WHERE CURRENT_MONTH!='')
  PRINT @CURRENT_MONTH
 SET @YEAR=(select substring(@CURRENT_MONTH, 0, 5))
 SET @PERIOD=(SELECT SUBSTRING(@CURRENT_MONTH,6,9))


  SELECT SUM(SIGNEDDATA) FROM dbo.PLANNING
  WHERE
TIMEID IN
  ( SELECT TIMEID FROM dbo.mbrTime
   WHERE
   ID BETWEEN
   @YEAR+'.'+(SELECT RIGHT ('000'+ CAST (@PERIOD-6 AS varchar), 3))
   AND
    @YEAR +'.'+(SELECT RIGHT ('000'+ CAST (@PERIOD AS varchar), 3)))