How to combine results of multiples queries into a single dataset (by using CTE)

I have a requiremnet in which  I need to show the weekly,monthly and yearly sale.
the problem is that I need to combine all three dataset in to one so here is the trick we can use with keyword
this is demo script with following result:

with t1 as (
select           
             E.product_name AS 'Product name',
              sum( B.Qty )                                       AS 'Total grams purchased in last 7 days' ,       
              SUM(A.total_amount) AS 'Total sales price purchased in the last 7 days'
              , row_number() over (order by product_name) rn
    FROM       A         
    INNER JOIN B         
    ON         (A.customer_id = B.customer_id       
               AND   A.ShopPurchaseStatus_ID=2 AND A.A_id = B.A_id )       
    INNER JOIN C         
    ON         (C.customer_id = B.customer_id       
               AND        C.shop_product_id = B.shop_product_id AND C.Valuepack_Type_ID=2 )       
         
     INNER JOIN D         
    ON        ( C.Reference_Number=D.LEOGold_By_Reference_Number
AND D.Customer_ID=C.Customer_ID) 
    INNER JOIN E         
    ON   D.shop_product_id = E.shop_product_id 
 WHERE A.Entry_Datetime >=   Dateadd(week, -1, Getdate())
   
 group by    E.product_name),

t2 as(
 select           
             E.product_name AS 'Product name',
              sum( B.Qty )                                       AS 'Total grams purchased in last 30 days' ,       
              SUM(A.total_amount) AS 'Total sales price purchased in the last 30 days'
              , row_number() over (order by product_name) rn
    FROM       A         
    INNER JOIN B         
    ON         (A.customer_id = B.customer_id       
               AND   A.ShopPurchaseStatus_ID=2 AND A.A_id = B.A_id )       
    INNER JOIN C         
    ON         (C.customer_id = B.customer_id       
               AND        C.shop_product_id = B.shop_product_id AND C.Valuepack_Type_ID=2 )       
         
     INNER JOIN D         
    ON        ( C.Reference_Number=D.LEOGold_By_Reference_Number
AND D.Customer_ID=C.Customer_ID) 
    INNER JOIN E         
    ON   D.shop_product_id = E.shop_product_id 
 WHERE A.Entry_Datetime >=   Dateadd(year, -1, Getdate())
   
 group by    E.product_name
 ),
 t3 as(
 select           
             E.product_name AS 'Product name',
              sum( B.Qty )                                       AS 'Total grams purchased YTD' ,       
              SUM(A.total_amount) AS 'Total sales price purchased YTD'
              , row_number() over (order by product_name) rn
    FROM       A         
    INNER JOIN B         
    ON         (A.customer_id = B.customer_id       
               AND   A.ShopPurchaseStatus_ID=2 AND A.A_id = B.A_id )       
    INNER JOIN C         
    ON         (C.customer_id = B.customer_id       
               AND        C.shop_product_id = B.shop_product_id AND C.Valuepack_Type_ID=2 )       
         
     INNER JOIN D         
    ON        ( C.Reference_Number=D.LEOGold_By_Reference_Number
AND D.Customer_ID=C.Customer_ID) 
    INNER JOIN E         
    ON   D.shop_product_id = E.shop_product_id 
 WHERE A.Entry_Datetime >=   Dateadd(month, -1, Getdate())
   
 group by    E.product_name
 )

 select t3.[Product name], t1.[Total grams purchased in last 7 days],t1.[Total sales price purchased in the last 7 days],t2.[Total grams purchased in last 30 days],t2.[Total sales price purchased in the last 30 days],
 t3.[Total grams purchased YTD],t3.[Total sales price purchased YTD]
from t1 full outer join t2 on t1.rn = t2.rn
full outer join t3 on t2.rn = t3.rn


Reference:
https://stackoverflow.com/questions/15230350/how-to-combine-results-of-two-queries-into-a-single-dataset

SQL Server Date Formats

This link is very helpful for SQL Server date format

http://www.sql-server-helper.com/tips/date-formats.aspx

Binding data to template fields

If you want to bind data to template fields you just need to use Bind function
For example i need to bind TextBox value with my data that is return from store procedure,right now Tracking_Number is the field name that I need to bind.

        <asp:TemplateField HeaderText="Tracking Number">
                                            <ItemTemplate>
