databas query question
-
Hi all! There is a question. i am facing difficulting in writting a query for selecting record in database.i am explaining whole scenerio of current problem: i have a table StockDelta having attributes as follows: stockDeltaID(primary key), productID(forighn key from product table(productID, productName, totalQuantity etc) ), Delta(the change in quantity of stock of a product, if the quantity increases the value of delta will be positive and if the quantity decreases value of delta will be negative), isStockAdded(bool value indicating if the changed amount was basically the added stock), isSecondarySales(bool value indicating if the changed amount was basically the sencondary sales), isDamages(bool value indicating if the changed amount was basically the damaged amount), isFreeGifts(bool value indicating if the changed amount was basically the amount of freeGift) now i have to generate a Stock report(using crystal reports), the report will take a parameter in case of daily report and two parameters in case of monthly report. the information that we want to show in report is as follows: The columns of report should be "Opening Stock"(representing quantity at start of month/day). we will get this column by Sum(delta) where date < pdate-1 //pdate is parameter date "Stock Added"(representing quantity purchased during month/day), we will get this column by Sum( Delta) where date = pdate and isStockAdded = true "Secondry Sales"(representing the sale out of a produt on a specific month/day), we will get this column by Sum(delta) where date = pdate and isSecondarySales = true "Closing Stock"(representing the net quantity of a product in stock on a specific month/day), we'll get this column by Sum (delta) where date < pdate "Damages"(from 1st of a month till a specific day or in a whole month), we get this column by quering Sum(delta) where date between 1st of month till pdate and isDamages = true "FreeGifts"(representing the quantity of freegifts from 1st till that day of a month or for whole month). we will get this column by quering Sum(quantity of Freegifts) where date between 1st of month till pdate and isFreeGift = true hope you people have grasped what i said.. i have tried my best to figure out query for this report but maybe at this student level i am unable to come with the right solution. i am stuck on it and request you people to please consider the problem if you get some time. Thanks in advance shaz jazz
-
Hi all! There is a question. i am facing difficulting in writting a query for selecting record in database.i am explaining whole scenerio of current problem: i have a table StockDelta having attributes as follows: stockDeltaID(primary key), productID(forighn key from product table(productID, productName, totalQuantity etc) ), Delta(the change in quantity of stock of a product, if the quantity increases the value of delta will be positive and if the quantity decreases value of delta will be negative), isStockAdded(bool value indicating if the changed amount was basically the added stock), isSecondarySales(bool value indicating if the changed amount was basically the sencondary sales), isDamages(bool value indicating if the changed amount was basically the damaged amount), isFreeGifts(bool value indicating if the changed amount was basically the amount of freeGift) now i have to generate a Stock report(using crystal reports), the report will take a parameter in case of daily report and two parameters in case of monthly report. the information that we want to show in report is as follows: The columns of report should be "Opening Stock"(representing quantity at start of month/day). we will get this column by Sum(delta) where date < pdate-1 //pdate is parameter date "Stock Added"(representing quantity purchased during month/day), we will get this column by Sum( Delta) where date = pdate and isStockAdded = true "Secondry Sales"(representing the sale out of a produt on a specific month/day), we will get this column by Sum(delta) where date = pdate and isSecondarySales = true "Closing Stock"(representing the net quantity of a product in stock on a specific month/day), we'll get this column by Sum (delta) where date < pdate "Damages"(from 1st of a month till a specific day or in a whole month), we get this column by quering Sum(delta) where date between 1st of month till pdate and isDamages = true "FreeGifts"(representing the quantity of freegifts from 1st till that day of a month or for whole month). we will get this column by quering Sum(quantity of Freegifts) where date between 1st of month till pdate and isFreeGift = true hope you people have grasped what i said.. i have tried my best to figure out query for this report but maybe at this student level i am unable to come with the right solution. i am stuck on it and request you people to please consider the problem if you get some time. Thanks in advance shaz jazz
Hi Shaz I have done the SQL for the opening-balance and stock-added results:
SELECT SD.productId, SUM(CASE WHEN SD.deltaDate < @StartDate THEN SD.Delta ELSE 0 END) AS OpeningStock, SUM(CASE WHEN SD.deltaDate >= @StartDate AND SD.isStockAdded = 1 THEN SD.Delta ELSE 0 END) AS StockAdded FROM StockDelta SD WHERE deltaDate < @EndDate GROUP BY productId ORDER BY productIdI'll let you figure out how to do the remaining columns. The @StartDate and @EndDate bits refer to the parameters that you need to supply. The above should work for SQL-Server and Oracle. If you are using MS-Access then you will need to use the IIF function. An alternative approach would be to use sub-queries. Regards Andy