Wednesday, June 19, 2013

[T-SQL] How to group by date in a date range

[T-SQL] How to group by date in a date range

How to group by date in a date range

Posted: 18 Jun 2013 10:45 PM PDT

Hello all,I wonder if somebody could point me in the right direction...I have following table:[code="sql"]SET DATEFORMAT DMY;SET NOCOUNT ON;DECLARE @t TABLE (ITEM varchar(10), QTY INT, CREATED DATETIME, ENDS DATETIME,USERID INT)INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 1',1,'27-05-2013 09:30:00',NULL,1INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 3',1,'28-05-2013 14:50:00',NULL,1INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 2',1,'27-05-2013 09:45:00',NULL,2INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 4',1,'28-05-2013 11:45:00',NULL,2INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 5',1,'28-05-2013 13:17:00',NULL,2INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 3',1,'28-05-2013 10:10:00',NULL,3INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 4',1,'28-05-2013 11:45:00', '30-06-2013 11:45:00',3INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 1',1,'28-05-2013 13:17:00',NULL,4INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 3',1,'28-05-2013 10:10:00','30-06-2013 11:45:00',4INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 5',1,'28-05-2013 10:10:00','30-06-2013 11:45:00',4SELECT * FROM @t [/code]It contains a list of items (ITEM1 - ITEM5) that is assigned to a user (USERID) once every day if that day < than ENDS of item (= this is discontinuation date for item and user) or if ENDS is NULL.For a given date range filter I need to group the quantities of items by date. e.g. for filter 01-06-2013 - 03-06-2013 the desired outcome would be[code="sql"]SET DATEFORMAT DMY;SET NOCOUNT ON;DECLARE @t TABLE (ASSIGNED DATETIME, ITEM varchar(10), QTY int)INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM3',3INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM4',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM5',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM3',3INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM4',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM5',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM3',3INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM4',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM5',1SELECT * FROM @t [/code]while if date range would be 01-07-2013 - 03-07-2013, desired outcome would be like this (ignoring the items discontinued on 30-06-2013):[code="sql"]SET DATEFORMAT DMY;SET NOCOUNT ON;DECLARE @t TABLE (ASSIGNED DATETIME, ITEM varchar(10), QTY int)INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM3',2 -- one item discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM4',1 -- one item discontinued on 30-06-2013-- ITEM5 missing completely for 01-07-2013 as discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM3',2 -- one item discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM4',1 -- one item discontinued on 30-06-2013-- ITEM5 missing completely for 02-07-2013 as discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM3',2 -- one item discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM4',1 -- one item discontinued on 30-06-2013-- ITEM5 missing completely for 03-07-2013 as discontinued on 30-06-2013SELECT * FROM @t [/code] How would I achieve these results? My main problem is how to group by date in selected range.Many thanks for any hints.

How can I display a block value count of 8 repeating

Posted: 19 Jun 2013 12:53 AM PDT

Hi AllThanks in advance for any advice.I need to print address data in blocks of 8 for the labels we are usingThe data sets returned could be any number of rows what I want to do is add a column that gives a values like thisRows returns values in new column1 - 8 19 - 16 217 – 24 3The new column value would be the page number as we need to do odd even to print on the back of the labels within SSRS 2008 body I would like to do this within the tsql I have tried ((ROW_NUMBER() OVER (ORDER BY oddpages.id_num)) / 9) + 1 AS pagenumber, but that gives strange results as the first count is correct but the 2nd give one extraRow 17 should be the start of page 3Row_ID page number1 12 13 14 15 16 17 18 19 210 211 212 213 214 215 216 217 218 319 320 321 322 323 324 325 326 327 4ThanksSimon

How To return Year & Month in this format: 2013-06

Posted: 18 Jun 2013 09:57 PM PDT

Hi All, I need to return the minimum date from my table and manipulate it so it starts from the first of the monthe.g 2012-06+'-'+'01+ and use this as the minimum date: '2012-06-01- instead of the 22ndSelect MIN(dtePostedToWebsiteDate) --Here's the minimum date >>> 2012-06-22 09:07:42.413FROM dtlVacancyPostAuditHow do I achieve this?Thanks Teee

PIVOT on VIEW for "special data"

Posted: 18 Jun 2013 09:13 PM PDT