<asp:TextBox ID="txtboxTrackingNumber" runat="server"  Enabled ="false"  Text='<%#Bind("Tracking_Number") %>'></asp:TextBox>
                                            </ItemTemplate>
                                        </asp:TemplateField>

Reference:
https://www.codeproject.com/Questions/254659/Binding-data-to-template-fields

Stock Calculate in SQL

If you want calulcate how many stock we have or how many stock sold or how many stock are holding

Status Table:

1 Upload
2 Pending
3 Reserved
4 Sold
5 Rejected

StockActivity : In which I upload all the stock with status accordingly
Product_Name: In this table I have all the prodcut

CREATE PROCEDURE Getstockholding @FromDate DATETIME, 
                                 @ToDate   DATETIME 
AS 
  BEGIN 
      SET nocount ON; 

      SELECT product_name.product_name 
             AS 
             'Product Name', 
             ( (SELECT Isnull(Sum(qty), 0) 
                FROM   stockactivity 
                WHERE  status_id IN( 1 ) 
                       AND product_id = product_name.product_id) - (SELECT 
               Isnull(Sum(qty), 0) 
                                                                    FROM 
               stockactivity 
                                                                    WHERE 
                 customer_id IS NOT NULL 
                 AND status_id IN( 4 ) 
                 AND product_id = shop_products.product_id) ) 
             AS 'Current Stock', 
             ( (SELECT Isnull(Sum(qty), 0) 
                FROM   stockactivity 
                WHERE  status_id IN( 1 ) 
                       AND product_id = product_name.product_id 
                       AND stockactivity.entry_datetime >= @ToDate) - (SELECT 
               Isnull(Sum(qty), 0) 
                                                                       FROM 
               stockactivity 
                                                                       WHERE 
                 customer_id IS NOT NULL 
                 AND status_id IN( 4 ) 
                 AND product_id = product_name.product_id 
                 AND stockactivity.entry_datetime >= @ToDate) ) 
             AS 'On Date Stock', 
             (SELECT Isnull(Sum(qty), 0) 
              FROM   stockactivity 
              WHERE  customer_id IS NOT NULL 
                     AND status_id IN( 4 ) 
                     AND product_id = product_name.product_id 
                     AND stockactivity.entry_datetime BETWEEN 
                         @FromDate AND @ToDate) 
             AS 
             'Stock Sold', 
             (SELECT Isnull(Sum(qty), 0) 
              FROM   stockactivity 
              WHERE  customer_id IS NOT NULL 
                     AND status_id IN( 3 ) 
                     AND product_id = product_name.product_id 
                     AND stockactivity.entry_datetime BETWEEN 
                         @FromDate AND @ToDate) 
             AS 
             'Stock Hold' 
      FROM   product_name 
      WHERE  product_name.product_id IN ( 1, 2, 3 ) 
      ORDER  BY product_name.product_name 
  END 

Query Execution Plan (SQL Server)

I am facing an issue in my query return data is too slow , Obviously there are some indexes missing on those table .Trick is when you run query Include Actual execution plan so it will suggest these are the missing indexes if you include this it will make your query faster.

Then just right click and click Missing index detail run that query and see the magic :)

Cursor in SQL

I have a task in which there are some records that are missing in my child table and need to get insert from parent table, records are more then 1000
so by using simple inserting script we can use cursor, I need to insert those users who have  FreeSignUp=1 and call my store procedure do rest of insert thing.




DECLARE @Customer_id numeric
 DECLARE cur_emp CURSOR
STATIC FOR
SELECT  Customer_id from customer where FreeSignUp=1
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN
 FETCH NEXT FROM cur_emp INTO @Customer_id
 WHILE @@Fetch_status = 0
 BEGIN
  EXEC sp_add_Customer @Customer_id

 FETCH NEXT FROM cur_emp INTO @Customer_id
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF


For Ref:
http://www.dotnettricks.com/learn/sqlserver/sql-server-basics-of-cursors

Monthly, Weekly and Daily Report Query

If you want to report that contain filter of daily,weekly,monthly,yearly you can use this query.What you have to do pass a paramter which report you want for
demo purpose I declare in SQL

DECLARE @Period AS VARCHAR(250)
SET @Period = 'Weekly'
select * from table_name WHERE  entry_datetime >= (CASE
       WHEN @Period = 'Daily' THEN Dateadd(day, -1, Getdate())
       WHEN @Period = 'Weekly' THEN Dateadd(week, -1, Getdate())
       WHEN @Period = 'Monthly' THEN Dateadd(month, -1, Getdate())
       WHEN @Period = 'Yearly' THEN Dateadd(year, -1, Getdate())
       END )
 
 
So this query get all the data that exist in this week

Ref:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d380800-dace-4960-9e4d-5766e3a8fe2f/monthly-weekly-and-daily-reports-question?forum=sqlreportingservices

Database Optimization RND

Now a days our database suck too much so one of my senior make a team so we can optimized a queries who suck CPU time , i did some RND these are the some useful  links that quite helpful may be it can help you as well.

DBCC TRACEON (4199, -1);

https://www.youtube.com/watch?v=Pm8wamJjLAk

https://www.brentozar.com/sql/sql-server-performance-tuning/

https://blog.sqlauthority.com/2007/05/30/sql-server-trace-flags-dbcc-traceon/

https://dba.stackexchange.com/questions/102292/trace-flag-4199-enable-globally
https://sqlserverscotsman.wordpress.com/2016/12/05/trace-flag-4199-query-optimiser-hotfixes/

https://blog.sqlauthority.com/2007/03/23/sql-server-stored-procedure-clean-cache-and-clean-buffer/

http://dbadiaries.com/using-dbcc-freeproccache-to-clear-sql-server-procedure-cache

https://blog.sqlauthority.com/2014/11/02/sql-server-what-are-my-trace-flags-enabled-on-sql-server/

https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

https://stackoverflow.com/questions/232333/how-long-should-set-read-committed-snapshot-on-take

Like query ignore spacing in SQL

If your are searching in like query and string contain spaces then it will not search as you know space is also a character
for example in my table my fullname field is [B]Arvind  Kumar  as user is searching with arvind kumar it will not fetch out the records, so what you have to do  use replace function



declare @FullName  as Varchar(MAX)=''
set @FullName='arvind kumar'
set @FullName=REPLACE('%'+@FullName+'%', ' ', '')

select * from Customer WHERE REPLACE(FullName, ' ', '') like   COALESCE('%'+@FullName+'%',customer.FullName)

Validate only certain selected Repter Row Controls like TextBox using ASP.Net Validators.

  In my repter there are textboxes and buttosn, what I am facing that if one of my button click it show in all row to fill the textboxs .
  So i have to do dynmic so it only validate that row in which button is click.In this Shop_Product_id is my unique id you can place your own unique id

   
                <asp:Repeater id="rptProducts" runat="server">
                            <ItemTemplate>
                               
                                           <asp:TextBox ID="txtVoucherCode" runat="server"   ValidationGroup = '<%# "Group_" + Eval("Shop_Product_id").ToString()%>'></asp:TextBox>
                                            <asp:Button ID="btnUpdateSkillVoucher" Text="UpdateVoucher"    ValidationGroup = '<%# "Group_" + Eval("Shop_Product_id").ToString()%>' CssClass="button" runat="server" />
<asp:RequiredFieldValidator runat="server" ID="rqfl" ErrorMessage="Skill Voucher Code Required!"    ValidationGroup = '<%# "Group_" + Eval("Shop_Product_id").ToString() %>' ControlToValidate="txtVoucherCode" SetFocusOnError="True"> </asp:RequiredFieldValidator>
                            </ItemTemplate>
                        </asp:Repeater>

For gridview
https://www.aspsnippets.com/Articles/Validate-only-certain-selected-GridView-Row-Controls-like-TextBox-using-ASPNet-Validators.aspx
                

Conditionally filter on a column in a WHERE clause in SQL Query

I need to apply some date filter in my query but there are some hectic behind it. What I facing in my table some dates are null and some have dates it depend upon my status like
If my status is pending so action date is empty and if somebody  take an action it insert a date.

So I summarize this I need to include my date filter on conditionally based,so what I did
first i make a variable in this I supplied a value 1 when i need this date rang filter otherwise I passed
0 in it.

DECLARE @ISChecked_Apporved_rejected_date AS VARCHAR 

SET ischecked_apporved_rejected_date ="1" 

SELECT 
       * 
FROM   table k
WHERE  k.statusid = 2 
       AND ( ( 0 = CASE 
                     WHEN ( @ISChecked_Apporved_rejected_date = '0' ) THEN 0 
                     ELSE 1 
                   END ) 
              OR ( CONVERT(NVARCHAR(10), k.approveddatetime, 111) BETWEEN 
                         COALESCE(@Approver_Rej_DateFrom, 
                         CONVERT(NVARCHAR(10), @Approver_Rej_DateFrom, 
                         111)) AND COALESCE(@Approver_Rej_DateTo, 
                                   CONVERT(NVARCHAR(10), @Approver_Rej_DateTo, 
                                   111)) ) 
           )

Short query:
Declare @StatusID as INT

set @StatusID=-1 -- it means all

DECLARE @Status_all AS VARCHAR 

 IF @StatusID =-1

 BEGIN                              

  SET @Status_all ='0'                          

    END  

   AND ( ( 0 = CASE 

                     WHEN ( @Status_all = '0' ) THEN 0 

                     ELSE 1 

                   END ) OR (Status_ID = COALESCE(@StatusID,Status_ID))) 

For Reference:
https://stackoverflow.com/questions/1978179/how-to-conditionally-filter-on-a-column-in-a-where-clause

Return data in XML format in SQL Server

if you want to return you data in XML you can just simply put this line in the end after you query

 FOR XML RAW ('ABC'), ELEMENTS ;  

get field value form XMl in SQL

if you need to fetch field from XML in SQL you can use this query.Right now my XML look like this
i need to fetch the value of Expiry DateTime .


  DECLARE @Expiry_DateTime AS DATE
SELECT @Expiry_DateTime=XMLDATA.value('(/CustomerInfo//field//value//node())[3]', 'nvarchar(max)')
FROM KYC WHERE  UserName='test11FN'   and ID=4112532

XMLDATA is my column name where my XML store, 3 represent index which value you want to get , value represent this   <value>2017-05-31</value>. if i need to get the label value i just replace this query with  XMLDATA.value('(/CustomerInfo//field//label//node())[3]', 'nvarchar(max)')



<CustomerInfo>
  <field>
    <label>CustomerId</label>
    <type>hidden</type>
    <value>3323</value>
  </field>
  <field>
    <label>ID Number</label>
    <type>text</type>
    <value>23</value>
  </field>
  <field>
    <label>Expiry DateTime</label>
    <type>text</type>
    <value>2017-05-31</value>
  </field>
  <field>
    <label>Customer Name</label>
    <type>hidden</type>
    <value>test11FN</value>
  </field>
  <field>
    <label>Customer Email</label>
    <type>hidden</type>
    <value>testa11@mailinator.com</value>
  </field>
  <field>
    <label>Picture Image</label>
    <type>image</type>
    <value>https://www.abccom/public/photoid/3323/</value>
  </field>
</CustomerInfo>

How to check which query take time on server

This is the following query which tell how much time it will take on server the status field, if it show running then its fine and if it show suspended its means :

The session is waiting for an event, such as I/O, to complete.
A query gets suspended when it is requesting access to a resource that is currently not available. This can be a logical resource like a locked row or a physical resource like a memory data page. The query starts running again, once the resource becomes available.
High disk IO means that a lot of data pages need to be accessed to fulfill the request

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Merge two rows into one row SQL

I have a requirement in which i need to show two column in one row .The simplest way to merge rows is with an aggregate function such as MIN/MAX. These functions will ignore nulls.

Before

EmployeeNo  Column1   Column2
================================
1           |  NULL       | Column2
1           |  Column1    |   NULL 

Sql

SELECT
  EmployeeNo,
  MAX(Column1) AS Column1,
  MAX(Column2) AS Column2
FROM MyTable
GROUP BY EmployeeNo

After Result

EmployeeNo  Column1   Column2
================================
1          | Column1  |   Column2

PS :
If you have a sub query to get a columns from other table it will not work in this you need to make a Derived Tables or you can use left join 
Reference:
https://dba.stackexchange.com/questions/67831/want-to-combine-two-rows-into-one-row