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
 General SQL Server Forums
 New to SQL Server Programming
 Select Query Help (?) VB

Author  Topic 

Takeshi19
Starting Member

1 Post

Posted - 2014-04-25 : 02:16:58
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

129 Posts

Posted - 2014-04-25 : 05:49:51
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.......
Go to Top of Page
   

- Advertisement -