automatically duplicate rows in sql server 2008 r2 select query
i have two table name 1) tbl_Stock_Journal 2) tbl_Stock_Journal_Details

i run a select query with where condition
i have 5 data in table1 and in table 2 have 3 data when i run the query result is showing 15 row the query show on date wise
i want to maximum data show and if other table in same date have no number of data then show blank or null

please help me.....

ALter proc P_GetStockJournalRPT --  'all', 'all','2018-07-10' , '2018-07-10'     

@SJItmID varchar (100)      ,          
@SJDItmID varchar (100)      , 
@Sdate date,                                  
@EDate Date  

if @SJItmID='ALL' and @SJDItmID='ALL'
select distinct SJ.SJID, i1.itemnm as itemnmsou, SJ.SJqty, SJ.SJPCS, SJ.SJRate, SJ.SJamt, SJ.SJleft, SJ.SJDate, SJ.nrr,
i2.itemnm as itemnmDES, SJD.SJDqty, SJD.SJDPCS, SJD.SJDRate, SJD.SJDamt, SJD.SJDstock, SJD.SJDdate

from tbl_Stock_Journal SJ

INNER JOIN tbl_item AS i1   ON i1.itemid  =SJ.SJItmID
 inner JOIN tbl_Stock_Journal_Details AS SJD   ON   SJ.SJID = SJD.SJID                             
inner JOIN tbl_item AS i2   ON i2.itemid  =SJd.SJdItmID

where CAST(sj.SJDate AS DATE) BETWEEN @Sdate AND @EDate         
--where CAST(sj.SJDate AS DATE) BETWEEN '2018-07-10' AND '2018-07-10'        

You need to union the two similar tables together then query the resulting set using group by with max to get the latest information.

I'd do this in two stages using views, otherwise your query will be complex and ugly.

I think you should also question your database design, needing to do stuff like this is usually a hint of a smell.

thank you sir for replying
but sir i am new in sql server can u help me how to do this please sir help me
if possible

Yes sure. Step one, learn SQL. Try CodeAcademy's SQL course, it's very good.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.