Hi Guys,I'm in need of a solution that takes data from a primary table and also values from a separate "specialised data" table for each record. For example, I have a item "Stock1" which has the following specific set of attributes:SpecialField1SpecialField2Next, "Stock2" has the following specific set of attributes:SpecialField1SpecialField2SpecialField3and finally, "Stock3" has the following specific set of attributes:SpecialField3SpecialField4All these "SpecialFieldX" records are defined in a reference table and are able to be associated (only once) to any stock. My code below creates and populates the table necessary:[code="sql"]CREATE TABLE #ReferenceCodes( [ReferenceGroupCode] [varchar](20) NOT NULL, [ReferenceCode] [varchar](50) NOT NULL, [ReferenceName] [varchar](50) NOT NULL, [Translation] [varchar](100) NULL) ON [PRIMARY]GOINSERT INTO #ReferenceCodes(ReferenceGroupCode,ReferenceCode,ReferenceName,Translation)SELECT 'SPECIAL_DATA','SpecialField1','Stores special field 1','E'UNIONSELECT 'SPECIAL_DATA','SpecialField2','Stores special field 2','E'UNIONSELECT 'SPECIAL_DATA','SpecialField3','Stores special field 3','F'UNIONSELECT 'SPECIAL_DATA','SpecialField4','Stores special field 4','D'CREATE TABLE #StockSpecialisedData( [StockCode] [varchar](20) NOT NULL, [AttributeName] [varchar](40) NOT NULL, [AttributeClass] [varchar](30) NOT NULL, [AttributeValue] [sql_variant] NOT NULL) ON [PRIMARY]CREATE TABLE #Stocks ( [StockCode] [varchar](20) NOT NULL ,[StockName] [varchar](50) NOT NULL)INSERT INTO #Stocks(StockCode, StockName)SELECT 'Stock1','Stock 1'UNIONSELECT 'Stock2','Stock 2'UNIONSELECT 'Stock3','Stock 3'UNIONSELECT 'Stock4','Stock 4'INSERT INTO #StockSpecialisedData(StockCode,AttributeName,AttributeClass,AttributeValue)SELECT 'Stock1','SpecialField1','GROUP1','2012-03-01'UNIONSELECT 'Stock1','SpecialField2','GROUP1','1000'UNIONSELECT 'Stock2','SpecialField1','GROUP1','2013-01-18'UNIONSELECT 'Stock2','SpecialField2','GROUP1','0.256'UNIONSELECT 'Stock3','SpecialField3','GROUP1','330'UNIONSELECT 'Stock3','SpecialField4','GROUP2','1'UNIONSELECT 'Stock4','SpecialField3','GROUP1','365'UNIONSELECT 'Stock4','SpecialField4','GROUP2','0'DROP TABLE #StocksDROP TABLE #ReferenceCodesDROP TABLE #StockSpecialisedData[/code]What I would like to be able to do is create a view that pivots the "Specialised Attributes" and returns all the stock-level data, as well as all the specialised data. From my limited knowledge, I'd assume it would need to SELECT from #ReferenceCodes, then LEFT JOIN onto #StockSpecialisedData as I'd like to have all columns even if there's no relating row in #StockSpecialisedData.Is this possible using a view?As always, thanks in advance!Kevin.

Why is this Query Taking a long time?

Posted: 18 Jun 2013 05:58 AM PDT

