binary tree structure with child count

DECLARE @Data TABLE (
  ID INTEGER PRIMARY KEY
  , ParentID INTEGER
  , Text VARCHAR(32)
  , Price INTEGER
)

INSERT INTO @Data
  SELECT 1, Null, 'Root', NULL
  UNION ALL SELECT 2, 1, 'Flowers', NULL
  UNION ALL SELECT 3, 1, 'Electro', NULL
  UNION ALL SELECT 4, 2, 'Rose', 10
  UNION ALL SELECT 5, 2, 'Violet', 5
  UNION ALL SELECT 6, 4, 'Red Rose', 12
  UNION ALL SELECT 7, 3, 'Television', 100
  UNION ALL SELECT 8, 3, 'Radio', 70
  UNION ALL SELECT 9, 8, 'Webradio', 90

  ;WITH ChildrenCTE AS (
  SELECT  RootID = ID, ID
  FROM    @Data
  UNION ALL
  SELECT  cte.RootID, d.ID
  FROM    ChildrenCTE cte
          INNER JOIN @Data d ON d.ParentID = cte.ID
)
SELECT  d.ID, d.ParentID, d.Text, d.Price, cnt.Children
FROM    @Data d
        INNER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.ID

demo test

demo test

Dynamic Date Picker On grid view...

$(".datagrid-row").each(function(index, element){
var id = $(this).attr("id");
  var dates = $("#"+id+" .StartDatetext, #"+id+" .EndDatetext").datepicker({
              minDate: '0',
              maxDate: '10000D',
              dateFormat: "MM dd yy",
              onSelect: function (selectedDate) {
                  var option = this.id == ".EndDatetext" ? "maxDate" : "minDate",
                    instance = $(this).data("datepicker"),
                    date = $.datepicker.parseDate(
                        instance.settings.dateFormat ||
                        $.datepicker._defaults.dateFormat,
                       selectedDate, instance.settings);

                  var odate = date;
                  if (this.id == "StartDatetext") {
                      odate.setDate(odate.getDate() + 1);
                  }
                  dates.not('.StartDatetext').datepicker("option", option, odate);
              }
          });
       });

Dynamic Date picker In Jqurey

 $(function () {
          var dates = $(".StartDtaeText, .EndDateText").datepicker({
              minDate: '0',
              maxDate: '100D',
              onSelect: function (selectedDate) {
                  var option = this.id == ".EndDateText" ? "maxDate" : "minDate",
                    instance = $(this).data("datepicker"),
                    date = $.datepicker.parseDate(
                        instance.settings.dateFormat ||
                        $.datepicker._defaults.dateFormat,
                        selectedDate, instance.settings);
                  //alert(this);
                  dates.not('.StartDtaeText').datepicker("option", option, date);
              }
          });

Can I use CASE statement in a JOIN condition?

A CASE expression returns a value from the THEN portion of the clause.

You could use it thusly: 

SELECT  * 
FROM    sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

Stop page post back action

 if (!IsPostBack)
            {
                ViewState["postids"] = System.Guid.NewGuid().ToString();
                Session["postid"] = ViewState["postids"].ToString();
            }
            else
            {
                if (ViewState["postids"].ToString() != Session["postid"].ToString())
                {
                    IsPageRefresh = true;
                }
                Session["postid"] = System.Guid.NewGuid().ToString();
                ViewState["postids"] = Session["postid"].ToString();
            }

Procedure to insert bulk data from heirarchy XML

USE [OMS]
GO
/****** Object:  StoredProcedure [inv].[insertbulkphysicalInventoryold]    Script Date: 11/26/2014 10:09:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------Create by preeti on 10-10-2014

--inv.insertphysicalInventory '
--<inv_feed>
--  <item>
--    <code>kba6_34</code>
--    <inv_detail>
--      <loc>DL</loc>
--      <bucket>
--        <name>PGRN</name>
--        <qty>20</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--      <bucket>
--        <name>CSMT</name>
--        <qty>50</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--    </inv_detail>
--    <inv_detail>
--      <loc>ST</loc>
--      <bucket>
--        <name>PGRN</name>
--        <qty>100</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>80</qty>
--      </bucket>
--      <bucket>
--        <name>CSMT</name>
--        <qty>70</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--    </inv_detail>
 
--  </item>
--  <item>
--    <code>kcr3765_32</code>
--    <inv_detail>
--      <loc>DL</loc>
--      <bucket>
--        <name>PGRN</name>
--        <qty>20</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>40</qty>
--      </bucket>
--      <bucket>
--        <name>CSMT</name>
--        <qty>60</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--    </inv_detail>
--    <inv_detail>
--      <loc>ST</loc>
--      <bucket>
--        <name>PGRN</name>
--        <qty>10</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>20</qty>
--      </bucket>
--      <bucket>
--        <name>CSMT</name>
--        <qty>40</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--    </inv_detail>

--  </item>
--</inv_feed>
--'

ALTER Proc [inv].[insertbulkphysicalInventoryold]
@invfeedxml XML =null,
@userid varchar(10)=''
as begin
begin try
begin transaction
    begin
if(@invfeedxml is not null)
begin
delete from  inv.physicalstock
insert into inv.physicalstock(itemcode,loccode,bucketcode ,qty,disabled,dstatus,createdby,createddate)

SELECT
   CAST(CAST(x.item.query('data(code)')   AS  varchar(30))  AS varchar(30)),
  CAST(CAST(y.item.query('data(loc)')  AS  varchar(1000)) AS varchar(1000)),
    CAST(CAST(z.item.query('data(name)') AS char(25)) AS varchar(100)),
   CAST(z.item.query('data(qty)') AS varchar(25)),'false' as disabled,'false' as dstatus,@userid as createdby,GETDATE() as createdate
FROM
    @invfeedxml.nodes('/inv_feed/item') x(item)
    CROSS APPLY  
     x.item.nodes('./inv_detail') AS y(item)
  CROSS APPLY
     y.item.nodes('./bucket') AS z(item)
  end
 
  --select itemcode,loccode,bucketcode ,qty from inv.physicalstock
  end
    select 'successfully updated' as msg
commit transaction
end try
begin catch
rollback transaction
select 'not successfully updated' as msg
end catch
end