SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Query Help (?) VB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Takeshi19
Starting Member

1 Posts

Posted - 04/25/2014 :  02:16:58  Show Profile  Reply with Quote
Hi guys, I need help.

I have a problem creating a SELECT query which involves getting the FIRST occurrence, the SUMS and the LAST occurrence of a selected row.

Here is a situation. I have two tables A and B which has the following data:

Table A

----------------------------------------------------------------
|Item ID | Item Name | Item Description | Current Quantity |
----------------------------------------------------------------
----1------------Bolts-------------35"----------------20
----2-----------Paint----------GOLD Paint------------30
----3-----------Screws------------30"----------------40
----------------------------------------------------------------

and Table B

--------------------------------------------------------------
|Log ID | Item ID | Beginning | Add | Less | Ending | Date |
--------------------------------------------------------------
---1---------1----------30-------0-----10------20------04-04
---2---------2----------40-------10----10------40------04-04
---3---------3----------50-------5-----10------45------04-04
---4---------1----------20-------0-----0-------20------04-05
---5---------2----------40-------0-----10------30------04-04
---6---------3----------45-------0-----5-------40------04-04
-------------------------------------------------------------

and I have two datetime pickers, one FROM date picker and a TO date picker

If a user selects FROM 04-04 TO 04-05

I want a select statement which will select the Item Name and Desc, the BEGINNING QUANTITY (in reference to the FROM DATE), the SUM of Adds and Less and the ENDING QUANTITY (in reference to the To Date)
and then group by their Item ID

In this case, it will generate a table like this

----------------------------------------------------------
Item Name | Item Desc | Beginning | Add | Less | Ending |
-----------------------------------------------------------
--Bolts---------35"---------30---------0-----10-----20
--Paint-----Gold Paint------40---------10----20-----30
--Screws-------30"---------50---------5-----15-----40
-----------------------------------------------------------

I'm really stumped

I have a query like this

"SELECT A.Item Name, A,Item Desc, B.Beginning=(select B.Beginning from Table B where A.Item ID = B.Item ID AND Date = From Date), SUM(B.ADD), SUM(B.LESS), B.Ending=(select B.Ending from Table B where A.Item ID = B.Item ID and Date = To Date) WHERE A.Item ID = B.Item ID and Date(B.Date)>From Date AND Date(B.Date)< To Date group by B.Item ID order by A.Item Name, Item Desc"

My query returns the right Item Name, Desc, the Sum of Add and Less

My problem is that I can't get the value of my beginning and ending.

I think I have a problem in A.Item ID = B.Item ID. As I tried a fixed value condition, (B.Item ID = 2), it will get the right result), I'm guessing the program is not getting A.Item ID.

Any suggestions pls?

Thank you so much in advance!

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 04/25/2014 :  05:49:51  Show Profile  Reply with Quote
Try This...



-------------------------------------------------------------------------------------------------------
--Table Creation 
-------------------------------------------------------------------------------------------------------
CREATE TABLE TableA(ItemID INT, ItemName VARCHAR(100),ItemDescription  VARCHAR(100),CurrentQuantity INT)
CREATE TABLE TableB(LogID INT,ItemID INT,Beginning INT,[Add] INT,Less INT,Ending INT,[Date] DATE)
INSERT INTO  TableA VALUES (1,'Bolts','35"',20)
			  ,(2,'Paint','GOLD Paint',30)
			  ,(3,'Screws','30"',40)
INSERT INTO  TableB VALUES (1,1,30,0,10,20,'04-04-2014')
			  ,(2,2,40,10,10,40,'04-04-2014')
			  ,(3,3,50,5,10,45,'04-04-2014')
			  ,(4,1,20,0,0,20,'04-05-2014')
			  ,(5,2,40,0,10,30,'04-04-2014')
			  ,(6,3,45,0,5,40,'04-04-2014')

-------------------------------------------------------------------------------------------------------
--Front End InputVariable Declartion
-------------------------------------------------------------------------------------------------------
DECLARE @FromDate VARCHAR(1024) = '04-04-2014'
DECLARE @ToDate   VARCHAR(1024) = '04-05-2014'

-------------------------------------------------------------------------------------------------------
--Query
-------------------------------------------------------------------------------------------------------
SELECT	                A.ItemName
		       ,A.ItemDescription
		       ,C.Begining
		       ,SUM(B.[Add])  AS [Add]
		       ,SUM(B.Less)   As [Less]
		       ,D.Ending
FROM		       TableA A
INNER JOIN	       TableB B
ON		       A.ItemID = B.ItemID
INNER JOIN             (SELECT (CASE WHEN MAX(B.[Date]) OVER(PARTITION BY B.[Date]) =  @FromDate THEN 
                   B.Beginning  END )AS Begining,ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS Rn  FROM TableB B ) C
ON			       A.ItemID =C.Rn
INNER JOIN              (SELECT (CASE WHEN MAX(B.[Date]) OVER(PARTITION BY B.[Date],B.Ending) =  @FromDate THEN 
                   B.Ending  END )AS Ending,ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS Rn  FROM TableB B )	D
ON			       A.ItemID =D.Rn
GROUP BY           A.ItemName,A.ItemDescription,C.Begining,D.Ending
-------------------------------------------------------------------------------------------------------
--DROP Table
DROP TABLE TableA
DROP TABLE TableB
-------------------------------------------------------------------------------------------------------




---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Edited by - MuralikrishnaVeera on 04/25/2014 05:58:15
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000