[size="4"]This query seems to take forever. Is there a way I can speed it up?[/size] SELECT cat.Category1 , cat.Category2 , cat.Category3 , tblItem.GPItemNumber AS [Item Number] , tblItem.ItemNumber AS [Regional Item Number] , GlbOrItm.QUANTITY AS [Quantity] , GlbOr.OrderNumber AS [Order Number] , GlbOr.Created AS [Order Date] , ( tblUserProfile.FirstName + ' ' + tblUserProfile.LastName ) AS [Ordered By] , GlbOr.GPLOCNCODE , GlbOr.GPCSTPONBR AS LocationID , GlbOr.ShipContact , GlbOr.ShipAddress1 , GlbOr.ShipAddress2 , GlbOr.ShipAddress3 , GlbOr.ShipCity , GlbOr.ShipState , GlbOr.ShipZipCode , GlbOr.ShipCountry , GlbOr.ShipPhone , tTH3.TypeHierarchyDesc AS Channel , tTH2.TypeHierarchyDesc AS ChannelType , tTH1.TypeHierarchyDesc AS LocationType , tLH3.LocationHierarchyDesc AS Region , tLH2.LocationHierarchyDesc AS SubRegion , tLH1.LocationHierarchyDesc AS Market [b] FROM OPENQUERY (OrderSvr,'Select * From Commerce.dbo.GlobalOrders with (NoLock)') AS GlbOr JOIN OPENQUERY(ORDERSvr,'Select * From Commerce.dbo.GlobalOrderItems with (NoLock)') AS GlbOrItm ON GlbOrItm.fk_GlobalOrderID = GlbOr.GlobalOrderID JOIN tblItem WITH (NOLOCK) ON tblItem.GPItemNumber = GlbOrItm.GPItemNmbr LEFT JOIN (SELECT fkItemId ,aih1.itemhierarchydesc + '/' + aih2.itemhierarchydesc AS 'Category1' , bih1.itemhierarchydesc + '/' + bih2.itemhierarchydesc AS 'Category2' , cih1.itemhierarchydesc + '/' + cih2.itemhierarchydesc AS 'Category3'[/b] [b] FROM (SELECT fkItemID, fkItemHierarchyid, ROW_NUMBER() OVER (PARTITION BY fkitemid ORDER BY fkitemhierarchyid DESC ) AS 'Row' FROM tblitemcategory WITH (NOLOCK) ) S PIVOT ( MAX(fkitemhierarchyid) FOR Row IN ([1],[2],[3])) P LEFT JOIN tblItemHierarchy aih2 WITH (NOLOCK)ON aih2.ItemHierarchyID = [1] LEFT JOIN tblitemhierarchy aih1 WITH (NOLOCK)ON aih1.itemhierarchyid = aih2.parentid LEFT JOIN tblItemHierarchy bih2 WITH (NOLOCK)ON bih2.ItemHierarchyID = [2] LEFT JOIN tblitemhierarchy bih1 WITH (NOLOCK)ON bih1.itemhierarchyid = bih2.parentid LEFT JOIN tblItemHierarchy cih2 WITH (NOLOCK)ON cih2.ItemHierarchyID = [3] LEFT JOIN tblitemhierarchy cih1 WITH (NOLOCK)ON cih1.itemhierarchyid = cih2.parentid ) AS cat ON cat.fkitemid = tblItem.itemid LEFT OUTER JOIN tblUserProfile WITH (NOLOCK)ON GlbOr.UserID = tblUserProfile.fkUserID JOIN tblLocation AS tL WITH (NOLOCK)ON GlbOr.GPCSTPONBR = CAST(tL.LocationID AS VARCHAR(20)) JOIN tblTypeHierarchy AS tTH1 WITH (NOLOCK) ON tL.fkTypeHierarchyID = tTH1.TypeHierarchyID JOIN tblTypeHierarchy AS tTH2 WITH (NOLOCK) ON tTH1.ParentID = tTH2.TypeHierarchyID JOIN tblTypeHierarchy AS tTH3 WITH (NOLOCK) ON tTH2.ParentID = tTH3.TypeHierarchyID JOIN tblLocationHierarchy AS tLH1 WITH (NOLOCK) ON tL.fkLocationHierarchyID = tLH1.LocationHierarchyID JOIN tblLocationHierarchy AS tLH2 WITH (NOLOCK) ON tLH1.ParentID = tLH2.LocationHierarchyID JOIN tblLocationHierarchy AS tLH3 WITH (NOLOCK) ON tLH2.ParentID = tLH3.LocationHierarchyID[/b] WHERE GlbOr.Created >= DATEADD(day, -365, @EndDate) AND GlbOr.Created <= @EndDate AND (LEFT(GlbOr.OrderNumber, 5) = 'CINOR' OR LEFT(GlbOr.OrderNumber, 5) = 'CINSK') ORDER BY Category1 , Category2 , GlbOrItm.GPItemnmbr

Need assistance with a query

Posted: 18 Jun 2013 04:54 AM PDT

Is there a way to structure a single query to select all the unique values from field 1 of table A, and the unique values from field 2 of Table A and then select count(*) for all of the resulting combinations of field 1 and field 2 values?Thanks in advance.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog