Query

Question

Post reply

Query

jsshivalik

SSChasing Mays

Points: 612
More actions
July 27, 2020 at 12:42 pm

#3774931

Hi
I have below Data & want to display in below format
Name <45 45 To 60 60 To 90 90 To 120 > 120
Black Paint 540
Blue Paint 360 50
USE [Test]
GO
/****** Object: Table [dbo].[Batch] Script Date: 27/07/2020 3:00:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Batch](
[BatchNum] [nvarchar](10) NULL,
[ItemName] [nvarchar](50) NULL,
[DocDate] [date] NULL,
[WhsCode] [nvarchar](10) NULL,
[Qty] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Batch] ([BatchNum], [ItemName], [DocDate], [WhsCode], [Qty]) VALUES (N'B02', N'Black Paint', CAST(N'2019-07-10' AS Date), N'F01', CAST(540 AS Decimal(18, 0)))
INSERT [dbo].[Batch] ([BatchNum], [ItemName], [DocDate], [WhsCode], [Qty]) VALUES (N'B05', N'Blue Paint', CAST(N'2020-07-10' AS Date), N'F01', CAST(360 AS Decimal(18, 0)))
INSERT [dbo].[Batch] ([BatchNum], [ItemName], [DocDate], [WhsCode], [Qty]) VALUES (N'B06', N'Blue Paint', CAST(N'2020-01-30' AS Date), N'F01', CAST(50 AS Decimal(18, 0)))
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

OK, but what is your question?

Your desired format is difficult to understand, can you make it clearer?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

drop table if exists dbo.TestBatch;
go
create table dbo.TestBatch(
  [BatchNum] [nvarchar](10) NULL,
  [ItemName] [nvarchar](50) NULL,
  [DocDate] [date] NULL,
  [WhsCode] [nvarchar](10) NULL,
  [Qty] [decimal](18, 0) NULL) ON [PRIMARY]
GO

insert dbo.TestBatch ([BatchNum], [ItemName], [DocDate], [WhsCode], [Qty]) values 
(N'B02', N'Black Paint', CAST(N'2019-07-10' AS Date), N'F01', CAST(540 AS Decimal(18, 0))),
(N'B05', N'Blue Paint', CAST(N'2020-07-10' AS Date), N'F01', CAST(360 AS Decimal(18, 0))),
(N'B06', N'Blue Paint', CAST(N'2020-01-30' AS Date), N'F01', CAST(50 AS Decimal(18, 0)));
--select * from dbo.TestBatch

;with agg_order_cte(ItemName, Qty_array) as (
    select ItemName, string_agg(Qty, ' ') within group (order by DocDate desc)
    from  dbo.TestBatch
    group by ItemName)
select concat(ItemName, ' ', Qty_array) NewColName
from agg_order_cte;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

sgmunson SSC Guru Points: 110640 More actions · Answer 3

I'm not at all sure how the rows you show as output:

Black Paint 540

Blue Paint 360 50

qualify as being in the format you're specifying:

Name <45 45 To 60 60 To 90 90 To 120 >120

Please tell me how 360 appearing before 50 for Blue Paint qualifies? Also, don't you need to have some kind of indicator that there's no values for a given range? Or am I fundamentally misunderstanding your objective?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Are you trying to create an aging query, so that each invoice is "binned" by age, which is determined by [DocDate]? (the usual is something like "less than 30 days", "31-60 days", "61-90 days", "over 90 days")... I didn't do that with yours because there are no records for a bunch of those bins.

Something like this maybe?

select BatchNum
, ItemName
, docDate
, Age = DATEDIFF(day,docDate,GETDATE())
, AgeBin = CASE 
                /* modify the case statements so they fit your "bins" */
WHEN DATEDIFF(day,docDate,GETDATE()) < 60 THEN 'less 60'
WHEN DATEDIFF(day,docDate,GETDATE()) <= 365 THEN '61-365'
ELSE 'over 365' END
, WhsCode
, Qty
from dbo.TestBatch

Then you can do totals with that after, like this...

SELECT aging.BatchNum
, ItemName
, AgeBin
, BinQty = SUM(Qty)
FROM 
(select BatchNum
, ItemName
, docDate
, Age = DATEDIFF(day,docDate,GETDATE())
, AgeBin = CASE 
WHEN DATEDIFF(day,docDate,GETDATE()) < 60 THEN 'less 60'
WHEN DATEDIFF(day,docDate,GETDATE()) <= 365 THEN '61-365'
ELSE 'over 365' END
, WhsCode
, Qty
from dbo.TestBatch) aging
GROUP BY aging.BatchNum
,aging.ItemName
,aging.AgeBin;

Maybe reading this will help you get better answers - as it seems a lot of people are confused by your question.

This reply was modified 4 years, 3 months ago by pietlinden. Reason: code reformat
This reply was modified 4 years, 3 months ago by pietlinden.