Need Some Query

• nandu524

  SSC Rookie

  Points: 30

  More actions

  June 1, 2011 at 5:09 am

  #145466

  Hi All,

  Im Stuck with some query...

  I have the following tables like

  Region_Master-->State_MAster-->Town_MAster-->Store_Master

  which is the location hierarchy.

  Brand_Master-->Cluster_master-->Item_master-->Gender_Master

  which is the product heirarchy

  Below r the transaction tables

  StockLedger (Contains the day wise stock balance(quantity) for each item and store)

  SalesPriceList (Contains the day wise sales price of each item)

  I need the output :-

  -----------------------------------------------------------------------

  Region|State|Town|StoreName|Brand|Cluster|Gender|Quantity|Stockvalue|Sale Price

  ------------------------------------------------------------------------

  Stock value will be the quantity*saleprice of the item (latest saleprice)

  I tried this

  select r.Region_name,s.State_Name,f.FTSTown_Name,st.Store_code,st.Store_name,b.Brand_Name,

  c.Cluster_name,g.Gender_Name,t.NewBalance,spl.salesprice*t.NewBalance,spl.salesprice

  from ER_Region_Master r, ER_State_Master s, ER_FTSTown_MAster f, ER_Store_Master st,

  ER_Brand_Master b,ER_Variant_Master v,ER_Cluster_Master c,ER_Gender g,StockLedgerTrn t,

  Salespricelist spl

  where r.Region_code=st.Region_id and s.State_code=st.State_id and f.FTSTown_code=st.FTSTown_id

  and t.warehouse=st.Store_code and v.variant_code =t.Itemnumber and v.Brand_id=b.Brand_code

  and v.Cluster_id=c.Cluster_code and v.Gender=g.Gender_code and spl.Itemnumber=t.itemnumber

  and spl.validfrom=(select max(validfrom) from salespricelist where itemnumber='1229SL06')

  and t.itemnumber='1229SL06' and t.Transactiondate='20100409'

  group by r.Region_name,s.State_Name,f.FTSTown_Name,st.Store_code,st.Store_name,b.Brand_name,

  c.Cluster_Name,g.Gender_name--,spl.salesprice,t.newBalance

  order by Region_Name,State_Name,FTSTown_name

  But i got stuck to retrieve the saleprice of all the items under a particular cluster, since i need the report till cluster only .

  Can somebody help me on this...

  Thanks in advance

• WayneS

  SSC Guru

  Points: 95461

  More actions

  June 1, 2011 at 7:20 am

  #1332491

  You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2